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
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
/* 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.