PatientWorks Exchange Group

PatientWorks Database Specifications

Overview

PatientWorks operates with any SQL Server 2000 SP3a or greater database. PatientWorks uses the database to provide a centralized repository for all forms, applications, temporaryand temporary patient data and audit trail. The database needs to be installed on a SQL instance monitored by the appropriate IT staff and accessible by PatientWorks.

Preparing SQL Server for a PatientWorks Installation

Aside from installing the Microsoft SQL Server software and Service Pack 3a, the only additional step required to prepare SQL Server for PatientWorks is to create a domain logon account for the PatientWorks Services and grant both the PatientWorks vendor account and the service account full rights to the database. PatientWorks Data Services uses a single logon to SQL Server and manages a connection pool for all client interaction with the database. Please provide the SQL Server logon username/password to your PatientWorks implementation specialist. This logon account must have the proper permissions to create/delete databases. This information is used for the Database Configuration utility during the installation of the PatientWorks Application Server and is stored in an encrypted configuration file. It is important that this logon account be kept secure to protect your application data as well as patient data.

PatientWorks includes a utility called Database Configuration that allows an Administrator or PatientWorks Implementation Specialist to configure the connection to SQL Server as well as create databases. Figure 1 shows the Database Configuration utility. To configure a PatientWorks database, you must provide the server name for the SQL Server host, a friendly repository name for PatientWorks to display to users during sign on, a connection timeout for the database, the database username and password, and the maximum connections to create in the connection pool. In the figure below, we’ve create a PatientWorks database called “Production” on the local machine with a 30 second connection timeout. PatientWorks Server would connect to SQL Server using the account provide in the database configuration and create a pool of 10 connections for all PatientWorks clients to utilize when storing/retrieving information from the repository.

Once you complete this information and press Create Repository****.PatientWorks creates a SQL Server database using the provided repository name and creates all necessary tables for PatientWorks to function properly. You have the option of creating multiple PatientWorks databases. This is helpful for creating a test environment as well as a production environment. A single PatientWorks Server can manage multiple database repositories. Each PatientWorks client sign on allows the user to specify the repository to which to connect.

Backing up the PatientWorks Database

It is important that normal backup procedures be put in place to protect your PatientWorks data. Because the database contains all application and form data as well as temporary patient data cache, daily backups allow you to quickly rebuild a PatientWorks Server should you experience drive failure or some catastrophic hardware failure.
Each environment is different and dynamic which in turn requires a unique database maintenance plan. PatientWorks will tailor an initial maintenance plan that can be altered or deactivated at the customer discretion. PatientWorks understands the sensitivity of the patient data and has constructed outside of the maintenance plan, a purge tool. This built in database maintenance process that combs the database and removes inactive files, orphaned records, and other house cleaning routines. The is also configurable to run at a specific time. It is recommended that the purge tool be ran prior to the database backup.
The database maintenance plan that PatientWorks recommends has two main models that depend on patient data retention. PatientWorks understands that there are a lot of factors that come into play when devising a database maintenance plan, however the main focus for our models depends on how approximate in time should a database get restored. SQL offers 3 recovery models, however PatientWorks mainly uses two. The first model is the simple recovery model. This model will maintain a small transaction log and will require daily to hourly database backups. Transaction log backups are not allowed with this recovery model. The second model is the full recovery model. Databases set to this model will have the capability to be restored to any point in time. This model allows transaction log backups, however it is common that without shrinking these files, over time the logs can grow too large causing undesired behavior. In the event this happens it is recommended to switch the recovery model to simple and perform
DBCC SHRINKFILE (databaseName, size (in MB)) or run a script that your DBA might construct.


Hardware Recommendations
Database Server
Windows Server 2003
Microsoft .NET Framework 2.0 (provided with PatientWorks® installation)
3 GHz processor or above
4 GB RAM per PatientWorks database
Dual 120 GB disk arrays (RAID 1 or 5) with 80 GB allocated space for database
Redundant power supply
MS SQL Server 2005 (not provided with PatientWorks® installation)