Hello!

Changes to dbatools Functions What I Wrote

As the saying goes, it never rains but it pours. And there’s been a flurry of activity on a few of the functions that I initially contributed late last December to dbatools. Up to this point, there have been very few changes pushed and issues raised. However fom the middle of October to the beginning of November there’s been about 15 issues/pr’s. Extraordinary! This is all good as it shows that people are using them and that other people are contributing to make them better, which is what it’s all about irrespective of who the author is.

Much of the change has been around bacpac support, but a few bugs have also been uncovered and fixed, including getting the actual error message and generating a report.

SSDT Gets Some Love

Amazingly, the phrase “it never rains but it pours” also applies to SSDT, as it not only supports SQL Server 2019 Projects, but also Azure SQL Data Warehouse is in the works via a preview. I am told that it only supports tables and procs, but the rest is coming. In the meantime you can either apply for the preview, wait for the preview to become more available, or try your hand at SQLDWSchemaMigrate. Oh, and Managed Instance is now deployable via SSDT (more below).

SQLSupper Lightning Talks

At the beginning of the year I was offered the chance to co-organise SQLSupper, a London-based user group that is more of a Data Platform smorgasbord than anything else: anything loosely related to data is what we’re interested in. And we decided to cancel the October meetup to focus our efforts on getting people to do some lightning talks - just 5 minutes on the stage talking briefly about whatever they wanted in the realm of data. It was a decent turnout, and we had some great talks over a variety of subjects; PowerBI, Premium Disk Latency in Azure (trust me that was interesting) amongst others, and the whole process went smoothly. Will be ohsting more of those in 2019.

New Project on GitHub

Sometime mid-November I uploaded a new project on GitHub called ephemeral, which is basically causing havoc with triggers and cursors, two of SQL Servers biggest no nos. The idea behind it being that once tables are created they have a shelf life before being made inaccessible and eventually deleted. It was related to a problem I once had many years ago at another company. Erm, maybe it’ll prove useful to someone, somewhere. As long as GitHub don’t start charging me money for essentially using their service as a mausoleum to bad ideas I’ll keep dumping stuff like ephemeral, Sundae and of course ExpandingBrains up there…

Happy Birthday PoshSSDTBuildDeploy

Speaking of GitHub, (and in what is proving to be quite a heavy SSDT/PowerShell/GitHub bulletin), PoshSSDTBuildDeploy has been available in some form or another for 1 year this November. It’s been downloaded nearly 10,000 times across Nuget and PowerShellGallery, been forked and starred a few times on GitHub, and I’ve had positive feedback from people who have been kind enough to get in touch with me about it. I’ve even had pull requests! Plus tests written for it! All in all quite pleased that something I wrote very much for my own benefit has proven to be useful for other people who are trying to automate their database build and deployments.

And so but I recently had to make another change to the module. You see because it does both build and deploy, and dacfx does both, it made sense to download only Microsoft DataTools MSBuild and use the dac dll included to do both. Seemed sensible and all was good. However, DacFx is available as a downloadable in it own right, and Microsoft release that with far more frequency. Why, I don’t know. And this only very recently became an issue with the version 15 release of SSDT, as that version now supports deployments to Managed Instance, which I happen to be working with on a project. So, if you need the latest version of DacFx you can download it using the function Install-MicrosoftSqlServerDacFxx64. It is pretty much identical to InstallMicrosoftDataToolsMsBuild but with some cunningly subtle yet distinct changes*. What is interesting about deploying to Managed Instance is that the target platform when building the project, it doesn’t have to be Manage Instance, mainly because no option exists. Rather, I’ve deployed a database targeted for SQL Server 2016 and all has been good. I’m sure most other on-prem targets will also deploy to Managed Instance.

*And to expand a bit on why I chose to copy and paste to create a new function, rather than add the logic to the current function: I’ve got some pretty decent testing for this module that is run prior to publishing. And so because I was adding new functionality, I wanted to write some tests. By creating a new function and altering a few parameters, I was able to copy the tests verabtim and make a few minor changes to those. Had I added logic to the current function, I would need to rewrite my old tests, and add more, and in fact write even more tests to cover any oddities that might occur from adding loads more switches. Couple this with the fact that the name of the function wouldn’t cover what it does any more means that it would be confusing. So to make my life easier and get a change pushed out in half hour I went the easier, possibly lazier but definitely safer route.