An example for setting database mirroring in SQL Server 2005


I am not a database mirroring expert, but a while ago I have set up database mirroring between two of my machines. I collected the steps that I followed in the following script:


— This script illustrates how to setup database mirroring between two machines.
— The script assumes that the principal machine is SQLPRINCIPAL and that the mirror machine is SQLMIRROR.
— Also, a dbm share will exist on SQLPRINCIPAL, and SQLMIRROR can read and write from it.


— on SQLPRINCIPAL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Af01AufdSL’;


CREATE CERTIFICATE cert_dbm_principal AUTHORIZATION DBO WITH SUBJECT = ‘DBM – Principal’;
BACKUP CERTIFICATE cert_dbm_principal TO FILE = ‘\\SQLPRINCIPAL\dbm\cert_dbm_principal.cer’;


CREATE ENDPOINT dbm STATE=started AS tcp (listener_port=5022) FOR database_mirroring (role=all, authentication=certificate cert_dbm_principal);


CREATE DATABASE dbm ON PRIMARY (NAME = br_dat1, FILENAME = “c:\dbm\dbm.mdf”, SIZE = 8mb) LOG ON (NAME = br_log1, FILENAME = “c:\dbm\dbm.ldf”, SIZE = 8mb);
BACKUP DATABASE dbm TO DISK = ‘\\SQLPRINCIPAL\dbm\dbm.dmp’ WITH FORMAT;


— do the mirror steps before returning here.


CREATE LOGIN l_dbm_mirror WITH PASSWORD = ‘Neufd1C’;
CREATE USER u_dbm_mirror FOR LOGIN l_dbm_mirror;
CREATE CERTIFICATE cert_dbm_mirror AUTHORIZATION u_dbm_mirror FROM FILE = ‘\\SQLPRINCIPAL\dbm\cert_dbm_mirror.cer’;
GRANT CONNECT ON ENDPOINT::dbm TO l_dbm_mirror;


ALTER DATABASE dbm SET PARTNER = ‘tcp://SQLMIRROR.domain.company.com:5022’;



 
— on SQLMIRROR:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Dgc6a3um’;


CREATE CERTIFICATE cert_dbm_mirror AUTHORIZATION DBO WITH SUBJECT = ‘DBM – Mirror’;
BACKUP CERTIFICATE cert_dbm_mirror TO FILE = ‘\\SQLPRINCIPAL\dbm\cert_dbm_mirror.cer’;


CREATE ENDPOINT dbm STATE=started AS tcp (listener_port=5022) FOR database_mirroring (role=all, authentication=certificate cert_dbm_mirror);


CREATE LOGIN l_dbm_principal WITH PASSWORD = ‘Sc6d061t’;
CREATE USER u_dbm_principal FOR LOGIN l_dbm_principal;
CREATE CERTIFICATE cert_dbm_principal AUTHORIZATION u_dbm_principal FROM FILE = ‘\\SQLPRINCIPAL\dbm\cert_dbm_principal.cer’;
GRANT CONNECT ON ENDPOINT::dbm TO l_dbm_principal;


RESTORE DATABASE dbm FROM DISK = ‘\\SQLPRINCIPAL\dbm\dbm.dmp’ WITH NORECOVERY;


ALTER DATABASE dbm SET PARTNER = ‘tcp://SQLPRINCIPAL.domain.company.com:5022’;


 



— Additional commands

— to remove mirroring, issue on either partner:
ALTER DATABASE dbm SET PARTNER OFF;


— to check the state of mirroring, issue:
SELECT * FROM sys.database_mirroring;

Comments (2)

  1. exBK says:

    Did you also add a witness server to the above scenario?  If yes, can you provide the details?  This is a very informative blog!  Thanks for sharing.

  2. No, I did not add a witness and my experience with mirroring is rather limited to the execution of this script. For additional information on Database Mirroring, you should check the dedicated forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=227&SiteID=1.