数据库镜像2008风格

数据库镜像是在SQL Server 2005中引入的。当人们读到新版本时，这是一个让人兴奋的主要特性。这是一个跨两个服务器同步数据库的功能，提供自动故障转移，不需要Windows集群服务，也不需要附带昂贵的硬件和软件。是的，数据库级的“穷人”故障转移集群。此功能在SQL Server 2008中得到了增强。不幸的是，由于客户在育空测试程序后期发现的问题，当2005年11月7日RTM版本最终发布时，该特性被认为是“不支持的”。当时测试数据库镜像的唯一方法是在SQL Server启动时设置跟踪标志(1400)，但微软明确表示它不支持生产系统。我们在培训中使用了Trace Flag，以便在产品发布的当天及时交付。然而，在我们正式设置数据库镜像会话并“支持”它之前，我们必须等待Service Pack 1。除了Principal和Mirror(称为Witness)之外，还有第三个SQL Server实例提供了自动故障转移特性。 When the Principal was having trouble, the Witness would initiate a Failover and the Mirror would become the new Principal. The old Principal would then become the new Mirror when it came back up again and resynchronized. There was even a Transparent Client Redirection feature using the SQL Native Client and ADO.NET 2.0. The connection string would allow the entry of two servers. If the Primary server was not available, the client would try the Secondary server and not skip a beat. This operating mode is named “High Safety with Automatic Failover” or “synchronous”. The key characteristic of this mode is that the synchronous processing would require distributed transactions between Principal and Mirror which guarantees no loss of data during a failover. The downside of this strategy is the potential performance bottlenecks as the Principal is waiting on the Mirror before the databases can commit in unison (we used to refer to this as the ”two phase commit”). As an alternative, Microsoft also gives us “High Performance” or “asynchronous” mode. This removes the performance overhead of distributed transactions but exposes the potential loss of the latest transactions that committed on the Principal but never made it to the Mirror before a failover. This mode also gives up the automatic failover feature but manual failover is still available. The one disappointing aspect of the Database Mirroring architecture was that the Mirror was not usable as a database until failover. So it’s great for data protection but not for load-balancing. True, you could create a database snapshot of the Mirror database but that would be a point-in-time read-only copy. If you are after load-balancing you might look at Replication or Log Shipping as alternative solutions but you would be giving up the automatic failover. I have tested out Database Mirroring in SQL Server 2008, and on the surface it works much like SQL Server 2005 SP1. The user interface is almost identical. The pros and cons are still the same. However, “under the hood” SQL Server works much more efficiently to try and negate those potential performance issues. There is now built-in compression of stream data, write-ahead processing for log records being sent to the Mirror, improved use of log send buffers on the Principal, and Page read-ahead processing during the undo phase of a failover. There is also an automatic recovery feature where corrupted data pages are repaired by the system by requesting a fresh copy from the partner whenever a data page becomes unreadable. All this adds up to a more robust and usable Database Mirroring feature. I usually advise caution with major new features as they take time to reach maturity (i.e. fix bugs/issues) and Database Mirroring was definitely in that category, but now with Katmai, it may be the time to re-evaluate. Of course, some heavy duty integration testing and performance benchmarks will give some answers. After all, it’s included in our SQL Server licensing so why not give it a whirl? Cheers Brian