PatientWorks Exchange Group

Service Packs/Security

It is the responsibility of the client to ensure that Microsoft SQL Server be kept up-to-date with the proper security patches/service packs to avoid exposing your database server to internal or external vulnerabilities. In the event that the database fails, it is the responsibility of the client to have a fail over system in place. Please follow all Microsoft security bulletins and install any/all recommended service packs.

It is also the client’s responsibility to keep the security of the database server locked down to prevent unauthorized access to patient data and your PatientWorks applications/forms. Please notify your PatientWorks Project Manager or Technical Support should your PatientWorks database account username/password change so the updated information can be configured in the Database Configuration utility. PatientWorks comes standard with HIPAA event logging. This logging is retained on the PatientWorks server and records certain events that occur for each user logged into PatientWorks. Please refer to the PatientWorks User Manual for the complete list of all the events captured in this log.


Database Maintenance Recommendations
• To ensure that full disaster recovery is achievable with your PatientWorks databases, the database recovery model should be set to Full from within Enterprise Manager.
• Backup database files on a nightly/daily basis.
• Backup database transaction logs on a 1 to 5 hour basis, depending on the throughput of the system. Smaller facilities can back up the transaction log on a 5 hour basis, while busier facilities should backup the logs on an hourly basis.
• It is necessary to run the sp_updatestats command on a periodic basis to make sure that the database is performing optimally. For most installations of PatientWorks, running this command on a nightly basis is recommended for all PatientWorks databases.
• Periodic use of the following script is recommended on a biweekly or monthly basis, depending on the throughput of the system. The script reindexes the selected database to improve searching and updates to patient data.
o USE [DBName] --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Recommended Database Maintenance Plan
Create a maintenance plan for each PatientWorks database. Please be sure to set the recovery model to Full for any PatientWorks database so that the transaction logs can be backed up as part of the maintenance plan. If the recovery model is set to Simple, the transaction log portion of the maintenance plan will fail.

General Tab
• Check the desired database


Optimizations Tab
• Reorganize data and index pages – Unchecked
• Update the statistics used by the query optimizer – Unchecked
• Remove unused space from database files – Unchecked


Integrity Tab
• Check database integrity – Checked
o Include indexes – Selected
 Attempt to repair any minor problems – Unchecked
• Schedule: At least once per week during off-peak hours


Complete Backup Tab
• Back up the database as part of the maintenance plan – Checked
• Verify the integrity fo the database upon completion
• Specify the required back up location for your organization. Typically, the backup is set to a directory that is included in an organization’s network backup plan.


Transaction Log Backup Tab
• Back up the database as part of the maintenance plan – Checked
• Verify the integrity fo the database upon completion
• Specify the required back up location for your organization. Typically, the backup is set to a directory that is included in an organization’s network backup plan.


Reporting Tab
• Write report to a text file in directory – Checked – specify an appropriate directory.
o Delete text report files older than: - Checked – Typically, set to 4 weeks.
• Write history to the table madb.dbo.sysdbmaintplan_history – Checked
o Limit rows in the table to: - Checked – Typically, set to 1000 rows.


Recommended PatientWorks System Indexes

Table Name Column Name
Apps Name
Form_revisions Filename
Form_revisions Form_id
Forms Filename
Html_forms Filename
Jobs App_id
Jobs Status
Jobs Job_type
Log_events Event_type
Log_events Severity
Log_events App_name
Log_events Job_id
Packets App_id
Pages App_id
Pages Name
Pages Form_front_id
Pages Form_back_id
data_sources tables app_id
directory_aliases alias
field_mappings data_source_id
field_mappings page_id
form_objects form_id
output_devices page_id
output_devices and output_device_type_id
output_device_... output_device_id
printer_aliases alias
raw_files filename
security_data_conditional_expression_elements app_id
security_data_conditional_expression_elements group_name
security_reprint_conditional_expression_elements app_id
security_reprint_conditional_expression_elements group_name
selected_output_pages page_id