SqlServer2008R2镜像（无域）

一、描述

在生产环境中，不仅我们的应用服务器需要做高可用，数据服务器同样需要做高可用，否则，一旦数据服务器出问题，整个系统就直接崩溃了。

这里我们介绍一下，使用SqlServer镜像的方式实现SqlServer的高可用。

二、搭建准备

我使用的是虚拟机来搭建环境：

机器名	版本	IP
WIN-SJPRGC2O1KO	Windows server 2008 R2	192.168.213.148
WIN-73NJ6DU4SO7	Windows server 2008 R2	192.168.213.149
WIN-LMIMQ4P4OCB	Windows server 2008 R2	192.168.213.150

 

 

 

 

 

 

 

 

我们分别为三台机器安装数据库SqlServer 2008 R2。 

为了方便，我直接禁用了三台机器的防火墙，生产环境下，允许5022端口即可。

三、开始搭建

我们选取192.168.213.148这台机器当作主体机器（解释一下，镜像里面分为主体机器，镜像机器和见证机器，主体就是我们实际用到的，而镜像就相当于一个备份，见证机器的作用是监督主体机器，当主体机器发生故障时，会自动将镜像机器变为主体机器，见证机器为可选方案，没有见证机器时，故障切换需要手动进行；当有见证机器时，故障切换将自动进行），选取192.168.213.149，为镜像机器。

1.   创建数据库主密钥

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asd123';

*注：这个密钥要相对复杂一些。

主体机器和镜像机器都执行该语句。

2.   创建证书

CREATE CERTIFICATE CertA

WITH Subject = 'CertA',

Expiry_Date = '2019-1-1';

主体机器和镜像机器都执行该语句。注意替换CertA  为CertB

3.   创建端点

IF NOT EXISTS ( SELECT  1

                FROM    sys.database_mirroring_endpoints )

    BEGIN

        CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,

            LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =

            CERTIFICATE CertA , ENCRYPTION = REQUIRED Algorithm AES, ROLE =

            ALL );

    END

主体机器和镜像机器都执行该语句。注意替换CertA为CertB

4.   备份证书

BACKUP CERTIFICATE CertA

TO FILE = 'C:SharesCertsCertA.cer

主体机器和镜像机器都执行该语句。注意替换CertA为CertB

每台机器上应该拥有所有的证书，即主体机器应该有CertA和CertB，镜像机器也应该有CertA和CertB

5.   创建登陆账户

创建互相登陆的账号，即为主体服务器创建镜像服务器的登陆账号，为镜像服务器创建主体服务器的登陆账号。

CREATE LOGIN LoginB WITH PASSWORD = 'Asd123';

主体机器和镜像机器都执行该语句。注意替换LoginB为LoginA

6.   为登陆账户创建用户

CREATE USER UserB For Login LoginB;

主体机器和镜像机器都执行该语句。注意替换LoginB为LoginA， UserB为UserA

7.   使用证书为用户授权

CREATE CERTIFICATE CertB

AUTHORIZATION UserB

FROM FILE = 'C:CertsCertB.cer';

主体机器和镜像机器都执行该语句。注意替换CertB为CertA， UserB为UserA

8.   为登陆账户授权端口

GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [LoginB];

主体机器和镜像机器都执行该语句。注意替换LoginB为LoginA

9.   备份与还原

备份没什么好说的直接下一步就可以，还原时要注意一下：

 

10.          创建镜像

分别在主体和镜像上面点一下任务—镜像，把图中那个地址复制下来，备用。

 

镜像机执行：

ALTER DATABASE HADBTest

SET PARTNER = 'TCP://WIN-SJPRGC2O1KO:5022';

HADBTest为我要创建镜像的数据库

同样在主机上将备机设置为PARTNER

执行完成后，我们刷新一下数据库就可以看到如下的样子

主体：

 

镜像：

 

四、测试

1.   测试故障切换

我们已经完成了搭建，那么我们来测试一下，加入主体机器故障了，我们需要切换至镜像机器。可以在镜像机上执行如下代码，将镜像数据库强制切换为主体数据库。

USE master

GO

ALTER DATABASE HADBTest SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

GO 

待故障机器修复成功后，我们可以恢复挂起的镜像

USE master

GO

ALTER DATABASE HADBTest SET PARTNER Resume  

GO 

恢复已修复故障机为主体机器，在当前主体机器上执行

USE master

GO

ALTER DATABASE HADBTest SET PARTNER FAILOVER       

GO

五、完善

到这里我们已经完成了镜像的搭配，也尝试了故障切换，但是尝试过后，我们发现了一个问题，就是发生故障时，我们还要人为去切换，这就是个很大的问题。所以为了解决这个问题，我们将见证机器加入进来。

与之前一样，我们为见证机器建立证书：

--创建主密钥

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asd123';

--创建证书

CREATE CERTIFICATE CertC

WITH SUBJECT = 'CertC ',

EXPIRY_DATE ='2019-1-1' ;

GO

         --授权端点

CREATE ENDPOINT DatabaseMirroring

STATE = STARTED AS TCP ( LISTENER_PORT=5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE CertC , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

--备份证书

BACKUP CERTIFICATE CertC TO FILE = 'C:CertsCertC.cer';

每台机器上应该拥有所有的证书，即主体机器应该有CertA，CertB和CertC，镜像机器也应该有CertA，CertB和CertC，见证机器上应该有CertA，CertB和CertC。

在见证机器上添加主体和镜像机器的账户用户和端点

--主体：

USE master;

CREATE LOGIN LoginA WITH PASSWORD = 'Asd123';

GO

CREATE USER UserA FOR LOGIN LoginA;

GO

CREATE CERTIFICATE CertA

   AUTHORIZATION UserA

   FROM FILE = 'C:CertsCertA.cer'

GO

GRANT CONNECT ON ENDPOINT::DatabaseMirroring

TO LoginA; 

GO --镜像 

CREATE LOGIN LoginB WITH PASSWORD = 'Asd123'; 

GO 

CREATE USER UserB FOR LOGIN LoginB; 

GO 

CREATE CERTIFICATE CertB AUTHORIZATION UserB FROM FILE = 'C:CertsCertB.cer' 

GO 

GRANT CONNECT ON ENDPOINT::DatabaseMirroring TO LoginB; 

GO 
--在主体和镜像机器上，将见证机器创建账户用户和端点 

USE master; 

CREATE LOGIN LoginC WITH PASSWORD = 'Asd123'; 

GO 

CREATE USER UserC FOR LOGIN LoginC; 

GO 

CREATE CERTIFICATE CertC AUTHORIZATION UserC FROM FILE = 'C:CertsCertC.cer' 

GO 

GRANT CONNECT ON ENDPOINT::DatabaseMirroring TO LoginC; 

GO 
--在主体机器上执行语句，将见证机器加入进来： 

ALTER DATABASE HADBTest SET WITNESS = 'TCP:// WIN-LMIMQ4P4OCB '

现在加入见证机器成功了，我们就可以实现自动的故障转移了。

六、访问

故障转移是完全没问题了，但是似乎有一个更大的问题要解决，就是，主体和镜像本身就是两台不同ip的机器，那么怎么访问呢？

在ADO.NET中提供了Failover Partner属性来解决这个问题，填写镜像数据库的ip地址，一旦出现了连接错误，ado.net会在超时以后自动去连接镜像数据库。

连接字符串示例如下：

    <add name="Model1" connectionString="data source=192.168.213.149;Failover Partner=192.168.213.148;initial catalog=HADBTest;persist security info=True;user id=sa;password=Asd123;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />