Sql Server 2008 数据库镜像

发表于2013年7月6日由kaisin

系统：win2008企业完全版
sql版本：Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)Enterprise Evaluation Edition (64-bit) Server Pack 1
网络环境：工作组模式
主服务器:   主机名：dbmaster  全名：dbmaster.test.com  IP:10.10.1.175
从服务器：  主机名：dbslave   全名：dbslave.test.com   IP:10.10.1.176
见证服务器：主机名：dbwint    全名：dbslave.test.com   IP:10.10.1.177
需要配置主从的数据库： student

第一步：配置好IP，无需设置工作组，但是必须设置主机名，主机名的设置为：
  右键“计算机”-“属性”-“更改设置”-“更改”--输入主机名dbmaster，
  设置dns后缀：
  “开始”-右键“计算机”-“属性”-“更改设置”-“更改”-"其他"-输入"test.com"，确定重启
  从服务器和见证服务器依次按照上述要求设置好！
 
第二步：配置三台服务器的时间同步，单机右下角的时间-”更改日期和时间设置“-”Internet 时间“-”更改设置“-”立即更新“，所有服务器都这样设置同步时间

第三步：所有服务器都关闭ipv6：
  ”开始“-右键“网络”-”属性“-”更改适配器设置“-右键”本地连接“-”属性“-取消勾选”Internet 协议版本6(TCP/IPv6)“
  ”开始“-”运行“-输入”cmd“回车，输入下列内容，每行输入后回车：
      netsh interface teredo set state disable
  　　netsh interface 6to4 set state disabled
　　  netsh interface isatap set state disabled

第四步：配置host文件解析(三台服务器都要操作)：
  在位置C:\Windows\System32\drivers\etc文件夹下用记事本打开hosts文件，在末尾添加如下：
  127.0.0.1 localhost
  10.10.1.175 dbmaster.test.com
  10.10.1.176 dbslave.test.com
  10.10.1.177 dbwint.test.com
  添加完毕后，记得ping一下另外两台的完整的主机名，看能不能解析到
 
第五步：关闭windows防火墙(你也可以配置防火墙)
  “开始”-“管理工具”-“高级安全 Windows 防火墙”-“windows防火墙属性”-把所有的选项卡的“防火墙状态”都设置关闭
*/

-- 1：为dbmaster配置出站连接
use master;
create master key encryption by password = 'mypassword';

-- 1.1：为dbmaster服务器实例创建证书
create certificate HOST_A_cert with subject = 'HOST_A_certificate',
start_date = '11/28/2012',expiry_date='01/01/2099';

-- 1.2：为dbmaster使用上面创建的证书为数据库实例创建镜像端点
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 );

-- 1.3：备份dbmaster上的证书并自行拷贝到dbslave和dbwint服务器上面，我拷贝过去的证书也放在"D:\"目录下
BACKUP CERTIFICATE host_A_cert TO FILE = 'D:\Host_A_cert.cer';

 

 

-- 2：为dbslave配置出战连接
use master;
create master key encryption by password = 'mypassword';

-- 1.1：为dbslave服务器实例创建证书
create certificate HOST_B_cert with subject = 'HOST_B_certificate',
start_date = '11/28/2012',expiry_date='01/01/2099';

-- 1.2：为dbslave使用上面创建的证书为数据库实例创建镜像端点
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 );

-- 2.3：备份dbslave上的证书并自行拷贝到dbmaster和dbwint服务器上面，我拷贝过去的证书也放在"D:\"目录下
BACKUP CERTIFICATE host_B_cert TO FILE = 'D:\Host_B_cert.cer';

 

 

-- 3：为dbwint配置出战连接
use master;
create master key encryption by password = 'mypassword';

-- 3.1：为dbwint服务器实例创建证书
create certificate HOST_C_cert with subject = 'HOST_C_certificate',
start_date = '11/28/2012',expiry_date='01/01/2099';

-- 3.2：为dbwint使用上面创建的证书为数据库实例创建镜像端点
create endpoint endpoint_mirroring
state = started
as
TCP ( LISTENER_PORT=5022 ,LISTENER_IP = ALL )
for
DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_C_cert,
ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

-- 3.3: 备份dbwint上的证书并自行拷贝到dbmaster和dbslave服务器上面，我拷贝过去的证书也放在"D:\"目录下
BACKUP CERTIFICATE host_C_cert TO FILE = 'D:\Host_C_cert.cer';

 

-- 4：  为dbmaster配置入站连接
-- 4.1：在dbmaster上为dbslave创建登陆
use master;
create login HOST_B_login WITH PASSWORD = 'mypassword';

-- 4.1.1：为以上用户创建一个用户
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

-- 4.1.2：绑定证书到用户
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';

-- 4.1.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_B_login];
 
 
-- 4.2：在dbmaster上为dbwint创建登陆：
use master;
create login HOST_C_login WITH PASSWORD = 'mypassword';

-- 4.2.1：为以上用户创建一个用户
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

-- 4.2.2：绑定证书到用户
CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer';

-- 4.2.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_C_login];

 

-- 5：  为dbslave配置入站连接
-- 5.1：在dbslave上为dbmaster创建登陆
use master;
create login HOST_A_login WITH PASSWORD = 'mypassword';

-- 5.1.1：为以上用户创建一个用户
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

-- 5.1.2：绑定证书到用户
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';

-- 5.1.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_A_login];
 
 
-- 5.2：在dbmaster上为dbwint创建登陆：
use master;
create login HOST_C_login WITH PASSWORD = 'mypassword';

-- 5.2.1：为以上用户创建一个用户
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

-- 5.2.2：绑定证书到用户
CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer';

-- 5.2.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_C_login];

 

-- 6：  为dbwint配置入站连接
-- 6.1：在dbwint上为dbmaster创建登陆
use master;
create login HOST_A_login WITH PASSWORD = 'mypassword';

-- 6.1.1：为以上用户创建一个用户
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

-- 6.1.2：绑定证书到用户
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';

-- 6.1.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_A_login];
 
 
-- 6.2：在dbwint上为dbslave创建登陆：
use master;
create login HOST_B_login WITH PASSWORD = 'mypassword';

-- 5.2.1：为以上用户创建一个用户
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

-- 5.2.2：绑定证书到用户
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';

-- 5.2.3：为刚创建的用户赋予connect的权限
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [HOST_B_login];

-- 7：  在dbmaster上备份数据库
-- 7.1：修改student数据库使用完整恢复模式
alter database student set recovery full with no_wait

-- 7.2：在dbmaster上备份数据库(完整备份)
BACKUP DATABASE student TO  DISK = N'D:\student.bak' WITH NOFORMAT, NOINIT,
NAME = N'student-完整备份',SKIP,NOREWIND,NOUNLOAD,STATS = 10;

-- 7.3：在dbmaster上备份主数据库的完整日志文件
BACKUP LOG student TO  DISK = N'D:\studentlog.bak' WITH NOFORMAT, NOINIT,
NAME = N'student-事务日志备份',SKIP,NOREWIND,NOUNLOAD,STATS = 10;

-- 8.1：在dbslave上恢复student数据库(须先把dbmaster上备份的文件传到dbslave的"D:\"下)：
RESTORE DATABASE student FROM  DISK = N'D:\student.bak' WITH  FILE = 1,NORECOVERY,NOUNLOAD,REPLACE,STATS = 10;

-- 8.2：在dbslave上恢复student的日志文件
RESTORE LOG student FROM  DISK = N'D:\studentlog.bak' WITH  FILE = 1,NORECOVERY,NOUNLOAD,STATS = 10;
--关于数据库日志的备份还原你也可以参照网上的教程直接在图形界面操作

--【
--如果你连接数据库的用户身份是自建的话，则你需要给dbslave上添加用户：
--在dbmaster上执行：
USE master
select sid,name from syslogins;
--得到用户的sid

--然后在dbslave上添加该用户,并设置好权限(关于设置权限，请自行百度)
USE master;
exec sp_addlogin
@loginame = 'xiangyu123',
@passwd = 'mypassword',
@sid = 'sid值'

--】

-- 9：设置dbslave上的partner(伙伴服务器)，必须先设置从服务器
ALTER DATABASE student SET PARTNER = 'TCP://10.10.1.175:5022';

-- 10：设置dbmaster上的partner和witness服务器
ALTER DATABASE student SET PARTNER = 'TCP://10.10.1.176:5022';
ALTER DATABASE student SET WITNESS = 'TCP://10.10.1.177:5022';

--over   是不是很简单？
--

--关于查看数据库的镜像端点和证书的操作(以下操作请务必要注意顺序)

--查看证书
select * from master.sys.certificates

--查看数据库镜像端点
select * from master.sys.endpoints

--删除端点
drop endpoint endpoint_mirroring

--删除证书
drop certificate host_1_cert
drop certificate hosts_2_cert

--删除登陆用户
drop login host_1_login
drop user host_1_user
drop master key

--手动切换主服务器和镜像服务器
------主服务器上执行
USE master; 
ALTER DATABASE student SET SAFETY FULL;-----切换到高安全模式否则执行手动切换会失败 
GO
ALTER DATABASE student SET PARTNER FAILOVER  ---手动进行主备切换 
------镜像服务器上执行： 
USE master;    
ALTER DATABASE student SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS ---在镜像机上执行强制切换(当主服务器数据宕机时) 
 
 
-------如果原来的主服务器恢复,可以继续工作,需要重新设定镜像(其实可以不用重新设置，但是原主现在会变成镜像，不会抢占)
----备机（镜像服务器）上执行： 
--恢复镜像      
 USE master;    
 ALTER DATABASE student SET PARTNER RESUME   
--切换主备 
 ALTER DATABASE student SET PARTNER FAILOVER  
------------------------删除数据库镜像 
  ALTER DATABASE student SET PARTNER OFF 
--暂停数据库镜像会话 
 ALTER DATABASE student SET PARTNER SUSPEND  
--恢复数据库镜像会话 
 ALTER DATABASE student SET PARTNER RESUME 
 ALTER DATABASE student SET PARTNER SUSPEND  
--关闭见证服务器 
 ALTER DATABASE student SET WITNESS OFF   
  
 
/* 
 
默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。 
关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。 
*/ 
--事务安全,同步模式   
 USE master;    
 ALTER DATABASE TestMirroring SET PARTNER SAFETY FULL  
--事务不安全,异步模式   
 ALTER DATABASE TestMirroring SET PARTNER SAFETY OFF; 
 
--以下内容适用于没有见证服务器的主从
/*
镜像与主体切换

--主机

use master;

alter database backuptest set partner failover;

执行成功后原主体数据库会显示正在还原，备机数据库显示主体正在同步字样

 

10.主机崩溃，强制备机当主机，原主机恢复后再切换回去

主机A

备机B

停止主机A的SQL SERVER 服务(比如断电)，此时备机上的数据库会显示正在恢复状态，大概持续几十秒

此时，在B机上执行

use master;

alter database backuptest set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收

最后变成backuptest(主体，已断开链接),即现在的镜像B可以用来充当主机了

假如现在有业务往数据库里插也是能成功的

现在把原主机A恢复(通电)，然后在B机里操作

use master;

alter database backuptest set partner resume;

此时A机是作为镜像的，B机是作为主机的，要再切换一下，则再在B上执行

alter database backuptest set partner failover;

---------------------------------------------------

 

--设置伙伴服务器
ALTER DATABASE [db] SET PARTNER = 'TCP://192.168.2.233:5022';

--设置见证服务器
ALTER DATABASE [db] SET WITNESS = 'TCP://192.168.2.232:5022';

--关闭镜像服务器
alter database [db] set partner off

--为数据库关闭见证服务器
ALTER DATABASE [db] SET WITNESS off;

-----------------------------------------------------

--删除用户
drop login HOST_B_login
drop user HOST_B_user

drop login HOST_C_login
drop user HOST_C_user

drop master key

------------------------------------------------------

来源：http://www.cnblogs.com/renyb/archive/2012/10/19/2731517.html

sqlserver 2008 设置了镜像，但日志文件增长过快，磁盘空间很快就用完了，
如果要清空日志，必须把恢复模式改成简单，但设置了镜像就无法更改，只能是完整
所以要对日志文件进行备份，每15分钟或1个小时备份一次
然后再用下面语句清理日志，这里我只收缩到4G，

DECLARE @I INT SET @I=1
WHILE @I<3
BEGIN
  BACKUP LOG 库名 TO  DISK = N'F:\X.bak'  
  DBCC SHRINKFILE('库名_Log', 4096)
  SET @I=@I+1
END

1、日志备份会清理日志中已经完成的部分，活动的以及还没有传递到镜像的日志将保留（没及时传送到镜像的就是活动的）
2、在镜像环境下每15分钟或1小时做一个日志备份，事务日志及时备份可以截断活动日志，让日志文件内标记非活动的虚文件可重复使用
3、镜像持续可用的话，多保留一次备份。

 

此条目发表在db分类目录，贴了, 标签。将固定链接加入收藏夹。