Availability Enhancements (Database Engine)

For protecting application databases in an enterprise environment from both planned and unplanned downtime, SQL Server 2012 introduces the AlwaysOn Availability Groups feature and a number of other high-availability enhancements.

In This Topic

• New or Enhanced Features in SQL Server 2012 SP1

• AlwaysOn SQL Server Failover Cluster Instances

• AlwaysOn Availability Groups

• Online Operations

New or Enhanced Features in SQL Server 2012 SP1

For a summary of all SQL Server 2012 SP1 enhanced features, see New or Enhanced Features in SQL Server 2012 SP1.

Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade

SQL Server 2012 SP1 introduces support for cross-cluster migration of AlwaysOn Availability Groups for deployments to a new Windows Server Failover Clustering (WSFC) cluster. A cross-cluster migration moves one AlwaysOn availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime. The cross-cluster migration process enables you to maintain your service level agreements (SLAs) when upgrading to a Windows Server 2012 cluster. SQL Server 2012 SP1 must be installed and enabled for AlwaysOn on the destination WSFC cluster. The success of a cross-cluster migration depends on thorough planning and preparation of the destination WSFC cluster.

For more information, see Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade.

[Top]

AlwaysOn SQL Server Failover Cluster Instances

• Multi-subnet failover clusters: A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. These subnets can be in the same location or in geographically dispersed sites. Clustering across geographically dispersed sites is sometimes referred to as Stretch clusters. As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets. With data replication, there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability. For more information, see SQL Server Multi-Subnet Clustering (SQL Server).

• Flexible failover policy for cluster health detection: In a SQL Server failover cluster instance, only one node can own the cluster resource group at a given time. The client requests are served through this node for that failover cluster instance. In the case of a failure, the group ownership is moved to another node in the failover cluster. This process is called failover. The improved failure detection introduced in SQL Server 2012, and addition of failure condition level property allows you to configure a more flexible failover policy. For more information, see Failover Policy for Failover Cluster Instances.

• Indirect checkpoints: The indirect checkpoints feature provides a database-specific alternative to automatic checkpoints, which are configured by a server property. Indirect checkpoints implements a new checkpointing algorithm for the Database Engine. This algorithm provides a more accurate guarantee of database recovery time in the event of a crash or a failover than is provided by automatic checkpoints. To ensure that database recovery does not exceed allowable downtime for a given database, you can specify the maximum allowable downtime for that database.

  Note

  An online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation.

  For more information, see Database Checkpoints (SQL Server).

[Top]

AlwaysOn Availability Groups

Deploying AlwaysOn Availability Groups involves creating and configuring one or more availability groups. An availability group is a container that defines a set user databases (availability databases) to fail over as a single unit, and a set of availability replicas to host copies of each availability database. Each availability group requires at least two availability replicas: the primary replica and one secondary replica.

AlwaysOn Availability Groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows:

• Multiple secondary replicas: one primary replica and up to four secondary replicas. For more information, see Overview of AlwaysOn Availability Groups (SQL Server).

  Important

  Each availability replica must reside on a different node of a single Windows Server Failover Clustering (WSFC) cluster. For more information about prerequisites, restrictions, and recommendations for availability groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

• Alternative availability modes: Asynchronous-commit mode and Synchronous-commit mode. For more information, see Availability Modes (AlwaysOn Availability Groups).

• Several failover modes: automatic failover, planned manual failover, and forced manual failover. For more information, see Failover and Failover Modes (AlwaysOn Availability Groups).

• Active secondary replicas, as follows:

  • Read-only access to the secondary replicas. For more information, see Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups).

  • Performing backup operations on secondary replicas. For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups).

  Active secondary capabilities improve IT efficiency and reduce cost through better resource utilization of secondary hardware. In addition, offloading read-intent applications and backup jobs to secondary replicas helps to improve performance on the primary replica.

• Availability group listeners that provide fast application failover after an availability group fails over. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

• A flexible failover policy for each availability group to provide some control over the automatic failover process. For more information, see Failover and Failover Modes (AlwaysOn Availability Groups).

• Automatic page repair for protection against page corruption. For more information, see Automatic Page Repair (Availability Groups/Database Mirroring).

• Forcing WSFC quorum (forced quorum). For more information, see Windows Server Failover Clustering (WSFC) with SQL Server.

• Encryption and compression, which provide a secure, high performing transport.

• Interoperation with the following SQL Server features:

  • Change data capture

  • Change tracking

  • Contained databases

  • Database encryption

  • Database snapshots

  • FILESTREAM

  • FileTable

  • Full-text search—Full-Text indexes are synchronized with AlwaysOn secondary databases.

  • Log shipping

  • Remote Blob Store (RBS)

  • Replication

  • Service Broker

  • SQL Server Agent

For more information, AlwaysOn Availability Groups: Interoperability (SQL Server).

AlwaysOn Availability Groups Tools

AlwaysOn Availability Groups provides an integrated set of tools to simplify deployment and management of availability groups, including:

• Transact-SQL DDL statements for creating and managing availability groups. For more information, see Overview of Transact-SQL Statements for AlwaysOn Availability Groups (SQL Server).

• Several SQL Server Management Studio wizards:

  • The New Availability Group Wizard creates and configures an availability group. In some environments, this wizard can also automatically prepare the secondary databases and start data synchronization for each of them. For more information, see Use the New Availability Group Dialog Box (SQL Server Management Studio).

  • The Add Database to Availability Group Wizard adds one or more primary databases to an existing availability group. In some environments, this wizard can also automatically prepare the secondary databases and start data synchronization for each of them. For more information, see Use the Add Database to Availability Group Wizard (SQL Server).

  • The Add Replica to Availability Group Wizard adds one or more secondary replicas to an existing availability group. In some environments, this wizard can also automatically prepare the secondary databases and start data synchronization for each of them. For more information, see Use the Add Replica to Availability Group Wizard (SQL Server Management Studio).

  • The Fail Over Availability Group Wizard initiates a manual failover on an availability group. Depending on the configuration and state of the secondary replica that you specify as the failover target, the wizard can perform either a planned or forced manual failover. For more information, see Use the Fail Over Availability Group Wizard (SQL Server Management Studio).

• The AlwaysOn Dashboard provides an at-a-glance view of the health of an availability group. To help database administrators make quick operational decisions, the dashboard provides visual indicators of the key states of availability groups and their component availability replicas and databases. The dashboard also provides launch points for various troubleshooting scenarios. For more information, see Use the AlwaysOn Dashboard (SQL Server Management Studio).

• The Object Explorer Details pane displays basic information about existing availability groups. For more information, see Use the Object Explorer Details to Monitor Availability Groups (SQL Server Management Studio).

• PowerShell cmdlets. For more information, see Overview of PowerShell Cmdlets for AlwaysOn Availability Groups (SQL Server).

[Top]

Online Operations

• Extended support for online index builds

  Indexes that include columns of large object (LOB) data—varchar(max), nvarchar(max), varbinary(max), or XML data—can now be built, rebuilt, or dropped online. For information about online index operations, see Guidelines for Online Index Operations and Perform Index Operations Online.

• Reduced downtime for application upgrade

  Adding table columns that contain default values is now a metadata-only operation, so only a brief exclusive lock needs to be taken on the object, typically lasting for less than a second. Long-term exclusive table locks are no longer needed. For information about adding table columns, see Add Columns to a Table (Database Engine).

[Top]

See Also

Concepts

What's New (Database Engine)

AlwaysOn Availability Groups (SQL Server)

AlwaysOn Failover Cluster Instances (SQL Server)

Windows Server Failover Clustering (WSFC) with SQL Server