Speed up your SQL Server 2005 Books Online

Microsoft did a very literal translation of the term Books Online with SQL Server 2005. Books Online happens to be the help file for SQL Server. In SQL Server 2005 (and now with SQL Server "Katmai"), Books Online has the capability to go "online" and look for related searches on the Internet, MSDN Online, community websites like CodeZone, etc. A lot of users find it annoying that it takes ages to load a search result in Books Online. This is because the default configuration is that it will search for the results in the local version, then, it will go online and grab the results from the online sources configured. The make Books Online respond a bit faster to searches, configure it not to go online at all (Google still offers faster search results, after all). Open SQL Server Books Online, go to the Tools menu then select Options. When the Options window appears, select the Online option under the Help node. On the When Loading Help content section, select the Try local only, not online option. This will return search results from the local copy of Books Online (and make the searches a bit faster). Try it out and see what happens

The pitfalls of ROBOCOPY

I have been using ROBOCOPY for a lot of reasons - Home folder replication for disaster recovery purposes, file-copy utility for custom transaction log shipping for SQL Server, etc. It has been a very useful tool for me, especially when you talk about disaster recovery. But what a lot of people do not realize is that this will become a very big headache if not used properly. One scenario I have seen is a database log file being replicated over to another server. Database log files are being locked by the service or application responsible for running the database engine. In this case, depending on how it is configured, ROBOCOPY will try execute the copy command until such time that it can do so. Now what will happen if it cannot copy the file due to a lock caused by the service? It will enter an infinite loop. And if you're like me who likes to log every activity done by any utility or script, you will configure ROBOCOPY to do so as well. When this happens, the log file generated will become so huge that it would be very difficult to open with Notepad. Just a word of caution when using ROBOCOPY - DO NOT use the utility on files that are being locked or used by services or applications.

The Pains of Collation Settings

Collation settings define the physical storage of character strings in SQL Server. It specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. Collation settings have a direct impact on the databases if you configure them to use a different collation other than the default. When you configure a different collation setting during the installation, the rest of the databases (system and user-defined) will have this setting was their default when you create them unless you specifically defined something different from the default. One case I encountered was that the tempdb database had a different collation setting compared to the user databases. This is because SQL Server was reinstalled and the master and msdb databases were restored from backup together with the user-defined databases. This made the collation settings of the entire database server back to their original configuration - well, almost. Since the model database was not restored (and obviously, the tempdb database does not require a backup), collation settings for the both would be the settings defined during re-installation. A script executed in one of the user-databases would require the use of the tempdb and since they do not have the same collation settings, queries will fail unless the queries themselves take into account collation setting differences. The next best bet is to rebuild the master database and change the collation settings and go through all the grueling task of restoring the system databases, provided that the user-defined databases are still intact. Well, another approach would be to simply restore the model database if you have a backup (or simply look for the old MDF and LDF files if they are still working fine). This is because tempdb database takes the settings of the model database everytime SQL Server service starts. This will resolve collation problems should you encounter a similar case

What? I lost my port on a named instance?Dynamic port detection?

One thing I realized is that SQL Server port numbers will not be the same for I don't know what reason. I found this out while rebuilding a SQL Server named instance. We had standard settings in setting up SQL Server. But since I am rebuilding my test environment, I just used the default port number settings (which is 1433) during the process. To my surprise, it was using port number 3080 when the service started. Apparently, SQL Server dynamically determines the port it listens on. It will try to listen on the port it used previously. If for some reason it cannot bind to that port, the named instance may bind to a different port - which is obviously the case for my setup. Now this would cause a lot of problems if your SQL Server is interacting with a lot of servers, services and applications as you need to either change the port number to it's original value (which I think would be more appropriate) or change the clients to use the new port number. I have yet to dig up more details on this as to why SQL Server behaves that way. At least I know another place to look at when troubleshooting connectivity problems

I was doing more research on SQL Server port usage when I chanced upon this Microsoft KB. SQL Server clients use DBNETLIB to perform port detection. This is either thru ODBC or SQLOLEDB componentes which loads DBNETLIB. SQL Server clients can use the Client Network Utility for dynamic port detection. This is to make sure that in case SQL Server uses a different port number during service startup or is configured to user dynamic port assignments, the client can map to the port number that a specific instance uses. Now, what if the client is a third party application running on a non-Microsoft platform(like a JSP application running on top of Apache on a Linux box?) How does this port resolution work?