Hosting Live and Archival Databases from Different Servers

Hosting Live and Archival Databases from Different Servers

Procedure Steps

It is possible to host the Live and Archival databases from different servers.
 
Note: Hosting the Live and Archival databases from different servers is not recommended, as this configuration will reduce archiving and reporting performance.
 
For SQL Server, we collect and use the Servername and DB name information for connection purposes. However, the user interface only allows the user to specify the DB name. To host the Live and Archival dabases from different servers:
  1. Create the Archival database on two servers, using the same database name on both servers. One of these is a dummy Archival database. The dummy Archival database is created on the server that is hosting the Live database.
  2. In OnGuard, enable the Archive to Database feature and specify the dummy Archival database name. The check will pass because the dummy database exists on the same server as the Live database.
  3. When Setup Assistant opens, cancel it.
  4. Delete the dummy Archival database from the Live database's server.
  5. Use SQL Server Management Studio to log into your Live database, then open the LNLCONFIG table on the Live database's server and add (or modify if this record already exists) the following record (where databaseServer is the name of your Archival database server):
    LNLCONFIGID = 178, LNLSTRING = databaseServer
  6. Launch Database Setup or Setup Assistant manually.
  7. Use SQL Server Management Studio to log into your Live database, then open a Query window.
  8. Add your Archival database server as a linked server to your Live database server (where databaseServer is the name of your Archival database server):
    sp_addlinkedserver 'databaseServer'
  9. From the Control Panel, open Administrative Tools > Services.
  10. Right-click on Distributed Transaction Coordinator, and then select Properties.
  11. In the Startup type drop-down, select Automatic.
  12. Click [Start], and then click [OK].
  13. Close the Services dialog.
  14. From the Control Panel, open Administrative Tools > Component Services.
  15. Under Component Services, expand Computers > My Computer > Distributed Transaction Coordinator.
  16. Right-click on Local DTC and select Properties.
  17. On the Security tab, enable Network DTC Access, and then enable the Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound, Enable XA Transactions, and Enable SNA LU 6.2 Transactions check boxes.
  18. Click [OK]. If you are asked to restart the MSDTC service, click [Yes].
  19. Close the Component Services dialog.
To Improve Reporting Performance:
  1. Use SQL Server Management Studio to log into your Live database, then open a Query window.
  2. Create the following views (where databaseName is the name of your Archival database):
    CREATE VIEW ACCTRANS_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.ACCTRANS_RESTORED
    CREATE VIEW EVENTS_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.EVENTS_RESTORED
    CREATE VIEW ALARMSACK_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.ALARMSACK_RESTORED
    CREATE VIEW ALARM_ACK_HISTORY_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.ALARM_ACK_HISTORY_RESTORED
    CREATE VIEW EVENTS_VIDEO_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.EVENTS_VIDEO_RESTORED
    CREATE VIEW VISIT_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.VISIT_RESTORED
    CREATE VIEW VISIT_EVENT_ARCHIVED AS SELECT * FROM [databaseServer].databaseName.dbo.VISIT_EVENT_RESTORED
For Oracle, it does not matter where the Live or Archival database is hosted. OnGuard is not aware if the Live and Archival databases are on the same server or not, as this is hidden within the Oracle SID information. 

Note: In an Oracle environment, if the Live and Archival databases are running on the same server, then the Archival database name can contain the A-Z, 0-9, '_', '#', '$', or '.'  characters. If the Live and Archival databases are running on different servers, then you must add the Net Service Name on the Live database server, and the Net Service Name can contain only A-Z, 0-9, or '_' characters. Other special characters might cause the archiving function to fail.

Applies To

OnGuard 7.0 and later
SQL Server
Oracle

Additional Information