Sql Server 2008镜像配置及测试（带见证服务）

1配置主备机及见证服务器

1.1服务器基本信息

主机名称：SQLSERVER-1，IP地址：10.185.16.10

备机名称：SQLSERVER-2，IP地址：10.185.16.11

见证名称：SQLSERVER-3，IP地址：10.185.16.12

2.1检查SQLSERVER 2008数据库

只有SQLSERVER 2008标准版、企业版和开发版才可以建立数据库镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本，执行如下语句查看：

select @@version

若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用 Transact-SQL 实现此目的，请使用 ALTER DATABASE 语句：

USE master;

ALTER DATABASE <DatabaeName> //<DatabaeName>既为需要配置镜像的数据库名    

SET RECOVERY FULL;  

2.主备实例互通

实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个SQL Server 2008的实例中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每一对主备实例（不是数据库）做一次互通。

2.1创建证书（主备可并行执行）

--主机执行

USE master

--DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';

GO

 

--DROP CERTIFICATE HOST_A_cert

CREATE CERTIFICATE HOST_A_cert

   WITH SUBJECT = 'HOST_A certificate',START_DATE  = '01/01/2012',EXPIRY_DATE = '01/01/2020';

GO 

--备机执行

USE master;

--DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';

GO

 

--DROP CERTIFICATE HOST_B_cert

CREATE CERTIFICATE HOST_B_cert

   WITH SUBJECT = 'HOST_B certificate for database mirroring', START_DATE  = '01/01/2012',EXPIRY_DATE = '01/01/2020';

GO

--见证执行

USE master;

--DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd0';

GO

 

--DROP CERTIFICATE HOST_C_cert

CREATE CERTIFICATE HOST_C_cert

   WITH SUBJECT = 'HOST_C certificate',START_DATE  = '01/01/2012',EXPIRY_DATE='01/01/2020';

GO

2.2创建连接的端点（主备可并行执行）

--主机执行

--DROP ENDPOINT Endpoint_Mirroring

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 = PARTNER

   );

GO

--备机执行

--DROP ENDPOINT Endpoint_Mirroring

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 = PARTNER

   );

GO

--见证执行

--DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

   STATE = STARTED

   AS TCP (

      LISTENER_PORT=5022

      , LISTENER_IP = ALL

   )

   FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE HOST_C_cert

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE = WITNESS

   );

GO

 

2.3备份证书以备建立互联（主备可并行执行）

--主机执行

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\backup\HOST_A_cert.cer';

GO

--备机执行

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'c:\backup\HOST_B_cert.cer';

GO

--见证执行

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'c:\backup\HOST_C_cert.cer';

GO

2.4互换证书

将备份到C:\backup\的证书进行互换，即HOST_A_cert.cer复制到备机的C:\backup\和见证机的C:\backup\。HOST_B_cert.cer复制到主机的C:\backup\和见证机的C:\backup\。HOST_C_cert.cer复制到主机的C:\backup\和备机的C:\backup\。

2.5添加登录名、用户（主备机可并行执行）

--主机执行

--为入站连接配置Host_A

--1.在HOST_A 上为HOST_B 创建一个登录名。

USE master;

--DROP LOGIN HOST_B_login

CREATE LOGIN HOST_B_login WITH PASSWORD = 'P@ssw0rd0';

GO

 

--2.创建一个使用该登录名的用户。

--DROP USER HOST_B_user

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

GO

 

--3.使证书与该用户关联。

--DROP CERTIFICATE HOST_B_cert

CREATE CERTIFICATE HOST_B_cert

   AUTHORIZATION HOST_B_user

   FROM FILE = 'C:\backup\HOST_B_cert.cer'

GO

 

--4.授予对远程镜像端点的登录名的CONNECT 权限。

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

GO

 

--5.在HOST_A 上为HOST_C 创建一个登录名。

USE master;

--DROP LOGIN HOST_C_login

CREATE LOGIN HOST_C_login WITH PASSWORD = 'P@ssw0rd0';

GO

 

--6.创建一个使用该登录名的用户。

--DROP USER HOST_C_user

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

GO

 

--7.使证书与该用户关联。

--DROP CERTIFICATE HOST_C_cert

CREATE CERTIFICATE HOST_C_cert

   AUTHORIZATION HOST_C_user

   FROM FILE = 'C:\backup\HOST_C_cert.cer'

GO

 

--8.授予对远程镜像端点的登录名的CONNECT 权限。

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

GO

 

--备机执行

--1,为入站连接配置Host_B

--在HOST_B 上为HOST_A 创建一个登录名。

USE master;

DROP LOGIN HOST_A_login

CREATE LOGIN HOST_A_login WITH PASSWORD = 'P@ssw0rd0';

GO

 

--2,创建一个使用该登录名的用户。

DROP USER HOST_A_user

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

GO

 

--3,使证书与该用户关联。

DROP CERTIFICATE HOST_A_cert

CREATE CERTIFICATE HOST_A_cert

   AUTHORIZATION HOST_A_user

   FROM FILE = 'c:\backup\HOST_A_cert.cer'

GO

 

--4,授予对远程镜像端点的登录名的CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

GO

 

--5,在HOST_B 上为HOST_C 创建一个登录名。

USE master;

DROP LOGIN HOST_C_login

CREATE LOGIN HOST_C_login WITH PASSWORD = 'P@ssw0rd0';

GO

 

--6,创建一个使用该登录名的用户。

DROP USER HOST_C_user

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

GO

 

--7,使证书与该用户关联。

DROP CERTIFICATE HOST_C_cert

CREATE CERTIFICATE HOST_C_cert

   AUTHORIZATION HOST_C_user

   FROM FILE = 'c:\backup\HOST_C_cert.cer'

GO

 

--8,授予对远程镜像端点的登录名的CONNECT 权限。

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

--见证执行

--1,为入站连接配置Host_C

--在HOST_C 上为HOST_B 创建一个登录名。

USE master;

DROP LOGIN HOST_B_login

CREATE LOGIN HOST_B_login WITH PASSWORD = 'P@ssw0rd0';

GO

 

--2,创建一个使用该登录名的用户。

DROP USER HOST_B_user

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

GO

 

--3,使证书与该用户关联。

DROP CERTIFICATE HOST_B_cert

CREATE CERTIFICATE HOST_B_cert

   AUTHORIZATION HOST_B_user

   FROM FILE = 'c:\backup\HOST_B_cert.cer'

GO

 

--4,授予对远程镜像端点的登录名的CONNECT 权限。

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

GO

 

--5,在HOST_C 上为HOST_A 创建一个登录名。

USE master;

DROP LOGIN HOST_A_login

CREATE LOGIN HOST_A_login WITH PASSWORD = ' P@ssw0rd0';

GO

 

--6,创建一个使用该登录名的用户。

DROP USER HOST_A_user

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

GO

 

--7,使证书与该用户关联。

DROP CERTIFICATE HOST_A_cert

CREATE CERTIFICATE HOST_A_cert

   AUTHORIZATION HOST_A_user

   FROM FILE = 'c:\backup\HOST_A_cert.cer'

GO

 

--8,授予对远程镜像端点的登录名的CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

3.建立镜像关系

以下步骤是针对每个数据库进行的，例如：现有主机中有5个数据库以下过程就要执行5次。

3.1手工同步登录名和密码

在上文中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"

在主数据库中执行如下语句：

USE master;    

select sid,name from syslogins;   

查找出相应的用户名和sid，例如：上述的’myuser’

在备数据库中执行如下语句：

USE master;    

exec sp_addlogin    

@loginame = '<LoginName>',    

@passwd = '<Password>',    

@sid = <sid> ; 

这里的’LoginName’即主数据库中的登录名，sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

 sid name   

 ---------------------------------- -----------------    

 0x074477739DCA0E499C29394FFFC4ADE4 cz_account 

则建立登录名的SQL语句：

USE master;    

exec sp_addlogin    

@loginame = 'cz_account',    

@passwd = 'password',    

@sid = 0x074477739DCA0E499C29394FFFC4ADE4;

到此为止可以认为备机数据库的环境已经与主机同步了，还差数据库内的数据未同步。

3.2准备备机数据库

使用主机的全备文件进行还原，在还原数据的时候需要使用选上“RESTORE WITH NORECOVERY”。如图所示：

 

如果执行成功数据库将会变成这个样子：

 

 

 

3.3建立镜像

--备机执行

 

--在HOST_B 的镜像服务器实例上，将HOST_A 上的服务器实例设置为伙伴（使其成为初始主体服务器实例）。

ALTER DATABASE crm

    SET PARTNER = 'TCP://10.185.16.10:5022';

--主机执行

-,必须要在镜像数据库中先设置好伙伴后，才能在主体服务器执行

--在HOST_A 的主体服务器实例上，将HOST_B 上的服务器实例设置为伙伴（使其成为初始镜像服务器实例）。

ALTER DATABASE crm

    SET PARTNER = 'TCP://10.185.16.11:5022';

GO

 

--设置见证服务器

ALTER DATABASE crm SET WITNESS ='TCP://10.185.16.12:5022';

GO

显示如下图表示已经成功建立镜像

 

 

4.测试操作

4.1主备互换

--主机执行：

USE master;    

ALTER DATABASE crm SET PARTNER FAILOVER;   

4.2主服务器Down掉,备机紧急启动并且开始服务

--备机执行：

USE master;    

ALTER DATABASE crm SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;   

 

4.3原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行：

USE master;    

ALTER DATABASE crm SET PARTNER RESUME; --恢复镜像   

ALTER DATABASE crm SET PARTNER FAILOVER; --切换主备  

 

4.4原来的主服务器恢复,可以继续工作

--默认情况下，事务安全级别的设置为FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。

 

--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

 

USE master;    

ALTER DATABASE crm SET PARTNER SAFETY FULL; --事务安全,同步模式   

ALTER DATABASE crm SET PARTNER SAFETY OFF; --事务不安全,异步模式 

5.总结

要进行以上sql server 2008的镜像设置一定要使用sql server 的配置管理器开启TCP/IP协议，如下图

 

如果没有启用TCP/IP协议则只能在同一个网段内的机器配置镜像，前面的配置步骤里面所用到的IP地址要换成对应的实例名。同一个网段配置并使用镜像的时实性、传输速率更高，适用于大数据量的同步，跨网段或者跨公网的sql server 2008镜像一般适用于数据量小，时实性要求不高的数据同步，而且数据库在公网上同步也不安全。