原标题:非域环境下SQL Server搭建Mirror(镜像)的详细步骤
1.测试验证环境
服务器角色
机器名
IP
SQL Server Ver
主体服务器
WIN-TestDB4O
172.83.XXX.XXX
SQL Server 2012 - 11.0.5058.0 (X64)
镜像服务器
WIN-TestDB5O
172.73.XXX.XXX
SQL Server 2012 - 11.0.5058.0 (X64)
2.创建前环境检查
(1)网络是否能联通,并且端口可用。
(2)SQL Server版本、补丁是否满足镜像要求。
(3)SQL Server数据库的恢复模式、兼容级别。
(4)SQL Server上是否有常规的备份作业,特别是日志备份。
(5)主体服务器和镜像服务器的SQL Server能否互通。
3. 使用证书配置镜像,并备份还原数据库
在这一步中,我们将做两件事,第一件是使用证书来配置镜像,第二件是备份还原数据库。在非域环境下,必须使用证书来搭建镜像,所以把搭建证书放在第一步。有些资料上会把备份还原操作放在证书搭建之前,但是根据个人经验,当磁盘IO、网络性能不佳的时候,备份、传输、还原都会浪费大量的时间(个人操作过2个小时),并且期间服务器几乎不能操作。这种时候,我会选择先搭建好,再还原,然后马上进行同步,减少主从差异,需要同步更多的数据。
第一部分创建证书:
【如果服务器使用Local System作为SQL Server服务账号,就需要使用证书授权。】
使用证书搭建镜像的步骤如下:
(1)创建数据库主密钥(如果主密钥不存在)。
(2)在Master数据库中创建证书并用主密钥加密。
(3)使用证书授权创建端点(endpoint)。
(4)备份证书成为证书文件。
(5)在服务器上创建登录账号,用于提供其他实例访问。
(6)在master库中创建用户,并映射到上一步的登录账号中。
(7)把证书授权给这些用户。
(8)在端点上授权。
(9)设置镜像服务器的主体伙伴。
(10)设置主体服务器的镜像伙伴。
(11)配置见证服务器。
Step 1:创建数据库主密钥
主密钥的用处在这里是用于加密证书,当然主密钥不仅仅只有这个作用。对数据库主密钥的密码及存储保护要小心,这是实例级别的对象,影响面非常广。可以使用下面语句来创建:
USEmaster
GO
CREATEMASTER KEYENCRYPTION BYPASSWORD ='Pa$$w0rd';
通过系统表查看,确认。
使用相同方式在镜像服务器创建数据库主密钥。
Step 2:创建证书,并用主密钥加密
创建证书时,默认在创建日期开始一年后过期,所以针对证书的创建,要注意其过期时间。下面是在“主体服务器”上创建HOST_P_cert证书的创建
USEmaster
GO
CREATECERTIFICATE Host_A_Cert
WITHSubject ='Host_P Certificate',
Expiry_Date ='2050-1-1'; --过期日期
使用相同的方法在镜像服务器上实现对HOST_S_cert证书的创建。
Step 3:创建端点
可以使用下面的代码在主体服务器中创建端点,并且指定使用5022,端口,端口在镜像配置过程中不强制使用特定端口(被占用或者特定端口如1433除外)。
--使用Host_A_Cert证书创建端点
IFNOTEXISTS( SELECT1
FROMsys.database_mirroring_endpoints )
BEGIN
CREATEENDPOINT [DatabaseMirroring]STATE =STARTED ASTCP ( LISTENER_PORT =5022,
LISTENER_IP =ALL) FORDATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_A_Cert, ENCRYPTION =REQUIRED Algorithm AES, ROLE =
ALL);
END
在镜像服务器对证书名稍作修改,创建镜像服务器的端点。
Step 4:备份证书
备份证书的目的是发送到别的服务器并导入证书,以便别的服务器能通过证书访问这台服务器(主体服务器)。
BACKUPCERTIFICATE Host_A_Cert
TOFILE='D:ShareFoldersMirrorHost_A_Cert.cer';
同理,在镜像服务器上重复一次,注意证书名和路径。备份之后可以在目标文件夹上看到有一个cer文件:
备份证书文件互相Copy至对方文件中。
Step 5:创建登录账号
针对每个服务器单独创建一个服务器登录账号,这里只需要创建一个登录给镜像服务器即可:
CREATELOGIN Host_B_Login WITHPASSWORD ='Pa$$w0rd';
同理,在镜像服务器上创建Host_A_Login给主体服务器。
Step 6:创建用户,并映射到Step 5中创建的登录账号中
在主体服务器上运行:
CREATEUSERHost_B_User ForLogin Host_B_Login;
同理在镜像服务器也创建。
Step 7:使用证书授权用户
创建一个新的证书,并使用从伙伴服务器中复制过来的证书导入,然后映射step 6中的账号到这个新证书上。
CREATECERTIFICATE Host_B_Cert
AUTHORIZATIONHost_B_User
FROMFILE='D:ShareFoldersMirrorHost_B_Cert.cer';
注意镜像服务器上也同样。
Step 8:把Step 5中的登录账号授权访问端口 GRANTCONNECT ONENDPOINT:: [DatabaseMirroring]TO[Host_B_Login];
镜像服务器也一样。
到此为止,配置镜像的步骤已经完毕,后续会给出尽可能自动化的配置脚本。
第二部分备份还原数据库:
这一部分没有什么特别强调的,在此次试验过程中,使用了界面配置。
注意:本次还原是为Mirror做准备,所以,点击 【选项】按钮 。
所以需要选择【不对数据库执行任何操作,不回滚未提交的事务….】
还原成功
第三步:启动镜像
依次分别在镜像Server和主Server上运行以下命令就可以了【最好在Master DB上执行以下命令】
在镜像Server上线运行
ALTERDATABASE[Test_Mirror]
SETPARTNER ='TCP://172.83.XXX.XXX:5022';
GO
在主Server上运行
ALTERDATABASE[Test_Mirror]
SETPARTNER ='TCP://172.73.XXX.XXX:5022';
GO
配置成功,此时显示如下:
主体服务器上显示
镜像服务器上DB显示
4. 补充说明
以下内容用来学习
问题1查询判断数据库是否已添加主密钥
---sys.databases的is_master_key_encrypted_by_server得到是否有加密
selecttop100is_master_key_encrypted_by_server, *fromsys.databases
----如果没有就看不到数据【需定义到数据库】
----解释说明:##MS_ServiceMasterKey##----是说的整个服务,而##MS_DatabaseMasterKey## 是说的Master数据库,需留意。
----我们 使用证书搭建镜像 是需要在master数据库上创建数据库主密钥(如果主密钥不存在)。
SELECT*FROMsys.symmetric_keys
以下截图查询的数据显示Master数据库尚未创建主密钥。
以下截图的数据显示Master数据库已有主密钥
问题2由日志传送更改为镜像。
希望直接更改,即不再需要备份和还原。
Step 1 【注意:此时先手动执行一下此DB的Log 备份的Job,然后停掉此Job,接下来再执行Copy Log 文件的Job(如果有此Job的话),再停掉此Job,最后执行Restore 此Log 文件的Job,接着停掉此Job】
Step 2选择指定DB,取消【将此数据库启用为日志传送配置中的主数据库…】,就是把勾去掉。
点击确定后,会要求我们再次连接一下。
Step 3开始建立伙伴关系
先在备份Server的DB上去做
ALTERDATABASE[YYYY_Mob]
SETPARTNER ='TCP://172.87.XXX.XX2:10001';
GO
然后再在主DB上运行
ALTERDATABASE[YYYY_Mob]
SETPARTNER ='TCP://172.89.XXX.XX4:10002';
GO
问题3 删除主密钥
解决方案:
DROP CERTIFICATE 证书名
但此时 还有报错了
Step 1删除映射的登录账号和用户名
查看登入名
删除标识的登入名 ,此时执行还会报同样的错误。
注意登入名和用户名是2个概念,
DROPLOGIN For_HOST_B_user
(有时还要查询 select top 100* from sys.sysusers是否还有这个用户,有的话,还要执行 DROP User For_HOST_B_user)
Step 2删除端口
SELECT*FROMsys.endpoints e WHEREe.name =N 'Endpoint_Mirroring'
存在则删除端口
Step 3删除
此时,就OK了。去删除证书和主密钥
问题4在建立伙伴关系时,需注意设置伙伴的顺序
如果按照网址上介绍的步骤 ,现在主服务器上执行,设置伙伴。
则可能报错,提示的错误信息如下:
我们先在Mirror服务器上执行
然后再在主服务器中执行,则不报错
参考文献
http://blog.csdn.net/dba_huangzj/article/details/27652857
出处:https://www.cnblogs.com/xuliuzai/p/11436460.html
版权申明:本文来源于网友收集或网友提供,如果有侵权,请转告版主或者留言,本公众号立即删除。返回搜狐,查看更多
责任编辑: