Set up Database mirroring
Woensdag 12 Oktober 2016 at 2:49 pm. Gebruikte Tags: sqlserverHere's the generic script:
/* Setup-Mirroring.sql */
/* Klaas Vandenberghe 20140624 */
/* To setup a new mirror or */
/* repair a broken mirror */
/* Execute statements one by one */
/* alter databasename, IP or ports if necessary, */
/* and change connection from principal to mirror where mentioned!!! */
-- remove corrupt mirror; in Tasks > mirror: choose 'remove mirroring'
-- on mirrorserver:
/*
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyCompany_MyDatabase'
GO
USE [master]
GO
ALTER DATABASE [MyCompany_MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [MyCompany_MyDatabase] Script Date: 27/02/2014 7:21:42 ******/
DROP DATABASE [MyCompany_MyDatabase]
GO
*/
-- first check existing endpoints on all servers:
SELECT * FROM sys.database_mirroring_endpoints
SELECT * FROM sys.tcp_endpoints
-- usually endpoints remain undamaged and there's no need to execute the following.
-- if necessary create endpoints
-- replace CREATE by ALTER if the endpint exists but needs to be changed
-- On Acceptance Servers: alter IPs and set listener Port to 7022!
-- On Production: Port 5022!
/*
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
*/
/*
ALTER ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
*/
-- grant permission to service account
/*
GRANT CONNECT ON ENDPOINT::Mirroring TO [MyCompany\MySQLServiceAccountName];
*/
-- alter log backup schedule to allow enough time
-- to complete this procedure before next log backup, > 15 min
-- take a full backup !!! This is the starting point for the backup chain !!!
/*
BACKUP DATABASE MyCompany_MyDatabase
TO DISK = 'E:\MSSQL\MyServer_MyCompany_MyDatabase_FULL_MirrorRepair.bak'
WITH FORMAT;
*/
-- If the backup was put in a local folder: copy the backup from MyServer to MyOtherServer
-- and restore DB with NORECOVERY on MyOtherServer
/*
RESTORE DATABASE MyCompany_MyDatabase
FROM DISK = 'E:\MSSQL\MyServer_MyCompany_MyDatabase_FULL_MirrorRepair.bak'
WITH NORECOVERY;
*/
-- mtake a log backup !!! this log backup is part of the new backup chain !!!
/*
BACKUP LOG MyCompany_MyDatabase
TO DISK = 'E:\MSSQL\MyServer_MyCompany_MyDatabase_LOG_mirrorlog2.trn';
*/
-- If the backup was put in a local folder: copy the log backup from MyServer to MyOtherServer
-- and restore DB with NORECOVERY on MyOtherServer
/*
RESTORE LOG MyCompany_MyDatabase
FROM DISK = 'E:\MSSQL\MyServer_MyCompany_MyDatabase_LOG_mirrorlog2.trn'
WITH FILE=1, NORECOVERY;
*/
-- start mirroring on MyOtherServer first
-- the order is important !
/*
-- because the servers have multiple NIC's, I use the correct IP instead of a name
ALTER DATABASE MyCompany_MyDatabase
SET PARTNER =
'TCP://10.0.1.1:5022';
*/
-- start mirroring on MyServer next
/*
ALTER DATABASE MyCompany_MyDatabase
SET PARTNER =
'TCP://10.0.1.11:5022';
*/
-- add the witness on MyServer
/*
ALTER DATABASE MyCompany_MyDatabase
SET WITNESS =
'TCP://10.0.1.31:5022';
*/
-- alter the log backup schedule back to 2 min
-- If something goes wrong and mirroring needs to be removed again:
/* ALTER DATABASE MyCompany_MyDatabase SET PARTNER OFF */
-- Test if you can restore MyCompany_MyDatabase , e.g. with restore-MyCompany_MyDatabase_Prod_to_localhost.ps1
-- If not, take a new backup
/*
BACKUP DATABASE MyCompany_MyDatabase
TO DISK = '\\sqlbackupshare\sqlbackups\MyServer\MyCompany_MyDatabase\FULL\MyServer_MyCompany_MyDatabase_FULL_AfterMirrorRepair.bak'
WITH FORMAT;
*/
Geen reacties