SQLGeordie's Blog

Helping the SQL Server community……where i can!

Docker and SQL Server on Linux series — June 20, 2018

Docker and SQL Server on Linux series

DockerBuildShipRunAnywhere

I’ve been playing around with Docker (more specifically with SQL Server on Linux) the beginning of 2017 to see if indeed I can “Build, Ship, Run, Any App Anywhere” and will be putting together a series of how-to’s and issues experienced so you guys don’t spend the countless hours trying to investigate and work out what is going on when things do not work.

I’ll be adding links to this main page as and when new posts are added as a central point of reference.

 

 

Advertisements
Speaking — July 3, 2018

Speaking

Upcoming and previous speaking engagements. Links to slides and demo’s can always be found on github or youtube. If you would like me to speak at your event whether in person or remotely then please contact me.

 

Upcoming Speaking Engagements

October 8th 2018: SQLSaturday #782 Slovenia 2018 (Ljubljana)
Slovenia (Ljubljana)
??

Previous Speaking Engagements

October 8th 2018: SQLRelay (Newcastle)
England (Newcastle)
AWS Glue – Let’s get “stuck” in!

October 2nd 2018: Introduction to Containers (One off for those who missed Data n’ Gravy)
England (Leeds)
Introduction to Containers

September 18th 2018: SQLNorthEast UserGroup
England (Newcastle)
AWS Glue – Let’s get “stuck” in!

September 14th 2018: SQLGLA 2018
Scotland (Glasgow)
AWS Glue – Let’s get “stuck” in!

September 6th 2018: Data Platform User Group (Leeds)
England (Leeds)
AWS Glue – Let’s get “stuck” in!

September 5th 2018: PASS Manchester SQL Server User Group
England (Manchester)
AWS Glue – Let’s get “stuck” in!

August 1st 2018: Introduction to Containers (One off for those who missed Data n’ Gravy)
England (Leeds)
Introduction to Containers

June 22nd 2018: SQLGrillen 2018
Germany (Lingen)
Introduction to Containers

June 12th 2018: Edinburgh Data Platform
Scotland (Edinburgh)
Introduction to Containers

April 28th 2018: Data n Gravy
England (Leeds)
Introduction to Containers

April 19th 2018: Glasgow SQL User Group
Scotland (Glasgow)
Introduction to Containers

November 30th 2017: SQLNorthEast User Group
England (Newcastle)
Introduction to Containers

 

Merge image layers in Docker using –squash — June 20, 2018

Merge image layers in Docker using –squash

A question which always gets asked regarding the union layered file system for images is whether the intermediary image layers can be merged into one? The answer is yes, you can –squash image layers during a build from a Dockerfile.

The attached video shows 2 example builds of the same Dockerfile, one is standard, the second using –squash to merge the layers.

Merge image layers in Docker

Example run script:

docker build -t NewImageNameHere . --squash

To view the image layers related to the newly created image you can run:

docker history NewImageNameHere

This will show all the layers created from each instruction in the dockerfile but the Imageid is “missing” (only 1 layer / Imageid now exists) and there will be a comment to indicate the merge has occurred and only 1 image layer now exists:

Output Without –squash

Output from Without --squash

Output With –squash (and comment showing merge)

Output from With --squashComment showing merge information

Docker for Windows -SwitchDaemon easily — May 3, 2018

Docker for Windows -SwitchDaemon easily

If you are using Docker for Windows and want to switch between Linux or Windows containers you can do this by right clicking the Docker “Whale” in the systray and selecting “Switch to Windows containers”:

Switch to Windows containers

….but no one likes clicking around do they!

There is an alternative way to do this which I use in my docker session demo’s which makes things so much easier and the switch is a lot quicker! In powershell, run the script below and it’ll switch from Linux to Windows containers and vice versa:

Linux Containers:

LinuxContainerDaemon

Run the SwitchDaemon script:

& 'C:\Program Files\Docker\Docker\DockerCli.exe' -SwitchDaemon
Windows Containers:
WindowsContainerDaemon
and there you have it, a very simple but extremely useful snippet to switch between Linux and Windows containers,
Workaround for – The transaction log for database ‘master’ is full due to ‘NOTHING’ – Docker — April 24, 2018

Workaround for – The transaction log for database ‘master’ is full due to ‘NOTHING’ – Docker

As with anything open source you’re prone to things changing without you necessarily being aware and the error message in the blog title is something that has been sprung upon me whilst tidying up demo’s for my latest session on Docker and SQL Server On Linux. I hadn’t touched these particular demo’s since October 2017 so as to when things stopped working, I don’t know but they certainly worked the last time I did the session but doing a re-pull of the images, things broke.

With the micorosft/mssql-server-linux images, if you wish to mount a volume to a container then you will experience an error message I’ve never seen in SQL Server:

The transaction log for database ‘master’ is full due to ‘NOTHING’.

The transaction log for database 'master' is full due to 'NOTHING'

Apparently I’m not the only one experiencing this and there is an issue opened (Since October 2017!!!) on GitHub and social.msdn.microsoft.com describing the issue.

I have tested:

  • micorosft/mssql-server-linux:latest
  • micorosft/mssql-server-linux:2017-GA

and the issue was apparent on both images. The reason as highlighted on GitHub seems to be related to the filesystem, seeing as I’m running Docker for Windows I can’t change my file system from ext3 to ext4 like what others have done.

There is a workaround for this. The issue (certainly for me) only seems to be apparent if you try to mount a volume to a folder that already exists (ie. /var/opt/mssql/data/), mount to a new folder (ie. /sqlserver/data) then it will work:

Works:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword1' `
--cpus="2" `
--name SQLLinuxLocal1 -d -i `
-p 1433:1433 `
-v C:\Docker\SQLServer\Linux\SQLLinuxLocal:/sqlserver/data/ `
microsoft/mssql-server-linux:latest

Does not work:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword1' `
--cpus="2" `
--name SQLLinuxLocal2 -d -i `
-p 15106:1433 `
-v C:\Docker\SQLServer\Linux\SQLLinuxLocal:/var/opt/mssql/data/ `
microsoft/mssql-server-linux:latest

Or if you can, create a data volume instead and use that 😉

Once you have the files you need (ie. .bak file) in the /sqlserver/data folder within the container, you can then restore it using WITH MOVE to the /var/opt/mssql/data/ folder and this will work.

Introduction to Docker – Slides and Demo’s —
Docker – SQL on Linux image tag has changed! — October 4, 2017

Docker – SQL on Linux image tag has changed!

With the announcement of SQL Server 2017 last week came the updated microsoft/mssql-server-linux and microsoft/mssql-server-windows* docker images.

By default if you do a docker pull without a tag you will pull down the latest however with these new docker images this no longer works:

Docker pull latest.png

You get the error:

Error response from daemon: manifest for microsoft/mssql-server-linux:latest not found

I found this a bit strange so went onto the docker hub searching for microsoft/mssql images and see if they actually did exist:

Docker images on msft repo.PNG

SQL on Linux is what I was after and you can see from the image above it does exist but taking a close look at the tags tab you can see that they have now amended the tags to be microsoft/mssql-server-linux:2017-GA and microsoft/mssql-server-linux:2017-latest.

SQLonLinux-tags
The new tags

 

SQLonLinux-tags 2
This kind of tells us things have changed!

 

NOTE: The windows docker images still have the “latest” tag available

SQLonWIndows-tags

Now, if we run docker pull with the 2017-GA or 2017-latest tag it will grab it:

docker pull microsoft/mssql-server-linux:2017-GA

Docker pull 2017-GA.png

Obviously I have already done this and the image is up to date 🙂

 

Time to update all my demo scripts 😦

Docker and SQL Server – Useful links — July 23, 2017

Docker and SQL Server – Useful links

Docker

Below are a collection of links I’ve used as reference throughout my journey of Docker and running SQL Server containers. These have been invaluable in knowledge gathering and troubleshooting throughout this (at times) painstaking process.

First and foremost I would like to put a huge shout out to Andrew Pruski (b|t) for his Container Series which if you haven’t had a look at then I suggest you do, like now! He has put together some great content for anyone looking to get started with Containers.

I have tried to section them to make things easier to refer to and more than happy for others to suggest others to add:

Getting Started with Docker:

Tutorials

SQL Server on Linux:

Use cases:

General:

Licensing:

Installing:

Setup on Windows 10 VM:

SQL Server On Linux:

Connecting to SQL Server via sqlcmd:

Legacy SQL Server Containers

Performance

Issues and Troubleshooting

Using Volumes

Hyper-V containers

Videos

Delayed Durability in the wild… — November 14, 2016

Delayed Durability in the wild…

Background

We have recently been working on large data migration project for one of our clients and thought I would share how Delayed Durability helped us overcome a performance issue when the solution was moved to the client’s Development domain.

I won’t go into details of the project or the finer detail of our proposed solution as I have plans to put some more content together for that but in short the migration of the data was to be run by a (large) number of BIML generated SSIS (Child) packages for each table to be migrated, derived from a meta data driven framework with each stage being run by a master package, all of which run by a MasterOfMaster packages.

To maximize throughput, utilise as much processing power as possible, reduce the time it would take to run the migration and control the flow we built a series of sequence containers each running it’s own collection of Child Packages. We built the framework in such a way that these could be run in parallel or linear and each master package could contain as many containers (no pun intended) of child packages as required. This also allowed us to handle the order that packages were run in, especially those with dependencies whilst keeping the potential for parallelising (is that a word? No idea but I like it) the whole process as much as possible. Leaving the MaxConcurrentExecutables property to -1 mean’t we could push the processing to run up to 10 packages at once due to the VM having 8 cores (on Integration, 4 cores on Development) and this value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two.

An small example of how the MasterOfMaster and a Master Package for a stage looked is shown below:

Each container number could have Parallel and/or Linear Containers and both must succeed before the next Container level can start.

NOTE that this is just an example representation, naming conventions shown do not reflect the actual solution.

Problem

During development and initial testing on our own hardware, we had the migration at the time running at ~25minutes for around 600 packages (ie. tables) covering (what we termed) RawSource–>Source–>Staging which was well within the performance requirements for the stage that development was at and for what was initially set out. The rest of this blog post will hone in specifically on Source–>Staging only.

However, once we transferred the solution to the clients development environment things took a turn for the worse. In our environment we were running VMs with 8 cores, 16GB RAM and utlising SSDs. The client environment was running SQL Server 2016 Enterprise on VMWare vSphere 5.5, 8 vCPUs, 32GB RAM (for Integration, Development was half this) but the infrastructure team have done everything in their power to force all VMs onto the lower tier (ie. slow disks) of their 3-PAR SAN and throttle them in every way possible, just to make things more of a challenge. Even though the VM’s themselves were throttled we were confident that we wouldn’t see too much of a performance impact, especially as this was only a subset of the processing to be done so we needed it to be quick and it will only ever get longer and longer.

How wrong we were. On the first run the processing (for Source–>Staging) took around 141 minutes, yes you read that right, a full 116 minutes longer than the whole process took on our hardware! Wowza, didn’t see that one coming. I won’t delve too much into the investigations as again that will be saved for another blog post but essentially we were seeing a huge amount of the WRITELOG wait type since moving to the client environment. We believed the reason for this was due to the significant amount of parallel processing (running of SSIS packages in parallel loading to the same DB) we were doing and the SAN didn’t seem to be able to handle it. One other thing to note, due to truncations not being flagged as error’s in OLE DB Destination fast load data mode access, some of the packages that weren’t a direct copy where we knew the schema was exactly the same were run in non-fast load, ie row-by-row which puts additional stress on the system as a whole.

I will be blogging at a later date regarding how we managed to get everything running in fast load and handle the truncation via automated testing instead.

Solution

Enter Delayed Durability.

I won’t enter into too much detail regarding what this is or how it specifically works as this has been blogged by many others (Paul Randal, Aaron Bertrand to name just a couple) but my favourite description of delayed durability is comes from the msdn blogs and they refer to it as a “lazy commit“. Before you ask, yes we understood the issues of implementing such a change but the migration process was always a full drop and reload of the data so we didn’t care if we lost anything as we could simply run the process again.

Setting delayed durability at the database level we were able to control which Databases involved in the process we wished to have this without altering the BIML framework or code itself to handle it at the transaction level. By simply applying this to the Source and Staging databases we reduced the processing time from 141 minutes to 59 minutes. This wasn’t exactly perfect but shaving more than half the time off with one simple change and pushing the WRITELOG wait stat way way down the list was a great start.

As a side not, we have managed to get the processing from ~59mins to ~30mins without changing the VM/hardware configuration but I will leave that for another post.

Proof

When I first set out with this blog post it was only going to be a few paragraphs giving an insight into what we did however, I thought that all this would be pointless without some visualisation of the processing both before and after.

Row-by-Row with no Delayed Durability

We needed to get a baseline and where better to start than capturing the metrics through SentryOne and using Adam Mechanic’s spWhoIsActive we can see what I was talking about with the WRITELOG wait stat:

spwhoisactive_output

Granted the wait time themselves was relatively low, these were apparent almost every time we hit F5 and running our wait stat scripts in was in the top 3. A sample of the processing indicating this wait stat can also be seen below:

waitstats_writelog

As stated previously, overall the Source–>Staging process took 141 minutes and the overall processing from SentryOne PA was captured:

so_fullprocess_output

Row-by-Row with Delayed Durability

So when we ran the same process with Delayed Durability we can see straight away that the transactions/sec ramp up from ~7000 to ~12500. Top left shows without Delayed Durability, bottom left with Delayed Durability and right shows them side by side:

The overall process for Source–>Staging took only 59 minutes. I’ve tried to capture the before/after in the image below, the highlighted section being the process running with Delayed Durability forced:

SO_FullProcess_withDD_Output.png

You can see from this the drastic increase in Transactions/sec and reduction in Log Flushes.

Two package execution time examples (trust me that they are the same package) showing that with Delayed Durability the processing time was only 43% (166sec down to 72sec and 991sec to 424sec) ) of that without Delayed Durability set. Apologies for the poor image quality….

To me that is a huge reduction for such a simple change!

Conclusion

So should you go out and apply this to all your production databases right this second? No, of course you shouldn’t. We applied this change for to fix a very specific problem in an isolated environment and were willing to take the hit on losing data if the server crashed – are you, or more importantly your company willing to lose that data? I’m taking an educated guess that this will be a no but for certain situations and environments this configuration could prove to be very useful.

Links

Why would you never use SSIS Fast Load…? —

Why would you never use SSIS Fast Load…?

We all know that if you want SQL Server to push data into a table then you want to batch the inserts / use a bulk insert mechanism but is there a time when performance isn’t everything?

Background

Although it has its critics, SSIS is a very powerful tool for Extracting, Transforming and ultimately Loading data from and to various systems. I kind of have a love / hate relationship with SSIS, I love it but it seemingly hates me with a passion.

During a recent data migration project we had a series of packages using a stored procedure as the source and a SQL Server table as the destination. By using the OLE DB Destination task you have a series of options Data Access Modes which can provide various additional configurations. I won’t delve into all of these but have a look at the msdn link provided at the end for further information.

The ones I want to concentrate on are:

  • Table or view
  • Table or view – Fast Load

In short, fast load does exactly what it says on the tin, it loads data fast! This is because it is optimised for bulk inserts which we all know SQL Server thrives on, it isn’t too keen on this row-by-row lark.

Problem

Now, I won’t be providing performance figures showing the difference between running a package in fast load compared to row-by-row, this has been done to death and it is pretty much a given (in most cases) that fast load will out perform row-by-row.

What I do want to bring to your attention is the differences between the two when it comes to redirecting error rows, specifically rows that are truncated. One of the beauties of SSIS is the ability to output rows that fail to import through the error pipeline and push them into an error table for example. With fast load there is a downside to this, the whole batch will be output even if there is only 1 row that fails, there are ways to handle this and a tried and tested method is to push those rows into another OLE DB Destination where you can run them either in smaller batches and keep getting smaller or simply push that batch to run in row-by-row to eventually output the 1 error you want. Take a look at Marco Schreuder’s blog for how this can be done.

One of the issues we have exerienced in the past is that any truncation of a column’s data in fast load will not force the package to fail. What? So a package can succeed when in fact the data itself could potentially not be complete!?! Yes this is certainly the case, lets take a quick look with an example.

Truncation with Fast Load

Setup

I have provided a script to setup a table where we can test this. I will attempt through SSIS to insert data which is both below and above 5 characters in length and show the output.

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.TruncationTest;
DROP TABLE IF EXISTS dbo.TruncationTest_error;

CREATE TABLE dbo.TruncationTest
(
TruncationTestID INT IDENTITY(1,1),
TruncationTestDescription VARCHAR(5)
)
GO

CREATE TABLE dbo.TruncationTest_error
(
TruncationTestID INT,
TruncationTestDescription VARCHAR(1000) --Make sure we capture the full value
)
GO

This code will set up 2 tables, one for us to import into (TruncationTest) and another to capture any error rows that we will output (TruncationTest_error).

I set up a very quick and dirty SSIS package to run a simple select statement to output 3 rows and use the fast load data access mode:

SELECT  ('123') AS TruncationTestDescription UNION ALL
SELECT  ('12345') UNION ALL
SELECT  ('123456789');

The OLE DB Source Editor looks like this:

sourceeditor

the OLE DB Destination data access mode:

desteditor_fastload

Finally, this is how the package looks:

package_fastload

Note the truncation warning. This is easy to see when viewing a package in Visual Studio, not so easy to pick up when you are dynamically generating packages using BIML.

Let’s run it……

package_fastload_success

Great, 3 rows populated into the TruncationTest table, everything worked fine! So let’s check the data:

SELECT * FROM dbo.TruncationTest

results_1

Eh? What happened there???? Where’s my ‘6789’ gone from row 3???

From this example you can see that the package succeeds without error and it looks as though all rows have migrated entirely but by querying the data after the package has completed you can see that the description column has indeed been truncated.

Let’s try the same test but changing the Data Access Mode to non-fast load (ie. Row-By-Row)

Truncation with row-by-row

In this example you can see that the row with truncation is in fact pushed out to the error pipeline as you would hope and expect.

desteditor_nonfastload

package_nonfastload_success

We now have 3 rows being processed but one row pushing out to the error pipeline which is what we would expect and hope for.

Let’s take a look at the output:

SELECT * FROM dbo.TruncationTest ORDER BY TruncationTestID
SELECT TruncationTestDescription FROM TruncationTest_error

results_2

The results highlighted in red are those from the fast load, in green are the results from the row-by-row indicating that the error row was piped out to the error table.

Solution(?)

You have a few different options here:

  1. Not really care and push the data through in fast load and suffer the concequences
  2. Run in row-by-row and suffer the performance hit
  3. Amend the OLE DB Source Output to be the same length as the destination column and redirect error rows from there.
  4. Probably loads of others involving conditional splits, derived columns and/or script tasks
  5. Apply option #1 and make sure that relevant (automated or otherwise) testing is applied

During the recent data migration project we were involved in we chose option #5. The reasons for this are:

  1. We wanted to keep the BIML framework, the code and the relevant mappings as simplistic as possible
  2. Performance was vital….
  3. …..but more importantly was the validity of the data we were migrating

We already had a series of automated tests setup for each package we were running and table we were migrating and we had to add to this a series of additional automated tests to check that no data itself was being truncated.

NOTE: Option #4 was also a very valid choice for us but due to the nature of the mapping between source and destination this was not something that was easily viable to implement.

I will leave the how we implemented these test this for another blog post 🙂

Conclusion

Taking a look at the error redirect in the OLE DB Destination we can clearly see that Truncation is greyed out and no option is provided so I have to assume that it simply isn’t an option to configure it here.

errorredirect

I used to have a link to an article which mentions that truncation cannot be deemed an error in a bulk import operation via SSIS due to the mechanics of how it all works but for the life of me I cannot find it :(. I am hoping someone who reads this will be able to provide me with this but for now I will have to draw my own conclusions from this. The closest thing I can find is an answer from Koen Verbeeck (b|t) in an msdn forum question where he states:

The only thing you get is a warning when designing the package.

You get truncation errors when you try to put data longer than the column width in the data flow buffer, i.e. at the source or at transformations, but not at the destination apparently.

What I still don’t understand is why in tSQL you will get an error when trying to “bulk insert” (loose sense of the term……ie. using an INSERT….SELECT) data that will truncate data but SSIS does not. Hopefully someone far cleverer than me will be able to shed some light on this!

The idea behind this blog post was not to focus too much on the importance of testing any data that is moved from one place to another but I wanted to highlight how easy it is to believe that what you are migrating is all fine n dandy because the SSIS package told you so but in actual fact you could be losing some very very important data!!

You have been warned 😉

Links