Mirroring SQL Server 2005
Mirroring SQL Server 2005 SP1/SP2 can be a pain if you don't follow a few simple steps. It's the small stuff that seems to get overlooked during the process. I kept getting Alter errors and a few other fun errors during setup.
- The server network address can not be reached
- Alter failed
- Error 1418
- Insufficient Log Data
- Error 1478
I hope to provide the cleanest step-by-step for setting up mirroring with a witness. Please feel free to edit anything I missed.
We are going to do this entire thing using the GUI. No TSQL necessary.
(I assume you know some things about backup and restore.)
- Make sure all your Servers are running under the same account under Services
- Go to your primary server's Microsoft SQL Management Studio.
- Right click the database you wish to mirror and click backup (This is a two part step. Pay close attention).
- Do a FULL backup of your database to any place. Wait for this to finish.
- Do a Transaction log backup. (I tried it without this and was part of my failure)>
- Go to your mirror server's Microsoft SQL Management Studio.
- The database should not exist here yet. Right click Databases and click Restore Database...
- Type the database name in the "To Database" text box.
- Click From Device and get add your main bak file.
- Check the Restore box and then click Options at the top left.
- On the Recovery state set RESTORE WITH NORECOVERY (The second radio button).
- Once that is done. Repeat the Recovery steps for the transaction file backup. (It also needs NORECOVERY on options).
- Go back to the Primary Server's Management Studio and right click the database to be mirrored.
- Select Tasks and then Mirroring.
- Configure Security. Make sure Encrypt data is off on all of the server connections (Another issue I had during setup. My witness server had "Encrypt data sent through this endpoint" set to on and it broke. See the note below on how to remove Encrypt data if it is prepopulated and you can't uncheck it.
- Leave the Security boxes blank.
- Click Start Mirroring and watch your mirror with the mirroring monitor (Screenshot below).
- If you plan on using SQL Express for the Witness (A great choice), make sure you go into it's SQL Server Configuration Manager and enable TCP/IP under Protocols.
- To clear an endpoint's Encryption flag, write down or remember the EndPoint name where the box is greyed out. Cancel out of the Security wizard. Connect to the witness server and execute the following command. DROP ENDPOINT <Endpoint Name>
- If you are getting an error you cannot figure out (eg 1418) then remember to check the Server's Application Event Log as it may provide a clue.
- Please edit this WIKI if you had any issues.
- Getting Sql 2005 Mirroring EndPoints:
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id
- Connection String Change (Cdyne uses trusted authentication. That way there is no user/password in the code):
Data Source=MyPrimary.domain.net;Failover Partner=MyFailover.domain.net;Integrated Security=SSPI;Initial Catalog=MyDB;Connect Timeout=20
Setup Mirroring with Certificate =
In case you get some error like 1418, 1443, 1478 ..... and you try everythings already.... you may need an alterative : Certificate
Hope this help everyone: http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx