Monthly Archives: December 2018

Running Windows and Linux SQL Containers together

Posted on by

Just for fun I decided to spend Christmas Eve getting Windows and Linux SQL containers running together.

WARNING

This is NOT a production ready solution, in fact I would not even recommend that you try it.
I definitely wouldn’t recommend it on any machine with anything useful on it that you want to use again.
We will be using a re-compiled dockerd.exe created by someone else and you know the rules about downloading things from the internet don’t you? and trusting unknown unverified people?

Maybe you can try this in an Azure VM or somewhere else safe.

Anyway, with that in mind, lets go.

Linux Containers On Windows

You can run Linux containers on Windows in Docker as follows. You need to be running the latest Docker for Windows.

Right click on the whale in the task bar and select Settings

Notice that I am running Windows Containers as there is a switch to Linux containers option. If you see Switch to Windows containers then click that first.

Click on Daemon and then tick the experimental features tick box and press apply.

Docker will restart and you can now run Linux containers alongside windows containers.

So you you can pull the Ubuntu container with

docker pull ubuntu:18.04

and then you can run it with

docker run -it --name ubuntu ubuntu:18.04

There you go one Linux container running 🙂
A good resource for learning bash for SQL Server DBAs is Kellyn Pot’Vin-Gorman b | t series on Simple Talk

Type Exit to get out of the container and to remove it

docker rm ubuntu


Running SQL Linux Containers On Windows

So can we run SQL Containers ?

Well, we can pull the image successfully.

docker pull mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu

If you try that without the experimental features enabled you will get this error.

image operating system “linux” cannot be used on this platform

So you would think that what you can do is to use the code from Andrew ‘dbafromthecold’ Pruski’s b | t excellent container series

docker run -d -p 15789:1433 --env ACCEPT_EULA=Y --env SA_PASSWORD=Testing1122 --name testcontainer mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu

When you do, the command will finish successfully but the container won’t be started (as can been seen by the red dot in the docker explorer).

If you look at the logs for the container. (I am lazy, I right click on the container and choose show logs in VS Code 🙂 ) you will see

sqlservr: This program requires a machine with at least 2000 megabytes of memory.
/opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.

Now, if you are running Linux containers, this is an easy fix. All you have to do is to right click on the whale in the taskbar, choose Settings, Advanced and move the slider for the Memory and click apply.

But in Windows containers that option is not available.

If you go a-googling you will find that Shawn Melton created an issue for this many months ago, which gets referenced by this issue for the guest compute service, which references this PR in moby. But as this hasn’t been merged into master yet it is not available. I got bored of waiting for this and decided to look a bit deeper today.

Get It Working Just For Fun

So, you read the warning at the top?

Now let’s get it working. I take zero credit here. All of the work was done by Brian Weeteling b | G in this post

So you can follow Brians examples and check out the source code and compile it as he says or you can download the exe that he has made available (remember the warning?)

Stop Docker for Windows, and with the file downloaded and unzipped, open an admin PowerShell and navigate to the directory the dockerd.exe file is and run

.\dockerd.exe

You will get an output like this and it will keep going for a while.

Leave this window open whilst you are using Docker like this. Once you see

Then open a new PowerShell window or VS Code. You will need to run it as admin. I ran 

docker ps-a

to see if it was up and available.

I also had to create a bootx64.efi file at C:\Program Files\Linux Containers which I did by copying and renaming the kernel file in that folder.

Now I can use a docker-compose file to create 5 containers. Four will be Windows containers from Andrews Docker hub repositories or Microsoft’s Docker Hub for SQL 2012, SQL 2014, SQL 2016, and SQL 2017 and one will be the latest Ubuntu SQL 2019 CTP 2.2 image. Note that you have to use version 2.4 of docker compose as the platform tag is not available yet in any later version, although it is coming to 3.7 soon.

version: '2.4'

services:
    sql2019:
        image: mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu
        platform: linux
        ports:  
          - "15585:1433"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"
    sql2012:
        image: dbafromthecold/sqlserver2012dev:sp4
        platform: windows
        ports:  
          - "15589:1433"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"
    sql2014:
        image: dbafromthecold/sqlserver2014dev:sp2
        platform: windows
        ports:  
          - "15588:1433"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"
    sql2016:
        image: dbafromthecold/sqlserver2016dev:sp2
        platform: windows
        ports:  
          - "15587:1433"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"
    sql2017:
        image: microsoft/mssql-server-windows-developer:2017-latest
        platform: windows
        ports:  
          - "15586:1433"
        environment:
          SA_PASSWORD: "Password0!"
          ACCEPT_EULA: "Y"

Save this code as docker-compose.yml and navigate to the directory in an admin PowerShell or VS Code and run

docker-compose up -d

and now I have Windows and Linux SQL containers running together. This means that I can test some code against all versions of SQL from 2012 to 2019 easily in containers 🙂

So that is just a bit of fun.

To return to the normal Docker, simply CTRL and C the admin PowerShell you ran .\dockerd.exe in and you will see the logs showing it shutting down.

You will then be able to start Docker For Windows as usual.

I look forward to the time, hopefully early next year when all of the relevant PR’s have been merged and this is available in Docker for Windows.

Happy Automating 🙂

Getting SQL Services, Starting, Stopping and Restarting them with dbatools

Posted on by

There was a question in the #dbatools slack channel 

dbatools question

Getting dbatools

dbatools enables you to administer SQL Server with PowerShell. To get it simply open PowerShell run

Install-Module dbatools

You can find more details on the web-site

Finding the Command

To find a command you can use the dbatools command Find-DbaCommand
For commands for service run

Find-DbaCommand Service

There are a whole bundle returned

find services.png

This is how you can find any dbatools command. There is also a -Tag parameter on Find-DbaCommand.

Find-DbaCommand -Tag Service

This returns

find services tag.png

How to use any PowerShell command

Always always start with Get-Help

Get-Help Get-DbaService -Detailed

get help.png

This will show you all the information about the command including examples 🙂

help examples.png

All of these commands below require that the account running the PowerShell is a Local Admin on the host.

One Host Many Hosts

Now I have used just one host for all of the examples on this page. Do not be fooled, you can always use an array of hosts wherever I have $ComputerName you can set it to as many hosts as you like

$ComputerName = 'SQL0','SQL1'

You can even get those names form a database, Excel sheet, CMS.

Getting the Services

So to get the services on a machine run

$ComputerName = 'Name of Computer'
Get-DbaService -ComputerName $ComputerName

getting servies 1.png

You can output into a table format.

Get-DbaService -ComputerName $ComputerName | Format-Table

I will use the alias ft for this in some of the examples, that is fine for the command line but use the full command name in any code that you write that other people use

services table.png
You have an object returned so you can output to anything if you want – CSV, JSON, text file, email, azure storage, database, the world is your oyster.

Getting the Services for one instance

The Get-DbaService command has a number of parameters. There is an InstanceName parameter enabling you to get only the services for one instance. If we just want the default instance services

Get-DbaService -ComputerName $ComputerName -InstanceName MSSQLSERVER| Format-Table

default instances.png

Just the MIRROR instance services

Get-DbaService -ComputerName $ComputerName -InstanceName MIRROR| Format-Table

mirror instances.png

Getting just the Engine or Agent services

You can also use the -Type parameter to get only services of a particular type. You can get one of the following: “Agent”,”Browser”,”Engine”,”FullText”,”SSAS”,”SSIS”,”SSRS”, “PolyBase”

So to get only the Agent Services

Get-DbaService -ComputerName $ComputerName -Type Agent
agent services.png
You can combine the InstanceName and the Type parameters to get say only the default instance engine service
Get-DbaService -ComputerName $ComputerName -InstanceName MSSQLSERVER -Type Engine
default engine service.png

Starting and stopping and restarting services

You can use Start-DbaService and Stop-DbaService to start and stop the services. They each have ComputerName, InstanceName and Type parameters like Get-DbaService.

So if after running

Get-DbaService -ComputerName $ComputerName | Format-Table

you find that all services are stopped

all stopped.png

Start All the Services

You can run

Start-DbaService -ComputerName $ComputerName | Format-Table

and start them all

start them all.png

The full text service was started with the engine service which is why it gave a warning. You can see this if you have all of the services stopped and just want to start the engine services with the type parameter.

Get-DbaService -ComputerName $ComputerName | Format-Table
Start-DbaService -ComputerName $ComputerName -Type Engine
Get-DbaService -ComputerName $ComputerName | Format-Table
all stopped - start engine.png

If you just want to start the Agent services, you can use

Start-DbaService -ComputerName $ComputerName -Type Agent

start agent.png

You can start just the services for one instance

Start-DbaService -ComputerName $ComputerName -InstanceName MIRROR

start instance services.png

Stopping the services

Stopping the services works in the same way. Lets stop the MIRROR instance services we have just started. This will stop the services for an instance

Stop-DbaService -ComputerName $ComputerName -InstanceName MIRROR

stopping instance services.png

We can stop them by type as well, although this will show an extra requirement. If we start our MIRROR instance services again and then try to stop just the engine type.

Start-DbaService -ComputerName $ComputerName -InstanceName MIRROR | ft
Stop-DbaService -ComputerName $ComputerName -Type Engine

cant stop.png

You will get a warning due to the dependant services

WARNING: [10:31:02][Update-ServiceStatus] (MSSQL$MIRROR on SQL0) The attempt to stop the service returned the following error: The service cannot be stopped because other services that are running are dependent on it.
WARNING: [10:31:02][Update-ServiceStatus] (MSSQL$MIRROR on SQL0) Run the command with ‘-Force’ switch to force the restart of a dependent SQL Agent

So all you have to do is use the force Luke (or whatever your name is!)

Stop-DbaService -ComputerName $ComputerName -Type Engine -Force

Use the force.png

You can also stop the services for an entire host, again you will need the Force parameter.

Start-DbaService -ComputerName $ComputerName |ft
Stop-DbaService -ComputerName $ComputerName -Force | ft

stop all of them.png

Restarting Services

It will come as no surprise by now to learn that Restart-DbaService follows the same pattern. It also has ComputerName, InstanceName and Type parameters like Get-DbaService, Start-DbaService and Stop-DbaService (Consistency is great, It’s one of the things that is being worked on towards 1.0 as you can see in the Bill of Health)

Again you will need the -Force for dependant services, you can restart all of the services on a host with

Restart-DbaService -ComputerName $ComputerName -Force

restart tehm all.png

or just the services for an instance

Restart-DbaService -ComputerName $ComputerName -InstanceName MIRROR -Force

restart instance.png

or just the Agent Services

Restart-DbaService -ComputerName $ComputerName -Type Agent

restart agent.png

Doing a bit of coding

Now none of that answers @g-kannan’s question. Restarting only services with a certain service account.

With PowerShell you can pipe commands together so that the results of the first command are piped into the second. So we can get all of the engine services on a host for an instance with Get-DbaService and start them with Start-DbaService like this

Get-DbaService -ComputerName $ComputerName -Type Engine | Start-DbaService

start.png

or get all of the engine services for an instance on a host and stop them

Get-DbaService -ComputerName $ComputerName -Type Engine  -InstanceName Mirror| Stop-DbaService

stop one isntance.png

or maybe you want to get all of the service that have stopped

(Get-DbaService -ComputerName $ComputerName -Type Engine).Where{$_.State -eq 'Stopped'}

stopped services.png

You can do the same thing with syntax that may make more sense to you if you are used to T-SQL as follows

(Get-DbaService -ComputerName $ComputerName -Type Engine) | Where State -eq 'Stopped'

T SQL syntax powershell.png

and then start only those services you could do

(Get-DbaService -ComputerName $ComputerName -Type Engine) | Where State -eq 'Stopped' | Start-DbaService

start the stopped ones.png

(note – you would just use Start-DbaService in this case as it wont start services that are already started!)

# Stop just one of the engine services
Stop-DbaService -ComputerName $ComputerName -InstanceName MIRROR -Type Engine
# Get the engine services
Get-DbaService -ComputerName $ComputerName -Type Engine
# This will only start the one engine service that is stopped
Start-DbaService -ComputerName $ComputerName -Type Engine

only one service.png

Come On Rob! Answer the question!

So now that you know a lot more about these commands, you can restart only the services using a particular service account by using Get-DbaService to get the services

Get-DbaService -ComputerName $ComputerName -Type Engine | Where StartName -eq 'thebeard\sqlsvc'

services by start name.png

and then once you know that you have the right ‘query’ you can pipe that to Restart-DbaService (Like making sure your SELECT query returns the correct rows for your WHERE clause before running the DELETE or UPDATE)

Get-DbaService -ComputerName $ComputerName -Type Engine | Where StartName -eq 'thebeard\sqlsvc' | Restart-DbaService

restarting only one.png

Happy Automating !

SQL Server Availability Group FailoverDetection Utility PowerShell Function Improvements – Named Instances, Archiving Data, Speed

Posted on by

In my last post I wrote about a new function for gathering the data and running the FailoverDetection utility by the Tiger Team to analyse availability group failovers. I have updated it following some comments and using it for a day.

Don’t forget the named instances Rob!

Michael Karpenko wrote a comment pointing out that I had not supported named instances, which was correct as it had not been written for that. Thank you Michael 🙂 I have updated the code to deal with named instances.

Confusing results

I also realised as we started testing the code that if you had run the code once and then ran it again against a different availability group the tool does not clear out the data folder that it uses so you can get confusing results.

In the image below I had looked at the default instance and then a MIRROR named instance. As you can see the results json on the left shows the default instance SQLClusterAG while the one on the right shows both the SQLClusterAG and the MirrrAG instance results.

duplicate results.png

This is not so useful if you don’t notice this at first with the expanded json!! Now you may in this situation want to see the combined results from all of the availability groups on one cluster. You could gather all of the data from each instance and then add it to the data folder easily enough.

By cleaning out the data folder before running the utility the results are as expected.

duplicate results fixed.png

Archive the data for historical analysis

One of the production DBAs pointed out that having gathered the information, it would be useful to hold it for better analysis of repeated issues. I have added an archiving step so that when the tools runs, if there is already data in the data gathering folder, it will copy that to an archive folder and name it with the date and time that the cluster log was created as this is a good estimation of when the analysis was performed. If an archive folder location is not provided it will create an archive folder in the data folder. This is not an ideal solution though, as the utility will copy all of the files and folders from there to its own location so it is better to define an archive folder in the parameters.

Get-Eventlog is sloooooooooooow

I was running the tools and noticed it sat running the system event log task for a long long time. I ran some tests using a variation of the dbatools prompt.

This will show in the prompt how long it took to run the previous statement .

speed.png

In the image above (which you can click to get a larger version as with all images on this blog) you can see that it took 18ms to set the date variable, FOUR MINUTES and FORTY THREE seconds to get the system log in the last 2 days using Get-EventLog and 29.1 seconds using Get-WinEvent and a FilterHashtable.

Getting the function

This function requires PowerShell version 5 and the dbatools module.

You can get the function from my GitHub Functions Repository here (at the moment – will be adding to dbatools see below)

Load the function by either running the code or if you have it saved as a file dot-sourcing it.

. .\Invoke-SqlFailOverDetection.ps1

There are two .’s with a space in between and then a \ without a space. so Dot Space Dot Whack path to file.

The next thing you should do is what you should always do with a new PowerShell function, look at the help.

Get-Help Invoke-SqlFailOverDetection -Detailed

You will find plenty of examples to get you going and explanations of all of the parameters and more info on my previous post.

Happy Automating!