This is documentation for Apprenda 7 and 8.
Documentation for older versions are also available.

Always On Availability Groups Support for Platform Databases

With the release of Apprenda Cloud Platform version 8.0.0, you can now run Platform databases within a SQL Server Availability Group (AG), which provides a variety of options for Contingency Planning, like High Availability and Disaster Recovery, use cases for your Platform. This page explains the best configuration for Platform databases and AGs, and how to integrate Always on Availability Groups support on your Platform.

For more on the

Availability Group Configuration for Platform databases

All Platform Databases should be put into one Availability Group with the exception of the Auditing database, which can optionally be in a separate AG if deployed to a separate SQL Server instance. A single Availability Group for non-auditing databases creates an ideal configuration so that all Platform databases can failover together. In the figure below, the Auditing database is represented in the same AG as the other Platform databases.

Always On Availability Groups Policy options

The Platform supports any AG configuration in the AG Dashboard as long the requirements below are met. This means any number or combination of availability modes, backup options, failover modes, or replicas.

Requirements for Always On Availability Groups support

To run your Platform databases in one Availability Group, you need the following

  • Platform version 8.0.0 or later
  • SQL Server 2016 Enterprise Edition
  • Windows Server Failover Clustering on your SQL Server instance
  • The 'contained database authentication' Server Configuration Option set

SQL Server 2016 Enterprise Edition

SQL Server 2016 is required because of Microsoft Distributed Transaction Co-ordinator (MS DTC) issues with Always On Availability Groups. Enterprise Edition is required for running more than one database in an Availability Group of which there are several in the Apprenda Cloud Platform. Standard Edition only supports a failover environment for a single database. 

Windows Server Failover Clustering

Before adding Platform databases to AGs, you will need to ensure that you are running your SQL Server instances with Windows Server Failover Clustering (WSFC). For other pre-requirements for setting up SQL Server with AGs, please refer to Microsoft documentation, https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server.

Contained Database Authentication

All SQL Server instances intending to run Platform version 8.0 or higher require that you set the 'contained database authentication' Server Configuration Option.

Note: The Apprenda Cloud Platform does not support readable secondary replicas largely because they are typically used for reporting-only functions rather than as targets for failover. Database reads and writes are often indistinguishable on the Platform, which limits the benefits of using readable secondaries for reporting.

New Platform install with Always on Availability Support

When installing the Platform, only enter your SQL Server instance directly to the Installer. After installation, you can then connect to the AG Listener and port post install using automated Powershell scripts provided by your support representative. You can change the AG Listener and port at any point after install using the same script. Once connected to the AG, you should add Platform DBs to the AG primary replica, back up databases to a common file share, and then restores the DBs on one or more secondary replicas (depending on your AG configuration).

Migrate to Always On Availability Groups support on your Platform

There are two workflows you can choose from to begin using AAG on your Platform. You must be running at least SQL Server 2012 SP3 before you upgrade to 8.0.0. Once that requirement has been met, you can upgrade 8.0.0, and post-upgrade, follow one of the two migration workflows. Also see the requirements section for more information about SQL Server configuration steps listed below.

Option 1: Upgrade your current SQL Server instance(s) to use AAG

  1. Set 'contained database authentication' Server Configuration Option on your SQL Server instance(s)
  2. Run the migration script executable (provided by a support representative) to enable Containment of the Account and Developer Portal Databases
  3. Flush Platform Cache(s) from the Configuration>Cache Browser page in the SOC
  4. Upgrade SQL Server instance(s) to 2016 Enterprise Edition
  5. Create an AG with all Platform DBs & Listener (SQL Server will take care of replicating the DBs in the AG to the secondary replica)
  6. Use AG Listener script (provided by support representative) to remove connection from SQL Server Instance and add connection to newly provisioned 2016 AG Listener

Option 2: Move to new AAG SQL Server Instance from your current SQL Server Instance

  1. Run Migration Script executable (provided by a support representative) to enable Containment of the Account and Developer Portal Databases
  2. Flush Platform Cache(s) from the Configuration>Cache Browser page in the SOC
  3. Export all logins from the Platform SQL instance w/ SIDs
  4. ​Provision new SQL Server 2016 Enterprise Edition instance(s)
  5. Import all logins on new SQL Server 2016 instance(s) 
  6. Backup all Platform DBs
  7. Set 'contained database authentication' Server Configuration Option on new SQL Server instance(s)
  8. Restore Platform DBs on one SQL 2016 instance
  9. Create an AG with all Platform DBs & Listener (SQL Server will take care of replicating the DBs in the AG to the secondary replica)
  10. Use AG Listener script (provided by support representative) to remove connection from SQL Server Instance and add connection to newly provisioned 2016 AG Listener
  11. Take the original Platform DBs offline on the original SQL Server instance