SQL Server

Deploying SQL Server schema changes using Flyway migration scripts

I was recently involved in a project using PostgreSQL on Linux where my goal was to “sprinkle some devops” on the database development process by getting the database schema in source control and automating the schema change deployments. For such a task, in the Microsoft SQL Server ecosystem, I would usually start with SQL Server Data Tools (SSDT). It is a very nice addon included for free with the various tools surrounding SQL Server and it supports my preferred database schema deployments approach – state based deployments. However, for this particular project I was dealing with PostgreSQL and since SSDT does not work with it I had to do some research to see what my options were.

Just a quick aside here – when it comes to deploying schema changes for databases with a strongly enforced schema (this usually means relational database systems) there are two main approaches: state based (the one supported by SSDT above) and migrations based. The focus of my current article is not to define these or to present their pros and cons but I want to include here some excellent reference articles that cover those points very well. It’s important to understand these two approaches because that will make it much easier to relate to the tools you have available to follow
your preferred approach.

So back to my search – I wanted to see what tools I could find for schema deployment automation with PostgreSQL. Since SSDT is a free tool on the SQL Server side I was looking for free / open source options that I could use. I wasn’t able to find any solid open source tools that support the state based approach that SSDT follows and I wasn’t too surpised about it. In the state based approach, the tool used has to do the heavy work: here’s my schema version A and I’d like to get to schema version B … please generate the SQL scripts for me that will allow me to get there. Such a tool would be fairly complex to write and if such open source options would exist they would probably be focused on a particular database engine (since the generated SQL would be engine specific).

Most of the articles I looked at mentioned migration based tools for managing PostgreSQL schema deployments – with two such tools listed in pretty much every article I looked at:

Some quick points of comparison between the two:

  • both are open source tools with some commercial extensions for advanced features
  • both are backed by commercial entities (Liquibase by Datical and Flyway by Redgate)
  • both are command-line tools that are Java-based
  • Flyway supports migration scripts in plain SQL format while Liquibase supports additional formats such as XML, YAML and JSON (this is useful if you need to migrate schema changes between database systems that don’t use the same SQL dialect)

In the end, for my PostgreSQL needs, I decided to work with Flyway. Why? Here are some quick reasons:

  • the GitHub project for Flyway is liked by more than twice the number of followers that Liquibase has (this has to mean something, right?)
  • Flyway appeared to me to be the less complex of the two. I didn’t need the advanced features of Liquibase and I was ok with writing migration scripts in SQL (which is what Flyway supports).
  • Flyway recently received commercial support from Redgate. I’m very familiar with Redgate’s tools for SQL Server and the fact that they got behind this particular open source project is a good
    sign in my book.

After I got Flyway working on PostgreSQL on Linux I started thinking about how Flyway might work with SQL Server on Windows. Why would this matter and why is it a good thing to know?

  • I said above that for SQL Server I prefer state based migrations using SSDT – but not everybody does. There are many DBAs working with SQL Server who don’t want to trust a tool (SSDT) to write the migration scripts so for them it’s more natural to use a migrations approach to schema deployments.
  • It’s good to be familiar with a database deployment tool that can be used across multiple database engines and operating systems.
  • It forced me to understand the deployment workflow needed when migrations are used. It’s always good to see multiple points of view and recognize their pros and cons.

So – how do we use Flyway migration scripts with SQL Server on Windows? Let’s proceed.

Go to https://flywaydb.org/download/ and download Flyway for Windows. It comes as a zip file so extract the archive and simply add the new flyway-6.2.4 directory to the PATH to make the flyway command available from anywhere on your system. Flyway is distributed with its own JAVA runtime environment (JRE) so you should be able to just type flyway in a command prompt to confirm you’re ready to use it. By default, when called without any parameters, flyway will give you a description of its execution options.

Let’s look at the Flyway directory structure.

By default, there are 2 folders you want to pay attention to initially. conf is the folder where the Flyway configuration file exists by default and sql is the folder where Flyway will look for SQL migration scripts it needs to execute. Both of these can be modified via configuration parameters but for now we’ll just work with the defaults.

Let’s look at flyway.conf and see what options we need to modify.

flyway.url – the database location (in JDBC format)
flyway.user – user info to use for authentication
flyway.password – the user’s password
flyway.locations – file system locations for the SQL migration files (if you don’t want to use the default /sql folder)

The options above (and many others) are very nicely documented in the configuration file so it should give you a good idea of what’s available and how to configure them.

One word about flyway.url: that config option contains the database name to use for the migration scripts so the database has to exist already. Since creating the database is typically a one-time operation it should be handled outside of the process that delivers the Flyway migrations. In my case, I’m using the following value to point to a local SQL Server database:

flyway.url=jdbc:jtds:sqlserver://localhost:1433/FlywayDemo

Note: the above format is not exactly the one suggested in the config file for SQL Server. I couldn’t get that one to work so I searched for alternate versions and found the one above (thank you StackOverflow).

With the basic configuration options out of the way let’s look now at the Flyway commands that we can work with. There aren’t that many (I did mention that Flyway is simple to use, right?):

migrate – Migrates the database
clean – Drops all objects in the configured schemas
info – Prints the information about applied, current and pending migrations
validate – Validates the applied migrations against the ones on the classpath
undo – [pro] Undoes the most recently applied versioned migration
baseline – Baselines an existing database at the baselineVersion
repair – Repairs the schema history table

Even without knowing much about Flyway or SQL migrations you can probably deduce (by everything you’ve seen above) how Flyway actually works. You, the developer, provide SQL scripts that modify the database schema based on your needs. The only requirement is that you have to name the scripts in a certain way (with version numbers) and place them in the folders that Flyway looks at. When you ask Flyway to migrate a target database schema it will look at the migration scripts it has available, it will figure out the schema version for the target database (using its own Flyway version history table) and it will execute the scripts that are not yet on the target. That’s pretty much all there is to it.

Let’s see what happens when we execute flyway info without any existing migration scripts.

Flyway will attempt to connect to the target and show information about the target’s schema version compared to any migration scripts it finds. In our case we don’t yet have any migration scripts to execute.

Let’s create a script to add a table. By default (and this can be configured – of course) Flyway expects migration scripts to follow a certain naming pattern in order to be picked up.

I created a SQL migration script file called V1__add_new_table.sql with the following contents:

Here’s what happens when we call flyway info (to see if it detects the new migration file), flyway migrate (to apply the migration), and flyway info again to see the result.

What happened on the SQL Server side?

We see the new table we just created via the migration along with Flyway’s own table for keeping track of schema versions – flyway_schema_history. Let’s see what’s inside that table:

This is pretty much the same information returned by the flyway info command. We looked here at how to get started with Flyway and how to create a versioned migration (those that start with V by default). Versioned migrations, once applied, should never be modified again. Flyway also has the concept of repeatable migrations (those files start with R) – these are migrations that will be executed in the future as long as Flyway detects that the migration file has been modified. Repeatable migrations are useful for scripts that can be easily re-executed (such as those that recreate procedures / views / functions or reinsert bulk data).

One tip that I found useful – how do you execute different migration scripts in different environments? Let’s say that in development you want certain sample data to be present but you don’t want the same data to be found in production. The trick to do that is to put common migration scripts in a common folder (for example) and then have different per-environment folders. When you execute migrations against development you’ll modify the flyway.locations parameter to run scripts from common and the development environment folders and so on for the other environments.

How do you generate the migration scripts to run? Flyway will not help there. It’s up to you to either create them manually or rely on some other tool that can produce the schema diff scripts for you. Flyway will simply execute the scripts it finds in the SQL folders.

How do you use Flyway in a CI/CD pipeline? That depends on the process and tool you use for CI/CD. You could install the Flyway executable directly with your CI/CD environment and use it that way, or, the way I used it with GitLab, you could rely on the fact that Flyway is distributed as a Docker container that you can easily pull in, execute the commands you need, and then throw it away.

Happy SQL schema migrations!

How to parse HL7 2.x messages stored in SQL Server using Python

jupyter

I’ve recently had a need to parse HL7 2.x messages stored in SQL Server. If you don’t know what HL7 2.x healthcare messages look like then here’s a quick sample:

MSH|^~\&|HIS|MedCenter|LIS|MedCenter|20060307110114||ORM^O01|MSGID20060307110114|P|2.3
PID|||12001||Jones^John^^^Mr.||19670824|M|||123 West St.^^Denver^CO^80020^USA|||||||
PV1||O|OP^PAREG^||||2342^Jones^Bob|||OP|||||||||2|||||||||||||||||||||||||20060307110111|
ORC|NW|20060307110114
OBR|1|20060307110114||003038^Urinalysis^L|||20060307110114

Each line in the HL7 message is called a segment and then each segment is split into individual fields by | (pipe) characters (typically). HL7 fields have well-defined names and meanings … for example in the example above PID-3 (the 3rd field in the PID segment where the identifier ‘PID’ is not counted) is 12001 and that represents the patient identifier.

For this particular project I’m working on we have HL7 messages stored in a SQL Server 2016 database table where each row in the table contains the raw HL7 2.x message in a particular column. I need to be able to intelligently filter over this HL7 data by looking at values in particular HL7 fields (as shown above). Since this HL7 data is stored in a varchar(MAX) column I could certainly attempt to play games using LIKE comparisons in SQL but that would not get me very far. SQL simply does not understand the complex structure of HL7 and I have no native SQL Server functions at my disposal that I could quickly use to parse this data and filter it.

I know I must get help from somewhere else. I’ve recently been experimenting with Python and with some quick Google searches I was convinced that Python had some interesting packages that could help with HL7 2.x parsing. Now – keep in mind that this is SQL Server 2016 so SQL Server Machine Learning Services is not available for this version … only for SQL Server 2017 and higher (this is the capability that would enable native execution of R and Python scripts directly in SQL Server T-SQL code). If I can’t run Python code natively in this version of SQL Server then I must execute this Python code from the outside.

I’ve also been looking at Jupyter Notebook recently – an awesome environment for interactive exploration of data sets using languages such as Python – so this was a good excuse to try to bring together all these technologies to enable me to look at HL7 data.

This is not a blog post about Jupyter but if you’d like to take a look at it the easiest way to do it is by installing the Anaconda Distribution which will bring together Jupyter, Python and a ton of other packages and frameworks that are super-useful for data analysis, data science and machine learning.

Once you have Jupyter installed this is the process we’ll follow to deal with HL7 data in SQL Server:

  • Connect in Python to SQL Server

 

import pandas as pd
import pyodbc

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=localhost;DATABASE=Training;Trusted_Connection=yes')

query = "SELECT * FROM HL7Messages"

 

  • Bring data from the SQL Server table into a pandas data frame (pandas is a well known package in Python that makes data exploration really easy)
df = pd.read_sql(query, sql_conn)
  •  Combine the pandas data frame functionality with HL7 parsing in Python in order to filter HL7 messages as needed
import hl7

for index, row in df.iterrows():
    h = hl7.parse(row['HL7Message'])
    print("Message type: {}, Patient ID: {}".format(h.segment('MSH')[9], h.segment('PID')[3]))

… which produces the following output using the sample data I was looking at:

Message type: ORM^O01, Patient ID: 12001
Message type: ORU^R01, Patient ID: 999999999

The HL7 parsing functionality in Python is provided by this package:

https://python-hl7.readthedocs.io/en/latest/

If you’d like to follow along you can look at my GitHub repo below where I’ve provided some sample HL7 2.x messages, a SQL script to create a table with that data and Jupyter notebook files to directly execute the relevant Python code:

https://github.com/csatnic/PythonHL7Parsing

 

 

 

Strategy and tips for performance troubleshooting in SQL Server

There was probably a time in its early days when SQL Server could be considered a simple database engine – we’d run queries with SQL and we were probably pleasantly surprised when stuff just worked. Much has changed since then. Over time SQL Server has evolved into a complex relational database management system (RDBMS). If there is some task that involves data manipulation then SQL Server most likely has a component to handle that – data queries with SQL, multi-dimensional analysis, statistical analysis with R, reporting, integration with other data sources, ETL and so on.

On one hand it is amazing that given all this complexity there are plenty of workloads that work just well enough on their own (no full-time DBA needed) if enough hardware resources are made available.

On the other hand, there are certainly times when performance troubleshooting is required – either because SQL Server’s response got worse over time or because it’s just not at the level where it needs to be to keep users happy.

With all these system components fighting for limited hardware and infrastructure resources how is a DBA supposed to troubleshoot performance problems? When angry users are on the phone or when your boss is looking over your shoulder demanding quick results – that is hardly the time to come up to speed on the finer points of waits statistics analysis.

Below is a general strategy with some specific tips and tools to guide you on the road to a better SQL Server performance.

1. Understand the environment and see how SQL Server is configured (the sanity check)

This first step is very important especially when you have to troubleshoot an environment you’re not familiar with (for example when you’re a performance consultant) or when you work at a company where many people can make changes to the SQL Server environment and you’re not really sure if what was true last month about the configuration is still true today. It doesn’t make any sense to spend time looking for bad queries only to discover eventually that somebody made changes to the SQL Server memory configuration, confused megabytes with kilobytes and now SQL Server is running with much less memory available for its own use.

Here are some scripts to help in this discovery phase:

Brent Ozar and company have an awesome set of scripts in their “First Responder Kit” that’s now available on GitHub:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

The main one that you want to get familiar with is sp_Blitz but certainly look over the other ones as well. sp_Blitz performs various checks in configuration settings and it will alert you when it discovers anything that’s not considered a best practice.

The other set of very useful diagnostic scripts are those provided by Glenn Berry:

http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

He usually updates them every month and targets specific features in the various SQL Server versions available.

If all looks good with the environment and configuration then it’s time to move to the next step.

2. What is SQL Server doing right now?

There are probably many commercial tools that offer all sorts of monitoring capabilities but the one free tool that every SQL Server DBA should be familiar with is sp_WhoIsActive (this is my ‘don’t leave SQL Server without it’ tool):

http://whoisactive.com/

It is an awesome stored procedure by Adam Machanic that shows all sorts of relevant data about what exactly is going on at the transaction level when the stored procedure is executed. It has tons of flags and options – all meant to customize the output in order to facilitate a performance troubleshooting session.

Adam wrote a series of blog posts explaining all its various features – it’s well worth reading these in advance so you’re familiar with them before you need to use them under pressure:

http://sqlblog.com/blogs/adam_machanic/archive/tags/month+of+monitoring/default.aspx

It’s possible that when you’re looking strictly at what’s going on in the present you may not be able to determine why SQL Server is hurting. In that case you’ll probably need to go to the next step:

3. What is SQL Server waiting on during a certain time interval? (wait statistics analysis)

SQL Server is a complex system but it’s also a pretty good patient – it keeps detailed track of many vital statistics but you need to know where to look for that data. SQL queries, until they finish their execution, go through many different kinds of waits: waiting for CPU resources, waiting for data to be moved to memory from disk, waiting for data that other queries have locks on and so on. An analysis of all these waits over time can offer critical insight into areas where SQL Server is spending time waiting instead of getting those queries processed. Below are some resources that should help you with wait stats analysis:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ – Wait statistics, or please tell me where it hurts (this is the classic Paul Randal article on the topic of wait stats)

Be sure to check out his other articles on this topic:

http://www.sqlskills.com/blogs/paul/category/wait-stats/

Paul Randal recently created an online waits library documenting the various wait types seen in SQL Server:

https://www.sqlskills.com/help/waits/

Brent Ozar also has a very good article & script on wait stats as part of the “First Responder Kit”:

https://www.brentozar.com/responder/triage-wait-stats-in-sql-server/

4. Query Store

If you’re fortunate enough to work with SQL Server 2016 or Azure SQL Database then you certainly need to become familiar with the Query Store:

https://msdn.microsoft.com/en-us/library/dn817826.aspx – Monitoring Performance By Using the Query Store

This is SQL Server’s new black box. It’s what will enable you to understand query performance problems in the recent past when you were not directly monitoring SQL Server. And certainly make sure Query Store is enabled and recording – otherwise it won’t do you much good 😉

Additional resources

https://www.youtube.com/watch?v=rf17jQRcfjI – The Server is Down What will you do

https://www.red-gate.com/library/troubleshooting-sql-server-a-guide-for-accidental-dbas – Troubleshooting SQL Server: A Guide for Accidental DBAs (free ebook)

https://www.red-gate.com/library/performance-tuning-with-sql-server-dynamic-management-views – Performance Tuning With SQL Server Dynamic Management Views (free ebook)

4 tell-tale signs of Entity Framework performance problems (for SQL Server DBAs)

Here’s the scenario: you are a SQL Server DBA and you manage at least one database server that’s used by custom applications created by in-house developers using Entity Framework. From your experience you believe that the database server is powerful enough (hardware-wise) to handle the applications using it but the fact is that the database server is actually struggling to keep up. You often see high CPU usage, high network traffic and a much larger volume of queries than you’d expect from the applications using it. Management wants to know what the problem is: is the server not powerful enough from a hardware point of view? Is it mis-configured? Is the application not properly coded?

You have a suspicion that the application code is not as tight as it could be but you’re not really a .NET developer and you don’t really know much about Entity Framework. What would you look for from the SQL Server side in order to make recommendations to management (and the development team maybe) about what steps to take to improve performance?

If your first instinct is to say that Entity Framework is evil, that all ORM (object-relational mapping) tools should be banned and that your databases can only be queried using stored procedures … then you’re probably on the wrong path and you’re not likely to make many friends with that approach.

Entity Framework is a tool – a very powerful tool I would say – in the .NET development stack. It enables developers to write applications faster by focusing on business logic and business models instead of having to worry about the low-level plumbing necessary to get data in and out of a database. The problem with Entity Framework (when it comes to performance) is that often it hides the database layer so well that developers forget that their various object manipulations end up generating all sorts of queries against the database – queries that they would probably be a lot more careful with if they actually had to write them from scratch in SQL code.

As is the case with most powerful tools Entity Framework has quite a set of instructions and best practices that must be followed in order to get the best performance out of it. The best single-page collection of Entity Framework performance tips (in my opinion) can be found at the location below:

Performance Considerations for Entity Framework 4, 5, and 6
https://msdn.microsoft.com/en-us/data/hh949853.aspx

That page is full of information and it can be quite intimidating. Much of it though applies to best-practices that belong in the .NET layer and would not directly be visible to a SQL Server DBA. If you want to see from the database side if best-practices were followed what are some of the tell-tale signs you should look for?

Some observations before we get started:

  • I mentioned that these tips would be for SQL Server DBAs because Entity Framework apps usually use SQL Server as a database backend. The truth is that probably most of these also apply to applications that use Entity Framework with other types of databases.
  • As any good IT troubleshooter will know one trick to solving IT problems is to try to isolate the actual issue from background noise. In the context of what we’re trying to do here you need to be able to run the offending application in a way that you can easily study its SQL Server activity – either against a SQL Server where you’re the sole active user or maybe against a staging system with low activity. You would then proceed to use the application through its UI and record its SQL activity for later analysis using tools such as SQL Server Profiler, Extended Events or any other third-party tool you prefer.

What should you then look for?

SELECT * FROM TABLE_NAME

Entity Framework will not exactly issue SELECT * FROM commands – what it will do though is have explicit SELECT statements that include ALL columns in a particular table. If you see that most SQL queries are selecting all columns this way (especially from large tables when it appears that the UI is not using all that data) then you know that developers got a little sloppy with their code. It’s very easy in Entity Framework to bring back all columns – it takes more work and thought to build a custom LINQ projection query to select only the needed columns.

The famous N+1 problem

Here’s how to spot this pattern: say that in the UI you go to a page that shows a grid of data with 100 rows. You would typically expect the application to be able to return all that grid data with a single query. Instead, from the SQL activity you captured you see an initial query that gets ‘most’ of the data and then … surprise … 100 other queries that all look very similar (usually with some different values in the WHERE clause).

In Entity Framework it’s very easy to fall into this trap that deals with loading related data. By default Entity Framework in an application has a setting called Lazy Loading set to true – you’re probably guessing already what this does. Let’s say you have one query where you request data for the grid in our example. If you display fields related to that data that were not directly included in the main query Entity Framework (out of the goodness of its heart) will just quietly go and grab the additional data from the database on a need-to-have basis. As the code loops over the various rows being displayed you’ll see additional queries being executed for each row. For N rows of data we then end up with N + 1 queries (or possibly even worse). This is mentioned in section ‘8 Loading Related Entities’ on the page above. The solution is to use Eager Loading (and possibly disable Lazy Loading) as a way to be very explicit about the data that is needed for display at the time when the original query is executed.

Implicit type conversions and unused indexes in queries against VARCHAR fields

This one might be harder to detect but in the ‘perfect storm’ kind of scenarios it can bring a server to its knees with high CPU usage and heavy IO activity. Here’s what’s going on: let’s say that you see queries that in the WHERE clause search against a VARCHAR column in a table (for example a GUID that’s used as a primary key). Nothing really wrong with that – the table has an index on that VARCHAR column so all should be well … except that it’s not. Simple queries from Entity Framework against that table take much longer than expected. You look at the query plan and you see that the index on that column is not being used and that SQL Server is warning you that an implicit type conversion took place.

What exactly is going on there? As you look at the original query more carefully you see that Entity Framework is passing that parameter as a NVARCHAR value. SQL Server must convert it to match the data type in the table and in the process it will not make use of that index. The problem comes from the fact that in Entity Framework all string fields by default are considered to be NVARCHAR types when queries are created. If you have VARCHAR fields in the database then the developers must specifically declare the column types in the Entity Framework data model as VARCHAR. This way Entity Framework will use the proper data types when running the query and all will be well again.

Missing caching layer for metadata / lookup tables

This is not exactly an Entity Framework problem but rather an application architecture issue that’s easy to overlook when using an ORM tool. Let’s say that you have data in the database that does not change often – such as the list of US states. Once the application gets it once it should hold on to it and use it from some sort of memory cache layer whenever it needs it. Instead, you see that queries are being made against such tables all over the place. The fastest query to execute is the one that does not need to execute at all. Developers should implement some sort of caching layer in the application and store such data locally next to the application for long term use.

So there you have it. I’ve outlined here a few usage patterns along with a very detailed page to help you, as a SQL Server DBA, determine if the Entity Framework application hitting one of your databases is probably lacking some of the performance best-practices mentioned above. Sometimes just throwing more hardware at the problem might be cheaper in the short term but as it usually happens bad application code combined with enough user activity will eventually bring even powerful servers to their knees.