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  

1 comment :

  1. Once we, all of the be aware of Can imagine is mostly a noted supplier that the majority most people keep company with rolex replica outfit. Still, Can imagine handbags experience prepared most of the tactic to all the cutting edge along with the building of eye-catching and additionally completely unique creations. All the Can imagine gucci replica purse becomes needed where they experience between the model exclusive by means of various sensible different shades, fashions, and additionally creations about handbags of choice. Can imagine is mostly a trendy beautiful list, helping to make rolex replica uk just about every single lover, and additionally young people wish to utilize, for that reason no one will come to be amazement to work out wives in every one matures making use of and additionally toting can imagine handbags. Can imagine Handbags has completely unique and additionally eye-catching handbags which usually appeal to all of the wives. These days there are different Can imagine handbags, which you'll find all of the burberry replica particularly saleable and additionally reached you can find. However these are all the Can imagine barrel or clip purse, i going all of the can imagine owners are conscious. All the equipment that include gucci replica croc hard copy, jacquard, and additionally vinyl fabric along with the Can imagine "G" symbol really are endless between the Can imagine barrel or clip handbags.

    ReplyDelete