SQL server镜像备份

此只有两台服务器，没有见证服务器的高安全模式

准备工作：

1：准备两台机器

主机

host_A 192.168.0.181

备份机

host-B 192.168.0.199

2：检查版本 要求一致只能是标准版，企业版和开发版才能建立数据库镜像

select @@version;

3：测试网络和端口

ping:检查网络 telnet:检查端口

4：磁盘检查 数据库需要放在同一个磁盘中，不然很容易出现问题

USE master

go

SELECT physical_name--物理文件路径

FROM sys.master_files

WHERE database_id = DB_ID('tangshi')

5:检查主机的恢复模式和兼容级别

USE master

go

SELECT name [数据库名] ,

recovery_model_desc [恢复模式] ,

CASE WHEN [compatibility_level] = 90 THEN '2005'

WHEN [compatibility_level] = 100 THEN '2008'

WHEN [compatibility_level] > 100 THEN '2008+'

ELSE '2000 or lower version'

END [兼容级别]

FROM sys.databases

WHERE name = 'tangshi'

兼容模式一定要改成完整恢复模式

USE [master]

GO

ALTER DATABASE [tangshi] SET RECOVERY FULL WITH NO_WAIT

GO

正式搭建工作

1：创建证书

------------------------主机执行---------------------------------------------------------------------

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill'; --非常重要测试环境下可以简单，但是正式环境下

--密钥一定要复杂，非常复杂

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,

START_DATE = '2018-06-02',

EXPIRY_DATE = '2099-08-02';---这个是过期时间，有些证书设计时间短，需要改但是安全，时间长就是不需要改

如果有证书和主密钥，则要先删除

--DROP ENDPOINT Endpoint_Mirroring

--drop master key;

---DROP CERTIFICATE "HOST_A_cert"

如果删除证书失败

查看终端点 select * from sys.endpoints

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

-----------------------备份机行---------------------------------------------------------------------

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',

START_DATE = '2012-08-02',

EXPIRY_DATE = '2099-08-02';

2：创建端点

可以使用下面的代码在主体服务器中创建端点，并且指定使用5022,端口，端口在镜像配置过程中不强制使用特定端口（被占用或者特定端口如1433除外）。

--------------------------------------主机执行------------------------------------------------------

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备份证书以备建立互联（主备可并行执行）

------------------------------------主机执行--------------------------------------------------------

1BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\SQLBackup\HOST_A_cert.cer';

------------------------------------备机执行--------------------------------------------------------

1BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\SQLBackup\HOST_B_cert.cer';

4、互换证书

将备份到D:\SQLBackup\的证书进行互换，即HOST_A_cert.cer复制到备机的D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\

5、添加登陆名、用户（主备可并行执行）

-----------------------------------主机执行：----------------------------------------------

CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\SQLBackup\HOST_B_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

------------------------------------备机执行：---------------------------------------------------

CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\SQLBackup\HOST_A_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

三、建立镜像关系

以下步骤是针对每个数据库进行的，例如：现有主机中有5个数据库以下过程就要执行5次。

1、 手工同步登录名和密码！！！！

在第一章中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"，这个很重要，当初就是因为我没有这一步 导致后来的主机怎么都连接不上

--------------------------------主机上执行---------------------------------------------------------

USE master;

select sid,name from syslogins;

-----------------------------------备份机上执行-----------------------------------------------------

1USE master;

2exec sp_addlogin

3@loginame = 'tan', ------主机上数据库tangshi的用户名，仅举例说明

4@passwd = 'password', -----用户密码

2、 准备备机数据库

---------------------------------主机上执行---------------------------------------------------------

备份数据库，完整模式

备份事务日志 将“备份类型”选成“事务日志”且备份目录与备份数据库的目录一致。

-------------------------------------备份机上执行----------------------------------------------------

将主机的备份文件拷贝到备机上，在备机上使用主机的全备文件进行还原，在还原数据的时候需要使用选上“with non recover”。

覆盖现有数据库

不对数据进行任何操作，不会滚来提交的事务，可以还原其他事务日志

如果执行成功会显示 --正在还原

3、 建立镜像

-----------------------------------------------主机执行-----------------------------------

1ALTER DATABASE tangshi SET PARTNER = 'TCP://192.168.0.199:5022';

备注 tangshi:是镜像数据库 192.168.0.199： 这是备份机的ip

--如果主体执行不成功，尝试在备机中执行如下语句：

1ALTER DATABASE tangshi SET PARTNER = 'TCP://192.168.0.181:5022';

备注：192.168.0.181 这是主机ip

测试操作

--主机执行：

USE master;

ALTER DATABASE SET PARTNER FAILOVER;

2、主服务器Down掉,备机紧急启动并且开始服务

--备机执行：

USE master;

ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

1--备机执行：

USE master;

ALTER DATABASE SET PARTNER RESUME; --恢复镜像

ALTER DATABASE SET PARTNER FAILOVER; --切换主备

4、原来的主服务器恢复,可以继续工作

--默认情况下，事务安全级别的设置为 FULL，即同步运行模式，

--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

USE master;

ALTER DATABASE SET PARTNER SAFETY FULL; --事务安全,同步模式

ALTER DATABASE SET PARTNER SAFETY OFF; --事务不安全,异步模式