适用范围:
sqlserver2008 局域网内的多个主机,见证和镜像在同一个电脑上,先做代码操作然后进行界面设置;见证和镜像出问题时,主体服务器不会被影响;
查看终端点
select * from sys.endpoints删除某终端点(终端点不带引号)
drop endpoint 镜像
alter database test5 set partner off;
0.1在 sqlserver Management Stdio 中执行减少权限问题;
修改每台机的HOST文件,将计算机名和IP绑定,用记事本修改系统盘下WINDOWS/system32/drivers/etc下面的文件hosts(可能需要修改hosts的权限),在该文件下加入主体及镜像地址的IP及对应的电脑名称,如:
192.168.1.167 SqlHost.jike.cn
192.168.1.128 SqlMirror.jike.cn
--210.14.79.98 SqlWitness.jike.cn
0.2远程连接设置(非必要),具体操作:
数据库服务实例->右键->方面->外围应用配置器->RemoteDACEnabled->True->确定。
1.1主题服务器
USE master;
-- drop MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';
GO
--为主体服务器 host_zhu 实例制作一个证书。
--DROP CERTIFICATE host_zhu_cert
CREATE CERTIFICATE host_zhu_cert WITH SUBJECT = 'host_zhu certificate',START_DATE = '6/8/2017',EXPIRY_DATE = '01/01/2099';
GO
--使用该证书为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5039, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE host_zhu_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER );
GO
--备份 host_zhu 证书,并将其复制到镜像服务器 host_jingxiang 和见证服务器 host_jingxiang
BACKUP CERTIFICATE host_zhu_cert TO FILE = 'D:\DbMirror\host_zhu.cer';
GO
1.2镜像服务器
***********************************************
在镜像服务器 host_jingxiang 执行此脚本
***********************************************/
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';
GO
--为镜像服务器 host_jingxiang 实例制作一个证书。
--DROP CERTIFICATE host_jingxiang_cert
CREATE CERTIFICATE host_jingxiang_cert
WITH SUBJECT = 'host_jingxiang certificate',START_DATE = '4/21/2015',EXPIRY_DATE = '01/01/2099';
GO
--在 host_jingxiang 中为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5039
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE host_jingxiang_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = PARTNER
);
GO
--备份 host_jingxiang 证书,并将其复制到主体服务器 host_zhu 和见证服务器 host_jingxiang 上
BACKUP CERTIFICATE host_jingxiang_cert TO FILE = 'D:\DbMirror\host_jingxiang.cer';
GO
1.3见证服务器
--ALTER DATABASE MirrorDB SET PARTNER OFF
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '435sa4f4s3d4f';
GO
--为此服务器实例制作一个证书。
--DROP CERTIFICATE host_jianzheng_cert
CREATE CERTIFICATE host_jianzheng_cert
WITH SUBJECT = 'host_jianzheng certificate',START_DATE = '4/21/2017',EXPIRY_DATE = '01/01/2099';
GO
--使用该证书为服务器实例创建一个镜像端点。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5041
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE host_jianzheng_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = WITNESS
);
GO
--备份 host_jianzheng 证书,并将其复制到主体服务器 host_zhu 和镜像服务器 host_jingxiang 上
BACKUP CERTIFICATE host_jianzheng_cert TO FILE = 'D:\dbmirror\host_jianzheng.cer';
GO
2配置出站连接
2.1主体服务器
--在 host_zhu 上为镜像服务器 host_jingxiang 创建一个登录名。
USE master;
--DROP LOGIN host_jingxiang_login
CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_jingxiang_user
CREATE USER host_jingxiang_user FOR LOGIN host_jingxiang_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE host_jingxiang_cert
CREATE CERTIFICATE host_jingxiang_cert
AUTHORIZATION host_jingxiang_user
FROM FILE = 'D:\DbMirror\host_jingxiang.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
GO
--在主体服务器 host_zhu 上为见证服务器 host_jianzheng 创建一个登录名。
USE master;
--DROP LOGIN host_jianzheng_login
CREATE LOGIN host_jianzheng_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_jianzheng_user
CREATE USER host_jianzheng_user FOR LOGIN host_jianzheng_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE host_jianzheng_cert
CREATE CERTIFICATE host_jianzheng_cert
AUTHORIZATION host_jianzheng_user
FROM FILE = 'D:\DbMirror\host_jianzheng.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jianzheng_login];
GO
--主体服务器上创建用于本机的登录名(MS可以省略)
USE master;
--DROP LOGIN host_zhu_login
CREATE LOGIN host_zhu_login WITH PASSWORD= '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_zhu_user
CREATE USER host_zhu_user FOR CERTIFICATE host_zhu_cert;
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
GO
2.2镜像服务器
--在镜像服务器 host_jingxiang 上为主体服务器 host_zhu 创建一个登录名。
USE master;
--DROP LOGIN host_zhu_login
CREATE LOGIN host_zhu_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_zhu_user
CREATE USER host_zhu_user FOR LOGIN host_zhu_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE host_zhu_cert
CREATE CERTIFICATE host_zhu_cert
AUTHORIZATION host_zhu_user
FROM FILE = 'D:\Dbmirror\host_zhu.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
GO
--在镜像服务器 host_jingxiang 上为见证服务器 host_jianzheng 创建一个登录名。
USE master;
--DROP LOGIN host_jianzheng_login
CREATE LOGIN host_jianzheng_login WITH PASSWORD = '435sa4f4s3d4f';
GO
----创建一个使用该登录名的用户。
--DROP USER host_jianzheng_user
CREATE USER host_jianzheng_user FOR LOGIN host_jianzheng_login;
GO
----使证书与该用户关联。
--DROP CERTIFICATE host_jianzheng_cert
CREATE CERTIFICATE host_jianzheng_cert
AUTHORIZATION host_jianzheng_user
FROM FILE = 'D:\Dbmirror\host_jianzheng.cer'
GO
----授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jianzheng_login];
GO
--在镜像服务器 host_jingxiang 上为本机创建一个登录名(MS可以省略)
USE master;
--DROP LOGIN host_jingxiang_login
CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_jingxiang_user
CREATE USER host_jingxiang_user FOR CERTIFICATE host_jingxiang_cert;
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
GO
2.3见证服务器
--在见证服务器 host_jianzheng 上为主体服务器 host_zhu 创建一个登录名。
USE master;
--DROP LOGIN host_zhu_login
CREATE LOGIN host_zhu_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_zhu_user
CREATE USER host_zhu_user FOR LOGIN host_zhu_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE host_zhu_cert
CREATE CERTIFICATE host_zhu_cert
AUTHORIZATION host_zhu_user
FROM FILE = 'D:\DbMirror\host_zhu.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_zhu_login];
GO
--在见证服务器 host_jianzheng 上为镜像服务器 host_jingxiang 创建一个登录名。
USE master;
--DROP LOGIN host_jingxiang_login
CREATE LOGIN host_jingxiang_login WITH PASSWORD = '435sa4f4s3d4f';
GO
--创建一个使用该登录名的用户。
--DROP USER host_jingxiang_user
CREATE USER host_jingxiang_user FOR LOGIN host_jingxiang_login;
GO
--使证书与该用户关联。
--DROP CERTIFICATE host_jingxiang_cert
CREATE CERTIFICATE host_jingxiang_cert
AUTHORIZATION host_jingxiang_user
FROM FILE = 'D:\DbMirror\host_jingxiang.cer'
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_jingxiang_login];
GO
/****************************不操作***************************/
3配置数据库登录信息
在主体数据库中查询 sid和name
USE master;
select sid,name from syslogins;
在备份数据库执行
USE master;
exec sp_addlogin
@loginame = 'sa',
@passwd = '123456',
@sid = 0x01 ;
/*******************************************************/
4备份数据库
/****************************不操作***************************/
法一: --未用
主体数据库中执行
USE MASTER;
GO
BACKUP DATABASE test5
TO DISK = 'D:\DbMirror\DB.bak'
WITH INIT
GO
BACKUP LOG test5
TO DISK = 'D:\DbMirror\DB_log.bak'
WITH INIT
GO
在镜像数据库
还原的时候必须把数据库和事务日志以NoRecovery的形式还原
/*******************************************************/
法二: ---正常
-- 主机备份
USE master
GO
BACKUP DATABASE [test5] TO DISK = N'D:\DbMirror\DB2.bak'
WITH FORMAT, INIT, NAME = N'test5-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
BACKUP LOG [test5] TO DISK = N'D:\DbMirror\DB2.bak'
WITH NOFORMAT, NOINIT, NAME = N'test5-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
-- 镜像恢复
USE master
GO
RESTORE DATABASE [test5] FROM DISK = N'D:\DbMirror\DB2.bak'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [test5] FROM DISK = N'D:\DbMirror\DB2.bak'
WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
/****************************不操作***************************/
5配置伙伴服务器
执行顺序为 镜像-》主体-》见证
在镜像服务器配置
ALTER DATABASE test5
SET PARTNER = 'TCP://192.168.1.167:5039';
GO
在主体服务器配置
ALTER DATABASE test5
SET PARTNER = 'TCP://192.168.1.128:5039';
GO
在见证服务器配置--错
ALTER DATABASE [test5]
SET WITNESS = 'TCP://192.168.1.128:5041';
GO
到此服务器配置成功
/*******************************************************/
6.在sqlserver manage stdio 中进行镜像设置;
删除证书 在master | Security | Certificates
删除用户 在master | User
然后可以删除登录名 drop login <login_name>
修改master key : alter master key drop encryption by service master key
删除master key : drop master key
删除镜像的命令: alter databse <dbname> set partner off
恢复中-->还原中:
ALTER DATABASE test5 SET PARTNER OFF
还原中-->正常状态:
RESTORE database test5 with recovery