Migrate ACM Database from Microsoft SQL Server to Azure SQL Database

To start with are some important definitions:

SQL Server on Azure VM:  Microsoft SQL Server running on a virtual machine in the cloud.  This is the same as if it were SQL Server running on a machine in the office.

Azure SQL Managed Instance:  This is very nearly the same as Microsoft SQL Server, but the user does not have access to the machine the database is running on.

Azure SQL Database:  This is a variation of Microsoft SQL, but is missing some features that the ACM Database requires..

Migrating

Only when migrating from an on-premise ACM database to an Azure SQL Database must you take any extra steps. If you wish to migrate an on-premise ACM database to a SQL Server on Azure VM or to an Azure SQL Managed Instance you do not need to take any further steps.

Two options for migrating to Azure SQL Database are listed below.

OPTION 1: The simplest - Create a new database and import:

1. Create an empty database on the Azure SQL via Azure - DO NOT use the create database button in ACM Monitor - it will fail.

2. Stop all ACM processes

3. Open the ACM Monitor-> Database ->Manage Database form

4. Update the connection information to connect to the database in step 1

5. Check the “Azure” checkbox

6. Click “Create/Update” database

7. Click Next

8. Select “Create tables and procedures in existing database”

9. Click Next, next, Finish

10. Decide if the Reports should share the database connection or not (update the Reports tab if not)

11. Restart all ACM processes

12. Open ACM Config and import the configuration from the previous database.

OPTION 2: The most comprehensive - Migrate everything including history etc.

1. Stop all ACM processes

2. Backup the current SQL database

3. On the SQL Server, run the following command:

   1. DROP FUNCTION dbo.GetNextRunTime

4. Do the migration

5. Against the new Azure SQL Database run

   1. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDbVersionHistory]') ) BEGIN CREATE TABLE [dbo].[tblDbVersionHistory](        [RowID] [smallint] IDENTITY(1,1) NOT NULL,        [DbVersion] [varchar](15) NOT NULL,        [DateUpdated] [datetime] NOT NULL,        [Description] [varchar](400) NULL ) ON [PRIMARY] ALTER TABLE [dbo].tblDbVersionHistory ADD  CONSTRAINT [DF_tblDBVersionHistory_DateUpdated]  DEFAULT (getdate()) FOR [DateUpdated] END GO UPDATE dbo.tblDbVersion SET DbVersion = 1 INSERT into dbo.tblDbVersionHistory (DbVersion, Description) VALUES (1, 'Setting DBVersion back to 1 to migrate to Azure SQL')

6. Open the ACM Monitor-> Database ->Manage Database form

7. Update the connection information to connect to the database created in step 4

8. Check the “Azure” checkbox

9. Click “Create/Update” database

10. Click Next

11. Select “Update Existing Database”

12. Click Next, next, Finish

13. Decide if the Reports should share the database connection or not (update the Reports tab if not)

14. Restart ACM services