金蝶云星空-SQLSERVER无域下部署镜像文档.pdf

SQLSERVER无域下部署镜像文档目录SQLSERVER无域下部署镜像文档..................................................................................................1机器环境...................................................................................................................................1Windows环境配置..................................................................................................................1数据库环境准备.......................................................................................................................3备份数据库.......................................................................................................................3镜像服务器上还原数据库...............................................................................................4配置镜像...................................................................................................................................7镜像服务器.......................................................................................................................7主服务器...........................................................................................................................9见证服务器.....................................................................................................................10配置结果.................................................................................................................................11管理中心站点，应用服务器站点配置.................................................................................12无域模式的关键问题在于不能通过域用户，来实现机器之间的互信。所以互信配置时，一定需要分清用户，以及证书。机器环境主服务器：192.168.10.129PrincipalServer.databasemirror镜像服务器：192.168.10.132MirrorServer.databasemirror见证服务器：192.168.10.135Witness.databasemirror操作系统：WindowsServer2016SQLServer：SQLServer2017+SQLServer2017-KB4508218-x64注：演示环境使用虚机，受限于机器资源，所以有些设置只做演示，生产环境按情况调整。Windows环境配置工作组机器，需要增加DNS后缀，三台机器做相同处理。修改SQLServer服务启动身份改为本地服务修改后观察结果数据库环境准备备份数据库数据库的故障还原模型必须为完全（FULL）主服务器上备份数据库备份事务日志镜像服务器上还原数据库选择备份文件选择还原路径，并且将还原状态设置为（RESTOREWITHNORECOVERY）还原事务日志选择RESTOREWITHNORECOVERY选项还原后，数据库的状态为配置镜像如果使用界面配置，由于不同SQLServer版本，以及SSMS版本的问题，会引起不少异常，所以配置采用脚本的方式来实现。镜像服务器步骤一：生成证书usemastergocreatemasterkeyencryptionbypassword='Password@1'gocreatecertificatemirrorserver_certwithsubject='mirrorservercertificate',expiry_date='2099-12-30'gocreateendpointK3cloud_Mirroringstate=startedastcp(listener_port=5022,listener_ip=all)fordatabase_mirroring(authentication=certificatemirrorserver_cert,encryption=requiredalgorithmaes,role=all)gobackupcertificatemirrorserver_certtofile='C:\mirrorserver_cert.cer'go--备份证书，并且拷贝文件到主服务器服务器和见证服务器步骤三：创建访问主服务器的登录和用户createloginprincipalserver_loginwithpassword='Password@1'gocreateuserprincipalserver_userforloginprincipalserver_logingocreatecertificateprincipalserver_certauthorizationprincipalserver_userfromfile='c:\principalserver_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOprincipalserver_login;go步骤五：指定数据库镜像故障转移的服务器器网络地址ALTERDATABASEtestmirrorSETPARTNER='TCP://principalserver:5022';go步骤八：创建连接见证服务器的见证用户相关信息createloginwitness_loginwithpassword='Password@1'gocreateuserwitness_userforloginwitness_logingocreatecertificatewitness_certauthorizationwitness_userfromfile='c:\witness_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOwitness_login;主服务器步骤二：生成证书usemastergo--创建主密钥，密码根据情况调整createmasterkeyencryptionbypassword='Password@1'gocreatecertificateprincipalserver_certwithsubject='principalservercertificate',expiry_date='2099-12-30'–-证书过期时间，根据实际情况调整gocreateendpointK3cloud_Mirroring–-创建镜像端点,根据实际情况命名state=startedastcp(listener_port=5022,listener_ip=all)fordatabase_mirroring(authentication=certificateprincipalserver_cert,encryption=requiredalgorithmaes,role=all)go--备份证书，并且拷贝文件到镜像服务器和见证服务器backupcertificateprincipalserver_certtofile='C:\principalserver_cert.cer'步骤四：创建访问镜像服务器的登录和用户go--创建与镜像服务器互信的登录和用户（使用镜像服务器证书），并且将登录权限赋予端点createloginmirrorserver_loginwithpassword='Password@1'gocreateusermirrorserver_userforloginmirrorserver_logingocreatecertificatemirrorserver_certauthorizationmirrorserver_userfromfile='c:\mirrorserver_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOmirrorserver_login;go步骤六：指定数据库镜像故障转移的服务器器网络地址ALTERDATABASEtestmirrorSETPARTNER='TCP://mirrorserver:5022';步骤九：创建连接见证服务器的见证用户相关信息createloginwitness_loginwithpassword='Password@1'gocreateuserwitness_userforloginwitness_logingocreatecertificatewitness_certauthorizationwitness_userfromfile='c:\witness_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOwitness_login;步骤十一:主服务器上配置见证服务器alterdatabasetestmirrorSETwitness=N'TCP://Witness.databasemirror:5022'见证服务器步骤七:生成证书USEmaster;createmasterkeyencryptionbypassword='Password@1'GOcreatecertificatewitness_certwithsubject='witnesscertificate',expiry_date='2099-12-30'gocreateendpointK3cloud_Mirroringstate=startedastcp(listener_port=5022,listener_ip=all)fordatabase_mirroring(authentication=certificatewitness_cert,encryption=requiredalgorithmaes,role=all)--备份证书，并且拷贝文件到镜像服务器和主服务器GOBACKUPCERTIFICATEwitness_certTOFILE='c:\witness_cert.cer';go步骤十:创建访问镜像/主服务器的登录和用户createloginprincipalserver_loginwithpassword='Password@1'gocreateuserprincipalserver_userforloginprincipalserver_logingocreatecertificateprincipalserver_certauthorizationprincipalserver_userfromfile='c:\principalserver_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOprincipalserver_login;gocreateloginmirrorserver_loginwithpassword='Password@1'gocreateusermirrorserver_userforloginmirrorserver_logingocreatecertificatemirrorserver_certauthorizationmirrorserver_userfromfile='c:\mirrorserver_cert.cer'goGRANTCONNECTONENDPOINT::K3cloud_MirroringTOmirrorserver_login;配置结果监控情况管理中心站点，应用服务器站点配置修改管理中心站点的C:\ProgramFiles(x86)\Kingdee\K3Cloud\ManageSite\App_Data和应用服务器站点的C:\ProgramFiles(x86)\Kingdee\K3Cloud\WebSite\App_Data下的Common.config文件，找到sqlServerConnectionStringPattern关键字，增加;FailoverPartner=192.168.10.132的字符串。修改后如下：注：这里在金蝶系统中数据库注册的时候使用192.168.10.129进行注册，所以这里使用192.168.10.132。即增加的参数为非注册时选择的数据库服务器。