How to Configure MS SQL Server Database for the Central Server

1. Prerequisites for the migration process

In the Microsoft SQL Server used for migration, make sure that the following conditions are satisfied:

• Hardware Requirements for the MSSQL Server

  Hardware Requirements for SQL server

  The hardware requirements for SQL server include the following:

  No. of endpoints to be Managed	Processor Details	RAM Size	Hard Disk Space
  1 to 250	Intel Core i3 (2 core/4 thread) 2.0 Ghz 3 MB cache	4 GB	5 GB*
  251 to 500	Intel Core i3 (2 core/4 thread) 2.4 Ghz 3 MB cache	4 GB	10 GB*
  501 to 1000	Intel Core i3 (2 core/4 thread) 2.9 Ghz 3 MB cache	4 GB	20 GB*
  1001 to 3000	Intel Core i5 (4 core/8 thread) 2.3 GHz	8 GB	30 GB*
  3001 to 5000	Intel Core i7 (6 core/12 thread) 3.2 GHz. 12 MB cache	8 GB	200 GB*
  5001 to 10000	Intel Xeon E5 (8 core/16 thread) 2.6 GHz. 20 MB cache	16 GB	250 GB*
  10001 to 15000	Intel Xeon E5 (12 core/24 thread) 2.7 GHz. 30 MB cache	32 GB	500 GB*
  15001 to 20000	Intel Xeon E5 (14 core/28 thread) 2.7 GHz. 30 MB cache	64 GB	500 GB*
  20001 to 25000	Intel Xeon (14 core/28 thread) 2.7 GHz. 30 MB cache	64 GB	1 TB*
  25001 to 30000	Intel Xeon (20 core/40 thread) 3.6 GHz. 50 MB cache	128 GB	1 TB*

  * May increase dynamically according to the frequency of scanning.

• The SQL Server browser must be up and running

  Configuring the MSSQL Server

  If you already have a functional MS SQL Server instance, then this step is not required. Follow the steps below to configure a freshly installed MS SQL Server instance:

  1. Open Services:
     • Click Start -> Run and type services.msc.
     • If the SQL Server Browser service is already running, leave it as is. If not, start the service to make the port number visible in the SQL Server instances list.
     
  2. Configure TCP/IP:
     • Open SQL Server Configuration Manager from the Start menu.
     • Expand SQL Server Network Configuration and navigate to Protocols for [Instance Name].
     • Ensure that TCP/IP is enabled for your desired instance. If it’s not, right-click TCP/IP and enable it.
     
  3. Restart SQL Services:
     • After enabling TCP/IP, select SQL Server Services from the left pane, right-click on the SQL instance, and choose Restart.
     

  Note: Ensure that Windows Firewall is configured to allow access to the SQL server. Refer to this document for more information.

• Microsoft SQL Server access is delegated to a user with db_creator and db_owner permissions at the database level

  Providing Permissions to the Microsoft SQL Instance

  You need to follow these steps to enable db_owner privileges for the user:

  Before Migration Permission:

  1. Right-click on the user and select Properties.
  2. Navigate to User Mapping.
  3. Select db_owner (master db) in the checkbox and click OK.
  4. Navigate to Securables, and check Create any database in the checkbox.
  5. Click OK.

  

  After Migration Permission:

  1. Right-click on the user and select Properties.
  2. Navigate to User Mapping.
  3. Select db_owner (Desktop Central db) in the checkbox and click OK.

  
• If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data, and to prevent the transaction log from filling. To know more, click here.
• The Central Server supports MSSQL 2008 or later versions.
• We recommend using the Central Server's backup method to ensure an uninterrupted workflow.
• This migration is applicable only for Central Server build 10 or above. Kindly upgrade your server before following these steps.
• If you are using a Failover server, you need to follow some additional steps as well.

2. Change SQL Server Machine Name/IP in the Central Server (Without Migration)

To update the machine name or IP address in the Central Server server without performing a database migration, follow these steps:

1. Stop the Central Server server.
2. Open the database_params.conf file located in <UEMS_CentralServer>/conf.
3. Update the old SQL server IP/hostname to the new SQL Server as follows:
   • url=jdbc:sqlserver://Old_Server_Name:SQLPort;DatabaseName=desktopcentral
   • url=jdbc:sqlserver://New_Server_Name:SQLPort;DatabaseName=desktopcentral
4. Save the file and then start the Central Server server.

3. Migrate the Central Server Database PGSQL to MSSQL Database

In this guide, we will explain how to migrate the Central Server server database from PostgreSQL (PGSQL) to Microsoft SQL Server (MSSQL). Follow these steps carefully to ensure a smooth migration.

1. Stopping the Central Server Server: Shutdown the Central Server service.
2. Run the Migration Script:
   • Go to the <UEMS_CentralServer>/bin directory and execute the script changeDBServer.bat. This will launch the Database Setup Wizard.
   • Example: C:\Program Files\UEMS_CentralServer\bin\changeDBServer.bat
3. Select SQL Server:
   • Select the Database type as SQL Server.
   • Enter the Host Name where SQL Server is installed.
   • From the list of SQL Server instances, select the one on which you want to run the database.
4. Authentication:
   • Choose the authentication method: Either Windows Authentication or SQL Server Authentication and provide the required credentials.

1. Enable Data Migration:
   • Ensure the "Migrate Data from Existing Database" checkbox is selected to migrate your data from current database to the new MSSQL database.
2. Test Connection:
   • Click the Test button to verify the connection. You should see a pop-up message indicating Connection Established.
   
3. Complete Configuration:
   • Click Save to complete the SQL Server configuration.
   • Upon saving, you will receive a pop-up confirmation message indicating that the SQL Server has been successfully configured.This shows that the migration has been completed successfully.
   
4. Please note that it will take a few minutes/hours to migrate the data, depending on the amount of data in the database and the performance of the machine/server. Kindly wait until the migration is complete.
5. Additional steps for the Failover server:
   • Stop both the Primary Server and the Secondary server.
   • Copy the"database_params.conf" & "Server.starttime" files from "conf" folder.
   • Move the copied files from the primary server's folder to the corresponding folder on the secondary (failover) server.
   • Start the Primary Server first and then the Secondary server.

Once the migration is completed, you can begin using the Central Server with the new configuration.

To configure the Always-on functionality of the SQL server, refer here - SQL Server Always On

Frequently Asked Questions (FAQ)

How to find Port Number?

To find the TCP port number, open SQL Server Configuration Manager on the computer where the Microsoft SQL instance (to which the database has to be moved) resides. Go to SQL Server Network Configuration > Protocols for MSSQLServer > Right-click TCP/IP > Properties > IP Addresses > IPALL > TCP Port Number.

What should you do if the database migration fails abruptly while migrating to MSSQL?

If the database migration fails midway, attempting to restart the migration without changes will show a Pop-up bar stating the "Database already exists". To fix this, access MSSQL, delete the partially created database, and restart the migration. This ensures a clean and successful process.

What should you do if the database migration gets stuck midway or at 99% completion?

Note : DB Migration will be struck at 99% due to either one of the following reasons: When data migration is done for tables with huge data. DB Migration will usually happen in batches. Even when the current batch migration is successful, other batches may still be in progress.

What will happen if the "Enable Data Migration" checkbox is not selected?

If it is a fresh setup, you can skip the data migration by unchecking the Enable Data Migration And uncheck while updating the SQL login credentials

What to do if it shows "Cannot Established a Connection "?

Some of the known cases like "connection refused,wrong hostname,invalid port range , FIPS , SSL , Access Denied Issue" are handled and popup alert is shown to troubleshoot the issue.

Is it possible to migrate the database from Postgres to MSSQL while the product is running?

No, It is won't allow since it creates "Data inconsistencies, Data Loss, Connection issues due to DB locks, etc."