两台服务器,没有域环境,如何配置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(此处添加“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