December 6, 2012

AlwaysOn Availability Groups Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

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 2012
SELECT [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 options in a WITH DHCP clause of the CREATE AVAILABILITY GROUP1 or ALTER AVAILABILITY GROUP2 Transact-SQL statement.
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 2012
SELECT [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 2012
SELECT [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 2012
SELECT [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 2012
SELECT [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 2012
SELECT [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 2012
SELECT [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

Total Pageviews