wanyong117

清风随我度

导航

统计

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

posted on 2010-07-03 21:30  我的.net之旅  阅读(...)  评论(...编辑  收藏