How to migrate IAS data to a new SQL Server instance

Applies to:

All UC cloud versions. 

Description:

This article describes how to install, configure, or migrate an older instance to the new SQL Server instance.

Starting version summer 2019,  the SQL server express of UC suite has been updated to version 2017. Hence this procedure is needed to migrate the Imagicle database from the old to the new SQL express server, but it also applies to any SQL version (be it local or external ) where the Imagicle DB resides.

IMPORTANT: Starting from Imagicle 2023.Spring.1 release, MS-SQL Server 2008/2008R2 is supported only for updating an existing installation to Spring 2023, but you can't deploy a new Imagicle instance with such old MS-SQL version. Starting from 2023.Summer.1 release, MS-SQL 2008/2008R2 is not supported anymore and therefore a MS-SQL update is mandatory.

Three different (alternative) options are described below, and are respectively based on:

  1. Microsoft Data Migration Assistant
  2. Imagicle backup & restore tool
  3. SQL database detach/attach

All of the above procedures imply having two local SQL instances on UC Suite server:

Option1: Imagicle Backup/Restore based migration

Please refer to the following article that explains in detail how to use "Microsoft Data Migration Assistant" for DB migration. 

Option2: Imagicle Backup/Restore based migration

Standalone UC Suite node

  1. Perform a full UC Suite backup by executing Imagicle Backup and Restore tool as administrator, as described here.
  2. If you want to enable new local authentication credentials, different than existing "sa", please create a new SQL login into new SQL Server 2017 Express instance, with local credentials to connect to UC Suite database, as described here.
  3. From Windows Start menu, please launch Imagicle AS Database Configuration tool as administrator and enter new SQL Server 2017 Express instance name (e.g. IMAGICLE2017), database name (e.g. UC Suite) and local authentication credentials, with permissions to allow DB creation.
  4. Hit "Next" to proceed with the creation of the new DB. If entered credentials are wrong or lack needed permissions, database creation fails. In this case, please go back to SQL login creation. Please note that even when using the "sa" user the password for the new instance could differ from the old one.
    (SvB1llyBlues$ is the default password)

  5. Perform now a restore by executing again Imagicle Backup and Restore tool as administrator.
    Warning: Please check the disk space before proceeding. (this procedure will be a copy of your previous database)
  6. From now on, your UC Suite starts using new SQL Server 2017 Express instance, where all DB data have been properly restored from the original SQL database.
  7. The original SQL Server Express instance is still there, even though it is no longer used by UC Suite. You can manually remove it from Imagicle server after having verified that all data have been properly moved to the new SQL Server 2017 Express instance and no additional databases are hosted on the original instance. Please note that while the original SQL Server Express instance could be deleted, all the other features including Management Objects, Native Client and Setup must not be deleted, in order not to compromise the proper UC Suite functioning. In the following example, where the original SQL Server Express instance was version 2008 R2, the highlighted feature should be the only one to be deleted.

Redundant UC Suite cluster

If you are running an Imagicle redundant HA cluster, please follow these steps:

  1. Stop all Imagicle services (including the replication services) on all nodes. You can use the <installation_folder>\Temp\Sv-StopAllServices.bat batch file.
    Please, notice that this implies a downtime for all the Imagicle services. 
  2. On each node, run the Node Removal Tool to remove the local node from the cluster as described here.
  3. Perform all the steps of the above "Standalone UC Suite node" section on the master node.
  4. On each other node in your Imagicle HA cluster use the Imagicle AS Database Configuration tool as described above in section "Standalone UC Suite node" (steps 2, 3 and 4) to configure the UC Suite with the new SQL Server instance.
  5. Check in this folder \StonevoiceAS\Var if a "MyDbBackup_{UC Suite_SHORT_VERSION}.zip" file exist, remove it if present
  6. Rebuild the cluster starting from the master node.

Option 3:  Database Detach migration

Database migration through detach and subsequent attach procedure requires a number of manual tasks to be applied on both original and recipient SQL Server instances. This is the suggested procedure for Imagicle HA clusters and if you have a large amount of billing data in your existing DB. Please carefully read current limitations described here.

Moreover, notice that this procedure requires a temporary stop of all Imagicle services (on all nodes in the cluster).

Standalone UC Suite node

This procedure applies to a stand-alone server, see the next paragraph if you need to handle an Imagicle AppSuite cluster.

  1. Stop all Imagicle services
    You can use the <installation_folder>\StonevoiceAS\Temp\Sv-StopAllServices.bat batch file.
  2. Disconnect (detach) UC Suite database from old SQL Server instance as described here.
    Select "Drop Connections" and click "Ok"
  3. Move (Cut and Paste) the UC Suite database .mdf and .ldf files from this folder 
    Warning: If you decide to copy and paste these files please check the disk space before proceeding. (this procedure will be a copy of your previous database)

    Imagicle default path here 

    <Installation path>\StonevoiceAS\SqlServerData\MSSQL10_50.IMAGICLE\MSSQL\DATA

    to a new SQL 2017 DB folder

    <Installation path>\StonevoiceAS\SqlServerData\\MSSQL14.IMAGICLE2017\MSSQL\DATA

  4. Connect (attach) the UC Suite database to the new SQL Server 2017 Express instance as described here.
  5. If you want to enable new local authentication credentials, different than existing "sa", please create a new SQL login into new SQL Server 2017 Express instance, with local credentials to connect to UC Suite database, as described here.
  6. If a new SQL login has been added as described in the previous step, please run the following SQL script to create a DB user with "owner" permissions on database itself.  [<DB_NAME>] should be replaced with actual UC Suite database name; [<UC Suite_USER>] should be replaced with the username of new login user previously created.

    USE [<DB_NAME>]

    CREATE USER [<UC Suite_USER>] FOR LOGIN [<UC Suite_USER>]

    EXEC sp_addrolemember N'db_owner', [<UC Suite_USER>]

  7. From Windows Start menu, please launch Imagicle AS Database Configuration tool as administrator and enter new SQL Server 2017 Express instance name (e.g. IMAGICLE2017), database name (e.g. UC Suite) and local authentication credentials, with the required DB permissions.
  8. Hit "Next" to proceed with the creation of the new DB. If entered credentials are wrong or lack needed permissions, database creation fails. In this case, please go back to SQL login creation. Please note that even when using the "sa" user the password for the new instance could differ from the old one.
    (SvB1llyBlues$ is the default password)

  9. If the task is successfully accomplished, the database configuration wizard will automatically restart all the Imagicle services.
  10. The original SQL Server Express instance is still there, even though it is no longer used by UC Suite. You can manually remove it from Imagicle server after having verified that all data have been properly moved to the new SQL Server 2017 Express instance and no additional databases are hosted on the original instance. Please note that while the original SQL Server Express instance could be deleted, all the other features including Management Objects, Native Client and Setup must not be deleted, in order not to compromise the proper UC Suite functioning. In the following example, where the original SQL Server Express instance was version 2008 R2, the highlighted feature should be the only one to be deleted.

Redundant UC Suite cluster

If you are running an Imagicle redundant HA cluster, please follow these steps:

  1. Stop all Imagicle services (including the replication services) on all nodes. You can use the <installation_folder>\Temp\Sv-StopAllServices.bat batch file.
    Please, notice that this implies a downtime for all the Imagicle services. 
  2. On each node, run the Node Removal Tool to remove the local node from the cluster as described here.
  3. On the master node only, perform all the steps of the above "Standalone UC Suite node" section above.
  4. On each other secondary node of your Imagicle HA cluster use the Imagicle AS Database Configuration tool as described above in section "Standalone UC Suite node" (steps 7 and 8) to configure the UC Suite with the new SQL Server instance.
  5. Rebuild the cluster starting from the master node.



Article ID: 688
Last updated: 31 Mar, 2023
Revision: 26
Imagicle AppSuite Cross Platform -> FAQ and Solutions -> How to migrate IAS data to a new SQL Server instance
https://kbp.imagicle.com/kb/entry/688/