Overview

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

Please refer to Server Requirements for the supported versions.

It is recommended to host the database on an existing SQL Server cluster, however a dedicated instance can be created if preferable.

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

If installing a new Microsoft SQL server, use Microsoft recommended specifications for requirements and redundancy.

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

High Availability

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

  1. Disaster recovery for SQL Server - SQL Server | Microsoft Docs

  2. Database Backup - SQL Server | Microsoft Docs

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

  4. Database Tuning – AppsAnywhere Support

AppsAnywhere must be installed on a high availability SQL instance when in Production

All services need a constant connection to the database to operate

Any failure in the connection will result in a full service outage

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

Database Configuration

Database containers will need to be created prior to deployment and configuration of any servers according to the details below.

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

  • 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 name:

AppsAnywhere

Usage:

This database will be used to store all AppsAnywhere configuration and details of the applications.

Permissions:

A SQL login named appsanywhere_user must be assigned db_owner permissions for the AppsAnywhere database.

Password:

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

Initial Sizing:

50MB-100MB (.mdf)

Database name:

appsanywhere_analytics

Usage:

This database will be used to store all Analytics configuration and details of the applications.

Permissions:

A SQL login named appsanywhere_user assigned with db_owner permissions for the appsanywhere_analytics database.

Password:

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

Initial Sizing:

50MB-100MB (.mdb)

Database name:

StreamDB

Usage:

This database will be used to store all Cloudpaging configuration and details of the applications.

Permissions:

A SQL login named dbjbuser must be assigned db_owner permissions for the StreamDB database.

Password:

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

Initial Sizing:

300MB-500MB (.mdf)

Create AppsAnywhere, Analytics and Cloudpaging SQL accounts

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


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 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.

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.