Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

How to setup Database Mirroring in SQL Server 2005 using T-SQL

Our disaster recovery and database availability solution involves clustering and Database Mirroring in SQL Server 2005.  At our primary site and our disaster recovery site, we have a 4 node cluster with several SQL Server 2005 instances on it.  So that's 8 nodes and 2 clusters at both sites.  The cluster is Active/Active/Active/Active at the primary site.

As we add databases to any of the instances on the cluster at the primary site, I have to setup Database Mirroring on them so that our DR site is ready in case of a real DR scenario or if we decide to move production to the DR site temporarily for whatever reason. 

There is a Database Mirroring wizard in SQL Server Management Studio that you can use to set it up, however I prefer to use T-SQL to do it.  Here is how we do it for asynchronous Database Mirroring. 

NOTE: We use asynchronous mode for performance reasons.  We want to avoid a two-phase commit.

Create initial database on mirror server

The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server.  You then must restore these to the mirror server using the WITH NORECOVERY option of the RESTORE command. 

Create endpoints on both servers

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Set partner and setup job on mirror server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute

Set partner, set asynchronous mode, and setup job on principal server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'

ALTER DATABASE DatabaseName SET SAFETY OFF

EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute 

Print | posted on Tuesday, February 13, 2007 12:40 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: How to setup Database Mirroring in SQL Server 2005 using T-SQL

does that mean that if i want to query from the database mirror, it's not possible? since it's log restore is set to norecovery?

thanks...
3/23/2007 9:36 AM | jen
Gravatar

# re: How to setup Database Mirroring in SQL Server 2005 using T-SQL

jen, that is correct. What you'd want to setup is a snapshot database.
3/26/2007 9:49 AM | Tara
Gravatar

# re: How to setup Database Mirroring in SQL Server 2005 using T-SQL

thanks Tara, I'm setting it up now to prove this point to a persistent dev

Cheers!
4/11/2007 5:55 AM | jen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET