sql server 2008 数据库镜像---镜像实战

USE [master]--创建数据库主密钥。IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1) --drop master key; OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';ELSE create MASTER KEY ENCRYPTION BY PASSWORD='123456789';GOSELECT * FROM sys.key_encryptions;go--向数据库中添加证书。IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_MirroringGOIF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert') DROP CERTIFICATE HOST_A_cert;GOCREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate', START_DATE = '2008-01-01';GOSELECT * FROM sys.certificates;GO--创建数据库端点IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_Mirroring;GOCREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = PARTNER );GOSELECT * FROM sys.database_mirroring_endpoints;GO--备份证书BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:/MIRROR/HOST_A_cert.cer';GO--在服务器之间手动拷贝证书,保证每个服务只器都有所有证书--创建用户,用于访问MIRRORIF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert') DROP CERTIFICATE HOST_B_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_B_login') DROP LOGIN HOST_B_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_B_user') DROP USER HOST_B_userGOcreate LOGIN HOST_B_login WITH PASSWORD = '123456789';create USER HOST_B_user FOR LOGIN HOST_B_login;create CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:/MIRROR/HOST_B_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];GO--创建用户,用于访问WITNESSIF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert') DROP CERTIFICATE HOST_C_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login') DROP LOGIN HOST_C_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user') DROP USER HOST_C_userGOcreate LOGIN HOST_C_login WITH PASSWORD = '123456789';create USER HOST_C_user FOR LOGIN HOST_C_login;create CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:/MIRROR/HOST_C_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];GO--拷贝用户SELECT [name],[sid] FROM sys.database_principals WHERE [name]='BetterDev'--备份数据库--设置伙伴ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.116:5022';GO--设置见证ALTER DATABASE NORTHWIND SET WITNESS='TCP://192.168.1.117:5022';GO--设置安全选项ALTER DATABASE NORTHWIND SET SAFETY FULL  

镜像服务器代码：

USE [master]--创建数据库主密钥。IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1) --drop master key; OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';ELSE create MASTER KEY ENCRYPTION BY PASSWORD='123456789';GOSELECT * FROM sys.key_encryptions;go--向数据库中添加证书。IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_MirroringGOIF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert') DROP CERTIFICATE HOST_B_cert;GOCREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE = '2008-01-01';GOSELECT * FROM sys.certificates;GO--创建数据库端点IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_Mirroring;GOCREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = PARTNER );GOSELECT * FROM sys.database_mirroring_endpoints;GO--备份证书BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:/MIRROR/HOST_B_cert.cer';GO--在服务器之间手动拷贝证书,保证每个服务器都有所有证书--创建用户,用于访问MAINIF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert') DROP CERTIFICATE HOST_A_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_A_login') DROP LOGIN HOST_A_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_A_user') DROP USER HOST_A_userGOcreate LOGIN HOST_A_login WITH PASSWORD = '123456789';create USER HOST_A_user FOR LOGIN HOST_A_login;create CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:/MIRROR/HOST_A_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];GO--创建用户,用于访问WITNESSIF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert') DROP CERTIFICATE HOST_C_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login') DROP LOGIN HOST_C_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user') DROP USER HOST_C_userGOcreate LOGIN HOST_C_login WITH PASSWORD = '123456789';create USER HOST_C_user FOR LOGIN HOST_C_login;create CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:/MIRROR/HOST_C_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];GO--添加用户EXEC sp_addlogin @loginname='' @passwd='' @sid='';GO--手动还原数据库--设置伙伴ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.115:5022';GO

见证服务器代码:

USE [master]--创建数据库主密钥。IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1) --drop master key; OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';ELSE create MASTER KEY ENCRYPTION BY PASSWORD='123456789';GOSELECT * FROM sys.key_encryptions;go--向数据库中添加证书。IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_MirroringGOIF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert') DROP CERTIFICATE HOST_C_cert;GOCREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate', START_DATE = '2008-01-01';GOSELECT * FROM sys.certificates;GO--创建数据库端点IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring') DROP ENDPOINT Endpoint_Mirroring;GOCREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = WITNESS );GOSELECT * FROM sys.database_mirroring_endpoints;GO--备份证书BACKUP CERTIFICATE HOST_C_cert TO FILE = 'E:/MIRROR/HOST_C_cert.cer';GO--在服务器之间手动拷贝证书,保证每个服务器都有所有证书--创建用户,用于访问MAINIF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert') DROP CERTIFICATE HOST_A_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_A_login') DROP LOGIN HOST_A_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_A_user') DROP USER HOST_A_userGOcreate LOGIN HOST_A_login WITH PASSWORD = '123456789';create USER HOST_A_user FOR LOGIN HOST_A_login;create CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'E:/MIRROR/HOST_A_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];GO--创建用户,用于访问镜像IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert') DROP CERTIFICATE HOST_B_certGOIF EXISTS(select * from sys.sql_logins WHERE name='HOST_B_login') DROP LOGIN HOST_B_loginGOIF EXISTS(select * from sys.database_principals WHERE name='HOST_B_user') DROP USER HOST_B_userGOcreate LOGIN HOST_B_login WITH PASSWORD = '123456789';create USER HOST_B_user FOR LOGIN HOST_B_login;create CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'E:/MIRROR/HOST_B_cert.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];GO