What you need to know about Database Mirroring
In recent months I have set up database mirroring for a number of clients. Why you may ask?
It is a great option for disaster recovery and even gives the organization additional options when it comes to maintenance times when an environment has limited maintenance windows. Mirroring is most likely my favorite new feature to SQL Server in over 10 years.
Just like all other technologies, to use mirroring to its fullest ability there are a few considerations that should be reviewed. None of these that are show stoppers, however if not done right, a mirror may failover as it should yet the organization may still have issues with connecting or applications working. I wrote a couple blog posts on this in the past, however I find that I often refer to these and they are not consolidated. This post is intended to be a consolidated list.
User Accounts
Problem – When a new SQL Server login is created on a server, a row is added to a system table in the master database. The Unique Identifier on that row of data is called the SID. The SID is unique based on the account and the server on which the login is created.
When a login is then added to a database as a user, and the user is granted permissions to work with data inside a database, those permissions are added to the individual databases. Logins are related to the server while users are related to the database. The relationship between the login and the user are based on the SID.
When a database mirror becomes the Principal on a different server, the users that are in that database remain due to the user account information being stored in the user database. The relationship from the User in the database to the Login on the server is broken. This is referred to as an orphaned user.
Solution- There are a couple ways of correcting orphaned user. One method is to correct an orphaned user using the sp_change_users_login stored procedure. I don’t use this method because it requires either additional activity on your side by either running that procedure manually or via SQL Server Agent job and, depending on how you have it configured, may take some time before those accounts can log on to the database.
The solution I choose to use is by creating all the user accounts on the mirror with the same SID as they have on the Principal. By having the same Login SID on both the mirror and the Principal, the accounts relationship remains. To create a new login by declaring the SID use the Create Login statement.
SQL Server Agent Jobs
Problem – Many servers have jobs that reach into databases to manipulate data, or have automated backups that are scheduled via the SQL Server Agent Service. On mirrored versions of the database not marked as being online, these jobs will fail if that is not taken into consideration. Some may consider just adding the job to the server and letting it run and fail and when the mirror becomes the Principal, the jobs will already be scheduled and ready when the next execution time comes around. (I am not a big fan of this, if for nothing else because I monitor for failed jobs and a false positive will eventually be ignored so when the job is needed to run and fails the notification may be missed.)
Solution – Kevin Cox and Glenn Berry, two SQL Server Professionals that I respect a lot posted a solution on the SQL Cat site that solves this issue. In short… Add a category of jobs to your server that you can assign all your jobs to. Once your database status changes the jobs can be enabled.
Indexing
Problem – Running a reindex on a database that is mirrored can create a number of transactions that need to be propagated over to the mirror. On servers that are located in different physical environments, an additional load can be placed on the bandwidth, and the server that is the mirrored.
Solution – It is important to know the impact of database mirroring on your database and the servers that are hosting those databases. In the situation that I am thinking about the mirror server did not have the same hardware as the Principal server. This meant that indexing was a bigger load on the mirror than it was the Principal.
One of the solution options is to make sure that only the indexes which need to be reindexed are reindexed. This can be done by checking the fragmentation before issuing the index statements.
Linked Servers
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.
External Dependencies
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 –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.
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.