Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

Set Up SQL Server Mirroring T-SQL Script

T-SQL Script to create a SQL Mirroring:-

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

/*FAILOVER */

ALTER DATABASE <database_name> SET PARTNER FAILOVER
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...