設定可用性複本上的唯讀存取 (SQL Server)

預設允許與主要複本之間的讀寫和讀取意圖的存取，但是不允許連接 AlwaysOn 可用性群組的次要複本。 本主題說明如何藉由使用 SQL Server Management Studio、Transact-SQL 或 PowerShell，針對 SQL Server 2012 中 AlwaysOn 可用性群組的可用性複本設定連接存取。

如需有關針對次要複本啟用唯讀存取之含意的資訊，以及連接存取簡介，請參閱＜關於可用性複本的用戶端連接存取 (SQL Server)＞和＜使用中次要：可讀取的次要複本 (AlwaysOn 可用性群組)＞。

• 開始之前： 

  必要條件和限制

  安全性

• 若要使用下列項目設定可用性複本的存取： 

  SQL Server Management Studio

  Transact-SQL

  PowerShell

• 後續操作： 針對可用性複本設定唯讀存取之後

• 相關工作

• 相關內容

開始之前

必要條件和限制

• 若要設定不同的連接存取，您必須連接到裝載主要複本的伺服器執行個體。

安全性

權限

工作	權限
若要在建立可用性群組時設定複本	需要系統管理員 (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. 在 [物件總管] 中，連接到裝載主要複本的伺服器執行個體，然後展開伺服器樹狀目錄。

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

3. 按一下要變更複本的可用性群組。

4. 以滑鼠右鍵按一下可用性複本，然後按一下 [屬性]。

5. 在 [可用性複本屬性] 對話方塊中，可以變更主要角色和次要角色的連接存取，如下所示：

   • 如果是次要角色，請從 [可讀取次要] 下拉清單中選擇一個新值，如下所示：

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

     • 僅限讀取意圖
       只允許與這個複本的次要資料庫進行唯讀連接。 可以讀取所有次要資料庫。

     • 是
       允許與這個複本的次要資料庫之間的所有連接，但只供讀取存取。 可以讀取所有次要資料庫。

   • 如果是主要角色，請從 [主要角色的連接模式] 下拉清單中選擇一個新值，如下所示：

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

     • 允取讀取/寫入連接
       當 Application Intent 屬性設為 ReadWrite 或是未設定 Application Intent 連接屬性時，便會允許連接。 不允許 Application Intent 連接屬性設為 ReadOnly 的連接。 這有助於防止客戶錯誤地將讀取意圖的工作負載連接至主要複本。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜搭配 SQL Server Native Client 使用連接字串關鍵字＞。

[回到頁首]

使用 Transact-SQL

若要設定可用性複本的存取

[!附註]

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

1. 連接到裝載主要複本的伺服器執行個體。

2. 如果您要針對新的可用性群組指定複本，請使用 CREATE AVAILABILITY GROUP Transact-SQL 陳述式。 如果您要加入或修改現有可用性群組的複本，請使用 ALTER AVAILABILITY GROUP Transact-SQL 陳述式。

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

     SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

     其中

     • NO
       不允許直接連接這個複本的次要資料庫。 無法讀取這些資料庫。 這是預設設定。

     • READ_ONLY
       只允許與這個複本的次要資料庫進行唯讀連接。 可以讀取所有次要資料庫。

     • ALL
       允許與這個複本的次要資料庫之間的所有連接，但只供讀取存取。 可以讀取所有次要資料庫。

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

   PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   其中

   • READ_WRITE
     不允許 Application Intent 連接屬性設為 ReadOnly 的連接。 當 Application Intent 屬性設為 ReadWrite 或是未設定 Application Intent 連接屬性時，便會允許連接。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜搭配 SQL Server Native Client 使用連接字串關鍵字＞。

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

範例 (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 指令程式。 修改現有的可用性複本時，請使用 Set-SqlAvailabilityReplica 指令程式。 相關參數如下所示：

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

     • AllowNoConnections
       不允許直接連接次要複本的資料庫，這些資料庫也不可用於讀取存取。 這是預設設定。

     • AllowReadIntentConnectionsOnly
       次要複本的資料庫只允許 Application Intent 屬性設為 ReadOnly 的連接。 如需有關這個屬性的詳細資訊，請參閱＜搭配 SQL Server Native Client 使用連接字串關鍵字＞。

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

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

     • AllowReadWriteConnections
       不允許 Application Intent 連接屬性設為 ReadOnly 的連接。 當 Application Intent 屬性設為 ReadWrite 或是未設定 Application Intent 連接屬性時，便會允許連接。 如需有關 Application Intent 連接屬性的詳細資訊，請參閱＜搭配 SQL Server Native Client 使用連接字串關鍵字＞。

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

   [!附註]

   若要檢視指令程式的語法，請使用 SQL Server 2012 PowerShell 環境中的 Get-Help 指令程式。 如需詳細資訊，請參閱＜取得 SQL Server PowerShell 說明＞。

若要設定和使用 SQL Server PowerShell 提供者

• SQL Server PowerShell 提供者

範例 (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

[回到頁首]

後續操作：針對可用性複本設定唯讀存取之後

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

• 當您使用 bcp 公用程式或 sqlcmd 公用程式時，您可以藉由指定 -K ReadOnly 參數，為啟用唯讀存取的任何次要複本指定唯讀存取。

• 若要啟用用戶端應用程式以連接到可讀取的次要副本：

  	必要條件	連結
  	確定可用性群組擁有接聽程式。	建立或設定可用性群組接聽程式 (SQL Server)
  	設定可用性群組的唯讀路由。	設定可用性群組的唯讀路由 (SQL Server)

容錯移轉之後可能會影響觸發程序和作業的因素

如果您的觸發程序和作業在非可讀取的次要資料庫或可讀取的次要資料庫上執行時會失敗，則需要撰寫觸發程序和作業的指令碼來檢查特定複本，判斷資料庫是主要資料庫或是可讀取的次要資料庫。 若要取得這項資訊，請使用 DATABASEPROPERTYEX 函數傳回資料庫的 Updatability 屬性。 若要識別唯讀資料庫，請指定 READ_ONLY 做為值，如下所示：

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

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

[回到頁首]

相關工作

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

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

[回到頁首]

相關內容

• AlwaysOn：可讀取次要的價值主張

• AlwaysOn：為何有兩個選項可針對讀取工作負載啟用次要複本？

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

• AlwaysOn：我剛剛啟用可讀取次要複本，但我的查詢被封鎖？

• AlwaysOn：在可讀取次要複本、唯讀資料庫和資料庫快照集上提供最新的統計資料

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

• AlwaysOn：在次要複本上執行報表工作負載時，對主要工作負載的影響

• AlwaysOn：可讀取次要上報表工作負載對應至快照集隔離的影響

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

• AlwaysOn：可讀取次要和資料延遲

[回到頁首]

請參閱

概念

AlwaysOn 可用性群組概觀 (SQL Server)

使用中次要：可讀取的次要複本 (AlwaysOn 可用性群組)

關於可用性複本的用戶端連接存取 (SQL Server)