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; however, a dedicated instance can be created if preferable.

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

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 2012 to 2019

  • MySQL 5.7.8 or later

  • MariaDB 10.0 or later

Analytics

Cloudpaging

  • Microsoft SQL Server 2012 to 2019

Database Managment

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

In line with Microsoft, database mirroring, which was deprecated in SQL Server 2012, is not supported with AppsAnywhere.

Database Configuration

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

Scripted database configuration

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

  • SQL database containers, logins and users should not be created before running the scripts

  • All databases should be created in the same, redundant SQL instance

  • The database schemas will be created automatically during configuration

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

  • Password must not contain any special characters (£$%^*@ etc.) but must include at least one number, one uppercase letter and one lowercase letter

Create Databases and SQL accounts

--Create the database containers
CREATE DATABASE AppsAnywhere;
CREATE DATABASE AppsAnywhere_Analytics;
CREATE DATABASE StreamDB;

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

--Create the Analyics login and users
USE Master;
GO
CREATE LOGIN appsanywhere_analytics_user WITH PASSWORD = '[Password]', DEFAULT_DATABASE = appsanywhere_analytics;
GO
Use 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
CREATE USER appsanywhere_user FOR LOGIN appsanywhere_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
SQL

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 database configuration

  1. Create the database container(s) by right clicking on Databases and selecting New Database

  2. Name the database and click ok

  3. Create SQL logins (appsanywhere_user , appsanywhere_analytics_user and dbjbuser) under SQL server security as new logins

  4. SQL server authentication should be selected and a password set

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

  6. User mapping should then be used to create the SQL user in the database

  7. Assign db_owner permissions to the SQL user

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.

Initial sizing estimates are:

Database name

AppsAnywhere

Initial Sizing

50MB-100MB

Database name

AppsAnywhere_Analytics

Initial Sizing

50MB-100MB

Database name

StreamDB

Initial Sizing

300MB-500MB

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

AppsAnywhere


Total number of active users

Apps

250

500

1000

2000

50

190 MB

359 MB

689 MB

1349 MB

100

191 MB

360 MB

698 MB

1374 MB

200

192 MB

361 MB

699 MB

1375 MB

400

198 MB

367 MB

705 MB

1381 MB

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 it is recommended to run 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.

Data Type

Row Size (per 50 rows)

Application Data

60 KB

Application Delivery Methods

200 KB

Audit Change Event log

42 KB

AppsAnywhere Active Session and Identity information

400 KB

Cloudpaging Active Session Information

3300 KB

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.

Cloudpaging and Parallels RAS

Refer to Cloudpaging and Parallels documentation for further database sizing information.