实验二：SQL server 2005高可用性之----数据库镜像

如转载，请注明出处：http://blog.csdn.net/robinson_0612/archive/2009/11/04/4769060.aspx

    SQL server 2005高可用性之数据库镜像，是SQL server 2005的新技术之一，是一种基于软件的高可用性解决方案，可以对不同服务器或同一服务器不同实例之间的数据库实验无数据延迟，自动故障转移的热备份。数据库镜像是基于数据库级别的，只适用于使用完整恢复模式的数据库。

    一、实验目的：掌握SQL server 2005数据库镜像原理并配置数据库镜像、监控镜像状态及实现故障转移。

    二、数据库镜像的组成

          数据库镜像由二个数据库必须的数据库角色组成，一个是主体服务器角色，一个是镜像服务器角色。还有一个可选的服务器角色为见证服务器角色。

         1. 主体服务器(Principal Role)之主体数据库，主体数据库提供客户端应用程序的连接，查询，更新，执行相关事务等，主体数据库要求使用完全恢复模式。

         2. 镜像服务器(Mirror Role)之镜像数据库，镜像数据库持续同步来自主体数据库的事务，使得镜像数据库的数据与主体数据库保持一致。镜像数据库不允许任何的连接存在，但可以对其创建数据库快照来作为只读数据库，实现用户的相关查询操作。

         3. 见证服务器(Witness Server)，可选的配置，用于高可用性操作模式，通过见证服务器自动侦测故障，实现角色切换和故障转移。一个见证服务器可以为多组镜像提供服务。

         4. 角色的转换。主体数据库与镜像数据库互为伙伴，当见证服务器侦测到主体服务器故障时，在高可用性模式下，实现故障自动转移后，会自动将主体服务器切换为镜像服务器角色，即角色发生了互换。

   三、数据库镜像的工作过程

         1. 主体数据库提供服务，当有来自客户端对主体数据库的更新时，主体数据库将数据写入主体数据库的同时也将事务传送给镜像数据库。

         2. 镜像数据库Redo来自主体数据库的事务，Redo完毕后，并发送消息通知主体服务器。

         3. 主体服务器收到来自镜像服务器中镜像数据写入完毕的消息后，将完成结果反馈给客户端。

    四、端点的作用

          SQL server 2005提供了多层次多级别的安全模式，连接端点便是安全中第一个层次级别，为实例级别，它控制着能否连接到实例。数据库镜像是三个实例级别的会话，故必须通过创建端点来实现互相通信。

         SQL server 2005可以创建两种类型的端点，一个是HTTP端点，一个是TCP端点。我们可以创建TSQL, SERVICE_BROKER, 或 DATABASE_MIRRORING类型的TCP端点。

         端点上安全分为三个层次，一是需要创建所需类型的端点，但该端点并不能提供服务。二是在创建的端点上指定端口号，并指定IP地址，数据库缺省的端口号为5022。三是对已创建并指定IP及端口号采用基于Windows身份认证或数字证书的加密功能加强安全。四是端点的状态必须为启动状态，才能够提供服务，如果端点在停止状态，对任意的连接，将给出错误提示。五是对于已建立的会话必须拥有端点的connect连接权限。

    五、数据库镜像的操作模式

          数据库镜像可以使用三种不同的操作模式，高可用性、高级别保护、高性能模式。在镜像会话期间，故障发生时，不同的操作模式对应着不同的事务转换方式。

         1. 高可用性:在镜像正常会话期间，主体服务器和镜像服务器之间能够持续，同步的传送事物。主体服务器中主体数据库发送日志后等待镜像服务器中的镜像数据库确认，确认完毕后再反馈给应用程序。高可用性模式需要使用见证服务器，参与会话的主体和镜像实例之间不停的发送ping命令来侦测对方的状态，见证服务器则侦测主体和镜像两者的状态。一旦侦测到故障发生，则主体或镜像提交请求到见证服务器，由见证服务器来仲裁角色的转换。高可用性的使用场景为要求提供高服务质量、能够自动实现故障转移、保证数据完整的场合。

         2. 高级别保护: 此模式没有见证服务器，主体服务器和镜像服务器之间同样能够持续，同步的传送事物。但由于少了见证服务器进行仲裁，则主体和镜像数据库之间不能够实现故障的自动转移，需要手动来实现角色之间的切换。高级别保护模式的使用场景多为高数据完整性要求、无须实现故障自动转移、对服务可用性要求相对较低的场合。

        3. 高性能: 此模式没有见证服务器，主体服务器和镜像服务器之间采用异步传送模式。主体服务器上的事务直接提交后通知应用程序，无须等待镜像服务器的确认，所主体数据库和镜像数据库之间有延迟的现象存在。没有了见证服务器进行仲裁，主体和镜像数据库之间不能够实现故障的自动转移，需要手动来实现角色之间的切换。高性能模式多使用于对性能要求高、主体镜像服务器相对较远、允许有延迟现象的场合。

         4. 事务安全性的说明:数据库镜像会话中数据库的安全性可以设定为Full或Off。Full模式的特性为主体和镜像数据库实现同步传输，主体发送日志后需要等待镜像数据库的确认，主体数据库和镜像数据库的日志完全一致。Off模式则表现为主体和镜像使用的异步传输模式，主体发送日志后无须等待镜像数据库的确认，主体数据库失败时，镜像服务器上可能会丢失部分日志，使得两者不能实时同步。

         5. 仲裁: 仲裁用于设定了见证服务器的镜像会话，用于高可用性模式。仲裁要求必须有两个或两个以上的服务器实例，且任一时间内必须要有一个伙伴为数据库提供服务，当故障发生时，仲裁决定故障的转移。

         6. 几种数据库镜像模式的比较，如下:                   

    六、数据库镜像所需的环境

         1. 支持数据库镜像所需的版本，确保主体服务器和镜像服务器使用相同的版本，如两个伙伴运行SQL server 2005标准版或SQL server 2005运行企业版，安装sp2以上补丁，否则需要使用跟踪标记1400来实现。

         2. 一个主体服务器，一个镜像服务器，一个可选的见证服务器，见证服务器可以使用任意版本的SQL server 2005。

         3. 主体服务器的主体数据库设置为 FULL恢复模式。     

    七、本次实验的环境

         1. windows xp pro (英文版) + sp2

         2. SQL server 2005 Developer + sp3

         3. 同一主机的三个实例： ROBINSON ， ROBINSON/MIRROR，ROBINSON/WITNESS

         4. 用于实现镜像的数据库为Performance，此Performance数据库为SQL server 2005技术内幕：T-SQL查询中的脚本生成，现转其脚本如下，此数据生成后大小为1GB左右，主要是日志文件较大，可以修改@max和@numorders的值来缩小数据库，也可以停止MSSQLSERVER服务后删除日志文件，使用sp_attach_single_file_db来重新生成较小日志文件。

SET NOCOUNT ON;
USE master;
GO
IF DB_ID('Performance') IS NULL
  CREATE DATABASE Performance;
GO
USE Performance;
GO

-- Creating and Populating the Nums Auxiliary Table
IF OBJECT_ID('dbo.Nums') IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

-- Drop Data Tables if Exist
IF OBJECT_ID('dbo.Orders') IS NOT NULL
  DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
IF OBJECT_ID('dbo.Shippers') IS NOT NULL
  DROP TABLE dbo.Shippers;
GO

-- Data Distribution Settings
DECLARE
  @numorders   AS INT,
  @numcusts    AS INT,
  @numemps     AS INT,
  @numshippers AS INT,
  @numyears    AS INT,
  @startdate   AS DATETIME;

SELECT
  @numorders   =   1000000,
  @numcusts    =     20000,
  @numemps     =       500,
  @numshippers =         5,
  @numyears    =         4,
  @startdate   = '20030101';

-- Creating and Populating the Customers Table
CREATE TABLE dbo.Customers
(
  custid   CHAR(11)     NOT NULL,
  custname NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.Customers(custid, custname)
  SELECT
    'C' + RIGHT('000000000' + CAST(n AS VARCHAR(10)), 10) AS custid,
    N'Cust_' + CAST(n AS VARCHAR(10)) AS custname
  FROM dbo.Nums
  WHERE n <= @numcusts;

ALTER TABLE dbo.Customers ADD
  CONSTRAINT PK_Customers PRIMARY KEY(custid);

-- Creating and Populating the Employees Table
CREATE TABLE dbo.Employees
(
  empid     INT          NOT NULL,
  firstname NVARCHAR(25) NOT NULL,
  lastname  NVARCHAR(25) NOT NULL
);

INSERT INTO dbo.Employees(empid, firstname, lastname)
  SELECT n AS empid,
    N'Fname_' + CAST(n AS NVARCHAR(10)) AS firstname,
    N'Lname_' + CAST(n AS NVARCHAR(10)) AS lastname
  FROM dbo.Nums
  WHERE n <= @numemps;

ALTER TABLE dbo.Employees ADD
  CONSTRAINT PK_Employees PRIMARY KEY(empid);

-- Creating and Populating the Shippers Table
CREATE TABLE dbo.Shippers
(
  shipperid   VARCHAR(5)   NOT NULL,
  shippername NVARCHAR(50) NOT NULL
);
INSERT INTO dbo.Shippers(shipperid, shippername)
  SELECT shipperid, N'Shipper_' + shipperid AS shippername
  FROM (SELECT CHAR(ASCII('A') - 2 + 2 * n) AS shipperid
        FROM dbo.Nums
        WHERE n <= @numshippers) AS D;

ALTER TABLE dbo.Shippers ADD
  CONSTRAINT PK_Shippers PRIMARY KEY(shipperid);

-- Creating and Populating the Orders Table
CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  custid    CHAR(11)   NOT NULL,
  empid     INT        NOT NULL,
  shipperid VARCHAR(5) NOT NULL,
  orderdate DATETIME   NOT NULL,
  filler    CHAR(155)  NOT NULL DEFAULT('a')
);

INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
  SELECT n AS orderid,
    'C' + RIGHT('000000000'
            + CAST(
                1 + ABS(CHECKSUM(NEWID())) % @numcusts
                AS VARCHAR(10)), 10) AS custid,
    1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid,
    CHAR(ASCII('A') - 2
           + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
      DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
        -- late arrival with earlier date
        - CASE WHEN n % 10 = 0
            THEN 1 + ABS(CHECKSUM(NEWID())) % 30
            ELSE 0
          END AS orderdate
  FROM dbo.Nums
  WHERE n <= @numorders
  ORDER BY CHECKSUM(NEWID());

CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);

CREATE NONCLUSTERED INDEX idx_nc_sid_od_cid
  ON dbo.Orders(shipperid, orderdate, custid);

CREATE UNIQUE INDEX idx_unc_od_oid_i_cid_eid
  ON dbo.Orders(orderdate, orderid)
  INCLUDE(custid, empid);

ALTER TABLE dbo.Orders ADD
  CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid),
  CONSTRAINT FK_Orders_Customers
    FOREIGN KEY(custid)    REFERENCES dbo.Customers(custid),
  CONSTRAINT FK_Orders_Employees
    FOREIGN KEY(empid)     REFERENCES dbo.Employees(empid),
  CONSTRAINT FK_Orders_Shippers
    FOREIGN KEY(shipperid) REFERENCES dbo.Shippers(shipperid);

   八、实验步骤

         1. 检查Performance数据库的还原类型是否为FULL，否则请修改Performance的恢复模式为FULL。

         2. 从主服务器备份主数据库后恢复到镜像服务器中，并确保两者数据库处于一致状态，在恢复时指定norecovery选项，此处也可以使用日志传送来初始化数据库镜像，恢复其他的如增量备份和日志备份文件，同样需使用norecovery选项。

         3. 复制其他需要的对象到镜像服务器，如logins，SSIS，Jobs等。

         4. 创建端点。端点的创建需要在每个实例上创建，且必须是sysadmin角色的成员，创建时需指定端点角色，并对端点激活。

            --ROBINSON ：

            CREATE ENDPOINT [DB_mirroring] 
               STATE=STARTED
               AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
               FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
               ENCRYPTION = SUPPORTED ALGORITHM RC4);

             --ROBINSON/MIRROR：

             CREATE ENDPOINT [DB_mirroring] 
               STATE=STARTED
               AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
               FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
               ENCRYPTION = SUPPORTED ALGORITHM RC4);

             --ROBINSON/WITNESS：

              CREATE ENDPOINT [DB_mirroring] 
               AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
               FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE,
               ENCRYPTION = SUPPORTED ALGORITHM RC4);

               ALTER ENDPOINT [DB_MIRRORING] STATE = STARTED;

          5. 分别在各个实例上查看端点的配置情况及端点的状态。

            SELECT * FROM SYS.DTABASE_MIRRORING_ENDPOINTS;

            GO

         6. 分别在各个实例上配置数据库镜像的安全性，本实验使用的同一帐户，故配置数据库镜像的安全性语句相同，如下。如要设定不同的帐户，请在各实例上增加Login帐户，映射到Windows。

             USE MASTER

             GO

             GRANT CONNECT ON ENDPOINT::”db_mirroring” TO ”robinson/SQL_mirror”;

             GO

         7. 单击各个实例的Security，Logins下的SQL_mirror帐户，查看其Properities，在Securables可以看到SQL_mirror被授予了connect权限。

         8. 启动数据库镜像

             在镜像服务器上执行以下语句，用已指明主服务器的伙伴。注意应先在镜像服务器上指明主服务器伙伴，然后才在主服务器上指明镜像伙伴。

             ALTER DATABASE Performance SET PARTNER = N ‘TCP://Robinson:5022’;  ----在镜像服务器上执行

             GO

             ALTER DATABASE Performance SET PARTNER = N ‘TCP://Robinson:5023’;  ----在主服务器上执行

             GO

             ALTER DATABASE Performance SET WITNESS = N ‘TCP://Robinson:5024’;  ----在主服务器上执行

             GO

         9. 配置数据库事务镜像安全级别

             ALTER DATABASE Performance SET SAFETY FULL;

             GO

         10. 查看数据库镜像的状态

               可以在主服务器上选择主体数据库，再单击属性，单击镜像，可以查看当前镜像数据库所使用的状态，端口及镜像模式等，也可以通过以下视图来查看当前镜像的状态。

               使用数据库镜像监视器。展开主服务器的主体数据库，右单击主体数据库，单击任务， 单击启动数据库镜像。在“数据库镜像监视器”对话框中，单击“注册镜像数据库”以注册一个或多个镜像数据库。

               使用动态管理视图监控镜像数据的转态。

                SYS.DATABASE_MIRRORING:此视图显示一个服务器实例中每个镜像数据库的数据库镜像元数据。

                SYS.DATABASE_MIRRORING_ENDPOINTS:显示有关服务器实例的数据库镜像的端点信息。

                SYS.DATABASE_MIRRORING_WITNESSES:显示服务器实例为见证服务器的每个会话的数据库镜像元数据。

                SYS.DM_DB_MIRRORING_ CONNECTIONS:为每个数据库镜像网络连接返回一行。

         11. 镜像数据库故障时角色转换的几种方式

               自动故障转移: 仅适用于高可用性，设置事务镜像安全级别为FULL。

               手动故障转移: 适用于高可用性和高级别保护模式，设置事务镜像安全级别为FULL。

               强制故障转移: 仅适用于高性能模式，设置事务镜像安全级别为OFF。

         12. 演示几种转移过程

               自动故障转移:在使用高可用性的配置环境中，手动停止主体服务器，并删除主体数据库日志文件后，再启动主体服务器，观察主体和镜像服务器中数据库名后所显示的字样发生了变化，主体数据库变成了镜像数据库，镜像修复后成了主体数据库。  

               手动故障转移:可以在无故障的情况下实现手动故障转移。在主体数据库中执行 ALTER DATABASE  Performance SET PARTNER FAILOVER;

               强制故障转移: 通常应用于高性能模式中，高可用性镜像和见证服务器均不可用时，可以使用此方法快速修复，但此方法容易以导致数据的丢失。强制故障转移语句：ALTER DATABASE  Performance SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;我们对上述采用了高可用性模式的实验切换到高性能模式并实行强制故障转移，执行下述语句：

               ALTER DATABASE Performance SET WITNESS OFF;      ----在主服务器上执行，停用Witness

               GO

               ALTER DATABASE Performance SET SAFETY OFF;         ----在主服务器上执行，关闭事务安全 

               GO

               ----然后停止主服务器的SQL server服务

               ALTER DATABASE Performance SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;  ----在镜像服务器上执行

               GO                                                                                                                                ----用于强制转移故障

               ALTER DATABASE Performance SET SAFETY OFF;         ----在镜像服务器上执行后，镜像服务器开始提供服务，此句可以不用执行。

               GO

               执行上述操作后，镜像服务器开始提供服务，原主体服务器处于挂起状态，此时可以使用以下SQL语句来恢复挂起的数据库。

               ALTER DATABASE  Performance SET PARTNER RESUME;           ----在新的主体服务器上执行

               GO

         13. 实现客户端重定向

               自动重定向连接，使用ADO.NET或者SQL Native Client能够自动连接到故障转移后的伙伴，连接字符串中必须指定故障转移伙伴。

                ConnectionString=”Data Source=computerA;Failover Partner=computerB;

                Initial Catalog=Profermance;Integrated Security=True;”

         14. 对镜像数据库创建快照用作报表服务器等，减轻主数据的负载

                 镜像数据库的不可直接访问的特性，使得创建数据库的快照用作报表服务器的特性得以体现。用户可以通过快照来访问镜像实例上的数据。当发生故障转移后，快照仍保留在原实例上，以下我们对Performance的镜像数据库创建快照。

                CREATE DATABASE Performance_snap ON 

                (NAME = N’Performance_data’,FILENAME = N‘D:/SQL_Data/Performance_mirror/Performance.ss’)

                AS SNAPSHOT OF Performance;

                GO

        15. 及时删除不用的快照，减轻镜像服务器的负载。