Setting up a mirroring between 2 databases

Setting up a mirroring between 2 databases

EndPoint creation on the principal and rights setting

USE master 
go

----------------------------------------------------------
--	Creation du EndPoint Principal
----------------------------------------------------------
IF NOT EXISTS (SELECT *  FROM sys.endpoints WHERE TYPE=4)
	CREATE ENDPOINT EndPoint_Principal
		STATE = STARTED
		AS TCP ( LISTENER_PORT = 5022 )
		FOR DATABASE_MIRRORING (
		   ENCRYPTION = SUPPORTED,
		   ROLE=PARTNER);
GO

--DROP ENDPOINT EndPoint_Principal

----------------------------------------------------------
--	Grant du compte de service SQL Engine / EndPoint
----------------------------------------------------------
GRANT CONNECT ON ENDPOINT::EndPoint_Principal TO [domain\accountname]

 

EndPoint creation on the Mirror and rights setting

USE master
go

----------------------------------------------------------
-- Creation du EndPoint Mirror
----------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.endpoints WHERE TYPE=4)
CREATE ENDPOINT EndPoint_Mirror
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (
ENCRYPTION = SUPPORTED,
ROLE=PARTNER);
GO

--DROP ENDPOINT EndPoint_Mirror

----------------------------------------------------------
-- Creation du User / Login et Grant
----------------------------------------------------------
GRANT CONNECT ON ENDPOINT::EndPoint_Mirror TO [domain\accountname]

 

EndPoint creation on the Witness and rights setting

USE master 
go

----------------------------------------------------------
--	Creation du EndPoint Principal
----------------------------------------------------------
IF NOT EXISTS (SELECT *  FROM sys.endpoints WHERE TYPE=4)
	CREATE ENDPOINT EndPoint_Witness
		STATE = STARTED
		AS TCP ( LISTENER_PORT = 5024 )
		FOR DATABASE_MIRRORING (
		   ENCRYPTION = SUPPORTED,
		   ROLE=WITNESS);GO

--DROP ENDPOINT EndPoint_Witness

----------------------------------------------------------
--	Creation du User / Login et Grant
----------------------------------------------------------
GRANT CONNECT ON ENDPOINT::EndPoint_Witness TO [domain\accountname]

Sauvegarde et restauration de la base principale

On Principal instance

BACKUP DATABASE [DatabaseName] TO  DISK = N'D:\DatabaseName.bak' WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP LOG [DatabaseName] TO  DISK = N'D:\DatabaseName.trn' WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

On Mirror instance


RESTORE DATABASE [DatabaseName]
FROM DISK= N'D:\DatabaseName.bak' WITH 
MOVE N'DatabaseName' TO N'D:\SPAppli\MSSQL10_50.IRXAIVTX102\MSSQL\DATA\ritmx.mdf'
, MOVE N'DatabaseName_log' TO N'D:\SPAppli\MSSQL10_50.IRXAIVTX102\MSSQL\DATA\ritmx.ldf'
, NORECOVERY--, REPLACE

RESTORE LOG DatabaseName 
FROM DISK= N'D:\DatabaseName.trn' WITH NORECOVERY

Setting partnership

On Mirror instance


USE master
Go

ALTER DATABASE DatabaseName
SET PARTNER ='TCP://SRXAIVTX101.domain.xxxxx.fr:5022'

On Principal instance

USE master
Go

ALTER DATABASE DatabaseName
SET PARTNER ='TCP://SRXAIVTX102. domain.xxxxx.fr:5023'

If you take a look in SSMS, at the Principal instance, you should see something looking loke the following screen (Principal, Synchronized) beside the database


In the same wy in SSMS Mirror side, you should see (Mirror, Synchronized,Restoring…) beside the mirrored database

Witness setting

On Principal instance

USE master
go

————————————————–


– Cree un partenariat avec le serveur Temoin


————————————————–

ALTER DATABASE DatabaseName
SET WITNESS =‘TCP://SCEAIVTX101. domain.xxxxx.fr :5024′

Automatic failover checking

The global configuration is activated with High Safety automatic failover mode.

You can check it on the following screen

Troubleshooting

The server cannot be reached

Message

The server network address “TCP://SRVAIVTX101:5023″ cannot be reached or does not exist.
Check the network address name and that the ports for the local and remote endpoints are operational.

Cause 1 = log file is too old

Make sure the database has been restored correctly with ‘NORECOVERY’ Option

Launch a new backup of the transaction log, and restore it on the mirror instance

On Principal instance

USE master
GO

BACKUP LOG DatabaseName TO DISK =‘D:\DatabaseName.trn’

On Mirror instance

RESTORE LOG DatabaseName FROM DISK= 'D:\DatabaseName.trn' WITH NORECOVERY

Cause 2 = Port opening

Open a console view and test if ports are opened, with telnet command and with server names

  • telnet SRVAIVTX101. domain.xxxxx.fr 5022
  • telnet SRVAIVTX102. domain.xxxxx.fr 5023
  • telnet SCEAIVTX101. domain.xxxxx.fr 5024

If it does not connect, the, try by using directly IP adresses, instead of server names

  • telnet 10.254.25.36 5022
  • telnet 10.254.25.37 5023
  • telnet 10.254.25.38 5024

If you can connect now, there is a DNS problem

If the connection fails, then there is a tunneling problem:

  • firewall between servers entre les machines
  • differents domains non approved
  • firewall software (you will have to create exceptions)

Cause 3 = Endpoints issues

On each instance, try the following command :

  • Select * from sys.database_mirroring_endpoints

STATE_DESC column must show STARTED. If it’s not the case, you have to start the endpoint with the ALTER ENDPOINT command

Cause 4 = ENDPOINTS Grant

On each instance, try the following command :

  • GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Cause 5 = Recréation des ENDPOINTS re-creation

On each instance, drop endpoints and created them again

How to drop an ENDPOINT

  • DROP ENDPOINT EndPoint_Mirror

Log too old

Message

The remote copy of database “DatabaseName” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Contournement

Perform again, backup and restore operation of the principal database (full + logs)