How to migrate IAS data to a new SQL Server instance

Applies to:

IAS Version >= 2019.Summer.1

IAS Version <= 2019.Spring.1

Description:

This article describes how to install, configure, or migrate an older instance to the new SQL Server instance. Two different procedures are provided, depending on IAS release:

Procedures for IAS Version >= 2019.Summer.1

Installing the new SQL Server 2017 Express

The new SQL Server 2017 Express local instance can be automatically installed along with the IAS by selecting "Install Microsoft SQL server 2017 Express" option during the setup.

Upgrading SQL Server Express

The typical use case is the migration of a local SQL Server 2008 R2 Express database, included by default in IAS setup packages 2019.Spring.1 and older releases, to the new SQL Server 2017 Express.

WARNING

This upgrade is recommended in those cases where operating
RAM usage is below 85% of overall available memory. As an example,
if you are using AX/BX server classes, equipped with 4GB RAM, 
together with Windows Server 2016 or 2019 OS, the installation of SQL Server 2017 might exceed the amount of required RAM and consequently slow down Imagicle services.
In those cases, we suggest to increase VM's assigned RAM of 2GB (6 GB total), to guarantee the correct system performances.


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

  1. Imagicle backup & restore tool
  2. SQL database detach/attach

Both procedures imply having two local SQL instances on IAS server:

Option1: Imagicle Backup/Restore based migration

Standalone IAS node

  1. Perform a full IAS 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 IAS 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. IAS) 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 IAS 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 IAS. 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 IAS 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 IAS 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 IAS 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 IAS node" (steps 2, 3 and 4) to configure the IAS with the new SQL Server instance.
  5. Rebuild the cluster starting from the master node.

Option 2:  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 IAS 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) IAS database from old SQL Server instance as described here.
    Select "Drop Connections" and click "Ok"
  3. Move (Cut and Paste) the IAS 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 IAS 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 IAS 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 IAS database name; [<IAS_USER>] should be replaced with the username of new login user previously created.

    USE [<DB_NAME>]

    CREATE USER [<IAS_USER>] FOR LOGIN [<IAS_USER>]

    EXEC sp_addrolemember N'db_owner', [<IAS_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. IAS) 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 IAS. 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 IAS 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 IAS 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 IAS 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 IAS node" (steps 7 and 8) to configure the IAS with the new SQL Server instance.
  5. Rebuild the cluster starting from the master node.

Procedures for IAS Version <= 2019.Spring.1

Migration using SQL Server Management Studio

Before starting the DB migration, please make sure the destination SQL Server version is the same or higher than source SQL Server.

When procedure is completed restart Imagicle Application Server, after the restart the applications will start to use the database in the new location

* <IAS_Installation_Folder> by default is C:\Program Files (x86)\StonevoiceAS\
**<IAS_DB_Name> by default is 'BluesPro' and for new installations, starting from Spring '18 is 'IAS'

Migration using Backup & Restore Tool

Before starting the DB migration, please make sure the destination SQL Server version is the same or higher than source SQL Server.



Article ID: 688
Last updated: 20 Nov, 2020
Revision: 22
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/