Getting Started with AlwaysOn Availability Groups (SQL Server)

This topic introduces the steps for configuring instances of SQL Server 2012 to support AlwaysOn Availability Groups and for creating, managing, and monitoring an availability group.

• **Before You Begin: **

  Recommended Reading

• **Getting started with: **

  Configuring an instance of SQL Server to support AlwaysOn Availability Groups

  Creating and configuring a new availability group

  Managing availability groups, replicas, and databases

  Monitoring availability groups

• Related Content

Before You Begin

Recommended Reading

Before you create your first availability group, we recommend that you read the following topics:

• Overview of AlwaysOn Availability Groups (SQL Server)

• Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

Configuring an Instance of SQL Server to Support AlwaysOn Availability Groups

	Step	Links
	Enable AlwaysOn Availability Groups. The AlwaysOn Availability Groups feature must be enabled on every instance of SQL Server 2012 that is to participate in an availability group. Prerequisites:  The host computer must be a Windows Server Failover Clustering (WSFC) node. For information about the other prerequisites, see "SQL Server Instance Prerequisites and Restrictions" in Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).	Enable and disable AlwaysOn Availability Groups
	Create database mirroring endpoint (if none). Ensure that each server instance possesses a database mirroring endpoint. The server instance uses this endpoint to receive AlwaysOn Availability Groups connections from other server instances.	To determine whether database mirroring endpoint exists: sys.database_mirroring_endpoints For Windows Authentication:  To create a database mirroring endpoint, using:  New Availability Group Wizard Transact-SQL SQL Server PowerShell For certificate authentication:  To create a database mirroring endpoint, using: Transact-SQL

[Top]

Creating and Configuring a New Availability Group

	Step	Links
	Create the availability group. Create the availability group on the instance of SQL Server that hosts the databases to be added to the availability group. Minimally, create the initial primary replica on the instance of SQL Server where you create the availability group. You can specify from one to four secondary replicas. For information about availability group and replica properties, see CREATE AVAILABILITY GROUP (Transact-SQL). We strongly recommend that you create an availability group listener. Prerequisites:  The instances of SQL Server that host availability replicas for a given availability group must reside on separate nodes of a single WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters. For information about the other prerequisites, see "Availability Group Prerequisites and Restrictions", "Availability Database Prerequisites and Restrictions", and "SQL Server Instance Prerequisites and Restrictions" in Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).	To create an availability group you can use any of the following tools: New Availability Group Wizard Transact-SQL SQL Server PowerShell
	Join secondary replicas to the availability group. Connect to each instance of SQL Server 2012 that is hosting a secondary replica, and join the local secondary replica to the availability group.	Join a secondary replica to an availability group Tip If you use the New Availability Group Wizard, this step is automated.
	Prepare secondary databases. On every server instance that is hosting a secondary replica, restore backups of the primary databases using RESTORE WITH NORECOVERY.	Manually prepare a secondary database Tip The New Availability Group Wizard can prepare the secondary databases for you. For more information, see "Prerequisites for using full initial data synchronization" in Select Initial Data Synchronization Page (AlwaysOn Availability Group Wizards).
	Join secondary databases to the availability group. On every server instance that is hosting a secondary replica, join each local secondary database to the availability group. On joining the availability group, a given secondary database initiates data synchronization with the corresponding primary database.	Join a secondary database to an availability group Tip The New Availability Group Wizard can perform this step if every secondary database exists on every secondary replica.
	Create an availability group listener.  This step is necessary unless you already created the availability group listener while creating the availability group.	Create or Configure an Availability Group Listener (SQL Server)
	Give the listener's DNS host name to application developers.  Developers needs to specify this DNS name in the connection strings to direct connection requests to the availability group listener. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).	"Follow Up: After Creating an Availability Group Listener" in Create or Configure an Availability Group Listener (SQL Server)
	Configure Where Backup Jobs.  If you want to perform backups on secondary databases, you must create a backup job script that takes the automated backup preference into account. Create a script for each database in the availability group on every server instance that hosts an availability replica for the availability group.	"Follow Up: After Configuring Backup on Secondary Replicas" in Configure Backup on Availability Replicas (SQL Server)

[Top]

Managing Availability Groups, Replicas, and Databases

Note

For information about availability group and replica properties, see CREATE AVAILABILITY GROUP (Transact-SQL).

Managing existing availability groups involves one or more of the following tasks:

Task	Link
Modify the flexible failover policy of the availability group to control the conditions that cause an automatic failover. This policy is relevant only when automatic failover is possible.	Configure the flexible failover policy of an availability group
Perform a planned manual failover or a forced manual failover (with possible data loss), typically called forced failover. For more information, see Failover and Failover Modes (AlwaysOn Availability Groups).	Perform a planned manual failover Perform a forced manual failover
Use a set of predefined policies to view the health of an availability group and its replicas and databases.	Use policy-based management to view the health of availability groups Use the AlwaysOn Group Dashboard
Add or remove a secondary replica.	Add a secondary replica Remove a secondary replica
Suspend or resume an availability database. Suspending a secondary database keeps at its current point in time until you resume it.	Suspend a database Resume a database
Add or remove a database.	Add a database Remove a secondary database Remove a primary database
Reconfigure or create an availability group listener.	Create or configure an availability group listener
Delete an availability group.	Delete an availability group
Troubleshoot add file operations. This might be required if the primary database and a secondary database have different file paths.	Troubleshoot a failed add-file operation
Alter availability replica properties.	Change the Availability Mode Change the Failover Mode Configure Backup Priority (and Automated Backup Preference) Configure Read-Only Access Configure Read-Only Routing Change the Session-Timeout Period

[Top]

Monitoring Availability Groups

To monitor the properties and state of an AlwaysOn availability group you can use the following tools.

Tool	Brief Description	Links
System Center Monitoring pack for SQL Server	The Monitoring pack for SQL Server (SQLMP) is the recommended solution for monitoring availability groups, availability replica and availability databases for IT administrators. Monitoring features that are particularly relevance to AlwaysOn Availability Groups include the following: Automatic discoverability of availability groups, availability replicas, and availability database from among hundreds of computers. This enables you to easily keep track of your AlwaysOn Availability Groups inventory. Fully capable System Center Operations Manager (SCOM) alerting and ticketing. These features provide detailed knowledge that enables faster resolution to a problem. A custom extension to AlwaysOn Health monitoring using Policy Based management (PBM). Health roll ups from availability databases to availability replicas. Custom tasks that manage AlwaysOn Availability Groups from the System Center Operations Manager console.	To download the monitoring pack (SQLServerMP.msi) and SQL Server Management Pack Guide for System Center Operations Manager (SQLServerMPGuide.doc), see: System Center Monitoring pack for SQL Server
Transact-SQL	AlwaysOn Availability Groups catalog and dynamic management views provide a wealth of information about your availability groups and their replicas, databases, listeners, and WSFC cluster environment.	Monitor Availability Groups (Transact-SQL)
SQL Server Management Studio	The Object Explorer Details pane displays basic information about the availability groups hosted on the instance of SQL Server to which you are connected. Tip Use this pane to select multiple availability groups, replicas, or databases and to perform routine administrative tasks on the selected objects; for example, removing multiple availability replicas or databases from an availability group.	Use Object Explorer Details to monitor availability groups
SQL Server Management Studio	Properties dialog boxes enable you to view the properties of availability groups, replicas, or listeners and, in some cases, to change their values.	Availability Group Properties Availability Replica Properties Availability Group Listener Properties
System Monitor	The SQLServer:Availability Replica performance object contains performance counters that report information about availability replicas.	SQL Server, Availability Replica
System Monitor	The SQLServer:Database Replica performance object contains performance counters that report information about the secondary databases on a given secondary replica. The SQLServer:Databases object in SQL Server contains performance counters that monitor transaction log activities, among other things. The following counters are particularly relevant for monitoring transaction-log activity on availability databases: Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec.	SQL Server, Database Replica SQL Server, Databases Object

[Top]

Related Content

• **Video—Introduction to AlwaysOn: ** Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1: Introducing the Next Generation High Availability Solution

• **Video—A Deep Dive into AlwaysOn: ** Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 2: Building a Mission-Critical High Availability Solution Using AlwaysOn

• **Whitepaper: ** Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

• **Blogs: ** SQL Server AlwaysOn Team Blog: The official SQL Server AlwaysOn Team Blog

[Top]

See Also

Concepts

AlwaysOn Availability Groups (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Configuration of a Server Instance for AlwaysOn Availability Groups (SQL Server)

Creation and Configuration of Availability Groups (SQL Server)

Monitoring of Availability Groups (SQL Server)

Overview of Transact-SQL Statements for AlwaysOn Availability Groups (SQL Server)

Overview of PowerShell Cmdlets for AlwaysOn Availability Groups (SQL Server)