設定地理冗餘的 SQL Server 複寫Setup Geographic Redundancy with SQL Server Replication

適用於:Windows Server 2016、Windows Server 2012 R2Applies To: Windows Server 2016, Windows Server 2012 R2

重要

如果您想要建立 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 發電廠 geo-冗餘。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-冗餘複寫之間兩個地理位置伸到遠端的網站資料,讓應用程式可以切換到另一個網站。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 ServerFor 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. 如需詳細資訊,請查看http://technet.microsoft.com/evalcenter/hh225126.aspxFor more information, see http://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.

建立 geo-冗餘的第二個 (replica) SQL ServerCreate the second (replica) SQL Server for geo-redundancy

  1. 安裝 SQL Server \ (如需詳細資訊,請查看http://technet.microsoft.com/evalcenter/hh225126.aspxInstall SQL Server (for more information, see http://technet.microsoft.com/evalcenter/hh225126.aspx. 顯示 CreateDB.sql 和 SetPermissions.sql 指令碼將檔案複製到複本 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. 執行Export-AdfsDeploymentSQLScript來建立 CreateDB.sql 和 SetPermissions.sql 檔案的主要 AD FS 節點上。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$.
    地理冗餘設定

  4. 複製到您的第二個伺服器的指令碼。Copy the scripts to your secondary server. 打開 CreateDB.sql 指令碼,在SQL 管理 Studio ,按一下 [執行Open the CreateDB.sql script in SQL Management Studio and click Execute. 地理冗餘設定

  5. 打開 SetPermissions.sql 指令碼以在SQL 管理 Studio ,按一下 [執行Open the SetPermissions.sql script in in SQL Management Studio and click Execute. 地理冗餘設定

注意

您也可以使用下列命令。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 管理 studio,在複製,以滑鼠右鍵按一下本機發行,然後選擇 [新發行... ![地理冗餘設定From the SQL Server Management studio, under Replication, right click Local Publications and choose New Publication... Set up Geographic Redundancy</span></span>

  2. 在畫面上新的發行精靈按一下下一步On the New Publication Wizard screen click Next.
    地理冗餘設定

  3. 代理商頁面上,選擇 [本機伺服器代理商,按一下 [On Distributor page, choose local server as distributor and click Next.
    地理冗餘設定

  4. 快照資料夾頁面上,輸入 \\SQL1\repldata 來取代預設的資料夾。On the Snapshot folder page, enter \\SQL1\repldata in place of default folder. \ (請注意: 您必須建立此分享 yourself)。(NOTE: You may have to create this share yourself).
    地理冗餘設定

  5. 選擇 [ AdfsConfigurationV3發行資料庫和按Choose AdfsConfigurationV3 as the publication database and click Next.
    地理冗餘設定

  6. 發行輸入,選擇合併發行並按下一步On Publication Type, choose Merge publication and click Next.
    地理冗餘設定

  7. 訂戶類型,選擇SQL Server 2008 或更新版本並按下一步On Subscriber Types, choose SQL Server 2008 or later and click Next.
    地理冗餘設定

  8. 文章頁面選取表格節點,然後選取 [所有表格, un\ 檢查 SyncProperties表格 \ (此不應該 replicated\)On the Articles page select Tables node to select all tables, then un-check SyncProperties table (this one should not be replicated)
    地理冗餘設定

  9. 文章頁面上,選取定義函式使用者節點選取所有使用者定義函式並按一下 [下一步...On the Articles page, select User Defined Functions node to select all User Defined Functions and click Next..
    地理冗餘設定

  10. 文章問題頁面上按下一步On the Article issues page click Next.
    地理冗餘設定

  11. 篩選表格列頁面上,按一下 [On the Filter Table Rows page, click Next.
    地理冗餘設定

  12. 快照代理程式頁面上,選擇預設值的即時和 14 天後,按On the Snapshot Agent page, choose defaults of Immediate and 14 days, click Next.
    地理冗餘設定

    您可能需要建立核對 SQL 代理程式。You may need to create a domain account for the SQL agent. 使用中的步驟執行設定 SQL 核對 CONTOSO\sqlagent 登入來建立 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. 代理程式安全性頁面上,按一下 [的安全性設定輸入核對 username\ 日密碼 \ (不 GMSA) 建立 SQL 代理程式,按一下 [ [確定]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.
    地理冗餘設定

  14. 精靈動作頁面上,按一下 [On the Wizard Actions page, click Next.
    地理冗餘設定

  15. 完成精靈頁面上輸入物的名稱,然後按一下完成]On the Complete the Wizard page, enter a name for your publication and click Finish. 地理冗餘設定

  16. 一旦建立發行之後,您應該會看到成功的狀態。Once the publication is created you should see the status of success. 按一下關閉Click Close. 地理冗餘設定

  17. 返回 SQL Server 管理 studio,以滑鼠右鍵按一下新的發行,然後按一下上市複寫監視器Back in SQL Server Management Studio, right-click the new Publication and click Launch Replication Monitor.
    地理冗餘設定

複本 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 管理 studio,在複製,以滑鼠右鍵按一下本機月租方案,然後選擇 [新裝機費....On the replica SQL Server, from SQL Server Management studio, under Replication, right click Local Subscriptions and choose New Subscription.... 地理冗餘設定

  2. 新裝機費精靈頁面上,按一下 [On the New Subscription Wizard page, click Next. 地理冗餘設定

  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.
    地理冗餘設定

  4. 合併代理程式位置頁面上,選取執行每個代理程式其訂戶 (pull subscriptions) (the default),按一下 [下一步On the Merge Agent Location page, select Run each agent at its Subscriber (pull subscriptions) (the default) and click Next.
    地理冗餘設定
    以及裝機費輸入下列判斷衝突解析度邏輯操作。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.
    地理冗餘設定

  6. 合併代理程式安全性頁面上,按... ,並輸入的使用者名稱和密碼核對 \ (不 GMSA) SQL 代理程式建立使用針對方塊與,按一下 [下一步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 and click Next. 地理冗餘設定

  7. 同步排程,選擇持續執行並按下一步On Synchronization Schedule, choose Run Continuously and click Next. 地理冗餘設定

  8. 初始化月租方案,按一下 [On Initialize Subscriptions, click Next.
    地理冗餘設定

  9. 裝機費類型,選擇Client並按下一步On Subscription Type, choose Client and click Next.

    記載的影響的在此以下Implications of this are documented here and here. 基本上,我們需要簡單 」 第一次發行者 wins 「 衝突,我們就不需要重新其他訂閱。Essentially, we take the simple “first to publisher wins” conflict resolution and we do not need to republish to other subscribers.
    地理冗餘設定

  10. 精靈動作頁面上,請確定建立裝機費核取,並按一下 [下一步On the Wizard Actions page, ensure Create the subscription is checked and click Next. 地理冗餘設定

  11. 完成精靈頁面上,按完成]On the Complete the Wizard page, click Finish. 地理冗餘設定

  12. 一旦建立程序完成裝機費,您應該會看到成功。Once the subscription has finished the creation process, you should see success. 按一下關閉Click Close. 地理冗餘設定

請確認初始設定和複寫程的序Verify the process of initialization and replication

  1. 主要 SQL server 上 right\ 按一下複製節點,然後按一下 [上市複寫監視器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 Agent\Jobs節點,以查看 job(s) 排程來執行的作業 publication\ 日裝機費。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\ 按一下工作,然後選取檢視歷史檢視中執行歷史及結果。Right-click a job and select View History to view execution history and results.

設定針對網域 account CONTOSO\sqlagent SQL 登入Configure SQL login for the domain account CONTOSO\sqlagent

  1. 建立新的登入主要的及複本稱為 CONTOSO\sqlagent SQL Server \ (建立新的網域使用者名稱,並設定代理程式安全性頁面中的程序上述。 )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 中 right\ 按一下 [登入一經建立,並選取 [],然後在您使用者對應索引標籤上,地圖此登入AdfsConfigurationAdfsArtifact的公開和 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. 也 distribution 資料庫地圖此登入並加入 adfsconfiguration 表格和 distribution db_owner 角色。Also map this login to distribution database and add db_owner role for both distribution and adfsconfiguration tables. 這樣做為主要和複本 SQL server 適用。Do this as applicable on both primary and replica SQL server. 如需詳細資訊,請查看複寫專員安全性模型For more information, see Replication Agent Security Model.

  3. 提供相對應的網域 account 讀取和寫入設定為代理商共用的權限。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 node(s) 指向 SQL Server 複本陣列Configure AD FS node(s) to point to the SQL Server replica farm

既然您已經設定地理冗餘,AD FS 發電廠節點可以指向使用標準 AD FS 」 加入 「 發電廠功能,其中一個 AD FS 設定精靈 ui 或使用 Windows PowerShell 您複本 SQL Server 發電廠設定。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 設定精靈 UI,選取 [新增聯盟伺服器聯盟伺服器陣列到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-AdfsFarmNodeIf you use Windows PowerShell, run Add-AdfsFarmNode. 不要執行-AdfsFarm Install\ 的Do 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.