# Ed Elliott's blog

## xSQLServer is dead long live SqlServerDsc

• Posted on: 15 December 2017
• By: Ed Elliott

I have had a problem with DSC in Azure Automation for a few months now, there was a change made to the xSqlServer DSC resource which meant that the paths internally were too long to be compiled on Azure Automation, I don't even really want to think why because the fact that path lengths are an issue in 2017 (almost 2018!) makes me want to cry, so if you want to know more look at:

https://github.com/PowerShell/DscResources/issues/310

The fix was to deploy the old version 7.1.0.0 to Azure Automation and use that but yesterday when I tried to use it, it failed as well and I was about to start crying when I found out that a version 10 had been written but not deployed, except it has been deployed so I switched my dsc script form xSqlServer to SqlServerDsc and everything is now cushty.

In the changelog there is a list of breaking changes and I had to change my resources form the old style to the new doing things like changing:

 xSqlServerLogin LoginThing{ SQLServer = '' SQLInstanceName = '' }

to

 xSqlServerLogin LoginThing{ ServerName = '' InstanceName = '' }

but once that was done and a typo or two fixed then compiling on azure automation works again and my life is now complete.

Tags:

## What The Tool? Multiple choice quiz to help you choose a tool for SQL Server

• Posted on: 14 December 2017
• By: Ed Elliott

There are a few different choices for tools to use when it comes to SQL Server, while the "experienced" amongst us have used query analyzer, enterprise manager and probably ssms it isn't always clear what tool you should use. If you consider sql operations studio and vscode it is a literal minefield of possible options.

I was quite pleased with how my https://the.agilesql.club/WhatTheIO/ tool worked out and so I thought I would build a simple multiple choice quiz to help people decide between SSMS, SSDT, SOS, VSCode + mssql add-in:

https://the.agilesql.club/WhatTheTool/

I will probably add the command line tools and maybe some third parties if I feel so inclined.

Enjoy!

Tags:

## My Azure ARM template development workflow

• Posted on: 13 December 2017
• By: Ed Elliott

Writing infrastructure as code is pretty nice and I enjoy the declarative approach of defining what you want and letting the tooling take care of the how. I guess this is because of my background as a SQL developer.

What I don't like so much is the development experience and if you don't get this part right it can be a right pain.

## What are the challenges?

The first real challenge is writing the correct json for each thing you want to create. For example, to create a virtual machine you need to know what the api version is that you should use and which properties are needed and which properties are required vs optional properties. Once you have that for a virtual machine you need to know the same details for a nic, nsg, storage account for vm diagnostics and any vm extensions you might want to include (and what properties they have).

Getting all this information together can be quite painful, there are four approaches really:

• 1. "Automation Script" in the portal
• 2. Viewing JSON schema docs
• 3. Examples
• 4. Visual Studio Deployment project

The first seems the easiest which is you deploy something then in the portal you hit the "Automation script" button which generates the arm templates and a script you can use to deploy it with.

There are two problems with this, firstly not everything is supported and so if you are deploying cutting-edge resources this won't work for you. The second issue is the way the script generator names all the resources and creates parameters for everything called something like resource_name_name_parameter if you have a fair few resources then this gets really confusing, really quickly.

The generated script is also for everything in the resource group, even if you only try to generate it for a single resource. Finally, the generated script also includes properties like status which aren't part of a deploy and just obscure the actual bits you need. If you do use the automation script option it must only really be used as a guide to get you started or to check something.

The second option is to look at the arm JSON schema's and work out what properties are supported with api version, you can find them:

https://github.com/Azure/azure-resource-manager-schemas

The third option using examples from either the Microsoft docs site or github is good but often samples do one very specific thing and you need to spend a while tweaking them. The samples are also all different and there is not a common way to name things so merging multiple together is often a pain.

The last option is the visual studio deployment project which has a wizard to add resources, this is often quite out of date and the fix suggested by Microsoft (see comments https://docs.microsoft.com/en-us/azure/azure-resource-manager/vs-azure-t...) is to just edit the json manually.

So, there really isn’t one ideal way to create arm templates the way I work is a combination of 1-3, I don’t really use visual studio because I have migrated to use vscode.

Once you have the resource versions and properties you then have two more problems, the first is that you end up with a big blob of json and navigating resources and doing things like copying and pasting and moving things about is a little risky. Visual studio has a “JSON Viewer” that is a tree viewer and lets you navigate different objects like parameters and resources but not the properties of resources so if you have a large resource you can still only navigate to the beginning of it.

The second problem is that ARM templates have their own JavaScript like language where you can have things like:

[concat(if(equals(parameters('parameterName'),1),'abc','def'),'something_else')]

If you use this sort of thing to name your objects you don’t really know if they are right until you deploy, these things can get really complicated. Both visual studio and vscode have add-ins that provide intelligence and red squiggles to show if these are right or wrong but won’t let you see if you have used the right parameter or got the final concatenation correct, the amount of times I have deployed something to see the name isn’t quite correct so I have had to redeploy is literally infuriating!

## What is the solution?

The first thing is getting the correct resource versions and properties and to be honest I haven't found a better way than using a combination of the approaches. I will look at bits already deployed and use the automation script to see what it can look like and also use existing samples and the azure docs such as:

https://docs.microsoft.com/en-us/azure/templates/microsoft.compute/virtu...

If I get really stuck then I will use the template schemas.

For the tooling I was using visual studio but I was having a problem because I was using octopus deploy variables which would do a find and replace with parameters in the parameters file and this is fine for strings and numbers but when passing arrays the format I need in the parameters file means the file is invalid json and when I copied and pasted something into that file Visual Studio tried to help the invalid format and ended up messing it up so I would have to undo the changes and be very careful about changing that file.

Because of this problem with the parameters file I started using vscode and the Microsoft Azure arm extension which gave me some bugs like intelligence etc and I could edit the parameters file easily but it didn't have the JSON outliner that Visual Studio had so I back to having a blob of JSON and editing it became a pain again.

This led me to write a similar JSON outliner for vs code but because I wanted to get a better idea of what the code would look like I also evaluate any properties which have values that are code values like:

[concat(if(equals(parameters('parameterName'),1),'abc','def'),'something_else')]

Then the treeview also shows the code value and the evaluated like:

'abcsomething_else'

I also added the facility to run a template script like the one above to see what it would look like when deployed, typing in lots of if(equals((((((((( etc into a string and not knowing what it will actually do is scary, so this helps me.

To get this to work there were a couple of things I have to do, firstly we need a parameters file that we can find so I look for a file called parameters.json if you have a different file for your parameters or you have something like I have invalid JSON then you can create a dummy parameters file and point to it using the .vscode/armed.json file so when we evaluate an expression we have something we can use for the parameter value.

I also haven't implemented everything, if you use "reference()" to do something like looking up a property of a deployed resource then I don't evaluate it - I don't want to make the api call as part of an extensions which would be very slow - it is something I am thinking about implementing using something similar to the parameters file but will see if I need it or not.

There are also a couple of things like the uniqueString, I can't find the type of hash that is used so can't guarantee that it will be the same and the gt() function on strings behaves the opposite way to the way I expect it to. If you call gt on a start 'a' is normally greater than 'A' but in arm it isn't. I've implemented it the way I think it should be and using gt() with two strings is odd anyway so I wouldn't use it.

The other oddity is that I use eval to run the code so you could if you wanted to also put valid javascript in and it would work but wouldn't actually work if you deployed it, I don't know why anyone would do that though.

So I use a mixture of ways to get the resources and my armed extension to navigate templates, I display resources and properties in the window so you can navigate either resources easier. I also use the evaluate tool to see whether my code not just compiles but looks correct.

Tags:

## ARMED - Where is the source?

• Posted on: 9 December 2017
• By: Ed Elliott

ARMED, where is the source?

For quite a few years I’ve been putting everything I wrote on github and made most of it open source with a permissive license like MIT or Apache2. If anyone asked then I would be happy to change any licenses, whatever they wanted but with this new extension I haven’t put it on github.

Basically it is freeware, I wrote it in TypeScript which compiles to JavaScript so if you want the JavaScript you can just read it, I prefer reading and debugging TypeScript but as it isn’t compiled to machine code the extension effectively ships with the source.

I’m not putting the repository on github for two reasons, the first is that it didn’t actually take very long to write. I took the json outliner sample and added to it by writing my own set of functions that arm template support like if, equals etc which are just wrappers around the normal if and ==‘s operators.

If anyone wants to write their own version it won’t take you long, I wrote the graphing stuff in about 3 hours one evening.

If you need to debug it, you have the JavaScript source and vscode lets you open the chrome debugger and debug like a boss.

The second reason is that I’ve had pr’s for projects before where I’ve been busy and not focusing on them at the time and ended up merging things I now wish I hadn’t.

The tools I wrote are because I want a tool to do something, if 1 person or 100 people use them then great. In fact, in many ways , I would prefer not many people to use them. My ssdt dev tools addin for visual studio/ssdt has over 10k downloads if even 10% of those are active then I could potentially break 1,000 visual studio instances with every release.

I don’t have the time or resources to test all the different possible combinations of visual studio and windows etc to be able to have a great deal of certainty about a release so releases slow down to only the necessary.

This isn’t about writing tests this is testing whether windows 7 sp 1 with multiple combinations of the c run times, .net framework and DacFx all work with the dll’s I write and it would take a lot of work to build and test this stuff.

So, use the tools or don’t. If you do and you get stuck reach out to me and ask for help. Sometimes I can but sometimes I’ll be busy and can’t.

I have created a github repo to let people raise issues and put some docs but there is no code there:

https://github.com/GoEddie/armed-docs

If you want the source just ask and I’ll send it to you, you can copy etc and create your own projects but I don’t think I’ll accept any changes, not right now anyway.

If you want better tooling, write it, share it :)

Tags:

## ARMED - ARM resource dependency graph inside VSCode

• Posted on: 8 December 2017
• By: Ed Elliott

I think the title sums it up, I have published a new version of ARMED the arm helper extension for VSCode.

This version includes the ability to see a graph of the resources and how they relate to each other:

There are a couple of limitations at the moment this knows about child resources and also resources referenced via dependOn, if you have an implicit reference using "resourceId" then I don't pick it up (yet).

Tags:

## ARMED - Azure ARM VS Code extension

• Posted on: 6 December 2017
• By: Ed Elliott

I have been working with ARM templates for a little while and have found it really annoying to test functions inside the templates, you see if you have an ARM template it is a JSON document made up of lots of key/values like:

{"name": "a_name"}

but it is slightly more complicated because there is a set of functions in a javascript like language you can use in the values so you might have:

{"name": "[concat('a_', 'name')]"}

which effectively does the same thing, now because ARM templates make lots of use of these template functions it is hard to write them and see if they are correct before deploying so I wrote an extension for vs code (https://marketplace.visualstudio.com/items?itemName=ed-elliott.azure-arm...) which does two things (at the moment), the first is an outliner similar to the visual studio (fat version) and more excitingly, it includes a tool to test these functions so when you have something complicated like:

"name": "[concat(parameters('servicesNames')[copyIndex()], 'pip')]",

and hoping you have the right parameters, you can use the vs code add-in to see what it is actually going to be before you send it off to Azure.

If you want to see it in action, check this:

Any issues shout or raise an issue https://github.com/GoEddie/armed-docs/issues

Tags:

## Azure Virtual Machine + Premium Disk Throughput Calculator

• Posted on: 2 December 2017
• By: Ed Elliott

I keep having to refer to the virtual machine size page and the disks pricing page to work out how best to stripe disks for whichever type of virtual maching in Azure to work out what sort of throughput we can get so I thought I would save myself some ink and automate it, hopefully someone else finds this useful.

To get to the calculator see:

http://the.agilesql.club/WhatTheIO/index.html

### What does it do?

You choose how many volume you want, whether you want caching or not and then how many disks choosing the amount and type of disks and then the calculator will tell you what your max throughput is and also when you have exceeded the maximum throughput of the virtual machine.

### What does it look like?

Like this:

You can switch between vm types, in this example we can see that a single P20 exceeds the total throttle limit of the E2s_v3 so striping the disk or using a faster disk is pointless (unless we are using it just to get more space rather than performance).

Let me know if you get any issues, I've only tested on chrome and will probably add to it in the future, maybe adding in non-premium disks but for the moment it is just premium disks and vm's that support premium disks.

The calculator itself is available:

http://the.agilesql.club/WhatTheIO/calculator.html

The instructions:

http://the.agilesql.club/WhatTheIO/instructions.html

ed

## Fixing SQL AG Routing "Cannot find host" using packet analysis (and a spell checker)

• Posted on: 30 November 2017
• By: Ed Elliott

I was setting up an availability group listener recently and when I tried to connect to the listener I got an error message to say "The host cannot be found" after the usual 15 seconds connection delay.

I checked the usual thing like TCP was enabled on the replicas and that routing was configured, but every time I tried I kept getting the "The host cannot be found" error. Now, I don't know about you, but I find error messages like this completely infuriating especially when I knew the hostname of the listener resolved because I could ping the damn thing (and then connect straight to the IP address.

Now with a listener, you connect and then either connected or you are routed to one of the other hosts. I knew I could connect so guessed that I was being redirected to somewhere else but I didn't know where. Of course, if the error had just said "The host (hostname) cannot be found" I wouldn't be writing this today :)

So I decided what any self-respecting lover of network packets would do, I opened a command prompt google'd "netsh trace start command line not rubbish thingy" to get to this blog: https://blogs.msdn.microsoft.com/canberrapfe/2012/03/30/capture-a-networ... and ran "netsh trace start capture=yes tracefile=.\path\to\trace.etl".

When the trace was running, I quickly reproduced the error and then ran "netsh trace stop" and copied the etl file to my laptop where I had "Microsoft Packet Analyzer" installed. A quick hop skip and a jump over to the etl file in the packet analyzer tool and I wanted to see if I could see a failed DNS request so I went to the "Filter" window and added the word "dns" then clicked "Apply":

This did show a request for the wrong machine in the wrong domain, but how did it get there? When I saw this I went to SQL and the read-only routing URL in sys.availability_read_only_routing_lists and of course it was totally wrong. That was the fix, but not the end of this story dear reader.

Now I had a network trace of routing in action I thought to myself, as I am sure everyone who has ever worked with SQL Server has thought at one stage or another: "I wonder if the routing is handled via a TDS ENVChange token?".

I changed my filter from "dns" to "tds" and then I could see all the tds traffic:

I also changed the columns to include source and destination ports so I could see which packets were from the client and which were from the server. Traffic to the server will have a destination port of 1433 (unless you are mental and change it or aren't mental but are using an instance), the source will be different for every connection.

Looking at the source / destination pairs I could see there was more than one set of connections intermingled so I guessed mine was the one with the source port of 52264 as 62084 was sending SQLBatch tds packets and I wasn't getting that far, so a change of the filter from "tds" to "tcp.port == 52264" and we have a trace with just our traffic:

So what is all this? Well, a SQL Server connection goes like this (forget about silly things like local connections and named pipes, this is just TCP connections obviously):

1 - Create a TCP connection:

Client - Hi Server, I want to create a TCP connection to port (probably) 1433
Server - OK, go on then
Client - Thanks, I will then

The "S" in the "Flags" part of the summary for each packet is short for SYNCHRONIZE and it is TCP's way of saying Hello, the client sends a S, the server responds to the S with an A for ACKNOWLEDGEMENT and sends its own S for SYNCHRONIZE, the client then hilariously replies to the server's S with its own A. To put it another way:

"The SYN, SYN ACK, ACK" TCP handshake.

2 - Create a TDS connection

Now this is where life jumps into the fact lane, go back to google grab the [MS-TDS] spec (https://msdn.microsoft.com/en-us/library/dd304523.aspx) and read it :). Done? Good so you know the sequence to create a TDS connection over the previous TCP connection is:

First the client sends a PRELOGIN packet, this is unencrypted and works with basically any decent version of TDS from 4.2 upwards.

After the PRELOGIN packet, we get some TLS traffic so SQL can encrypt the LOGIN7 packet. This can be "man in the middle'd" unless you have "Trust server certificate" set to false, so if your not on a LAN make sure you always do that.

We'll skip the TLS setup stuff and can't see the response to the LOGIN7 packet as it is encrypted but we do see the response to the LOGNI7 packet which includes some NTLM setup guff which we don't care about here.

Once the connection is validated and the user logged on, we get the response to the login:

Now what happens in a login response is SQL says lots of nice things to us like "your database is now XXX" and "your language is XXX". It does this by sending a series of tokens that are the ENVCHANGE (environment change) token and also an INFO token which is the message you see written to the "messages" window in SSMS.

If we look at some of the tokens we see our first token is an ENVCHANGE with a token type of 1 = DATABASE which is a change database context token:

I'll leave you to go through any other TOKENS you like and jump onto the last token in the packet which is again an ENVCHANGE but packet analyzer doesn't recognise the TOKEN which is of type 20 (0x14). Jump back to the [MS-TDS] docs for ENVCHANGE TOKEN 20 and we see:

HOOPLA!, token 20 is an ENVCHANGE token that routes the client from one host to another, if we examine it we see the name of the server we will be redirected to:

Anyway, now to put the correct domain name in the routing list!

PS, Just to finish off the connection we can see the F flag sent from the client, the server responding with its own F and an A and then the client sends its own A and then nothing else happens. I will leave you to figure what this last bit was about and for bonus points have a look at why people don't use R or RESET (or RST) to close connections!

Tags:

## "the input device is not a TTY" error running docker on VSTS build agent

• Posted on: 22 November 2017
• By: Ed Elliott

I have been playing around with including all my build dependencies in docker for windows lately and had a build that kept giving me this error in the build log:

"the input device is not a TTY. If you are using mintty, try prefixing the command with 'winpty'"

The command I had been running was along the lines of:

docker exec -it powershell.exe ./some/script.ps1

The problem was that I was using -it which means "interactive" and "create a virtual TTY", to run my script I needed neither of those so took them off. The command then failed and I could see the actual error message which allowed me to find the real problem, instead of seeing a message about the TTY because the docker exec command failed for some other reason (container not running, powershellexe instead of powershell.exe lol).

Anyone hope it helps someone, if you get this error then the root cause is something else, and you don't need -it on docker exec when you run exec a script in a docker container as part of a build.

Tags:

## How do I use VSTS variables in YAML CI Build definitions?

• Posted on: 21 November 2017
• By: Ed Elliott

Hey, VSTS YAML builds are my new favorite thing, by like a million miles.

If you have a yaml build definition ( .vsts-ci.yml ) and you want to use one of the build variables then it isn't totally clear how to include them in a definition (it hasn't been out long so I expect more docs to come soon), while we wait for the docs if you want to use one of the variables from https://docs.microsoft.com/en-us/vsts/build-release/concepts/definitions... use this format:

 \$(Build.SourcesDirectory)

Simple! On the page https://docs.microsoft.com/en-us/vsts/build-release/concepts/definitions... each variable has two definitions, use the top one so Build.Thing rather than the upper case single word like BUILD_THING.

ok?

Tags: