sqlserver镜像

Pre:
对于workgroup环境，修改hosts，手动指定FQDN
1、（我的电脑->属性->计算机名->更改->其他->此计算机的主DNS后缀，改为统一规范的命名）
2、C:\WINDOWS\system32\drivers\etc\hosts

60.28.114.81 vm1.svr
60.28.114.82 vm2.svr
60.28.114.83 vm3.svr

启用TCP/IP over NetBIOS服务

启用网卡中的Windows客户端和文件打印共享

启用WINS服务中的TCP/IP over NetBIOS

一、限制
1、对于每个服务器实例，数据库镜像最多支持 10 个数据库。
2、CPU负载小于50%
3、同步镜像依赖可靠的网络环境


二、步骤
1、为主体数据库配置出站连接

--更改数据库日志恢复模式
use master;
alter database aion set recovery full;

--创建数据库主密钥
use master;
create master key encryption by password = 'Qf159528';

--在A数据库上创建证书
create certificate aion_a_cert with subject = 'aion_a certificate',start_date='01/01/2009',expiry_date='01/01/2099';

--在A数据库上使用上面创建的证书为数据库实例创建镜像端点
create endpoint Endpoint_Mirroring
state = started
as tcp(
listener_port=5022,listener_ip=all)
for database_mirroring(
authentication = certificate aion_a_cert,encryption = required algorithm aes,role = all);

--备份A数据库上的证书并拷贝到B,W上
backup certificate aion_a_cert to file = 'D:\aion_a_cert.cer'


2、为镜像数据库配置出站连接

--创建数据库主密钥
use master;
create master key encryption by password = 'Qf159528';


--在B数据库上创建证书

create certificate aion_b_cert with subject = 'aion_b certificate',start_date='01/01/2009',expiry_date='01/01/2099';

--在B数据库上为上面创建的证书创建镜像端点
create endpoint Endpoint_Mirroring
state = started
as tcp(
listener_port=5022,listener_ip=all)
for database_mirroring(
authentication = certificate aion_b_cert,encryption = required algorithm aes,role = all);


--备份B数据库上的证书并拷贝到A，W上

backup certificate aion_b_cert to file = 'D:\aion_b_cert.cer'


3、为见证数据库配置出站连接

--创建数据库主密钥
use master;
create master key encryption by password = 'Qf159528';


--在W数据库上创建证书

create certificate aion_w_cert with subject = 'aion_w certificate',start_date='01/01/2009',expiry_date='01/01/2099';

--在W数据库上为上面创建的证书创建镜像端点
create endpoint Endpoint_Mirroring
state = started
as tcp(
listener_port=5022,listener_ip=all)
for database_mirroring(
authentication = certificate aion_w_cert,encryption = required algorithm aes,role = all);


--备份W数据库上的证书并拷贝到A，B上

backup certificate aion_w_cert to file = 'D:\aion_w_cert.cer'


4、为主体数据库配置入站连接

--在A上为B创建登陆
use master;
create login aion_b_login with password = 'Qf159528';

--为以上登陆创建一个用户

create user aion_b_user for login aion_b_login;

--绑定证书到用户
create certificate aion_b_cert authorization aion_b_user from file = 'D:\aion_b_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_b_login];

--在A上为W创建登陆
use master;
create login aion_w_login with password = 'Qf159528';

--为以上登陆创建一个用户
create user aion_w_user for login aion_w_login;

--绑定证书到用户
create certificate aion_w_cert authorization aion_w_user from file = 'D:\aion_w_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_w_login];


5、为镜像数据库配置入站连接

--在B上为A创建登录
create login aion_a_login with password = 'Qf159528';

--为以上登录创建一个用户
create user aion_a_user for login aion_a_login;

--绑定证书到用户
create certificate aion_a_cert authorization aion_a_user from file = 'D:\aion_a_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_a_login];

--在B上为W创建登录
create login aion_w_login with password = 'Qf159528';

--为以上登录创建一个用户
create user aion_w_user for login aion_w_login;

--绑定证书到用户
create certificate aion_w_cert authorization aion_w_user from file = 'D:\aion_w_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_w_login];


6、为见证数据库配置入站连接

--在W上为A创建登录
create login aion_a_login with password = 'Qf159528';

--为以上登录创建一个用户
create user aion_a_user for login aion_a_login;

--绑定证书到用户
create certificate aion_a_cert authorization aion_a_user from file = 'D:\aion_a_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_a_login];

--在W上为B创建登录
create login aion_b_login with password = 'Qf159528';

--为以上登录创建一个用户
create user aion_b_user for login aion_b_login;

--绑定证书到用户
create certificate aion_b_cert authorization aion_b_user from file = 'D:\aion_b_cert.cer';

--在镜像端点上为登陆赋予CONNECT权限
grant connect on endpoint::Endpoint_Mirroring to [aion_b_login];


7、在A上备份数据库

8、在B上恢复数据库(使用norecovery)

9、为镜像数据库配置镜像伙伴

--在B的镜像服务器实例上，将A上的服务器实例设置为伙伴

alter database aion set partner = 'TCP://aion-a（完整服务器名）:5022'

10、为主体数据库配置镜像伙伴和见证服务器

--在A的主体服务器实例上，将B上的服务器实例设置为伙伴

alter database aion set partner = 'TCP://aion-b（完整服务器名）:5022'

--在A的主体服务器实例上，将W上的服

务器实例设置为见证服务器

alter database aion set witness = 'TCP://aion-w（完整服务器名）:5022'


11、配置数据库镜像事务安全级别(主从都要做)

ALTER DATABASE aion SET SAFETY FULL

12、匹配用户sid
主
use master;
select sid,name from syslogins;

从
use master;
exec sp_addlogin
@loginame='luan',
@passwd='newaion',
@sid=;

exec sp_addlogin 'bbsuser', 'bbsuser', 'master',null,'0x939962CA3170464A9B6100AA9298CFE1',null


12、分别在A和B上查看数据库镜像的配置状态

-- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态

-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态

-- 3.)通过系统目录视图查看数据库镜像配置情况


USE MASTER

GO

SELECT * FROM sys.database_mirroring_endpoints;

GO

SELECT * FROM sys.database_mirroring WHERE database_id =

(SELECT database_id FROM sys.databases WHERE name = 'Northwind')

GO





--- 六 服务器角色切换测试----

预备知识

数据库镜像角色切换

自动故障转移

只针对高可用性模式

SAFETY=FULL

手动故障转移

针对高可用性模式和高保护级别性模式

SAFETY=FULL

强制服务

只针对高性能模式

SAFETY=OFF



---(一) 自动故障转移

在主体服务器上,断开网络连接,通过数据库资源管理器查看主体/镜像服务器上数据库的状态

镜像服务器状态: 主体,已断开连接

恢复主体服务器

此时原来主体服务器(SQLSERVER实例)变为镜像服务器状态为:镜像,已同步/正在还原..

原来镜像服务器(SQL2实例)变为主体服务器状态:主体,已同步





---(二) 手动故障转移

-- 主体服务器上执行故障转移过程

USE MASTER

go

ALTER DATABASE RecoveryDb SET PARTNER FAILOVER

go

执行完后主体服务器状态变为: 镜像,已同步/正在还原..

镜像服务器状态变为 主体,已同步



---(三) 强制服务,有可能造成数据丢失

--1) 在镜像服务器上,取消对鉴证服务器的配置

ALTER DATABASE RecoveryDb SET WINTESS OFF



--2) 在主体服务器上,配置事务安全性为OFF

ALTER DATABASE RecoveryDb SET SAFETY OFF



--3) 断开主体服务器,在镜像服务器上,强制服务 进行角色切换

ALTER DATABASE RecoveryDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

完成后数据库状态为 主体,已断开连接





-----七 如何查询镜像服务器上的数据: 在镜像服务器上创建数据库快照

--1 在镜像服务器上,创建数据库快照

CREATE DATABASE ReconveryDb_snap_1

ON (NAME=RecoveryDb,FILENAM='H:\seconddb\recovery_snap1.ss')

AS SNAPSHOT OF RecoveryDb



--2 访问镜像数据库

USE ReconveryDb_snap_1

go

SELECT * FROM test



注：

1） 做镜像时，必须保证主体和镜像

数据库的日志一致性，如果在镜像服务器使用NORECOVERY选项恢复数据库时，主体数据库日志有更新，需要在主体数据库备份日志然后在镜像服务器上使用NORECOVERY选项恢复主体服务器更新的日志，否则在主体数据库上指定到镜像服务器的伙伴端点时会报错

2） 镜像完成后，对主体数据库新建/修改/删除表，镜像服务器将同步更新

3） 镜像完成后，对主体数据库执行收缩文件，镜像服务器将同步更新





----------------------------------------------------------------------------------------------------------------------
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;