It’s not very often that I need to setup database mirroring and because it’s something I do rarely, I can never remember the exact syntax. In the code below ServerP is the Principle, ServerM the mirror and ServerW the witness.
-- On ServerP
CREATE ENDPOINT [Mirror] AUTHORIZATION [domain\sqladmin]
STATE=STARTED
AS TCP (LISTENER_PORT=7777, LISTENER_IP=ALL)
FOR DATA_MIRRORING (
ROLE=PARTNER,
AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4
)
GO
BACKUP DATABASE [DatabaseName] TO DISK 'C:\SomeFolder\DBName.Bak'
GO
BACKUP LOG [DatabaseName] TO DISK 'C:\SomeFolder\DBName.trn'
GO
Copy the .bak and .trn file to ServerM and then execute the following.
-- On ServerM
CREATE ENDPOINT [Mirror] AUTHORIZATION [domain\sqladmin]
STATE=STARTED
AS TCP (LISTENER_PORT=7777, LISTENER_IP=ALL)
FOR DATA_MIRRORING (
ROLE=PARTNER,
AUTHENTICATION_WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4
)
GO
CREATE DATABASE [DatabaseName]
GO
RESTORE DATABASE [DatabaseName]
FROM DISK='C:\SomeFolder\DBName.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG [DatabaseName]
FROM DISK='C:\SomeFolder\DBName.trn'
WITH NORECOVERY, REPLACE
GO
ALTER DATABASE [DatabaseName] SET PARTNER='TCP://ServerP:7777';
GO
On the Witness Server ServerW execute the following.
-- On ServerW
CREATE ENDPOINT [Mirror]
AUTHORIZATION [domain\sqladmin]
STATE=STARTED
AS TCP (LISTENER_PORT=7777,LISTENER_IP=ALL)
FOR DATA_MIRRORING (
ROLE=WITNESS,
AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=REQUIRED ALGORITHM RC4
)
GO
Finally, back on the principle server ServerP execute these two commands.
-- On ServerP ALTER DATABASE [DatabaseName] SET PARTNER='TCP://ServerM:7777'; GO ALTER DATABASE [DatabaseName] SET WITNESS='TCP://ServerW:7777'; GO
