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 working MS SQL Server instance, this step is not required. Follow these steps to configure a newly installed MS SQL Server instance:

  1. Open Services:
     • Click Start → Run and type services.msc.
     • If the SQL Server Browser service is not running, start it to make the port number visible in the instance list.
     
  2. Configure TCP/IP:
     • Open SQL Server Configuration Manager.
     • Go to SQL Server Network Configuration → Protocols for [Instance Name].
     • Ensure that TCP/IP is enabled. Right-click and enable it if not.
     
  3. Restart SQL Services:
     • After enabling TCP/IP, go to SQL Server Services, right-click the instance, and select Restart.
     

  Note: Ensure that Windows Firewall is configured to allow SQL Server access. Refer to this document for more details.

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

  Providing Permissions to the Microsoft SQL Instance

  Follow these steps to grant db_owner privileges to the user:

  Before Migration:

  1. Right-click the user and select Properties.
  2. Go to User Mapping.
  3. Select db_owner (master db) and click OK.
  4. Go to Securables and enable Create any database.

  

  After Migration:

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

  
• For databases using the Full or Bulk-Logged recovery model, back up the transaction logs regularly to avoid data loss and prevent the transaction log from filling up. Learn more.
• The Central Server supports MSSQL 2008 or later versions.
• Use the Central Server’s backup method to ensure an uninterrupted workflow.
• This migration is applicable only for Central Server build 10 or above. Upgrade your server before proceeding.
• If you are using a Failover server, additional steps are required.

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 without migrating the database:

1. Stop the Central Server.
2. Open the database_params.conf file located at <UEMS_CentralServer>/conf.
3. Replace the old SQL Server IP/hostname with the new one:
   • url=jdbc:sqlserver://Old_Server_Name:SQLPort;DatabaseName=desktopcentral
   • url=jdbc:sqlserver://New_Server_Name:SQLPort;DatabaseName=desktopcentral
4. Save the file and start the Central Server.

3. Migrate the Central Server Database from PGSQL to MSSQL

This section explains how to migrate the Central Server database from PostgreSQL (PGSQL) to Microsoft SQL Server (MSSQL). Follow these steps carefully to ensure a smooth migration:

1. Stop the Central Server: Shut down the Central Server service.
2. Run the Migration Script:
   • Navigate to <UEMS_CentralServer>/bin and run the changeDBServer.bat script. This launches the Database Setup Wizard.
   • Example: C:\Program Files\UEMS_CentralServer\bin\changeDBServer.bat
3. Select SQL Server:
   • Choose SQL Server as the database type.
   • Enter the hostname where SQL Server is installed.
   • Select the appropriate instance from the list.
4. Authentication:
   • Select either Windows Authentication or SQL Server Authentication and provide the necessary credentials.

5. Enable Data Migration:
   • Ensure the "Migrate Data from Existing Database" checkbox is selected to migrate data from your current database to the new MSSQL database.
6. Test Connection:
   • Click the Test button to verify the connection. A pop-up message will confirm the connection is established.
   
7. Complete Configuration:
   • Click Save to complete the SQL Server configuration.
   • A confirmation message will indicate successful SQL Server setup and data migration.
   
8. Note: Migration duration depends on the amount of data and machine performance. Please wait until completion.
9. Additional Steps for Failover Server:
   • Stop both the Primary and Secondary Servers.
   • Copy the database_params.conf and Server.starttime files from the conf folder.
   • Move them to the corresponding location on the Secondary (failover) server.
   • Start the Primary Server first, followed by the Secondary Server.

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

To configure SQL Server Always On functionality, refer to: SQL Server Always On

Frequently Asked Questions (FAQ)

How to find the Port Number?

Open SQL Server Configuration Manager on the SQL Server machine. Navigate to SQL Server Network Configuration → Protocols for MSSQLServer → Right-click TCP/IP → Properties → IP Addresses → IPALL → TCP Port.

What should you do if the database migration fails midway?

If migration fails halfway, re-running the process will show a pop-up: “Database already exists.” To fix this, delete the partially created database in MSSQL and restart the migration.

What if the migration is stuck at 99%?

Migration may get stuck at 99% due to tables with large datasets. Data is migrated in batches, and though one batch may be done, others may still be in progress. Please wait.

What happens if "Enable Data Migration" is not selected?

If you're setting up a fresh instance, you can skip data migration by unchecking the option. Also uncheck it while updating SQL login credentials.

What to do if you see "Cannot Establish a Connection"?

This could be due to connection refusal, incorrect hostname, invalid port range, FIPS, SSL issues, or access denial. A pop-up alert will help troubleshoot the issue.

Can I migrate the database while the product is running?

No. Migration while the product is running can cause data inconsistencies, loss, and connection issues due to DB locks. Always stop the product before migrating.