The steps of configuring SQLServer2005 Database Mirror.doc

 

   1.  Executive Summary

 

The document describes the steps to configure SQL Server 2005 Database Mirror without Witness Server in Certificate Authentication.

 

Database Mirror is a transparent to the Datadirect ODBC Driver.

2. SQL Server 2005 DataBase Mirror Setting

 

2.1 Physical Connection.

The connection between Master DB and Backup DB shows as below figure.

 

2.2 Check the edition of SQL Server 2005

Database Mirror can be built on SQL Server 2005 Standard Edition, Enterprise Edition or Developer Edition except Express Edition (which can only be as Witness Server).

Use the Transact-SQL as below to search the detail information about the current SQL Server.

 SELECT @@version;

2.3 Backup master database

Note: Set the recovery mode of master DB as Full Recovery Mode before backup the master database.

Use the Transact-SQL as below to set the recovery mode of master DB as Full Recovery Mode.

 USE master;

ALTER DATABASE <DatabaseName>

SET RECOVERY FULL;

Backup the Master DB:
                                                                BACKUP DATABASE <DatabaseName>

TO DISK = “D:\<SQLBACKUP>/<Database.bak>”

WITH FORMAT

Backup the transition log of Master DB:

                                                                BACKUP LOG <DatabaseName>

                                                                                TO DISK = ”D:\<SQLBACKUP>\<DatabaseLog.bak”

Copy the backup files to backup DB machine.

2.4 Master instance and backup instance exchange setting

Note: Just only do the “Master instance and backup instance exchange setting” configuration once. In other word, if there are 5 databases need to be Mirror database between the two instances, just do the configuration once. In a word, the operation just should be done once in each master instance and backup instance (not database) pair.

2.4.1 Create certificate

Master Machine executes:

                                US`E master;

                                CREATE MASTER KEY ENCRYTION BY PASSWORD = ‘Tibco2012’;

                                CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate',

                                                START_DATE = ‘19/08/2011’;

Backup Machine executes:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Tibco2012’;
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '19/08/2011';

2.4.2 Create the Endpoint

Master Machine executes:

CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS 
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR 
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

Backup Machine executes:

CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS 
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR 
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )

2.4.3 Backup certificates

Master Machine executes:

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';

Backup Machine executes:

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';

2.4.4 Exchange certificates

Copy Host_A_cert.cer to the directory,” D:\SQLBACKUP”, in backup machine.  Copy Host_B_cert.cer to the directory, “D:\SQLBACKUP”, in master machine.

2.4.5 Add login, user

Master Machine executes:

CREATE LOGIN HOST_B_login WITH PASSWORD = 'Tibco2012';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

Backup Machine executes:            

CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

2.5 Setup Database Mirror

The steps below should be done on every mirroring database.

2.5.1 Synchronize login and password

One weakness of the Database Mirror is that database cannot maintain logins. So it’s essential to maintain a login manually.

In general, there are many users used to access database. If there is no relative user in backup database, application cannot access backup database once backup database changing to master database.

Master Machine executes:

USE master;
select sid,name from syslogins;

It can return all users and SIDs from the instance. Ensure there is a same user for this SID.

Backup Machine executes:            

USE master;
exec sp_addlogin 
@loginame = '<LoginName>', 
@passwd = '<Password>', 
@sid = <sid> ;

 

2.5.2 Restore backup database.

Restore the database from the backup database file. Then choose RESOTRE WITH NORECOVERY mode.

 

 

If do the restore operation successfully. The backup database will become like this:

 

 

Restore the transaction log of backup database:

 

2.5.2 Setup Database Mirror

Backup Machine executes:

ALTER DATABASE danieltest SET PARTNER = 'TCP://10.168.6.45:5022';

Master Machine executes:

ALTER DATABASE danieltest SET PARTNER = 'TCP://10.168.6.49:5022';

 

If do the operations successfulyl. The backup database will become like

 

 

2.6 Test

2.6.1 Exchange

Master Machine executes:

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

2. If master DB downs, backup should DB start.

. Backup Machine executes:

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3. If master DB works again, DBA can reset the role.

Backup Machine executes:

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER RESUME; 
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;