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.
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.
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
Create the login (service account) in the mirror server and principal
server then grant connect permission to the account by the below script
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.
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:
Create the principal server login (service account) in the witness
server then grant connect permission to the account by the below script
Create the mirror server login (service account) in the witness server
then grant connect permission to the account by the below script
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.
|
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!
Uaras0ces_ta_Tulsa Josh Monson https://wakelet.com/wake/7700Li5lvRjraqG_144uC
ReplyDeleteseuwordcalro