Migrating your on-premises Harmony Enterprise database to Azure SQL is a two-step process: first use the Microsoft Data Migration Assistant (DMA) tool to perform the primary migration, then use the IHS Markit script to migrate users and repair database objects that the DMA tool is not capable of migrating.
Step 1: Migrate with the DMA tool
To perform the primary migration:
1. Back up your existing Harmony Enterprise database.
| Note: | Ensure that all Harmony Enterprise users have disconnected from the database before backing up. Otherwise, wells that are being edited can become permanently locked. |
2. Upgrade your database to the latest version.
a. From an on-premises computer, install and launch the Harmony Enterprise Database Manager. Make sure the Database Manager version and the Harmony Enterprise version are the same. (Harmony Enterprise 2020.2 is the earliest version that supports cloud deployment.)
b. In the Upgrade Database tab, type the server name, database name, and your authentication information. Click Upgrade Database and wait for this to complete.
3. In Azure, create a blank database as the target for your migrated Harmony Enterprise database. To do this, open the Azure portal, go to the SQL server instance you intend to use, and click + Create database.
4. Use the Microsoft Data Migration Assistant (DMA) tool to perform the migration.
a. Download the DMA tool and install it.
b. Click the + icon and start a new migration. Select Schema and data for Migration scope.
c. “Select source” step: Connect to your source server and select the Harmony Enterprise database to be migrated.
d. “Select target” step: type the Azure server name. You can find this in the Azure portal in the top-right of the SQL server’s Overview tab, under “Server name” (for example, my-harmony-sql-server.database.windows.net). Type your admin SQL server login info and click Connect. Select the target database where you want to migrate your data.
| Note: | Windows-authenticated users may see errors beside their names, and the HarmonyCertificate1_User is ineligible for migration. Do not worry about these errors because they are corrected in step 2: user migration and database repair. |
e. The resulting generated script needs to be modified.
- Copy the entire generated SQL script and save it as a text file on your computer.
- Open a Powershell window and navigate to the directory where you saved the SQL file.
- In the code snippet below, replace <FILENAME> with the name of the SQL file you just saved, then copy and paste the code into the Powershell window and run it:
- ( Get-Content -Path ".\<FILENAME>.sql" ) -replace "AS \[db_owner\]", "AS [dbo]" -replace "TO \[db_owner\]", "TO [dbo]" > ".\<FILENAME>_modified.sql"
- Open the <FILENAME>_modified.sql file, and copy-and-paste the contents back into the DMA tool, replacing the original auto-generated SQL. Then, click Deploy Schema.
- “Script & deploy schema” step: After the schema deployment is finished, click the Export button to save the results of the schema deployment to disk as a JSON file. This file is useful for debugging, if there are problems with the migration. Click Migrate data.
- “Select tables” step: Click Start data migration. Wait for the migration to complete. When a “migration complete” message is displayed in the lower right corner, you can close the DMA tool.
Step 2: User migration and database repair
Microsoft’s DMA tool is not capable of migrating certain critical parts of the Harmony Enterprise database, such as users. A custom powershell script must be run after the DMA tool’s migration.
Our scripts migrate SQL logins and users, and convert all Windows users in your on-premises Harmony Enterprise database into Azure Active Directory users in your Azure SQL target database. Additionally, the tool makes necessary modifications to your migrated database to ensure that Harmony Enterprise features continue to function correctly after migration. However, we recommend that you do not migrate any users named “admin”, as this is a reserved user in the target database, and it does not migrate from the source as expected.
| Note: | This migration process does not migrate Windows groups. You need to recreate these manually in the cloud, and re-assign any Harmony Enterprise role-based-security permissions |
From a computer that has access to both your on-premises Harmony Enterprise database and your target Azure SQL server:
1. Install Powershell 7. It can be installed alongside your existing powershell version, if needed.
2. On your local drive, go to: C:\Program Files (x86)\SPGlobal\Harmony Enterprise <version 20.2 or higher>\Database Manager\Cloud. Open the file runPostMigration.ps1 in a text editor, or independent development environment (IDE).
Note: Release versions 20.2 - 22.2 are in the Program Files (x86) \IHS Markit folder.
3. Replace the function arguments in this script with your own.
4. Launch Powershell 7 and navigate to where the ps1 script is saved. Type in the following and click Enter: ./runPostMigration.ps1
You may be prompted to install the sqlserver module for Powershell. When the script is finished, a SQL file is generated.
5. Follow the instructions at the top of the generated SQL file to learn how to run the file on the target migrated database.
| Note: | All SQL users’ passwords are changed to Harmony!1234567890 by running these migration scripts. |