SQLServer 数据库镜像（一）单服务器无见证的镜像脚本配置

SQLServer 数据库镜像（一）单服务器无见证的镜像脚本配置

SQLServer 数据库镜像（二）域环境中完整镜像脚本配置

/*数据库镜像*/

/**************************************【测试环境】******************************************/

--同一台服务器,无见证服务器：
Windows Server 2003 32bit,SQLServer 2008R2 IP：192.168.1.10,ServerName: kk-db1

主服务器:
InstanceName = MSSQLSERVER
LISTENER_PORT = 5022

镜像服务器: 
InstanceName = MSSQLSERVERA
LISTENER_PORT = 5023

/***************************************【主数据库准备】******************************************/
USE [master]
GO
CREATE DATABASE [DBName]
 ON  PRIMARY 
( 
	  NAME = N'DBName'
	, FILENAME = N'C:\Databases\MSSQLSERVER\DBName.mdf' 
	, SIZE = 5120KB 
	, MAXSIZE = UNLIMITED
	, FILEGROWTH = 1024KB 
)
 LOG ON 
(
  NAME = N'DBName_log'
  , FILENAME = N'C:\Databases\MSSQLSERVER\DBName_log.ldf'
  , SIZE = 5120KB 
  , MAXSIZE = 4GB 
  , FILEGROWTH = 10%
 )
GO

--设置数据库恢复模式为“完整”模式：
ALTER DATABASE [DBName] SET RECOVERY FULL
GO

--创建测试表
USE [DBName]
GO
CREATE TABLE [dbo].[MyTable](
	[Guid]	[uniqueidentifier] NOT NULL,
	[SID]	[varbinary](85) NOT NULL,
	[Name]	[nvarchar](50) NULL,
	CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([Guid] ASC,[SID] ASC)ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Guid]  DEFAULT (newid()) FOR [Guid]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_SID]  DEFAULT (suser_sid()) FOR [SID]
GO

INSERT INTO [dbo].[MyTable]([Name]) SELECT 'KK'
GO 5

--	SELECT * FROM [dbo].[MyTable]


/***************************************【镜像数据库准备】**************************************/
--主服务器（备份）
USE [master]
GO
BACKUP DATABASE [DBName]
TO DISK = 'C:\Databases\MSSQLSERVER\DBName.BAK' WITH INIT,FORMAT
GO

 --镜像服务器(还原：NORECOVERY)
USE [master]
GO
RESTORE DATABASE [DBName]
FROM DISK = 'C:\Databases\MSSQLSERVER\DBName.BAK'
WITH FILE = 1,
MOVE 'DBName' TO 'C:\Databases\MSSQLSERVERA\DBName.mdf',
MOVE 'DBName_log' TO 'C:\Databases\MSSQLSERVERA\DBName_log.ldf',
NORECOVERY


/***************************************【登录账户准备】**************************************/
--数据库镜像可以使用两种类型的传输安全功能：Windows 身份验证或基于证书的身份验证
--若使用非域帐户运行SQL Server，则必须使用证书.(当前都使用内置账户：Network Service)
--由于未加入域，分别创建2个SQLserver登录账户[usermirror]：

--主服务器实例:  
USE [master]
GO
CREATE LOGIN [usermirror] WITH PASSWORD=N'usermirror'
GO

--镜像服务器实例: 
USE [master]
GO
CREATE LOGIN [usermirror] WITH PASSWORD=N'usermirror'
GO

/***************************************【端点准备】**************************************/
--每个服务器实例需要且只需要一个唯一的侦听器端口。
--对于 Windows 身份验证，AUTHENTICATION 选项是可选的
--尽管 RC4 远远快于 AES，但它是一个相对较弱的算法，而 AES 是一个相对较强的算法
--更多参考：GRANT 端点权限 (Transact-SQL) https://msdn.microsoft.com/zh-cn/library/ms187811(v=sql.105).aspx
--更多参考：CREATE ENDPOINT(Transact-SQL) https://msdn.microsoft.com/zh-cn/library/ms181591(v=sql.105).aspx

--在【主服务器实例】执行，创建的端点并授予连接权限给SQL账户: 
USE [master]
GO
CREATE ENDPOINT [Endpoint_For_Mirror] 
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [usermirror];
GO


--在【镜像服务器实例】执行，创建的端点并授予连接权限给SQL账户: 
USE [master]
GO
CREATE ENDPOINT [Endpoint_For_Mirror] 
STATE=STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)--同一个服务器不同实例，不同端口
FOR DATABASE_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [usermirror];
GO

/***************************************【开始镜像】**************************************/
--镜像开始后,只有主体能访问.
--更多参考：ALTER DATABASE 数据库镜像 (Transact-SQL)https://msdn.microsoft.com/zh-cn/library/bb522476.aspx

--在【镜像服务器实例】执行,PARTNER为主服务器
USE [master]
GO
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.1.10:5022';
GO

--在【主服务器实例】执行,PARTNER为镜像服务器
USE [master]
GO
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.1.10:5023';
GO
--在主体执行:设置为高安全模式
ALTER DATABASE [DBName] SET SAFETY FULL

--在主备执行:创建监视镜像数据库作业
use msdb;
exec sys.sp_dbmmonitoraddmonitoring
exec sys.sp_dbmmonitorhelpmonitoring
exec sys.sp_dbmmonitorresults DBName,0,0
--exec sys.sp_dbmmonitorchangemonitoring
--exec sys.sp_dbmmonitordropmonitoring

/***************************************【数据测试】**************************************/

--在主体操作:随意进行DDL操作进行测试
USE DBName;
SELECT * FROM [dbo].[MyTable]

UPDATE [dbo].[MyTable] SET NAME = 'master'
DELETE TOP(1) FROM [dbo].[MyTable]


--主备切换
USE [master]
GO
ALTER DATABASE DBName SET PARTNER FAILOVER; 
GO

--在镜像操作:查看数据更改情况



--现在模拟主体不能访问的情况,将主体服务停止.

--在原镜像操作,将正在还原的镜像设置允许访问.
--在镜像随意进行DML操作
USE master;
ALTER DATABASE DBName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;



--现在将原主体的实例和代理启动,模拟主体恢复正常
--登录主体实例查看,此时原来的"主体"变为了"镜像",并处于"挂起"状态

--在原镜像操作:现在把刚恢复的"镜像"更改回原来的"主体".
USE master;
ALTER DATABASE DBName SET PARTNER RESUME;
ALTER DATABASE DBName SET PARTNER FAILOVER;

更多参考：数据库镜像 (SQL Server)