Three Tricky Questions

This month I'll address a few perplexing questions that users ask over and over in the Microsoft SQL Server newsgroups. I picked three problems that can be incredibly frustrating to troubleshoot unless you know the trick to fixing them. I hope one of the answers will solve a problem you're facing now or save you a few hours (or days) of headaches in the future.

Q: My SQL Server query runs from a Windows NT client but takes forever from Windows 95. What's up?

Don't worry, you're not alone. Fortunately, you can work around the problem relatively easily. I first noticed this behavior last summer when I was helping a client who used IPX as a network protocol. Our custom SQL Server application ran fine from some clients and was intolerably slow from others. Eventually I noticed that the application ran slowly only from Win95 clients that had IPX installed. The application became lightning fast if I removed the IPX protocol.

The problem is a result of a networking enhancement called direct hosting. Microsoft implemented the enhancement in network client software to increase communications speeds. Direct hosting lets the client bypass the NetBIOS layer when communicating with the server over IPX. Unfortunately, the Win95 direct-hosting technology seriously slows processing when you use it over Named Pipes. Named Pipes is the default and most commonly used SQL Server NetLib. (A NetLib is the software that lets a client talk to SQL Server over the network.) I've seen queries that return just a few rows take 2 seconds from an NT client and more than 20 seconds from Win95 clients. Ouch!

Getting around the problem is simple. Because SQL Server clients are slow when they use direct hosting with IPX over Named Pipes, my advice is: "Don't do that." I know two easy alternatives. Both alternatives let you use IPX but avoid the deadly direct-hosting-over-Named-Pipes problem.

One technique is to disable direct hosting. This technique works fine, but it requires hacking the Registry and can slow the performance of other applications on your client that don't use Named Pipes. If you have your heart set on this method, Microsoft Knowledge Base article Q156430 (http://www.microsoft.com/kb/articles/q156/4/30.htm) lists the necessary Win95 Registry changes to disable the direct hosting feature for client computers running Win95. To the Registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VxD\VN, add a string value named DirectHost, and set the string value to 0. Also ensure that NetBIOS support is enabled for NWLink if NWLink is the only transport protocol loaded on the client computer.

I recommend keeping your life simple by using the other alternative: Pick a different SQL Server interprocess communication (IPC) mechanism, such as IPX sockets or the Multi-Protocol NetLib. Changing the default IPC for a SQL Server client is a breeze. The SQL Client Configuration Utility provides a GUI for setting up all client site networking information. Configuring the Client Software in Books Online (BOL) explains the process well.

Q: I lost all my SQL Server Registry settings when I reinstalled NT, but I still have all my original SQL Server data devices. What's the easiest way to get my database back?

The answer is as easy as knowing about the undocumented RegistryRebuild option available within the SQL Server Setup program. RegistryRebuild isn't supposed to be a secret, but Microsoft accidentally left it out of the SQL Server 6.5 documentation.

The following command rebuilds the necessary SQL Server Registry entries and registers all the SQL Server-related services with the NT Service Control Manager:

setup /t RegistryRebuild = On

Be sure you use this capitalization and spacing. Don't misspell the option! If you do, SQL Server will ignore the unrecognized setup switch and proceed with the full setup program, with unexpected results. You need to run the command from the \binn in the SQL Server root directory; if you don't, you might run another application's setup program.

The RegistryRebuild version of Setup is similar to the real McCoy. This RegistryRebuild version doesn't offer you all the options of a regular installation and doesn't splash a big banner across the screen explicitly saying that the RegistryRebuild mode is running. The program will ask you for the size and location of the master device, but it won't create a master device because you're reusing an existing file; the program will use this information only to repopulate the Registry keys. Setup will also ask you for the size (in megabytes) of the original master device and the master device's fully qualified path. You can determine the size by looking at the original master device, which is created in \mssql\data\master.dat by default. Entering the wrong size doesn't make a difference because the size isn't stored in the Registry, but why tempt fate? However, the server definitely will not start without the correct location of the original master device.

Setup also will ask you what sort order and character set to install. Usually, keeping this information consistent is important when you're dealing with databases, but in this case, Setup doesn't store your responses in the Registry. So don't worry if you can't remember what sort order and character sets you picked the first time you installed SQL Server; the RegistryRebuild option won't change your old settings. Setup does listen to you when you decide which network protocols you want SQL Server to support, so make sure you select all the protocols you need. Forgetting one protocol won't hurt your data, but certain applications may not connect correctly. I've never had a problem with RegistryRebuild, but having a good backup of your devices never hurts.