--======================================================

----镜像计划建立 2016-05-10 17:05:16.463 hubiyun

----======================================================

--配置

--1.主体服务器一台（包含主体数据库)、镜像服务器一台（包含镜像数据库）、见证服务器一台(可选，监控，故障自动转移)

--2.主体数据库：提供客户端应用程序的连接，查询，更新，执行相关事务等，主体数据库要求使用完全恢复模式。

--3.镜像数据库：持续同步来自主体数据库的事务，镜像数据库不允许任何的连接存在，但可以对其创建数据库快照来作为只读数据库，实现用户的相关查询操作。

--4.见证服务器：可选的配置，用于高可用性操作模式，通过见证服务器自动侦测故障，实现角色切换和故障转移。一个见证服务器可以为多组镜像提供服务。

--5.角色的转换：主体数据库与镜像数据库互为伙伴，当见证服务器侦测到主体服务器故障时，在高可用性模式下，实现故障自动转移后，会自动将主体服务器切换为镜像服务器角色，即角色发生了互换。

--6.确保主体服务器和镜像服务器使用相同的版本，见证服务器可以使用相对应的任意版本的SQL server。

--7.主体服务器的主体数据库设置为 FULL恢复模式。

--步骤以及常见问题

--1.主机192.168.2.152 镜像192.168.2.153

--2.用于实现镜像的数据库为Performance

--3.服务器d:mirror为共享文件夹，其中涉及的文件都放在该文件夹中

--常见1418ERROR—》 开启RemoteDacEnabled、修改hosts文件、修改计算机DNS、telnet测试、修改xpstar.dll、xplog70.dll权限

--使用管理-SQL SERVER日志来诊断具体问题

--https://msdn.microsoft.com/en-us/library/ms191140.aspx

--http://blog.csdn.net/whatday/article/details/8350709

--http://www.cnblogs.com/mrhgw/p/3514855.html

exec sp_configure 'show advanced options', 1

reconfigure

exec sp_configure 'xp_cmdshell', 1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

--exec sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB','192.168.2.153'

--exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sa'

--go

--exec sp_dropserver 'srv_lnk','droplogins'

--go

--=================

----1.数据同步

----===============

--数据库备份

use master

go

backup database Performance to disk = N'D:\mirror\Performance.bak'

with format, init, name = N'Performance-Full Database Backup', skip, norewind, nounload, stats = 10

go

backup log Performance to disk = N'D:\mirror\Performance.bak'

with noformat, noinit, name = N'Performance-Transaction Log Backup', skip, norewind, nounload, stats = 10

go

exec master..xp_cmdshell 'net use \\192.168.2.153\mirror Aa123456 /user:domain\Administrator'

exec Master..xp_cmdshell 'copy D:\mirror\Performance.bak \\192.168.2.153\mirror\Performance.bak'

go

--数据库恢复

use master

go

restore database Performance from disk = N'D:\mirror\Performance.bak'

with file = 1,

norecovery, nounload, replace, stats = 10

go

restore log Performance from disk = N'D:\mirror\Performance.bak'

with file = 2, norecovery, nounload, stats = 10

go

--=================

----2.证书交换

----===============

--A服务器执行SQL

use master

go

if exists (select 1 from sys.endpoints where name ='Endpoint_Mirroring')

drop endpoint Endpoint_Mirroring

if exists (select 1 from sys.certificates where name ='HOST_A_cert')

drop certificate HOST_A_cert

if exists (select 1 from sys.certificates where name ='HOST_B_cert')

drop certificate HOST_B_cert

if exists (select 1 from sys.databases where name ='master' and is_master_key_encrypted_by_server>0)

drop master key

if exists (select 1 from sysusers where name ='HOST_B_user')

drop user HOST_B_user

if exists (select 1 from syslogins where name ='HOST_B_login')

drop login HOST_B_login

create master key encryption by password = 'OmBzkbeUGmXgJoZqg0vsqp4'

go

create certificate HOST_A_cert

with subject = 'HOST_A certificate',start_date='',expiry_date=''

go

create endpoint Endpoint_Mirroring

state = started

as tcp (

listener_port=7024

, listener_ip = ALL

)

for database_mirroring (

authentication = certificate HOST_A_cert

, encryption = required algorithm AES

, role = ALL

)

go

exec master.dbo.xp_cmdshell 'del D:\mirror\HOST_A_cert.cer'

backup certificate host_a_cert to file = 'D:\mirror\HOST_A_cert.cer'

go

exec master..xp_cmdshell 'net use \\192.168.2.153\mirror Aa123456 /user:domain\Administrator'

exec Master..xp_cmdshell 'copy D:\mirror\HOST_A_cert.cer \\192.168.2.153\mirror\HOST_A_cert.cer'

go

create login HOST_B_login with password = 'BBluOHCsEqd4'

go

create user HOST_B_user for login HOST_B_login

go

--B服务器执行SQL

use master

go

if exists (select 1 from sys.endpoints where name ='Endpoint_Mirroring')

drop endpoint Endpoint_Mirroring

if exists (select 1 from sys.certificates where name ='HOST_B_cert')

drop certificate HOST_B_cert

if exists (select 1 from sys.certificates where name ='HOST_A_cert')

drop certificate HOST_A_cert

if exists (select 1 from sysusers where name ='HOST_A_user')

drop user HOST_A_user

if exists (select 1 from syslogins where name ='HOST_A_login')

drop login HOST_A_login

if exists (select 1 from sys.databases where name ='master' and is_master_key_encrypted_by_server>0)

drop master key

create master key encryption by password = 'Ny0d7X1X46QAqxfheSkdE9PQYFu9B'

go

create certificate HOST_B_cert

with subject = 'HOST_B certificate for database mirroring',start_date='',expiry_date=''

go

create endpoint Endpoint_Mirroring

state = started

as tcp (

listener_port=7024

, listener_ip = ALL

)

for database_mirroring (

authentication = certificate HOST_B_cert

, encryption = required algorithm AES

, role = ALL

)

go

exec master.dbo.xp_cmdshell 'del D:\mirror\HOST_B_cert.cer'

backup certificate HOST_B_cert to file = 'D:\mirror\HOST_B_cert.cer'

go

exec master..xp_cmdshell 'net use \\192.168.2.152\mirror Aa123456 /user:domain\Administrator'

exec Master..xp_cmdshell 'copy D:\mirror\HOST_B_cert.cer \\192.168.2.152\mirror\HOST_B_cert.cer'

go

create login HOST_A_login with password = 'OTYNeiFau7DY'

go

create user HOST_A_user for login HOST_A_login

go

--A服务器执行SQL

create certificate HOST_B_cert

authorization HOST_B_user

from file = 'D:\mirror\HOST_B_cert.cer'

go

grant connect on endpoint::Endpoint_Mirroring TO HOST_B_login

go

--B服务器执行SQL

create certificate HOST_A_cert

authorization HOST_A_user

from file = 'D:\mirror\HOST_A_cert.cer'

go

grant connect on endpoinT::Endpoint_Mirroring TO HOST_A_login

go

--=================

----3.镜像操作

----===============

--B服务器

use master;

go

alter database Performance

set partner = 'TCP://2008zjk152.bird.cn:7024'

--set partner off

go

alter database Performance set partner resume

alter database Performance set partner failover

alter database Performance set partner force_service_allow_data_loss

go

--A服务器

use master;

go

alter database Performance

set partner = 'TCP://2008zjk153.bird.cn:7024'

--set partner off

go

alter database Performance set partner resume

alter database Performance set partner failover

go