示例:使用证书设置数据库镜像 (Transact-SQL)Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

此示例演示了使用基于证书的身份验证创建数据库镜像会话所需的所有阶段。This example shows all the stages required to create a database mirroring session using certificate-based authentication. 本主题中的示例使用 Transact-SQLTransact-SQLThe examples in this topic use Transact-SQLTransact-SQL. 建议您对数据库镜像连接进行加密,除非您能够保证网络的安全。Unless you can guarantee that your network is secure, we recommend that you use encryption for database mirroring connections.

将证书复制到其他系统时,请使用安全的复制方法。When copying a certificate to another system, use a secure copy method. 必须格外小心地保证所有证书的安全。Be extremely careful to keep all of your certificates secure.

示例Example

下面的示例演示必须对驻留在 HOST_A 上的一个伙伴执行哪些操作。The following example demonstrates what must be done on one partner that resides on HOST_A. 在此示例中,两个伙伴是三个计算机系统上的默认服务器实例。In this example, the two partners are the default server instances on three computer systems. 两个服务器实例在非信任的 Windows 域中运行,因此需要基于证书的身份验证。The two server instances run in nontrusted Windows domains, so certificate-based authentication is required.

HOST_A 担当初始主体角色,HOST_B 担当镜像角色。The initial principal role is taken by HOST_A, and the mirror role is taken by HOST_B.

使用证书设置数据库镜像涉及四个常规阶段,本示例演示其中的三个阶段:1、2、4。Setting up database mirroring using certificates involves four general stages, of which three stages-1, 2, and 4-are demonstrated by this example. 这些阶段如下:These stages are as follows:

  1. 配置出站连接Configuring Outbound Connections

    本示例显示了下列操作的步骤:This example shows the steps for:

    1. 为出站连接配置 Host_A。Configuring Host_A for outbound connections.

    2. 为出站连接配置 Host_B。Configuring Host_B for outbound connections.

    有关设置数据库镜像的本阶段信息,请参阅 允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL)For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

  2. 配置入站连接Configuring Inbound Connections

    本示例显示了下列操作的步骤:This example shows the steps for:

    1. 为入站连接配置 Host_A。Configuring Host_A for inbound connections.

    2. 为入站连接配置 Host_B。Configuring Host_B for inbound connections.

    有关设置数据库镜像的本阶段信息,请参阅 允许数据库镜像终结点将证书用于入站连接 (Transact-SQL)For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).

  3. 创建镜像数据库Creating the Mirror Database

    有关如何创建镜像数据库的信息,请参阅 为镜像准备镜像数据库 (SQL Server)For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).

  4. 配置镜像伙伴Configuring the Mirroring Partners

配置出站连接Configuring Outbound Connections

为出站连接配置 Host_ATo configure Host_A for outbound connections

  1. 在 master 数据库中,创建数据库主密钥(如果需要)。On the master database, create the database master key, if needed.

    USE master;  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';  
    GO  
    
  2. 为此服务器实例制作一个证书。Make a certificate for this server instance.

    USE master;  
    CREATE CERTIFICATE HOST_A_cert   
       WITH SUBJECT = 'HOST_A certificate';  
    GO  
    
  3. 使用该证书为服务器实例创建一个镜像端点。Create a mirroring endpoint for server instance using the certificate.

    CREATE ENDPOINT Endpoint_Mirroring  
       STATE = STARTED  
       AS TCP (  
          LISTENER_PORT=7024  
          , LISTENER_IP = ALL  
       )   
       FOR DATABASE_MIRRORING (   
          AUTHENTICATION = CERTIFICATE HOST_A_cert  
          , ENCRYPTION = REQUIRED ALGORITHM AES  
          , ROLE = ALL  
       );  
    GO  
    
  4. 备份 HOST_A 证书,并将其复制到其他系统,即 HOST_B。Back up the HOST_A certificate, and copy it to other system, HOST_B.

    BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';  
    GO  
    
  5. 使用任一安全的复制方法,将 C:\HOST_A_cert.cer 复制到 HOST_B。Using any secure copy method, copy C:\HOST_A_cert.cer to HOST_B.

为出站连接配置 Host_BTo configure Host_B for outbound connections

  1. 在 master 数据库中,创建数据库主密钥(如果需要)。On the master database, create the database master key, if needed.

    USE master;  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';  
    GO  
    
  2. 为 HOST_B 服务器实例制作一个证书。Make a certificate on the HOST_B server instance.

    CREATE CERTIFICATE HOST_B_cert   
       WITH SUBJECT = 'HOST_B certificate for database mirroring';  
    GO  
    
  3. 在 HOST_B 中为服务器实例创建一个镜像端点。Create a mirroring endpoint for the server instance on HOST_B.

    CREATE ENDPOINT Endpoint_Mirroring  
       STATE = STARTED  
       AS TCP (  
          LISTENER_PORT=7024  
          , LISTENER_IP = ALL  
       )   
       FOR DATABASE_MIRRORING (   
          AUTHENTICATION = CERTIFICATE HOST_B_cert  
          , ENCRYPTION = REQUIRED ALGORITHM AES  
          , ROLE = ALL  
       );  
    GO  
    
  4. 备份 HOST_B 证书。Back up HOST_B certificate.

    BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';  
    GO   
    
  5. 使用任一安全的复制方法,将 C:\HOST_B_cert.cer 复制到 HOST_A。Using any secure copy method, copy C:\HOST_B_cert.cer to HOST_A.

有关详细信息,请参阅 允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL)For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

[示例顶部][Top of Example]

配置入站连接Configuring Inbound Connections

为入站连接配置 Host_ATo configure Host_A for inbound connections

  1. 在 HOST_A 上为 HOST_B 创建一个登录名。Create a login on HOST_A for HOST_B.

    USE master;  
    CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';  
    GO  
    
  2. 创建一个使用该登录名的用户。--Create a user for that login.

    CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
    GO  
    
  3. 使证书与该用户关联。--Associate the certificate with the user.

    CREATE CERTIFICATE HOST_B_cert  
       AUTHORIZATION HOST_B_user  
       FROM FILE = 'C:\HOST_B_cert.cer'  
    GO  
    
  4. 授予对远程镜像端点的登录名的 CONNECT 权限。Grant CONNECT permission on the login for the remote mirroring endpoint.

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];  
    GO  
    

为入站连接配置 Host_BTo configure Host_B for inbound connections

  1. 在 HOST_B 上为 HOST_A 创建一个登录名。Create a login on HOST_B for HOST_A.

    USE master;  
    CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';  
    GO  
    
  2. 创建一个使用该登录名的用户。Create a user for that login.

    CREATE USER HOST_A_user FOR LOGIN HOST_A_login;  
    GO  
    
  3. 使证书与该用户关联。Associate the certificate with the user.

    CREATE CERTIFICATE HOST_A_cert  
       AUTHORIZATION HOST_A_user  
       FROM FILE = 'C:\HOST_A_cert.cer'  
    GO  
    
  4. 授予对远程镜像端点的登录名的 CONNECT 权限。Grant CONNECT permission on the login for the remote mirroring endpoint.

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];  
    GO  
    

重要

如果打算在具有自动故障转移功能的高安全性模式下运行,则必须重复相同的设置步骤为出站连接和入站连接配置见证服务器。If you intend to run in high-safety mode with automatic failover, you must repeat the same setup steps to configure the witness for outbound and inbound connections. 设置入站连接时,如果涉及到见证服务器,则需要为两个伙伴的见证服务器和见证服务器的两个伙伴设置登录名和用户。Setting up the inbound connections when a witness is involved requires that you set up logins and users for the witness on both of the partners and for both partners on the witness.

有关详细信息,请参阅 允许数据库镜像终结点将证书用于入站连接 (Transact-SQL)For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).

[示例顶部][Top of Example]

创建镜像数据库Creating the Mirror Database

有关如何创建镜像数据库的信息,请参阅 为镜像准备镜像数据库 (SQL Server)For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).

配置镜像伙伴Configuring the Mirroring Partners

  1. 在 HOST_B 的镜像服务器实例上,将 HOST_A 上的服务器实例设置为伙伴(使其成为初始主体服务器实例)。On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance). TCP://HOST_A.Mydomain.Corp.Adventure-Works``.com:7024替换为有效的网络地址。Substitute a valid network address for TCP://HOST_A.Mydomain.Corp.Adventure-Works``.com:7024. 有关详细信息,请参阅 指定服务器网络地址(数据库镜像)For more information, see Specify a Server Network Address (Database Mirroring).

    --At HOST_B, set server instance on HOST_A as partner (principal server):  
    ALTER DATABASE AdventureWorks   
        SET PARTNER = 'TCP://HOST_A.Mydomain.Corp.Adventure-Works.com:7024';  
    GO  
    
  2. 在 HOST_A 的主体服务器实例上,将 HOST_B 上的服务器实例设置为伙伴(使其成为初始镜像服务器实例)。On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance). TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024替换为有效的网络地址。Substitute a valid network address for TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024.

    --At HOST_A, set server instance on HOST_B as partner (mirror server).  
    ALTER DATABASE AdventureWorks   
        SET PARTNER = 'TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024';  
    GO  
    
  3. 此示例假设会话将在高性能模式下运行。This example assumes that the session will be running in high-performance mode. 若要在高性能模式下配置此会话,在主体服务器实例上(位于 HOST_A 上),将事务安全性设置为 OFF。To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.

    --Change to high-performance mode by turning off transacton safety.  
    ALTER DATABASE AdventureWorks   
        SET PARTNER SAFETY OFF  
    GO  
    

    备注

    如果打算在具有自动故障转移功能的高安全性模式下运行,请将事务安全性设置为 FULL(默认设置),并在执行第二个 SET PARTNER ' partner_server ' 语句后尽快添加见证服务器。If you intend to run in high-safety mode with automatic failover, leave transaction safety set to FULL (the default setting) and add the witness as soon as possible after executing the second SET PARTNER 'partner_server' statement. 注意,必须首先为出站连接和入站连接配置见证服务器。Note that the witness must first be configured for outbound and inbound connections.

[示例顶部][Top of Example]

另请参阅See Also

针对数据库镜像和 AlwaysOn 可用性组的传输安全性 (SQL Server) Transport Security for Database Mirroring and Always On Availability Groups (SQL Server)
指定服务器网络地址(数据库镜像) Specify a Server Network Address (Database Mirroring)
数据库镜像终结点 (SQL Server) The Database Mirroring Endpoint (SQL Server)
使用数据库镜像终结点证书 (Transact-SQL) Use Certificates for a Database Mirroring Endpoint (Transact-SQL)
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
SQL Server 数据库引擎和 Azure SQL Database 的安全中心Security Center for SQL Server Database Engine and Azure SQL Database