SQL Server – Database Mirroring

In the past, I was involved in implementing Log Shipping, Microsoft Clustering, Veritas Clustering, VMWare ESX Virtualisation, and Replication for different SQL Server systems. All of these provide the comfort of higher availability (HA) that a modern business requires.

At the last MVP Summit, my SQL Server colleagues were adamant that Database Mirroring was the best HA solution available … for dummies. Many of these colleagues were consultants who provide their services to small business that simply could not afford to retain a full time database administrator.

For these MVPs, simplicity was the key feature of any high HA solution. Database Mirroring delivered this and has become a facility that they and their customers depend on.

Another key feature is that Database Mirroring is supported in SQL Server Standard Edition and this dramatically reduces that cost of an HA solution. Many HA solutions require expensive enterprise software.

I found a rather good article on Database Mirroring from GRE Solutions Limited on their website (http://www.gre-sqlserver-solutions.com/mirroring.html)

An important note is that while Database Mirroring keeps both servers in synch, the databases on the second server are constantly restoring from the primary server and are not available for use while mirroring is enabled.

Last week, I finally implemented Database Mirroring, on a small database server that had both a primary and standby server running SQL Server standard edition.

While it was easy enough to do, it was not as easy as the documentation implied. I got the following message repeatedly:

Msg 1418, Level 16, State 1, Line 1
The server network address “TCP://myserver.domain.domain.domain.com:5022” can not be reached or does not exist. Check the network address name and reissue the command.

Microsoft describe the problem in an MSDN blog (http://blogs.msdn.com/b/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx)

When I could not get it working through the wizards, I switched to the Transact-SQL to run checks to find where things had gone awry and enable mirroring there.

The following MSDN articles were immensely helpful (http://msdn.microsoft.com/en-us/library/ms179306.aspx & http://msdn.microsoft.com/en-us/library/ms175876.aspx).

Now that it is working, I hope to roll it out to other SQL Servers that are neither clustered nor virtualised in my organisation but have a standby server.

Footnote – Database Snapshot is a feature that can be implemented with Database Mirroring when using SQL Server Enterprise Edition. While Database Mirroring does not allow you to access the data on the standby server, Database Snapshot allows one to create a virtual copy of the database at a particular time that is available for use on the standby server.

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: