Configure SQL AlwaysOn Availability Groups in Windows Azure Pack

  • Article

 

Applies To: Windows Azure Pack

You can use the SQL AlwaysOn Availability Groups feature with the SQL Server service in Windows Azure Pack. The SQL AlwaysOn Availability Groups feature is available in Microsoft SQL Server 2012 Enterprise Edition and is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. This feature reduces the dependency of the tenant database on the fallibility of a single SQL instance.

For more information about SQL AlwaysOn Availability Groups, go to https://msdn.microsoft.com/library/hh510230.aspx.

Contained database creation is the core change that enables this feature. A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Details about contained databases can be found at https://msdn.microsoft.com/library/ff929071.aspx. Contained database creation enables administrators to add the databases to availability groups and initiate replication. Contained users are created against instance level users. In case of failover, all contained databases and contained users are replicated. The user continues to have access to the databases on the secondary without creating new logins on the secondary instance.

Prerequisites

Ensure the following before using SQL AlwaysOn Availability Groups:

  • The list of prerequisites for setting up SQL AlwaysOn Availability Groups can be found here.

  • To enable SQL AlwaysOn Availability Groups, run the following script on every SQL instance before creating SQL database through the Windows Azure Pack:

    sp_configure ‘contained database authentication’, 1
RECONFIGURE
GO

  • Ensure that all the secondary servers are configured as part of the AlwaysOn Availability Group before adding the availability group listener.

  • After the availability group listener has been used with Windows Azure Pack, avoid changing the above containment setting of the associated instance.

  • Provision a file share required for SQL AlwaysOn database, as described in the prerequisites.

Actions in the management portal for administrators

Do the following:

  • Create an availability group in SQL with the corresponding availability group listener. See https://msdn.microsoft.com/library/ff878399.aspx for details.

    Note

    Configure the AlwaysOn Group Listener to use the default port 1433.

  • Create a SQL group of type “High availability (AlwaysOn enabled).”

  • Specify a file share that is accessible to all primary and secondary servers that will be a part of this SQL Server group.

  • Add the availability group listener to the group you created above.

  • If you add a secondary server to the availability group after you have added the listener to Windows Azure Pack, ensure that repair connection for the listener is triggered on the SQL Server extension on the management portal for administrators. Otherwise, tenants will not be able to create new databases or resize, alter, or delete existing databases.

Actions in SQL Server

The administrator will need to perform the following actions in SQL Server itself:

  • Purge the file share specified for each SQL Server High Availability (AlwaysOn enabled) group regularly to free space. There is no automated process to do this.

  • Add a SQL Server Policy to the SQL Server management pack to track occurrences of failover. Once you add this policy, the management pack will automatically detect it and use it as a health model.

References

The following topics provide additional information about using the SQL AlwaysOn Availability Groups feature: