I had interesting case last week - I had to setup SQL 2005 database mirror. First it sounded as piece of cake - I had to follow steps and recommendations as described in SQL Server Books Online (and here). My goal was to setup DB mirror of type High safety with automatic failover (synchronous).
I had to prepare SQL Instances as:
- Create a user (called SqlService in my case) with same password on all machines that will participate in the mirror. Give it enough rights to it. (I put it in Administrators group and denied local login. Later I set more granular security to it)
- Setup instances to run in this user context
- Set trace flag 1400 as startup parameter to SQL instance
Then I followed How to: Prepare a Mirror Database for Mirroring (Transact-SQL) and when it came to setup DB Mirror it was created... But although I had witness server I was unable to test automatic failover. More precisely I set a connection string of type (
"Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial Catalog=myDataBase;Integrated Security=True;"
) to a simple web application but when I stopped the instance that hold Principal database the mirror database didn't became principal as expected.
Note: This test was done on SQL 2005 Standard Edition with SP2 on Windows 2003 Standard with the latest updates.
I decided to test this on SQL 2005 Standard (with no SP2) in Win2003. And it worked. I applied SP2 then and I as able to setup DB mirroring again and everything worked as expected - even automatic failover.
Bottom line: Somehow SQL 2005 SP2 break DB mirroring if installed right after SQL Server installation. This was my experience and I if someone has another experience with this I would be grateful if we can discuss here
Useful links:
Hope this helps!