Latest News
1

Attaching databases via a dockerfile

~

There’s been an update posted about this topic here-

https://dbafromthecold.com/2018/12/11/attaching-databases-via-a-dockerfile-update/

Last week I presented my session on SQL Server & Containers for the PASS Virtualization Group and during my prep I noticed that there’s some functionality available to Windows containers and not Linux containers.

One of the (if not the) main benefits of working with SQL in a container is that you can create a custom image to build container from that has all of your development databases available as soon as the container comes online.

This is really simple to do with Windows containers. Say I want to attach DatabaseA that has one data file (DatabaseA.mdf) and a log file (DatabaseA_log.ldf): –

ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\\SQLServer\\DatabaseA.mdf','C:\\SQLServer\\DatabaseA_log.ldf']}]"



Nice and simple! One line of code and any containers spun up from the image this dockerfile creates will have DatabaseA ready to go.


However this functionality is not available when working with Linux containers. Currently you cannot use an environment variable to attach a database to a SQL instance running in a Linux container.


This was a problem for me as I wanted to change things up a little for the Virtualization Group’s webinar. I wanted to show all the code in my slides running on Windows Server but do my demos on my Windows 10 desktop but working with Linux containers. I wanted to do this as I thought it would be cool to show how you can work with SQL on Linux from Windows.


I started doing some research online and there are different work arounds to attaching the database into SQL in a Linux container but they all involved separate scripts outside of the dockerfile. I wanted to keep things simple, only show minor changes from Windows containers so I had to get a bit creative.


Here’s what I came up with: –


HEALTHCHECK --interval=10s  \
	CMD /opt/mssql-tools/bin/sqlcmd -S . -U sa -P Testing11@@ \
		-Q "CREATE DATABASE [DatabaseA] ON (FILENAME = '/var/opt/sqlserver/DatabaseA.mdf'),(FILENAME = '/var/opt/sqlserver/DatabaseA_log.ldf') FOR ATTACH"



EDIT – 2018-12-11 – Finally came back to this and blogged about attaching databases via a script here


A bit more involved but it performs the same functions as the attach_dbs environment variable in the dockerfile for Windows containers. Here’s what each part of the code does: –


# Instruct docker to wait for 10 seconds (to allow SQL to initialise) and then perform a check to ensure the container is running as expected
HEALTHCHECK --interval=10s

# Use sqlcmd to connect to the SQL instance within the container
CMD /opt/mssql-tools/bin/sqlcmd -S . -U sa -P Testing11@@

# Runs a SQL script to attach the database
-Q "CREATE DATABASE [DatabaseA] ON (FILENAME = '/var/opt/sqlserver/DatabaseA.mdf'),(FILENAME = '/var/opt/sqlserver/DatabaseA_log.ldf') FOR ATTACH"




So that’s how you can get the same result, an image in which you can create containers with DatabaseA available on startup, whether you are working with Linux or Windows containers by running: –


docker build -t demoimage <pathtodockerfilelocation>



If you want to see the full dockefiles, I’ve made both the Windows and Linux versions that I use for my demos available on my GitHub here.


Thanks for reading!
0

Monday Coffee: Time Off

~

Nice to be back after a week off. I’ve been writing three posts a week (sometimes more) since the beginning of the year so decided last week that I’d take a (albeit short) break.

It got me thinking about actual time off and how different people deal with taking a break from work. I have to admit, going on leave for me end up with me working more in the lead up to it in order to get everything done before I go and then working more to catch up once I’m back.

It shouldn’t be that way, should it?

I know I’m not the only one who this happens to and I don’t really have an answer for it. I don’t think I could just drop everything before going on leave but maybe there’s an answer to playing catch up when I’m back.

I once saw a senior manager in a previous company come back from leave, highlight all his unread emails and, promptly delete them. When I asked him about it (I was standing at his desk when he did it) he said that anything that was that important would be sent again.

I like that approach as he’s exactly right. We shouldn’t be expected to trawl through (sometimes hundreds) of emails once we’re back from holiday.

Ahh, what am I saying. There’s no way I’d do that, I’ve have kept up-to-date with my email whilst on holiday 🙂

Have a good week!

0

Friday Reading 2017-08-04

~

It’s a bank holiday weekend here in Ireland so I’m looking forward to having the extra day off. Bought a new computer this week so am going to spend a bit of time setting that up (one of my favourite things to do).

One thing to mention is that next week (on the 9th @ 17:00 GMT) I’ll be presenting my SQL Server & Containers session for the Pass Virtualisation Virtual Chapter. Really looking forward to it, you can find out more details here.

Anyway, this week I’ve been reading…

Kubernetes Interactive Tutorials
James Anderson (b|t) recommended this to me a while back but I just haven’t had the chance to look at it. The tutorials are a really good way of getting your toe in the Kubernetes water.

SQL Server 2017 RC2 Now Available
MS Official SQL Server Blog announcing SQL Server 2017 RC2.

Upgrading SQL Server–Day 1
First part of Glenn Berry’s blog series on upgrading and migrating to SQL Server 2016/2017.

Sudo in Powershell
Here’s a module that replicates sudo functionality in Powershell (warning, I haven’t had a chance to fully test this yet).

Jurassic Park: 10 things you might have missed
Fun Den Of Geek article to round the week off.

Have a good weekend!

0

Automating installation of Docker & SQL command line tools on Linux

~

I’ve been getting to grips with Docker SQL Containers on Linux (specifically Ubuntu 16.04) and have found that I’ve been running the same commands over and over when I’m configuring a new server.

The old adage goes that if you run anything more than once it should be automated, right?

So I’ve created a repository on GitHub that pulls together the code from Docker to install the Community Edition and the code from Microsoft to install the SQL command line tools.

The steps it performs are: –

  • Installs the Docker Community Edition
  • Installs the SQL Server command line tools
  • Pulls the latest SQL Server on Linux image from the Docker Hub

To run this yourself, first clone a copy of the repository onto the server: –

git clone https://github.com/dbafromthecold/InstallDockerOnUbuntu.git

Then navigate to the directory: –

cd InstallDockerOnUbuntu

Make the script executable: –

chmod +x installdocker.sh

Then run the script!

./installdocker.sh

N.B. – This is setup for Ubuntu 16.04 so it will not work on other distros

Contact me @dbafromthecold on twitter or email dbafromthecold@gmail.com if you have any issues or have any improvements to the script 🙂

Thanks for reading!

1

SELECT…INTO in SQL Server 2017

~

One of the new options available in SQL Server 2017 is the ability to specify a filegroup when using SELECT..INTO to create a table.

Previous versions would create the new table on the PRIMARY filegroup which isn’t ideal so this is a pretty nifty option imho. Let’s run through a quick demo for which I’m going to restore the AdventureWorks database that’s available here.

The first thing to do once the database has been restored is to set the database to the SQL 2017 compatibility level:-

USE [master]
GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 140
GO

Then I’m going to add a new filegroup to the database (so that I can create my new table on it): –

USE [master];
GO

ALTER DATABASE [AdventureWorks] ADD FILEGROUP [TempData]
GO

ALTER DATABASE [AdventureWorks] ADD FILE 
(	NAME = N'AdventureWorks_TempData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks_TempData.ndf' , 
	SIZE = 8192KB , 
	FILEGROWTH = 65536KB ) TO FILEGROUP [TempData]
GO

Now I can run the SELECT…INTO statement using the new ON FILEGROUP option. I’m going to run an example SELECT statement to capture Sales in the UK: –

USE [AdventureWorks];
GO

SELECT c.CustomerID, c.AccountNumber, p.FirstName, p.LastName, p.EmailAddress

	INTO dbo.CollectedData ON TempData

FROM Sales.Customer c
INNER JOIN Sales.CustomerPII p ON c.CustomerID = p.CustomerID
INNER JOIN Sales.SalesTerritory t ON p.TerritoryID = t.TerritoryID
WHERE t.Name = 'United Kingdom';
GO

Once that has completed I can check that the new table is on the filegroup that I specified by running: –

SELECT f.name AS [Filegroup]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.name = 'CollectedData'
GO

Pretty cool huh? So what’s the benefits of this?

Well, this allows us to have a separate filegroup for all user created tables (in this manner). The files behind that filegroup could be on a separate drive allowing you to separate the IO of these processes away from the day-to-day database operations.

What it comes down to is that it gives us more flexibility when working with data and that’s a good thing, right?

Finally, I did have a go a specifying a memory optimised filegroup but unfortunately it’s not supported.

Now that would be really useful as it would be blazingly fast. Hopefully something for a future version?

Thanks for reading!