SQL Server prerequisites

When using an internal SQL Server instance, XProtect Management Server Failover replicates the contents of the SQL Server databases and triggers failover if the SQL Server instance fails.

For large VMS installations, you can connect the management server to an external SQL Server instance.

Internal SQL Server instance

The SQL Server installations must be identical on both nodes. To see a list of supported SQL Server editions for your VMS product, go to https://www.milestonesys.com/systemrequirements/.

Also, consider the following:

Prerequisite

Description
Database backup

Back up any existing databases to avoid loss of data.

During the failover cluster configuration, the wizard replicates the SQL Server databases on Node 1 to the SQL Server databases on Node 2. All data on the SQL Server databases on Node 2 is overwritten.
SQL Server service account

The SQL Server service must run under the same user account as the XProtect services.

To change a service account for the XProtect VMS, see Changing the service account that runs a VMS service
Database location

For the data replication to work:

  • Store your VMS databases data and log files in DATA and Log folders. By default, the VMS databases data and log files are stored in the DATA folder.

  • Store your SQL Server error logs, trace log files, and log events in the Log folder.

  • Keep the DATA and Log folders in the same parent folder.

  • Do not change the names of the DATA and Log folders.

The default locations are:

  • C:\Program Files\Microsoft SQL Server\MSSQL{nn}.MSSQLSERVER\MSSQL\DATA

  • C:\Program Files\Microsoft SQL Server\MSSQL{nn}.MSSQLSERVER\MSSQL\Log

{nn} is the version number.

See View or Change the Default Locations for Data and Log Files.

Virus scanning exclusions In your antivirus program, exclude the locations of the DATA and Log folders from virus scanning.
Instance name Verify that the instance name of your SQL Server is MSSQLSERVER. See View the SQL Server instance name.

External SQL Server instance

You can use a SQL Server instance that is hosted elsewhere in your network. XProtect Management Server Failover does not monitor the SQL Server databases when the SQL Server instance is hosted on a separate server.

The failover server configuration with external SQL Server does not work in a workgroup environment.

Prerequisite

Description
Permissions for the SQL Server user

In Microsoft SQL Server Management Studio, add a Windows user to the public role and map the user to the  db_owner database role for the following databases:

  • Surveillance: Management and event server

  • Surveillance_IDP: IDP

  • Surveillance_IM: Incident Manager

  • LogserverV2: LogServer

Connection

Verify that the VMS installations on both nodes are connected to the external SQL Server instance.
Service account

Make sure that the Management Server service on both nodes runs under the Windows user you added on the SQL Server computer.

If your SQL Server runs under a different user, you can change the account that runs the Management Server service. See Changing the service account that runs a VMS service.
Database conflicts

If you have two or more running management servers that are connected to the same SQL Server databases, your data might be corrupted.

To avoid potential database conflicts, go to Node 1 and: