设置 SQL Server 复制的地理冗余Setup Geographic Redundancy with SQL Server Replication

重要

如果要创建 AD FS 场并使用 SQL Server 来存储配置数据,可以使用 SQL Server 2008 或更高版本。If you want to create an AD FS farm and use SQL Server to store your configuration data, you can use SQL Server 2008 or higher.

如果使用 SQL Server 作为 AD FS 配置数据库,则可以 - 使用 SQL Server 复制为 AD FS 场设置异地冗余。If you are using SQL Server as your AD FS configuration database, you can set up geo-redundancy for your AD FS farm using SQL Server replication. 异地 - 冗余在两个地理位置较远的站点之间复制数据,以便应用程序可以从一个站点切换到另一个站点。Geo-redundancy replicates data between two geographically distant sites so that applications can switch from one site to another. 这样一来,如果一个站点发生故障,你仍可以在第二个站点上提供所有配置数据。This way, in case of the failure of one site, you can still have all the configuration data available at the second site. 有关详细信息,请参阅使用 SQL Server 的联合服务器场中的 "SQL Server 地理冗余部分"。For more information, see the "SQL Server geographic redundancy section" in Federation Server Farm Using SQL Server.

先决条件Prerequisites

安装和配置 SQL server 场。Install and configure a SQL server farm. 有关详细信息,请参阅 https://technet.microsoft.com/evalcenter/hh225126.aspxFor more information, see https://technet.microsoft.com/evalcenter/hh225126.aspx. 在初始 SQL Server 上,确保 SQL Server 代理服务正在运行,并设置为自动启动。On the initial SQL Server, make sure that the SQL Server Agent service is running and set to automatic start.

( ) 为异地冗余创建第二个副本 SQL Server -Create the second (replica) SQL Server for geo-redundancy

  1. 安装 SQL Server ( 有关详细信息,请参阅 https://technet.microsoft.com/evalcenter/hh225126.aspxInstall SQL Server (for more information, see https://technet.microsoft.com/evalcenter/hh225126.aspx. 将生成的 CreateDB 和 SetPermissions 脚本文件复制到副本 SQL server。Copy the resulting CreateDB.sql and SetPermissions.sql script files to the replica SQL server.

  2. 确保 SQL Server 代理服务正在运行并且设置为自动启动Ensure SQL Server Agent service is running and set to automatic start

  3. 在主 AD FS 节点上运行Export - AdfsDeploymentSQLScript ,以创建 CreateDB 和 SetPermissions 文件。Run Export-AdfsDeploymentSQLScript on the primary AD FS node to create CreateDB.sql and SetPermissions.sql files. 例如: PS:\>Export-AdfsDeploymentSQLScript -DestinationFolder . –ServiceAccountName CONTOSO\gmsa1$For example:PS:\>Export-AdfsDeploymentSQLScript -DestinationFolder . –ServiceAccountName CONTOSO\gmsa1$. 设置地理冗余Set up Geographic Redundancy

  4. 将脚本复制到辅助服务器。Copy the scripts to your secondary server. sql Management Studio中打开 CreateDB 脚本,然后单击 "执行"。Open the CreateDB.sql script in SQL Management Studio and click Execute. 设置地理冗余Set up Geographic Redundancy

  5. sql Management Studio中打开 SetPermissions 脚本,然后单击 "执行"。Open the SetPermissions.sql script in SQL Management Studio and click Execute. 设置地理冗余Set up Geographic Redundancy

备注

还可以从命令行使用以下命令。You can also use the following from the command line.

c:\>sqlcmd –i CreateDB.sql

c:\>sqlcmd –i SetPermissions.sql

在初始 SQL Server 上创建发布服务器设置Create publisher settings on the initial SQL Server

  1. 在 SQL Server Management studio 中的 "复制" 下,右键单击 "本地发布",然后选择 "新建发布 ..." 设置地理冗余From the SQL Server Management studio, under Replication, right click Local Publications and choose New Publication... Set up Geographic Redundancy

  2. 在新建发布向导屏幕上,单击 "下一步"。On the New Publication Wizard screen click Next.
    设置地理冗余Set up Geographic Redundancy

  3. 在 "分发服务器" 页上,选择本地服务器作为分发服务器,然后单击下一步On Distributor page, choose local server as distributor and click Next. 设置地理冗余Set up Geographic Redundancy

  4. 在 "快照文件夹" 页上,输入 \ \SQL1\repldata 来替换默认文件夹。On the Snapshot folder page, enter \\SQL1\repldata in place of default folder. (注意:可能需要自行创建此共享 ) 。(NOTE: You may have to create this share yourself). 设置地理冗余Set up Geographic Redundancy

  5. 选择AdfsConfigurationV3作为发布数据库,然后单击 "下一步"。Choose AdfsConfigurationV3 as the publication database and click Next. 设置地理冗余Set up Geographic Redundancy

  6. 在 "发布类型" 上,选择 "合并发布" 并单击 "下一步"On Publication Type, choose Merge publication and click Next. 设置地理冗余Set up Geographic Redundancy

  7. 订阅服务器上,选择SQL Server 2008 或更高版本,然后单击 "下一步"。On Subscriber Types, choose SQL Server 2008 or later and click Next. 设置地理冗余Set up Geographic Redundancy

  8. 在 "项目" 页上,选择 "" 节点以选择所有表,然后取消 - 选中 ( 不应复制此项的 SyncProperties 表)On the Articles page select Tables node to select all tables, then un-check SyncProperties table (this one should not be replicated)
    设置地理冗余Set up Geographic Redundancy

  9. 在 "项目" 页上,选择 "用户定义函数" 节点以选择所有用户定义的函数,然后单击 "下一步"。On the Articles page, select User Defined Functions node to select all User Defined Functions and click Next.. 设置地理冗余Set up Geographic Redundancy

  10. 在 "问题" 页上,单击 "下一步"。On the Article issues page click Next. 设置地理冗余Set up Geographic Redundancy

  11. 在“筛选表行”页上,单击“下一步”********。On the Filter Table Rows page, click Next. 设置地理冗余Set up Geographic Redundancy

  12. 在 "快照代理" 页上,选择 "即时" 和 "14 天",然后单击 "下一步"。On the Snapshot Agent page, choose defaults of Immediate and 14 days, click Next. 设置地理冗余Set up Geographic Redundancy
    可能需要为 SQL 代理创建域帐户。You may need to create a domain account for the SQL agent. 使用为域帐户 CONTOSO \ SQLAGENT 配置 sql 登录名中的步骤为此新 AD 用户创建 sql 登录名并分配特定权限。Use the steps in Configure SQL login for the domain account CONTOSO\sqlagent to create SQL login for this new AD user and assign specific permissions.

  13. 在 "代理安全性" 页上,单击 "安全设置",并输入 / ( 不是为 SQL 代理创建的 GMSA 的域帐户的用户名密码 ) ,然后单击 "确定"On the Agent Security page, click Security Settings and enter the username/password of a domain account (not a GMSA) created for the SQL agent and click OK. 单击“下一步”。Click Next. 设置地理冗余Set up Geographic Redundancy

  14. 在 "向导操作" 页上,单击 "下一步"。On the Wizard Actions page, click Next. 设置地理冗余Set up Geographic Redundancy

  15. 在 "完成向导" 页上,输入发布的名称,然后单击 "完成"。On the Complete the Wizard page, enter a name for your publication and click Finish. 设置地理冗余Set up Geographic Redundancy

  16. 创建发布后,应会看到 "成功" 状态。Once the publication is created you should see the status of success. 单击“关闭” 。Click Close. 设置地理冗余Set up Geographic Redundancy

  17. 返回 SQL Server Management Studio,右键单击新发布,然后单击 "启动复制监视器"。Back in SQL Server Management Studio, right-click the new Publication and click Launch Replication Monitor. 设置地理冗余Set up Geographic Redundancy

在副本 SQL Server 上创建订阅设置Create subscription settings on the replica SQL Server

请确保已按照上述步骤在初始 SQL Server 上创建发布服务器设置,然后完成以下过程:Make sure that you created the publisher settings on the initial SQL Server as described above and then complete the following procedure:

  1. 在副本 SQL Server SQL Server 上的 "复制" 下,右键单击 "本地订阅",然后选择 "新建订阅 ..."。设置地理冗余On the replica SQL Server, from SQL Server Management studio, under Replication, right click Local Subscriptions and choose New Subscription.... Set up Geographic Redundancy

  2. 在 "新建订阅向导" 页上,单击 "下一步"。On the New Subscription Wizard page, click Next. 设置地理冗余Set up Geographic Redundancy

  3. 在 "发布" 页上,从下拉端中选择发布服务器。On the Publication page, select the publisher from the drop-down. 展开 " AdfsConfigurationV3 ",选择上面创建的发布的名称,然后单击 "下一步"。Expand AdfsConfigurationV3 and select the name of the publication created above and click Next. 设置地理冗余Set up Geographic Redundancy

  4. 在 "合并代理位置" 页上,选择 "在其订阅服务器上运行每个代理 ( 请求 ) 订阅 ( ", ) 然后单击 "下一步"。On the Merge Agent Location page, select Run each agent at its Subscriber (pull subscriptions) (the default) and click Next. 设置地理冗余Set up Geographic Redundancy
    这与下面的订阅类型一起确定冲突解决逻辑。This, along with Subscription Type below, determines the conflict resolution logic. (有关详细信息,请参阅检测并解决合并复制冲突(For more information, see Detect and Resolve Merge Replication Conflicts.

  5. 在 "订阅服务器" 页上,选择AdfsConfigurationV3作为订阅服务器数据库,然后单击 "下一步"。On the Subscribers page, select AdfsConfigurationV3 as the subscriber database and click Next. 设置地理冗余Set up Geographic Redundancy

  6. 在 "合并代理安全" 页上,单击 " ... " 并输入域帐户的用户名和密码,而 ( 不是 ) 使用省略号框为 SQL 代理创建的 GMSA,然后单击 "下一步"。On the Merge Agent Security page, click ... and enter the username and password of a domain account (not a GMSA) created for the SQL agent by using the ellipses box and click Next. 设置地理冗余Set up Geographic Redundancy

  7. 同步计划中,选择 "连续运行",然后单击 "下一步"。On Synchronization Schedule, choose Run Continuously and click Next. 设置地理冗余Set up Geographic Redundancy

  8. 初始化订阅时,单击 "下一步"。On Initialize Subscriptions, click Next. 设置地理冗余Set up Geographic Redundancy

  9. 在 "订阅类型" 上,选择客户端,然后单击 "下一步On Subscription Type, choose Client and click Next.

    这里介绍了这种情况的含义。Implications of this are documented here and here. 实质上,我们采用简单的 "首先发布服务器入选" 冲突解决方法,而不需要将其重新发布到其他订阅服务器。Essentially, we take the simple "first to publisher wins" conflict resolution and we do not need to republish to other subscribers. 设置地理冗余Set up Geographic Redundancy

  10. 在 "向导操作" 页上,确保选中 "创建订阅" ,然后单击 "下一步"。On the Wizard Actions page, ensure Create the subscription is checked and click Next. 设置地理冗余Set up Geographic Redundancy

  11. 在 "完成向导" 页上,单击 "完成"。On the Complete the Wizard page, click Finish. 设置地理冗余Set up Geographic Redundancy

  12. 订阅完成创建过程后,应会看到 "成功"。Once the subscription has finished the creation process, you should see success. 单击“关闭” 。Click Close. 设置地理冗余Set up Geographic Redundancy

验证初始化和复制的过程Verify the process of initialization and replication

  1. 在主 SQL server 上,右键 - 单击 "复制" 节点,然后单击 "启动复制监视器"。On the primary SQL server, right-click the Replication node and click Launch Replication Monitor.

  2. 复制监视器中,单击发布。In Replication Monitor, click the publication.

  3. 在 "所有订阅" 选项卡上,右键单击并查看详细信息On the All Subscriptions tab, right click and View Details.

    在初始复制的 "操作" 下,应该可以看到许多条目。You should be able to see many entries under Actions for the initial replication.

  4. 此外,还可以在 " SQL Server 代理 \ 作业" 节点下查看 ( ) 计划执行发布订阅操作的作业 / 。Additionally, you can look under the SQL Server Agent\Jobs node to see the job(s) scheduled to execute the operations of the publication/subscription. 仅显示本地作业,因此请确保在发布服务器和订阅服务器上进行故障排除。Only local jobs are shown, so make sure to check on the publisher and the subscriber for troubleshooting. 右键 - 单击某个作业,然后选择 "查看历史记录" 以查看执行历史记录和结果。Right-click a job and select View History to view execution history and results.

为域帐户配置 SQL 登录 CONTOSO \ sqlagentConfigure SQL login for the domain account CONTOSO\sqlagent

  1. 在主副本和副本 SQL Server 上创建新的登录名,该登录名 \ 为 CONTOSO sqlagent 在 ( 上述过程的 "代理安全性" 页上创建和配置的新域用户的名称。)Create a new login on the primary and replica SQL Server called CONTOSO\sqlagent (the name of the new domain user created and configured on the Agent Security page in the procedures above.)

  2. 在 SQL Server 中,右键 - 单击你创建的登录名,然后选择 "属性",然后在 "用户映射" 选项卡下,将此登录名映射到AdfsConfiguration ,并将AdfsArtifact数据库映射为公有和 db _ genevaservice 角色。In SQL Server, right-click the login you created, and select Properties, then under the User Mapping tab, map this login to AdfsConfiguration and AdfsArtifact databases with public and db_genevaservice roles. 还需要将此登录名映射到分发数据库,并 _ 为分发表和 adfsconfiguration 表添加 db 所有者角色。Also map this login to distribution database and add db_owner role for both distribution and adfsconfiguration tables. 在主 SQL server 和副本 SQL server 上执行此操作。Do this as applicable on both primary and replica SQL server. 有关详细信息,请参阅 复制代理安全模式For more information, see Replication Agent Security Model.

  3. 向相应的域帐户授予对配置为分发服务器的共享的读取和写入权限。Give the corresponding domain account read and write permissions on the share configured as distributor. 请确保在 "共享" 权限和 "本地文件" 权限上都设置 "读写" 权限。Make sure that you set read and write permissions both on the share permissions and the local file permissions.

将 AD FS 节点 ( s 配置 ) 为指向 SQL Server 副本场Configure AD FS node(s) to point to the SQL Server replica farm

设置异地冗余后,可以使用标准 AD FS "加入" 场功能,将 AD FS 场节点配置为指向副本 SQL Server 场,无论是通过 AD FS 配置向导 UI 还是使用 Windows PowerShell。Now that you have set up geo redundancy, the AD FS farm nodes can be configured to point to your replica SQL Server farm using the standard AD FS "join" farm capabilities, either from the AD FS Configuration Wizard UI or using Windows PowerShell.

如果使用 AD FS 配置向导用户界面,则选择 "将联合服务器添加到联合服务器场"。If you use the AD FS Configuration Wizard UI, select Add a federation server to a federation server farm. 不要选择 "在联合服务器场中创建第一个联合服务器"Do NOT select Create the first federation server in a federation server farm.

如果使用 Windows PowerShell,请运行 "添加 - add-adfsfarmnode"。If you use Windows PowerShell, run Add-AdfsFarmNode. 不要运行安装 - install-adfsfarmDo NOT run Install-AdfsFarm.

出现提示时,提供副本 SQL Server 的主机名和实例名,而是初始 SQL Server。When prompted, provide the host and instance name of the replica SQL Server, NOT the initial SQL server.