In part 9 of our webcast series titled: Implementing Database Mirroring, we covered the steps from setup to failover to monitoring. There were lots of great questions and I think we could easily go back and do a couple more hours on database mirroring, failover combinations – including manual failover and client application questions. Having said that, there were a few interesting scenarios that came up that I thought I’d add a bit more details about here. For simplicity I created sections…
Where to go for more information on Database Mirroring and SQL Server SP1
- Database Mirroring in SQL Server 2005, TechNet whitepaper by Ron Talmage
- Database Mirroring Best Practices and Performance Considerations, TechNet whitepaper by Sanjay Mishra
- TechNet Webcast: How to Increase Availability Using Database Mirroring in SQL Server 2005 (Level 200), Mark Wistrom’s first Database Mirroring webcast from November 2005
- Implementing Database Mirroring, Part 1 of 2 (Level 200), Part 8, Mark’s Database Mirroring webcast from our TechNet series, April 28, 2006
- Implementing Database Mirroring, Part 2 of 2 (Level 200), Part 9, my webcast from our TechNet series, May 5, 2006
- SQL Server 2005 Service Pack 1
- SQL Server 2005 Books Online Refresh, April 2006 – NOT included in SP1 Update
Database Mirroring between Editions
Database Mirroring is supported in both the Standard Edition (SE) and the Enterprise Engine (EE) Edition(s): Enterprise, Enterprise Eval and Developer. In the EE Editions all configurations (synchronous and asynchronous) are supported: High Availability (sync), High Protection (sync) and High Performance (async). In the SE, only the synchronous forms of Database Mirroring are supported: High Availability and High Protection. One thing that is true however, (and I learned this as well – durin the webcast in Part 9 – thanks to the question submitted and Mark being present…thanks Mark!), is that even while synchronous mirroring is supported in both SE and EE, you can only create a mirroring partnership between servers of the same edition.
Database Mirroring between Platforms
Database Mirroring is supported in both the Standard Edition (SE) and the Enterprise Engine (EE) Edition(s): Enterprise, Enterprise Eval and Developer. In the EE Editions all configurations are supported: High Availability, High Protection and High Performance. In the SE, only the synchronous forms of Database Mirroring are supported: High Availability and High Protection but not the asynchronous High Performance configuration. One thing that is true however, (and I learned this as well in Part 9 – thanks Mark!), is that even while synchronous mirroring is supported in both SE and EE, you can only create a mirroring partnership between servers of the same edition.
Combining Database Mirroring with Other Technologies
The Books Online has a section targeting exactly this discussion. Review this section in the SQL Server 2005 Books Online (April Update): Database Mirroring and Other Features and Components. Additionally, I’ve provided a few comments for you to review as well as links to some of the specific BOL topics that exist on these combinations.
Database Mirroring with Failover Clustering
These two technologies CAN be combined but there are multiple things with which you should be aware. First, a failover of a cluster is SLOWER than a failover of a Mirror pair… as a result, it is likely that your secondary server will come online as the new principal in the time that it takes your principal (which is on a cluster) to recover. In a lot of cases, this is good because this keeps you online longer and results in less downtime but it may also be undesireable when your primary is now running at your alternate operations site – which is unstaffed. So, in some cases you may want to prevent automatic failover and instead only use the secondary mirror when you absolutely have to (i.e. NOT just when the cluster fails). If this is the case then you might prefer running with the High Protection configuration of Database Mirroring instead of the High Availability configuration.
This will allow you to manually failover when desired.
As another option – you can increase the timeout for Database Mirroring failover to 90 seconds. If the cluster comes back online within 90 seconds then the automatic detection/failover of the High Availabilty configuration will not occur unless the cluster does not come back online (as the principal) within x seconds. You can configure the Database Mirroring Failover timeout by using ALTER DATABASE.
ALTER DATABASE dbname SET PARTNER TIMEOUT x
Please note, this is only one timeout of many. There are many different types of timeouts in the system that can cause a failover. However, a hard error code generally starts the failure procedure sooner. Mark pointed this out in his failure detection slides in our TechNet webcast series, Part 8.
Review this section in the SQL Server 2005 Books Online (April Update): Database Mirroring and Failover Clustering.
Database Mirroring with Replication
These two technologies CAN be combined together but not all configurations are supported and where supported, there are specific setup requirements. From the BOL: Replication supports mirroring the publication database for merge replication and for transactional replication with read-only Subscribers or queued updating Subscribers. Immediate updating Subscribers, Oracle Publishers, Publishers in a peer-to-peer topology, and republishing are not supported.Review this section in the SQL Server 2005 Books Online (April Update): Replication and Database Mirroring
Database Mirroring with Log Shipping
These two technologies CAN be combined together but it will require a bit of manual configuration to continue log shipping when a mirror becomes the new principal.
Review this section in the SQL Server 2005 Books Online (April Update): Database Mirroring and Log Shipping.
And – there are others in the BOL. Please reference the sections listed above for more details.
And – with that – we’re caught up with our resources and references for this series. Part 11 – the LAST one – is this Friday, May 19. I look forward to your being there LIVE. Register here and come ready with your questions, this one is going to be VERY focused on best practices, ideas/architectures and your questions. Those of you that are there LIVE will help to direct the session.