SQL Questions & Answers Server Tuning, Log Shipping, Mirroring, and More
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)
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.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.