SQL SERVER2008 镜像全攻略

 SQL SERVER2008 镜像全攻略

[sql] view plaincopy

  1. 在非域控环境中创建数据库镜像, 我们必须使用证书来创建数据库镜像。 大致的步骤包括:  

  2.   

  3. 在为数据库镜像配置的每个服务器实例上执行下列步骤:  

  4.   

  5. 在 master 数据库中,创建数据库主密钥。  

  6. 在 master 数据库中,为服务器实例创建加密证书。  

  7. 使用服务器实例的证书为该服务器实例创建端点。  

  8. 将证书备份到文件,并将其安全地复制到其他系统。  

  9.    

  10.   

  11. 然后,对为数据库镜像配置的每个伙伴执行这些步骤。在 master 数据库中:  

  12.   

  13. 为其他系统创建登录名。  

  14. 创建一个使用该登录名的用户。  

  15. 获取其他服务器实例的镜像端点的证书。  

  16. 将该证书与在步骤 2 中创建的用户相关联。  

  17. 授予对该镜像端点的登录名的 CONNECT 权限。  

  18.    

  19.   

  20. /*查询所有当前数据库名  

  21. select SERVERPROPERTY(N'servername')  

  22. SELECT  

  23. dtb.name AS [Name]  

  24. FROM  

  25. master.sys.databases AS dtb  

  26. ORDER BY  

  27. [NameASC  

  28. */  

  29.   

  30.   

  31.    SELECT @@VERSION  

  32.   

  33.    ALTER DATABASE TestMirroring SET SAFETY FULL   

  34.   

  35.    USE master  

  36.     ALTER DATABASE TestMirroring   

  37.     SET RECOVERY FULL WITH no_wait  

  38.   

  39. /*跨数据库事务和分布式事务均不支持数据库镜像  

  40. */  

  41.   

  42.   

  43.   

  44.    BACKUP DATABASE [TestMirroring] TO  DISK = N'C:/wenzhongfiles/TestMirror.bak' WITH NOFORMAT, NOINIT,   

  45.    NAME = N'TestMirroring-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  

  46.   

  47.    BACKUP LOG [TestMirroring] TO  DISK = N'C:/wenzhongfiles/TestMirrorlog.bak' WITH NOFORMAT, NOINIT,   

  48.    NAME = N'TestMirroring-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  

  49.   

  50.    RESTORE DATABASE [TestMirroring] FROM  DISK = N'F:/TestMirror.bak' WITH  FILE = 1,  MOVE N'TestMirror'  

  51.     TO N'E:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/TestMirroring.mdf',  MOVE N'TestMirror_log'  

  52.     TO N'E:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/TestMirroring_1.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10  

  53.   

  54.    RESTORE LOG [TestMirroring] FROM  DISK = N'F:/TestMirrorlog.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10  

  55.   

  56.   

  57.   

  58.   

  59.   

  60.   

  61.   USE master;     

  62.     

  63.   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'wenzhong';     

  64.   GO   

  65.   

  66.     

  67.   CREATE CERTIFICATE host_A_cert      

  68.   WITH SUBJECT = 'host_A certificate',START_DATE = '01/01/2011',EXPIRY_DATE = '12/31/2099';    

  69.   GO     

  70.   

  71.     

  72.   CREATE ENDPOINT Endpoint_Mirroring     

  73.   STATE = STARTED     

  74.   AS TCP (     

  75.   LISTENER_PORT=5022     

  76.   , LISTENER_IP = ALL     

  77.   )      

  78.   FOR DATABASE_MIRRORING (      

  79.   AUTHENTICATION = CERTIFICATE host_A_cert     

  80.   , ENCRYPTION = REQUIRED ALGORITHM AES     

  81.   , ROLE = PARTNER     

  82.   )     

  83.   GO     

  84.   

  85.   

  86.   BACKUP CERTIFICATE host_A_cert TO FILE = 'F:\Host_A_cert.cer'  

  87.   

  88.   USE master;     

  89.     

  90.   CREATE LOGIN host_B_login WITH PASSWORD = 'wenzhong'     

  91.   GO    

  92.   USE master;     

  93.     

  94.   CREATE LOGIN host_C_login WITH PASSWORD = 'wenzhong'    

  95.   GO    

  96.   

  97.     

  98.   CREATE USER host_B_user FOR LOGIN host_B_login;     

  99.   GO   

  100.   CREATE USER host_C_user FOR LOGIN host_C_login;     

  101.   GO   

  102.   

  103.     

  104.   CREATE CERTIFICATE host_B_cert     

  105.   AUTHORIZATION host_B_user     

  106.   FROM FILE = 'F:/host_B.cer'    

  107.   GO   

  108.     

  109.   CREATE CERTIFICATE host_C_cert     

  110.   AUTHORIZATION host_C_user     

  111.   FROM FILE = 'F:/host_C.cer'    

  112.   GO  

  113.   

  114.   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login]     

  115.   GO  

  116.   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login]    

  117.   GO  

  118.   

  119.   

  120.   

  121.   USE master   

  122.   select sid,name from syslogins  

  123.   

  124.   USE master     

  125.   exec sp_addlogin      

  126.   @loginame = '<LoginName>',      

  127.   @passwd = '<Password>',      

  128.   @sid = <sid>    

  129.   

  130.   

  131.   

  132.   

  133.   

  134.   ALTER DATABASE TestMirroring     

  135.   SET PARTNER = 'TCP://192.168.0.211:5022'     

  136.   GO   

  137.   

  138.   ALTER DATABASE TestMirroring     

  139.   SET PARTNER = 'TCP://192.168.0.111:5022'     

  140.   GO   

  141.   

  142.   ALTER DATABASE TestMirroring      

  143.   SET WITNESS = 'TCP://192.168.0.192:5022'     

  144.   GO   

  145.   

  146.   

  147.   

  148.   

  149.   

  150.   

  151.  USE master;     

  152.   

  153. ALTER DATABASE TestMirroring SET SAFETY FULL;  

  154.   

  155. GO  

  156.  ALTER DATABASE TestMirroring SET PARTNER FAILOVER    

  157.   

  158.  USE master;     

  159.  ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS   

  160.   

  161.   

  162.   

  163.   

  164.   

  165.  USE master;     

  166.  ALTER DATABASE TestMirroring SET PARTNER RESUME    

  167.   

  168.  ALTER DATABASE TestMirroring SET PARTNER FAILOVER   

  169.   

  170.   ALTER DATABASE TestMirroring SET PARTNER OFF  

  171.   

  172.    ALTER DATABASE TestMirroring SET PARTNER SUSPEND   

  173.   

  174.    ALTER DATABASE TestMirroring SET PARTNER RESUME  

  175.    ALTER DATABASE TestMirroring SET PARTNER SUSPEND   

  176.   

  177.    ALTER DATABASE TestMirroring SET WITNESS OFF  

  178.   

  179.    

  180.   

  181.    

  182.   

  183. /*  

  184.   

  185. 默认情况下,事务安全级别的设置为 FULL,即同步运行模式,而且SQL Server 2005 标准版只支持同步模式。  

  186. 关闭事务安全可将会话切换到异步运行模式,该模式可使性能达到最佳。  

  187. */  

  188.   

  189.  USE master;     

  190.  ALTER DATABASE TestMirroring SET PARTNER SAFETY FULL   

  191.   

  192.  ALTER DATABASE TestMirroring SET PARTNER SAFETY OFF;  

  193.   

  194.   

  195.   

  196.   

  197.   

  198.   

  199.   ALTER DATABASE TestMirroring SET PARTNER OFF  

  200.     

  201. /*  

  202.  自动故障转移所需条件  

  203.   

  204.   A、数据库镜像会话必须在高安全性模式下运行,并且必须处理见证服务器。  

  205.   B、镜像数据库必须已经同步。这将保证发送到镜像服务器的所有日志都已写入磁盘。  

  206.   C、主体服务器已中断了与其余数据库镜像配置的通信,而镜像服务器和见证服务器将保留仲裁。但是,如果所有服务器实例都已中断通信,  

  207.    而见证服务器和镜像服务器稍后重新建立通信,则不会发生自动故障转移。  

  208.   D、镜像服务器已检测到丢失了主体服务器  

  209.   E、镜像服务器检测主体服务器故障的方式取决于故障是硬故障还是软故障。  

  210.   

  211.  自动故障转移原理  

  212.   

  213.   A、如果主体服务器仍在运行中,则将主体数据库的状态更改为 DISCONNECTED 并断开所有客户端与主体数据库的连接。  

  214.   B、见证服务器和镜像服务器将主体服务器注册为不可用。  

  215.   C、如果重做队列中有任何等待的日志,则镜像服务器将完成前滚镜像数据库的操作  

  216.   D、前一个镜像数据库作为新的联机主体数据库,恢复通过尽快回滚未提交的事务将这些事务全部清除。锁将隔离这些事务。  

  217.   E、当前一个主体服务器重新联接到会话时,它将认定其故障转移伙伴现在拥有主体角色。前一个主体服务器接管镜像角色,并将其数据库作为镜像数据库。  

  218.    新的镜像服务器会尽快将新的镜像数据库与主体数据库同步。新的镜像服务器重新同步数据库后,就可以再次执行故障转移,但按反向执行。。  

  219. */  

  220.   

  221.   

  222.   

  223.   ConnectionString="DataSource= A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=true;   

  224.   

  225.   

  226.   

  227.   

  228.   

  229.   

  230. Imports System.Data.SqlClient   

  231. Imports System.Data   

  232.    

  233. Public Class dbConn   

  234. Private primaryServerLocation As String="SERVER=primaryAddress;DATABASE=yourDB;User id=youruserID;Password=yourPassword;"   

  235. Private secondaryServerLocationAsString="SERVER=secondaryAddress;DATABASE=yourDB;User id=youruserID;Password=yourPassword;"   

  236.    

  237.    

  238. Public sqlConnection AsSqlConnection   

  239. Public cmd AsSqlCommand   

  240.    

  241. Public Sub primaryConnection()   

  242.     Try   

  243.         sqlConnection = New System.Data.SqlClient.SqlConnection(primaryServerLocation)   

  244.         cmd = NewSystem.Data.SqlClient.SqlCommand()   

  245.    

  246.         'test connection   

  247.         sqlConnection.Open()   

  248.         sqlConnection.Close()   

  249.     Catch ex As Exception   

  250.         secondaryConnection()   

  251.     End Try   

  252. End Sub   

  253.    

  254. Public Sub secondaryConnection()   

  255.     'Used as the failover secondary serverif primaryis down.   

  256.     Try   

  257.         sqlConnection = New System.Data.SqlClient.SqlConnection(secondaryServerLocation)   

  258.         cmd = NewSystem.Data.SqlClient.SqlCommand()   

  259.    

  260.         'test connection   

  261.         sqlConnection.Open()   

  262.         sqlConnection.Close()   

  263.     Catch ex As Exception   

  264.     End Try   

  265. End Sub   

  266.   

  267.   

  268.   

  269.     

  270.   

  271.   

  272.   

  273.   

  274.   

  275.  use master   

  276.   go   

  277.   SELECT * FROM sys.database_mirroring_endpoints   

  278.   SELECT * FROM sys.database_mirroring   

  279.   WHERE database_id =(SELECT database_id FROM sys.databases   

  280.           WHERE name = 'TestMirroring')  

  281.   SELECT * FROM sys.database_mirroring_witnesses  

  282.    

  283.   

  284.   

  285.   

  286.    

  287.   

  288. 镜像的运行模式有三种:  

  289.   

  290. 1、 高性能(异步):先提交主服务器上的更改,然后将其传输到镜像服务器上。  

  291.   

  292. 2、不带自动故障转移功能的高安全(同步): 过程始终提交主服务和镜像服务器上的更改。  

  293.   

  294. 3、带自动故障转移功能的高安全(同步):需要见证服务器实例。如果主服务器和镜像服务器都可用,则提交在它们上面所做的更改并镜像。如果主服务器不可用,则见证服务器就会控制自动故障转移到镜像服务器上。  

  295.   

  296.    

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。