Some of my goals for 2019

In late December 2018 I had a discussion with Amanda Martin the PASS Community Manager around things that I would be doing in 2019.  I thought I would share the questions we discussed, and my replies below.

What are your professional goals for 2019?

My professional goals for 2019 are based around what SQL Server 2019 will bring to the Data Platform.  I am looking forward specifically to the changes around SQL Server on Linux – specifically the Red Hat Enterprise Linux based images.

See my blog post where I run up the Ubuntu images on CentOS:

Setting up CentOS to run docker for SQL Server

I have clients who are heavily into containers and this has driven me to start promoting this within the community – to help others learn about what containerized SQL Server is all about.

Is there a technology you want to learn or master?

As mentioned above SQL Server 2019 is going to be a great release in terms of what it can offer people to extend their data platform.

This in itself will be a technology that I will be mastering in 2019 and I look forward to be able to back-fill that knowledge to our community.

A technology I am looking forward to learn and master in 2019 is Azure Kubernetes Service (AKS).

The reason for this is I have presented on how powerful the Azure platform can be. I am also educating my clients and community around containers so this is a great partnership of both technologies.

In 2018 an area that I have been working in a fair bit has been Availability Groups and the fact that SQL Server 2019 CTP 2.2 allows for running an AG on docker with Kubernetes is exciting.

Using containers as part of a DevOps deployment pipeline for databases and applications is an area that will grow and being at the forefront of that is a driver for me in 2019.

Do you have a skill you want to upgrade?

I speak on continuous improvement – around deploying quality value every time you release software.

A skill I want to “upgrade” is my speaking skills, I want to spend time honing my speaking craft. I want to continually improve how I deliver content to the community and industry.

Being able to deliver quality content that helps people learn is fundamental to why I get up in front of crowds and talk about how to #MakeStuffGo

In terms of technological skills I want to upgrade – I always want to be a better Data Professional and so this year will be spent reading blog posts, registering for some of the fantastic (and free!!) webinars that PASS run.

Our Data Platform is growing exponentially and so a fair chunk of my free time will be spent ensuring that the skills I have within it are relevant, current and transferable.

What are your PASS community goals for 2019?

My PASS community goals for 2019 are to be more involved. To extend my reach and influence within the community. I live in New Zealand which is quite a remote country – which is a good thing and sometimes a bad thing when it comes to travelling (anywhere).

I want to be able to reach the wider community and part of this will be where I do more webcasts as opposed to writing. Being able to run webinars is a great way to connect and share content with a fair wider and diverse audience.

Are you already involved with a PASS Local or Virtual Group? If so, do you want to get more involved in these speaking, organizing and coordination opportunities?

I am already a PASS Local User Group Leader in Christchurch, New Zealand. I run the SQL Server and Data Management User Group which has grown from 250 members to 745 members in 3 years under my leadership.

I have recently been involved in the  “rebooted” DevOps Virtual Group with Rob Sewell (t | w) which has been a fantastic platform for us to get the DevOps message out to the world via the PASS VG platform. Rob is a very energetic guy and I think it’s gonna be brilliant working with a guy who is as bouncy as myself!!

It means that at least twice a month I am speaking, organizing and coordinating educational content to up-skill people – for free. That in itself is awesome and thanks PASS for all the support you give us UG/VG leaders.

Do you want to share more ideas and content with the PASS community through blogging or publishing video content?

I certainly do – 2018 has been a proving ground year for me. Over the past 3 years being involved with PASS I’ve grown my contributions within the community and the reason I’ve grown it is that it is rewarding to see the impact of what we all can do. 2019 I will be looking at how I can do more with web based sessions – so that a more diverse group of people can learn from both myself and others.

Collaboration is a key thing in how I work and also in my personal life and I am looking forward to seeing how I can work with others to increase our “touch points” within the industry to help people connect, learn and share.

I am also looking to collaborate with some people on writing a book to help people learn via another medium.

Do you want to get involved with or plan a SQLSaturday for your local PASS community?

I already organize a SQLSaturday – SQLSaturday South Island. This event has grown each year since I took it over in 2016 and it now boasts Australasia’s largest % of WIT speakers.

I want to further this and a goal for 2019 is to be a more inclusive and diverse community based event.

This is an exciting goal as I have never been one to stray away from goals or from encouraging people to be part of a community.

I am looking forward to collaborating with a whole range of people within both my local and extended network.

So there you have it – 4 days into the New Year and I’m excited about where 2019 will lead me – both from a technological, personal and community perspective.

A mention has to go to PASS as if it weren’t for the platform they provide then I wouldn’t be able to use such things as the Learning Center
https://www.pass.org/Learning/Recordings/Listing.aspx?EventID=931

to learn about containers and SQL Server….

…or attend heaps of SQLSaturdays around the world https://sqlsaturday.com to learn for free (and to give back to the community by speaking)…..

…. or attend PASS Summit year in Seattle  https://www.pass.org/summit/2018/Live.aspx to both learn things and share my own knowledge.

So if you’re reading this and have not joined the PASS Community https://www.pass.org/AboutPASS.aspx  then register here:

https://www.pass.org/RegisterforSQLPASS.aspx

it’s free!! And will help you set and achieve some professional, technical and community goals you may set yourself for 2019.

Yip.

Advertisements

Adding a database running in a docker container to SQLTest

in my previous post I wrote about how easy it is to add the tSQLt unit test framework to your database if it is running in a docker container.

How to use tSQLt unit test framework with a SQL Server database in a docker container

But let’s say you have the SQLTest utility from Redgate (hopefully you’ve bought the SQL Toolbelt as it really does make your life way simpler and more productive)

https://www.red-gate.com/products/sql-development/sql-toolbelt/index

So as per usual – open up SQLTest and choose the database that you want to add the tSQLt framework (that is encapsulated in SQLTest) to;

4. Adding database to SQLTest
This is just the same as if the database was running in SQL Server on windows – right?

You will be presented with this screen – I generally take off the tick for SQL Cop and add those tests later..

5. Add Database to SQL TEST

Error:

Unfortunately we get an error:

6. Error

Resolution:

Basically we need to add tSQLt manually and edit some things to make it work with SQL Server running in a docker container:

1. First we need to ensure set both clr enabled and clr strict security to enabled:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

 

2. We can then either grab the script manually that SQLTest is using or manually download the tSQLt framework from http://tsqlt.org/download/tsqlt/

We will use the tSQLt.Class.sql script and basically search for:

PERMISSION_SET = EXTERNAL_ACCESS

and change it to

PERMISSION_SET = SAFE

We can now either add our database to SQLTest as per normal (in fact if we’ve done everything correctly the it will just appear) so just try and hit refresh and your database should be there.

Now

 

11. Tests Run
Let’s write and run some unit tests  (and keep ourselves employed!!)

There is now no reason at all that you should not be writing/running unit tests for your SQL code.

You can even do it against databases running in a docker container using SQLTest.

I always say unit tests are the thing that keep me employed – as all my buggy code (and there’s a lot) gets caught on my laptop (or container) and NOT in PROD or even UAT systems.

So go visit

https://tsqlt.org/user-guide/tsqlt-tutorial/

or these other great sites:

https://www.simple-talk.com/sql/t-sql-programming/getting-started-testing-databases-with-tsqlt

http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt

https://www.itprotoday.com/sql-server/getting-started-test-driven-design-sql-server

http://d-a-green.blogspot.co.uk/search/label/tSQLt

http://datacentricity.net/tag/tsqlt/

and in no time at all you will be writing your very own unit test.

Yip.

How to use tSQLt unit test framework with a SQL Server database in a docker container

This blog post describes how to add the tSQLt unit testing framework to a database running in a docker container. You may have already read my article on how easy it is to install against a ‘normal’ SQL Server instance – running on windows…

How to install the tSQLt unit test framework to start unit testing your database code

..but what about SQL Server running in a docker container??

Background:

I have a SQL Server instance running in a docker container as described in my previous post:

Setting up CentOS to run docker for SQL Server

The main reason why I run SQL Server in a container is so that I can run up specific versions – all from a script and generally as part of an automated build within Continuous Integration processes.

Method:

The first thing we need to do is spin up our docker container which has our SQL Server instance:

1. Spinning up our SQL Server in container
Let’s spin up SQL Server and use the latest version of SQL Server 2017

Just to verify that it is running let’s run

docker ps

We can see that the container has an ID of 789f58fbef3f and a name of sql1 .

I have also mapped the default port of 1433 to 59666

2. container SQL Server is running

We can now connect to SQL Server on port 59666 and do some typical queries whenever we connect to an instance for the first time:

3. What version is running
Connecting to our SQL Server instance running in a container known as 789f58fbef3f

Note that in the screenshot above – the servername result is the name of our container.

Now we can create our database and SQL Authenticated users as normal.

Yes – you interpreted that sentence correctly – right now you cannot use windows authenticated users against SQL Server running in a docker container on Linux. There is a way to get around this that I will be blogging about in late January 2019.

We can add our database to Source Control as per normal and apply the latest changes in our branch. I use the popular Redgate tooling for Source Control (and DevOps) activities

9. Adding stuff from Source Control
We can treat our database in the container like any other database (except for windows auth…)
10. Progress
Applying changes from Source Control to a database running in a container – easy as..!!

So all that is left to do is download the tSQLt framework (a whopping 86KB zip file)  from http://tsqlt.org/download/tsqlt/

Inside the zip file are some files:

7. tSQLt download
tSQLt comes with an example database you can use to try things out!!

There is:

Example.sql

Which is a script that allows you to create a database and run your first unit tests. It’s a great example and you can follow the documentation at https://tsqlt.org/user-guide/quick-start/

SetClrEnabled.sql

Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.

This is where I diverge slightly from a stock standard install:

The SetClrEnabled script does the following:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

Whereas for Linux based SQL Server we need to run this – as it will not install with clr strict security enabled – note we have to ‘show advanced options’ first:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

tSQLt.Class.sql

This is the framework itself. Now before you run this you have to do the following to get it to run in a database running in a container:

In the script you need to change PERMISSION_SET = EXTERNAL_ACCESS to be PERMISSION_SET = SAFE

We now run the script and voila – we have tSQLt in our database and can now write or run all the unit tests we want.

8. Running tSQLt against container SQL Server
Hooray – we now have tSQLt running in a container

So there you have it – you can add tSQLt very easily (just 2 changes to make) to a database running in a docker container.

As mentioned the reason why I am very interested in running tests against databases running on container is around testing – I can spin up the container, download a particular patch level of SQL Server 2017 and above, add my database from source control, apply any referential data from source control and apply tSQLt which allows me to run a plethora of unit tests.

All within seconds and all consistently across multiple environment levels (QA, Integration, Functional test etc).

So go visit

https://tsqlt.org/user-guide/tsqlt-tutorial/

or these other great sites:

https://www.simple-talk.com/sql/t-sql-programming/getting-started-testing-databases-with-tsqlt

http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt

https://www.itprotoday.com/sql-server/getting-started-test-driven-design-sql-server

http://d-a-green.blogspot.co.uk/search/label/tSQLt

http://datacentricity.net/tag/tsqlt/

and in no time at all you will be writing your very own unit test.

Yip.

How to install the tSQLt unit test framework to start unit testing your database code

This blog post describes how easy it is to install the tSQLt framework.

Don’t know much about tSQLt? If you are serious about dealing with data in SQL Server then you should be as serious about unit testing the changes you are making to your stored procedures, functions and the like.

In fact I wrote a blog post about unit testing here:

Doing DevOps for your Database? You need to start here…

So I am assuming that you have realised how important it is to unit test your data and code so here’s how to install tSQLt to your database.

First – go to the tSQLt website:

https://tsqlt.org/

and go to download the framework:

tsqlt_website.png
Yip – it is just 86KB of unit testing goodness

The direct URL is http://tsqlt.org/download/tsqlt/

You will get a zip file with some files in it:

7. tSQLt download

There is:

Example.sql

Which is a script that allows you to create a database and run your first unit tests. It’s a great example and you can follow the documentation at https://tsqlt.org/user-guide/quick-start/

SetClrEnabled.sql

Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.

tSQLt.Class.sql

This is the framework itself.

We now run the SetClrEnabled & tSQLt.Class scripts and voila – we have tSQLt in our database and can now write or run all the unit tests we want.

I guess at this stage you might ask – why do I need to enable CLR – well let’s say you don’t enable it and just run tSQLt.Class.sql – you will get the following error:

tSQLt_error
You need CLR – but that’s OK as only install tSQLt in your DEV or selected TEST environments.

So yeah – enable CLR and then when you run the tSQLt.Class.sql script – things will go much better:

tSQLt_good
This really is the most simple and easy method of  installing a unit test framework for SQL code

So now you can start writing your own tests. The tSQLt framework adheres to the three A’s of unit testing:

Assemble

Act

Assert

tSQLt_test
It is very easy to create a template that you just use to write your unit tests.

The tSQLt website has some great examples and tutorials and honestly – the learning curve is very small – I’d say most Data Professionals can be up and running unit tests within 1-2 hours.

So go visit

https://tsqlt.org/user-guide/tsqlt-tutorial/

or these other great sites:

https://www.simple-talk.com/sql/t-sql-programming/getting-started-testing-databases-with-tsqlt

http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt

https://www.itprotoday.com/sql-server/getting-started-test-driven-design-sql-server

http://d-a-green.blogspot.co.uk/search/label/tSQLt

http://datacentricity.net/tag/tsqlt/

and in no time at all you will be writing your very own unit test.

In my opinion tSQLt is that sweet spot of easy to use and comprehensive enough to make a difference.

Yip.

SQL Server services will not start automatically after server reboots

This blog post is about a situation where after windows patching the SQL Server service and SQL Server Agent services will not start automatically – but will manually.

At a client site they had recently patched the servers – things went swimmingly – except the SQL Server services would not start.

SQLServer_Broken
Whilst automatic – the services would not start

Manually starting the services by right clicking | choosing start worked….

It is SQL Server 2017 with CU12 applied.

We tried a few things:

Making the services Automatic (Delayed)

Increasing the service pipeline timeout from 30 seconds to 60 seconds

But nothing worked.

Looking at the event logs I (eventually) found this:

Event ID 7000:

The MSSQLSERVER service failed to start due to the following error:
The account name is invalid or does not exist, or the password is invalid for the account name specified.

Which is bizarre – as the service account had been used for months – but after each reboot the services had to be manually started. GPO and other things had been blamed but no one could actually find out why.

Just for a laugh – I decided to “rename” the service account.

From svcSQL@<domain> to <domain>\svcSQL

Rebooted and it worked.

Just to make sure it wasn’t a false positive I left the SQL Server Agent service how it was – and it did not start.

SQLServer_Fixed
<domain>\<service account> works – – but why…?

As to why this is the case – I’m not sure. To date I had always set up my SQL Server services with <domain>\<service account> so I have never stumbled across this – until now.

But I don’t care too much – it means that the client can reboot their server and know SQL Server will come back…

Yip.

Setting up CentOS to run docker for SQL Server

This blog post is about running SQL Server in a docker container on CentOS Linux distribution.

This was both an experiment and also for me to re-learn CentOS – years ago I had done a fair bit with CentOS – back when I was a true Operations Consultant. Up until last weekend I had been using Ubuntu – as it is really easy to use.

All SQL Server container images on docker are built on the Ubuntu image so I wondered – can I run it on a CentOS Linux server running docker?

BTW – the obvious answer is ‘yes, of course because it is just a container”.

But I wanted to try it out and see what differences there were.

The first main difference for me so far – remembering to run sudo when I want to elevate my permissions. This is a good thing – I did many bad things in Ubuntu that I should have thought about first – that is why sudo is a good thing for people like me…!!

So if you are thinking of running docker on CentOS and

Install the latest version of Docker CE:

$ sudo yum install docker-ce

If prompted to accept the GPG key, verify that the fingerprint matches

060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35

and if so, accept it.

Initially start docker:

sudo systemctl start docker

To make it start every time the  Linux server

sudo systemctl enable docker

Download latest SQL server image:

sudo docker pull microsoft/mssql-server-linux:latest

Run up our container and assign a non-default port – in this case I am  swapping out port 1433 to port 56969:

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -e 'MSSQL_PID=Developer' -p 56669:1433 --name SQLServer-Docker-DEV -d microsoft/mssql-server-linux:latest

Port mapping is specified via -p host port:container port.

Therefore in my example above -p 56669:1433 means that port 56669 on the host (CentOS VM) is mapped to port 1433 in the container. I am not a fan of using the default port 1433  – mainly as I like to stipulate different ports for different SQL Server instances (QA vs UAT vs PROD). This means that all connection strings I create from outside the container will need to include port 56669. How do I remember this number – that’s where configuration management comes in – another blog post I think…

I also want to change the password to something more secure (and less published on the internet) and this allows me to also show you how to access the SQL tools which are built into the image:

How to use SQL Server tools in the container

sudo docker exec -it SQLServer-Docker-DEV "bash"

Once inside the container, connect locally with sqlcmd. Note that sqlcmd is not in the path by default, so you have to specify the full path.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>'

How to Change SA password in container

Once we have connected with sqlcmd we can then use our native T-SQL to change the password:

change_sa_pwd
Using sqlcmd to change our sa password and then testing it out

Now we can go to the Azure portal and allow access to this port:

azure_centos
Allowing network access to the SQL Server port from certain IP addresses in Azure portal

My preference is to tie it right down to the hosts that I know will access the SQL Server instance running in this container on this CentOS VM hosted in Azure.

Kinda reminds me of the dream layers of the movie ‘Inception’.

In the next blog post – let’s connect to this VM using SQL Server Management Studio on our PC.

Yip.

 

This is a company that practises what they preach

How good is a company at engaging with their user base? Obviously when things are going great then it’s wins all around for the company and hopefully the people have invested their money in that company’s products…

When things are not going well – I feel this is how you know what a company is really like and more importantly how their endemic culture actually is.

The company I am talking in this blog post is Redgate, I’ve known Redgate products for the best part of 3 years. Back when I started looking at how I could integrate database deployments with application deployments – specifically around folding in Continuous Delivery principles and processes – one company was leading the rest of the pack.

Redgate.

I like Redgate tools – they make what can be difficult and cumbersome in other technology stacks simple and easy. I like that – mainly because there are enough hard things to do in computers and getting a database into source control (and easily deployable) can be right up there in terms of hair pulling.

I especially like the culture of Redgate – I’ve yet to meet anyone that works there that is not engaging, passionate and knowledgeable about all things DevOps. It’s awesome to deal with them, they are really friendly and even the scariest of DBAs ( t | w) is a guy who is fanatical about making stuff go and better for the community and industry. Heck,  they just hired Kendra Little ( t | w ) so they must be both a cool company and awesome company.

(Side note: It was thanks to Kendra’s awesome blog posts and videos that helped me become a better Data Professional and yeah – I’m a big fan of what she does for the community. Plus, she’s as humorous as she is intelligent).

So what about when things go wrong with a company?

That’s normally when you find out how they really are.

If you’re a customer of Redgate you might have got an email from Tony Payne – their Chief Operating Officer – so the thing that occurred is fairly important and I like the fact it came “from the top”. The issue was a security vulnerability in SQL Monitor – which is a fairly important thing to fix and get right.

I knew about this security vulnerability as I had contacted some of the people I know at Redgate and trust and said “hey, I have noticed something and I think you need to look at SQL Monitor”.  I use SQL Monitor and I recommend SQL Monitor to my clients – it is a fantastic tool and the way it integrates with other tooling really fits in with the ethos of DevOps .

(Another side note: because I’m the Hybrid DBA – I don’t just do databases, I also do stuff like pen testing, configure web nodes with powershell and/or write in ansible to make stuff go… so sometimes I don’t just use tools, I like to break tools)

Now Redgate could have just said “cool, thanks for bringing this to our attention” and left it at that. And whilst that sounds like no company would do that – trust me there are companies that do exactly that.

Redgate responded back to me immediately, the product manager kept me in the loop both of how serious they took this and more importantly what they would do about it. They went to work immediately to fix it and got in outside consultants to audit that things were fixed.

As per the website above detailing what they did – they did all the right things.

What that article doesn’t tell you is they did something that Agile brings to the table – because they practise Agile they were able to React and Adapt to something that was not forecast as part of sprint planning.

Redgate preaches all things DevOps – but could they practise it. The long answer is Yes. Yes they can and for me that is a fantastic measure of a company.

People are fairly intelligent and can tell if you’re wanting to pull the wool over their eyes (you can fool some of the people some of the time…). If they had just swept this under the rug then yeah – their whole brand about delivering value quicker and keeping your data safe would be a fallacy.

But it’s not – they understand how important your data is, they know how to deliver value quicker because – they did.

I’m really impressed by how they handled this situation – I was very lucky to be an insider on what they were doing and I really appreciate that they let me in on that. It made me feel valued and made reinforced why I love both their products and the culture they have within their company.

Nice one team!!

Want to make your database deployment way safer, easier? You should check them out:

https://www.red-gate.com/solutions/overview

Yip.