SQLServer 数据库镜像（一）单服务器无见证的镜像脚本配置
SQLServer 数据库镜像（二）域环境中完整镜像脚本配置
断断续续弄了几天，搭建好了域，服务器加入域后现在测试这种情况。
测试环境：
主服务器:
IP = 192.168.2.10
InstanceName=MSSQLSERVER
LISTENER_PORT=5022
镜像服务器:
IP = 192.168.2.10
InstanceName=MSSQLSERVERA
LISTENER_PORT=5023
见证服务器：
IP = 192.168.2.11
InstanceName=MSSQLSERVER
LISTENER_PORT=5022
【1. 数据库备份还原】
–主体：设置数据库“恢复模式”为“完整”模式USE master;ALTER DATABASE [DBName] SET RECOVERY FULL GO–主体：备份数据库USE master;BACKUP DATABASE [DBName] TO DISK = 'C:\Databases\MSSQLSERVER\DBName.BAK' WITH INIT,FORMAT GO –镜像：还原数据库(NORECOVERY)USE master;RESTORE DATABASE [DBName] FROM DISK = N'C:\Databases\MSSQLSERVER\DBName.BAK' WITH FILE = 1, MOVE N'DBName' TO N'C:\Databases\MSSQLSERVERA\DBName.mdf', MOVE N'DBName_log' TO N'C:\Databases\MSSQLSERVERA\DBName_log.ldf', NOUNLOAD, NORECOVERY, STATS = 10GO【2. 创建数据库主密钥和证书，，备份交换证书】–主体：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';CREATE CERTIFICATE Cert_kk_db1_mssqlserver WITH SUBJECT = 'Cert_kk_db1_mssqlserver',START_DATE = '2015-03-20';BACKUP CERTIFICATE Cert_kk_db1_mssqlserver TO FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer';–镜像：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';CREATE CERTIFICATE Cert_kk_db1_mssqlserverA WITH SUBJECT = 'Cert_kk_db1_mssqlserverA',START_DATE = '2015-03-20';BACKUP CERTIFICATE Cert_kk_db1_mssqlserverA TO FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer';–见证：USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';CREATE CERTIFICATE Cert_kk_db2_mssqlserver WITH SUBJECT = 'Cert_kk_db2_mssqlserver',START_DATE = '2015-03-20';BACKUP CERTIFICATE Cert_kk_db2_mssqlserver TO FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';–交换证书(相互拷贝证书):/*主体证书(拷贝到)————>镜像、见证镜像证书(拷贝到)————>主体、见证见证证书(拷贝到)————>主体、镜像*/【3. 创建数据库登录账户和用户并还原证书】–创建域用户：UserForMirror–SQLServer 使用 [network service] 启动实例服务–主体(还原 镜像和见证 的证书)：USE masterGOCREATE LOGIN [KK\UserForMirror] FROM WINDOWS;GOCREATE USER UserForMirror FOR LOGIN [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db2_mssqlserver]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GO–镜像(还原 主体和见证 的证书)：USE masterGOCREATE LOGIN [KK\UserForMirror] FROM WINDOWS;GOCREATE USER UserForMirror FOR LOGIN [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db1_mssqlserver]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db2_mssqlserver]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GO–见证(还原 主体和镜像 的证书)：USE masterGOCREATE LOGIN [KK\UserForMirror] FROM WINDOWS;GOCREATE USER UserForMirror FOR LOGIN [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db1_mssqlserver]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GOCREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]AUTHORIZATION [UserForMirror] FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer';GOGRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];GO【4. 创建数据库镜像端点】–主体：(域账户：KK\UserForMirror,UTHENTICATION = CERTIFICATE 证书)USE master;CREATE ENDPOINT [Endpoint_For_Mirror] AUTHORIZATION [KK\UserForMirror]STATE=STARTEDAS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)GO–镜像(主体镜像同一服务器，端口不一样：LISTENER_PORT = 5023)：USE master;CREATE ENDPOINT [Endpoint_For_Mirror] AUTHORIZATION [KK\UserForMirror]STATE=STARTEDAS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserverA, ENCRYPTION = REQUIRED ALGORITHM AES)GO–见证(ROLE = WITNESS)：USE master;CREATE ENDPOINT [Endpoint_For_Mirror] AUTHORIZATION [KK\UserForMirror]STATE=STARTEDAS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)GO【5. 开始镜像】–注意执行顺序:镜像——>主体——>见证–镜像:(PARTNER为主体服务器)USE master;ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5022'GO–主体:(PARTNER为镜像服务器)USE master;ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5023'GO–主体:(WITNESS为见证服务器)USE master;ALTER DATABASE [DBName] SET WITNESS = 'TCP://192.168.2.11:5022';GO配置完成！界面查看如图：你在会议中吵架时，尼泊尔的背包客一起端起酒杯坐在火堆旁。
