[AlwaysOn] 建立SQL Server高可用性組T-SQL語法：例項

AUTOMATED_BACKUP_PREFERENCE

SECONDARY

此自動備份首選項指示備份應發生在輔助副本上，除非主副本是唯一聯機副本(這是預設行為)。This   automated backup preference indicates that backups should occur on a   secondary replica except when the primary replica is the only replica online   (this is the default behavior).要使“自動備份”首選項設定具有任何效果，您需要在可用性資料庫上編寫備份作業指令碼，以將“自動備份”首選項考慮在內。For the   AUTOMATED_BACKUP_PREFERENCE setting to have any effect, you need to script   backup jobs on the availability databases to take the automated backup   preference into account.

FAILURE_CONDITION_LEVEL

3

此故障條件級別設定指定應在關鍵的SQL Server內部錯誤(如孤立的spinlocks、嚴重的寫訪問衝突或轉儲過多)上啟動自動故障轉移。This failure condition level   setting specifies that an automatic failover should be initiated on critical   SQL Server internal errors, such as orphaned spinlocks, serious write-access   violations, or too much dumping.

HEALTH_CHECK_TIMEOUT

600000

此執行狀況檢查超時值(60秒)指定WSFC群集等待60000毫秒，以便sp_server_diagnostics系統儲存過程返回有關伺服器例項的伺服器執行狀況資訊，該伺服器例項在主機伺服器例項慢或掛起之前，群集自動關聯同步提交副本。(預設值為30000毫秒)。This health check timeout value, 60 seconds, specifies that the   WSFC cluster waits 60000 milliseconds for the   system stored procedure to return server-health information about a server   instance that is hosting a synchronous-commit replica with automatic before   the cluster assumes that the host server instance is slow or hung. (The   default value is 30000 milliseconds).

ENDPOINT_URL

TCP://COMPUTER01:5022

TCP://COMPUTER02:5022

TCP://COMPUTER03:5022

在本例中，系統是相同的域，因此端點URL可以使用計算機系統的名稱作為系統地址。In this example, the systems are the same domain, so the endpoint   URLs can use the name of the computer system as the system address.

AVAILABILITY_MODE

SYNCHRONOUS_COMMIT

SYNCHRONOUS_COMMIT

ASYNCHRONOUS_COMMIT

兩個副本使用同步提交模式。Two of the replicas use synchronous-commit   mode.同步時，它們支援故障轉移而不丟失資料。When synchronized, they   support failover without data loss. 第三個副本，使用非同步提交可用性模式。The third replica, which uses asynchronous-commit availability   mode.

FAILOVER_MODE

AUTOMATIC

AUTOMATIC

MANUAL

同步提交副本支援自動故障轉移和計劃的手動故障轉移。The   synchronous-commit replicas support automatic failover and planned manual   failover. 非同步提交可用性模式副本只支援強制手動故障轉移。The asynchronous-commit   availability mode replica supports only forced manual failover.

BACKUP_PRIORITY

30

30

90

與同步提交副本相比，非同步提交副本具有更高的優先順序90。A higher priority, 90, is assigned to the asynchronous-commit   replica, than to the synchronous-commit replicas.備份通常發生在承載非同步提交副本的伺服器例項上。Backups tend to occur on the server instance that hosts the   asynchronous-commit replica.

SECONDARY_ROLE

( ALLOW_CONNECTIONS =   NO,READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' )

( ALLOW_CONNECTIONS =   NO,READ_ONLY_ROUTING_URL = 'TCP://COMPUTER02:1433' )

( ALLOW_CONNECTIONS =   READ_ONLY, READ_ONLY_ROUTING_URL = 'TCP://COMPUTER03:1433' )

只有非同步提交副本可用作可讀的輔助副本。Only the   asynchronous-commit replica serves as a readable secondary replica.

指定計算機名和預設資料庫引擎埠號(1433)。Specifies the computer name and default Database Engine port   number (1433).

此引數是可選的。This argument is   optional.

PRIMARY_ROLE

( ALLOW_CONNECTIONS =   READ_WRITE, READ_ONLY_ROUTING_LIST = (COMPUTER03) )

( ALLOW_CONNECTIONS =   READ_WRITE, READ_ONLY_ROUTING_LIST = (COMPUTER03) )

( ALLOW_CONNECTIONS =   READ_WRITE, READ_ONLY_ROUTING_LIST = NONE )

在主角色中，所有副本都拒絕讀意圖連線嘗試。In the   primary role, all the replicas reject read-intent connection attempts.

如果本地副本在輔助角色下執行，則將讀意向連線請求路由到computer03。Read-intent connection requests are routed to COMPUTER03 if the   local replica is running under the secondary role.當該副本在主角色下執行時，將禁用只讀路由。When that replica runs under the primary role, read-only routing   is disabled.

此引數是可選的。This argument is   optional.

SESSION_TIMEOUT

10

10

10

此示例指定預設會話超時值(10)。This example specifies the default session timeout value (10).

此引數是可選的。This argument is   optional.