SSDT: /p:AllowIncompatiblePlatform, what platforms are compatible?

Sometimes you don’t have the perfect development environment for SQL Server, sometimes you deploy to things like SQL Azure and want to test locally, for various reasons it is possible that you want to deploy to one version of SQL Server but set the project properties to a different version of SQL Server. If you find yourself in this situation you might need to use the parameter AllowIncompatiblePlatform which essentially says “I know I said the project is for SQL 2017 but I am deploying to SQL 2014”, anytime you say this you also sign the contract that says “If I am deploying to a lower version then I have made sure I haven’t used any keywords or object types that didn’t exist in that version and also if everything fails then so be it, I live with my choices every day”.

TDD for SQL Server using tSQLt course certificates now available

I have been asked a few times now for course completion certificates so I have decided to give out certificates to anyone who pays for a plan and sends me a screen shot of a unit test they have written and answer a question on unit testing. I will generate the certificates by hand so i’m not doing them for everyone otherwise I would have hundreds to do (over 300 people have now taken the online self-paced course).

mentor or mentee be, be both, be neither, be happy

TLDR: There is a new exciting community slack channel where you can find a mentor, a mentee or both if that floats your boat. The great Chrissy LeMaire ( from dba tools and fame and general Powershell and SQL legend has started a new mentor’s slack channel. Come and join the fun, it is a community-driven informal process. I have put up a google docs sheet with a list that people can add themselves to or use to contact other people who might be a good fit for them.

Running MySQL on a VSTS build agent

I had a requirement to run MySQL on a VSTS hosted build agent and then to be able to run commands from outside of the container and this gave me the chance to have a play around with VSTS support for containers and builds as YAML (YAML builds / builds as YAML??). I struggled getting started with this for a little bit longer than I would have hoped, if you have a container and want to run commands inside the container then the process is pretty straight forward, although on vsts you don’t get a TTY so need to do “docker exec -i” rather than “docker exec -it” as all the examples show.

Database testing and ETL processes

Unit testing using tSQLt is really great, you call FakeTable on any tables your code touches and then insert the columns and rows your test actually need, nothing more, nothing less but what happens when you have more complicated processes like an ETL process? TLDR: the same as unit testing, just you probably don’t use tSQLt to mock the tables. What types of test are there? Tests get larger and more complicated the further away from the code, so first off we have unit tests which are very close to the code.

Interesting Azure ARM "Make it So" behaviour

I had a template that deployed a premium managed disk and a virtual machine. I defined the disk like: { "comments": "Managed Disk.", "type": "Microsoft.Compute/disks", "name": "[variables('diskName')]", "apiVersion": "2016-04-30-preview", "location": "[parameters('location')]", "tags": { "displayName": "Data Disk 1 - Presented as LUN 0" }, "properties": { "accountType": "Premium_LRS", "creationData": { "createOption": "Empty" }, "diskSizeGB": 1024 }, "dependsOn": [] }, Then in the same template, I had a virtual machine with the storage section that looked like:

Why didnt my Azure ARM deploy work?

I have been using Octopus to deploy ARM templates for a while now and the default task that comes with Octopus to deploy doesn’t have any debugging information enabled and when you run this powershell: New-AzureRMResourceGroupDeployment If you don’t have debugging information enabled, the output of a failed deployment is basically “Deployment Failed, suck it up dude” or something along those lines. If you want to find out why your deployment failed, the best thing to do is grab your template and parameter file and run:

VSTS extensions, mixing VSS.require and import ... require

I have been starting to write more and more typescript lately and quite enjoying it. I have also been writing a few vsts extensions and using typescript to write vsts extensions seems to be working quite nicely. One of the best things, for me, about typescript is to use type information which gives compile time verification and intellisense. This is good but when it comes to VSTS extensions, almost every sample I have seen follows this pattern:

gdpr - panic part 4

In the first part of this series, we looked at where to find out more information about GDPR in the UK (hint: The website has everything you need). In the second part, we looked at some historical enforcement action by the ICO against companies who had made a mistake with their security and data that they were responsible for. In the third part, we again looked at some different angles of opsec which have caused issues such as physical access to a server room not monitored by CCTV and how it led the RSA to a £150,000 fine.

tSQLt Course

In the last half of 2017, I decided to run a tSQLt course and went down the route of putting together a free 12-week email course. The intention was that every week students would get a bit of a description about how unit testing T-SQL using tSQLt works and a little bit of homework to ease them into writing tests both in terms of how to technically get tSQLt running but also the art of writing effective unit tests.