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.
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.
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.