SQL Server复制情况下的高可用方案（一）镜像+复制

                    BACKUP
                    DATABASE
                    [
                    mydb
                    ]
                    TO
                    DISK
                    = N‘D:\backup\mydb.bak‘WITH NOFORMAT, NOINIT,  NAME = N‘mydb-完整数据库备份 ‘, SKIP, NOREWIND, NOUNLOAD,  STATS =10GOBACKUPLOG[mydb]TODISK= N‘D:\backup\mydb.trn‘WITH NOFORMAT, NOINIT,  NAME = N‘mydb-事务日志 备份‘ , SKIP , NOREWIND , NOUNLOAD ,  STATS =10GO

                    RESTORE
                    DATABASE
                    [
                    mydb
                    ]
                    FROM
                    DISK
                    = N‘D:\backup\mydb.bak‘WITHFILE=1,  NORECOVERY ,  NOUNLOAD ,  STATS =10GORESTORELOG[mydb]FROMDISK= N‘D:\backup\mydb.trn‘WITHFILE=1,   NORECOVERY,  NOUNLOAD,  STATS =10GO

                    --
                    创建密钥
                    CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘password‘SELECT*FROM sys.symmetric_keys
--创建证书CREATE CERTIFICATE cert_3_100
   WITH SUBJECT =‘HOST_A certificate for database mirroring‘,
   EXPIRY_DATE =‘1/1/2020‘SELECT*FROM sys.certificates ;
--创建端点CREATE ENDPOINT master_instance
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP =ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE cert_3_100
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE =ALL
   )
SELECT name , role_desc , state_desc , connection_auth_desc , encryption_algorithm_desc
   FROM sys.database_mirroring_endpoints
--备份证书到其他系统BACKUP CERTIFICATE cert_3_101 TOFILE=‘d:\cert_3_100.cer‘;
GO

                    --
                    创建密钥
                    CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘password‘SELECT*FROM sys.symmetric_keys
--创建证书CREATE CERTIFICATE cert_3_101
   WITH SUBJECT =‘HOST_A certificate for database mirroring‘,
   EXPIRY_DATE =‘1/1/2020‘SELECT*FROM sys.certificates ;
--创建端点CREATE ENDPOINT backup_instance
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP =ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE cert_3_101
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE =ALL
   )
SELECT name , role_desc , state_desc , connection_auth_desc , encryption_algorithm_desc
   FROM sys.database_mirroring_endpoints
--备份证书到其他系统BACKUP CERTIFICATE cert_3_101 TOFILE=‘d:\cert_3_101.cer‘;
GO

                    USE
                     master

                    --
                    为其他系统创建登录名
                    CREATE LOGIN login_3_101 WITH PASSWORD =‘password‘;
SELECT*FROM sys.server_principals
--创建一个使用该登录名的用户CREATEUSER user_3_101 FOR LOGIN login_3_101
SELECT*FROM sys.sysusers
--将证书与用户关联，并将镜像库生成的证书文件放到指定目录中CREATE CERTIFICATE cert_3_101
   AUTHORIZATION user_3_101
   FROMFILE=‘d:\cert_3_101.cer‘SELECT*FROM sys.certificates
--授权对远程镜像端点的登录名的 CONNECT权限，授权完成后，完成对登录到所需的证书身份验证的设置GRANT CONNECT ON ENDPOINT::master_instance TO login_3_101

                    USE
                     master

                    --
                    为其他系统创建登录名
                    CREATE LOGIN login_3_100 WITH PASSWORD =‘password‘;
SELECT*FROM sys.server_principals
--创建一个使用该登录名的用户，并将主体库生成的证书文件放到指定目录中CREATEUSER user_3_100 FOR LOGIN login_3_100
SELECT*FROM sys.sysusers
--将证书与用户关联CREATE CERTIFICATE cert_3_100
   AUTHORIZATION user_3_100
   FROMFILE=‘d:\cert_3_100.cer‘SELECT*FROM sys.certificates
--授权对远程镜像端点的登录名的 CONNECT权限，授权完成后，完成对登录到所需的证书身份验证的设置GRANT CONNECT ON ENDPOINT::backup_instance TO login_3_100

• 在主体数据库(发布数据库)和镜像数据库中指定分发数据库

输入管理密码与步骤3中配置的管理链接密码一致

 

下一步，直至完成。然后如法炮制，在镜像服务器中将WIN-3-102配置为分发服务器，指向同一分发数据库和快照文件

 

步骤3配置发布服务器

选择复制->本地发布->新建发布，按照如下步骤操作

如果要发布的数据库非常大，建议选择业务空闲期进行初始化快照

 

配置用于故障转移的复制代理（快照代理和日志读取代理）

启动复制监视器，选择发布服务器，选择快照代理，右键代理配置，选择代理配置文件

输入代理名称，取消勾选“仅显示次配置文件中使用的参数”，指定 –PublisherFailoverPartner 代理参数的镜像名称WIN-3-100

 

按照上述方法，设置日志读取器代理,指定–PublisherFailoverPartner 代理参数的镜像名称WIN-3-100

创建完成之后还有关键的一步，就是在主体和镜像服务器上执行 DBCC TRACEON(1448，-1)，如果可以重启最好加入到实例的启动参数中去。如果不设置该参数，会报复制的事务正等待下一次日志备份或等待镜像伙伴更新

 

步骤4配置订阅服务器

在订阅服务器中（WIN-3-103）中建立订阅复制，选择复制->本地订阅（右键）->新建订阅

步骤5测试

连接发布库，向测试表中插入数据并查询

                                        USE
                                         mydb

                                        INSERT
                                        INTO Admin( user_name) VALUES(‘master‘ )
SELECT*FROM admin WHEREuser_name=‘master‘

连接订阅库，查询插入数据

                                    USE
                                     mydb

                                    SELECT
                                    *
                                    FROM admin WHEREuser_name=‘master‘

故障切换：

停止主体数据库服务，过一会在镜像库执行强制接收

                                    use
                                     master ;

                                    alter
                                    database mydb set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收

切换成功后，新的主体数据库显示‘主体，已断开连接’

在新的主体服务器执行：

                                        USE
                                         mydb

                                        INSERT
                                        INTO Admin( user_name) VALUES(‘mirror‘ )
SELECT*FROM admin WHEREuser_name=‘mirror‘

在订阅服务器查看：

                                            USE
                                             mydb

                                            SELECT
                                            *
                                            FROM admin WHEREuser_name=‘mirror‘

测试成功（测试成功的图居然忘记截了。。。）

当宕机的原主体数据库连接上来后，现在的主体数据库状态由“主体，已断开”变成“主体，挂起”，此时需要在现主体数据库上执行恢复操作，主体数据库状态变为“主体，已同步”

                                                    use
                                                     master ;

                                                    alter
                                                    database mydb set partner resume;