Mirror, Mirror…

So, while I was discussing database snapshots in SQL Server, I kind of glossed over the subject of database mirroring, assuming you already had it set up in your environment.  But maybe you don’t.  So today I’m going to go through the steps involved in setting up database mirroring in SQL Server 2008.  To keep things interesting, let’s say you want to mirror between servers in… untrusted domains (dun! dun! dun!).  And we’ll only use T-SQL (gasp!!!)  Ready?

I’ll set up the demo by creating your primary database on QAServer.

/*    Create the database and set the recovery model to full */
CREATE DATABASE [MirrorTestDB] ON  PRIMARY
( NAME = N'MirrorTestDB', FILENAME = N'F:QAServer_dataMirrorTestDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MirrorTestDB_log', FILENAME = N'F:QAServer_logMirrorTestDB_log.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)
GO

USE MirrorTestDB;
GO

CREATE TABLE MyBooks (
Title        varchar(50),
Author        varchar(50),
Published    char(4)
);
GO

INSERT INTO MyBooks VALUES ('Gone With The Wind', 'Mitchell, Margaret', '1936');
INSERT INTO MyBooks VALUES ('Go Dog Go', 'Eastman, P. D.', '1966');
INSERT INTO MyBooks VALUES ('Through The Looking Glass', 'Carroll, Lewis', '1871');
GO

USE master;
GO
ALTER DATABASE MirrorTestDB
SET RECOVERY FULL;
GO

The first step in mirroring is to take a backup of the primary database and copy is over to DEVServer.

BACKUP DATABASE MirrorTestDB
TO DISK = 'F:QAServer_BackupMirrorTestDB.bak'
WITH FORMAT
GO

Once the backup file is copied to DEVServer, we need to restore it.

RESTORE DATABASE [MirrorTestDB]
FROM  DISK = N'D:Program FilesMicrosoft SQL ServerMSSQL10.DEVServerMSSQLBackupMirrorTestDB.bak'
WITH    FILE = 1,
MOVE N'MirrorTestDB' TO N'D:Program FilesMicrosoft SQL ServerMSSQL10.DEVServerMSSQLDATAMirrorTestDB.mdf',
MOVE N'MirrorTestDB_log' TO N'D:Program FilesMicrosoft SQL ServerMSSQL10.DEVServerMSSQLDATAMirrorTestDB_1.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Here’s where this demo deviates from your run-of-the-mill mirroring configuration.  Because the servers are in untrusted domains, you can’t use Windows authentication to communicate between them.  So we need to create certificates that the servers will use to authenticate.  So we start by creating a certificate and mirroring endpoint on QAServer, and copying the certificate to DevServer.

/*    Execute on Primary */

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperStrongPassword!';
GO

USE master;
CREATE CERTIFICATE QAServer_cert
WITH SUBJECT = 'QA Server certificate for database mirroring';
GO

CREATE ENDPOINT MirroringEndpoint
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE QAServer_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

BACKUP CERTIFICATE QAServer_cert TO FILE = 'F:QAServer_backupQAServer_cert.cer';
GO

/*    Copy the .cer file to the Mirror server */

And do the same thing on DevServer.

/*    Execute on Mirror */

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperStrongPassword!';
GO

USE master;
CREATE CERTIFICATE DevServer_cert
WITH SUBJECT = 'Dev Server certificate for database mirroring';
GO

CREATE ENDPOINT MirroringEndpoint
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE DevServer_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

BACKUP CERTIFICATE DevServer_cert TO FILE = 'D:Program FilesMicrosoft SQL ServerMSSQL10.DevServerMSSQLBackupDevServer_cert.cer';
GO

/*    Copy the .cer file to the Primary server */

So now we have our certificates and we have our mirroring endpoints using the certs to authenticate.  But DevServer still doesn’t recognize QAServer’s certificate, and vice versa.  So now we need to create logins and  inbound certificates on each server using the .cer files we copied from the other server.  Starting on QAServer:

/*    Execute on Primary    */

USE master;
CREATE LOGIN DevServer_login
WITH PASSWORD = 'SuperStrongPassword!';
GO

USE master;
CREATE USER DevServer_user FOR LOGIN DevServer_login;
GO

USE master;
CREATE CERTIFICATE DevServer_cert
AUTHORIZATION DevServer_user
FROM FILE = 'F:QAServer_dataDevServer_cert.cer'
GO

USE master;
GRANT CONNECT ON ENDPOINT::MirroringEndpoint TO [DevServer_login];
GO

Then on DevServer:

/*    Execute on Mirror    */

USE master;
CREATE LOGIN QAServer_login
WITH PASSWORD = 'SuperStrongPassword!';
GO

USE master;
CREATE USER QAServer_user FOR LOGIN QAServer_login;
GO

USE master;
CREATE CERTIFICATE QAServer_cert
AUTHORIZATION QAServer_user
FROM FILE = 'D:Program FilesMicrosoft SQL ServerMSSQL10.DEVServerMSSQLDATAQAServer_cert.cer'
GO

USE master;
GRANT CONNECT ON ENDPOINT::MirroringEndpoint TO [QAServer_login];
GO

From this point the remaining setup is the same as any other mirroring configuration.  We tell each database to partner up with the other.  This time, we start on the mirror server, DevServer.

/*    Execute on Mirror    */

ALTER DATABASE MirrorTestDB
SET PARTNER = 'TCP://QAServer.firm.jonesday.qa:7024';
GO

And finally on the primary server, QAServer:

/*    Execute on the primary    */

ALTER DATABASE MirrorTestDB
SET PARTNER = 'TCP://DEVServer.firm.jonesday.dv:7024';
GO

/*    change to high-performance mode    */

ALTER DATABASE MirrorTestDB
SET PARTNER SAFETY OFF;
GO

Tada!  Mirroring between servers in untrusted domains.

To be honest, I love the idea of mirroring, but I hate setting it up.  It seems like such a pain, even without the added complexity of using certificates.  Backup here, copy there, restore there, configure the endpoints, set the partners, oops you need to backup the tail of the transaction log so go back to the primary and do that, copy it to the mirror, restore, etc. etc.  And then you have to worry about keeping logins in synch, and jobs, linked servers, etc.  Sure, you can automate a lot of it, but I really hope Microsoft comes up with a more comprehensive solution in future versions.

Also recommended: