Sunday, April 1, 2007

SQL Server 2005 Database Mirroring

I have been working with database mirroring feature in SQL Server 2K5 and these are some of interesting points I have noted during setting up the process.

1. In SQL Server 2005 SP1 and later versions, database mirroring is fully supported and is made available for general production use. To use database mirroring in a production environment, upgrade to SQL Server 2005 Service Pack 1 (SP1).


2. An error occurred while starting mirroringDatabase Mirroring Transport is disabled in the endpoint configuration.(Microsoft SQL Server, Error: 1486)

Solution: Run DBCC TRACEON (1400)

3. The mirror database, "XXX", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Solution: After the full backup on the principal, you need to backup the Transaction log on the principal server. Restore the full backup on mirror in ‘NORECOVERY MODE’ and then restore the Transaction log in ‘NORECOVERY MODE’

4. Can we use database mirroring for the purpose of transferring data from production to test or development server?

In mirroring, if the first server should become unavailable, the system will failover to the mirrored database and SQL Server, helping to ensure high availability. I don't think you want to failover to test or development box.However, you can use Database Mirroring and create a Database Snapshot against the mirror. This Database Snapshot can be periodically dropped and recreated in order to give access to more up to date information. You can use this snapshot for reports or testing.

5. Can we use Database mirroring with Log Shipping?

This MS article explains it clearly