I had a lot of fun yesterday presenting my talk on how to go from not having your database in version control to deploying to a CI database, running unit tests and generating deployment scripts for a QA/Prod database so effectively how to build a fully functioning continuous delivery process for SQL Server databases in less than an hour (my wittering was about 40 minutes and the demo just over 10).
Niche tool of the day
If you know what a deployment contributor is and have wanted to see what steps were in a plan and have got bored setting a breakpoint in visual studio and using the watch window or something to display the steps in a plan, you might find this useful.
It is really simple, all it does it look through all the steps in a deployment plan and dumps (Console.WriteLine) the steps, any properties it can find and some custom bits like the T-SQL that makes up the step.
To help with this scenario (it is pretty specific):
You have a table that has columns that you do not know about when you create the dacpac, possibly you have a customer who modifies your schema after you deploy. If you deploy your dacpac those columns will be dropped which is bad and you don’t want to completely ignore all changes to a table what do you do?
You can now use the new version of my deployment contributor with a filter like SqlPackageFilter=KeepTableColumns(Employees)
In my deployment filter I have been asked a couple of times to allow columns that exist on a table that do not exist in a dacpac to be ignored so that they are deleted. The typical scenario is that a vendor supplies a database and then customers can add extra columns (I know what a horrible place to be but it happens).
I had a look at this and my first thought was that if we use the deployment filter and add an IgnoreType(SqlColumn) that it would work but it obviously doesn’t. What I ended up doing is adding a new filter type specifically for table columns and it actually turned out to be quite an interesting jaunt into the ScriptDom and Microsoft.Data.Tools.Schema.Sql.dll so I thought I would blog it because it also strays into understanding how the DacFx builds the deployment scripts for changes to tables (when do you get an Alter Table Add Column and when do you get a Create/Insert/Drop Table).
I am going to ignore the fact that people don’t want to install visual studio on a build server, in the future there will likely be a way to install just the required components rather than everything. Today, the way I see it is that I develop in Visual Studio, I am happy to install Visual Studio on the build server for me Visual Studio is as dependency for SSDT.
I installed the 2016 SSMS client recently and in one way or another any of my apps that I have written that use the DacFx ScriptDom have all broken in slightly different ways. One app throws a type conversion error and another just failed to find any statements in a batch.
When I started debugging the problem I noticed that I got this when I started one of the apps from Visual Studion:
I wrote a test earlier and forgot one of the two cardinal rules about tSQLt so I was running my test suite and the test did not run, I re-deployed and it still didn’t run so I thought I would create this handy list of things to check that mean that even when the tests exist in the database they do not run:
- Are the tests in a schema that has the tSQLt test class extended property?
- Does the name of the test start with the word “test”? If it doesn’t then it will not actually run
Test schema extended property
Really excited by my first post on medium, haven’t really used it before but found it to be a really nice tool:
https://medium.com/@edelliott.the.agilesql.club/let-s-start-doing-profes…
Last night I had a lot of fun presenting on one of my favorite topics, MARS and SQL Server protocols including a rather fun live demo.
The slides and demo apps are available: https://github.com/GoEddie/SQLSupper-WhenMarsAttacks
If anyone wants to know more about MARS or TDS feel free to contact me as always :)
