After we succeeded configuring database mirroring using database mirroring wizard. So another option to configuring database mirroring using T-SQL. Here it is step by step configuration using T-SQL.
1. Full recovery model for principal database. How to check recovery model use the following Query.
For change Recovery model in database use the following query.
2. Set Compatibility of Database to level 90 (SQL 2005) or higher. use the following query to set compatibility database level.
3. Create Full & Transaction Log Backup of Principal Database. Disable all backup jobs (Full & Transaction Log Backup of Principal database) before establishing Database Mirroring. Use the following query for backup database Full & Transaction Log.
4. After Backup Database (Full & Transaction Log), copied Backup file to mirror server, make sure you have disabled AdventureWorks2008 backup job in principal server. After copied Backup file, Restore Full & Transaction Log into mirror server. Use the following Query for Restore Backup Full & Transaction Log with NoRecovery Option.
5. Create end point & listener port on Principal server. Here it is Query for creating end point & listener port on principal assume you have SQL Instance named “XMS”
— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 1. Creating End Point & Listener Port on Principal Server
— Run : Principal Server Instance
— =============================================
Use master
IF Exists(Select * From sys.endpoints Where name =’Principal’)
Drop EndPoint Principal
GO
CREATE ENDPOINT [Principal]
AUTHORIZATION [XMS\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT=5042, LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4)
GO
Select * From sys.endpoints Where name =’Principal’
GO
6. Create end point & listener port on Mirror server. Here it is Query for creating end point & listener port on Mirror assume you have SQL Instance named “XMS\Mirror”
— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 2. Creating End Point & Listener Port on Mirror Server
— Run : Mirror Server Instance
— =============================================
Use master
IF Exists(Select * From sys.endpoints Where name=’Mirror’)
Drop EndPoint Principal
GO
CREATE ENDPOINT [Mirror]
AUTHORIZATION [XMS\Administrator]
STATE=STARTED
AS TCP (LISTENER_PORT=5043, LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4)
GO
Select * From sys.endpoints Where name=’Mirror’
GO
7. Establish Mirroring session on Principal server.
— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 3. Establish Mirroring Session
— Run : Principal Server Instance
— =============================================
ALTER DATABASE AdventureWorksDW
SET PARTNER=’TCP://XMS:5042′
GO
8. Create partnership between mirror and principal server.
— =============================================
— Author: Aries Budi Susilo
— Create date: 13 Juli 2012
— Description: 4. Create Partnership
— Run : Mirror Server Instance
— =============================================
ALTER DATABASE AdventureWorksDW
SET PARTNER=’TCP://XMS:5043′
GO
Now we have configure Database Mirroring using wizard and T-SQL Script. Next article will be discuss how to fail over Database Mirroring and test Database Mirroring with inserting data. Failover on database mirroring it is the step to switching role (Principal server become Mirror Server & Mirror server become Principal Server).
Think Big 