Migrating MS SQL Databases to UTF-8

As a system and/or database administrator, you need to migrate your MS SQL database to a new UTF-8 one before upgrading your system to v24.

Important! Before migrating your database, make sure that you are familiar with all considerations and prerequisites for the migration. For more information, see Migrating AE Databases to UTF-8.

This page includes the following:

Prerequisites to Migrate MS SQL Databases Using the Migration Action Pack

As a system/database administration, you need to make sure that your system meets the requirements needed to use the UTF-8 DB Migration Action Pack.

In Client 0, set the following keys of the UC_SYSTEM_SETTINGS variable:
- SQLVAR_INTERNAL key to YES
- SQLVAR_MAX_ROWS key to 1000 lines or higher
- VAR_SECURITY_LEVEL to 3 (allow all)
Create a new, empty UTF-8 enabled AE database. MS SQL databases use the with the Latin1_General_100_CI_AS_SC_UTF8 character set.
You must have an Agent for Database Variables installed.
You must have an Agent for Database Jobs and Events (SQL Agent) installed and able to reach the source and destination databases.
You must set both Connection (CONN) objects for the source and destination databases respectively before executing the Workflow.
If you use an alternative MS SQL database schema, you must configure two additional Connection (CONN) objects in the MSSQL MQ sub-folder.
You must have a database link configured from the destination to the source database.

As before any migration or upgrade, make sure you also create a backup of your existing database.

For more information, see:

Configuring the Database Link for MS SQL Databases

Your MS SQL source and destination databases can be used in the same or different instances. If they are in different instances, Microsoft requires a linked server to establish the connection between them.

As a system/database administration, you must define if the databases are in the same instance (N) or in different ones (Y) and create the database link for migration to UTF-8 manually.

0 - No (default)

The destination database is in the same instance as the source database.
1 - Yes

The source and destination databases are in different instances. The DBA must create a linked server called UC4_SOURCE using sp_addlinkedserver. This linked server provides access to distributed, heterogeneous queries against OLE DB data sources.

Note: To execute sp_addlinkedserver, your database user must have the ALTER ANY LINKED SERVER permission. For more information, see the official SQL documentation on the permissions required for creating linked servers.

SQL Syntax

USE [master]
EXEC sp_addlinkedserver
      @server = N'UC4_SOURCE',
      @srvproduct = N'',
      @provider = N'MSOLEDBSQL',
      @datasrc = N'<DbSourceHostName>,<DbSourcePort>',
      @catalog = N'<SourceDbName>'
GO

Example

EXEC sp_addlinkedserver @server = N'UC4_SOURCE', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'autmicdb.test.com,1433', @catalog = N'AEDB'

For details, see Microsoft's sp_addlinkedserver (Transact-SQL) documentation.

After defining the link, please test the connection before you try to run the migration:

select count(*) from UC4_SOURCE.ae.dbo.OH

After you have created this linked server, you can run distributed queries against this server.

You also need to define if you are using an alternate database schema or not:

0 - No (default) There is only one database for all tables.
1 - Yes An additional database is used for Message Queue (MQ) tables in the Microsoft SQL Server.

For more information, see Integrating an Alternative MS SQL Server Schema.

Migrating the MS SQL Database with the Migration Action Pack

Before starting, make sure that you meet all the prerequisites listed above and that you have read all the considerations and recommendations, see:

Follow the steps below to use the Migration Action Pack to migrate your AE database to UTF-8.

Download the Migration Action Pack from https://marketplace.automic.com/. You can either search for Package.UTF8.DB.Migration, or simply type UTF8 to find it.

It contains the Workflow and Connection (CONN) objects that you need for the migration.
In a Client other than Client 0, go to the Administration perspective. You cannot execute the Workflow included in the Action Pack in Client 0.
Click the Packs tab at the bottom of the Navigation pane on the left, see Working with Packs and Plug-ins.
Click Install from File and select your downloaded package. Once selected, click Install.
Once your Pack has been installed successfully, right-click it and select Jump to Source to open it.
Open the SOURCE - MOVE.UC4.DB.TO.UTF8 - SETUP.OBJECTS folder. It contains two Connection (CONN) objects and an MSSQL_MQ folder with two additional ones to be defined in case that you use MS SQL with an alternative schema (two separate AE databases), see Integrating an Alternative MS SQL Server Schema.
Define the Connection (CONN) objects with the required connection data for the source and the destination databases:

In the Connection sections:
- Database Type
  
  The type of your source/destination database.
- Server:Port
  
  Name of the source/destination computer on which the database is available and the port number.
- Database Name
  
  Name of the source/destination database that should be used.
Test the database connections using the Test button in the Connection Test section of each Connection object.
Execute the PCK.AUTOMIC_UTF-8-MIG.START Workflow. It guides you through a documented migration process, where you can input all the relevant parameters using a prompt with various fields.

Important!
- The Workflow recognizes if the source database was loaded with partitioning (ILM) and automatically selects whether the migration process takes place with or without ILM. The data to be input depends on the database vendor you use and whether you are using ILM or not.
- If possible, make sure there is no ILM activity (no partition switch out operation) during the database migration process with ILM.
- Upon executing the AE database Migration Action Pack, it checks the encoding of the source (not UTF-8) and destination (UTF-8) AE databases. If one of the two checks is positive, the migration proceeds; otherwise, it aborts.
- You must execute the Workflow running each Run Mode at least once.

Define the SQL Agent for the source and destination database.
Define the Run Mode of the Workflow. Each run mode uses data from the various database areas with different requirements. Therefore, you must run each mode at least once.
- 0 - Initial (default)
- 1 - Refresh
- 2 - Final
For more information, see First Migration Part - System Online.
Enter the email address of the person or group that should receive the SQL queries required to complete the migration process during system downtime after the Workflow has finished.

Submit your entries.
Once done and depending on the defined Run Mode, you can do the following:
- Initial: Once the Workflow finishes successfully (it might run long), you can start Workflow in Refresh mode.
- Refresh: Once the Workflow finishes successfully, you can start, you can start the Workflow again in Refresh mode until you are ready to start the offline migration part, or start it again in Final mode.
- Final: Once the Workflow finishes and generates the required SQL queries, it sends an email to the defined address, provided that SMTP is correctly configured and works. The SQL statements are also stored in the PCK.AUTOMIC_UTF-8.EMAIL.CONTENT.FINAL.STEPS VARA object that is provided in the SOURCE - MOVE.NON-UTF8.DB.TO.UTF8 - EMAIL folder in case sending the email is not possible for any reason.
Important!
- If the email cannot be sent successfully in this final step, and the corresponding task fails with an error status, you can ignore this task and resume the Workflow.
- No rollback function is required because the Workflow does not change anything in your source database. It is copied to the destination database and a temp table (uc_migration) that controls the data for the migration process is created. If something goes wrong, you can easily delete the destination database and the temp table and restart the migration process. If you want to perform a test run of the Action Pack, you can use a copy of your existing AE database with actual life data.
Shut down your system.
Manually execute the SQL queries generated in the Final mode.

After executing the queries, the structure and data of the source AE database are completely copied to the destination AE database, which is a clone of the source AE database, but UTF-8 is configured, including the data that was converted to UTF-8. The source and destination databases have the same version.

Next Steps

Now that the database has been migrated, you have a UTF-8 AE database that has not been upgraded to v24 yet. You can now proceed with the system upgrade.

During the upgrade, make sure that the connection strings in all INI files are adapted so that they point to the new UTF-8 AE database and have the encoding set correctly to UTF-8. For more information, see Upgrading an Automation Engine System Manually.