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

SQL-Server2008-镜像数据库实施手册(双机)一、配置主备机1、?服务器基本信息主机名称为：HOST_A，IP地址为：55备机名称为：HOST_B，IP地址为：56二、主备实例互通实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个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_MirroringSTATE = STARTEDAS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); --备机执行：CREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDAS 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;GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 三、建立镜像关系以下步骤是针对每个数据库进行的