Configuring Database Mirroring with SQL

Back

How to configure Database Mirroring between instances of SQL Server As you might have noticed, while configuring Database Mirroring using the GUI, it requires us to enter FQDNs of the servers; hence using the GUI, we can configure Database Mirroring between instances of SQL Server in a domain environment. Step 1: Create encryption key, certificate and end-points on Principal Instance /* Execute this against the Principal Instance */ USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!' GO CREATE CERTIFICATE HOST_PRIN_cert WITH SUBJECT = 'HOST_PRIN certificate', START_DATE = '01/07/2009' GO CREATE ENDPOINT End_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_PRIN_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL ) GO BACKUP CERTIFICATE HOST_PRIN_cert TO FILE = 'D:\certificate\HOST_PRIN_cert.cer' GO Step 2: Create encryption key, certificate and end-points on Mirror Instance /* Execute this against the Mirror Instance */ USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!' GO CREATE CERTIFICATE HOST_MIRR_cert WITH SUBJECT = 'HOST_MIRR certificate', START_DATE = '01/07/2009' GO CREATE ENDPOINT End_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_MIRR_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL ) GO BACKUP CERTIFICATE HOST_MIRR_cert TO FILE = 'D:\certificate\HOST_MIRR_cert.cer'; GO Step 3: Create login, user and associate certificate with user on Principal Instance /* * Execute this against the Principal Instance. The HOST_MIRR_cert.cer * needs to be copied on the Principal Server. */ USE MASTER GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login GO CREATE CERTIFICATE HOST_MIRR_cert AUTHORIZATION HOST_MIRR_user FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login] GO Step 4: Create login, user and associate certificate with user on Mirror Instance /* * Execute this against the Mirror Instance. The HOST_PRIN_cert.cer * needs to be copied on the Mirror Server. */ USE MASTER GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login GO CREATE CERTIFICATE HOST_PRIN_cert AUTHORIZATION HOST_PRIN_user FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login] GO Step 5: Create encryption key, certificate and end-points on Witness Instance /* Execute this against the Witness Instance */ USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!' GO CREATE CERTIFICATE HOST_WITT_cert WITH SUBJECT = 'HOST_WITT certificate', START_DATE = '01/07/2009' GO CREATE ENDPOINT End_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_WITT_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = Witness ) GO BACKUP CERTIFICATE HOST_WITT_cert TO FILE = 'D:\certificate\HOST_WITT_cert.cer' GO Step 6: Create login, user and associate certificate with user on Principal Instance /* * Execute this against the Principal Instance. The HOST_WITT_cert.cer * needs to be copied on the Principal Server. */ USE MASTER GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login GO CREATE CERTIFICATE HOST_WITT_cert AUTHORIZATION HOST_WITT_user FROM FILE = 'D:\certificate\HOST_WITT_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login] GO Step 7: Create login, user and associate certificate with user on Mirror Instance /* * Execute this against the Mirror Instance. The HOST_WITT_cert.cer * needs to be copied on the Mirror Server. */ USE MASTER GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login GO CREATE CERTIFICATE HOST_WITT_cert AUTHORIZATION HOST_WITT_user FROM FILE = 'D:\certificate\HOST_WITT_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login] GO Step 8: Create login, user and associate certificate with user on Witness Instance /* * Execute this against the Witness Instance. The HOST_PRIN_cert.cer * and HOST_MIRR_cert.cer needs to be copied on the Witness Server. */ USE MASTER GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login GO CREATE CERTIFICATE HOST_PRIN_cert AUTHORIZATION HOST_PRIN_user FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login] GO /* * We are creating a SQL Login here. For Windows logins, * use the Grant Login instead of Create Login */ CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!' GO CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login GO CREATE CERTIFICATE HOST_MIRR_cert AUTHORIZATION HOST_MIRR_user FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer' GO GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login] GO Step 9: Create the Mirrored Database on the Mirror Server using backups from the Principal Server /* * Execute this against the Principal Instance. */ USE MASTER GO BACKUP DATABASE MirrorDB TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak' GO BACKUP LOG MirrorDB TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn' GO /* * Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the * Mirror Server. * Execute this against the Mirror Instance. */ USE MASTER GO RESTORE DATABASE MirrorDB FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak' WITH NORECOVERY GO RESTORE LOG MirrorDB FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn' WITH NORECOVERY GO Step 10: Setup Mirroring /* * Execute this against the Mirror Instance. */ ALTER DATABASE MirrorDB SET PARTNER = 'TCP://<>:5022' GO /* * Execute this against the Principal Instance. */ ALTER DATABASE MirrorDB SET PARTNER = 'TCP://<>:5023' GO ALTER DATABASE MirrorDB SET WITNESS = 'TCP://<>:5024' GO At this point your Database Mirroring should be up and running. You can use the Database Mirroring Monitor to verify the setup as well as to monitor the Synchronization status.

Was this article helpful?
Dislike0 Like0

Views: 3437