Database Best Practices

Consider the following best practices when planning for Imprivata PatientSecure database maintenance.

Storage Hardware

• Use enterprise grade, high performance disks for data storage.

  We highly recommend redundant arrays of independent disks (RAID) to provide up-time resiliency against hardware failure.

  NOTE:

  RAID is not a substitute for a thorough backup plan. RAID is designed for increased availability, not data security. Using more drives increases the probability of having a hardware failure in any given time period.

• You may choose to use solid state drives (SSD) for performance. SSDs have a limited write lifetime, so your maintenance plan must account for replacement of individual drives, and it is critical to regularly monitor drive health.

  You should implement a RAID configuration to account for the fact that drives will fail regularly (relative to traditional magnetic spinning disks). Indexes and data files are good candidates for SSD storage because they are primarily read-heavy. Log files and tempdb can gain benefits from the faster performance, but because they are heavily write-weighted, they will significantly shorten the lifetime of the drives.

Database Creation

• Place data and log files on different physical drives.

• Log files should be placed on drives that have high write performance.

• Auto-grow should be enabled to prevent the system from halting in the event that the size of the database grows to the size initially configured.

  However, this is a last-chance safety mechanism, and storage use should be monitored on a regular basis. If you do not ensure that there is enough free space to allow for growth, then the auto-grow operation will take the database offline while it is being performed and could, without warning, take down Imprivata PatientSecure during normal operating hours.

  In addition, frequent database expansions can result in fragmentation of the file, thus reducing performance. It is better to ensure that you have plenty of space for anticipated growth.

Tempdb Database

The Tempdb database should be located on a different physical drive from both the production data and log files.

• This drive should have the best performance possible.

• The number of data files should be the lesser of eight or the number of CPU cores on the database server, and one log file.

• If you have more than eight cores and are experiencing contention, increase the number of data files in multiples of four up to the number of cores until contention drops to acceptable levels. For more information, see https://support.microsoft.com/en-us/kb/2154845.

Database Backups

• Periodically verify your access to vital components of your disaster recovery plan so that you can be confident that you will be able to perform a successful recovery in the event that you need to recover your systems.

• Practice recovering your environment periodically to ensure your team understands their roles and responsibilities during a recovery. By doing a trial recovery, you will understand any limitations in your recovery plan and so you can modify it accordingly. Further, knowing how long it takes will enable your organization to better understand the impact a disaster would have on normal operations.

• Establish a backup frequency plan.

  The following table represents the minimum frequency at which we recommend backing up each item. If your organization has more stringent requirements for frequency of backups, use those.

  Item	Backup Frequency	Verification
  Full Database	Weekly
  Transaction Logs	30 minutes

• Verify the ability to read the backups. Verify that you can still read your backups, especially for those items that are backed up very infrequently. Verification means checking that the appropriate personnel know where all the backups are located (likely because they have access to the documentation of those locations) and can find them and that they are readable.

Disaster Recovery Plan

Create a detailed and comprehensive disaster recovery plan, detailing exactly where to obtain any necessary software and backup files.

The plan should cover, at a minimum:

• Scenarios for complete loss of local resources, for example, all hardware and all backup files and physical records at the same physical facility as the hardware are lost.

• Scenarios of complete hardware failure, for example, storage crash resulting in total loss of installed software and database, but backups and physical records at the same physical location as the hardware are available.

  Therefore, the plan should cover acquisition of new hardware, a communication plan, and a software recovery plan.

Microsoft has some excellent resources on SQL Server disaster recovery: https://technet.microsoft.com/en-US/library/ms178094(v=SQL.105).aspx

Example Disaster Recovery Tasks

The disaster recovery plan will generally follow these steps.

1. Acquire hardware.

2. Acquire working installation of a supported SQL Server database server.

3. Restore the database backups.

4. Reinstall the Imprivata PatientSecure server.

5. Recover desktop clients (Imprivata PatientSecure scanning workstations) as required, updating connection strings as necessary to match any changes due to step 2.

All personnel with any responsibility for disaster recovery operations should be familiar with the location and content of the full disaster recovery plan you develop. The disaster recovery plan should be practiced on a regular basis to ensure everyone knows their roles and that all operations are fully documented. Various scenarios should be practiced.

Security of Backup Files

The database certificates backup file contains certificate files that are themselves protected with passphrases. The archive containing those passphrases is protected by a password. At a minimum, those files should be stored securely off-site in separate locations. The password for the passphrase archive also needs to be stored securely off-site, separately from both of the zip archives.

Database backups are automatically encrypted by the certificates configured by the database setup tool. Database backup files should be stored securely off-site in a location separate from the certificate and passphrase archives and the archive password. The reason all backup files should be kept in separate locations is the same reason you do not leave your car keys in your car. However, in this case, the database contains encrypted private health information (PHI), which can be trivially decrypted by someone in possession of the certificates and passphrases.

Summary of Backup Best Practices

The following table summarizes the preceding recommendations. See the full text for details.

Item	Frequency	Notes
Back Up Database Certificates	One time
Verify Location and Integrity of Backups	Two times per year	Database certificate archive, certificate passphrase archive, certificate passphrase archive password
Back Up Full Database	Weekly
Back Up Transaction Logs	30 minutes
Monitor Storage Utilization	Weekly	Monitoring storage utilization will allow you to proactively increase data file sizes during periods when Imprivata PatientSecure operations will be minimally impacted.
Hardware Monitoring	Continuous	Alerts should be configured on storage hardware to alert an administrator if a drive is on the verge of failure.
Event Log Cleanup (server and client)	Weekly
Database Index Maintenance	As necessary
Create a Disaster Recovery Plan	One time and updated whenever software or hardware is changed
Rehearse the Disaster Recovery Plan	Two times per year
Off-Site Storage of Backups	Every backup