SQL Server 2008 镜像操作心得(非域控环境中创建数据库镜像)


下面的文章,我是在网上找的(非域控环境中创建数据库镜像),针对这篇文章,我只想补充几句:

1、主机ip: 192.168.9.181

备机ip:192.168.9.182

2、删除镜像文件语句:
ALTER DATABASE backuptest1 SET PARTNER OFF

3、查询和删除SQL 端口语句:

declare @sql varchar(100)
declare @mirrName varchar(30)
select @mirrName=name from sys.database_mirroring_endpoints
set @mirrName=isnull(@mirrName,'')

if @mirrName<>''
begin
select @sql='drop endpoint '+@mirrName
exec(@sql)
end

4、确定就是只能手动进行数据库主备切换;

----------------------------------------------  下面是具体文章内容------------------------------------------------------------

说明:
a.镜像服务器备份的是用户的数据库,不是系统的数据库,比如不能镜像master,msdb,model和tempdb
b.镜像的数据库对象恢复模式必须是完整的,不能是简单和大容量日志类型的
c.备机镜像不能被访问,正常情况下一直处于正在还原的状态
d.主机可以被访问,正常情况下一直处于主体正在同步的状态
e.主机和备机的5022端口必须没有被占用,可以用telnet 192.168.9.182 5022来验证
0.确定主机的恢复模式是完整性恢复的
USE master;
ALTER DATABASE backuptest SET RECOVERY FULL;
1.创建证书,实现互通的根本
--主机
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'clq';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '09/20/2011';
--备机
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'clq';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '09/20/2011';
2.创建主备连接的端点
--主机
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_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 HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
3.备份证书用来互换
--主机
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\working\HOST_A_cert.cer';
--备机
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\working\HOST_B_cert.cer';
4.主备之间互换证书
将主机上产生的证书HOST_A_cert复制到备机上,将备机上产生的证书HOST_B_cert复制到主机上,放在第5步指定的目录
5.新增主备登陆用户
--主机
CREATE LOGIN HOST_B_login WITH PASSWORD = 'clq';  --主机上登录到备机的用户
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\working\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
--备机
CREATE LOGIN HOST_A_login WITH PASSWORD = 'clq';  --备机上登录到主机的用户
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\working\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
6.登录名维护,即主机与备机有相同的登录用户,防止切换到备机时业务无法登录,假如主备登录用户一致,此步骤可免
--主机
use master;
select sid,name from syslogins where name = 'clq';
sid                                                                                    name
0xC6C9DB1494DA514882D4BEAA362A8FB7        clq
--备机
USE master;
exec sp_addlogin
@loginame = 'clq',
@passwd = 'clq',
@sid = 0xC6C9DB1494DA514882D4BEAA362A8FB7 ;
----以下步骤是针对每个库执行的,有几个库就执行几次
7.准备备机数据库
--主机
backup database backuptest to disk= 'E:\database\sql server\MSSQL10.MSSQLSERVER\MSSQL\Backup\backuptest.log'
with init
go
--备机
将主机上备份的文件复制到备机上,并还原备机数据库,还原时必须指定norecovery参数
create database backuptest;   --该步骤为备机上无此数据时需先创建一个空数据库,若已有则直接恢复
restore database backuptest from disk = 'E:\working\backuptest.log' with norecovery;
恢复时指定覆盖原有备机数据库即可。
当恢复时报错时可以参考用replace参数(首次恢复)
restore database EOL_TCCLUB FROM disk= 'd:\backup\EOL_TCCLUB.bak'
with move 'AC_SSC_BZ' to 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\EOL_TCCLUB.mdf',
move 'AC_SSC_BZ_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\EOL_TCCLUB.ldf',
replace,norecovery.
如果还不行,报错信息如:日志尾部未备份之类信息,则去主机上备份日志文件,在备机上做恢复,恢复时同样指定norecovery。
8.增加镜像伙伴,需要先在备机上执行,再执行主机
--备机
ALTER DATABASE backuptest SET PARTNER = 'TCP://192.168.9.181:5022';
--主机
ALTER DATABASE backuptest SET PARTNER = 'TCP://192.168.9.182:5022';
执行成功以后,主机上数据库backuptest会显示主体正在同步字样,备机数据库会显示正在还原字样。若上述步骤
有报日志错误,则还需从主机上备份日志文件,然后在备机上还原,还原日志时同样需要指定norecovery
9.镜像与主体切换
--主机
use master;
alter database backuptest set partner failover;
执行成功后原主体数据库会显示正在还原,备机数据库显示主体正在同步字样
10.测试主备切换
主机崩溃,强制备机当主机,原主机恢复后再切换回去
主机A
备机B
此时,在B机上执行
use master;
alter database backuptest set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收
停止主机A的SQL SERVER 服务(比如断电),此时备机上的数据库会显示正在恢复状态,大概持续几十秒,最后变成
backuptest(主体,已断开链接),即现在的镜像B可以用来充当主机了
假如现在有业务往数据库里插也是能成功的
现在把原主机A恢复(通电),然后在B机里操作
use master;
alter database backuptest set partner resume; --恢复镜像
此时A机是作为镜像的,B机是作为主机的,要再切换一下,则再在B上执行
alter database backuptest set partner failover; --切换主备

查看终端点 select * from sys.endpoints

删除某终端点(终端点不带引号)   drop endpoint <endpoint_name>

删除证书  在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 <dbname> set partner='TCP://主体服务器IP : 端口号(通常为5022)'

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.9.182:5022';

如果主机执行不成功可在备机执行:

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.9.181:5022';

声明:张拓的天空-ZTsky.cn|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - SQL Server 2008 镜像操作心得(非域控环境中创建数据库镜像)


我在黑夜里急速前行,却追不到你远去的身影。