Tuesday, January 22, 2008

Database Mirroring on SQL Server 2005

SQL Server 2005 Database Mirroring is a set of technologies that allows you to currently mirror a database on a different instance or server. Database mirroring is different than replication, although it depends on what you are trying to achieve. Mirroring is really only for redundancy, but if you have multiple sites, you can replicate across multiple sites and then have the server at each site mirrored by another database server.

In the case for Comet Computing, we run a number of production SQL Servers, so the setup and investigation for Database Mirroring was a requirement since we need to minimize downtime.

Database replication is relatively straight forward to setup with a few "Snafus'".

The big one that seems to get everyone and is what I call the famous 1418 error.

"The server network address TCP://..com: can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)".

My simple fix for this after fiddling around for 2 hours for the mirroring was actually reading the documentation more thoroughly (the SQL Server Books Online Help).

The biggest issue is security. All the servers involved with the database mirroring should use the same domain account for the SQL Server Service. Otherwise you have to use certificate and this complicates this. After I fixed this issue, mirroring setup was relatively painless.

The best articles ro refer to are the ones in books online.

database mirroring [SQL Server], configuring
database mirroring [SQL Server], security

No comments: