SQL 2005 Mirroring – Automatic Failover
This post is a follow on from a previous post ‘Complete Guide to SQL 2005 mirroring using the GUI‘
Now we know when Mirroring in the ‘High safety with automatic failover’ mode it will automatic failover, which works superbly, but a few questions for you, are you using SQL authentication for you databases? have you tried the failover and then tried to call your app through the failover server now as the principle server? Do you manually change your .config or use a script to do a find and replace if your web application is using a connection string?
If you answered
Yes to SQL authentication
No to trying the failover
Yes to manually change the connection string
this post if for you read on…
Lets start with the connection string, now hopefully your application is using .NET 2.0 or above, if it is you will be very happy to know you don’t have to script anything for when a server dies and your mirrored database takes over, for those using 1.1 or below UPGRADE, but if you cant well I under stand, you might want to read another post about a find and replace tool I found that will make your scripting life much easier for the failover it can be found here, anyway back to the .NET 2.0 users, if you are using a mirror then you will be very happy Microsoft have included some additional functionality within the connection string, where you can specify a failover partner, this will allow you to enter the failover’s server name or IP and once the principle mirror has failed you need not worry if you have the failover partner string within the connection string, it will detect this and failover immediately.
Here is an example:
Server=MyPrincipleDatabaseServer; Failover Partner=MyFailoverDatabaseServer;; Database=AdventureWorks;
it works perfectly, try it, you will be amazed.
find more info on the above here
Ok so manually change the connection string….. Check
now moving along to SQL authentication
If you use SQL authentication and lets say you take a backup of database and then restore it to another SQL server, what one thing that you will always have to do….?
give up… You will have to remove the user and then re-add the user to the database, why because the SID (security Identifier) is different on each SQL server, the same applies to mirroring, when you failover even though you are using the same user and password for the SQL authentication, the app / database will fail to work because once again just like a restore you will have to delete the user and then re-create it, one massive pain in the but, especially if you want automatic failover and then it does failover in the middle of the night, but yet all your databases still fail, now wouldn’t that just be a kick in the face.
However we have an easy fix for that, all you need to do is make the failover database SID the same as the principles SID for that user, or all users that you are using for the mirrored databases.
So first thing is getting the SID of the principle SQL server.
use the below script: (use master for the query)
SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else ''< /span> end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
once run you should see something like (you would probably have a heck of a lot more users)
Ok so find the user you want to replicate the SID on your Failover, for this example I am using test_user
The below is what I have pulled out directly from the above query for my Test_user
create login [test_user] with password = 0×01004981172537214b13a072a6674d0316673b48cbdadd184d3b hashed, sid = 0×6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english
So now I want to copy user above and take its SID and re-create the user on my fail over so I would now go to the failover database and then run
Use Master create login [test_user] with password = 'PasswordGoesHere', sid = 0x6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english
REMEMBER: to remove the hashed password replace it with a clear text password, I also advise to keep the password expiration off, other wise you will find yourself doing this task again and again, or worse yet forget about it and then when the SQL server fails, so does the mirror 
Now that that is done time for that testing you answered No to, so lets start the test:
(I do suggest you try this on a dev environment before you do this on production)
go on to the primary and then tasks > Mirror > click on Failover
your failover I should now failover quickly and smoothly, without any manual intervention.
Now just one last thing that might be helpful, if you are running a mirror and your failover server is not the same spec as your Primary SQL server, or you just don’t want the databases to run on the failover server until the next failover, then you can run a TSQL script to make them go back to the primary database server:
Use master
ALTER DATABASE DatabaseNameHere SET PARTNER FAILOVER go
Repeat this for each database, if you have hundreds of databases, do a SP_database and then copy to excel and then save to CSV and the play with it that way.
)
