T-SQL- Using Linked Server Data

In a modern hospital environment, you inevitably have multiple applications holding disparate sets of data. But that data is often related to the data in other systems, and sooner or later (generally sooner) someone is going to ask you something like:

“Can you pull in the time from the time-carding system to show on this productivity report alongside the clinical work that was done?”

To make things easier in these examples, we’ll assume the time-carding system is Kronos, your payroll system is PP and your clinical data is documented in PCS and/or NUR. AND we are assuming that Kronos is running on SQL Server, as is your DR.

We know that in Kronos, we have all of the time-card punches for our staff, and those staff have an Employee Number. That Employee Number matches to the HrEmployees.UserID in PP as well. (We’re pretty safe with this assumption because the Kronos time-punches have to load into PP to generate checks… you’re getting a check, right? Good!)

One way to get all this data into the same report is to write a stored procedure that does multi-step processing and one of those steps… is to query Kronos for the time-carding data.

How do we do that? We can set up a LINKED SERVER entry on our DR server. This is a sever level object that saves the DNS name of the other SQL server, a username to login to that other server, and the credential (password) for that login.

Note! When you set up a linked server, you have an option to “impersonate” the person running the report’s credentials when connecting to the target server. We don’t recommend that, because that requires you give everyone who might run the report permissions to the time-carding server. It’s a security risk, and it’s a lot more permissions to set up.

Instead we recommend that the DBA (or equivalent) at your site set up a “connector” SQL-only login with a complex, non-expiring password. Then use that “connector” account for all of the LINKED SERVER entries you set up. Name that account something like:

DR-Linked-SQL

And then you’ll have a consistent, and more secure, approach. The permissions for that account will almost always be:

READ, EXECUTE

On the target server databases.

Back to our code!

Setting up a LINKED SERVER creates an extra database object with its own name. In this case, let’s say is “Kronos”. To query a table via the linked server, we use the fully qualified name of the table, in the format:

Like so:

All right! That is good. Now you are thinking you can use tables from the linked server just like a table on your local server by referring to them by the fully qualified name, and you’d be right… except there are issues around performance.

In particular:

1. If you try and JOIN a linked server table to non-linked server tables, your query slows way, way down.
2. If you are trying to UPDATE, INSERT or DELETE something through a linked server entry, everything bogs because of SQL Server trying to maintain transactional integrity across the link.

We have worked in environments where ALL of the data had to be accessed through a LINKED SERVER entry, and queries would regularly take 10-15 times longer to execute.

Let’s avoid that!

There most straightforward programming pattern you can use to avoid slow performance due to LINKED SERVER traffic is:

1. Identify the specific sets of data you need from the LINKED SERVER.
2. Create a #Table for each data set, preferably with a PRIMARY KEY CLUSTERED defined.
3. Execute focused queries across the LINKED SERVER to populate those #Table(s).
4. Then use those local #Tables with your DR data in your final queries.

This reduces the complexity of the querying process by making each LINKED SERVER query a discrete step, with a single query:

Now we can take this #tTime table and JOIN it to whatever PP or HR tables we need, with no loss in performance.

This particular query is pretty much as fast as we can get it to execute, because it goes against a single table (VP_TOTALS).

We could also JOIN multiple tables together, from the LINKED SERVER, in a query like this, but that structure will not execute as quickly as a single table SELECT.

Extra Credit

Maximize your setup query performance by using dynamic SQL to execute a single query entirely on the target server, and just get the result set back.

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!