SQL SERVER2008 镜像之旅
两台服务器，没有域环境，如何配置SQL SERVER2008镜像呢？
详细过程如下：
SQL 2008配置有证书镜像的步聚共有四步：
1. 配置出站连接：创建主秘钥、证书和端点，备份证书并拷贝到另一台服务器
2. 配置入站连接：分配登陆用户、赋予登陆用户connect本机端点的权限
3. 创建镜像数据：备主数据库，还原镜像数据库
4. 配置镜像伙伴关系：顺序：镜像----主体
5. 测试镜像：切换镜像
运行环境：
主数据库服务器： 192.168.1.2
镜像数据库服务器： 192.168.1.3
以下是测试的详细脚本：
============================================================================
一．配置出站：配置完证书后，分别互存到各数据库服务器的对应路径，注意端口
============================================================================
配置HOST_2出站
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord123';
GO
CREATE CERTIFICATE HOST_2_cert
WITH SUBJECT = 'HOST_2 certificate for database mirroring';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5023
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_2_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_2_cert TO FILE = 'D:\CERT\HOST_2_cert.cer';
GO
配置HOST_3出站
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord123';
GO
CREATE CERTIFICATE HOST_3_cert
WITH SUBJECT = 'HOST_3 certificate for database mirroring';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5023
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_3_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_3_cert TO FILE = 'D:\CERT\HOST_3_cert.cer';
GO
=============================================================================
二．入站镜像连接配置服务器：注意证书的路径，登录账户的命名
=============================================================================
HOST_2的入站配置：
USE master;
CREATE LOGIN HOST_3_LOGIN
WITH PASSWORD = 'mainone%123';
GO
USE master;
CREATE USER HOST_3_USER FOR LOGIN HOST_3_LOGIN;
GO
CREATE CERTIFICATE HOST_3_cert
AUTHORIZATION HOST_3_USER
FROM FILE = 'D:\CERT\HOST_3_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_3_LOGIN
GO
==============================================================
HOST_3的入站配置：
USE master;
CREATE LOGIN HOST_2_LOGIN
WITH PASSWORD = 'mainone%123';
GO
USE master;
CREATE USER HOST_2_USER FOR LOGIN HOST_2_LOGIN;
GO
CREATE CERTIFICATE HOST_2_cert
AUTHORIZATION HOST_2_USER
FROM FILE = 'D:\CERT\HOST_2_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_2_LOGIN
GO
====================================================================
三．配置数据库：备份包括数据库和日志
=====================================================================
备份主数据库HOST_2：
USE master;
GO
ALTER DATABASE DBNAME
SET RECOVERY FULL;
GO
BACKUP DATABASE DBNAME
TO DISK = 'e:\DBNAME.bak'
WITH FORMAT
GO
BACKUP LOG DBNAME
TO DISK = 'e:\DBNAME_log.bak'
GO
还原镜像数据库HOST_3：
RESTORE DATABASE DBNAME
FROM DISK = 'E:\DBNAME.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM DISK = 'E:\DBNAME_log.bak'
WITH FILE=1, NORECOVERY
GO
===========================================================================
四．配置镜像：顺序：镜像----主体
===========================================================================
先执行HOST_3服务器：
ALTER DATABASE DBNAME
SET PARTNER = 'TCP://192.168.1.2:5023'
GO
再执行HOST_2服务器：
ALTER DATABASE DBNAME
SET PARTNER = 'TCP://192.168.1.3:5023' SAFETY OFF
GO
====================================================================
五．测试镜像:在主数据库上执行以下代码可以强制将镜像数据库启用为主数据库，可以查看数据同步的完整性。
=============================================================================
主数据库HOST_2执行:
USE master;
ALTER DATABASE DBNAME
SET PARTNER FAILOVER
GO
2死机之后，在3 上执行 ：强行将镜像服务器提升为主服务器：
ALTER DATABASE DBNAME
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
