0

Friday Reading 2017-04-28

Nearly May! Although it’s been snowing this week in Dublin so summer still appears to be a while off…

Community driven Enhancements in SQL Server 2017
The MS Tiger Team list the new features in SQL 2017 that have been built based on feedback from the community

SQL Server 2017 – Adaptive Query Processing
Joe Sack takes us through a new (and really cool imho) feature in SQL 2017

SQL Community Scripts
Shane O’Neill has put together a handy list of all the scripts available out there

Why Did Your Availability Group Creation Fail?
Anthony Nocentino takes us through an error that he encountered when setting up an Availability Group (I like posts about real world issues and resolutions)

Have a good weekend!

1

Pausing index rebuilds in SQL Server 2017

Last week Microsoft confirmed that the name of SQL vNext will indeed be SQL Server 2017. I was looking through the documentation for more info on Adaptive Query Processing as I wanted to write a post on adaptive joins but then I saw: –

To see the new Adaptive Join operator in Graphical Showplan, a new version of SQL Server Management Studio is required and will be released shortly.

Source

Ok, I’ll wait! So I went back to the list of new features in SQL 2017 and something else caught my eye. The ability to pause and then resume online index rebuilds.

Sounds pretty cool, let’s see it in action. Here’s the setup: –

(SQL Server 2017 can be downloaded from here btw)

USE [master];
GO

CREATE DATABASE [Test]
GO

ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 140
GO

That’s the database created and put into the correct compatibility level (just to be sure). Now let’s create a table to test with: –

USE [Test];
GO

CREATE TABLE [TestTable]
(PKID INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 ColC VARCHAR(10),
 ColD DATETIME)


SET NOCOUNT ON;
INSERT INTO [TestTable]
(ColA,ColB,ColC,ColD)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE())
GO 100000

OK, now let’s rebuild the clustered index that we’ve created on the table: –

ALTER INDEX [PK_Test] ON [TestTable] REBUILD WITH (ONLINE=ON,RESUMABLE=ON);
GO

N.B. – notice the new option RESUMABLE=ON

Whilst that’s running, open a new connection and run: –

USE [Test];
GO

ALTER INDEX [PK_Test] ON [TestTable] PAUSE;
GO

The session that running the rebuild should now have stopped with the rather disconcerting error: –

Looks pretty worrying imho! But not to stress, jump back to your other connection and run: –

ALTER INDEX [PK_Test] ON [TestTable] RESUME;
GO

Hmm, but how do I tell that this has worked? Well, if you have sp_whoisactive on your instance you can verify that the query is re-running: –

Pretty cool, huh? Full information on this can be found here: – https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

I think this is very useful but we do need to be careful. The documentation says that pausing an online index rebuild for a long time may affect query performance and disk utilisation. This is due to the newly rebuild index being created side-by-side to the original one so we’ll need to watch out for that.

Thanks for reading!

0

Monday Coffee 2017-04-24

Back after a couple of busy weeks, kinda looking forward to getting back into the old work routine (ah, well maybe 🙂 )

Last week was a big week, I had my first article published on SQLShack.com and there were a couple of small events going on called Docker Con 2017 and Microsoft Data Amp.

Docker announced that all the open-source projects are being consolidated under one name Moby. Not too sure what this means for the future of the project as Docker (the company) now want to focus on Enterprise (paid-for) solutions. Hopefully this is a good thing but time will tell.

The biggest announcement from Microsoft was that we now know that the next version of SQL Server will be called SQL Server 2017. Lots of new features to get stuck into but the one that caught my eye was Adaptive Query Processing.

There’s an excellent video here in which Joe Sack takes us through what AQP is and how it works. I definitely recommend that you give the video a watch.

I jokingly said on twitter that this feature is liable to put me out of a job. Obviously I don’t think that, the way I look at features like these is that they are another tool in the belt of the DBA. It will allow us to be freed up from routine tasks and focus on more in-depth work.

Plus, it’s not going to get things right all the time, is it?

Have a good week!

0

Thursday Reading 2017-04-13

Morning all, it’s been a busy week so I haven’t had a chance to write a post. Instead I’ve seen some great posts written about SQLBits 2017 so I thought I’d link to three of my favourites:-

Why Volunteer at SQLBits ?
Rob talks about his experiences volunteering at SQLBits and how you can get involved. The photos are hilarious!

Attending SQLBits for the first time
Great video by Adam Sexton of pretty much everything that went on at SQLBits!

State of the SQL Nation and the Microsoft Engineering Model
Victoria Holt talks about the points mentioned at the 15 mins Q & A session with Conor Cunningham and Simon Sabin at SQLBits this year

Have a good weekend!

0

Monday Coffee 2017-04-10

So SQLBits is over and I have to say that I really enjoyed attending on the Saturday, hopefully next year I’ll be able to go for the whole event.

Highlights for me included chatting to the RedGate people about their new product SQL Clone, attending Denny Cherry’s “What not to do with SQL Server” session and of course, presenting my session.

I’m still pretty new to presenting and SQLBits is a little different to SQL Saturdays! Having a microphone and a light shining on you is a bit distracting at first but I found that 10 minutes into my presentation I didn’t notice anymore.

Overall I thought my session went well, I covered everything that I wanted to and had some time at the end which allowed the attendees to ask a few questions, most of which I managed to answer (reasonably well 🙂 )

One thing I did notice, about half way through my session, was that I was enjoying it. Previously when I’ve spoken I’ve been pretty nervous so have kinda had the mentality of…let’s just get this done…so I hope the fact that I enjoyed it came across.

So all in all, pretty chuffed about how it went and I can’t wait to do more.

Have a good week!

0

Friday Reading 2017-04-07

As this is being published I’m on my way up to Dublin airport to head on over to the UK for SQLBits. I’ve never been before and it’s always being mentioned as people’s favourite SQL event so I’m really looking forward to it.

Anyway, this week I’ve been reading…

How to Talk Yourself out of your New Year’s Blogging Resolution… One Day At A Time
Tongue in cheek article about getting started blogging. There’s one great line… “One of the best ways to learn more is to risk being accidentally stupid on the internet.”

Among Giants
Monica Rathbun talks about how she became an MVP and what you can do for someone you think deserves to be awarded.

Is this database being used?
Nice post on the various different ways to see if a database is being used

Inside the Hunt for Russia’s Most Notorious Hacker
Wired’s article on the investigation into a notorious hacker group. Very interesting.

Have a good weekend and if you’re at SQLBits, I’m presenting on Saturday morning in Dome 8 @ 09:30, come and say hello! 🙂

2

Connecting a powershell session to a container

One of the questions that I was asked at SQL Saturday Iceland was “how can I view the filesystem within a container?”.

This is a great question as one of the things that people find off-putting about containers is their opaqueness. It’s not obvious where everything lives within the container or how we can view the files within it.

Thankfully there’s a simple docker command that allows us to open a powershell session within a container, that command is docker exec.

Let’s run through an example. Firstly I’m going to spin up a new container running an instance of SQL Server: –

docker run -d -p 15777:1433 --env ACCEPT_EULA=Y --env sa_password=Testing11@@ --name testcontainer microsoft/mssql-server-windows

Then verify that the container is running: –

docker ps

Now, to open up the powershell session inside run: –

docker exec -it testcontainer powershell

N.B. – the -i flag keeps the powershell session open, otherwise it will immediately exit. The -t flag creates a pseudo terminal, you don’t have to include it tbh but the examples I’ve seen online always include it.

Ok, we know have a powershell session within the container and we can execute commands as normal, for example: –

get-psdrive

This will allow us to explore what the filesystem is within the container and run powershell commands as needed. In the screenshot above it’s interesting to see the space available/used on the C: drive. Have a dig around within your containers and let me know if you find anything interesting 🙂

Thanks for reading!

0

Monday Coffee 2017-04-03

Last week Pass announced changes to how speakers are going to be selected for their annual conference (you can read the announcement here.

There’s been a bit of a debate about the changes but as far as I can tell, these seem to be for the better (right?).

Inviting speakers with a proven track record of delivering high-quality presentations can only be a good thing and guaranteeing that a (albeit) small number of new speakers will be there will hopefully prevent presenting at Pass Summit becoming only available to a certain circle of people.

I’ve only been to the event once and I have to say, I found quite a few of the sessions there to be below-par. Don’t get me wrong, I also saw a few great sessions but if these changes improve the overall quality then I say that they are a good thing. Of course, it’s all about the execution so only time will tell if these make the expected improvements but I think Pass are doing this right.

They’ve asked for feedback here and have organised two online meetings so that we, as the community, have the chance to speak directly to them and voice any concerns that we may have.

Have a good week.

0

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!

1

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.