SQL SERVER 基于数据库镜像的主从同步(数据库镜像实践汇总) - 郭大侠1
个人操作总结:
1、环境准备:
最好相同系统,相同sqlserver版本,相同目录,相同IP段
SQL Server最好2005sp1 及以上,且是企业/开发/数据库中心版
2、操作步骤
>1、创建数据库密钥——创建出站证书——用证书创建端点——创建登录账户——为登录账户创建用户——使用证书为用户授权——授权端口
>2、备份数据库——还原数据库——创建镜像——添加见证服务器
注意:
为了操作方便,可以先分别创建密钥、证书、端点、账户后相互分发证书,然后再创建其他两个服务器登录用户并授权提高效率,操作过程中使用命令大致相同,容易搞混,要细心
新安装 步骤
主服务器HostA | 从服务器HostB | 见证服务器HostC |
1.创建数据库密钥 | 1.创建数据库密钥 | 1.创建数据库密钥 |
2.基于1密钥创建出站证书 | 2.基于1密钥创建出站证书 | 2.基于1密钥创建出站证书 |
3.使用证书创建端点 | 3.使用证书创建端点 | 3.使用证书创建端点 |
4.导出证书并复制到其他服务器 | 4.导出证书并复制到其他服务器 | 4.导出证书并复制到其他服务器 |
5.为 从/见证 服务器创建登录名 | 5.为 主/见证 服务器创建登录名 | 5.为 主/从 服务器创建登录名 |
6.创建使用登录名5的用户 | 6.创建使用登录名5的用户 | 6.创建使用登录名5的用户 |
7.使用证书为6中用户授权 | 7.使用证书为6中用户授权 | 7.使用证书为6中用户授权 |
8.对5中登录名进行端点授权 | 8.对5中登录名进行端点授权 | 8.对5中登录名进行端点授权 |
9.主数据库设置为完全恢复,并全备复制到从服务器 | 9.以norecovery模式还原需要镜像的库 | |
10.(后做)设置镜像伙伴 | 10.(先做)设置镜像伙伴 | |
11.检验主从切换 | 11.检验主从切换 |
实操SQL命令
下面例子password的值自行修改,HostB和HostC命令和HostA大致相同,注意修改下对应的名称
主体服务器HostA | 镜像服务器HostB | 见证服务器HostC | |
1.创建密钥 | USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'; | 同HostA | 同HostA |
2.创建证书 | CREATE CERTIFICATE CertA WITH Subject = 'CertA', Expiry_Date = '2030-1-1'; | CREATE CERTIFICATE CertB WITH Subject = 'CertB', Expiry_Date = '2030-1-1'; | CREATE CERTIFICATE CertC WITH Subject = 'CertC', Expiry_Date = '2030-1-1'; |
3.创建端点 | CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =CERTIFICATE CertA, ENCRYPTION = REQUIRED Algorithm AES, ROLE =ALL ); | 同HostA 改CertA为CertB | 同HostA 改CertA为CertC |
4.备份证书 | BACKUP CERTIFICATE CertA TO FILE = 'C:\Users\Administrator\Desktop\CertA.cer' | 同HostA 改CertA为CertB | 同HostA 改CertA为CertC |
把生成的证书分别复制到另外两个服务器 | |||
5.创建账户 | CREATE LOGIN LoginB WITH PASSWORD = 'Password'; CREATE LOGIN LoginC WITH PASSWORD = 'Password'; | 同HostA 改LoginB为LoginA | 同HostA 改LoginC为LoginA |
6.创建用户 | CREATE USER UserB For Login LoginB; CREATE USER UserC For Login LoginC; | 同HostA 改CertB为CertA 改LoginB为LoginA | 同HostA 改CertC为CertA 改LoginC为LoginA |
7.证书授权 | CREATE CERTIFICATE CertB AUTHORIZATION UserB FROM FILE = 'C:\Users\Administrator\Desktop\CertB.cer';CREATE CERTIFICATE CertC AUTHORIZATION UserC FROM FILE = 'C:\Users\Administrator\Desktop\CertC.cer'; | 同HostA 改CertB为CertA 改UserB为UserA | 同HostA 改CertC为CertA 改UserC为UserA |
8.端口授权 | GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [LoginB]; GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [LoginC]; | 同HostA 改LoginB为LoginA | 同HostA 改LoginC为LoginA |
数据库——右键-任务——备份
复制到镜像服务器——数据库——右键-任务——还原
10.创建镜像分别看下网络地址格式如 TCP://win-xxxxx:5022
1).先在镜像机执行
ALTER DATABASE 数据库名 SET PARTNER = '主体服务器地址tcp://xxxx:5022';
2).之后在主体机执行
ALTER DATABASE 数据库名 SET PARTNER = '镜像服务器地址tcp://xxxx:5022';
11.测试故障切换数据库——任务——镜像——开始故障转移
12.加入见证服务器在主体服务器添加
数据库——任务——镜像——填入主体服务器地址