SQL-Server2008-镜像数据库实施手册(双机).docx

SQL-Server2008-镜像数据库实施手册(双机)一、配置主备机1、?服务器基本信息主机名称为：HOST_A，IP地址为：192.168.1.155备机名称为：HOST_B，IP地址为：192.168.1.156二、主备实例互通实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个SQL Server 2008的实例中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每一对主备实例（不是数据库）做一次互通。1、创建证书（主备可并行执行）--主机执行：USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 123456; CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = HOST_A certificate , START_DATE = 2012-08-02, EXPIRY_DATE = 2099-08-02; --备机执行：USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 123456; CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = HOST_B certificate, START_DATE = 2012-08-02, EXPIRY_DATE = 2099-08-02; 2、创建连接的端点（主备可并行执行）--主机执行：?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、备份证书以备建立互联（主备可并行执行）--主机执行：BACKUP CERTIFICATE HOST_A_cert TO FILE = C:\SQLBackup\HOST_A_cert.cer; --备机执行：BACKUP CERTIFICATE HOST_B_cert TO FILE = C:\SQLBackup\HOST_B_cert.cer;4、互换证书将备份到C:\SQLBackup\的证书进行互换，即HOST_A_cert.cer复制到备机的C:\SQLBackup\。HOST_B_cert.cer复制到主机的C:\SQLBackup\。5、添加登陆名、用户（主备可并行执行）以下操作只能通过命令行运行，通过图形界面无法完成。（截至SQL Server2005的补丁号为SP2）--主机执行：CREATE LOGIN HOST_B_login WITH PASSWORD = 123456; 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 = 123456; 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; G