sql server 2008 数据库镜像(二)—镜像实战

核心提示:这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server
2005数据库镜像的配置脚本

通过证书方式创建数据库镜像

这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server
2005数据库镜像的配置脚本,详细内容请大家参考下文: SQL Server
2005数据库镜像配置脚本:

主数据库代码:

示例如下:

澳门新葡亰手机版 1澳门新葡亰手机版 2Code
USE [master]

–在MIR-A上,创建数据库镜像端点

–创建数据库主密钥。
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′;
GO

create endpoint DB_MirroringEP

SELECT * FROM sys.key_encryptions;
go

AS tcp (listener_port = 5022)

–向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring
GO

for database_Mirroring (role = partner,encryption=supported);

IF EXISTS(select * from sys.certificates WHERE name=’HOST_A_cert’)
    DROP CERTIFICATE HOST_A_cert;
GO

go

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate’,
    START_DATE = ‘2008-01-01’;
GO

–在MIR-B上,创建数据库镜像端点,用于伙伴通讯

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT Db_MirroringEP

–创建数据库端点
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring;
GO

AS TCP (LISTENER_PORT = 5022)

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 RC4 , 
        ROLE = PARTNER );
GO

FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);

SELECT * FROM sys.database_mirroring_endpoints;
GO

GO

–备份证书
BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘D:MIRRORHOST_A_cert.cer’;
GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

–在服务器之间手动拷贝证书,保证每个服务只器都有所有证书

GO

–创建用户,用于访问MIRROR
IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)
    DROP CERTIFICATE HOST_B_cert
GO

–在MIR-W上,创建数据库镜像端点,用于见证通讯

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_B_login’)
    DROP LOGIN HOST_B_login
GO

CREATE ENDPOINT Db_MirroringEP

IF EXISTS(select * from sys.database_principals WHERE  name=’HOST_B_user’)
    DROP USER HOST_B_user
GO

AS TCP (LISTENER_PORT = 5022)

create 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:MIRRORHOST_B_cert.cer’;
GO

FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

GO

–创建用户,用于访问WITNESS
IF EXISTS(select * from sys.certificates WHERE name=’HOST_C_cert’)
    DROP CERTIFICATE HOST_C_cert
澳门新葡亰手机版,GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_C_login’)
    DROP LOGIN HOST_C_login
GO

GO

IF EXISTS(select * from sys.database_principals WHERE  name=’HOST_C_user’)
    DROP USER HOST_C_user
GO

–在MIR-A,MIR-B,MIR-W上,检查端点配置

create 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:MIRRORHOST_C_cert.cer’;
GO

SELECT * FROM sys.database_mirroring_endpoints

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

GO

–拷贝用户
SELECT [name],[sid] FROM sys.database_principals WHERE [name]=’BetterDev’

–在MIR-A,MIR-B,MIR-W上,配置数据库镜像安全性,somodesql.com为自己的域名

–备份数据库

use master

–设置伙伴
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

go

 

grant connect on endpoint::DB_MirroringEP to SOMODESQL/sqladmin

镜像服务器代码:

go

 

–在MIR-A上,对AdventureWorks数据库做完全备份

澳门新葡亰手机版 3澳门新葡亰手机版 4Code
USE [master]

BACKUP DATABASE AdventureWorks TO DISK = C:/AdventureWorks.bak

–创建数据库主密钥。
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′;
GO

GO

SELECT * FROM sys.key_encryptions;
go

–在MIR-B上恢复AdventureWorks数据库。

–向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring
GO

–通过安全方法,将 C:/AdventureWorks.bak 复制到 MIR-B。

IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)
    DROP CERTIFICATE HOST_B_cert;
GO

–在 MIR-B 的镜像服务器实例上还原数据库:

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = ‘HOST_B certificate’,
    START_DATE = ‘2008-01-01’;
GO

RESTORE DATABASE AdventureWorks

SELECT * FROM sys.certificates;
GO

FROM DISK = C:/AdventureWorks.bak

–创建数据库端点
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring;
GO

WITH NORECOVERY

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 RC4 , 
        ROLE = PARTNER );
GO

GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

–启动数据库镜像,注意顺序,需要在首先在镜像服务器上配置伙伴

–备份证书
BACKUP CERTIFICATE HOST_B_cert TO FILE = ‘D:MIRRORHOST_B_cert.cer’;
GO

–在MIR-B上,指定伙伴端点,somodesql.com为自己的域名

–在服务器之间手动拷贝证书,保证每个服务器都有所有证书