One moment please...
 
 
Exact Synergy Enterprise   
 

How-to: Migrating Exact Synergy Enterprise database to Windows SQL Azure

Introduction

Note: This document is only relevant to the controlled release participants.

This document explains the steps to migrate the Exact Synergy Enterprise database to Microsoft SQL Azure.

Prerequisites

You must have the following prerequisites:

  • An existing Exact Synergy Enterprise environment with the database running on the Microsoft SQL Server. Ensure you have the following:
    • The corresponding Exact Synergy Enterprise license for the database.
    • The Exact Synergy Enterprise administrator user to log in to the environment.
    • SQL administrator rights to access the SQL Server.
  • Exact SQL Azure Tool. From product update 505, the tool (Exact.SQLAzure.Tool.exe) is located in the bin subfolder of Exact Synergy Enterprise installation path.
  • Access to the SQL Azure server. Ensure you have the following:
    • The administrator account. For example, the SQL Azure administrator user name and password.
  • Access to the Exact Synergy Enterprise website on the cloud environment. Ensure you have the following:
    • FTP or portal access if Exact Synergy Enterprise will be running on the Azure website, or
    • Remote desktop access if Exact Synergy Enterprise will be running on the Azure virtual machine.

Note:

  • It is recommended that you back up the Exact Synergy Enterprise SQL server database in case any of the following steps encounters errors.
  • This document assumes that the Exact Synergy Enterprise database migration to SQL Azure is for the use of Exact Synergy Enterprise hosted in Azure using the federated identity authentication.
  • During the migration of the database, the appropriate SQL Server Management Studio (SSMS) version must be used. For example, if you are migrating the SQL server 2016 database, SSMS 2016 or higher must be used; likewise if you are migrating the SQL server 2017 database, SSMS 17.x or higher must be used.
Overview of process

Migrating Exact Enterprise Synergy database to Microsoft SQL Azure involves the following steps, which will be explained in detail later in this document:

  1. Setting up the database
    In order for Exact Synergy Enterprise to use a database hosted on SQL Azure, the database connection mode of SQL login is required. Use the function in Exact Synergy Enterprise to set this mode before beginning the migration. 
  2. Preparing the database for deployment
    Certain features used in the Microsoft SQL Server database must be prepared for migration to SQL Azure servers, otherwise deployment may encounter errors. Use the Exact SQL Azure Tool to apply this preparation to the database via “Step 1” of the tool. 
  3. Deploying the database to SQL Azure
    After the database is prepared using the tool, the database can be copied (i.e. deployed) to Microsoft SQL Azure. Use the Deploy function in Microsoft’s SQL Server Management Studio to place the database into the Azure environment. 
  4. Using the SQL Azure Tool to complete the database deployment
    Once the database is deployed to an SQL Azure server, the Exact SQL Azure Tool must be used again to complete the migration. The “Step 3” function of this tool completes the conversion that was prepared in “Step 2”. 
  5. Setting up Exact Synergy Enterprise to use the database
    At this point, the database on SQL Azure server can now be opened and used for your Exact Synergy Enterprise application. 

Setting up the database

From product update 257 onwards, Exact Synergy Enterprise provides a function to change the database connection mode from application role to SQL login and vice versa. This is required because SQL Azure does not support application roles.

  1. Log in to the existing Exact Synergy Enterprise environment with the Exact Synergy Enterprise administrator user.
    Note: Run the browser with the administrator rights by right-clicking the mouse, and then click Run as administrator.
  2. Create or edit the person with the federated identity (WAAD or Auth0 email address). This is the account that you will be using for the first time to access the cloud-hosted Exact Synergy Enterprise.
    Note: The existing Exact Synergy Enterprise must have a person with a WAAD or an Auth0 email address.
  3. Go to Modules ? System ? Setup ? Settings – Database ? Connection setting.
        
  4. On the Connection Setting page, select SQL Login at Connection Mode.
  5. Define the administrative SQL Server login credentials under the Login section. Select the Integrated security check box or type the user name and password.
        
  6. Click Save.

If successful, the SQL Login page will be displayed if you go to Modules ? System ? Setup ? Settings – Database ? Connection setting. The page will display the Update database password field. Once the Exact Synergy Enterprise connection database mode is set to "SQL Login", a random SQL login and password will be created for the connection. The credentials will then be encrypted and saved in the db.config file.

In the SQL login connection mode, the administrator can change the SQL password via the Update database password field. This is an optional step. For more information, see Changing database passwords. 

The db.config entry for Exact Synergy Enterprise will be changed to use the SQL login.

If you encounter error(s) in this section, see the Known issues - Setting up the database section.

Using the SQL Azure tool 

From product update 267 onwards, Exact Synergy Enterprise databases use enhanced security features that are specific to Microsoft SQL Server or SQL Azure engine. This means that when the database is migrating to Microsoft SQL Azure, the security implementation embedded in the database must also be migrated.

This is done using the Exact SQL Azure Tool located in the bin subfolder of Exact Synergy Enterprise installation path.

Once the Exact Synergy Enterprise database is using the SQL login database connection mode, you can use the tool to prepare for deployment.

Note: You are recommended to have enough disk space on your SQL Server.

  1. Launch the Exact SQL Azure Tool with administrator rights.
    Note: Run the Exact.SQLAzure.Tool.exe file with administrator rights by right-clicking the mouse, and then click Run as administrator.
  2. In Step 1 of the tool, do the following:
    • Enter the SQL Server details of the Exact Synergy Enterprise database.
    • Specify the SQL administrator credential 
    • Specify if the tool should prepare the current database or a copy of the current database for deployment. 
    • Click Start to prepare the database for deployment.
      Note: By default, the recommended option for preparing a copy of the database for deployment is selected. However, this requires enough hard disk space for the tool to duplicate the database files. If there is insufficient disk space, please uncheck the Copy option.
  3. Note: If you have already prepared a database and are re-launching the tool, you can click Skip to proceed to Step 2 or Step 3 directly. A database should be prepared for deployment once. The Skip option is available in the event when you have closed the tool at any point in migration process and need to re-launch it to continue the process.
  4. In Step 2 of the tool, the name database that should be deployed is displayed.
    • If the option to copy the original database was selected, the new database name is _sqlazure. If the option was not selected, the database shown is the original database. 
    • Follow the next steps in this document to deploy the database to SQL Azure. 

Deploying the database to SQL Azure

Once the Exact Synergy Enterprise database is prepared with the SQL Azure tool, you can start to deploy the database to SQL Azure.

  1. In the SQL Management Studio screen, right-click on Database, click Tasks, and then click Deploy Database to Microsoft Azure SQL Database.
         
  2. In the Introduction screen, click Next.
         
  3. In the Deployment Settings screen, do the following:
    • Click Connect.
    • In the Connect to Server screen, define your SQL Azure details, and then click Connect.
               
    • In the Deployment Settings screen, define the database name at New database name.
               
    • The other recommended settings are:
      • Select Standard at Edition of Microsoft Azure SQL Database.
      • Select 250 at Maximum database size (GB).
      • Select S2 at Service Objective.
      • Click Next.
    • In the last screen, click Finish.

If you encounter error(s) in this section, see the Known issues - Setting up the database section.

Using the SQL Azure Tool to complete the database deployment 

After deploying the database to SQL Azure using SQL Management Studio, you should use the Exact SQL Azure Tool to secure the database and enable it for Exact Synergy Enterprise use.

  1. If not still open, launch the Exact SQL Azure Tool with administrator rights. 
    • Click Skip in Step 1
    • Click Next in Step 2 
  2. In Step 3 of the tool, do the following: 
    • Enter the SQL Azure details of the Exact Synergy Enterprise database. 
    • Specify the SQL Azure administrator credential 
    • Click Reactivate 



Setting up Exact Synergy Enterprise to use the database on SQL Azure

This section assumes that Exact Synergy Enterprise is hosted on the Azure website or on the Azure virtual machine.

  1. Access the web.config file for the cloud-hosted Exact Synergy Enterprise and ensure that HostedEnvironment under <appSettings> is set to “2”.
        
  2. Ensure that the db.config is empty (or cleared for the virtual directory).
  3. In SQL Management Studio, connect to the SQL Azure database.
  4. Ensure there is a federated identity user (WAAD or Auth0) in the Humres table.
  5. Open Exact Synergy Enterprise in a browser.
  6. Log in with the federated identity that you have created under the Setting up the database section.
  7. In the Database: Create screen, select the Exact Synergy Enterprise license, and click Continue.
        
  8. Select Open an existing database.
  9. In the Database: Open screen, do the following:
    • Select SQL Azure at DBMS.
    • Type the SQL Azure server name at Server.
    • Type the SQL Azure database name at Database.
    • Type the SQL Azure administrator user name at Login name and password at Password.
  10. Click Open.

You should be able to access Exact Synergy Enterprise with the database hosted on SQL Azure.

Known issues

Setting up the database

Error:

Invalid: Database - Failed to connect to server : Error in SQL statement 'DROP SCHEMA Baco' : Cannot drop schema 'Baco' because it is being referenced by object 'DF__Integrati__Clien__1D5CFB42'.

Explanation:

This may occur when you try to change from AppRole to SQL Login at System à Setup à Settings – Database à Connection settings for older databases that had Exact Lightweight Integration Server (ELIS) Add On activated, creating the IntegrationClient table under Baco schema instead of dbo.

Solution:

Change the schema owner of IntegrationClient from Baco to dbo using the following statement:

ALTER SCHEMA dbo TRANSFER Baco.IntegrationServers

Deploying the database to SQL Azure

Error:

Error SQL71626: The element Symmetric Key: [KEY1] is not supported in Microsoft Azure SQL Database v12.

Error SQL71626: The element Symmetric Key: [KEY2] is not supported in Microsoft Azure SQL Database v12.

Error SQL71626: The element Symmetric Key: [KEY3] is not supported in Microsoft Azure SQL Database v12.

Error SQL71626: The element Symmetric Key: [KEY4] is not supported in Microsoft Azure SQL Database v12.

Error SQL71626: The element Symmetric Key: [KEY5] is not supported in Microsoft Azure SQL Database v12.

Error SQL71626: The element Symmetric Key: [KEY6] is not supported in Microsoft Azure SQL Database v12.

Explanation:

The database has not been prepared for deployment using the Exact SQL Azure tool.

Solution:

Use the Exact SQL Azure tool and apply Step 1 to the database. Next, try deploying the database using SQL Management Studio again.

 

Error:

Error SQL71564: Error validating element [dbo].[xxxxx]: The element [dbo].[xxxxx] cannot be deployed as the script body is encrypted.

Explanation:

SQL Azure does not support encrypted scripts. These scripts should be re-created without the encryption before trying to migrate.

Solution:

Drop the customized function or procedure.

 

Error:

Could not import package.

Warning SQL0: A project which specifies SQL Server 2014 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure T_D_DATA_SOURCE_MASTER, Line 97 Incorrect syntax near '@error_number'.

Error SQL72045: Script execution error.  The executed script: ….

Explanation:

SQL Azure does not support RAISERROR. Scripts that use this command must be removed or reworked.

Solution:

DROP function, trigger, or procedure, or edit and COMMENT the RAISERROR line.

Note: The known issues occur only when you migrate the non-standard Exact Synergy Enterprise databases. The solutions are only suggestions of the workarounds, and should be evaluated based on your needs.

Related documents

     
 Main Category: Attachments & notes  Document Type: Online help main
 Category:  Security  level: All - 0
 Sub category:  Document ID: 27.549.510
 Assortment: Exact Synergy Enterprise  Date: 02-05-2025
 Release:  Attachment:
 Disclaimer

Tags
No tags added
Attachments
ESE-OH-Migrating ESE DB to SQL Azure for Private Cloud_VerSQLAzureTool.docx 428.2 KB View Download