Database Mirroring

Patriot's database mirroring feature provides a very low latency 'warm' backup solution for businesses who demand the shortest possible monitoring down-time, and the most minimal signal loss in the rare event of a critical server fault. Database mirroring works by sending all transactions (received signals, activation status changes, client file updates, report settings, etc.) on the primary (principal) server in real-time - as they happen - to a preconfigured Patriot backup (mirror) server. This system keeps the two databases synchronised to within a fraction of a second, this is a marked reduction on Patriot's standard minimum automated backup time-delay of thirty seconds. With database mirroring enabled backup server testing is simplified; the server currently acting in the 'mirror' role can be promoted to the 'principle' in a matter of seconds (Note: actual down-time between primary and backup server monitoring will be longer and vary across systems as it it highly dependent on the alarm receiver/panel/camera hardware installed).

Prerequisites

• Enterprise level Patriot 6.2+ registration with identical build numbers (build number should be 09.05.13 or later to enable all features) on both primary and backup servers.

• An identical Standard edition of Microsoft's SQL Server (the Express version does not support this feature) on both primary and backup servers.

• SQL Server instances on both primary and backup must be configured to run under Windows accounts with correct Connect permissions and Named Pipes should be enabled on the instance in the SQL Server Configuration Manager (see Troubleshooting section at the bottom of this document).

See also

• Mirroring Monitor task

Setup

On the backup server, ensure that the Patriot services are not running, and then start the Patriot Utilities program from the Windows start menu.

Navigate to the Mirroring setup tool: Maintenance → Database →Mirroring Setup and enter your Microsoft SQL and Patriot server details before clicking the arrow button to proceed. The SQL server details can refer to either the primary or backup instance, but the Patriot server details must be for the primary Patriot Server. The Patriot Host Port No. can be found by opening the connection settings panel from any Patriot client login screen.

The second stage of the Mirroring Setup tool requires the names (or IP addresses) of the primary and backup MS SQL servers to be entered. The primary server must be set to the machine hosting the SQL server instance to which the primary Patriot server is connected. The backup SQL server may or may not have a Patriot database installed. When all form fields have been completed click the enable button to begin mirroringand a pop-up window will appear, prompting for a new primary server backup to taken, and restored for mirroring on the backup server.

If "No" is selected at the prompt the mirror database restore tool will open. This tools requires up-to-date backup files from the primary server - see this document for information on creating a backup of Patriot's databases. "Full database Restore and Transaction Log Restore" should be selected, and the paths to the main and archive database backup .dat files must be entered into the appropriate fields on the form. Note that transaction log (.log) files must be saved in the same Windows directory as the .dat backup files in order for Patriot to detect them. Close the Restore Mirror Databases tool when the restore process completes.

Finally, to complete the database mirroring setup process, return to the Mirroring Setup tool and select "Yes."

Mirroring Monitor Task

Patriot includes a Mirroring Monitor task to help you monitor the mirroring status of the core Patriot database. This task will generate alarm events if, for example, the backup (mirror) MS SQL instance was to drop offline. It is strongly recommended that this task is setup immediately after enabling databases mirroring.

Managing the Mirrored Patriot database connection

The Patriot database mirror connection is controlled with the Patriot Configurator program which can be found through the Windows start menu on a Patriot primary or backup server. The mirroring control tools are located beneath the Data and Task service tools. On the left side of the window, text is displayed indicating the current state of the mirroring connection (note that this indicator is not updated automatically, the "refresh" button at far right must be used to get up-to-date information). The "play" button is used to control the connection and has two functions depending on the current state of the connection:

• Manually failover (force the two databases to switch roles) when they are in a "Principle Synchronized" state. This function is useful for carrying out planned failover tests of backup server hardware. Provided that the Patriot Data Service is configured with both a Primary and Backup SQL server, as soon as the Mirror is promoted, the Patriot Data Service will switch over to the Backup server and there should be little or no noticeable disruption to Patriot operation.

  Note: the manual failover function is only avaliable from the server acting as the Principle, and when in the "Principle Syncronized" state.

Manual Failover button is enabled when in the "Principle Syncronized state".

• Promote the Mirror database to the Principal role in response to a major fault on the current Principal server resulting in the "Mirror Disconnected" state being assumed by the Mirror server. After promoting the Mirror server its Patriot databases will enter the active state "Principle Disconnected" and a Patriot Data service can connect as normal and monitoring may be continued from backup hardware. If the primary server is later restored, the mirroring connection will automatically be re-established, once a "Synchronised" state is reached (again, note that the "refresh" button must be used to update the status indicator) the Manual Failover function will again become avaliable allowing Patriot monitoring to return to the original Principal server.

Promote to Pinciple button is enabled when in the "Mirror Disconnected" state.

Disable a Patriot Database Mirroring session

A Patriot database mirroring session can be de-activated with the same Mirroring Setup tool provided by the Patriot Utilities program and explained in the Setup section of this document. The de-activate button to the right of the activate button will be operational if any Patriot database mirroring connection is detected. This button should be used to disable the mirroring session - a safety confirmation window will ask for confirmation before the session is disabled. Once disabled, Patriot databases on both servers will enter an active state and neither will be automatically updated to reflect changes in the other.

Screenshot of the Patriot Utilities program Mirroring Setup tool when in the active state ready for de-activation.

Troubleshooting

"An error occurred while activating Database Mirroring..."

If a message like the above is returned after pressing the "Activate" button:

• Check that the SQL server instances are configured to run under Windows accounts with sufficient access rights: find the Services administrative tool on your version of Windows and then locate the SQL Server instance used by Patriot. Right click the service and open the properties window. The most straight forward setup is to configure the service to login under the same Windows domain account on both primary and backup servers. Restart the SQL server process after making any changes. See the following document for more information on SQL server Windows accounts and permissions:

  http://blogs.msdn.com/b/grahamk/archive/2008/12/11/database-mirroring-error-1443-connection-handshake-failed.aspx

• If you do not have a Windows domain server you will need to use security certificates to allow non-domain accounts access to mirroring endpoints. Essentially you are required to create a SQL Login on each server which matches that used as the "Run Under" account of the SQL server instance on the opposite SQL server. To this Login, a SQL User is mapped to the Master database, and that user is authorised with a security certificate generated on the opposite server. TSQL scripts are avaliable from Microsoft from the above link.

• Check that the backup files are up-to-date. The latest transaction log should cover up to the most restore point as possible. This requirement is difficult to quantify - high Patriot signal traffic between the time of the restore point backup and the primary server's current database state will reduce the size of the allowable time gap.

• Manually disabling any older mirroring sessions may be required before a new session can be enabled in older versions of Patriot. If this is the case run the Database -> Force Mirroring Shutdown tool to completely disable any existing mirroring sessions. Also see this reference document from Microsoft for information on disabling Mirroring sessions manually via SQL commands:

  http://msdn.microsoft.com/en-us/library/ms190471.aspx