sqlserver 2008镜像(测试可用)

适用范围：

      sqlserver2008 局域网内的多个主机，见证和镜像在同一个电脑上，先做代码操作然后进行界面设置；见证和镜像出问题时，主体服务器不会被影响；

查看终端点

    select * from sys.endpoints

删除某终端点(终端点不带引号)
    drop endpoint 镜像

    alter database test5 set partner off;
    
0.1在 sqlserver Management Stdio 中执行减少权限问题；    
    修改每台机的HOST文件，将计算机名和IP绑定，用记事本修改系统盘下WINDOWS/system32/drivers/etc下面的文件hosts（可能需要修改hosts的权限），在该文件下加入主体及镜像地址的IP及对应的电脑名称，如：
　     192.168.1.167       SqlHost.jike.cn
       192.168.1.128       SqlMirror.jike.cn
       --210.14.79.98     SqlWitness.jike.cn

0.2远程连接设置(非必要)，具体操作：
        数据库服务实例->右键->方面->外围应用配置器->RemoteDACEnabled->True->确定。
        
1.1主题服务器
    USE master;
    
    -- drop MASTER KEY
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';  
    GO      
    --为主体服务器 host_zhu 实例制作一个证书。  
      
    --DROP CERTIFICATE host_zhu_cert  
    CREATE CERTIFICATE host_zhu_cert  WITH SUBJECT = 'host_zhu certificate',START_DATE = '6/8/2017',EXPIRY_DATE = '01/01/2099';  
    GO   
      
    --使用该证书为服务器实例创建一个镜像端点。  
      
    --DROP ENDPOINT Endpoint_Mirroring  
    CREATE ENDPOINT Endpoint_Mirroring  STATE = STARTED   AS TCP (   LISTENER_PORT=5039, LISTENER_IP = ALL )  FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE host_zhu_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER  );  
    GO  
      
    --备份 host_zhu 证书，并将其复制到镜像服务器 host_jingxiang 和见证服务器 host_jingxiang  
      
    BACKUP CERTIFICATE host_zhu_cert TO FILE = 'D:\DbMirror\host_zhu.cer';  
    GO      
    
1.2镜像服务器    
    ***********************************************
    在镜像服务器 host_jingxiang 执行此脚本
    ***********************************************/

    USE master;

    --DROP MASTER KEY
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';
    GO

    --为镜像服务器 host_jingxiang 实例制作一个证书。
    --DROP CERTIFICATE host_jingxiang_cert
    CREATE CERTIFICATE host_jingxiang_cert
    WITH SUBJECT = 'host_jingxiang certificate',START_DATE = '4/21/2015',EXPIRY_DATE = '01/01/2099';
    GO

    --在 host_jingxiang 中为服务器实例创建一个镜像端点。
    --DROP ENDPOINT Endpoint_Mirroring
    CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (
    LISTENER_PORT=5039
    , LISTENER_IP = ALL
    )

    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE host_jingxiang_cert
    , ENCRYPTION = REQUIRED ALGORITHM AES
    , ROLE = PARTNER
    );
    GO

    --备份 host_jingxiang 证书，并将其复制到主体服务器 host_zhu 和见证服务器 host_jingxiang 上

    BACKUP CERTIFICATE host_jingxiang_cert TO FILE = 'D:\DbMirror\host_jingxiang.cer';
    GO    
    
1.3见证服务器

    --ALTER DATABASE MirrorDB SET PARTNER OFF
    USE master;
    --DROP MASTER KEY
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';
    GO

    --为此服务器实例制作一个证书。
    --DROP CERTIFICATE host_jianzheng_cert
    CREATE CERTIFICATE host_jianzheng_cert
    WITH SUBJECT = 'host_jianzheng certificate',START_DATE = '4/21/2017',EXPIRY_DATE = '01/01/2099';
    GO

    --使用该证书为服务器实例创建一个镜像端点。
    --DROP ENDPOINT Endpoint_Mirroring
    CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (
    LISTENER_PORT=5041
    , LISTENER_IP = ALL
    )

    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE host_jianzheng_cert
    , ENCRYPTION = REQUIRED ALGORITHM AES
    , ROLE = WITNESS
    );
    GO

    --备份 host_jianzheng 证书，并将其复制到主体服务器 host_zhu 和镜像服务器 host_jingxiang 上

    BACKUP CERTIFICATE host_jianzheng_cert TO FILE = 'D:\dbmirror\host_jianzheng.cer';
    GO
    
2配置出站连接
  2.1主体服务器

    --在 host_zhu 上为镜像服务器 host_jingxiang 创建一个登录名。

    USE master;
    --DROP LOGIN host_jingxiang_login
    CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_jingxiang_user
    CREATE USER host_jingxiang_user FOR LOGIN host_jingxiang_login;
    GO

    --使证书与该用户关联。
    --DROP CERTIFICATE host_jingxiang_cert
    CREATE CERTIFICATE host_jingxiang_cert
    AUTHORIZATION host_jingxiang_user
    FROM FILE = 'D:\DbMirror\host_jingxiang.cer'
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
    GO

    --在主体服务器 host_zhu 上为见证服务器 host_jianzheng 创建一个登录名。
    USE master;
    --DROP LOGIN host_jianzheng_login
    CREATE LOGIN host_jianzheng_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_jianzheng_user
    CREATE USER host_jianzheng_user FOR LOGIN host_jianzheng_login;

    GO

    --使证书与该用户关联。
    --DROP CERTIFICATE host_jianzheng_cert
    CREATE CERTIFICATE host_jianzheng_cert
    AUTHORIZATION host_jianzheng_user
    FROM FILE = 'D:\DbMirror\host_jianzheng.cer'
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jianzheng_login];
    GO

    --主体服务器上创建用于本机的登录名(MS可以省略)
    USE master;
    --DROP LOGIN host_zhu_login
    CREATE LOGIN host_zhu_login WITH PASSWORD= '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_zhu_user
    CREATE USER host_zhu_user FOR CERTIFICATE host_zhu_cert;
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
    GO

  2.2镜像服务器

    --在镜像服务器 host_jingxiang 上为主体服务器 host_zhu 创建一个登录名。

    USE master;
    --DROP LOGIN host_zhu_login
    CREATE LOGIN host_zhu_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_zhu_user
    CREATE USER host_zhu_user FOR LOGIN host_zhu_login;
    GO

    --使证书与该用户关联。
    --DROP CERTIFICATE host_zhu_cert
    CREATE CERTIFICATE host_zhu_cert
    AUTHORIZATION host_zhu_user
    FROM FILE = 'D:\Dbmirror\host_zhu.cer'
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
    GO

    --在镜像服务器 host_jingxiang 上为见证服务器 host_jianzheng 创建一个登录名。

    USE master;
    --DROP LOGIN host_jianzheng_login
    CREATE LOGIN host_jianzheng_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    ----创建一个使用该登录名的用户。
    --DROP USER host_jianzheng_user
    CREATE USER host_jianzheng_user FOR LOGIN host_jianzheng_login;
    GO

    ----使证书与该用户关联。
    --DROP CERTIFICATE host_jianzheng_cert
    CREATE CERTIFICATE host_jianzheng_cert
    AUTHORIZATION host_jianzheng_user
    FROM FILE = 'D:\Dbmirror\host_jianzheng.cer'
    GO

    ----授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jianzheng_login];
    GO

    --在镜像服务器 host_jingxiang 上为本机创建一个登录名(MS可以省略)
    USE master;
    --DROP LOGIN host_jingxiang_login
    CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_jingxiang_user
    CREATE USER host_jingxiang_user FOR CERTIFICATE host_jingxiang_cert;
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
    GO

  2.3见证服务器

    --在见证服务器 host_jianzheng 上为主体服务器 host_zhu 创建一个登录名。

    USE master;
    --DROP LOGIN host_zhu_login
    CREATE LOGIN host_zhu_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_zhu_user
    CREATE USER host_zhu_user FOR LOGIN host_zhu_login;
    GO

    --使证书与该用户关联。
    --DROP CERTIFICATE host_zhu_cert
    CREATE CERTIFICATE host_zhu_cert
    AUTHORIZATION host_zhu_user
    FROM FILE = 'D:\DbMirror\host_zhu.cer'
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
    GO

    --在见证服务器 host_jianzheng 上为镜像服务器 host_jingxiang 创建一个登录名。

    USE master;
    --DROP LOGIN host_jingxiang_login
    CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
    GO

    --创建一个使用该登录名的用户。
    --DROP USER host_jingxiang_user
    CREATE USER host_jingxiang_user FOR LOGIN host_jingxiang_login;
    GO

    --使证书与该用户关联。
    --DROP CERTIFICATE host_jingxiang_cert
    CREATE CERTIFICATE host_jingxiang_cert
    AUTHORIZATION host_jingxiang_user
    FROM FILE = 'D:\DbMirror\host_jingxiang.cer'
    GO

    --授予对远程镜像端点的登录名的 CONNECT 权限。
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
    GO
    
    
    
    
/****************************不操作***************************/    
    
                        3配置数据库登录信息

                            在主体数据库中查询 sid和name

                            USE master;
                            select sid,name from syslogins;
                            
                            在备份数据库执行

                            USE master;
                            exec sp_addlogin
                            @loginame = 'sa',
                            @passwd = '123456',
                            @sid = 0x01 ;
/*******************************************************/                            
    
4备份数据库
/****************************不操作***************************/    
                      法一： --未用
                        主体数据库中执行

                        USE MASTER;
                        GO
                        BACKUP DATABASE test5
                        TO DISK = 'D:\DbMirror\DB.bak'
                        WITH INIT
                        GO
                        BACKUP LOG test5
                        TO DISK = 'D:\DbMirror\DB_log.bak'
                        WITH INIT
                        GO

                        在镜像数据库

                        还原的时候必须把数据库和事务日志以NoRecovery的形式还原
/*******************************************************/    
    
  法二： ---正常
    -- 主机备份
    USE master
    GO

    BACKUP DATABASE [test5] TO DISK = N'D:\DbMirror\DB2.bak'
    WITH FORMAT, INIT, NAME = N'test5-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
    GO
    
    BACKUP LOG [test5] TO DISK = N'D:\DbMirror\DB2.bak'
    WITH NOFORMAT, NOINIT, NAME = N'test5-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
    GO

    -- 镜像恢复
    USE master
    GO
    RESTORE DATABASE [test5] FROM DISK = N'D:\DbMirror\DB2.bak'
    WITH FILE = 1,
    NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
    GO

    RESTORE LOG [test5] FROM DISK = N'D:\DbMirror\DB2.bak'
    WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
    GO    
        
/****************************不操作***************************/            
            5配置伙伴服务器

                执行顺序为 镜像-》主体-》见证

                在镜像服务器配置

                ALTER DATABASE test5
                SET PARTNER = 'TCP://192.168.1.167:5039';
                GO

                在主体服务器配置

                ALTER DATABASE test5
                SET PARTNER = 'TCP://192.168.1.128:5039';
                GO

                在见证服务器配置--错

                ALTER DATABASE [test5]
                SET WITNESS = 'TCP://192.168.1.128:5041';
                GO

                到此服务器配置成功    
    
/*******************************************************/        
    
    
6.在sqlserver manage stdio 中进行镜像设置；    
    
    
    
    
删除证书  在master | Security | Certificates

删除用户  在master | User

然后可以删除登录名  drop login <login_name>

修改master key  :   alter master key drop encryption by service master key

删除master key  : drop master key

删除镜像的命令: alter databse <dbname> set partner off

恢复中-->还原中:
    ALTER DATABASE test5 SET PARTNER OFF
还原中-->正常状态:
    RESTORE database   test5 with recovery