Friday Reading 2017-03-31

SQLBits is only a week away! But before that I’ve been reading…

Backing up SQL Server on Linux using Ola Hallengrens Maintenance Solution
Rob goes through how to use Ola Hallengren’s scripts to back up databases in SQL Server on Linux (now that the agent is supported)

Setting the default backup directory for SQL Server on Linux
Slava Murygin shows us three steps to set the default backup directory for an instance of SQL Server running on Linux

Top 5 Questions about Basic Availability Groups
Pieter Vanhove goes through 5 common questions about AGs.

What the heck is a DTU?
Good post explaining what the performance metric for Azure SQL DB is

Have a good weekend!

Using SQL Client Aliases

SQLServerCentral.com recently published my case study on how my company implemented Windows Containers running SQL Server to streamline our QA setup process. If you haven’t seen it, it’s here.

One of the problems that we ran into when moving to using containers was how to get the applications to connect. Let me explain the situation.

The applications in our production environment use DNS CNAME aliases that reference the production SQL instance’s IP address. In our old QA environment, the applications and SQL instance lived on the same virtual server so the DNS aliases were overwritten by host file entries that would point to 127.0.0.1.

This caused us a problem when moving to containers as the containers were on a separate server listening on a custom tcp port. Port numbers cannot be specified in DNS aliases or host file entries and we couldn’t update the application string (one of the pre-requisites of the project) so we were pretty stuck until we realised that we could use SQL client aliases.

The client aliases would allow us to replicate the DNS aliases that we had in production and we wouldn’t have to change the app connection string.

Setting up aliases is simple enough if you have the SQL configuration manager installed. Simply navigate down to the SQL Native Client section within the config manager, right click and fill out the name, server IP and port number: –

N.B. – I always do both 64 and 32-bit sections to be safe

But we didn’t want to install the config manager on the QA servers and also, that’s a very manual process. So we scripted it out.

First thing to do was get the SQL Native Client installed on all the VMs. The way we did this was to install it on one and then create a VM template that all the other servers were built from. The SQL Native Client installer can be downloaded from here. (that’s for SQL 2012 btw as that’s the version of SQL that we worked with).

Once that was done we then ran a powershell script to create the aliases directly in the registry of the server. Here’s a sample of the script: –

# First of all create the registry keys to hold the aliases
New-Item -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop | Out-Null
New-Item -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop  | Out-Null

# Then create the aliases
$ServerName = "192.168.1.7";
$dbPort     = "10010";
$TCPAlias   = "DBMSSOCN," + $ServerName + "," + $dbPort

$Aliases = @("Alias1","Alias2")

foreach($Alias in $Aliases)
{
    New-ItemProperty -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
    New-ItemProperty -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
}

Obviously we wrapped the script up in a load of try…catch blocks to make sure that if the keys were already there then it would drop & re-create but the core of what it does is above. What the script above will do is create two aliases that map to server 192.168.1.7 on port 10010: –

This allowed us to move the apps from using local instances of SQL to using containers listening on a custom port on a remote server without having to change anything in the app configuration. This reduced our server setup time significantly and freed up resources on the app server. Also, we could control what container the apps were pointing to via the registry entries! Very handy.

Thanks for reading, let me know if you have any questions.

Monday Coffee 2017-03-27

Hey all, I’m back after attending SQL Saturday in Iceland. This week’s going to be busy!

I’ve said before that SQL Saturday Iceland was the first time that I presented a full hour session. It seemed to go well although having it start at 14:50 wasn’t the best as I was kinda nervous so I stressed out for most of the day! But done now and I was pretty pleased with it as I finished on time whilst getting all my material in and I answered all the questions.

I don’t really want to write an “Advice for first time presenters” post as there are plenty of them out there but I will say a couple of things that really helped me. First one, don’t wing it. I made notes on each of the slides that I had in my deck and worked through them one by one to make sure I got everything in that I wanted to say. I then grabbed a meeting room after hours in work and went through it over and over, making sure that it flowed nicely and that I got used to referring to slides whilst talking.

Also, my demos would have required a lot of typing and parts that required things like server bounces which would have taken too long to do live so I invested in decent video editing software and it paid for itself imho. The demos worked well and I was able to talk whilst they were playing, something that I don’t think that I’d have been able to do if I was doing everything live.

So all in all, really chuffed that I’ve done it and it’s now onto SQL Bits!

Final word, Iceland is an absolute amazing country. I hired a car and went driving round for a couple of days and the scenery is nothing sort of spectacular. My advice, if you have an chance, go.

Have a good week!

Friday Reading 2017-03-17

As this is being posted I’m on my way up to Dublin Airport to catch my flight to Iceland ready for SQL Saturday Iceland tomorrow! Been looking forward to this for a while now, my first opportunity to give a full session on a subject that I’ve become quite involved in, containers.

After the session I’m hanging around for a few days afterwards to explore, it’s going to be amazing but that does mean no posts next week 😦 (ha)

A bit of shameless self promotion first: –

Summary of my Container Series
I’ve added this because I’ve written a few posts about containers and thought it’d be handy to have one page that references them all.

Ding – The World’s Largest Mobile Top-up Network Streamlines QA with SQL Server Containers
SQLServerCentral.com has published a case study I wrote on how Ding.com implemented containers to improve their QA processes. This could be handy for anyone looking at doing the same

So anyway, this week I’ve been reading: –

Defending Invoke-SqlCmd
Should this cmdlet be used? Some think not, others…

The Biggest Danger to your Database: Me
Love articles like this! Everyone makes mistakes and it takes guts to post them online.

The Database Corruption Challenge
I’ve linked to this (lots of times) before but it’s really good practice for DBAs so want to keep putting it out there

DevOps Isn’t Perfect
DevOps is all the rage at the moment. Steve Jones talks about how the process should lead to fewer mistakes

Alright that’s it from me. Have a good weekend and I’ll see you in just over a week.

Summary of my Container Series

I’ve written a few articles on containers so I thought it’d be a good idea to have a page that listed them for easy reference. So here’s all the posts: –

Running your first container

Building a custom image

Pushing images to the Docker Hub

Using WinDocks to run earlier versions of SQL Server in Container

Creating SQL Containers from a Dockerfile

Viewing container logs

Remotely Administering the Docker Engine on Windows Server 2016

SQL Containers and Networking

Sharing Container Images

Connecting a powershell session to a container

Monday Coffee 2017-03-13

Less than a week to go until SQLSaturday Iceland now, it’s going to be a busy couple of months for me but am looking forward to it. I’ll get my slides and demos uploaded here at some point.

I’ve been debating whether or not to do demos live when I’m speaking. In the past I’ve everything live but certain presentations make that a little risky. I’ve done lightening talks where I’ve performed the demo then and there but that’s because I know that the risk of something going wrong is minimal. I’m working on a local instance of SQL and am not depending on anything else (e.g. – an internet connection).

It also depends on what the demo actually is, if it’s code based then running it live means that you can talk about the code but if the code is simple and the demo is more about the process, running it live really doesn’t add anything to the table.

For my session on containers, the demos will be videos. This is in part to reduce the risk of something going wrong but mainly it’s because the code is very simple, what’s happening when I’m running it is what I want to talk about. A video demo means that I don’t have to concentrate on typing the code (you really should never type in demos, copy & paste) and can talk about what’s happening.

Doing it this way will be beneficial as I can focus entirely on the audience and not be staring at my screen. I want to engage with the audience as much as possible and make the session as good as it can be.

Have a good week!

Friday Reading 2017-03-10

Just over a week to go until SQL Saturday Iceland! Really looking forward to presenting my session and then I have four days to explore!

Building the Terminator Vision HUD in HoloLens
Because why not?

The $650 SQL Saturday
Steve Jones talks about running a SQLSat event on a budget

Azure Marketplace
Can someone find the Windows Server 2016 Core image please?

xp_cmdshell – Naming, Shaming and Taming
Setting up a SQL Server Audit to capture xp_cmdshell usage

TEN BUCKS! TEN FREAKIN’ BUCKS! ZERO W AFTERMATH
Old news really but have you all got your Raspberry Pi Zero W?

Also – v 7.3.3 – Fix CIA Hacking Notepad++ Issue

Enjoy the rugby and have a good weekend!

Exporting Container Images

I’ve already covered how to push images to the Docker hub here but what if we only want to share a custom image in-house?

Well, it’s really simple to do by using the docker save and load commands. I’ll run through the process.

What I’ve done is created a custom image following the instructions here. Here’s my custom image: –

dockercustomimage

What I’m going to do now is save my custom image to a location [C:\temp] on my host server. So I’ll run: –

docker save -o myfirstimage.tar myfirstimage

N.B. – I’m saving the file as a .tar as this seems to be the format that works best for loading the image. I’ve tried exporting to other formats (.zip) but it always seems to become corrupt. The .tar format has always worked for me so I’ll stick with it.

Now the SQL images aren’t the smallest so this could take some time. But once it’s complete you’ll see the save .tar file: –

dockersavedimage

This can now be copied over to another server running the Docker engine and loaded into the local registry by running:-

docker load -i myfirstimage.tar

dockerloadimage

This will take some time but once it’s complete the exported image will be available in the server’s Docker registry. So that image was built on one server, exported and then loaded on another server. It’s an easy way to share images without having to push up to the Docker Hub.

By doing this you can ensure that every one of your team is using the same configured instance of SQL Server. Hopefully an end to “well it worked on my machine”…

Thanks for reading!

Monday Coffee 2017-03-06

Last week Docker announced the availablity of Docker Enterprise Edition. The existing version of Docker that I’ve been using has now been renamed to the Docker Community Edition.

So what does this mean for us Windows people hacking around with Docker on our local Windows 10 boxes? Very little it seems. The Community Edition will have access to the full Docker platform and (if needed) can be added to with paid-for addons from the Docker cloud. I haven’t had a chance to look over all the paid offerings but they seem to be mainly cloud based services, so I doubt I’ll need them (at the moment).

The Enterprise Edition is interesting as it raises the question, is Docker suitable for SQL Server in production environments? Now, I’m a big fan of Docker and have been using it extensively in my dev/test environments but I’m still not sure about production.

If you think about the advantages running SQL Server in containers brings; simple to setup, quick to deploy; are they relevant to production? I want to spend time setting up my 24/7 critical SQL Server instance, speed of build doesn’t really matter.

There may be other advantages that Docker Enterprise Edition brings so I’m definitely going to check it out but there’ll have to be something pretty good in there to convince me SQL Server containers are for production.

Have a good week!

Friday Reading 2017-03-03

Fun week, lot’s of things going on and throughout I’ve been reading…

Windows Server Premium Assurance and SQL Server Premium Assurance
Microsoft announce Premium Assurance, an additional six years of support

SQL VNext sp_configure on Windows and Linux with dbatools
Rob explores the SpConfigure commands in dbatools in SQL instances running on Windows and Linux

William Durkin – My first MVP Award
William Durkin thanks those who have helped him along the way to becoming a Data Platform MVP. Congrats William!

SQL Browser, what is it good for? Absolutely something!
Chris Sommer goes through what the SQL Browser service actually does

Announcing Docker Enterprise Edition
Docker have release a new version for business-critical deployments

Have a good weekend!