About a week or so ago I had posted a few thoughts on database mirroring in SQL Server and some of the considerations that should be reviewed before relying on it to be your failover solution. I love the features in mirroring and as I write this I am looking at how I can start using the new features in SQL Server 2012. However, as much as I would like to upgrade today, I need to run a full round of testing with the developers I work with to make sure everyone understands the behavior. The key part to any backup or HA/DR plan all lies in the testing and the planning that you do. However, if you do a bunch of planning without any testing, I am afraid you are likely to run into some issues. This is why I have created this post. There have been a few things that I have seen which are often missed with database mirroring.
Problem – Over the years a bit of code has been developed to support Linked Servers. If you have a server named Server A and one of the stored procedures in one of the databases on that server link to a database on another server (Server B), you may run into an issue during a failover. Linked server information is a server level configuration, not database level, so the information about the linked server is not kept in any of the individual user databases.
Solution – This may sound like an over simple solution, but it is what it is. Make sure that you have all your user database external dependencies configured on both the principal and the mirror. The complexity increases when you have links referring to the mirrored solution.
Problem – In like fashion to the linked servers, there are many external SQL Server items that may be relying on the name of the server to connect. When the mirror takes over from the principal, the server that was the principal is no longer hosting the database in an online mode. Even if there is a user database on the same server as the database that was failed over. These links/connection do not just change. This could include many items such as SSIS packages, SQL Mail and cross database queries.
Solution – The deeper we look at the considerations the easier I find it is to recommend SQL Server 2012. There are a number of things that you can do when it comes to making adjustments to the external dependencies you can create/configure these items on both servers and in turn enable or disable them depending on where a database is live at the time. Many of these issues are being addressed SQL Server 2012 with Contained Databases, Partially Contained Databases and Always On Availability Groups.
No matter the solution, that you choose to use the key to a successful DR solution is testing. I am not sure I can think of any critical process outside the technology that is not tested and then re-tested. This is why the military spends countless hours training and the local fire departments do disaster drills. The time to learn that your disaster plan is week in an area is not when the disaster happens.
I am really curious as to what you considerations you may have seen that I have not noted. Send me an email and I can add yours to this list.