SQL Server queries, Registry settings, and replication

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.

Q: Replication works fine from servers, but I can't replicate data using TCP/IP. Why not?

Users ask this question frequently on the SQL Server newsgroups, and many of my clients have had trouble with replication. Successful replication using TCP/IP requires a little knowledge of replication architecture, SQL Server integrated security, and Windows NT domain security. But the solution is not hard.

First, I'll review the three roles that SQL Server plays in replication. Two distinct roles, subscription server and publication server, are easy to explain: A subscription server subscribes to a publication, or dataset, from a publication server. The distribution server plays the third role: managing most of replication's internal operations.

The distribution server holds replicated transactions as they pass from the publisher to a subscriber and runs the three core processes that make replication tick: synchronization, the log reader process, and distribution. The synchronization process sets up the initial copy of the data on the subscription server. The log reader process then scans the publication server's transaction log looking for transactions marked for replication. The log reader moves them into the distribution database for storage until the distribution process gets around to parceling out the transactions to interested subscribers.

Distribution server's SQL Executive service (SQLExec) manages these three core replication processes. SQLExec must have permission to log on to the SQL Servers on the publication, distribution, and subscription servers. The servers can run on one machine, or you can distribute the servers across multiple boxes. SQLExec is a separate process from the core database service (MSSQL), so SQLExec must log on to the database like any other client application, even if SQLExec and MSSQL both run locally on the same machine. (SQLExec and the distribution server always run on the same machine in a replication model.) SQLExec logs on to the database using a trusted connection, which uses SQL Server's integrated security mode to authenticate users against their NT-based user credentials (username and password) rather than checking an internal SQL Server login account. SQL Server authenticates trusted connections for service-based applications such as SQLExec. The authentication uses the credentials under which you have configured the service (SQLExec, in this case) to start.

Everything make sense so far? Great! The trick to getting replication to work over TCP/IP is that you can establish a trusted connection only by using the Multi-Protocol or Named Pipes NetLibs. The TCP/IP sockets NetLib can't establish the crucial trusted connection, so you have to use the Multi-Protocol or Named Pipes NetLibs when you replicate databases over TCP/IP. Usually the fix is as simple as using the Client Configuration Utility on the distribution server to correctly configure Multi-Protocol or Named Pipes as the default NetLib.

If the error history for a replication task shows that you can't establish a trusted connection after setting the default NetLib, examine the NT user account that runs SQLExec. I can't say exactly what you need to change to let SQLExec connect via a trusted connection because there are so many variables, but you're usually safe if you stick to a domain account with administrator privileges.

I know the issues mentioned in this article are common problems because they frequently come up in public discussion forums and they have been problems for many of my clients. But the worst problem in the world is the one you're currently facing.

The mark of an expert isn't knowing the answer but being able to find the answer quickly. Nothing is more productive than hard work and a little midnight reading, but I've found the Microsoft SQL Server newsgroups (msnews.microsoft.com), the CompuServe SQL Server forum (MSSQL), and Microsoft TechNet to be invaluable resources. I encourage you to use these resources to their fullest.

Let me know other resources you've discovered. I'll do my best to squeeze a T-shirt out of Windows NT Magazine for new resources--and I'll mention them in a future article.

Next month Karen Watterson and I will begin cohosting this column, and we need some great questions from you to make the column successful. Send your questions our way to brian@spectrumtech.com or to karenwatterson@msn.com.