数据库服务器在安装数据库系统后更改计算机名称,将会导致复制订阅出现问题,所以需要将名称更改为一致,以下SQL语句用于处理此问题:
1)先执行如下脚本,看下名称是否一致
use master
go
select @@servername
select serverproperty('servername')
2)如果一致就不需要修改,不一致时执行如下
脚本:
EXEC sp_dropserver 'old_server_name'
go
EXEC sp_addserver 'current_computer_name','local'
-
注意:old_server_name为上面查询出的第一行的值,current_computer_name为第二行的值
-
注意:需要重启服务之后,再次运行第1个脚本,如下:
-
观察发现已经保持一致。再次新建发布订阅,观察一切正常。
也可以通过批处理的方式处理此问题
if serverproperty('servername') <> @@servername
begin
declare @server sysname
set @server = @@servername
exec sp_dropserver @server = @server
set @server = cast(serverproperty('servername') as sysname)
exec sp_addserver @server = @server , @local = 'LOCAL'
end
数据库服务器在安装数据库系统后更改计算机名称,通过执行下述SQL,会发现数据库的所有者是更名前的计算机名,将其更改为“sa”:
select name, suser_sname(owner_sid) from sys.databases -- where name = 'AH_AnalyticalData'
USE AH_AnalyticalData
GO
sp_changedbowner 'sa'
SQL Server2008R2-镜像数据库实施手册(双机)
一、配置主备机
主机名称为:masterdb01,IP地址为:192.168.1.158
备机名称为:mirrordb01, IP地址为:192.168.1.254
二、主备实例互通
1.创建证书(主备可并行执行)
--主机执行:
--如果有endpoint,master key先删除
--SELECT * FROM master.sys.database_mirroring_endpoints
--DROP ENDPOINT Endpoint_Mirroring
--DROP master key;
USE master;
ALTER SERVICE MASTER KEY FORCE REGENERATE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy';
CREATE CERTIFICATE Matser_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '2019-09-24',
EXPIRY_DATE = '2099-09-24';
----数据库中已存在主密钥。执行此语句前,请先删除该主密钥
--DROP CERTIFICATE Matser_cert
----删除数据库主密钥
--DROP MASTER KEY
--备机执行:
--如果有endpoint,master key先删除
--select * from master.sys.database_mirroring_endpoints
--DROP ENDPOINT Endpoint_Mirroring
--drop master key;
USE master;
ALTER SERVICE MASTER KEY FORCE REGENERATE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy';
CREATE CERTIFICATE Slave_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '2019-09-24',
EXPIRY_DATE = '2099-09-24';
----数据库中已存在主密钥。执行此语句前,请先删除该主密钥
--DROP CERTIFICATE Slave_cert
----删除数据库主密钥
--DROP MASTER KEY
2.创建连接的端点(主备可并行执行)
--主机执行:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE Matser_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
--备机执行:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE Slave_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
3.备份证书以备建立互联
--------------主备可并行执行
--主机执行:
BACKUP CERTIFICATE Matser_cert TO FILE = 'C:\SQLBackup\Matser_cert.cer';
--备机执行:
BACKUP CERTIFICATE Slave_cert TO FILE = 'C:\SQLBackup\Slave_cert.cer';
4. 互换证书
--将备份到C:\SQLBackup\的证书进行互换,即Matser_cert.cer复制到备机的C:\SQLBackup\。Slave_cert.cer复制到主机的C:\SQLBackup\。
5. 添加登陆名、用户
--------------主备可并行执行
**--**以下操作只能通过命令行运行,通过图形界面无法完成。
--主机执行:
CREATE LOGIN Slave_01_login WITH PASSWORD = 'ayhrrfl_19brsyj';
CREATE USER Slave_01_user FOR LOGIN Slave_01_login;
CREATE CERTIFICATE Slave_cert AUTHORIZATION Slave_01_user FROM FILE = 'C:\SQLBackup\Slave_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Slave_01_login];
--备机执行:
CREATE LOGIN Matser_01_login WITH PASSWORD = 'ayhrrfl_19brsyj';
CREATE USER Matser_01_user FOR LOGIN Matser_01_login;
CREATE CERTIFICATE Matser_cert AUTHORIZATION Matser_01_user FROM FILE = 'C:\SQLBackup\Matser_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Matser_01_login];
三、 建立镜像关系
以下步骤是针对每个数据库进行的,例如:现有主机中有7个数据库以下过程就要执行7次。
手工同步登录名和密码
数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录。
通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如业务系统使用’myuser’作为登录名访问数据库,但是在备机中没有’myuser’这个登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为'孤立用户'。在主机和备机数据库上建立相同用户名及密码即可。
准备备机数据库(主机备份及镜像还原)
在主机上备份数据库,先做完整备份,再做日志事务备份。
-
主数据必须设置成完整模式进行备份,如下图:
上图中将“恢复模式”选成“完整模式”。- 备份数据库,如下图:
备份时将“备份类型”选成“完整”。
-
备份事务日志,如下图:
将“备份类型”选成“事务日志”且备份目录与备份数据库的目录一致。
在备机上使用主机的备份文件进行还原(将主机的备份文件拷贝到备机上,)。
A. 还原要点
数据库还原时,在“还原”菜单选项中,完整备份选择“文件和文件组”进行还原,日志事务备份还原选择 “事务日志”。
B. 还原数据库
在还原数据库的时候需要使用选上**“WITH NORECOVERY”****。如图所示:**
上二个截图对应的中文界面如下:
还原的路径建议设置成和主体服务器一致的路径。
如果数据库还原成功,将会变成如下所示的样子:
C. 还原日志备份
在还原日志备份的时候需要使用选上**“WITH NORECOVERY”** (重要,否则后面执行镜像会报错)****。如图所示:
3**、启动镜像操作****(先备机后主机)**
--备机执行:
ALTER DATABASE AH_AnalyticalData SET PARTNER = 'TCP://192.168.1.158:5022';
--说明:AH_AnalyticalData为数据库名。192.168.1.158为主机IP地址。
--主机执行:
ALTER DATABASE AH_AnalyticalData SET PARTNER = 'TCP://192.168.1.254:5022';
--说明:AH_AnalyticalData为数据库名。192.168.1.254为备机IP地址。
到此,SQL镜像热备配置完成。以下为镜像配置界面:
说明:目前为止,还没有配置见证服务器,所以上图为空,另外运行模式只有“高性能(异步)”和“高安全(同步)”可选择。
目前主体数据库和镜像数据库的状态显示界面如下:
四、 数据库复制
参考:https://www.cnblogs.com/datazhang/p/6142349.html
创建完成之后还有关键的一步,就是在主体和镜像服务器上执行 DBCC TRACEON(1448,-1),如果可以重启最好加入到实例的启动参数中去。如果不设置该参数,会报复制的事务正等待下一次日志备份或等待镜像伙伴更新
五、 测试
测试一:
在主体服务器中,点击下图中的“故障转移按钮”:然后观察主体和镜像数据库角色的切换。
关闭主体服务器的SQL服务,然后观察各数据库状态,见下图:
测试二:
连接发布库,向测试表中插入数据并查询
USE mydb
INSERT INTO Admin( user_name) VALUES('master' )
SELECT * FROM admin WHERE user_name='master'
连接订阅库,查询插入数据
USE mydb
SELECT * FROM admin WHERE user_name='master'
故障切换:
停止主体数据库服务,过一会在镜像库执行强制接收
use master ;
alter database mydb set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收
切换成功后,新的主体数据库显示‘主体,已断开连接’
在新的主体服务器执行:
USE mydb
INSERT INTO Admin( user_name) VALUES('mirror' )
SELECT * FROM admin WHERE user_name='mirror'
在订阅服务器查看:
USE mydb
SELECT * FROM admin WHERE user_name='mirror'
测试成功(测试成功的图居然忘记截了。。。)
当宕机的原主体数据库连接上来后,现在的主体数据库状态由“主体,已断开”变成“主体,挂起”,此时需要在现主体数据库上执行恢复操作,主体数据库状态变为“主体,已同步”
use master ;
alter database mydb set partner resume;
六、 常见命令
--切换主备(在主机principle数据库所在HOST上执行)
use master;
ALTER DATABASE testdb set partner failover;
-- 备机强制切换(在备机上数据库状态非同步状态下可执行)
use master;
ALTER DATABASE testdb set partner force_service_allow_data_loss;
--恢复镜像
use master;
ALTER DATABASE testdb set partner resume;
--取消见证服务器
ALTER DATABASE testdb SET WITNESS OFF ;
--取消镜像(在主机principle数据库所在HOST上执行)
ALTER DATABASE testdb SET PARTNER OFF;
--设置镜像数据库还原为正常
RESTORE DATABASE testdb WITH RECOVERY;
备份主数据库出现:Backup a database on a HDD with a different sector size,可以执行以下语句备份:
BACKUP DATABASE MyDB TO DISK = N'D:\MyDB.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup', STATS = 10, FORMAT
七、 总结
要进行以上sql server的镜像设置一定要使用sql server 的配置管理器开启TCP/IP协议,如下图:
如果没有启用TCP/IP协议则只能在同一个网段内的机器配置镜像,前面的配置步骤里面所用到的IP地址要换成对应的实例名。同一个网段配置并使用镜像的时实性、传输速率更高,适用于大数据量的同步,跨网段或者跨公网的sql server 镜像一般适用于数据量小,时实性要求不高的数据同步,而且数据库在公网上同步也不安全。