Database Mirroring Failover Considerations

Posted: February 22, 2012 in Database Recovery, SQLServerPedia Syndication

 

Database mirroring is a great option for many disaster scenarios. If using a witness server automated failovers can be achieved.  However, just like any other disaster recovery solution database mirroring should be tested.  Before you execute your first test of failing over a database from one server to another you may want to take into consideration a few different items.

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.

About these ads
Comments
  1. Chris Yates says:

    This is great stuff man. Appreciate it.

  2. Nirav Gajjar says:

    Hi, Chris

    You can also look into Linked Servers and mail profiles.

  3. Nirav Gajjar says:

    In Our case we uses linked server in our stored procedure and we have some mail profiles and accounts to send reports automatically.

    as you said, i had configured jobs and user accounts in my mirrored server and tried to test it.
    It runs well but in some stored procedure it gives me error as i traced the stored procedure and get solution some of my sp uses linked server, i had created a linked server and it runs well.

    same as linked server if you have any mail profile worked in principle server and you want to make them live in mirror server you must have to create on mirrored server also.

  4. Chris Shaw says:

    Good input, I think I am going to need to add the Mail Profile in there as well. Thanks for taking the time to add that.

  5. [...] Comments Chris Shaw on Database Mirroring Failover…Nirav Gajjar on Database Mirroring Failover…Chris Shaw on Database Mirroring [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s