If you use the MySQL database see MySQL Schemas.

 

UltraPort database schema profiles are basically a set of information and instructions which tell the schema engine processors:

 

•How to connect to the SQL Server Database which contains (or will contain) the schema tables (see creating schema tables)

•Whether the schema tables have been created or not (see verifying schema tables)

•Which HL7 vendor/version definition to use when creating and verifying the database schema tables

•Whether to allow the schema engine to perform database maintenance or not

•Starting in version 6.6.0 allow Auto-Training which will insure that ALL HL7 data is imported into your database by dynamically altering your tables.

•Schema Warning levels

•Starting in version 6.1.3, forcing SQL Encryption

 

 

 

Walk-through: Creating a database schema profile

 

•Prefix. This is a 2 to 4 character value which you choose. It must begin with a letter (A-Z) and is not case-sensitive. When the program creates the SQL database tables it will preface the name of each one the value you enter here followed by an underscore. Example LAB1_HL7Data or LAB1_SEGMENT_PID_A. This is important because it allows you to either a) create multiple schemas within the same SQL database and/or b) create the schema tables right within your OWN SQL database without fear of overwriting any of your own database tables.

•Profile Name. Since you can create many different schema profiles, each one must have a different name. It's a freeform value and allows you to easily identify your schema in the list of schemas displayed in the main window.

•HL7 Version. This one is important and should be carefully considered. Click the button next to the field to select the HL7 Vendor Definition to use as the basis for the schema. We recommend strongly that you create your OWN HL7 vendor definitions and select them here. See Before You Begin and HL7 Vendor Definitions for more information.

•SQL Server Name. The FULL name of the SQL Server as shown in the SQL Server management studio or the SQL Server Service Manager.

 

 

•Database Name. The name of the SQL database which will contain the schema tables. NOTE: The Schema Engine does NOT create SQL databases. You have to create the database yourself or enter the name of one that already exists in the SQL Server.

•Authentication / Security. How should the Schema Engine gain access to the SQL Server database. There are two options 1) Windows Logon (Trusted) and 2) SQL Server User ID. This is VERY IMPORTANT and requires some thought before deciding which one is right for you.

1.Windows Logon (Trusted). The #1 support call we get here at HermeTech is from our database schema engine customers who chose this option without fully considering the implications. Choosing this option is usually the easiest, you can usually choose it and immediately continue on and everything works swimmingly. HOWEVER, this option is also how most users get into trouble and there are some things you need to remember if you use it. Using the Windows Logon means that when the schema engine runs it uses the currently logged in MS Windows user to connect to SQL Server. This all works just fine when you're in the configuration program creating schema profiles and running your inbound and outbound processors in local mode. BUT you have to remember that IF you want use the Schema Engine's MS Windows Services (see running as a service) you must remember that Microsoft Windows Services work differently and run in their own separate memory space and under DIFFERENT user credentials than the current user. So, if you choose this option and you want to run the services you MUST remember to go into the MS Windows Services snap-in and also configure the UltraPort Schema Engine services to run under the context of a Windows User as well.

2.SQL Server User ID. Choosing this option is the one that we recommend that you use if at all possible. The downside is that you have to go into SQL Server and create a log in / password AND make sure that the SQL User has FULL access rights to ALL of the UltraPort Schema Tables. To help facilitate this, when the Schema Engine generates your database schema tables it also creates a SQL DATABASE ROLE called <prefix>_DBARole which has been granted all rights to the schema tables. This way, if you're in a hurry and don't want to use a SQL Administrator (SA or SA equivalent) log-in you just assign your new SQL log-in to this database role and everything should work fine.

 

•Database Connection Timeout. The number of seconds the schema engine should use before it gives up trying to connect to the SQL Server. Valid values are between 5 and 60 seconds. The only important consideration here is to consider exactly WHERE is the SQL Server in relation to the Schema Engine on your network. If it's on the same computer/server then a short value (10 or 15 seconds) is probably sufficient. If your office is in New York and you're connecting to the SQL Server through a WAN to a server in Los Angeles you might want to choose something higher.

•Store HL7 Segment data in the <prefix>_MessageManifest table (Optional). This option is completely optional and is NOT currently used by any portion of the Schema Engine itself.This option is only applicable for INBOUND processors. When an HL7 message is imported into the schema tables the entire raw HL7 message is stored in the main interface table (<prefix>_HL7Data) in a field called HL7Message. The <prefix>_MessageManifest table contains just as you might expect, a "manifest" of the HL7 message which is a "vertical" listing of each HL7 segment contained in the message. If you check this option then the RAW HL7 for each segment will also be placed in this table which you might find useful for your own software development. One important consideration before checking this is SQL table space. When the Schema Engine imports an HL7 message the amount of physical space used is approximately DOUBLE the size of the message (plus a smidgen). If your HL7 message is 3KB then it takes up 6KB+ of table space. This is primarily because (as described above) the program stores the entire message (3KB) in one field AND then parses out the data into the different schema segment tables. If this option is checked, then obviously that metric changes and goes from DOUBLE+ to TRIPLE+ because the size of all of the HL7 message segments is also 3KB.

•Use the Alternat SQL Database Driver. If you experience trouble connecting to the database you can try checking this box. The Schema Engine uses Microsoft ADO to connect to SQL Server and checking this option instructs the program to try an alternate driver.

 

Once you've completed filling out this page you should verify that you've entered everything correctly by clicking the 'Test the Connection to the Database' button.

 

When clicked, the system will validate your entries and attempt to connect to SQL Server using them. NOTE: If you typed something in wrong you might have to wait for as long as the value you selected for the Database Connection Timeout for the system to respond. If everything is correct then you can save your schema profile, but you're really not done yet. Keep reading for your next steps. Obviously you can't actually DO anything unless you create your schema tables (click here for the how-to). If you're new and are evaluating the UltraPort Schema Engine then you can just jump in and start experimenting with what it's capabilities are, however, before you actually try and implement the Schema Engine you should read the Before You Begin section thoroughly.

 

Using SQL Encryption

 

To enable TLS encryption to MS SQL Server simply check the box "Force TLS Encrypted SQL Connections" and your connection to your SQL Server will be automatically encrypted.

 

IMPORTANT NOTE: This document is not intended to be an exhaustive conversation about TLS encryption and Microsoft SQL Server. Starting with SQL Server 2008 you can ALWAYS use TLS encryption with SQL Server without having to do any special configuration in SQL Server to allow it. This means that if you (or your DBA) do not explicitly configure your SQL Server by assigning a "trusted" commercial or Domain encryption certificate then SQL Server will automatically create a "self-signed" encryption certificate when the SQL services start.

 

Generally a "best-practices" approach would advise against using this "self-signed" certificate if possible which we leave to your discretion. When this property is checked the Schema Engine will set the encrypted property of it's connection string to True and the Trust Server Certificate property also to True, meaning it will explicitly "trust" any certificate used by the SQL Server. If you think that you need a more specific or complex encryption paradigm then you should create your Schemas using the ODBC connection type (See ODBC Data Connections).

 

 

 

 

 

 

Your next steps: Creating Schema Tables or setting up the database maintenance plan and entering your schema warnings settings.