• Article

SQL Questions & AnswersServer Tuning, Log Shipping, Mirroring, and More

Edited by Nancy Michell

Log Shipping or Mirroring?

Q Is it true that once mirroring becomes available, there is no reason to use log shipping anymore? It seems that mirroring is less functional than log shipping if you want to use the mirrored database for reporting. It also seems that mirroring only allows you to query against a database snapshot, which represents a point in time. Log shipping, on the other hand, appears to allow you to query against current data. I know the data can be out of date depending on how often you ship the logs, but this seems better than having to constantly create new snapshots. In other words, shipping the logs every hour seems a lot better than using mirroring and creating a new snapshot each hour.

A If reporting is needed, log shipping is not necessarily better in this scenario than mirroring and using snapshots. The log shipping secondary must be restored with the WITH STANDBY option in order to allow read-only access. The data is static at that point in time up to the last transaction log loaded, just like a database snapshot represents a point in time. If you need more up-to-date data to query against with log shipping, the secondary database must have transaction logs applied, which means users cannot access the database while that is occurring. Log shipping is not really a reporting solution.

SQL Server Tuning

Q I have SQL Server 2000 with approximately 45 databases on a single instance. These databases are used by multiple ASP applications running on my public Web site. SQL Server regularly reaches 100 percent CPU utilization and requires reboots. How should I start troubleshooting and repairing this problem?

A Start with rebuilding all of your indexes. It’s easy, assuming you have memory configured correctly. Then run SQL Profiler for a few hours and capture a snapshot of your standard business T-SQL traffic. Run this trace through the Index Tuning Wizard (ITW) that comes built into SQL Server 2000. It will take a look at the queries and make recommendations for indexes to improve performance.

If you have SQL Server 2005 installed, you can use the Database Tuning Advisor (DTA) which is the successor to ITW, to tune your workload. DTA can tune SQL Server 2000 as well. It tunes a database on a production server by offloading most of the tuning load onto a test server. DTA uses the production server hardware configuration information, without actually copying the data from the production server to the test server. It only copies the metadata and necessary statistics.

Database mirroring and using a snapshot on the mirror may be easier to administer since you are not affecting the process like you are with log shipping. You are just creating a new snapshot on a periodic basis. The problem, though, with mirroring and snapshots is that since each snapshot is named, either each new snapshot created would have a different name (which may impact the reporting application) or the old snapshot would need to be deleted and then created again (which would impact the availability of the reporting solution). So to some degree, both log shipping and mirroring have different advantages as reporting solutions in addition to their intended use for availability and disaster recovery. But with log shipping, reporting is unavailable when you apply a new transaction log to refresh data, and with database mirroring, reporting is unavailable when you drop and recreate the existing snapshot, or create an additional snapshot to make it more current.

Log shipping is based on backup and restore, and supports multiple secondaries, each of which can have its own schedule or delay, which can also account for other problems, such as user error. Database mirroring can be simpler to implement. When using its synchronous mode, the mirror database is more up-to-date (with no data loss) than its log shipping counterpart, which is only as good as the last log that has been applied to the secondary. With the right configuration, mirroring also supports an automatic failover scenario. Database mirroring, unlike log shipping, can only have one mirror database. Both database mirroring and log shipping provide protection at the database level, while failover clustering provides protection at the instance level. A geographically dispersed cluster could work over distances for availability purposes, but this can be an expensive solution. Database mirroring and log shipping can work with clustering to provide extremely high availability for your SQL Server™ instances.

Load Balancing SQL Server

Q How can I configure SQL Server to operate over multiple servers that operate virtually as one? In other words, I’d like to have SQL Server load-balanced across several Windows® servers in order to distribute the processing load to improve overall performance when there are many users accessing the database through a Web site. I would also like failover clustering.

A No version of SQL Server supports load balancing in the manner you discuss. However, there are a number of high-availability techniques to separate the processing load onto different servers. For example:

  1. Peer-to-peer replication (new in SQL Server 2005)
  2. Distributed partitioned views (available in SQL Server 2000 and enhanced in SQL Server 2005)
  3. Active/active failover clustering, which is really active/passive and passive/active; in other words, two instances of SQL Server (this technique has been available since the release of SQL Server 7.0)

SQL Server doesn’t support load balancing because Microsoft Cluster Service (MSCS) uses what is known as a "shared nothing" cluster methodology. That is, the nodes operate independently of each other and do not share disks or resources. There is only one owner of each resource at a time.

Hiding Data from Your DBAs

Q For security reasons, my HR department doesn’t want the IT department to be able to view the personnel database, which is stored in SQL Server. How can I prevent the IT staff from simply restoring backed-up copies of the database onto another server during the backup process, enabling them to see the data?

A Unfortunately, there is no way to completely prevent DBAs from restoring databases elsewhere unless their physical access is restricted to only certain servers. There are a few things you can do, though, to make the backup process more secure:

  1. 1. Encrypt the data in the database. You may take a small performance hit, but the DBAs won’t be able to see the data in plain text (at least not easily).
  2. 2. Use a third-party product such as Quest Software’s LiteSpeed for SQL Server and encrypt your backups on the way out so that nobody can view the backup files. Doing so would add an additional layer of protection as standard backups made by the SQL Server engine are not encrypted.

However, keep in mind that at one point or another the DBAs may be asked to do things that will involve sensitive data. There is no single solution for securing data and corporate policies should be put in place to ensure that data-related accidents are prevented. Even with all the security in the world, you still need to trust those who have access to your data.

Finding the Bottleneck

Q When I run one particular query, CPU usage will reach 100 percent after a few seconds, and the server will be shut down in a few minutes. I can reproduce it every time. We have hundreds of millions of unique values in the table in question.

Is this a hardware issue or a software issue? I can’t get any information from Event Log. The server is an HP AMD 64-bit machine. I am using SQL Server 2005 (64-bit).

A If the system really is crashing (bugchecking) then you will need to take a look in the memory.dmp file. First check the Startup and Recovery settings (in the Control Panel) and make sure it’s set to create at least a kernel memory dump. If that is already set, then find the memory.dmp file and save it to another machine. The next step is to download the Debugging Tools for Windows (windbg) from microsoft.com/whdc/devtools/debugging. Make sure to select the right package for your system (in this case you want the 64-bit versions), and then choose the x64 package.

When the debugger is installed, run windbg and set the symbol path to the Microsoft Symbol Server. To do this, type Ctrl+S and enter a valid symbol path. I usually use something like srv*c:\symcache*https://msdl.microsoft.com/download/symbols. For more information on this, see Debugging Tools and Symbols: Getting Started.

Next, open the memory.dmp file in your favorite Windows debugger and run !analyze -v. This will tell you what the bugcheck was and give more details on why the server crashed.

Thanks to the following Microsoft IT pros for their technical expertise: David M. Brown, Alan Doby, Keith Elmore, Doug Harrison, Rob Hawthorne, Allan Hirt, Matt Hollingsworth, Steve Korman, Matt Neerincx, Rambabu Pusarla, Venkat Rao, Vishal Subramaniam, Manoj Syamala, Frank Wiemer, Steven Wort, and Jim Yang.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.