[AlwaysOn] 创建SQL Server高可用性组T-SQL语法：SECONDARY_ROLE子句

SECONDARY_ROLE ( ... )

指定当此可用性副本当前拥有辅助角色(即，每当它是辅助副本时)时生效的特定于角色的设置。Specifies role-specific settings that take effect if this availability replica currently owns the secondary role (that is, whenever it is a secondary replica). 在括号内，指定一个或两个辅助角色选项。Within the parentheses, specify either or both secondary-role options. 如果两者都指定，请使用逗号分隔的列表。If you specify both, use a comma-separated list.

辅助角色选项如下：The secondary role options are as follows:

ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }

指定执行辅助角色(即充当辅助副本)的给定可用性副本的数据库是否可以接受来自客户端的连接，其中之一：Specifies whether the databases of a given availability replica that is performing the secondary role (that is, is acting as a secondary replica) can accept connections from clients, one of:

NO

不允许用户连接到此副本的辅助数据库。No user connections are allowed to secondary databases of this replica. 它们不可用于读取访问。They are not available for read access. 这是默认行为。This is the default behavior.

READ_ONLY

仅允许连接到辅助副本中的数据库，其中application intent属性设置为readonly。Only connections are allowed to the databases in the secondary replica where the Application Intent property is set to ReadOnly. 有关此属性的详细信息，请参阅将连接字符串关键字用于SQL Server本机客户端。For more information about this property, see Using Connection String Keywords with SQL Server Native Client.

ALL

允许所有到辅助副本中数据库的连接进行只读访问。All connections are allowed to the databases in the secondary replica for read-only access.

有关详细信息，请参阅活动的辅助副本：可读的辅助副本(始终在可用性组上)。For more information, see Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

READ_ONLY_ROUTING_URL ='TCP://system-address:port'

指定用于将只读连接请求路由到此可用性副本的URL。Specifies the URL to be used for routing read-intent connection requests to this availability replica. 这是SQL Server数据库引擎侦听的URL。This is the URL on which the SQL Server Database Engine listens. 通常，SQL Server数据库引擎的默认实例侦听TCP 1433端口。Typically, the default instance of the SQL Server Database Engine listens on TCP port 1433.

对于命名实例，可以通过查询sys.dm_tcp_listener_states动态管理视图的port和type_desc列来获取端口号。For a named instance, you can obtain the port number by querying the port and type_desc columns of the sys.dm_tcp_listener_states dynamic management view. 服务器实例使用Transact-SQL侦听器(type_desc='tsql')。The server instance uses the Transact-SQL listener ( type_desc='TSQL').

有关计算副本的只读路由URL的详细信息，请参阅计算AlwaysOn的只读路由URL。For more information about calculating the read-only routing URL for a replica, see Calculating read_only_routing_url for Always On.

注释 Note

对于SQL Server的命名实例，应将Transact-SQL侦听器配置为使用特定端口。For a named instance of SQL Server, the Transact-SQL listener should be configured to use a specific port. 有关详细信息，请参阅配置服务器以侦听特定的TCP端口(SQL Server配置管理器)。For more information, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/81227/viewspace-2654210/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/81227/viewspace-2654210/