State of knowledge

October 2020

Version note

Microsoft Access is no longer used as a default database by current ProCall versions, but only in upgrade scenarios.
As of ProCall 8 Enterprise, Microsoft Access is no longer used as the journal database.

Expertise required

The conversion of the database for the UCServer is an intervention in the overall system and should only be carried out with the necessary expertise.

This article explains how to migrate your UCServer database to Microsoft SQL server, as well as optionally migrate your existing Journal dataset.

Procedure

Installation Microsoft SQL server

  1. Basically, in order to convert the UCServer database to SQL, you need a working SQL server or SQL Server Express instance.
  2. For security reasons, we recommend that you always install the latest Microsoft server service pack.
  3. When planning, please note the ProCall 7 Enterprise system requirements and the Editions and supported features of SQL servers.
    Please also refer to the Limitations when using Microsoft SQL Server Express
  4. The installation is performed using the Microsoft Documentation.

Conversion of the UCServer database to "Microsoft SQL server"

You can change the UCServer database in the UCServer administration under General → Database.

Example screenshot

Configuration

The following configuration is required to use the SQL Server:

  • Server name
    Enter the name or IP address of the Microsoft SQL/SQL Server and instance here. If the Server Browser is active on the SQL server, then the appropriate entry can be selected via the drop-down menu.
  • Databank name
    Enter here the name of the database that the UCServer should create or use.
  • Authentication
    Microsoft SQL Server supports two authentication modes: Windows authentication or mixed-mode (i.e. Windows and SQL Server authentication). We recommend using Mixed Mode so that SQL authentication can be used independently of Windows password changes. Please note the following details for the authentication methods:
    • Windows authentication with the service account
      The estos UCServer implicitly logs on to the SQL Server with the Windows user account under which the estos UCServer service is started. The specification of the user name with a password is not required in this case. For this authentication method, make sure that the service account under which the estos UCServer instance is running has the necessary access rights on the SQL Server. By default, the estos UCServer installation enters the local system account as the service account, which normally already has full access to a local SQL Server instance. When using the local system account, estos UCServer, as well as SQL Server, must be running on the same computer. When using Windows authentication with the service account and a SQL Server accessed via a network, the estos UCServer service must be switched from the local system account to a Windows account with network permissions. The service account can be changed by opening the services in the "Administration" group in the Control Panel and selecting the desired Windows user account there in the properties of the estos UCServer entry.
    • Windows authentication
      By selecting Windows authentication, you must explicitly specify a Windows user account with a password with which the estos UCServer service is to log on to the SQL Server. For this authentication method, make sure that the specified Windows user account has the necessary access rights on the SQL server.
    • SQL Server® authentication
      Select SQL Server authentication if you want to use the user accounts created in the SQL Server to log on to the database server. For this purpose, the so-called "Mixed Mode" must be activated on the SQL Server. For this authentication method, make sure that the specified SQL Server user account has the necessary access rights on the SQL Server.

By clicking on "Apply", the UCServer creates the database and the required tables in the SQL Server instance on its own.

Authentication and access rights

In addition, to read and write, the UCServer requires the following rights to access the database:

  • Create a database
    When the server starts the configuration for the first time, it tries to recreate the database if it does not already exist.
    If the database already exists, the right is not needed.
  • Create tables
    When the server starts with the configuration for the first time, the required tables are created in the specified database if they do not already exist.
    If the tables already exist, the right is not needed.
  • Change tables
    It may happen that the table structure has to be changed or supplemented due to the introduction of new features or improvements.
    The server examines the table structure at startup and will make extensions or changes automatically.
    These rights are not required if the corresponding work or changes are performed manually.

Necessary services and protocols

To ensure the correct functioning of ProCall in connection with Microsoft SQL Server, you need the following active services or protocols:

  • Active TCP protocol in Microsoft SQL Server
    The TCP protocol must be active. You make these settings in the SQL Server "Configurations Manager". This can be found under Start > MSSQL Server 20xx > Configurations Tools. Navigate to "SQL Server Network Configuration" > "Protocols for SQL Express" and enable the TCP/IP protocol. Please enable the TCP protocol as well under "SQL Native Client Configuration" > "Client Protocols".
  • SQL Server Browser Service
    Make sure that the SQL Server Browser service is started. You can do this either via the SQL Server "Configuration Manager" > SQL Server Services or the Services Manager. It is best to set this service to "Automatic" so that it is started every time the computer is restarted. If the service is "Disabled", set it to "Enabled".
  • Firewall
    Refer to the Microsoft documentation on configuring a firewall for SQL Server.

[Optional] Migration of the database from SQLite UCServer database to SQL Server

Best Practice: Datenmigration der UCServer Datenbank SQLite <->Microsoft SQL Server

[Optional] Migration of the data stock from the internal UCServer database to SQL Server 

If you want to migrate your existing Microsoft Access dataset to SQL, you can do this by performing an SQL import. You can find a short guide to help you here:

Migration of the internal UCServer database from Microsoft Access to SQL Server