Anything for sysadmins!


How to setup mirroring for SQL databases

A good way of having a standby SQL server is to setup database mirroring. This will at least make sure that if the server goes down, you still have a hot standby. Having a witness server also enables you to do automatic failover. However setting this up using the GUI isn’t well documented. Since I’m not that sure about my Transact-SQL skills, I rather use the GUI.

Start by making a backup of the DB and restore it on the designated mirror server.

Open the SQL Server Management Studio and expand the Databases folder.

Right-click the database you want to mirror and go to Tasks > Mirror

Click configure Security to continue.

Make sure that your firewall allows inbound connections on TCP port 1433 otherwise you won’t be able to find your mirror server. Also make sure that the port set in the mirror security wizard is also allowed in your firewall.

If needed fill out the services accounts screen, otherwise just click next.

If you run into any errors, just check the logs. This error:

This means that the server is unreachable (firewall) or that the account that is trying to connect doesn’t have connect permissions on the endpoint. This will show in the logs as well. All SQL service accounts of the principal, mirror and witness server need this permission!

Happy mirroring!

Be Sociable, Share!

Posted by Mischa Oudhof

Comments (0) Trackbacks (0)

No comments yet.

Leave a comment

No trackbacks yet.