Skip to main content
Skip table of contents

Configuring VMS to use an external Microsoft SQL Server

Information regarding how to configure Mirasys VMS to use an external MS SQL Server installation than the default MS SQL Server Express.

Currently is possible to use different names for databases. Before making any modifications you need to stop VMS services; WDServer, SMServer and DVRServer.

If there are any temp files, remove those before starting services again. Because the system may detect that those files are modified and restore data from temp file, which overwrite SQL connection string changes.

Database configuration files location

You can find in these locations database configuration files, which need to be modified if using an external Microsoft SQL Server.

  • C:\Program Files\DVMS\SystemManagement\Database.xml

  • C:\Program Files\DVMS\DVR\dvr.xml

  • C:\Program Files\DVMS\DVR\DatabaseConfigurationFailover.xml

    • If this file is missing, you can create it manually.

  • C:\Program Files\DVMS\Export\DatabaseConfiguration.xml

  • C:\Program Files\DVMS\IncidentReporting\DatabaseConfiguration.xml

  • C:\Program Files\DVMS\StorageLocker\DatabaseConfiguration.xml

Connection string

Mirasys uses the connection string method to make a connection to Microsoft SQL Server. More information can be found here.

Example of connection string

XML
<ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=DvmsData;Integrated Security=True" />

In this example

  • Data Source=localhost\sqlexpress

    • Server address and instance name

  • Initial Catalog=DvmsData

    • Database name

  • Integrated Security=True

    • Use Windows authentication

Second example of connection string

XML
<ConnectionString value="Data Source=WIN-1234ASD,1433;Initial Catalog=DvmsData;User Id=LoginUsername;Password=UserPassword;Integrated Security=False" />
  • Data Source=WIN-1234ASD,1433

    • Server address and port for connection

    • When using SQL Server, there is no need to fill instance name

    • If needed, you can add the instance name ServerAddress\InstanceName

      • WIN-1234ASD\SQLEXPRESS,1433

  • Initial Catalog=DvmsData

    • Database name

  • User Id=LoginUsername

    • Log in username to make a login to SQL Server

  • Password=UserPassword

    • Password for username

  • Integrated Security=False

    • This means that the login method to SQL Server is used by SQL Server’s user management

Third example of connection string

CODE
<AnprConnectionString value="Data Source=WIN-1234ASD,1433;Initial Catalog=Anpr;User Id=LoginUsername;Password=UserPassword;Integrated Security=False" />
  • Data Source=WIN-1234ASD,1433

    • Server address and port for connection

    • When using SQL Server, there is no need to fill instance name

    • If needed, you can add the instance name ServerAddress\InstanceName

      • WIN-1234ASD\SQLEXPRESS,1433

  • Initial Catalog=Anpr

    • Database name

  • User Id=LoginUsername

    • Log in username to make a login to SQL Server

  • Password=UserPassword

    • Password for username

  • Integrated Security=False

    • This means that the login method to SQL Server is used by SQL Server’s user management

Configuration

Before making changes to database files, it is need to stop these services.

  • WDServer

  • DVRServer

  • SMServer

  • ADV Export Service

  • ADV Incident Reporting Service

  • ADV Storage Locker Service

Database.xml

  1. C:\Program Files\DVMS\SystemManagement\

  2. Open the Database.xml file example using Notepad.

  3. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “DvmsData”, “WdContext”, “DvmReportContext“, and “Failover” values with your database names.

    • Please note that “DvmsData“ is mentioned two times in this configuration.

      • In “DvmsDatabaseName value” and “ConnectionString value”.

    • If you need to change data retention times, please check this guide.

    • The DvmReportContext database is created when the end user uses the Spotter Audit Plugin.

  4. Save the Database.xml file.

dvr.xml

  1. Browse to C:\Program Files\DVMS\DVR\

  2. Open the dvr.xml file example using Notepad.

  3. Fill this information end of the file before </dina>.

    XML
    <!-- Read database configuration from dvr.xml file. The format for settings in dvr.xml is -->
    <Metadata>
         <Database>
         <DatabaseName value="RecorderDB" />
         <Enabled value="True" />
         <ParameterMarker value="@" />
         <ProviderType value="System.Data.SqlClient" />
         <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=RecorderDB;Integrated Security=True" />
         <CreateDatabaseCommand value="CREATE DATABASE RecorderDB"/>
         <CreateTablesCommand value="USE RecorderDB CREATE TABLE RecorderMetadata (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SourceType int NOT NULL, EventTime bigint NOT NULL, FormatType int NOT NULL, Channel int NOT NULL, MetaData nvarchar(MAX) NULL ) CREATE NONCLUSTERED INDEX IX_RecorderMetadata ON RecorderMetadata ([Channel] ASC, [SourceType] ASC, [FormatType] ASC, [EventTime] ASC)CREATE INDEX IX_EventTime ON RecorderMetadata (EventTime)" />
         <InsertCommand value="INSERT INTO RecorderMetadata(SourceType, EventTime, FormatType, Channel, Metadata) VALUES(@SourceType,@EventTime,@FormatType,@Channel,@Metadata)" />
         <DeleteRowsCommand value="DELETE FROM RecorderMetadata WHERE EventTime in (SELECT TOP {0} EventTime FROM RecorderMetadata ORDER BY EventTime ASC)" />
      </Database>   
    </Metadata>
  4. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “RecorderDB” value with that database name.

    • Please note that this is mentioned two times in this configuration.

      • In “ConnectionString value” and “CreateTablesCommand value”.

  5. Save the dvr.xml file.

dvr.xml with Anpr database

If you are using the EasyLPR feature in the VMS Server, this has its connection string name.

  1. Browse to C:\Program Files\DVMS\DVR\

  2. Open the dvr.xml file example using Notepad.

  3. Fill this information end of the file before </dina>.

    XML
    <!-- Read database configuration from dvr.xml file. The format for settings in dvr.xml is -->
    <Metadata>
          <AnprConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=Anpr;Integrated Security=True"  />
          <AnprMaxRows value="10000" />
      </Database>   
    </Metadata>
  4. Edit the “AnprConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “Anpr” value with that database name.

    • The Anpr database is created when the end user uses the Spotter EasyLPR Plugin.

  5. Save the dvr.xml file.

dvr.xml with RecorderDB and Anpr databases

  1. Browse to C:\Program Files\DVMS\DVR\

  2. Open the dvr.xml file example using Notepad.

  3. Fill this information end of the file before </dina>.

XML
<!-- Read database configuration from dvr.xml file. The format for settings in dvr.xml is -->
<Metadata>
     <Database>
     <DatabaseName value="RecorderDB" />
     <Enabled value="True" />
     <ParameterMarker value="@" />
     <ProviderType value="System.Data.SqlClient" />
     <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=RecorderDB;Integrated Security=True" />
     <CreateDatabaseCommand value="CREATE DATABASE RecorderDB"/>
     <CreateTablesCommand value="USE RecorderDB CREATE TABLE RecorderMetadata (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SourceType int NOT NULL, EventTime bigint NOT NULL, FormatType int NOT NULL, Channel int NOT NULL, MetaData nvarchar(MAX) NULL ) CREATE NONCLUSTERED INDEX IX_RecorderMetadata ON RecorderMetadata ([Channel] ASC, [SourceType] ASC, [FormatType] ASC, [EventTime] ASC)CREATE INDEX IX_EventTime ON RecorderMetadata (EventTime)" />
     <InsertCommand value="INSERT INTO RecorderMetadata(SourceType, EventTime, FormatType, Channel, Metadata) VALUES(@SourceType,@EventTime,@FormatType,@Channel,@Metadata)" />
     <DeleteRowsCommand value="DELETE FROM RecorderMetadata WHERE EventTime in (SELECT TOP {0} EventTime FROM RecorderMetadata ORDER BY EventTime ASC)" />
     <AnprConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=Anpr;Integrated Security=True"  />
     <AnprMaxRows value="10000" />
  </Database>   
</Metadata>
  1. Edit the “ConnectionString value” and “AnprConnectionString value” to match these SQL Server details, where you want to make the connection.

    • If you want to use your database name, replace the “RecorderDB” value with that database name.

    • Please note that this is mentioned two times in this configuration.

      • In “ConnectionString value” and “CreateTablesCommand value”.

    • If you want to use your database name, replace the “Anpr” value with that database name.

    • The Anpr database is created when the end user uses the Spotter EasyLPR Plugin.

  2. Save the dvr.xml file.

DatabaseConfigurationFailover.xml

  1. Browse to C:\Program Files\DVMS\DVR\

    • If the file is missing, you can create it manually.

    • Example of DatabaseConfigurationFailover.xml

      XML
      <?xml version="1.0" encoding="utf-8"?>
      <configuration connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=RecorderFailover;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  2. Open the DatabaseConfigurationFailover.xml file example using Notepad.

  3. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “RecorderFailover” value with that database name.

  4. Save the DatabaseConfigurationFailover.xml file.

Export Service DatabaseConfiguration.xml

  1. Browse to C:\Program Files\DVMS\Export

  2. Open the DatabaseConfiguration.xml file example using Notepad.

  3. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “Export” value with that database name.

  4. Save the DatabaseConfiguration.xml file.

Incident Reporting Service DatabaseConfiguration.xml

  1. Browse to C:\Program Files\DVMS\IncidentReporting

  2. Open the DatabaseConfiguration.xml file example using Notepad.

  3. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “IncidentReporting” value with that database name.

  4. Save the DatabaseConfiguration.xml file.

Storage Locker Service DatabaseConfiguration.xml

  1. Browse to C:\Program Files\DVMS\StorageLocker

  2. Open the DatabaseConfiguration.xml file example using Notepad.

  3. Edit the “ConnectionString value” to match these SQL Server details where you want to make the connection.

    • If you want to use your database name, replace the “StorageLocker” value with that database name.

  4. Save the DatabaseConfiguration.xml file.

Changing the database name after configuration

If there is a need to change the database name after the first configuration, you only need to replace the database name with the new one.

After the database name change, the Mirasys VMS will not transfer data from the old database to the new one. This needs to be done manually.

Default database XML files

Database.xml

XML
<?xml version="1.0" encoding="utf-8"?>
<Database>
  <Enabled value="True" />
  <MaximumAlarmEventRows value="100000" />
  <MaximumAlarmEventDays value="2147483647" />
  <MaximumAuditTrailRows value="2147483647" />
  <MaximumAuditTrailDays value="2147483647" />
  <MaximumWatchdogEventRows value="100000" />
  <MaximumWatchdogEventDays value="2147483647" />
  <ProviderType value="System.Data.SqlClient" />
  <DvmsDatabaseName value="DvmsData" />
  <!-- <ConnectionString value="Data Source=10.99.100.153;Initial Catalog=DvmsData;User Id=sa;Password=test;Integrated Security=False" /> -->
  <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=DvmsData;Integrated Security=True" />
  <ParameterMarker value="@" />
  <Tables>
    <AlarmConfig value="AlarmConfig" />
    <AlarmAction value="AlarmAction" />
    <AlarmEvent value="AlarmEvent" />
    <AuditTrail value="AuditTrail" />
    <AlarmExport value="AlarmExport" />
  </Tables>
  <WdDatabase>
    <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=WdContext;Integrated Security=True" />
  </WdDatabase>
  <DvmReportDatabase>
    <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=DvmReportContext;Integrated Security=True" />
  </DvmReportDatabase>
  <FailoverDatabase>
    <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=Failover;Integrated Security=True" />
    <MaximumFailoverLogEntryRows value="5000" />
  </FailoverDatabase>
</Database>

dvr.xml

XML
<!-- Read database configuration from dvr.xml file. The format for settings in dvr.xml is -->
<Metadata>
     <Database>
     <DatabaseName value="RecorderDB" />
     <Enabled value="True" />
     <ParameterMarker value="@" />
     <ProviderType value="System.Data.SqlClient" />
     <ConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=RecorderDB;Integrated Security=True" />
     <CreateDatabaseCommand value="CREATE DATABASE RecorderDB"/>
     <CreateTablesCommand value="USE RecorderDB CREATE TABLE RecorderMetadata (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SourceType int NOT NULL, EventTime bigint NOT NULL, FormatType int NOT NULL, Channel int NOT NULL, MetaData nvarchar(MAX) NULL ) CREATE NONCLUSTERED INDEX IX_RecorderMetadata ON RecorderMetadata ([Channel] ASC, [SourceType] ASC, [FormatType] ASC, [EventTime] ASC)CREATE INDEX IX_EventTime ON RecorderMetadata (EventTime)" />
     <InsertCommand value="INSERT INTO RecorderMetadata(SourceType, EventTime, FormatType, Channel, Metadata) VALUES(@SourceType,@EventTime,@FormatType,@Channel,@Metadata)" />
     <DeleteRowsCommand value="DELETE FROM RecorderMetadata WHERE EventTime in (SELECT TOP {0} EventTime FROM RecorderMetadata ORDER BY EventTime ASC)" />
     <AnprConnectionString value="Data Source=localhost\sqlexpress;Initial Catalog=Anpr;Integrated Security=True"  />
     <AnprMaxRows value="10000" />
  </Database>   
</Metadata>

DatabaseConfigurationFailover.xml

XML
<?xml version="1.0" encoding="utf-8"?>
<configuration connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=RecorderFailover;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Export Service DatabaseConfiguration.xml

XML
<?xml version="1.0" encoding="utf-8"?>
<configuration connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Export;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Incident Reporting Service DatabaseConfiguration.xml

XML
<?xml version="1.0" encoding="utf-8"?>
<configuration connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=IncidentReporting;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Storage Locker Service DatabaseConfiguration.xml

XML
<?xml version="1.0" encoding="utf-8"?>
<configuration connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=StorageLocker;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Troubleshooting

If you are not sure if the connection is not working, you can use SQL Management Studio to test connection to Microsoft SQL Server.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.