Saturday Reading 2016-12-17

It was my office Xmas party last night so whilst recovering on my settee, I’ll be reading:-

The Most Important Role of a SQL Server DBA
Back to basics, Angela Tidwell talks about what the most important task of a DBA is (can you guess?)

Don’t blink you might READPAST it
Cool post about the query hint READPAST

The ambiguity of the ORDER BY in SQL Server
Klaus Aschenbrenner talks about quirks with the ORDER BY clause

Evernote’s new privacy policies
So Evernote’s new privacy policies basically say that its employees can view your notes, yea…

Going to take a break over the holiday period so have a good Xmas and see you in the New Year.

Thanks for reading.

Presenting with SQL Server Management Studio

Short one this week as it’s the usual madness on the lead up to Xmas!

One of the cool things that was mentioned in the AMA that the SQL Server Team did back in November was a quick and easy way to setup SSMS for presenting.

N.B. – This only works with v17.0 which can be grabbed in the usual place.

Load up SSMS and hit Ctrl + Q to take you to the quick launch bar. Then type PresentON and that’s it! You’re setup for using SSMS whilst presenting!

So here’s a screen shot of SSMS with a simple query in it:-

presenting1

And this is the same query after running PresentON:-

presenting2

To reverse the process, simply type PresentOFF into the quick launch bar!

From what I can see, the main difference is that the text size has been taken up to 14 from 10, which may not be enough but it’s a start. Be pretty cool if, in a future version, you could customise this!

Of course, if you don’t have v17.0 (and I must admit, the part about it not being recommended for production use is a bit worrying) you can always follow Paul Randal’s guide on how manually setup SSMS for presenting and then save it as a template.

Monday Coffee 2016-12-12

Speaking and presenting

One of my biggest fears is public speaking and judging by this article, I’m not alone.

However it’s something that I’ve always wanted to do and as such this year I’ve done a few lightening talk sessions at various events. For those that don’t know, a lightening talk is a short (5 to 15 min) session on any subject that you are free to pick and they’re bundled up together so you’ll get 4/5 speakers within an hour.

They’re a great way to get into public speaking, the time goes incredibly fast and there’s no time for Q&A afterwards (usually). I would advise anyone looking to get into presenting to do a couple of these sessions first, just to get a feel for it. I’d also say that, as the session is so short, you should memorise your entire presentation. Practice it over and over so that you can do it in your sleep!

I’d also say that even though I’ve just said you need to memorise your presentation, don’t have a script that you need to stick to. If you plan what you’re going to say word for word and lose your way, you’ll get lost and find it difficult to recover. Instead have a general guide in your head that allows for things to be moved around a bit.

Now demos, if you’re going to do a demo, it needs to be flawless. There’s no time for you to be fixing things. There’s also no time for typing so have everything prepared to be run immediately. Time is short but I’ve seen a few great demos done in lightening talks!

Hopefully that’s a bit of advice that will help you out if you’re thinking of doing one of these talks.

I’m going to move onto some longer talks in 2017, I’ll post up any that I do and let you know how they go! Thanks for reading.

Friday Reading 2016-12-09

One week nearer to Xmas! Before I spend the weekend braving the centre of town desperately wondering what presents to get my family, I’ll be reading…

Mr Robot killed the Hollywood hacker
Article on MIT Tech Review about how hacking is depicted in Mr Robot.

Understanding Scan Count 0, Logical Reads N
Shane O’Neill details why the output of STATISTICS IO can sometimes show 0 scans but a non-zero value for logical reads.

Cassini sends back 1st images from new orbit around Saturn
CBC News article on the first images sent back by Cassini from Saturn

This is How I Work
Inspired by Adam Bertram’s post, Chrissy LeMaire shares details on her work setup

The 12 Days of 2FA: How to Enable Two-Factor Authentication For Your Online Accounts
The Electronic Frontier Foundation’s article on enabling 2FA for all your online accounts.

Have a good weekend!

SQL Server & Containers – Part Four

In this final part of my series on SQL Server & Containers I’ll detail an option to run earlier versions of SQL Server in containers.

This post follows on from Part Three in which we uploaded a custom image to the docker repository.

The three previous posts have all been detailing how to run SQL Server 2016/vNext in containers on Windows Server 2016, but I’m guessing that a lot of people out there are using earlier versions of SQL Server in production. Containers can be a really handy tool for development and QA but there’s no point in running SQL in containers that’s a higher version that your production environment.

Say you’re running SQL Server 2012 in production and you want to be able to run containers with instances of SQL Server 2012. What options have you got? Well, one option is in the form of a custom port of the open source software that Docker have released to the community built by a company called WinDocks. WinDocks software allows earlier versions for SQL Server to run in containers on earlier versions of Windows Server.

They have a free community edition available here so that you can see the software in action.

Let’s go through creating containers with SQL Server 2012 instances on Windows Server 2012 R2.

So the first thing to do is get a new server with Windows Server 2012 R2 installed. Then once that’s up and running, you need to install SQL Server…

…wait, what??

The WinDocks software is different from the previous docker software that we’ve worked with in that it needs an instance of SQL installed on the host in order to use it’s binaries to create SQL within the containers. The instance won’t need to be running, it just needs to be installed.

So, the following SQL server features need to be installed on the server:-

windockssqlserverfeature

EDIT: The guys from WinDocks have informed me that the configuration of SQL is now handled by the WinDocks installer, so all you have to do is install SQL and then run the package. I’m leaving the below parts in this post so you know what the package is doing but it’s now all handled automatically

Once SQL Server is installed set all the services to manual, apart from the SQL Browser service which needs to be on automatic startup. The following also needs to be enabled:-

  • Mixed mode authentication
  • Remote connections
  • TCP ports
  • Named pipes

Then once SQL is installed and configured, run the WinDocks package that you downloaded from the link I specified earlier (it’ll take about 10 mins):-

windocksinstall

Reboot the server and once it’s back up open an administrative command prompt window (I’m also going to turn it into a PoSH window).

Navigate to C:\windocks\bin and run:-

docker -H tcp://0.0.0.0:2375 -d

This will start up the docker daemon, allowing you to send requests to it to create containers.

windocksdaemon

Once the daemon is up and running, open another command prompt and run: –

docker images

windocksimages

The WinDocks software comes with three images, the one we’re interested in is the mssql-2012. So let’s build a new container running an instance of SQL Server 2012!

First step is to run:-

docker create mssql-2012

windocksnewcontainer

What we’ve done here is create a new container from the mssql-2012 image that came with the WinDocks software (built from the instance of SQL that we installed).

Next thing is to start the container, so my container’s reference is ab3, so I’ll run:-

docker start ab3

Once you see something similar to below, your container is up and running:-

windocksstartcontainer

The container behaves like a named instance of SQL Server. To connect you need to enter the SERVERNAME,PORT. So for me to connect locally, using the sa credentials detailed when I created the container:-

ssmsconnect

And boom! I’m in:-

objectexplorerssms

So that’s how to get earlier versions of SQL Server running in containers on earlier versions of Windows!

Thanks for reading!

Monday Coffee 2016-12-05

You are the DBA your company makes you. Aren’t you?

SQL Server has such a wide range of features that no DBA role will implement all of them. So, you use a certain subset in each role that you occupy throughout your career. What those features are depends on the role.

However your job as a DBA is to (at the very least) be aware of the features that SQL Server has to offer. That way when you’re presented with an issue from your business you can suggest ideas based on your knowledge of those features.

Need an efficient archiving solution? Partitioning and stretched databases will help you out.
Need your systems to have 99.999% uptime? Always On availability groups are there.
Need a cold standby in your disaster recovery environment? Take a look at transaction log shipping.

The list goes on.

What the absolute best thing about working with SQL Server is, is the amazing community that has built up around it. Online communities like SQL Server Central, Stack Exchange and the #sqlhelp tag on twitter are brilliant resources filled with people who dedicate no small part of their free time to helping other data professionals out.

In addition to those resources there’s also a tonne of free training events on offer. In person events such as SQL Saturdays and online events such as the Pass Virtual Chapters.

Technology is always evolving and SQL Server is no exception. Just look at all the new features that have become available in the last month alone! It can feel like keeping up-to-date with everything that going on is impossible but with such great resources available to your average DBA, it needn’t be so taxing.

Thanks for reading.

Friday Reading 2016-12-02

Another week almost over so here’s a random selection of articles that I’ve been reading…

Changing the SQL Server port on Linux
Denis Gobo goes through the process of changing the port that an instance of SQL Server running on Linux is listening on

Reload a table quickly
Kenneth Fisher talks about how to reload a table’s data. It’s a really nice tip!

Free scripts and tools for SQL Server
Free scripts and tools from the Microsoft Tiger Team for monitoring SQL Server. Run them on a dev instance first!

Docker and Canonical Partner on CS Docker Engine
An agreement between Docker and Canonical means that stable, maintained releases of Docker will be available as snap packages on Ubuntu. Another step into making containers a viable production option (support being available is a big thing).

Capacity Planning Using Performance Data
Excellent article by Erin Stellato on capacity planning, with links to articles by the equally good Glen Berry.

Have a good weekend!