You can 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
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.
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.
To run your Platform databases in one Availability Group, you need the following
SQL Server 2016 is required because of Microsoft Distributed TransactionCo-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.
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.
All SQL Server instances that are intended to run on Platform 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.
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).
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 or later. Once that requirement has been met, upgrade your Platform. Post-upgrade, follow one of the two migration workflows. Also see the requirements section for more information about SQL Server configuration steps listed below.