SQL Questions & Answers
Server 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.
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:
- Peer-to-peer replication (new in SQL Server 2005)
- Distributed partitioned views (available in SQL Server 2000 and enhanced in SQL Server 2005)
- 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. 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. 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.
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