Summary:
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.
¶ Error List
- The server network address can not be reached
- Alter failed
- Error 1418
- Insufficient Log Data
- Error 1478
¶ Step-by-Step setup
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).
¶ Notes
- 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 - 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
¶ Screen Shots
¶ 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