Database Requirements

Overview

AppsAnywhere requires a number of databases to store configuration and application information.

It is recommended to host the database on an existing SQL Server cluster.

Management, licensing, backups, redundancy and maintenance of the database server remain the responsibility of the customer.

• AppsAnywhere must be installed on a redundant Database Engine Instance when in Production

• All services use a single connection string and need a constant connection to the database to operate

• A failed database connection can result in a full-service outage

• An existing database instance can be used and is recommended

• For a new database instance, follow the relevant version of SQL Server | Microsoft Learn or MySQL Documentation for server requirements and redundancy

• No additional features such as the Reporting Services are required, only the database engine

Refer to Connectivity Requirements to ensure all services can connect to the database.

Supported Database Servers

A fully licensed, production version of a database server is required. Trial, demo and express versions are not supported by database vendors in production environments.

AppsAnywhere

• Microsoft SQL Server 2014 to 2019

• MySQL 5.7.8 or later

Analytics

• Microsoft SQL Server 2014 to 2019

• With support for TLSv1.2

Cloudpaging

• Microsoft SQL Server 2017 to 2022

Database Management

Please ensure the DBA reads the following articles and implements the requirements:

1. Business continuity and database recovery - SQL Server | Microsoft Learn

2. Database Backup - SQL Server | Microsoft Docs

3. Transaction Log management (SQL Server) - SQL Server | Microsoft Docs

4. Database Tuning – AppsAnywhere Support

Database Configuration

Database containers, SQL logins and SQL users need to be created prior to deployment and configuration of servers.

A SQL script is provided below to create the required SQL logins and database users. 

• The database container names and logins must be created exactly as shown and must be created before running any of the scripts below

• All databases should be created in the same, redundant SQL instance (when using Microsoft SQL Server)

• The database schemas will be created automatically during configuration

• The script should be edited and used to correctly create the SQL logins then map them to the correct databases

• As the scripts are creating SQL logins, they must be edited so that an appropriate password is set, replacing the [Password] variable shown below

• The script will then set the SQL login to have db_owner user mapping role membership on the database

Database containers

SQL logins and SQL users

--Create the AppsAnywhere login and user
USE Master
GO
CREATE LOGIN appsanywhere_user WITH PASSWORD = '[Password]', DEFAULT_DATABASE = appsanywhere;
GO
USE AppsAnywhere
CREATE USER appsanywhere_user FOR LOGIN appsanywhere_user
GO
ALTER ROLE db_owner ADD MEMBER appsanywhere_user;
GO

--Create the Analyics login and user
Use appsanywhere_analytics;
GO
CREATE LOGIN appsanywhere_analytics_user WITH PASSWORD = '[Password]', DEFAULT_DATABASE = appsanywhere_analytics;
GO
CREATE USER appsanywhere_analytics_user FOR LOGIN appsanywhere_analytics_user
GO
ALTER ROLE db_datareader ADD MEMBER appsanywhere_analytics_user;
GO
GRANT CREATE TABLE to appsanywhere_analytics_user;
GO
GRANT CREATE VIEW to appsanywhere_analytics_user;
GO
CREATE SCHEMA appsanywhere_analytics_cache AUTHORIZATION appsanywhere_analytics_user;
GO
ALTER ROLE db_owner ADD MEMBER appsanywhere_user;
GO

--Add SQL Server permissions to the Analytics login
USE Master;
GRANT ALTER ANY CONNECTION TO appsanywhere_analytics_user;
GRANT VIEW SERVER STATE to appsanywhere_analytics_user;
GO

--Create the Cloudpaging login and user
USE Master
GO
CREATE LOGIN dbjbuser WITH PASSWORD = '[Password]', DEFAULT_DATABASE = streamdb;
GO
Use StreamDB
GO
CREATE USER dbjbuser FOR LOGIN dbjbuser
GO
ALTER ROLE db_owner ADD MEMBER dbjbuser;
GO

Analytics will continue to function without adding SQL Server permissions to the Analytics login, but it will not be able to terminate any long running queries.

This is unlikely to cause an issue due to the low volume of data collated.

Monitoring should be configured in SQL server to track and kill long running queries.

Please refer to https://docs.microsoft.com/sql for more information on configuring SQL server monitoring.

Manual creation

If the accounts are to be created manually, the SQL logins (appsanywhere_user, appsanywhere_analytics_user and dbjbuser) should be created under SQL server security as new logins:

SQL server authentication should be selected and a password set.

Enforce password expiration should be unchecked with the default database set (AppsAnywhere or StreamDB).

User mapping should then be used to create the user in the database and set db_owner permissions.

Default Schema should be left blank.

Database Sizing

The databases will grow over time based on the number of applications and delivery methods that you configure within your AppsAnywhere portal.

Please refer to the Database Tuning article for on going maintenance of the databases.

The following table contains approximate database size based on number the of applications and active users.

AppsAnywhere

The calculations above assume that there is an average of 2 delivery methods per application. All applications have 1 Cloudpaging delivery method and each active user has an average of 5 active Cloudpaged applications/sessions.

Sizes are specifically for the SQL Server .mdb database file and do not include the SQL Server database transaction log.

The SQL Server database transaction log can grow significantly during use. In order to ensure it does not consume large amounts of disk space we recommend daily backups of the transaction log to flush old records.

The following table contains approximate data sizes for the type of information stored within the AppsAnywhere database.

An additional 10MB is used for static configuration data.

Data categories above may span multiple tables. The row size does not include the table index size.