How to Migrate a UMS Database From Embedded DB to Microsoft SQL Server

This article describes how to migrate the database of a Universal Management Suite (UMS) installation from an Embedded DB to a Microsoft SQL Server.

This is an exemplary representation. If you want to integrate the other way round or integrate other databases, the same steps are always performed. You can always use this description as a guide.

IGEL Demos Channel

Setting Up the SQL Database

The UMS supports only those standard sortings of Microsoft SQL Server which are case insensitive ("CI"). Therefore, make sure that the parameter Collation in MS SQL Server is set appropriately

Execute the following SQL script on the Microsoft SQL Server to create database, login, user, and schema. Replace the placeholders such as [databasename] with settings of your choice.

[sql-user] can be an SQL account or a Microsoft Active Directory (AD) account; for more information on the latter, see Connecting the UMS to an SQL Server via Active Directory. The script uses the same string for login, user, and schema in order to simplify UMS setup.

The user name for the external database may only be created with the following properties:

it consists only of lower case letters or upper case letters.
the low-cut character ("_") is the only special character, which is allowed.

Do not mix upper and lower case letters. Don't use points, spaces, minus, or @ sign!

CREATE DATABASE [databasename]
GO
USE [databasename]
GO
CREATE LOGIN [sql-user] with PASSWORD = '[password]',
DEFAULT_DATABASE=[databasename]
GO
CREATE USER [sql-user] with DEFAULT_SCHEMA = [sql-user]
GO
CREATE SCHEMA [sql-user] AUTHORIZATION [sql-user] GRANT CONTROL to [sql-user]
GO

Copying the Database Contents

Start IGEL Universal Management Suite Administrator.

Default path to the UMS Administrator:

Linux: /opt/IGEL/RemoteManager/RMAdmin.sh
Windows: C:\Program Files\IGEL\RemoteManager\rmadmin\RMAdmin.exe

The IGEL UMS Administrator application can only be started on the UMS Server.

Go to Datasource > Add... to create a new SQL Server data source; use exactly the same database name and settings you have defined while setting up the SQL Database (see Setting Up the SQL Database).
Select the Embedded DB entry and click Copy.
Select the newly created SQL Server entry as the target and click OK.
Enter the password and click OK to start the copying.
When the copying has completed, test the database connection by clicking Test and entering the password.
If the test was successful, select the SQL Server datasource and click Activate.
Enter the password to confirm the activation.

Now the Microsoft SQL Server is set up as the datasource. From now on, back up the SQL Server in order to back up UMS data.
The same way you can go back to the embedded database, if you need.