Ed Elliott's Agile SQL Club

DevOps isn’t running SQL Server in a container and pushing code to it from Jenkins When we talk about DevOps we envision that we have the ability to check-in code, spin up a new environment, deploy, test and push that code to production and be in the pub…

Every now and then I come across this error in SSDT, normally when trying to publish and the odd thing is that the description never matches the actual cause (if you desperate for the cause it is because you can’t connect).

I have upgraded the ssdt dev pack to support visual studio 2017 and fixed a couple of minor annoyances and have started to think about how to improve it going forward.

I have been quite interested by vs code and have been using it more and more recently.

This one is for the DacFx nuts out there, it can’t be a very big club but judging from the occasional emails I get about it, the quality is very high ha ha.

A teams maturity shows in its choice of tools.

What is this?

If you have ever tried to debug a program that used the TSql Script Dom to parse some T-SQL you will know that the process is extremely slow and this is due to the volume of NoViableAltExceptions (and others) that are thrown and then caught.

Following on from yesterdays blog I was wondering about the comparison of objects that were the same and how the IgnoreWhitespace, IgnoreComments, IgnoreKeywordCasing and IgnoreSemiColonsBetweenStatements flags affected the comparison.

Publishing dacpac’s is a little bit of a pain when you have multiple databases, it can easily start to take minutes to hours to deploy changes depending on how many databases and the size of those databases.

I did a talk at the london .net meetup if you want to get an overview of what SSDT is and how to get started then I would recommend it: https://skillsmatter.com/skillscasts/9274-londondot-net-january-meetup This was aimed at .net developers rather than DB…

I was talking to someone at a meetup recently who was really keen on doing continuous deployment for their database but they had a number of issues, the main was that because management wasn’t sold on the idea and the DBA’s had complete control to push ba…

There have been a couple of fixes in SQLCover this week, kindly submitted by John Mclusky (https://github.com/jmclusky): Code coverage not reported correctly for CTEs at the end of a stored procedure if the ‘with’ is immediately preceded with a semicolon…

In this post I will talk about the in-built refactoring support in SSDT – the language is slightly different from my normal style as originally it was going to be published else but rest assured it is written by myself What is refactoring?

The ideal is to make a change and see that change deployed to production, in a perfect world we would be told to work on something, write the code + tests, deploy to a test environment, prove it works and deploy - this is the cycle time and the faster you…

I have been thinking quite a lot recently (ok not that much but my thinking has changed) about how to deploy dacpac’s.

tSQLt Visual Studio Test Adapter What is this?

Deploying a dacpac from powershell should be pretty easy, there is a .net api which you can use to open a dacpac, compare to a database and either create a script or get the database to look the same as the dacpac but there are a couple of problems with t…

I have released a new version of SQLCover which is a code coverage tool for T-SQL (let’s you identify where you need to focus when writing tests).

There seems to be two trains of thought and I think this is mainly down to who and where your developers are.

I saw recently an email where someone stated that the reason that they manually create deployment scripts is that because of the restirctions put on them by the requirements for auditing and the ability to be able to demonstrate exactly what was deployed…

Code coverage gives you an indication of how well tested a particular area is.

Open Source code coverage tool for T-SQL, SQL Server 2008+ What is code coverage?

Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole.

I have been pretty busy over january and the first half of feburary and haven’t had time to write any blogs, I did have an article on simple talk published: https://www.simple-talk.com/sql/sql-tools/connected-development-with-sql… A couple of exciting thi…

Code Coverage What is code coverage?

In the final part of this 3 part series on what SSDT actually is I am going to talk about the documented API.

In part 1 of this series available here I introduced my drawing of what I think SSDT is and talked about the first major category the development ide and how it can help us.

I often get asked the questions “What is SSDT" and I have wanted to have a single reference as to what it is as it is actually pretty big.

You sometimes want to do things like split a table into two or move a column into another table and when you use SSDT or the compare / merge type of deployments it can be hard to migrate the data in a single deploy as you can’t insert the data into a table

I love writing c# code using visual studio and resharper, it is a really nice experience and resharper does a lot of boiler plate stuff and offers re-writes for you that mean you can concentrate on writing the useful bits of code.

“where ISNULL(column, ‘DontMatch’) = ‘Match’” - is kind of a bad thing to do in SQL as it means that any index on column can’t be used.

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… Duplicate indexes, one of the most pointless things you can do to a table that just adds pure overhead.

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… In-line TVF’s are awesome, they are a great way to reuse T-SQL code without the performance penalties of multi line TVF’s or horror of horrors Scalar…

New ScriptDom visualizer now also shows the tokens that are found in a sql script.

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… Bert likes his T-SQL keywords in UPPERCASE, Ernie likes his in lowercase which causes some internal struggle as it is actually one person “Mr Bert Er…

SSDT is great but one thing that is not so great is the performance of the publish.

The ScriptDom is cool, it lets you parse T-SQL, play around with the AST (tree of statements) and then generate T-SQL again (probably in a different format etc).

When developing stored procedures in SSDT it is important to know when you have written a query that is potentially slow or has a high IO or CPU cost but there is no real way other than examining the the tables you are querying and understanding the data…

I have released another tool as part of the ssdt dev pack, what this does is create tSQLt classes and stub tests.

Automatically name primary key constraints There are some things you see when writing t-sql code and schemas that just look sloppy, one of those is unnamed constraints, what you see is: create table t( id int not null primary key ) The problem with this is

I have made some changes to the way MergeUi works and also moved it into a new repository (https://github.com/GoEddie/SSDT-DevPack).

What is the ScriptDom?

I have made a few ui changes to this to make it a little more usable, I can’t really think of any new features so if anyone has any ideas let me know :) New version (slightly prettier): https://github.com/GoEddie/ScriptDomVisualizer/blob/master/release/Sc…

This is probably the last contributor that I am going to add to the DacFx deployment contributors project (https://github.com/DacFxDeploymentContributors/Contributors/) for a little while, if anyone else has one they want to contribute please feel free.

Another day another deployment contributor for the project: https://github.com/DacFxDeploymentContributors/Contributors.

I have added my first actual deployment contributor to my deployment contributor project: https://github.com/DacFxDeploymentContributors/Contributors The idea is basically for people to share ideas about writing deployment contributors for SSDT and help e…

Writing code to generate TSql using the ScriptDom is hard.

One of the best features of SSDT is deployment contributors.

I have been using SSDT seriously for a couple of years now and have really been impressed with the code that is generated to upgrade schemas and over that time have learnt to trust that SSDT generates good upgrade scripts.