Using Certificate Deployment Database (SQLSERVER2008R2) mirroring in a non-domain environment

Using the certificate Deployment database (SQLSERVER2008R2) image in a non-domain environment preface

Deploying database mirroring there are generally two ways to deploy a certificate deployment in a domain environment under http://liulike.blog.51cto.com/1355103/339183 and non-domain environments reference address: http://www.cnblogs.com/shanyou/ Archive/2010/05/10/1732007.html

This is where you deploy your tests in three virtual machine non-domain environments.

First, the environment

Database	System	Ip	Role
Sqlserver2008r2	Server 2008R2	10.10.0.52	Subject
Sqlserver2008r2	Server 2008R2	10.10.0.53	Mirror
Sqlserver2008r2	Server 2008R2	10.10.0.54	Witness

Before you configure database mirroring, you need to open ports 1433 and 5022 for all three servers.

The steps to turn on 1433 and 5022 are illustrated below:

1. Turn on firewall-Advanced settings

Ii. mirroring Deployment 1 certificates and endpoints (outbound connections)

1.1 Theme Servers

Use master;    --drop master key  CREATE master key encryption by PASSWORD = ' 1qaz! QAZ ';  GO    --Create a certificate for the principal server Host_a instance.    --drop CERTIFICATE Host_a_cert  CREATE CERTIFICATE host_a_cert with   SUBJECT = ' host_a CERTIFICATE ', start_date = ' 3/12/2015 ', expiry_date = ' 01/01/ 2099 ';  GO     -Use this certificate to create a mirroring endpoint for the server instance.    --drop ENDPOINT endpoint_mirroring  CREATE ENDPOINT endpoint_mirroring state  = STARTED  as TCP (  listener_port=5022  , listener_ip = all< c15/>)   for database_mirroring (   authentication = CERTIFICATE Host_a_cert  , encryption = REQUIRED Algorithm AES  , ROLE = PARTNER  );  GO    -Back up the HOST_A certificate and copy it to the mirror server Host_b and witness Host_c    backup CERTIFICATE host_a_cert to FILE = ' C:\DbMirror\host_A . cer ';  GO  

1.2 Mirror server

/*********************************************** execute this script on the mirror server host_b ********************************************* **/use master;--drop Master Keycreate Master KEY encryption by PASSWORD = ' 1qaz! QAZ '; go--Create a certificate for the mirror server Host_b instance. --drop CERTIFICATE host_b_certcreate CERTIFICATE host_b_certwith SUBJECT = ' host_b CERTIFICATE ', start_date = ' 3/12/2015 ' , expiry_date = ' 01/01/2099 '; go--creates a mirroring endpoint for the server instance in Host_b. --drop ENDPOINT endpoint_mirroringcreate ENDPOINT endpoint_mirroringstate = Startedas TCP (listener_port=5022, LISTENER _IP = All) for database_mirroring (authentication = CERTIFICATE Host_b_cert, encryption = REQUIRED algorithm AES, ROLE = P Artner); go--back up the Host_b certificate and copy it to the principal server host_a and the witness server Host_c on backup CERTIFICATE host_b_cert to FILE = ' c:\DbMirror\host_B.cer '; GO

1.3 Witness Server

/**************************** Witness Server Host_c execute *****************************/--alter DATABASE mirrordb SET PARTNER Offuse master;--drop Master Keycreate Master KEY encryption by PASSWORD = ' 1qaz! QAZ '; go--Create a certificate for this server instance. --drop CERTIFICATE host_c_certcreate CERTIFICATE host_c_certwith SUBJECT = ' Host_c CERTIFICATE ', start_date = ' 3/12/2015 ' , expiry_date = ' 01/01/2099 '; go--Use this certificate to create a mirroring endpoint for the server instance. --drop ENDPOINT endpoint_mirroringcreate ENDPOINT endpoint_mirroringstate = Startedas TCP (listener_port=5022, LISTENER _IP = All) for database_mirroring (authentication = CERTIFICATE Host_c_cert, encryption = REQUIRED algorithm AES, ROLE = W itness); go--back up the Host_c certificate and copy it to the principal server host_a and the mirror server Host_c backup CERTIFICATE host_c_cert to FILE = ' c:\dbmirror\host_C.cer '; GO

2 Configuring Outbound Connections

2.1 Principal Server

--Create a login name for the mirror server host_b on host_a. Use Master;--drop login host_b_logincreate login host_b_login with PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_b_usercreate user host_b_user for LOGIN host_b_login; go--Associate the certificate with the user. --drop CERTIFICATE host_b_certcreate CERTIFICATE host_b_certauthorization host_b_userfrom FILE = ' C:\DbMirror\host_ B.cer ' go--grants CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login]; go--creates a logon name for the witness server Host_c on the principal server host_a. Use Master;--drop login host_c_logincreate login host_c_login with PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_c_usercreate user host_c_user for LOGIN host_c_login; go--Associate the certificate with the user. --drop CERTIFICATE host_c_certcreate CERTIFICATE host_c_certauthorization host_c_userfrom FILE = ' C:\DbMirror\host_ C.cer ' go--grants CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_c_login]; go--on the principal server to create a login for this machine (MS can be omitted) using Master;--drop login host_a_logincreate login host_a_login with password= ' 1qaz! QaZ '; go--Create a user that uses the login name. --drop User Host_a_usercreate user host_a_user for CERTIFICATE Host_a_cert; go--Grant CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login]; GO

2.2 Mirror server

--Create a logon name for the principal server host_a on the mirror server host_b. Use Master;--drop login host_a_logincreate login host_a_login with PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_a_usercreate user host_a_user for LOGIN host_a_login; go--Associate the certificate with the user. --drop CERTIFICATE host_a_certcreate CERTIFICATE host_a_certauthorization host_a_userfrom FILE = ' C:\Dbmirror\host_ A.cer ' go--grants CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login]; go--Create a logon name for the witness server Host_c on the mirror server host_b. Use Master;--drop login host_c_logincreate login host_c_login with PASSWORD = ' 1qaz! QAZ '; GO----Create a user who uses the login name. --drop user Host_c_user CREATE user host_c_user for LOGIN host_c_login; The GO----Associate the certificate with that user. --drop CERTIFICATE host_c_certcreate CERTIFICATE host_c_certauthorization host_c_userfrom FILE = ' C:\Dbmirror\host_ C.cer ' GO----Grant CONNECT permission to the logon name of the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_c_login]; go--Create a login name for this machine on the mirror server host_b (MS can be omitted) use Master;--drop login host_b_logincreate login Host_b_login WITH PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_b_usercreate user host_b_user for CERTIFICATE Host_b_cert; go--Grant CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login]; GO

2.3 Witness Server

--Create a logon name for the principal server host_a on the witness server Host_c. Use Master;--drop login host_a_logincreate login host_a_login with PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_a_usercreate user host_a_user for LOGIN host_a_login; go--Associate the certificate with the user. --drop CERTIFICATE host_a_certcreate CERTIFICATE host_a_certauthorization host_a_userfrom FILE = ' C:\DbMirror\host_ A.cer ' go--grants CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login]; go--Create a logon name for the mirror server host_b on the witness server Host_c. Use Master;--drop login host_b_logincreate login host_b_login with PASSWORD = ' 1qaz! QAZ '; go--Create a user that uses the login name. --drop User Host_b_usercreate user host_b_user for LOGIN host_b_login; go--Associate the certificate with the user. --drop CERTIFICATE host_b_certcreate CERTIFICATE host_b_certauthorization host_b_userfrom FILE = ' C:\DbMirror\host_ B.cer ' go--grants CONNECT permission to the login for the remote mirroring endpoint. GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login]; GO

3 Configuring Database logon Information

Querying SIDs and name in the principal database

Use Master;select sid,name from syslogins;

In the backup database execution

Use master;exec sp_addlogin @loginame = ' Xiejun ', @passwd = ' 1qaz! QAZ ', @sid = 0x9e2d3238732d264483489528b0dc0d9f;

4 Backing up the database

Executing in the principal database

Back up the database on the host, make a full backup, and then make a log transaction backup.

1, master data must be set up in full mode for backup, such as:

Select "Recovery Mode" as "full mode".

2, backup database, such as:

Select "Backup Type" as "full" when backing up.

3, back up the transaction log, such as:

Select the backup type as the transaction log and the backup directory is consistent with the directory of the backup database.

In the mirrored database

The database and transaction logs must be restored in norecovery form when restoring

5 Configuring the Partner server

Execution order is mirror-"body-" witness

In the mirror server configuration

ALTER DATABASE xiejunset PARTNER = ' tcp://10.10.0.52:5022 '; GO

In the principal server configuration

ALTER DATABASE xiejunset PARTNER = ' tcp://10.10.0.53:5022 '; GO

In the principal server configuration

ALTER DATABASE [Xiejun]set WITNESS = ' tcp://10.10.0.54:5022 '; GO

To this server configuration succeeded

Using Certificate Deployment Database (SQLSERVER2008R2) mirroring in a non-domain environment