在可用性複本上設定 Read-Only 存取權 （SQL Server）

根據預設，允許對主要複本進行讀寫和讀取意圖存取，而且不允許連線到 AlwaysOn 可用性群組的次要複本。 本主題描述如何使用 SQL Server Management Studio、Transact-SQL 或 PowerShell，在 SQL Server 2014 中的 AlwaysOn 可用性群組可用性複本上設定連線存取。

如需啟用次要複本的唯讀存取可能造成的影響，以及連線存取介紹的相關資訊，請參閱關於可用性複本的用戶端連線存取（SQL Server）和啟動中的次要複本：可讀取的次要複本（AlwaysOn 可用性群組）。

開始之前

必要條件和限制

• 若要設定不同的連線存取，您必須連線到裝載主要複本的伺服器實例。

安全

權限

任務	權限
在建立可用性群組時設定複本	需要 系統管理員 (sysadmin) 固定伺服器角色的成員資格，以及 CREATE AVAILABILITY GROUP 伺服器權限、ALTER ANY AVAILABILITY GROUP 權限或 CONTROL SERVER 權限。
若要修改可用性複本	需要有對可用性群組的 ALTER AVAILABILITY GROUP 權限、CONTROL AVAILABILITY GROUP 權限、ALTER ANY AVAILABILITY GROUP 權限，或是對 CONTROL SERVER 的權限。

使用 SQL Server Management Studio

在可用性複本上設定存取權

1. 在 Object Explorer 中，連接到托管主要複本的伺服器執行個體，然後展開伺服器樹狀目錄。

2. 展開 [ AlwaysOn 高可用性 ] 節點和 [ 可用性群組] 節點。

3. 點選您要更改其副本的可用性群組。

4. 以滑鼠右鍵按一下可用性副本，然後點按 屬性。

5. 在 [ 可用性復本屬性 ] 對話框中，您可以變更主要角色和次要角色的聯機存取，如下所示：

   • 針對次要角色，從 [可讀取的次要 ] 下拉式清單中選取新的值，如下所示：

     否
     不允許使用者連接至這個複本的次要資料庫。 它們無法供讀取存取使用。 這是預設設定。

     唯讀意圖
     此複本的輔助資料庫只允許唯讀連接。 輔助資料庫全部可供讀取存取。

     是的
     此複本的所有連線都允許至輔助資料庫，但僅限於讀取存取。 輔助資料庫全部可供讀取存取。

   • 針對主要角色，從 [主要角色中的連線 ] 下拉式清單中選取新的值，如下所示：

     允許所有連線
     主要複本的資料庫允許所有連接。 這是預設設定。

     允許讀取/寫入連線
     當 Application Intent 屬性設定為 ReadWrite 或未設定 Application Intent 連接屬性時，允許連接。 不允許將 Application Intent 連接屬性設定為 ReadOnly 的連線。 這有助於防止客戶錯誤地將以讀取為目的的工作負載連接到主副本。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜ Using Connection String Keywords with SQL Server Native Client＞。

使用 Transact-SQL

在可用性複本上設定存取權

備註

如需這個程序的範例，請參閱本節稍後的 範例 (Transact-SQL)。

1. 連接到主要複本所在的伺服器實例。

2. 如果您要為新的可用性群組指定複本，請使用 CREATE AVAILABILITY GROUPTransact-SQL 語句。 如果您要新增或修改現有可用性群組的複本，請使用 ALTER AVAILABILITY GROUPTransact-SQL 語句。

   • 若要設定次要角色的連線存取，請在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 SECONDARY_ROLE 選項，如下所示：

     SECONDARY_ROLE （ ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } ）

     其中

     [否]
     不允許直接連線到這個複本的輔助資料庫。 它們無法供讀取存取使用。 這是預設設定。

     READ_ONLY
     此複本的輔助資料庫只允許唯讀連接。 輔助資料庫全部可供讀取存取。

     全部
     此複本的所有連線都允許至輔助資料庫，但僅限於讀取存取。 輔助資料庫全部可供讀取存取。

3. 若要設定主要角色的連線存取，請在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 PRIMARY_ROLE 選項，如下所示：

   PRIMARY_ROLE （ ALLOW_CONNECTIONS = { READ_WRITE | ALL }

   其中

   讀寫
   不允許 Application Intent 連接屬性設為 ReadOnly 的連接。 當 Application Intent 屬性設定為 ReadWrite 或未設定 Application Intent 連接屬性時，允許連接。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜ Using Connection String Keywords with SQL Server Native Client＞。

   全部
   主要複本的資料庫允許所有連接。 這是預設設定。

範例 (Transact-SQL)

下列範例會將次要複本新增至名為 AG2 的可用性群組。 指定獨立伺服器實例 COMPUTER03\HADR_INSTANCE，以裝載新的可用性複本。 此複本設定為只允許主要角色的讀寫連線，以及只允許次要角色的讀取意圖連線。

ALTER AVAILABILITY GROUP AG2   
   ADD REPLICA ON   
      'COMPUTER03\HADR_INSTANCE' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',  
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )  
         );   
GO  

使用 PowerShell

在可用性複本上設定存取權

備註

如需程式代碼範例，請參閱本節稍後的 PowerShell 範例。

1. 將目錄 （cd） 變更為裝載主要複本的伺服器實例。

2. 將可用性復本新增至可用性群組時，請使用 New-SqlAvailabilityReplica Cmdlet。 修改現有的可用性複本時，請使用 Set-SqlAvailabilityReplica Cmdlet。 相關參數如下所示：

   • 若要設定次要角色的連線存取，請指定 ConnectionModeInSecondaryRolesecondary_role_keyword 參數，其中 secondary_role_keyword 等於下列其中一個值：

     AllowNoConnections
     次要複本中的資料庫不允許直接連線，而且資料庫無法進行讀取存取。 這是預設設定。

     AllowReadIntentConnectionsOnly
     只有將 Application Intent 屬性設定為 ReadOnly 之次要復本中的資料庫，才允許連接。 如需有關這個屬性的詳細資訊，請參閱＜ Using Connection String Keywords with SQL Server Native Client＞。

     AllowAllConnections
     次要複本的資料庫允許所有連接進行唯讀存取。

   • 若要設定主要角色的連線存取，請指定 ConnectionModeInPrimaryRoleprimary_role_keyword，其中 primary_role_keyword 等於下列其中一個值：

     AllowReadWriteConnections
     不允許 Application Intent 連接屬性設定為 ReadOnly 的連線。 當 Application Intent 屬性設定為 ReadWrite 或未設定 Application Intent 連接屬性時，允許連接。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜ Using Connection String Keywords with SQL Server Native Client＞。

     AllowAllConnections
     主要複本的資料庫允許所有連接。 這是預設設定。

   備註

   若要檢視 Cmdlet 的語法，請使用 Get-Help SQL Server 2014 PowerShell 環境中的 Cmdlet。 如需詳細資訊，請參閱 Get Help SQL Server PowerShell。

若要設定及使用 SQL Server PowerShell 提供者，請參閱 SQL Server PowerShell 提供者。

下列範例會將 ConnectionModeInSecondaryRole 與 ConnectionModeInPrimaryRole 參數設定為 AllowAllConnections。

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  

Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" `
 -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" `
-InputObject $primaryReplica

設定可用性副本 Read-Only 存取之後的後續步驟

唯讀存取權至可讀取的次要複本

• 使用 bcp 公用程式 或 sqlcmd 公用程式時，您可以使用 -K ReadOnly 參數來指定對啟用唯讀存取的次要複本的唯讀存取權。

• 若要讓用戶端應用程式連線到可讀取的次要複本：

  	先決條件	連結
  	請確定可用性群組具有聆聽器。	建立或設定可用性群組接聽程式 (SQL Server)
  	設定可用性群組的唯讀路由。	設定可用性群組的唯讀路由 (SQL Server)

可能會影響故障轉移後觸發器和作業的因素

如果您有在不可讀取輔助資料庫或可讀取輔助資料庫上執行時將會失敗的觸發程式和作業，您必須編寫觸發程式和作業的腳本，以檢查指定的複本，以判斷資料庫是主資料庫還是可讀取的輔助資料庫。 若要取得這項資訊，請使用 DATABASEPROPERTYEX 函數傳回資料庫的 Updateability 屬性。 若要識別只讀資料庫，請將READ_ONLY指定為 值，如下所示：

DATABASEPROPERTYEX([db name],'Updatability') = N'READ_ONLY'  

若要識別讀寫資料庫，請將READ_WRITE指定為 值。

相關工作

• 設定可用性群組的唯讀路由 (SQL Server)

• 建立或設定可用性群組接聽程式 (SQL Server)

相關內容

• 隨時開啟：可讀次要資源的價值主張

• AlwaysOn：為什麼有兩個選項可以啟用讀取工作負載的次要複本？

• AlwaysOn：設定可讀取的次要複本

• Always On：我剛剛啟用可讀副本，但我的查詢被封鎖了？

• AlwaysOn：在可讀取次要資料庫、Read-Only 資料庫和資料庫快照集上提供最新的統計數據

• AlwaysOn：唯讀資料庫、資料庫快照和次要副本上的統計數據挑戰

• Always On：當您在次要複本上執行報表工作負載時對主要工作負載的影響

• Always On: 將報告工作負載對應至可讀取次要副本和快照隔離的影響

• AlwaysOn：在次要複本上執行報告工作負載時，將 REDO 線程封鎖降至最低

• AlwaysOn：可讀取的次要副本和數據延遲

另請參閱

AlwaysOn 可用性群組概觀 （SQL Server）
可啟動的次要副本：可讀取的次要副本（AlwaysOn 高可用性群組）
關於可用性複本的用戶端連接存取 (SQL Server)