SQL Server:Steps to Configure Database Mirroring by T-SQL Script.

Enable Mirroring
Check Endpoints & mirroring logins account exist by running the below scripts ‘MIR1_Script to check endpoints and logins.sql’

--Scripts to check existence of endpoints and port associated with database
--mirroring in the principal and mirror server.

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
       t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
       e.connection_auth_desc
FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON     e.endpoint_id = t.endpoint_id

--If endpoint not exists, then refer ‘Mirroring Plan Server level Configuration .doc’
--to create an endpoint in the principal and mirror server.

--Scripts to check the existence of the logins required for mirroring in the principal
-- and mirror server.
/****** Object:  Login [[TEST\Kumar-PrincipalServername] ]    Script Date: 01/25/2013 10:17:00 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'[TEST\Kumar-PrincipalServername] ')
Print 'Login [TEST\Kumar-PrincipalServername]  Exists'
GO

--If logins not exists, then refer ‘Mirroring Plan Server level Configuration .doc’
-- to create the logins in the mirror server.


/****** Object:  Login [[TEST\Kumar-MirrorServername] ]    Script Date: 01/25/2013 10:30:33 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'[TEST\Kumar-MirrorServername] ')
Print 'Login [TEST\Kumar-MirrorServername]  Exists'
GO

--If logins not exists, then refer ‘Mirroring Plan Server level Configuration .doc’
--to create the login in the principal server.


If endpoints and logins not exist, follow the below ’Mirroring Plan Server level Configuration.doc’ to create those endpoints and login accounts

’Mirroring Plan Server level Configuration.doc
Endpoints and Login account creation for Database mirroring configuration
Endpoints:
Ensure the login [TEST\Kumar-WitnessServername] exists in the principal and mirror server.
If not, Run the below script to create that login.
USE [master]
GO
CREATE LOGIN [TEST\Kumar-Witness_Servername] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Create ENDPOINTS for mirroring on the principal server. Execute the following statement on the principal server, Principalserver,14331.

/****** Object:  Endpoint [Mirroring]    Script Date: 01/24/2013 14:01:35 ******/
CREATE ENDPOINT [Mirroring]
          AUTHORIZATION  [TEST\Kumar-Witness_Servername]
          STATE=STARTED
          AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
          FOR DATA_MIRRORING (ROLE = PARTNER,
 AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)

The next step is to create ENDPOINTS for mirroring on the mirrored server. Execute the following statement on the mirrored server, Mirror_Server_Name,14331.

/****** Object:  Endpoint [Mirroring]    Script Date: 01/24/2013 14:04:47 ******/
CREATE ENDPOINT [Mirroring]
          AUTHORIZATION  [TEST\Kumar-Witness_Servername]
          STATE=STARTED
          AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
          FOR DATA_MIRRORING (ROLE = ALL, 
AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO


Login accounts:
Create the login (service account) in the principal server and mirror server then grant connect permission to the account by the below script

/****** Object:  Login [TEST\Kumar-PrincipalServername]    Script Date: 01/25/2013 10:17:00 ******/
CREATE LOGIN [TEST\Kumar-PrincipalServername]  FROM
 WINDOWS;
GO
--Grant connect permissions on endpoint to login account
--of partners
GRANT CONNECT ON ENDPOINT::Mirroring TO [TEST\Kumar-PrincipalServername];
GO

Create the login (service account) in the mirror server and principal server then grant connect permission to the account by the below script


/****** Object:  Login [[TEST\Kumar-MirrorServername] ]    Script Date: 01/25/2013 10:30:33 ******/
CREATE LOGIN  [TEST\Kumar-MirrorServername]  FROM 
WINDOWS
--Grant connect permissions on endpoint to login account
--of partners
GRANT CONNECT ON ENDPOINT::Mirroring TO [TEST\Kumar-MirrorServername] ;
GO


The below script is to be implemented in the automatic failover database mirroring session.

The next step is to create ENDPOINTS for mirroring on the witness server. Execute the following statement on the witness server, Witness_Server_Name,14331.

/****** Object:  Endpoint [Mirroring]    Script Date: 01/24/2013 14:04:47 ******/
CREATE ENDPOINT [Mirroring]
          AUTHORIZATION  [TEST\Kumar-Witness_Servername]
          STATE=STARTED
          AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
          FOR DATA_MIRRORING (ROLE = WITNESS, 
AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

Witness setup section:

--Scripts to check the existence of the logins required for mirroring in the principal
-- and mirror server.
/****** Object:  Login [[TEST\Kumar-PrincipalServername] ]    Script Date: 01/25/2013 10:17:00 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = TEST\witness-ServerName’)
Print 'Login TEST\witness-ServerName Exists'
GO

--If logins not exists, then refer the below steps to create the logins in the mirror server.

Login Creation:

On each of the partner server instances, create a login for the witness server instance:
--Create a login for the witness server instance,
--which is running as SOMEDOMAIN\witnessuser:

USE master ;
GO
CREATE LOGIN  [TEST\witness-ServerName]  FROM WINDOWS ;
GO
--Grant connect permissions on endpoint to login account
--of partners
GRANT CONNECT ON ENDPOINT::Mirroring TO [TEST\witness-ServerName] ;
GO


Create the principal server login (service account) in the witness server then grant connect permission to the account by the below script

/****** Object:  Login [[TEST\Kumar-PrincipalServername] ]    Script Date: 01/25/2013 10:17:00 ******/
CREATE LOGIN [TEST\Kumar-PrincipalServername]  FROM 
WINDOWS;
GO
--Grant connect permissions on endpoint to login account
--of partners
GRANT CONNECT ON ENDPOINT::Mirroring TO [TEST\Kumar-PrincipalServername];
GO

Create the mirror server login (service account) in the witness server then grant connect permission to the account by the below script


/****** Object:  Login [[TEST\Kumar-MirrorServername] ]    Script Date: 01/25/2013 10:30:33 ******/
CREATE LOGIN  [TEST\Kumar-MirrorServername ] FROM
 WINDOWS
--Grant connect permissions on endpoint to login account
--of partners
GRANT CONNECT ON ENDPOINT::Mirroring TO [TEST\Kumar-MirrorServername ];
GO

Alerts for Database Mirroring
Check for alerts existence else create the alerts needed for database mirroring in both principal and mirror server.

Database Mirroring Monitor Job
If mirroring is configured through Tsql scripts, then the Job “Database Mirroring Monitor Job” will not be created by default.

We need to create the job in the principal and mirror server by running the below scripts.


if not exists(select * from msdb..sysjobs where name ='Database Mirroring Monitor Job')
EXEC sp_dbmmonitoraddmonitoring 1;
else
PRINT'Job "Database Mirroring Monitor"  is already configured'


Enabling Mirroring by the following Steps:

Setting the recovery model to FULL
·         Run  the below script ‘MIR1.0_Script to check recovery model and changing to FULL.sql’ on the principal server.

if not exists(SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'DatabaseName' and recovery_model_desc='FULL')
BEGIN
USE master ;
ALTER DATABASE DatabaseName SET RECOVERY FULL;
END

Backup the database on the principal server
·         Run the below script ‘MIR2_Backup Databases.sql’ on the principal server.

USE master
GO
BACKUP DATABASE [DatabaseName] TO  DISK = N'Y:\DatabaseName_Full_TSQL.bak'
 WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Full Database Backup', SKIP,
  NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [DatabaseName] TO  DISK = N'Y:\DatabaseName_TLog_TSQL.trn'
 WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Transaction Log  Backup',
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


Restore the database from the principal server backups on the target server
·         Run  the below script ‘MIR3_Restore Database.sql’ on the mirror server.

RESTORE DATABASE [DatabaseName] FROM  DISK = N'X:\DatabaseName\DatabaseName
_Full_TSQL.bak'
WITH  FILE = 1,
MOVE N'DatabaseName_log' TO N'H:\Microsoft SQL Server\MSSQL.INST01\Data\DatabaseName_1.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DatabaseName] FROM  DISK = N'X:\DatabaseName\DatabaseName
_TLog_TSQL.trn'
WITH  FILE = 1,
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Make the initial principal server instance.
·         Run the below script ‘ MIR4_Set database principal on mirror server.sql’ on the mirror server.

ALTER DATABASE DatabaseName
SET PARTNER = 'TCP://PRINCIPAL_SERVER_NAME.PROD.LTSBAFD.NET:5022'

Set mirror on principal server.
·         Run the below script ‘MIR5_Set mirror on principal server.sql’ on the principal server

ALTER DATABASE DatabaseName
SET PARTNER = 'TCP://MIRROR_SERVER_NAME.prod.ltsbafd.net:5022'

Witness setup

Check the existence of logins and endpoints required for witness server by executing scripts under Witness setup section in  ’Mirroring Plan Server level Configuration’, if it is not available then create it by following the ‘login creation’ steps in the same section.

 Set witness on principal server
·         Run the below script ‘ MIR6_Set Witness on principal server.sql’ on the principal server

ALTER DATABASE DatabaseName
SET WITNESS = 'TCP://WITNESS_SERVER_NAME.prod.ltsbafd.net:5022'

To manually fail over and fail back a database mirroring session
·         Run the below script ‘MIR7_FailOver and Failback a database mirroring session.sql’

/*************To manually fail over a database mirroring session
1.    Connect to the principal server.
2.    Issue the following statement on the principal server: ***********/

USE master;
GO
ALTER DATABASE DatabaseName
SET PARTNER FAILOVER

/*************To manually fail back the database mirroring session
1.    Connect to the new principal server (i.e mirror server).
If you want to failback,Then execute the above query in the new principal server***********/


Set up of the mirroring thresholds
    
·         Run the below scripts ‘Scripts to set thresholds for Database Mirroring.sql ‘ on the principal and mirror server

/*

1 Oldest unsent transaction
 Specifies the number of minutes worth of transactions that can accumulate in the send queue
 before a warning is generated on the principal server instance. This warning helps measure the
 potential for data loss in terms of time, and it is particularly relevant for high-performance mode.
 However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the
 partners become disconnected.

2 Unsent log
 Specifies how many kilobytes (KB) of unsent log generate a warning on the principal server instance. This warning
 helps measure the potential for data loss in terms of KB, and it is particularly relevant for high-performance mode.
 However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners
 become disconnected.

3 Unrestored log
 Specifies how many KB of unrestored log generate a warning on the mirror server instance. This warning helps measure
 failover time. Failover time consists mainly of the time that the former mirror server requires to roll forward any log
 remaining in its redo queue, plus a short additional time.

4 Mirror commit overhead
 Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated
 on the principal server. This delay is the amount of overhead incurred while the principal server instance waits for the
 mirror server instance to write the transaction's log record into the redo queue. This value is relevant only in high-safety mode.

5 Retention period
 Metadata that controls how long rows in the database mirroring status table are preserved.


 */
-- Event ID 32042
EXEC sp_dbmmonitorchangealert DatabaseName, 1, 2, 1 ; -- OLDEST UNSENT TRANSACTION (set to 2 minutes)
-- Event ID 32043
EXEC sp_dbmmonitorchangealert DatabaseName, 2, 10000, 1 ; -- UNSENT LOG SIZE ON P(set to 10000K)
-- Event ID 32044
EXEC sp_dbmmonitorchangealert DatabaseName, 3, 10000, 1 ; -- UNRESTORED LOG ON M (set to 10000K)
-- Event ID 32045
EXEC sp_dbmmonitorchangealert DatabaseName, 4, 1000, 1 ; -- MIRRORING COMMIT OVERHEAD (milisecond delay for txn on P_

--EXEC sp_dbmmonitorchangealert Auctions, 5, 1000, 1 ; -- MIRRORING COMMIT OVERHEAD (milisecond delay for txn on P_

EXEC sp_dbmmonitorchangealert DatabaseName, 5, 48, 1 ;


-- Use following to review alerts
--
 sp_dbmmonitorhelpalert DatabaseName;


·         Run the below scripts to check the existence of alerts

      if exists (select * from msdb..sysalerts)
print 'Alerts was configured already in the server'

If alert is not configured for database mirroring, then create the alerts required for database mirroring

Have a Happy Mirroring setup!

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)