- sys.availability_databases_cluster
- sys.availability_group_listener_ip_addresses
- sys.availability_group_listeners
- sys.availability_groups
- sys.availability_groups_cluster
- sys.availability_read_only_routing_lists
- sys.availability_replicas
sys.availability_databases_cluster
Contains one row for each availability database on the instance of SQL Server that is hosting an availability replica for any AlwaysOn availability group in the Windows Server Failover Clustering (WSFC) cluster, regardless of whether the local copy database has been joined to the availability group yet. Note When a database is added to an availability group, the primary database is automatically joined to the group. Secondary databases must be prepared on each secondary replica before they can be joined to the availability group.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| group_id | • | uniqueidentifier | Unique identifier of the availability group in which the availability group, if any, in which the database is participating. NULL = database is not part of an availability replica of in availability group. |
|||
| group_database_id | • | uniqueidentifier | Unique identifier of the database within the availability group, if any, in which the database is participating. group_database_id is the same for this database on the primary replica and on every secondary replica on which the database has been joined to the availability group. NULL = database is not part of an availability replica in any availability group. |
|||
| database_name | • | sysname | Name of the database that was added to the availability group. |
TSQL
Sql 2012SELECT [group_id], [group_database_id], [database_name] FROM sys.availability_databases_cluster
Back to Top
sys.availability_group_listener_ip_addresses
Returns a row for every IP address that is associated with any AlwaysOn availability group listener in the Windows Server Failover Clustering (WSFC) cluster. Primary key: listener_id + ip_address + ip_sub_mask| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| listener_id | • | nvarchar(36) | Resource GUID from Windows Server Failover Clustering (WSFC) cluster. | |||
| ip_address | • | nvarchar(48) | Configured virtual IP address of the availability group listener. Returns a single IPv4 or IPv6 address. | |||
| ip_subnet_mask | • | nvarchar(15) | Configured IP subnet mask for the IPv4 address, if any, that is configured for the availability group listener. NULL = IPv6 subnet |
|||
| is_dhcp | • | bit | Whether the IP address is configured by DHCP, one of: 0 = IP address is not configured by DHCP. 1 = IP address is configured by DHCP |
|||
| network_subnet_ip | • | nvarchar(48) | Network subnet IP address that specifies the subnet to which the IP address belongs. | |||
| network_subnet_prefix_length | • | int | Network subnet prefix length of the subnet to which the IP address belongs. | |||
| network_subnet_ipv4_mask | • | nvarchar(45) | Network subnet mask of the subnet to which the IP address belongs. network_subnet_ipv4_mask to specify the DHCP NULL = IPv6 subnet |
|||
| state | • | tinyint | IP resource ONLINE/OFFLINE state from the WSFC cluster, one of: 1 = Online. IP resource is online. 0 = Offline. IP resource is offline. 2 = Online Pending. IP resource is offline but is being brought online. 3 = Failed. IP resource was being brought online but failed. |
|||
| state_desc | • | nvarchar(60) | Description of state, one of: ONLINE OFFLINE ONLINE_PENDING FAILED |
TSQL
Sql 2012SELECT [listener_id], [ip_address], [ip_subnet_mask], [is_dhcp], [network_subnet_ip], [network_subnet_prefix_length], [network_subnet_ipv4_mask], [state], [state_desc] FROM sys.availability_group_listener_ip_addresses
Back to Top
sys.availability_group_listeners
For each AlwaysOn availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the Windows Server Failover Clustering (WSFC) cluster. This view displays the real-time configuration gathered from cluster. Note This catalog view does not describe details of an IP configuration, that was defined in the WSFC cluster.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| group_id | • | uniqueidentifier | Availability group ID (group_id) from sys.availability_groups1. | |||
| listener_id | • | nvarchar(36) | GUID from the cluster resource ID. | |||
| dns_name | • | nvarchar(63) | Configured network name (hostname) of the availability group listener. | |||
| port | • | int | The TCP port number configured for the availability group listener. NULL = Listener was configured outside SQL Server and its port number has not been added to the availability group. To add the port, pleaseuse the MODIFY LISTENER option of the ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| is_conformant | • | bit | Whether this IP configuration is conformant, one of: 1 = Listener is conformant. Only €œOR€ relations exist among its Internet Protocol (IP) addresses. Conformant encompasses every an IP configuration that was created by the CREATE AVAILABILITY GROUP3 Transact-SQL statement. In addition, if an IP configuration that was created outside of SQL Server, for example by using the WSFC Failover Cluster Manager, but can be modified by the ALTER AVAILABILITY GROUP tsql statement, the IP configuration qualifies as conformant. 0 = Listener is nonconformant. Typically, this indicates an IP address that could not be configured by using SQL Server commands and, instead, was defined directly in the WSFC cluster. |
|||
| ip_configuration_string_from_cluster | • | nvarchar(max) | Cluster IP configuration strings, if any, for this listener. For example: IPv4 address: 10.120.19.155. IPv6 address: 2001::4898:23:1002:20f:1fff:feff:b3a3 NULL = Listener has no virtual IP addresses. |
TSQL
Sql 2012SELECT [group_id], [listener_id], [dns_name], [port], [is_conformant], [ip_configuration_string_from_cluster] FROM sys.availability_group_listeners
Back to Top
sys.availability_groups
availability group for which the local instance of SQL Server hosts an availability replica. Each row contains a cached copy of the availability group metadata.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| group_id | • | uniqueidentifier | Unique identifier (GUID) of the availability group. | |||
| name | • | sysname | Name of the availability group. This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC). | |||
| resource_id | • | nvarchar(40) | Resource ID for the WSFC cluster resource. | |||
| resource_group_id | • | nvarchar(40) | Resource Group ID for the WSFC cluster resource group of the availability group. | |||
| failure_condition_level | • | int | User-defined failure condition level under which an automatic failover must be triggered, one of the following integer values: ValueFailure Condition 1Specifies that an automatic failover should be initiated when any of the following occurs: The SQL Server service is down. The lease of the availability group for connecting to the WSFC failover cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server AlwaysOn Lease Timeout1. 2Specifies that an automatic failover should be initiated when any of the following occurs: The instance of SQL Server does not connect to cluster, and the user-specified health_check_timeout threshold of the availability group is exceeded. The availability replica is in failed state. 3Specifies 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. This is the default value. 4Specifies that an automatic failover should be initiated on moderate SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server internal resource pool. 5Specifies that an automatic failover should be initiated on any qualified failure conditions, including: Exhaustion of SQL Engine worker-threads. Detection of an unsolvable deadlock. The failure-condition levels (1€“5) range from the least restrictive, level 1, to the most restrictive, level 5. A given condition level encompasses all of the less restrictive levels. Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth. To change this value, use the FAILURE_CONDITION_LEVEL option of the ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| health_check_timeout | • | int | Wait time (in milliseconds) for the sp_server_diagnostics3 system stored procedure to return server-health information, before the server instance is assumed to be slow or hung. The default value is 30000 milliseconds (30 seconds). To change this value, use the HEALTH_CHECK_TIMEOUT option of the ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| automated_backup_preference | • | tinyint | Preferred location for performing backups on the availability databases in this availability group. One of: ValueDescription 0Primary. Backups should always occur on the primary replica. 1Secondary only. Performing backups on a secondary replica is preferable. 2Prefer Secondary. Performing backups on a secondary replica preferable, but performing backups on the primary replica is acceptable if no secondary replica is available for backup operations. This is the default behavior. 3Any Replica. No preference about whether backups are performed on the primary replica or on a secondary replica. For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)4. |
|||
| automated_backup_preference_desc | • | nvarchar(60) | Description of automated_backup_preference, one of: PRIMARY SECONDARY_ONLY SECONDARY NONE |
TSQL
Sql 2012SELECT [group_id], [name], [resource_id], [resource_group_id], [failure_condition_level], [health_check_timeout], [automated_backup_preference], [automated_backup_preference_desc] FROM sys.availability_groups
Back to Top
sys.availability_groups_cluster
AlwaysOn availability group in the Windows Server Failover Clustering (WSFC) . Each row contains the availability group metadata from the WSFC cluster.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| group_id | • | uniqueidentifier | Unique identifier (GUID) of the availability group. | |||
| name | • | sysname | Name of the availability group. This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC). | |||
| resource_id | • | nvarchar(40) | Resource ID for the WSFC cluster resource. | |||
| resource_group_id | • | nvarchar(40) | Resource Group ID for the WSFC cluster resource group of the availability group. | |||
| failure_condition_level | • | int | User-defined failure condition level under which an automatic failover must be triggered, one of the following integer values: ValueFailure Condition 1Specifies that an automatic failover should be initiated when any of the following occurs: The SQL Server service is down. The lease of the availability group for connecting to the WSFC failover cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server AlwaysOn Lease Timeout1. 2Specifies that an automatic failover should be initiated when any of the following occurs: The instance of SQL Server does not connect to cluster, and the user-specified health_check_timeout threshold of the availability group is exceeded. The availability replica is in failed state. 3Specifies 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. This is the default value. 4Specifies that an automatic failover should be initiated on moderate SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server internal resource pool. 5Specifies that an automatic failover should be initiated on any qualified failure conditions, including: Exhaustion of SQL Engine worker-threads. Detection of an unsolvable deadlock. The failure-condition levels (1€“5) range from the least restrictive, level 1, to the most restrictive, level 5. A given condition level encompasses all of the less restrictive levels. Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth. To change this value, use the FAILURE_CONDITION_LEVEL option of the ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| health_check_timeout | • | int | Wait time (in milliseconds) for the sp_server_diagnostics3 system stored procedure to return server-health information, before the server instance is assumed to be slow or hung. The default value is 30000 milliseconds (30 seconds). To change this value, use the HEALTH_CHECK_TIMEOUT option of ALTER AVAILABILITY GROUP2Transact-SQL statement. |
|||
| automated_backup_preference | • | tinyint | Preferred location for performing backups on the availability databases in this availability group. One of: ValueDescription 0Primary. Backups should always occur on the primary replica. 1Secondary only. Performing backups on a secondary replica is preferable. 2Prefer Secondary. Performing backups on a secondary replica preferable, but performing backups on the primary replica is acceptable if no secondary replica is available for backup operations. This is the default behavior. 3Any Replica. No preference about whether backups are performed on the primary replica or on a secondary replica. For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)4. |
|||
| automated_backup_preference_desc | • | nvarchar(60) | Description of automated_backup_preference, one of: PRIMARY SECONDARY_ONLY SECONDARY NONE |
TSQL
Sql 2012SELECT [group_id], [name], [resource_id], [resource_group_id], [failure_condition_level], [health_check_timeout], [automated_backup_preference], [automated_backup_preference_desc] FROM sys.availability_groups_cluster
Back to Top
sys.availability_read_only_routing_lists
Returns a row for the read only routing list of each availability replica in an AlwaysOn availability group in the WSFC failover cluster.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| replica_id | • | uniqueidentifier | Unique ID of the availability replica that owns the routing list. | |||
| routing_priority | • | int | Priority order for routing (1 is first, 2 is second, and so forth). | |||
| read_only_replica_id | • | uniqueidentifier | Unique ID of the availability replica to which a read-only workload will be routed. |
TSQL
Sql 2012SELECT [replica_id], [routing_priority], [read_only_replica_id] FROM sys.availability_read_only_routing_lists
Back to Top
sys.availability_replicas
of the availability replicas that belong to any AlwaysOn availability group in the WSFC failover cluster. If the local server instance is unable to talk to the WSFC failover cluster, for example because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. These rows will contain only the columns of data that are cached locally in metadata.| Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
|---|---|---|---|---|---|---|
| replica_id | • | uniqueidentifier | Unique ID of the replica. | |||
| group_id | • | uniqueidentifier | Unique ID of the availability group to which the replica belongs. | |||
| replica_metadata_id | • | int | ID for the local metadata object for availability replicas in the Database Engine. | |||
| replica_server_name | • | nvarchar(256) | Server name of the instance of SQL Server that is hosting this replica and, for a non-default instance, its instance name. | |||
| owner_sid | • | varbinary(85) | Security identifier (SID) registered to this server instance for the external owner of this availability replica. NULL for non-local availability replicas. |
|||
| endpoint_url | • | nvarchar(128) | String representation of the user-specified database mirroring endpoint that is used by connections between primary and secondary replicas for data synchronization. For information about the syntax of endpoint URLs, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server)1. NULL = Unable to talk to the WSFC failover cluster. To change this endpoint, use the ENDPOINT_URL option of ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| availability_mode | • | tinyint | The availability mode of the replica, one of: ValueMeaning 1Asynchronous commit. The primary replica can commit transactions without waiting for the secondary to write the log to disk. 2Synchronous commit. The primary replica waits to commit a given transaction until the secondary replica has written the transaction to disk. For more information, see Availability Modes (AlwaysOn Availability Groups)3. |
|||
| availability_mode_desc | • | nvarchar(60) | Description of availability_mode, one of: ASYNCHRONOUS_COMMIT SYNCHRONOUS_COMMIT To change this the availability mode of an availability replica, use the AVAILABILITY_MODE option of ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| failover_mode | • | tinyint | The failover mode4 of the availability replica, one of: ValueMeaning 1Manual failover. A failover to a secondary replica set to manual failover must be manually initiated by the database administrator. The type of failover that is performed will depend on whether the secondary replica is synchronized, as follows: If the availability replica is not synchronizing or is still synchronizing, only forced failover (with possible data loss) can occur. If the availability mode is set to synchronous commit (availability_mode = 2) and the availability replica is currently synchronized, manual failover without data loss can occur. 2Automatic failover. The replica is a potential target for automatic failovers. Automatic failover is supported only if the availability mode is set to synchronous commit (availability_mode = 2) and the availability replica is currently synchronized. To view a rollup of the database synchronization health of every availability database in an availability replica, use the synchronization_health and synchronization_health_desc columns of the sys.dm_hadr_availability_replica_states5 dynamic management view. The rollup considers the synchronization state of every availability database and the availability mode of its availability replica. Tip To view the synchronization health of a given availability database, query the synchronization_state and synchronization_health columns of the sys.dm_hadr_database_replica_states6 dynamic management view. |
|||
| failover_mode_desc | • | nvarchar(60) | Description of failover_mode, one of: MANUAL AUTOMATIC To change the failover mode, use the FAILOVER_MODE option of ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| session_timeout | • | int | The time-out period, in seconds. The time-out period is the maximum time that the replica waits to receive a message from another replica before considering connection between the primary and secondary replica have failed. Session timeout detects whether secondaries are connected the primary replica. On detecting a failed connection with a secondary replica, the primary replica considers the secondary replica to be NOT_SYNCHRONIZED. On detecting a failed connection with the primary replica, a secondary replica simply attempts to reconnect. Note Session timeouts do not cause automatic failovers. To change this value, use the SESSION_TIMEOUT option of ALTER AVAILABILITY GROUP2 Transact-SQL statement. |
|||
| primary_role_allow_connections | • | tinyint | Whether the availability allows all connections or only read-write connections, one of: 2 = All (default) 3 = Read write |
|||
| primary_role_allow_connections_desc | • | nvarchar(60) | Description of primary_role_allow_connections, one of: ALL READ_WRITE |
|||
| secondary_role_allow_connections | • | tinyint | Whether an availability replica that is performing the secondary role (that is, a secondary replica) can accept connections from clients, one of: 0 = No. No connections are allowed to the databases in the secondary replica, and the databases are not available for read access. This is the default setting. 1 = Read only. Only read-only connections are allowed to the databases in the secondary replica. All database(s) in the replica are available for read access. 2 = 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 (AlwaysOn Availability Groups)7. |
|||
| secondary_role_allow_connections_desc | • | nvarchar(60) | Description of secondary_role_allow_connections, one of: NO READ_ONLY ALL |
|||
| create_date | • | datetime | Date that the replica was created. NULL = Replica not on this server instance. |
|||
| modify_date | • | datetime | Date that the replica was last modified. NULL = Replica not on this server instance. |
|||
| backup_priority | • | int | Represents the user-specified priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)8. |
|||
| read_only_routing_url | • | nvarchar(256) | Connectivity endpoint (URL) of the read only availability replica. For more information, see Configure Read-Only Routing for an Availability Group (SQL Server)9. |
TSQL
Sql 2012SELECT [replica_id], [group_id], [replica_metadata_id], [replica_server_name], [owner_sid], [endpoint_url], [availability_mode], [availability_mode_desc], [failover_mode], [failover_mode_desc], [session_timeout], [primary_role_allow_connections], [primary_role_allow_connections_desc], [secondary_role_allow_connections], [secondary_role_allow_connections_desc], [create_date], [modify_date], [backup_priority], [read_only_routing_url] FROM sys.availability_replicas
Back to Top
No comments:
Post a Comment