Migrating databases to a new SQL server

Introduction

Over time you might find there is a need to migrate your AppsAnywhere and/or Cloudpaging databases from the current database server to new infrastructure.

This is not a difficult task, but as you can imagine, not following the correct procedure will result in a total loss of service. If you plan to move your database(s) to a new instance then please raise a support ticket via AppsAnywhere Support so we can work with you to perform the move in a safe and efficient manner.

For any database migration request, we need at least 2 working days notice, from when we have both the old and new database details.

Server Specification

All AppsAnywhere servers (including Cloudpaging) need access to the database server. The recommended specification for a SQL server are as per Microsoft's recommendations. No additional SQL features such as the Reporting Services are required. 

The database mainly stores configuration and basic session information. An average database will be around 300-500MB with a SQL TRX log of about 1GB. This may vary depending on the number of usage and concurrent connections to the database. 

Expected growth is about 2-5GB per year. Database tuning and backups should be undertaken by your DBA. Please refer to the AppsAnywhere Documentation and Cloudpaging Server Admin Guide for further details.

Note: This article and procedure only applies to supported Microsoft SQL Server versions:

• 10.0 - SQL Server 2008*
• 10.5 - SQL Server 2008 R2*
• 11.0 - SQL Server 2012
• 12.0 - SQL Server 2014
• 13.0 - SQL Server 2016
• 14.0 - SQL Server 2017
• 15.0 - SQL Server 2019

*End of Microsoft support is on 9th July 2019

Database Names & Users

Product	Database Name	SQL login
Cloudpaging	StreamDB	dbjbuser
AppsAnywhere	appsanywhere	appsanywhere_user
Analytics	appsanywhere_analytics	appsanywhere_analytics_user

Note: verify the database names with a database administrator (DBA) and/or AppsAnywhere before planning any migration. 

Preparation

Your Cloudpaging license is tied to the database IP address, so a new license we will be required prior to moving the database. To generate a new license we will need to know the new IP address of the database (cluster and all nodes). To prevent delay, please allow at least two working days for us to generate a new license.

The database move itself is relatively simple but does need coordination to minimize downtime to end users. It is not possible to complete the whole process without any downtime as there will be a switchover from the old to new servers. The downtime should only be a matter of minutes, but we recommend you schedule a 30 minute outage with a 1-hour at risk window.

Important

To minimize the amount of downtime (service outage) required, AppsAnywhere recommend that Database Tuning is applied before and after database migration. This will ensure that the database backup, move and restore can be completed within the estimated outage window. 

Procedure

The process of moving the database can either be carried out by your DBA or by a AppsAnywhere engineer.

If requesting AppsAnywhere to do the work, then we will need to test that we can connect to both the old and new database servers with the required permissions and file transfer at least two working days before the database migration is due to take place.

If your DBA will be moving the database they should not need our notes, but for clarity and to ensure all steps are completed, please request that they follow Microsoft documentation and these steps:

Estimated outage: <30 minutes (dependent on database size)

1. At the agreed migration time AppsAnywhere will stop all connections to the old database. **Service outage begins**
2. The DBA should then, backup the database on the current SQL server
3. Take the old database offline
4. Restore the database backup to the new SQL server (with the same database name)
5. Ensure that the relevant SQL login is migrated to the database security > logins section 
6. Set the SQL login with the correct default database
7. Ensure the SQL login has db_owner permissions on the database
8. Run the orphaned users command to check and (if required) map the account GUID’s
9. Finally, AppsAnywhere will update the database connection string(s) to reference the new database location and restart the service(s). **Service outage ends**

Orphaned users command:

To check for orphaned users run the following commands.

Cloudpaging:

USE StreamDB 
EXEC sp_change_users_login 'Report'; 

AppsAnywhere:

USE appsanywhere
EXEC sp_change_users_login 'Report'; 

Analytics:

USE appsanywhere_analytics
EXEC sp_change_users_login 'Report'; 

Note: the database names can be different than those specified above.

If there are orphaned users that means the account on the SQL server does not match the account in the actual database.

To resolve orphaned users:

Please refer to Microsoft Troubleshoot orphaned users (SQL Server) and use one of the following:

Option 1

If you already have a login which you want to map your database user to, run the following (note that the first instance of 'dbjbuser' is the user in the database, the second instance is the login to be mapped to)

EXEC sp_change_users_login 'update_one', 'dbjbuser', 'dbjbuser';

Option 2

If you don’t already have a login to map to, sp_change_users_login can create a login with a password. The following code resolved this for dbjbuser with a password of ‘abcdefg' as an example.

EXEC sp_change_users_login 'Auto_Fix', 'dbjbuser', NULL, 'abcdefg';

IMPORTANT: If using a high availability (HA) cluster or database mirroring, you will need to ensure the SIDs of the SQL accounts on all SQL servers are the same. Otherwise, when fail over occurs, the system will not be able to connect to the database returning an error along the lines of 'database is read only'. Please refer to Microsoft's Troubleshoot orphaned users (SQL Server) article to  resolve mismatched SIDs.

Re-configuration and Testing

Once the database has been moved AppsAnywhere will connect the servers to the new database instance. After completion, both AppsAnywhere and the customer will test that the move has been successful, ensuring that applications launch and no errors are returned when navigating the AppsAnywhere portal. If required, redundancy will also be tested.

All services would fail to start if the database connection failed, and as the old database will be offline, it is sufficient to simply test that each service is online and functioning as expected (applications launch and no errors are returned).

Rollback

If for any reason we encounter an issue that cannot be resolved within the agreed downtime window, then we would change the connection strings to point back to the old database servers to restore service.