Chapter 2: Managing SQL Server

On a daily basis, database administrators face a number of issues, problems, and tasks. This chapter covers common problems with SQL Server and how to fix them.

Managing the Server

In this section, I’ll talk about typical problems you may experience as you manage and configure SQL Server. I’ll introduce the “best practices” approaches you can implement to ensure you can sleep at night without getting paged.

Managing Multiple Instances

SQL Server now provides the ability to run multiple instances (up to 16) on the same server. For as many reasons as there are to do this, there are twice as many reasons to run for the hills when this request comes across your desk. This feature was added to compete with database products like Sybase and Oracle.

If this feature (I’ll let you decide for yourself if “feature” is the correct word) is used properly, it is perfect for testing systems where you’d like to know the effects of a SQL Server service pack on your application. Since the server directories are not shared among instances, you can test a hotfix on one instance and not affect your normal development server. If your first instance crashes, it won’t affect any other instance.

Caution 

Keep in mind that if you do test a hotfix or service pack on one instance of SQL Server, it will upgrade the tools across all instances. If the service pack breaks a tool, it will be broken across all instances.

Multiple instances are also great for ISPs who are charging customers based on their CPU usage. Tools like Performance Monitor have been improved to let you monitor individual instances. You could schedule Performance Monitor to log the CPU every hour to see its utilization for each instance and generate logs for easy line-item billing.

If you’re clustering SQL Server in an Active/Active cluster, SQL Server will be installed across all nodes at once. When you install the second node, it installs it as a second instance of SQL Server. This is an example of a time when you have to use multiple instances. If a failover occurs, you’ll need to make sure that the machines are sized to handle the load of both machines for a short period of time. We’ll talk much more about clustering in Chapters 10 and 11.

The downside of using instances is the competition this paradigm sets up for server memory and CPU usage. You must manage the memory carefully, setting a maximum for each instance. Even though you can use up to 16 instances on a server, that approach is impractical. You must also buy additional licenses for any new instances you create in SQL Server Standard Edition. It is a better idea to buy a new server, or expand your current server, than to add another instance.

Caution 

Unlike SQL Server, Analysis Services cannot run more than one instance. Analysis Services also cannot run on the same machine as SQL Server 7.0’s OLAP Services.

Tip 

You can use the @@SERVERNAME server variable to determine which instance you’re currently connected to. If you’re connected to the default instance, the query SELECT @@SERVERNAME returns only the server name. If you’re connected to a named instance, the query returns the fully qualified named instance.

Renaming a Server

One of the most frequently asked questions I see in newsgroups is how to rename a server once it’s installed. Actually, accomplishing this is much easier than most people realize. Simply rename the server in Windows. After you reboot the server and start SQL Server, SQL Server 2000 automatically recognizes the fact that the server’s name has been changed.

Note 

In SQL Server 7.0, you would have to rerun setup as after rebooting. Setup would make the necessary registry changes and make no changes to the databases.

However, you must change the server entry in the sysservers system table. This is easy to accomplish: all you have to do is log in to each instance and perform the following steps:

1. Change the Enterprise Manager registration to reflect the new server name.

2. Disable replication (if applicable).

3. Run stored procedure sp_dropserver <old server name>.

In the Trenches

If you have replication installed, you may encounter the following error when issuing the sp_dropserver stored procedure:

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44 There are still remote logins for the server ServerName.

If you receive this error, remove the remote logins in Enterprise Manager, add them back, and then reissue the command.

4. Run stored procedure sp_addserver <new server name> with the LOCAL option.

5. Enable replication (if applicable).

   Caution 

   You cannot rename a SQL Server virtual name in a cluster. The proper way to rename a server that has been installed in a cluster is to uninstall the instance and reinstall.

Tweaking Configuration Options

SQL Server generally comes configured rather well right out of the box. However, some configuration options respond positively to tweaking, and other options actually lessen your SQL Server power rather than enhance it. You can view the current configuration options by going to Enterprise Manager, right-clicking on your server’s object, and selecting Properties.

Select the Running Value to see what your system is currently running. Select Configured Values to see configuration options that have been set but are not yet applied (usually because the server needs to restart in order to apply them). This can be done in the Processor, Memory, Connections, Server Settings, and Database Settings tabs. In Chapter 4, we’ll discuss how to rapidly deploy configuration changes to many servers through registry changes and queries.

Some of the configuration setting will require a restart of the SQL Server service in order for them to taken effect. The following configuration settings will require this type of measure:

• Configuring the affinity-mask option

• Configuring the AWE-enabled option

• Configuring the default fill factor for the server

• Configuring the lightweight pooling option

• Configuring the locks option

• Configuring the open objects option

• Configuring the priority-boost of SQL Server

• Configuring the remote access option

• Configuring the scan for startup procedure option

• Configuring the user connections option

• Configuring the working set size option

Additionally, you’ll have to restart the SQL Server services if you do many of the tasks we’ve already discussed, such as:

• Applying a service pack

• Changing the server name

• Changing the account that starts SQL Server

• Correcting a suspect database by resetting the status of the database

• Changing the mail profiles for SQL Mail

In later chapters, I’ll discuss SQL Mail and how clustering affects many of these items.

Optimizing Memory

Use the Memory tab of the Properties dialog box to configure memory usage. SQL Server is very memory and CPU intensive. Starting with Version 7.0, SQL Server automatically negotiates with other processes (such as the operating system) for memory. This usually prioritizes SQL Server above other applications.

Analysis Services handles this in the same manner. You can recapture some control from the automated processes by configuring the Minimum (MB) and Maximum (MB) settings (see Figure 2-1).

Figure 2-1: Set memory controls instead of relying on automatic processes.

Configuring the Minimum (MB) setting gives SQL Server a minimum pool of memory that will not be compromised by other applications. This pool of memory is grabbed during the server’s startup. Similarly, you can set the Maximum (MB) value to cap the amount of memory SQL Server can take. If you have a machine with lots of RAM and use the PAE switch, don’t forget to reset the limits so you use the new RAM.

It’s usually not a good idea to apply a fixed memory setting in SQL Server. By setting the maximum and minimum values, you give SQL Server boundaries that prevent it from stepping on other applications’ memory needs. If you can avoid placing other applications on your SQL Server, do so. This especially holds true with Exchange or heavily used IIS machines.

If you can’t avoid placing another application like Analysis Server on your SQL Server, make sure you place a minimum and maximum boundary for SQL Server’s memory. Otherwise, you risk the other application either taking too much memory away from SQL Server or SQL Server taking too much memory away from the application.

Caution 

Several bugs are known to exist as a result of conflicts with other BackOffice suite applications on the same server as SQL Server.

Memory is the number one thing you can add to increase your server’s performance. Frankly, it’s hard to run a moderately active SQL Server without at least 2GB of RAM dedicated to just SQL Server. If you add additional RAM, SQL Server can cache more data, which allows more queries to pull their results right from RAM versus having to go to disk. In some cases, you’ll notice that by adding RAM your processor utilization will be expanded as well, since more tables are cached.

Tip 

One way of estimating the amount of RAM you’ll need is to look at the active tables. If you have a table that has 3GB of data in it that is used frequently, you may not want to go into production until you have at least 3GB of RAM for that one table.

Managing Processor Use

On the Processor tab, you can specify which processors are being used by each SQL Server instance, perhaps assigning each instance of SQL Server its own processor. However, it’s best to avoid the temptation to tweak the settings on the Processor tab.

Several settings have a history of crashing servers when they’re adjusted, such as the Boost SQL Server Priority on Windows. If you select that option, SQL Server’s priority in Windows moves from 7 to 13, and there are hardly any occasions where this would benefit you. Instead, it may hinder your operating system from operating normally, eventually causing your system to blue screen.

Managing Connections

On the Connections tab, you can specify the maximum number of connections that SQL Server will allow to your instance. If this option is set to 0, SQL Server allows an unlimited number of connections. There are few cases where this option would need to be adjusted. An ISP or ASP may want to adjust this setting in order to lock a customer into a given amount of connections to a SQL Server.

Setting options in this tab assigns default options to users who are connecting to the SQL Server instance and who don’t have options already defined. Some user options supercede settings on the Connections tab. For example, the NO COUNT option is turned off by default in Query Analyzer. The connection options from Query Analyzer supercede any settings you configure here, because the tool passes SQL Server its options when running the query.

Other command-line tools, such as osql, use what you configure here because these tools don’t have configurable options. Instead, the user would manually issue a SET NOCOUNT ON statement in osql. This also applies to programs connecting through ADO.

Active Directory Integration

On Windows 2000 systems, you can now add a SQL Server instance and its objects to Active Directory. Doing this gives your users a quick way to find your server’s objects.

If you want to add or remove a server in Active Directory, the account that starts SQL Server must have local administrator rights. Because of this, in most environments the security risk outweighs the benefit of having your SQL Server in Active Directory. You can add or remove a server under the Active Directory tab or by using the sp_ActiveDirectory_SCP stored procedure. Use the parameter @Action=‘CREATE’ to add your server to Active Directory. Use the parameter @Action=‘DELETE’ to remove it.

Optimizing Database Settings

When SQL Server starts up, it rolls back any uncompleted transactions and rolls forward to disk any completed transactions that may be in cache. This process is called recovery in the SQL Server error logs. The Recovery Interval option in the Database Settings tab specifies how long, in minutes, SQL Server will allow itself to recover a database on startup. A setting of 0 allows the server to autoconfigure itself.

The Recovery Interval option should be left alone unless you see that your transaction logs are reaching checkpoints often, and are therefore adversely affecting the performance of your database. To monitor this, watch the disk write activity in System Monitor. If you see spikes in write activity due to checkpoints, make small incremental increases in the recovery interval. In Chapter 5, I’ll talk about monitoring your SQL Server in more detail.

The Database Settings tab also has an option for you to set the default locations for data and log directories. If you don’t set your own customized directory locations, SQL Server places data and log files in the system database folder (\Program Files\ Microsoft SQL Server\MSSQL\Data).

I always recommend that administrators move the SQL Server files away from the system databases. This protects you from the worst-case scenario, where a user deletes the wrong files when the server is stopped. Additionally, make sure your backup files are off the data drive as well in case you lose the data drive.

Tweaking Server Settings

On the Server Settings tab, you can turn on or off access to modification of the system tables. With this option turned on, you can perform some brute force administrative tasks that I discuss throughout this book.

Tip 

Only turn on the modification to the system tables option for the few minutes that you need it, then turn it off again. I keep this option off to protect my systems from myself.

Another setting you can tweak on this tab is the Query Governor. This is an advanced option you can use to limit the size of queries that can be executed. For example, if you set this option to 45, queries that are estimated to take longer than 45 seconds to run will be rejected. This would also be a handy setting to tweak if you’re in a service bureau type environment where you don’t want one query to take all of your server’s resources.

The main difference between this option and the Query Timeout option is that the Query Governor option uses the estimated amount of time while the Query Timeout option uses real time. The Query Governor will not even execute a query if the optimizer estimates it to be greater than the cost limit. A setting of 0, which is the default, means that the Query Governor is turned off.

Note 

You can also set the Query Governor option at the connection level by using the SET QUERY_GOVERNOR_COST_LIMIT command. The option is set for the user as long as the connection is active.

Security Tab

On the Security tab, you can change the authentication type for your server. SQL and Windows Authentication is the equivalent of Mixed Mode in previous releases of SQL Server. Running SQL Server in this mode allows users to log in using either their Windows accounts or a standard SQL Server login.

Opting for Windows Authentication Mode means that only users with Windows accounts are able to log in. The respective advantages of each of these modes are covered in the next chapter.

You can also set up auditing in the Security tab. You can log successful login attempts as well as failed login attempts. Logs are written to the SQL Server error log and to the Windows application log. The locations of the logs are configurable, and Chapter 5 covers these configuration options.

If you’re running SQL Server in Windows Only mode, and a user tries to connect to the server with a standard SQL login, an error displays.

Most users translate this error to mean a bad password and call the help line. Even though SQL Server allows you to add new standard logins in Windows Only mode, users can’t use them.

Tweaking Startup Parameters

A huge array of startup parameters is available for SQL Server, and these parameters can be extremely helpful when tracing problems or checking on the system’s health. You can specify any of these parameters in the Startup Parameters area of the General tab in Server properties.

• -d<master database path> This option is set by default and specifies the location of the master database (for example, -d C:\Program File\Microsoft SQL Server\MSSQL\Data\Master.mdf).

• -e<error path> The full path to the error log. This option is set by default (typically, -e C:\Program Files\Microsoft SQL Server\MSSQL\Log\ Errorlog).

• -l<master log path> The full path to the master database’s log file. This option is set by default (for example, -d C:\Program File\Microsoft SQL Server\MSSQL\Data\Master.ldf).

• -c This option stops SQL Server from running as a Windows service by breaking ties with the Service Control Manager. This shortens your startup time, but should not be adjusted unless absolutely necessary.

• -f This option starts the SQL Server instance in minimal mode. Once in minimal mode, you can fix minor problems that have occurred in your system using sp_configure. For example, if you specify a fixed memory setting that is too high and lock your system, you can start the system in this mode to fix the problem. Think of it like Safe Mode in Windows (or VGA mode in Windows 2000).

• -g This option reserves a block of memory in megabytes for use by SQL Server processes other than the core components. For example, extended stored procedures, OLE automation objects, and OLE DB providers in distributed transactions use this space. The default for this is 128MB, which is normally sufficient. Use this option only if you receive warnings in the SQL Server error log. Improper setting of the option may lead to SQL Server crashing.

• -m This option starts the SQL Server instance in single user mode. This also disables the CHECKPOINT process. The CHECKPOINT process ensures that transactions that are written to the data cache are written to the physical database. This option is used often in conjunction with the -f option.

• -n This option disables the writing of events to the Windows NT or Windows 2000 event log. If you set this option, make sure that you enable the -e option or no logging will occur on your server. Only turn the -n option on if you have an enormous number of errors showing up in the application log erroneously. Correct the problem, then turn logging back on.

• -s This option allows you to start a named instance. For example, use sqlservr.exe -sINSTANCE1 to start a named instance called INSTANCE1.

• -T<trace number> This option turns on trace flags, which are discussed throughout this book. Note that this option requires an uppercase T (a lowercase t is for internal Microsoft support).

• -x This option disables logging of CPU activities for connections to the sysprocesses table. Setting this option provides a small increase in performance.

Something that has helped me in a production environment is to occasionally take a snapshot of the master database’s database and log files and keep them on a separate drive or server in case of a disaster. Having backups of these databases should always be primary, but in case of a corrupt master database, it is much quicker to point the startup parameters to the alternative master database until you can fix the problem during regular maintenance hours. The key here of course is that you take a regular snapshot of the master database files after every major change (like creating another database or login).

Take Command

You can use any of the startup parameters discussed in the previous section from the command line. The executable file is sqlservr.exe in the binn directory. Running this command displays the error log (shown in the following illustration). To stop SQL Server, use ctrl-c or ctrl-break and confirm the shutdown. If you close the DOS prompt, it will also stop SQL Server. When you start the server from the command line, you can't stop or restart it in the Services applet of Control Panel or through Enterprise Manager.

After you start SQL Server in this mode, you can use Enterprise Manager to perform all the administrative functions that are available in normal mode. I don't recommend that you run your server from the command line, but sometimes Microsoft support requests this method to get a server up.

Setting Advanced Options

There are a number of advanced options you can set once you use the stored procedure sp_configure. As a matter of fact, 26 of the 36 configurable options for SQL Server are considered advanced options, and you cannot use them until you enable them to be viewed. As of this writing, Service Pack 3 for SQL Server hadn’t been released. The service pack does add an option.

In the Trenches

If you have a problem starting the SQL Server service, the first thing that should be done is to try to start SQL Server in minimal mode (-f) and in single user mode (-m). This will start your SQL Server with minimal resources and with no one connecting to it. Once you're in this mode, you can correct any problems that led to your server crashing. For example, if you set your memory to fixed and over-allocate the amount of RAM you assign SQL Server, your server may not start. You can start it in minimal mode and use sp_configure to set it back to normal RAM usage.

Another thing to keep in mind if you're starting your SQL Server in single user mode is you will want to ensure that no one else can connect to the server while you're trying to fix the problem. Many times I've had to unplug the server from the network. When that doesn't work, generally it's a third-party service running on that server, such as BackupExec, that is taking the only connection to the database.

Microsoft has placed a safeguard on your system to make it a little harder to configure advanced options. To turn on the ability to set advanced options, you must go to Query Analyzer and run the following command:

sp_configure 'show advanced options', 1 RECONFIGURE

The command does not go into effect until you issue the RECONFIGURE statement. At that point, run the sp_configure stored procedure with no parameters to view the configuration on your server.

Pay special attention to the run_value and config_value columns. These two columns are the equivalent of the configured and running values I mentioned when discussing server properties earlier in this chapter. The current running values are stored in the syscurconfigs table in the master database.

A primary scale-up enhancement to SQL Server 2000 is its support of Address Windowing Extensions (AWE). This gives you the ability to access more memory than was traditionally available to SQL Server (3GB in Enterprise Edition on a 4GB Windows NT machine). This option is only available to SQL Server Enterprise and Developer Edition servers under Windows 2000 Advanced or DataCenter. If you have less than 3GB of RAM, SQL Server will ignore the option.

In the Trenches

You can turn on AWE mode for other editions of SQL Server, but doing so may cause your server to behave unpredictably. One of my clients set this option on a Windows NT machine and then couldn't stop the service.

Enable AWE with the following syntax:

sp_configure 'awe enabled', 1 RECONFIGURE

Once this option is set, SQL Server takes all but 128MB of RAM on your server. This is why it’s especially important to set the Maximum (MB) setting I discussed earlier. If you use sp_configure to set the Maximum (MB) setting, it is called max server memory. You can set max server memory in the Server Properties screen or use the command line to set it to 8GB by entering:

sp_configure 'max server memory', 8192 RECONFIGURE

Setting this option requires a restart of SQL Server. SQL Server also loses the ability to dynamically control memory once you turn AWE support on. This means that SQL Server acquires whatever memory is available on startup (other than the 128MB of RAM that’s reserved) or up to the point of the configured maximum.

Caution 

If you have more than one instance on the same server, you must pay special attention to max server memory. If both instances are configured for AWE support and your SQL Server’s memory is not managed properly, it is possible for one of the instances to ignore the AWE option. For example, if you have 6GB of available RAM and you’ve set one server to use a maximum of 4GB, the second will not use AWE because it doesn’t have the available 3GB required to start in this mode.

Another new feature is C2-level auditing, which allows you to monitor all access to your server in conjunction with SQL Server Profiler. To set this option, use the following syntax:

sp_configure 'c2 audit mode', '1' RECONFIGURE

C2-level auditing is covered in greater detail in Chapter 9.