sql2008数据库镜像.docx

下面主要是2008 MSSQL的一个镜像安装步骤，使用到的工具是mssql自带的Management Studio域环境安装较为简单，所有登陆账户为域账户即可，如非域环境，需要匹配登陆账户（第6步）。镜像安装的环境:主机：win server 2008,sql server 2008，IP:192.168.1.173 PC账户：jlsy\administrator备机：win server 2008,sql server 2008，IP:192.168.1.174 PC账户：jlsy\administrator见证服务器：win server 2008,sql server 2008，IP:192.168.1.164 PC账户：jlsy\administrator说明：a.镜像服务器备份的是用户的数据库，不是系统的数据库，比如不能镜像master,msdb,model和tempdbb.镜像的数据库对象恢复模式必须是完整的，不能是简单和大容量日志类型的c.备机镜像不能被访问，正常情况下一直处于正在还原的状态d.主机可以被访问，正常情况下一直处于主体正在同步的状态e.主机和备机的5022端口必须没有被占用，可以用telnet 192.168.9.182 5022来验证0.确定主机的恢复模式是完整性恢复的USE master;ALTER DATABASE backuptest SET RECOVERY FULL;1.创建证书，实现互通的根本--主机USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = abc123!@#;CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = HOST_A certificate ,?START_DATE = 09/20/2011,EXPIRY_DATE = 01/01/2099;--备机USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = abc123!@#;CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = HOST_B certificate,?START_DATE = 09/20/2011?,EXPIRY_DATE = 01/01/2099;--见证USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = abc123!@#;CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = HOST_C certificate,?START_DATE = 09/20/2011?,EXPIRY_DATE = 01/01/2099;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 );--见证CREATE ENDPOINT Endpoint_Mirroring?STATE = STARTED?AS?TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )?FOR?DATABASE_MIRRORING?( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );3.备份证书用来互换--主机BACKUP CERTIFICATE HOST_A_cert TO FILE = D:\HOST_A_cert.cer;--备机BACKUP CERTIFICATE HOST_B_cert TO FILE = D:\HOST_B_cert.ce