Wednesday, 1 August 2012

Set Up SQL Server Mirroring T-SQL Script

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.  
 
1:  /*Create endpoints on both servers*/ 
2:  CREATE ENDPOINT EndPointName  
3:  STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)  
4:  FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4) 
 
5:  /*Set partner and setup job on mirror server*/
6:  ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'  
7:  EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
  
8:  /*Set partner, set asynchronous mode, and setup job on principal server*/  
9:  ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'  
10:  ALTER DATABASE DatabaseName SET SAFETY OFF  
11:  EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
 
12:  /*FAILOVER */  
13:  ALTER DATABASE <database_name> SET PARTNER FAILOVER  
14:  ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS  

No comments :

Post a Comment