Knowlege base

How to configure SQL Server 2017 Express local DB

Article ID: 688
Last updated: 12 Aug, 2019

Applies to:

IAS Version >= 2019.Summer.1

  • New IAS installations that will use the locally installed SQL Server 2017 Express
  • IAS Database migration from an existing SQL Server Express local instance version >= 2008 and < 2017 to a newer SQL Server 2017 Express local instance

Description:

This article describes how to install, configure, or upgrade an older instance to the new SQL Server 2017 Express local instance shipped along with IAS installation package starting from 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 (2019.Summer.1 and above).

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.

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 1GB (5GB total), to guarantee the correct system performances.

Two mutually exclusive procedures are described below, and are respectively based on:

  • Imagicle backup & restore tool
  • SQL database detach/attach

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

  • Existing SQL Server Express instance (version >= 2008 and < 2017) including current IAS configuration database
  • A new target, blank SQL Server 2017 Express instance

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.

  5. Perform now a restore by executing again Imagicle Backup and Restore tool as administrator.
  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

  1. If you are running an Imagicle redundant HA cluster, please stop data replication on all nodes by accessing web page Admin ⇒ High Availability ⇒ Stop replication service.
  2. Perform all the steps of the above "Standalone IAS node" section on the master node.
  3. On each other node in your Imagicle HA cluster:
    1. Remove the node from the cluster by executing the Node Removal Tool as described here
    2. 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.
  4. Rebuild the cluster starting from the master node.

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.

Standalone IAS node

  1. Stop all Imagicle services from services.msc Control Panel.
  2. Disconnect (detach) IAS database from old SQL Server instance as described here.
  3. Move the IAS database .mdf and .ldf files from this folder

    <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.

  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

  1. If you are running an Imagicle redundant HA cluster, please stop data replication on all nodes by accessing web page Admin ⇒ High Availability ⇒ Stop replication service.
  2. Perform all the steps of the above "Standalone IAS node" section on the master node.
  3. 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 5, 7 and 8) to configure the IAS with the a new database in the new SQL Server instance.
  4. Restart data replication on all nodes.

Article ID: 688
Last updated: 12 Aug, 2019
Revision: 5
Views: 55
Print Export to PDF Subscribe Share
This article was:  
Prev   Next
Unexpected error. Unable to install role: Web server (IIS) and...     How to force HTTPS usage for Application Suite web access and...