Ed Elliott's Agile SQL Club

Runing tSQLt Tests with Claude

Wed, 14 Jan 2026 19:35:27 +0000

Running tSQLt unit tests is great from Visual Studio but my development workflow isn’t just write tests, run tests, fix tests, run tests anymore, it is 2026 and I am a big fan of using claude code to help me write more efficiently than I used to be with just a keyboard and mouse.

I was using claude and asked it to write tests, there were some errors and so I had to paste the error over to claude, let claude diagnose and then I had to rerun the tests and my first reaction was to wonder why I am even involved in this process? It should be that I tell claude what to do, it writes the code and the tests and then runs the tests to check it all works, there is no reason for me to copy and paste anything.

tSQLt Test Adapter for Visual Studio 2022+

Tue, 30 Dec 2025 14:56:27 +0100

I have had a number of requests for me to update the tSqlt Test Adapter over the years so it would work with more recent versions of Visual Studio. I finally got around to doing this and I am pleased to say that the new version works with VS 2022, 2025 and should continue to work in future releases.

Between Visual Studio 2017 and 2019 the changes required were minimal but between 2017 and 2022 the changes meant a rewrite of the test adapter visual studio integration parts which meant it wasn’t a simple change.

Github Copilot in SSMS can include data in its memory, simple prompt injections ahead

Sat, 15 Nov 2025 09:00:00 +0000

SQL Server Management Studio (SSMS) has recently added support for Github Copilot. This is a great feature that can help with writing SQL queries and scripts sql development. However, there is a potential security risk that means that I will only be using it for local development with a database that has no externally provided data.

When evaluating GitHub copilot I think there are three core risks:

Destructive commands being run for me without me asking - I don’t want to be the first person whose copilot broke something.
Sensitive/PII data being read and sent to GitHub or somewhere else.
Prompts in the data causing malicious code to be generated.

The first one is most scary, if I ask a question like “I need to speed up this database quickly”, what is to stop copilot from deleting the data? That would fulfil the request quickly. I am told that the connection that copilot uses is read only so this cannot happen but there is no visibility of this. What I really need here is to be able to configure the connection so I can force the connection to be read only (likely using a lower privileged user).

Delta Lake over Spark Connect

Wed, 18 Jun 2025 20:10:20 +0100

All Spark Connect Posts

I have just finished an update for the spark connect dotnet lib that contains the DeltaTable implementation so that we can now use .NET to maintain delta tables, over and above what we get out of the box by using DataFrame.Write.Format("delta"), this is an example of how to use the delta api from .NET:

    var deltaTable = DeltaTable.ForPath(spark, deltaPath);
    deltaTable.History().Show(10, 1000);

    deltaTable.Update("id < 10", (Col("id"), Lit(0)));
    
    deltaTable.ToDF().Show(20);

    var source = spark.Range( 5).WithColumn("name", Lit("teddy")).Alias("src");
    deltaTable
        .As("tgt")
        .Merge(source, "src.id = tgt.id")
        .WithSchemaEvolution()
        .WhenNotMatchedInsertAll()
        .WhenMatchedUpdateAll()
        .Execute(spark);
    
    deltaTable.ToDF().Show(50);

    deltaTable.RestoreToVersion(1);
    
    deltaTable.ToDF().Show(50);
    deltaTable.Delete(Lit(true));
    deltaTable.ToDF().Show(50);
    
    deltaTable.Detail().Show();

    var newDeltaTable = DeltaTable
        .CreateOrReplace(spark)
        .TableName("deltatablefuntest")
        .AddColumn(
            new DeltaTableColumnBuilder("col_a")
                .DataType("int")
                .Build()
        )
        .AddColumn(
            new DeltaTableColumnBuilder("col_b")
                .DataType("int")
                .GeneratedAlwaysAs("1980")
                .Nullable(false)
                .Build()
        )
        .Execute();
    
    newDeltaTable.ToDF().Show();
    
    var sourceDataFrame = spark.CreateDataFrame((new List<(int, long)>()
    {
        (1, 1980), (2, 1980), (3, 1980), (4, 1980), (5, 1980), (6, 1980)
    }).Cast<ITuple>(), new StructType((List<StructField>) [new StructField("this_is_cola", new IntegerType(), false), new StructField("colb", new BigIntType(), false)])).Alias("source");

    sourceDataFrame.Show();
    newDeltaTable.As("target")
        .Merge(sourceDataFrame, "source.this_is_cola = target.col_a")
        .WhenMatchedUpdate(
            (Col("target.col_a"), Col("source.this_is_cola")), 
            (Col("target.col_b"), Col("source.colb")))   
        .WhenNotMatchedInsert(
            (Col("target.col_a"), Col("source.this_is_cola")), 
            (Col("target.col_b"), Col("source.colb")))
        .WithSchemaEvolution()
        .Execute(spark);
    
    newDeltaTable.ToDF().Show();
    
    newDeltaTable.Optimize().ExecuteCompaction();
    
    newDeltaTable.Vacuum();

There isn’t really too much to say, the only thing worth pointing out is how nice the delta extensions work with spark, it was really super easy to implement the functions once I had figured out the first one.

ADF: Publish suddenly includes everything where it used to be incremental changes since the last publish

Sun, 15 Jun 2025 09:10:20 +0100

I recently encountered an interesting issue with ADF where the publish feature suddenly attempted to republish every single object, claiming they were new, despite having incrementally published changed objects for some time.

We were using the publish feature where you work on a branch until you are happy, then you raise a PR to main, merge to main, and then switch back to ADF and click publish to push the changes to the adf_publish branch.

Spark Connect Dotnet November 2024 Where are we?

Sat, 16 Nov 2024 09:00:00 +0000

All Spark Connect Posts

Introduction

There have been quite a few changes in the last couple of months and I just wanted to give a quick update on the current state of the project. In terms of usage I am starting to hear from people using the library and submitting pr’s and requests so although usage is pretty low (which is expected from the fact that the Microsoft supported version usage wasn’t very high) it is growing which is interesting.

Spark Connect Dotnet Variant Data Type

Sat, 12 Oct 2024 09:00:00 +0000

All Spark Connect Posts

I recently published the latest version of the Spark Connect Dotnet library which includes support for the new Variant data type in Apache Spark 4.0 here. One of the new features of Spark 4.0 is the Variant data type which is a faster way of processing Json data (see here).

Sample data

For this post I used a copy of the sample data from Adobe (https://opensource.adobe.com/Spry/samples/data_region/JSONDataSetSample.html).

Creating the Variant Column

The first thing we need to do is get the data into spark, normally you would have a file and do a spark.read on he json file, in this example I will have a hard coded string and use CreateDataFrame:

Delta Lake from .NET in a Spark Connect gRPC world

Sat, 17 Feb 2024 10:00:00 +0000

UPDATE - I have implemented delta in spark-connect-dotnet

All Spark Connect Posts

Code

What to do?

At some point we will want to do something with delta lake and so I wanted to explore the options. Before we do that there is a little explaining to do about delta lake and Spark. There are two completely separate sides to this, the first is getting Spark to read and write in delta format and the second is performing operations on the factual files directly without using Spark, operations like Vaccum etc.

Apache Spark from PHP - it is not just a .NET thing

Sat, 10 Feb 2024 07:00:00 +0000

All Spark Connect Posts

Code

Goal of this post

I wanted to explore what the Spark Connect API looked like from other languages, I am not a php developer - I used it a long time ago and read up on some of the modern changes but apologies if I insult any php-ers! I will say that I quite like php.

Setup

The instructions are from https://grpc.io/docs/languages/php/quickstart/ with an extra step. There are a load of dependencies that we will need in the example repo so I thought the simplest way to get this running was to get the current php gRPC example running and then add my code as an extra example in-place and then use the existing dependencies to build and run this code.

Implementing functions and more fun in Spark Connect using gRPC and .NET

Sat, 10 Feb 2024 07:00:00 +0000

All Spark Connect Posts

Code

Goal of this post

The goal of this post is to look at creating a SparkSession and a DataFrame that will wrap the Range relation and then we will use the WithColumn function to add a column to the DataFrame and then we will use the Show function to show the DataFrame.

We won’t have a builder but we are moving towards:

var spark = SparkSession
    .Builder
    .Remote("http://localhost:15002")
    .GetOrCreate();

var dataFrame = spark.Range(1000);
dataFrame.Show();

var dataFrame2 = dataFrame
    .WithColumn("Hello",
        Lit("Hello From Spark, via Spark Connect"));

I’m pretty much going to leave the code as-is from the previous post but will move things about a bit and add a SparkSession and a DataFrame class. Also, instead of passing the session id and client around i’m going to wrap them in the SparkSession so that we can just pass a single object and also use it to construct the DataFrame so we don’t even have to worry about passing it around.

Moving towards the DataFrame API using the Spark Connect gRPC API in .NET

Mon, 05 Feb 2024 07:00:00 +0000

All Spark Connect Posts

Code

Goal of this post

So there are two goals of this post, the first is to take a look at Apache Arrow and how we can do things like show the output from DataFrame.Show, the second is to start to create objects that look more familiar to us, i.e. the DataFrame API.

I want to take it in small steps, I 100% know that this sort of syntax is possible:

Exploring the Spark Connect gRPC API more

Thu, 01 Feb 2024 07:00:00 +0000

All Spark Connect Posts

Code

Goal of this post

In this post we will continue looking at the gRPC API and the AnalyzePlan method which takes a plan and analyzes it. To be honest I expected this to be longer but decided just to do the AnalyzePlan method. There are a few more API’s like ReleaseExecute, InterruptAsync, and ReattachExecute that I was going to cover but changed my mind so consider this part of the last post :).

Exploring the Spark Connect gRPC API

Fri, 26 Jan 2024 09:00:00 +0000

All Spark Connect Posts

Code

Goal of this post

In the first two posts, we looked at how to run some Spark code, firstly against a local Spark Connect server and then against a Databricks cluster. In this post, we will look more at the actual gRPC API itself, namely ExecutePlan, Config, and AddArtifacts/ArtifactsStatus.

`SparkConnectService.SparkConnectServiceClient`

The way we call the API is using the SparkConnectServiceClient, when we take the .proto files from the Spark repo, and we add them to a visual studio with the Protobuf build action that comes from the Grpc.Tools NuGet package then the SparkConnectService is generated as C# code.

Using Spark Connect from .NET to run Spark jobs on Databricks

Tue, 23 Jan 2024 09:00:00 +0000

All Spark Connect Posts

Goal of this post

This post aims to show how we can create a .NET application, deploy it to Databricks, and then run a Databricks job that calls our .NET code, which uses Spark Connect to run a Spark job on the Databricks job cluster to write some data out to Azure storage.

In the previous post, I showed how to use the Range command to create a Spark DataFrame and then save it locally as a parquet file. In this post, we will use the Sql command, which will return a DataFrame or, in our world, a Relation. We will then pass that relation to a WriteOperation command, which will write the results of the Sql out to Azure storage.

Spark Connect Dotnet First Major Milestone

Wed, 17 Jan 2024 09:00:00 +0000

All Spark Connect Posts

When I wrote the spark-connect-dotnet lib I didn’t envisage that I would implement every function, instead it would be a combination of implementing the most common functionality and showing people how they can make their own gRPC calls to call the rest of the functions but what I found is that actually implementing the functions once I had figured out the shared functionality was pretty easy and by implementing all of the functions I was able to get the supporting functions like collecting data back through arrow working.

Using Spark Connect from .NET

Wed, 17 Jan 2024 09:00:00 +0000

All Spark Connect Posts

Introductory Ramble

Spark Connect

In July 2022, at the Data and AI summit, Apache Spark announced “Spark Connect,” which was a way of connecting to Apache Spark using the gRPC protocol rather than using the supplied Java or Python APIs. I’ve been using Spark since 2017 and have been completely obsessed with how transformative it has been in the data processing space.

Me

Over the past couple of decades working in IT, I have found a particular interest in protocols. When I was learning how MSSQL worked, I spent a while figuring out how to read data from disk via backups rather than via the database server (MS Tape Format, if anyone cared). I spent more time than anyone should learning how to parse TDS (before the [MS-TDS] documentation was a thing)—having my head buried in a set of network traces and a pencil and pen has given me more pleasure than I can tell you.

Spark Connect

Mon, 01 Jan 2024 00:00:00 +0000

Spark Connect

Useful Videos

If this doesn’t get you excited then nothing will it is, of course, suitable for work, it is a talk about Spark Connect.

Spark Connect From .NET

https://the.agilesql.club/2024/01/using-spark-connect-from-.net/

https://the.agilesql.club/2024/01/using-spark-connect-from-.net-to-run-spark-jobs-on-databricks/

All Spark Connect Posts

Privacy Policy

Wed, 17 Mar 2021 05:38:00 +0000

The Agile SQL CLub Privacy Policy (google Made me do it)

The website uses cookies to track views and where you go, if you block cookies then they will be stopped. The reason for tracking is to see which posts are popular and where a really old post is still popular and is out of date I can put a note on it to say so.

I collect no information about you.

ADF: Error trying to debug pipeline: BadRequest

Fri, 12 Mar 2021 21:10:20 +0100

I made a mistake recently when I was creating an ADF pipeline, annoyingly I made loads of changes and then clicked the debug button, when I pressed debug the pipeline failed to start and I was presented with this little beaut of an error message:

The pipeline was quite complicated and so I didn’t know exactly what was causing it so I went through the usual ADF troubleshooting steps (save all then refesh the web page) that didn’t help. The thought of doing the “binary chop” on my pipeline was not fun especially as I use a macbook and ctrl+z doesn’t undo (nor does cmd+z) annoyingly.

ADF: Querying JSON documents

Fri, 26 Feb 2021 02:10:20 +0100

In my previous blog post I talked about how to read from an XML Webervice and use xpath to query the XML on the expressions side of things. You can read the XML article here (https://the.agilesql.club/2021/02/adf-xml-objects-and-xpath-in-the-expression-language/). Now, what if we don’t have XML but have JSON? Well well indeed, what if there was a way to query JSON documents using a query, imagine if you will a JSONQuery where you can pass a similar query to an xpath query to retrieve specific values from the JSON document. That would be awesome wouldn’t it? Well it isn’t available here, the is no support fpr JSONQuery in ADF. However, we can create an xml document from a JSON document and then query that, if we are very very careful about how we pass the JSON document to the xml function.

ADF, XML objects and XPath in the expression language

Fri, 12 Feb 2021 06:30:00 +0100

When you use ADF, there are two sides to the coin. The first is the data itself that ADF does very well, from moving it from one site to another to flattening JSON documents and converting from CSV to Avro, to Parquet, to SQL is powerful. The other side of the coin is how ADF uses data as variables to manage the pipeline, and it is this side of the coin that I wish to talk about today.

Synapse Analytics and .NET for Apache Spark Example 4 - JOINS

Mon, 25 Jan 2021 19:10:00 +0100

This is a bit of a longer one, a look at how to do all the different joins and the exciting thing for MSSQL developers is that we get a couple of extra joins (semi and anti semi oooooooh).

T-SQL

SELECT * FROM 
    chicago.safety_data one 
INNER JOIN chicago.safety_data two ON one.Address = two.Address;

Spark SQL

SELECT * FROM 
    chicago.safety_data one 
INNER JOIN chicago.safety_data two ON one.Address = two.Address

DataFrame API (C#)

var dataFrame = spark.Read().Table("chicago.safety_data");
dataFrame.Alias("one")
            .Join(
                    dataFrame.Alias("two")
                    , Functions.Col("one.Address") == Functions.Col("two.Address")
                    , "inner").Show();

T-SQL LEFT OUTER JOIN

Spark SQL

ELECT * FROM 
    chicago.safety_data one 
LEFT OUTER JOIN chicago.safety_data two ON one.Address = two.Address

DataFrame API (C#)

var dataFrame = spark.Read().Table("chicago.safety_data");;
dataFrame.Alias("one")
            .Join(
                    dataFrame.Alias("two")
                    , Functions.Col("one.Address") == Functions.Col("two.Address")
                    , "left_outer").Show();  //alternative "left" or "leftouter"

T-SQL RIGHT OUTER JOIN

SELECT * FROM 
    chicago.safety_data one 
RIGHT OUTER JOIN chicago.safety_data two ON one.Address = two.Address;

Spark SQL

SELECT * FROM 
    chicago.safety_data one 
RIGHT OUTER JOIN chicago.safety_data two ON one.Address = two.Address

DataFrame API (C#)

var dataFrame = spark.Read().Table("chicago.safety_data");;
dataFrame.Alias("one")
            .Join(
                    dataFrame.Alias("two")
                    , Functions.Col("one.Address") == Functions.Col("two.Address")
                    , "right_outer").Show();   //alternative "right" or "rightouter"

T-SQL FULL OUTER JOIN

SELECT * FROM 
    chicago.safety_data one 
FULL OUTER JOIN chicago.safety_data two ON one.Address = two.Address;

Spark SQL


SELECT * FROM 
    chicago.safety_data one 
FULL OUTER JOIN chicago.safety_data two ON one.Address = two.Address

DataFrame API (C#)

var dataFrame = spark.Read().Table("chicago.safety_data");
dataFrame.Alias("one")
            .Join(
                    dataFrame.Alias("two")
                    , Functions.Col("one.Address") == Functions.Col("two.Address")
                    , "full_outer").Show(); //alternative "outer" or "fullouter"

T-SQL CROSS JOIN

SELECT * FROM 
    chicago.safety_data one 
CROSS JOIN chicago.safety_data two;

Spark SQL

SELECT * FROM 
    chicago.safety_data one 
CROSS JOIN chicago.safety_data

DataFrame API (C#)

Note that we must use CrossJoin rather than Join when we don’t pass in a list of columns to join on.

Synapse Analytics and .NET for Apache Spark Example 3 - CTE()

Tue, 19 Jan 2021 17:10:00 +0100

The next example is how to do a CTE (Common Table Expression). When creating the CTE I will also rename one of the columns from “dataType” to “x”.

T-SQL

WITH CTE(x, dataType, dataSubType)
AS
(
  SELECT dateTime, dataType, dataSubType FROM chicago.safety_data
)
SELECT * FROM CTE;

Spark SQL

WITH CTE 
AS 
    (SELECT dateTime as x, dataType, dataSubType FROM chicago.safety_data)

SELECT * FROM CTE

DataFrame API (C#)

The DataFrame example is a bit odd - by creating a data frame with the first query we have the CTE that we can use:

Synapse Analytics and .NET for Apache Spark Example 2 - ROW_NUMBER()

Mon, 18 Jan 2021 21:10:00 +0100

The next example is how to do a ROW_NUMBER(), my favourite window function.

T-SQL

SELECT 
	*, ROW_NUMBER() OVER(ORDER BY dateTime)  as RowNumber
FROM 
    chicago.safety_data

Spark SQL

SELECT 
	*, ROW_NUMBER() OVER(ORDER BY dateTime)  as RowNumber
FROM
    chicago.safety_data

DataFrame API (C#)

var dataFrame = spark.Read().Table("chicago.safety_data");

var window = Microsoft.Spark.Sql.Expressions.Window.OrderBy("dateTime");
dataFrame = dataFrame.WithColumn("RowNumber", Functions.RowNumber().Over(window));
dataFrame.Show();

To see this in action, please feel free to deploy this repo to your Synapse Analytics repo: https://github.com/GoEddie/SynapseSparkExamples

Synapse Analytics and .NET for Apache Spark Example 1 - Group By

Mon, 18 Jan 2021 20:10:20 +0100

I have been playing around with the new Azure Synapse Analytics, and I realised that this is an excellent opportunity for people to move to Apache Spark. Synapse Analytics ships with .NET for Apache Spark C# support many people will surely try to convert T-SQL code or SSIS code into Apache Spark code. I thought it would be awesome if there were a set of examples of how to do something in T-SQL, then translated into how to do that same thing in Spark SQL and the Spark DataFrame API in C#.

How to get data in a DataFrame via .NET for Apache Spark

Sun, 17 Jan 2021 20:10:20 +0100

When I first started working with Apache Spark, one of the things I struggled with was that I would have some variable or data in my code that I wanted to work on with Apache Spark. To get the data in a state that Apache Spark can process it involves putting the data into a DataFrame. How do you take some data and get it into a DataFrame?

This post will cover all the ways to get data into a DataFrame in .NET for Apache Spark.

Git in 10 commands to do 99% of gitting and keeping out of trouble

Tue, 12 Jan 2021 19:10:20 +0100

Git is hard, probably harder than it needs to be but I have been using it for about 5 years and have a workflow that works for me. I use git from a command line and have learnt how to use these ten commands. If I need to deal with a merge conflict, I use VS Code and the gitlens extension. If I need to do anything else then I probably copy out the files I want to keep, reset my local repo or delete it and clone a new repo then paste back the files I want to include in the change.

TF-IDF in .NET for Apache Spark Using Spark ML v2

Sat, 19 Dec 2020 17:10:20 +0100

Spark ML in .NET for Apache Spark

Apache Spark has had a machine learning API for quite some time and this has been partially implemented in .NET for Apache Spark.

In this post we will look at how we can use the Apache Spark ML API from .NET. This is the second version of this post, the first version was written before version 1 of .NET for Apache Spark and there was a vital piece of the implementation missing which meant although we could build the model in .NET, we couldn’t actually use it. The necessary functionality is now available and so I am updating the post. To see the previous version go to: https://the.agilesql.club/2020/07/tf-idf-in-.net-for-apache-spark-using-spark-ml/

Blog .NET for Apache Spark UDFs Missing Shared State

Thu, 29 Oct 2020 19:10:20 +0100

The Problem

When you use a UDF in .NET for Apache Spark, something like this code:

class Program
{
    static void Main(string[] args)
    {
        var spark = SparkSession.Builder().GetOrCreate();
        
        _logging.AppendLine("Starting Select");
        
        var udf = Functions.Udf<int, string>(theUdf);
        
        spark.Range(100).Select(udf(Functions.Col("id"))).Show();
        
        _logging.AppendLine("Ending Select");
        
        Console.WriteLine(_logging.ToString());
    }

    private static readonly StringBuilder _logging = new StringBuilder();
    
    private static string theUdf(int val)
    {
        _logging.AppendLine($"udf passed: {val}");
        return $"udf passed {val}";
    }
}

Generally, knowing .NET we would expect the following output:

TF-IDF in .NET for Apache Spark Using Spark ML

Tue, 07 Jul 2020 17:10:20 +0100

Last Updated: 2020-10-18

NOTE: What you read here was before .NET for Apache Spark 1.0 which includes everything we need to do this purely in .NET - in this post you will see an example that is no longer necessary for TF-IDF, instead view: https://the.agilesql.club/2020/12/spark-dotnet-tf-idf.

Spark ML in .NET for Apache Spark

Spark is awesome, .NET is awesome, machine learning (ML) is awesome, so what could be better than using .NET to write ML code in Spark?

Approaches to running Databricks ETL code from Azure ADF

Wed, 03 Jun 2020 20:29:20 +0000

Databricks is fantastic, but there is a small issue with how people use it. The problem is that Databricks is all things to all people. Data scientists and data analysts use Databricks to explore their data and write cool things. ML engineers use it to get their models to execute somewhere. Meanwhile, the cool kids (data engineers obviously) use it to run their ETL code. Some use cases favour instant access to “all the datas”, some favour rigorous engineering discipline so when we look at Databricks it is a case of one size does not fit all.

The four tenets of ETL testing

Fri, 06 Mar 2020 20:29:20 +0000

Every ETL pipeline is only ever as reliable as the data that the upstream system provides. It is inevitable that assumptions you make about the data you are provided will be shattered and there is absolutely nothing you can do about it.

So what can we do? Do we just accept that our pipelines will break and fix them when the CEO shouts that the figures are out or even worse if no one notices and the data is wrong for months or years? No, of course we don’t! What we do us use testing to help us.

Passing status messages and results back from Databricks to ADF

Wed, 05 Feb 2020 20:45:20 +0100

When we use ADF to call Databricks we can pass parameters, nice. When we finish running the Databricks notebook we often want to return something back to ADF so ADF can do something with it. Think that Databricks might create a file with 100 rows in (actually big data 1,000 rows) and we then might want to move that file or write a log entry to say that 1,000 rows have been written.

Spark Delta Lake, Updates, Deletes and Time Travel

Mon, 20 Jan 2020 20:45:20 +0100

When you use delta lake there are a couple of interesting things to note based around the fact that the data is stored in parquet files which are read-only and delta lake includes the ability to delete and update data and view the state of a table at a specific point in time. Obviously read-only and updates and deletes don’t exactly sound like they work together, so how does it all work and what do we need to be aware of?

Validating upstream data quality in ETL processes, SQL edition

Mon, 28 Oct 2019 14:29:20 +0000

It is a non-null constraint, not a non-ish-null constraint

You are writing an ETL process, part of this process you need to import a semi-structured file (think CSV, JSON, XM-bleurgh-L, etc.) when you import the data into an RDBMS you get all sorts of things that make schema designers excited like unique constraints and check constraints. The problem is that the file you are importing is from another system and all “other” systems in the world make mistakes and changes and send you duff data that won’t work with your lovely constraints. The problem we have is that RDBMS’s apply to entire column at one time. This columnar approach to constraints means that, although you might have nine hundred and ninety nine thousand rows that will pass the constraints, if you have even one, single solitary, row that fails then the whole constraint fails. It is a non-null constraint, not a non-ish-null constraint

SQLCover 0.5 - Fixes, smaller features and an exciting surprise

Wed, 16 Oct 2019 19:21:20 +0000

It has been a little while but I have updated SQLCover to include a number of fixes and small features, the majority of which are improvements to the html output:

For full details and to download the latest version see:

https://github.com/GoEddie/SQLCover/releases/tag/0.5.0

https://www.nuget.org/packages/GOEddie.SQLCover/0.5.0

If you get any issues please comment below or raise an issue on github.

Highlights

Cobertura

Cobertura is a format for code coverage tools, Azure DevOps supports cobertura files to display code coverage results alongside the build so this is a really nice thing to be able to have, if you use SQLCover in your Azure DevOps builds (or any ci server that supports Cobertura files) then you can use the Cobertura output to generate this:

How to test ETL Processes in production

Wed, 02 Oct 2019 03:29:20 +0000

This is the final part in the four-part series into testing ETL pipelines, how exciting!

Part 1 - Unit Testing https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelines-part-one-unit-tests/
Part 2 - Integration Testing https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-processes-are-correct-how-do-we-make-sure-upstream-changes-dont-break-our-processes-and-break-our-beautiful-data/
Part 3 - Validating the upstream data https://the.agilesql.club/2019/09/how-do-test-the-upstream-data-is-good-in-an-etl-process-etl-testing-part-3/

This final part is the last step, you have documented your business logic with unit tests, you have validated your pipeline with sample data (good and bad data), you have a step in your pipeline to ensure the upstream data meets your expectations and you have deployed the code to production where, AND ONLY where, you can be confident the code works. Now you have code in production you need to do the final type of testing, “testing in production”.

How to connect spark to ms sql server without '[Error] [JvmBridge] java.sql.SQLException: No suitable driver'?

Tue, 01 Oct 2019 03:29:20 +0000

“[Error] [JvmBridge] java.sql.SQLException: No suitable driver” - unable to connect spark to Microsoft SQL Server.

In spark when you want to connect to a database you use Read() passing in the format “jdbc” and include the options of url, driver and either dbtable or query.

DataFrame dataFrame = spark.Read()
.Format("jdbc")
.Option("url","jdbc:sqlserver://localhost;databaseName=dName;")
.Option("user", "user_name")
.Option("password", "password or secret")
.Option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
.Option("dbtable", "schema.table_name")
.Load();

The url tells jdbc that we want to connect to sqlserver (jdbc:sqlserver) and then the details of the server to connect to. For spark to be able to find a driver for “sqlserver” you need to do two things, firstly you need to pass the jar to the driver to spark and secondly pass in the name of the driver that can implement a connection to “sqlserver”.

How do test the upstream data is good in an etl process (etl testing part 3)?

Mon, 30 Sep 2019 15:21:20 +0000

In the first two parts of this series (https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelines-part-one-unit-tests/ and https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-processes-are-correct-how-do-we-make-sure-upstream-changes-dont-break-our-processes-and-break-our-beautiful-data/), I talked about how to unit test your business logic and integration test your ETL infrastructure code. Having these tests ensures that your code is in order, it means you have documented, and future-proofed your code which is a fantastic thing to have. What testing our code doesn’t give us is a way to validate the data we receive is correct.

Visual Studio tSQLt Test Adapter

Mon, 02 Sep 2019 06:29:20 +0000

I finally got around to updating the tSQLt test adapter for visual studio, you can download it from:

https://marketplace.visualstudio.com/items?itemName=vs-publisher-263684.GoEddietSQLt2019

or the search in visual studio extensions thingy finds it as well.

For details on what this is and how it works see the original post:

https://the.agilesql.club/2016/08/tsqlt-visual-studio-test-adapter/

Help! My Apache Spark configuration on windows isn't working (I think)

Thu, 29 Aug 2019 05:45:20 +0100

Steps needed

Getting Apache Spark running on windows involves:

Installing a JRE 8 (Java 1.8/OpenJDK 8)
Downloading and extracting SPARK and setting SPARK_HOME
Downloading winutils.exe and setting HADOOP_HOME
If using the dotnet driver also downloading the Microsoft.Spark.Worker and setting DOTNET_WORKER_DIR if you are going to use UDF’s
Making sure java and %SPARK_HOME%\bin are on your path

There are some pretty common mistakes people make (myself included!), most common I have seen recently have been having a semi-colon in JAVA_HOME/SPARK_HOME/HADOOP_HOME or having HADOOP_HOME not point to a directory with a bin folder which contains winutils.

How do you debug your spark-dotnet app in visual studio?

Tue, 20 Aug 2019 14:29:20 +0000

When you run an application using spark-dotnet, to launch the application you need to use spark-submit to start a java virtual machine which starts the spark-dotnet driver which then runs your program so that leaves us a problem, how to write our programs in visual studio and press f5 to debug?

There are two approaches, one I have used for years with dotnet when I want to debug something that is challenging to get a debugger attached - think apps which spawn other processes and they fail in the startup routine. You can add a Debugger.Launch() to your program then when spark executes it, a prompt will be displayed and you can attach Visual Studio to your program. (as an aside I used to do this manually a lot by writing an __asm int 3 into an app to get it to break at an appropriate point, great memories but we don’t need to do that anymore luckily :).

How do we prove our ETL processes are correct? How do we make sure upstream changes don't break our processes and break our beautiful data?

Tue, 13 Aug 2019 05:45:20 +0100

ETL Testing Part 2 - Operational Data Testing

This is the second part of a series on ETL testing, the first part explained about unit testing, and in this part, we will talk about how we can prove the correctness of the actual data, both today and in the future after every ETL run.

Testing ETL processes is a multi-layered beast, we need to understand the different types of test, what they do for us, and how to actually implement them. The tests we are talking about here are data tests or operational testing, we are going to actually look at the real data that was produced by an ETL run and see whether the data is as we expect it to be.

spark-dotnet how to manually break a file into rows and columns

Tue, 06 Aug 2019 05:45:20 +0100

I found this question on stack overflow that went something like this:

“I have a file that includes line endings in the wrong place and I need to parse the text manually into rows” (https://stackoverflow.com/questions/57294619/read-a-textfile-of-fixed-length-with-newline-as-one-of-attribute-value-into-a-ja/57317527).

I thought it would be interesting to implement this with what we have available today in spark-dotnet. The thing is though that even though this is possible in spark-dotnet or the other versions of spark, I would pre-process the file in something else and by the time spark reads the file have it already in a suitable format. On that note, lets look at the problem.

java.lang.ClassNotFoundException: org.apache.spark.deploy.DotnetRunner with 0.4.0 of spark-dotnet

Fri, 02 Aug 2019 05:45:20 +0100

There was a breaking change with version 0.4.0 that changed the name of the class that is used to load the dotnet driver in Apache Spark.

To fix the issue you need to use the new package name which adds an extra dotnet near the end, change:

spark-submit --class org.apache.spark.deploy.DotnetRunner

into:

spark-submit --class org.apache.spark.deploy.dotnet.DotnetRunner

What if I have this error but that doesn’t fix it?

When you run a spark app using spark-submit and you get a ClassNotFoundException for the driver then it boils down to either making a typo or something on your system blocking the jar from being loaded (anti-virus?).

How do we test ETL pipelines? Part one unit tests

Thu, 25 Jul 2019 04:45:20 +0100

Why do we bother testing?

Testing isn’t an easy thing to define, we all know we should do it, when something goes wrong in production people shout and ask where the tests were, hell even auditors like to see evidence of tests (whether or not they are good isn’t generally part of an audit) . What do we test, how and why do we even write tests? It is all well and good saying “write unit tests and integration tests” but what do we test? We are writing ETL pipelines, the people who run the source system can’t even tell us if the files are in CSV, JSON, or double-dutch – they certainly don’t have a schema that persists more than ten minutes.

spark-dotnet examples - reading and writing csv files

Mon, 15 Jul 2019 04:45:20 +0100

How do you read and write CSV files using the dotnet driver for Apache Spark?

I have a runnable example here:

https://github.com/GoEddie/dotnet-spark-examples

Specifcally:

https://github.com/GoEddie/dotnet-spark-examples/tree/master/examples/split-csv

Let’s take a walkthrough of the demo:

            Console.WriteLine("Hello Spark!");

            var spark = SparkSession
                .Builder()
                .GetOrCreate();

We start with the obligatory “Hello World!”, then we create a new SparkSession.


            //Read a single CSV file
            var source = spark
                            .Read()
                            .Option("header", true)
                            .Option("inferShchema", true)
                            .Option("ignoreLeadingWhiteSpace", true)
                            .Option("ignoreTrailingWhiteSpace", true)
                            .Csv("./source.csv");

Here we read a csv file, the first line of the file contains the column names so we set the option “header” to true. We want to take a guess at the schema so use the option inferSchema. Because the csv has been written with lots of whitespace like “col1, col2” - if we didn’t ignore leading whitespace our second column would be called " col2" which isn’t ideal.

spark-dotnet how does user .net code run spark code in a java vm

Sun, 14 Jul 2019 04:45:20 +0100

Apache Spark is written in scala, scala compiles to Java and runs inside a Java virtual machine. The spark-dotnet driver runs dotnet code and calls spark functionality, so how does that work?

There are two paths to run dotnet code with spark, the first is the general case which I will describe here, the second is UDF’s which I will explain in a later post as it is slightly more involved.

Spark and dotnet in a single docker container

Thu, 11 Jul 2019 04:45:20 +0100

I really like the new dotnet driver for Spark because I think it makes spark more accesable to devs who might not know pythpn or scala.

If you want to be able to build and run a dotnet application using the dotnet driver to run locally you will need:

jre
Spark
dotnet sdk

Now the JRE has some docker images and so does the dotnet sdk, but what if you want both in a single container? The simplest way to get this setup is to use both dotnet core image and jre images and create a multi-stage dockerfile. For a working example see:

Where do you do your database development? Hopefully not production

Fri, 05 Jul 2019 05:45:20 +0100

Here are three scenarios, if you work with SQL Server either as a provider of database environments (DBA) or a consumer of database environments for your application (developer) then you will likely see yourself in one of these descriptions. If you don’t please 100% find some way to tell me (email, comment below etc.)

Prod FTW’ers – There is only one place where the developers can develop. DBA’s (if you have one) complain every so often about someone using “sa” on production, whoever is using the “sa” account, keeps leaving open transactions in SSMS blocking all the users. Sometimes developers delete the wrong thing, it happens less now that they generally use a tool that warns if the where clause is missing from an update or a delete. Hopefully, not too many identify with this scenario today (past sins have already been forgiven).

As a DBA, how do I offload some of my work?

Thu, 13 Jun 2019 05:45:20 +0100

I don’t have time for this

“I am a DBA, I am busy, too busy. Developers keep pushing changes to production without me reviewing the code, and now I am stuck again, over the weekend fixing performance issues while the developers are on the beach with a pina colada and a cigar” Sound familiar? Maybe the developers aren’t on the beach drinking and smoking, but the sentiment is the same:

What is the key to automated database deployments?

Tue, 11 Jun 2019 06:45:20 +0100

In my blog post here https://the.agilesql.club/2019/06/what-steps-are-there-to-move-to-safe-automated-database-deployments/ I described the steps you need to go through so you can build up your confidence that you are capable of deploying databases using automation. I mean, afterall, knowing that it is possible to automate your deployments and having confidence that they will succeed are two very different things.

Even with the best tooling in the world, automated database deployments are still a struggle and there is one key thing that you can do, no matter what tools you choose and that is to make the deployments re-runnable. (Insert discussion here on the word idempotent and how it means re-runnable but sounds far cooler and intellectual). If you make your deployments re-runnable then you can, by their very definiton, re-run them.

What steps are there to move to safe automated database deployments?

Mon, 10 Jun 2019 06:45:20 +0100

Database deployments are scary, you have all this data and if you drop the wrong table, run the wrong delete statement or have an error in a stored procedure that forgets to write that critical piece of data then you may never truly recover from that. You may well have backups but what if your backup is corrupt? What if your stored procedure hasn’t been writing the right data for a week? Will you be able to recover that data?

How do you choose which columns to include in your SQL Server clustered index?

Mon, 03 Jun 2019 05:45:20 +0100

Database Engineering: Database Modelling: Do I need to have an incrementing identity int/bigint as my clustered index in a SQL Server database?

When you want to produce a professional table design that will scale in the future and stop you being called at 4 AM to fix a performance issue, will you regret that decision to not add an incrementing “id” column to that core table?

When you look in forums, do you see people fiercely guarding their opinion that you should/should not include an id column?

Why is my pivot query not returning any data? How to debug a PIVOT query

Tue, 21 May 2019 21:45:20 +0100

If you have a PIVOT query and it isn’t returning the data you expect, what can you do to troubleshoot it? The thing to do is to break it down into the constituent parts. First, lets take a look at a query and see what we can do to help.

If you have this query:

SELECT [1] AS Student1, [2] AS Student2, coalesce([3], 0) AS Student3
FROM 
    (SELECT student_id, amount FROM [a]) as source
    PIVOT (
            SUM(amount) FOR student_id in ([1],[2],[3])
        ) as pv

Step 1 - Do you have any actual data?

If we break this down, the first thing is we select the original data from the table here:

How to delete a lot of data from a SQL Server database

Wed, 15 May 2019 07:45:20 +0100

How to delete data efficiently

When we delete data from a table in SQL Server, we can often find that the delete is slower than even the original insert, especially if the insert was made using the bulk insert API. Often when people find that they have a delete that is taking longer than it should and blocking other users, they kill the delete, but the rollback takes just as long if not longer than the delete up until that point. They can sometimes panic and restart SQL Server, but when the server starts back up again, it puts the database into recovery mode and continues rolling back that data.

How to load test a SQL database

Wed, 08 May 2019 21:45:20 +0100

How do you know whether a database code change is going to be great, okay or awful? If you want to test a new version of SQL Server how do you know whether to leave on or disable the legacy cardinality estimator? Will the latest cumulative update destroy the performance of your application?

You don’t have to wait until production to know if the change you are going to do works or not and building a system to test the performance of your database is probably more straightforward than you realize.

Should I pluralize table names, is it Person, Persons, People or People?

Thu, 02 May 2019 04:20:15 +0000

Reading Time: 5 minutes (just do it!)

There is a lot of confusion when it comes to designing tables in SQL Server around whether to pluralize names or not. How do you choose whether to pluralize or not?

If we want to store a list of people and their details do we use “Person”, “Persons”, “People” or “Peoples”? Some people will use “People” and some will use “Person”, other persons or people would go for “Peoples” or “Persons”.

DacFx how to get the data type from a column a discussion of properties, relationships and the TSqlModel

Wed, 17 Apr 2019 21:20:15 +0000

Reading Time: 15 minutes (it is quite hard going, but you need to know this if you need to work with the DacFx)

DacFx - Getting the data type of a column on a table, a rehash of an old blog post

This post has been years in the making, I did a version of it years ago here: https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information - well fast forward 5 years to now and I can now understand how it works and whereas before I basically sat in a debugger trying to figure out what method to use. I am now pretty confident I can get to any property I need using excel rather than using a debugger and reflection, this makes me happy :)

How can we merge multiple dacpacs into one

Tue, 05 Mar 2019 19:20:15 +0000

Reading Time: 4 minutes (not a minute longer)

TLDR;

You probably don’t want to actually merge dacpac’s you probably want to deploy multiple and use /p:IncludeCompositeObjects=true.

If you do really want to merge dacpacs you can start with this sample: https://github.com/GoEddie/DacpacMerge

Why?

I was reading the dacfx issues on the dacextensions sample’s github site: https://github.com/Microsoft/DACExtensions/issues and one of the issues https://github.com/Microsoft/DACExtensions/issues/23 asked about how to merge dacpac’s and I have been asked a few times how to do this and I thought there was a sample but I couldn’t find it so I knocked a quick one up.

SSDT: Build in VSCode

Tue, 29 Jan 2019 00:00:00 +0000

Over the last couple of years I have been using VSCode more and more and Visual Studio less and less, this is great but there isn’t first class support for SSDT in VSCode, it would be great if there was but there isn’t today. This means I need to use Visual Studio to work with SSDT but often I find I just need to change a stored procedure or table and I don’t actually need the full SSDT experience, just a subset - can I change something and does it build, and can I deploy that build and run the tests?

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

Fri, 25 Jan 2019 00:00:00 +0000

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”.

minikibe on windows, getting stuck on networking issues like cant connect messages?

Tue, 09 Oct 2018 23:00:00 +0000

Reading Time 1 Minute

I have been getting kubernetes setup on my laptop via minikube and I followed all the examples to the T, especially the part about creating a new external network and referencing that when you create the minikube (go search for a tutorial on setting up minikube on windows and they all say “create an external networkin hyper-v and reference that).

The problem is, if you already have a default external network (or another external network) that also uses the same physical network card then when the minikube virtual machine starts up, hyper-v behaves “erratically”. I setup a ping to my local router and started minikube and the network kept dropping out and generally being a bit rubbish.

TDD for SQL Server using tSQLt course certificates now available

Wed, 18 Jul 2018 20:00:00 +0000

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

Fri, 06 Jul 2018 06:10:00 +0000

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 (https://twitter.com/cl) from dba tools and sqlcommunity.slack.com 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.

Deploying to Azure Without Writing JSON Arm Templates

Tue, 19 Jun 2018 22:47:00 +0000

Reading Time: 6 minutes (not a minute longer)

TLDR;

ARM templates are a great way to deploy to Azure, setting up a template that can deploy an entire environment from databases to web servers and load balancers to firewalls (damn you azure, ok, I’ll call it an nsg) is satisfying but writing and managing blobs of JSON is less satisfying. In this post, we look at what are the alternatives to writing ARM templates in JSON.

Running MySQL on a VSTS build agent

Tue, 08 May 2018 22:28:00 +0000

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. For this, I specifically wanted to run a command on the host and have MySQL exposed externally to the container which is the bit I struggled with the longest.

Database testing and ETL processes

Tue, 01 May 2018 20:07:00 +0000

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. A unit tests typically understands how one single unit of code works and so setting up data is the simplest, we just have the rows and columns we need for that test (I know i’ve repeated it, I repeat to to myself over and over and over again).

Interesting Azure ARM "Make it So" behaviour

Thu, 05 Apr 2018 06:04:00 +0000

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?

Wed, 04 Apr 2018 18:04:00 +0000

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

Mon, 05 Mar 2018 21:05:00 +0000

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

Thu, 08 Feb 2018 17:22:00 +0000

In the first part of this series, we looked at where to find out more information about GDPR in the UK (hint: The ICO.gov 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. Finally, in part 3 we looked at Keurboom Communications Ltd who deliberately used the data they had to make almost 100 million phone calls to sell PPI or accident handling – I am sure anyone who has a UK number would have received some of these, I know I certainly did and ended up getting so many at one point I changed my phone number.

tSQLt Course

Tue, 06 Feb 2018 17:43:00 +0000

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.

Re-Imagining an SSIS IDE

Sun, 04 Feb 2018 22:37:00 +0000

Re-Imagining the SSIS development environment.

oooh:

ahhh:

SSIS is a powerful and versatile tool for extracting, transforming and loading data into or out of SQL Server. The development environment, BIDS and now SSDT allow developers to create, edit and debug SSIS package. I struggle with the actual IDE. The thing that I find is that the IDE is designed around a series of dialog boxes, tabs and a graphical map of the package as a whole. SSIS packages let you embed either c# or vb.net scripts and to open one of these scripts a whole separate instance of visual studio is started and I have worked on projects where opening scripts can literally take 5 minutes.

gdpr - panic part 3

Thu, 01 Feb 2018 23:49:00 +0000

In part three of this short series

Part one is: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/GDPR-Panic-Part-1
Part two is: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/GDPR-Panic-Part-2
This is part three: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/gdpr-panic-part-3

In part two I started looking at previous enforcement action taken by the ICO, Talk Talk (3!!! times) and the historical society:

https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/GDPR-Panic-Part-2

In this part, we will start by looking at the Royal & Sun Alliance Insurance PLC from January 2017:

Royal & Sun Alliance

https://ico.org.uk/media/action-weve-taken/mpns/1625635/mpn-royal-sun-al…

The RSA has a data centre in Horsham, West Sussex - I only mention this as I live nearby.

gdpr - panic part 2

Thu, 01 Feb 2018 20:17:00 +0000

Welcome GDPR friends :)

Part one is: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/GDPR-Panic-Part-1
This is part two: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/GDPR-Panic-Part-2
Part three is: https://the.agilesql.club/blogs/Ed-Elliott/2018-02-01/gdpr-panic-part-3

In this part, I am going to have a look at previous action the ICO has taken to give some context to the scary sounding 4% turnover or millions of pounds you will receive if you do something wrong. We obviously can’t predict exactly how an enforcement action will go, but we can think about the sort of things we should be doing, before during and after a breach.

gdpr - panic part 1

Thu, 01 Feb 2018 13:57:00 +0000

GDPR is coming (or if you are reading this in a few weeks then gdpr is here, what do you need to know and where do you start?

This post is based on how gdpr will apply to the UK. I have nothing against the EU, but in the UK it is the ICO which governs GDPR. Each country is allowed to add specific requirements, and each country does so if you are looking for how GDPR applies to another country the ICO can’t help you sorry.

Azure ARM template function internals

Mon, 18 Dec 2017 19:24:00 +0000

I have been working with azure arm functions for quite a while and they are exceptionally useful but also quite a pain to work with, visualizing what it is that the functions will resolve to is often hard.

If you haven’t used an ARM template, ARM templates are JSON documents which are effectively a whole load of properties made up of a name and a value. The value can either just be a value or it can include arm template functions. If we look at this example:

xSQLServer is dead long live SqlServerDsc

Fri, 15 Dec 2017 12:45:00 +0000

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:

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

Thu, 14 Dec 2017 22:52:00 +0000

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:

My Azure ARM template development workflow

Wed, 13 Dec 2017 22:29:00 +0000

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).

ARMED - Where is the source?

Sat, 09 Dec 2017 10:45:00 +0000

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.

ARMED - ARM resource dependency graph inside VSCode

Fri, 08 Dec 2017 23:07:00 +0000

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).

ARMED - Azure ARM VS Code extension

Wed, 06 Dec 2017 22:34:00 +0000

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:

Azure Virtual Machine + Premium Disk Throughput Calculator

Sat, 02 Dec 2017 19:55:00 +0000

UPDATE: I haven’t been keeping it up to date so deprecated it - if you find something like this useful moan at microsoft and get them to implement it!

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.

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

Thu, 30 Nov 2017 13:09:00 +0000

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.

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

Wed, 22 Nov 2017 05:40:00 +0000

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).

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

Tue, 21 Nov 2017 21:22:00 +0000

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:

You have your database in source control now what?

Thu, 16 Nov 2017 08:02:00 +0000

This post is for a specific type of person if you are:

New to source control
Are getting started on your path to the continuous delivery nirvana
Have been able to get your database into some sort of source control system
Have more than one person checking in code to the database source
You are unsure what yo do next

Then this post is for you!

Choosing a source control system and tools to manage your database code is a great hurdle to have got past, well done!

Running any AzureRM powershell command tells you to "Run Login-AzureRmAccount to login." even though you are logged in!

Wed, 15 Nov 2017 12:40:00 +0000

Every AzureRM command I was running I would get an error message telling me to login, I then did a login, checked that I had the right subscription and I was still getting the error message - after logging in 7 times I figured that maybe there was something else wrong :)

It turns out that I had originally installed the Azure RM cmdlets via the Azure SDK and had somehow also managed to install the AzureRM.profile module from the powershell gallery. Having different versions of the AzureRM.profile and the rest of the cmdlets caused hilarity (well not exactly hilarity but hey ho).

Watching for powershell changes and running Invoke-Pester

Tue, 14 Nov 2017 22:16:00 +0000

It seems like more and more recently I have been writing powershell and typescript rather than c# and t-sql and there are quite a few things to like about the tools for both of these (typescript and powershell). One thing I really like with typescript and javascript in general is that it seems everything has a file system watcher so you can have your code ide, a couple of terminals and all your tests run etc in the background.

SSDT How To Fix Error SQL17502

Mon, 06 Nov 2017 06:51:00 +0000

TLDR:

If you build an SSDT project you can get an error which says:

“SQL71502: Function: [XXX].[XXX] has an unresolved reference to object [XXX].[XXX].”

If the code that is failing is trying to use something in the “sys” schema or the “INFORMATION_SCHEMA” schema then you need to add a database reference to the master dacpac:

Add a database reference to master:

Under the project, right-click References.
Select Add database reference….

A virtualized CPU forced me to eat my lunch early, every day, for weeks

Sat, 21 Oct 2017 20:59:00 +0000

I worked one particular contract where I was forced to take my lunch at 11:35 every day, and it was all virtualisations fault!

To set the scene it was a company who wasn’t really used to having developers, they had a load of SQL analysts and some mainframe developers but SQL developers writing T-SQL, C# and SSIS code was new to them. The IT management had decided that buying actual computers wasn’t necessary for development. We could use the standard PC’s and RDP onto a Windows 2008 R2 server with Visual Studio installed and work on that.

Where do you install tSQLt?

Thu, 19 Oct 2017 18:00:00 +0000

The question of where to install tSQLt is probably the most common question I get when I talk about unit testing T-SQL, so much so that I thought that it would be a good topic for a blog. I mention tSQLt in the title, but this covers all unit test code for T-SQL.

So to be more specific:

Where do you install unit tests and unit tests frameworks for SQL Server, which databases should have that code in them?

tSQLt Test Adapter for Visual Studio 2017

Tue, 03 Oct 2017 19:08:00 +0000

So Visual Studio 2017 has been released a while and I had created a version of the test adapter (which lets you run tSQLt tests from within Visual Studio with SSDT, ReadyRoll or just plan sql scripts using the test window but I held off on pushing it to the marketplace as it didn’t work with Visual Studio 2015.

I spent quite a lot of time trying to work out how to build a single vsix that worked with VS 2015 and 2017 and in the end gave up and now there are two versions, one for 2015 and one for 2017 - I am not putting any fixes into the 2015 version so I would upgrade to 2017 if you want any updates.

Learn how to unit test SQL Server T-SQL code

Tue, 05 Sep 2017 05:37:00 +0000

A free email course on how to use tSQLt including the technical aspects of writing unit tests AND the art of writing repeatable, using unit tests for even the most complicated T-SQL code

NEW UPDATE: Enroll in the course here: https://courses.agilesql.club/p/unit-testing-sql-server-code-using-tsqlt

Unit testing helps us to write better code, make rapid changes to our code and has been generally seen as a good idea for about 10 years. Writing tests for T-SQL code is made much easier by using tSQLt but there is quite a high barrier to entry both in terms of the technical skills in getting tSQLt running and also how to approach large code bases of, sometimes, unfriendly T-SQL code and taming the code with unit tests.

SSIS ForEach Enumerator File Order

Mon, 04 Sep 2017 20:46:00 +0000

I saw on slack recently a question about how the ssis file enumeraror orders (or more specifically doesn’t order) files. I have been thinking about ssis quite a lot lately and whil I am in no hurry to start using it day to day it is quite an interesting tool.

So anyway, I saw this question that went like:

“does anyone know in what order files are processed in ‘Foreach File Enumerator’?
I used to think it was alphabetically but after some testing this is not always the case?
Second part is there anyway to specify the order by say size or date?”

K-SSIS-ed

Thu, 17 Aug 2017 16:32:00 +0000

K-SSIS-ed - View any property or script in SSIS with a single click

A cross platform re-imagined IDE and SSIS package viewer with one goal to allow a developer to get to any property, including a c# or vb.net script within a single click and one second.

For support, please raise an issue: https://github.com/GoEddie/K-SSIS-ed-docs

Releases: https://github.com/GoEddie/K-SSIS-ed-docs/releases

SQLCover

Thu, 17 Aug 2017 16:32:00 +0000

SQL Cover is a tool to measure code coverage in T-SQL. This will tell you how much of your database code your unit tests are exercising.

You can read more about it:

https://the.agilesql.club/blogs/Ed-Elliott/2016-04-08/SQLCover-Code-Coverage-For-SQL-Server-T-SQL

You can get the source code from:

https://github.com/GoEddie/SQLCover

You can download it from:

https://github.com/GoEddie/SQLCover/releases/

For help feel free to ping me.

Articles / Posts about SQL Cover:

https://www.red-gate.com/blog/building/sql-cover https://documentation.red-gate.com/display/SR1/Using+SQL+Cover+with+SQL+Release+PowerShell+cmdlets http://www.jankowskimichal.pl/en/2016/09/sql-unit-tests-coverage-report/?utm_campaign=shareaholic&utm_medium=twitter&utm_source=socialnetwork

Redgate:

Redgate kindly sponsored the initial version of SQLCover and I am most greatful to David Atkinson who really helped to shape what we have ended up with.

SSDT-Dev-Pack

Thu, 17 Aug 2017 16:32:00 +0000

What is the SSDT Dev Pack

It is basically a collection of tools that I find make developing in for SQL Server in SSDT better or easier. I will never have the time on my own to do as much for t-sql that resharper does for t-sql but this is a start and maybe someone will invest in tools for t-sql developers at some point :)

The general idea is that if I want to do something and can make it re-usable then I will add it here. If anyone has any ideas for a tool that they would find useful please feel free to fork it on github and add it, or send me the details and if I have time and I think it is useful I will add it in myself.

TSQL Tuesday - Databases and DevOps

Tue, 13 Jun 2017 16:16:00 +0000

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 at 4.

We know that by having the right tooling in place we can make releases more reliable and more frequent enabling us to deploy the changes that the business want when they want them rather than every x days/weeks/months/years/decades. This outcome is best for everyone, no one loses and the path to fun and profit is that, fun and profitable.

SSDT: Unable to connect to master or target server.

Mon, 12 Jun 2017 16:27:00 +0000

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). The thing I like about the description is the way it tries to tell you what is wrong and what server you are connecting to but it fails at both and instead tells you about an unrelated error and a database name instead of a server name.

SSDT DevPack + Visual Studio 2017

Mon, 08 May 2017 17:48:00 +0000

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.

The first feature of sorts is the ability to clear the connection used by the quick deployer without having to restart visual studio.

Secondly I am pretty convinced that the main thing people use it for is the quick deploy, the tSQLt test builder and I use the keyword formatters so I have moved everything else to underneath a menu item saying “deprecated” - if anyone really wants one of those features then let me know and I will keep it but I will likely remove them at some point.

SSDT Dev in Visual Studio Code

Thu, 27 Apr 2017 21:39:00 +0000

I have been quite interested by vs code and have been using it more and more recently. I use it for all my GO (#golang FTW) work and also powershell and I have been toying with the sql tools team’s sql extension which is great. For a long time I have thought about bringing the SSDT experience to other IDE’s like Jetbrains IntelliJ but because I have been using vscode quite a lot recently and separately I have been doing more and more javascript and typescript I thought it would be interesting to see how hard it would be to write a vscode extension that lets me build dacpac’s.

Updating TSqlModels (DacFx)

Wed, 26 Apr 2017 06:41:00 +0000

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.

If you have a TSqlModel and you want to make a change to it, you have a couple of choices:

- Create a new model and copy over everything
- Use AddOrUpdateScript

AddOrUpdate can only update scripts that you add (i.e. Know the name of) so if you get the model from a dacpac you are out of luck.

My SQL Server Development Team Maturity Levels

Mon, 24 Apr 2017 16:21:00 +0000

A teams maturity shows in its choice of tools.

I have seen quite a few different development teams in wildly different environments and the single fact that really stands out is that you can tell how good a team is by the tools that they use. It isn’t always the specific choice of which tools they use, although that can be important, it is the fact that they evaluate and chose to either use or ignore new tools.

SQL Server Continuous Deployment "In a Box"

Tue, 07 Mar 2017 23:02:00 +0000

What is this?

Well if you read the name aloud “SQL Server Continuous Deployment in a box” then, if I have done my work correctly choosing the title for the blog, give a hint :)

what is the big idea?

There is really some great tooling for SQL Server - second to none really when it comes to RDBMS’s and setting up Continuous Deployment pipelines is actually pretty simple once you know which parts to plug together. I even did it in 55 minutes once (https://www.youtube.com/watch?v=9YJQTx3bPek).

ScriptDom parsing and NoViableAltExceptions

Thu, 02 Mar 2017 22:02:00 +0000

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. Because these are first chance exceptions they are being handled and it is the way that the script dom interacts with Antlr and the Lexer that they use. When you debug a program what happens is you have two processes, process one is the debuger, this starts (or attaches) to process two, the debugee.

SqlPackage Deploy Performance - IgnoreXX are not your friend!

Thu, 02 Mar 2017 16:26:00 +0000

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. To be fair I was only interested in IgnoreWhitespace but actually it turns out that those four are very closely related.

When the deploy happens, where a script in the source and target are compared the process is:

1. Loads of things we will skip
2. Any cmd variables in the scripts are replaced with their appropriate values
3. If both the scripts are null - the comparison returns true. This has to be the best for performance but the worse for functionality ;)
4. If one script is null but not the other then the comparison returns false. This actually has to be the best for comparison performance but worse for deploy performance!
5. We then get a good old fashioned String.Equals, the standard .net compare goes: if both strings are not null and the lengths are the same do a check on each byte in the strings
6. If the strings are equal we have a match, happy days no more action required

It is what happens if the strings do not match that it starts to get a bit more interesting, if the strings are not equal and any of those four ignore options are True then we then fall down into doing a further comparison but after the scripts have been normalized using the script dom and antlr which is an expensive operation in itself (this also happens to be my next topic!).

SSDT Deploy / Publish Performance

Wed, 01 Mar 2017 18:03:00 +0000

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 wanted to understand more about the publish process and what we can do to speed it up as much as possible so I did some digging and this is a randomish post about how it all works and what we can do to make it faster.

SSDT and Friends - .net meetup video

Fri, 10 Feb 2017 16:38:00 +0000

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 DBA’s so there isn’t much talk about “why you should use source control” etc as everyone in the room used source control already :)

Devops without management buy in?

Fri, 10 Feb 2017 16:20:00 +0000

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 back on all and every idea he had - there was no way he could deploy continuously.

The accepted route for devops is management buy-in, if you do not have management buy-in then you can’t do devops. I agree totally with this, I have seen how an executive can set the direction and all of a sudden hurdles that were there are now removed or being removed but what do you do when you don’t have management buy-in?

SQLCover Fixes and Download location

Fri, 30 Sep 2016 16:46:00 +0000

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

and

DeclareTableVariableStatement statements cannot be covered, so report falsely as missing coverage

I have also changed where the releases can be downloaded from to use the github releases:

https://github.com/GoEddie/SQLCover/releases

The previous version is still available but I would recommend the latest version.

Refactoring in SQL Server Data Tools - SSDT

Tue, 27 Sep 2016 19:23:00 +0000

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?

In programming , the term ‘refactoring’ essentially means taking some code and improving it without adding features and without breaking the code. When we refactor code we ideally want to make small improvements over time, using an IDE that automates as many of the tasks as possible for us.

Database Deployments in Uncontrolled Environments

Tue, 20 Sep 2016 06:16:00 +0000

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 can get this the easier many things become.

The cycle time is easy to measure - it is the time the ticket arrives in the backlog to the time it moves to the done column, if your issue tracking system can’t tell you this easily then use something else! The tickets are moved into the “Done” state when they have been deployed into production - if you do nothing but investigate and try to reduce your cycle time you will make a massive difference to your development process.

Controlling a dacpac deployment

Sun, 21 Aug 2016 21:06:00 +0000

I have been thinking quite a lot recently (ok not that much but my thinking has changed) about how to deploy dacpac’s. In particular I am talking about how to control the deployment, what tool is used to deploy the dacpac, what connection details are passed in, which of the hundreds of parameters are passed in and how.

I think that as I configured and deployed databases using dacpac’s in a number of different environments my approaches are been:

tSQLt Visual Studio Test Adapter

Wed, 17 Aug 2016 16:32:00 +0000

tSQLt Visual Studio Test Adapter

What is this?

This lets you use Visual Studio to run tSQLt tests easily. Visual Studio has a built in framework for finding and executing tests so that if you have tSQLt tests in an SSDT project for example, although this just requires you have the .sql files in source control and does not require ssdt - you can easily see and execute your tests via the Visual Studio Test Explorer window. It is also available for use in your favorite build server via the vstest.console.exe tool or if you have vsts then you can use the default test task.

DacFxed - Powershell Nugetized DacFx wrapper

Tue, 02 Aug 2016 23:17:00 +0000

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 the approach.

Problem 1 - Is the DacFx installed?

The first problem is whether the DacFx is installed, the chances are if Visual Studio or SQL Server have been installed then it will be there.

SQLCover v 0.2 - Bug fixes and Azure V12 Support

Thu, 05 May 2016 23:03:00 +0000

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).

This includes a few minor fixes but also support for SQL Azure so if you run your test in a v12 database or higher you can now get an idea of code coverage from that.

If you are interested in using this but don’t know where to start, there is a powershell script in the download (https://the.agilesql.club/SQLCover/download.php) and if you also get reportgenerator (https://github.com/danielpalme/ReportGenerator/releases/tag/v2.4.5.0):

Where do you put your business logic? In stored procedures?

Thu, 05 May 2016 19:52:00 +0000

There seems to be two trains of thought and I think this is mainly down to who and where your developers are. The first is that a stored procedure or a function is a great place to put all the business logic that an application needs. The second is that no logic should be in code deployed to SQL Server at all.

You can see these two extremes if you compare the difference between what stack overflow does (.Net/SQL Server BUT highly optimized for performance and deployments) where they have no stored procedures and embed T-SQL directly in their code to the many many thousand line procedures that we see on a daily basis, this quora answer from Kevin Kline I think is extreme but not unexpected:

How do you audit automatically generated sql deployment scripts?

Tue, 03 May 2016 16:13:00 +0000

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 at what time.

When automating deployments it really makes sense to use a tool to generate the deployment so you can concentrate on writing productive code instead of deployment scripts. How do you balance the ideal of spending less time writing deployments but also ensure you can audit what happened?

What is code coverage for?

Mon, 11 Apr 2016 06:39:00 +0000

Code coverage gives you an indication of how well tested a particular area is. It is not a measure of code quality and having a statement covered by a test does not mean that the code is accurate.

Using code coverage you can see how risky changes are to a particular area of code. If you have a high level of code coverage you can be fairly confident in changes, if you have low code coverage in an area then when you make changes you will need be careful.

SQLCover Code Coverage for SQL Server T-SQL

Fri, 08 Apr 2016 15:38:00 +0000

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

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is - the better covered with tests, the less likely you will miss issues when you make changes in those areas.

Post Deploy Scripts In Composite Dacpacs not deploying

Thu, 03 Mar 2016 17:13:00 +0000

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. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like:

Main.dbproj - actual production code and reference data
Main.UnitTests.dbproj - unit test code + references to tSQLt etc

The unit tests have a “Same Database” reference pointing to the main project so it is as if my tests were in the same project.

Where did january go?

Tue, 16 Feb 2016 16:23:00 +0000

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 things did happen, firstly I was allowed back into the friends of redgate programme - I like the redgate guys and am pleased to be back! Secondly I have been accepted into the Microsoft ALM Ranger programme which means I can help deliver some tooling and documentation as part of a team instead of beavering away by myself. I am particularly pleased about the ALM Rangers as I have been thinking about who is actually interested in better development practices and automated deployments for SQL Server and whereas I did originally think it would be SQL developers, I feel that the majority of SQL devs and especially DBA’s are more focused on other things than improving their working practices so this is hopefully a way to help more people write and deploy code for SQL Server and databases in general :)

T-SQL Code Coverage in SSDT using the SSDT Dev Pack

Wed, 13 Jan 2016 23:20:00 +0000

Code Coverage

What is code coverage?

When you write some code and then test it, how sure are you that you have tested the whole thing? Code coverage gives you an idea of how well tested a bit of code is.

If you have lots of branches in your code (not something I am advocating) it is important to make sure you test it all so we can use code coverage to get an idea of how much of a particular piece of code has been tested (or not).

What is SSDT? Part 3 - an API for me, an API for you, an API for everyone!

Thu, 07 Jan 2016 21:14:00 +0000

In the final part of this 3 part series on what SSDT actually is I am going to talk about the documented API. What I mean by documented is that Microsoft have published the specification to it so that it is available to use rather than the documentation is particularly good - I warn you it isn’t great but there are some places to get some help and I will point them out to you.

What is SSDT? Part 2 - More Reasons to Bother!

Wed, 06 Jan 2016 16:30:00 +0000

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. Just to recap for each subject on the image I give a bit of blurb and then some links to further reading.

In this part (part 2 would you believe) I will give an overview of the second major category which is that it is a deployment utility. What I mean by deployment utility is that we can take our code and deploy it to a SQL Server (hopefully that is fairly obvious).

What is SSDT all about aka Why should I bother?

Tue, 05 Jan 2016 16:53:00 +0000

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. To be clear I am just looking at the database projects version of SSDT what was originally SSDT rather than the BI tools that came with SSDT-BI and are now being merged with SSDT – Perhaps I will expand this to include those one day but probably not.

SSDT Migrating Data Without Losing It

Wed, 30 Dec 2015 00:28:00 +0000

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 that doesn’t exist and you can’t drop the data before it has bee migrated. To fix this we can use pre/post deploy scripts in SSDT. The overall process is:

T-SQL Clippy

Thu, 10 Dec 2015 22:09:00 +0000

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. T-Sql Clippy is no where near as great as resharper but hopefully starts to show what we could actually do with t-sql and ssdt :).

What is it?

There are some things that we can do using the ScriptDom and the rest of the DacFx to help people write better t-sql code, so the things it does today is:

Automatically re-write non-sargable isnulls into sargable code in SSDT

Thu, 10 Dec 2015 21:28:00 +0000

“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. It is a really simple thing that is a waste of time, fixing it is pretty simple but I see it a lot and so I thought it would be a perfect candidate for some SSDT Dev Pack t-sql re-writing :).

So the dev pack now has the ability to re-write any non-sargable IsNull statements into ones that are sargable (if you don’t know what sargable means and you are a SQL dev, look it up, it is really important!)

Find duplicate indexes in SSDT

Fri, 27 Nov 2015 12:29:00 +0000

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. No one likes them so SQL Developers do your DBA’s a favour and stop them from ever being deployed :)

This new tool for the SSDT Dev Pack adds a menu item (Tools–>SSDT Dev Pack –> Find Duplicate Indexes) what it does is scan all the projects in the solution (it doesn’t follow “this database” references to examine them, maybe a future version) and then print to the output window a list of all the duplicate indexes based on the table, the columns and included columns - I don’t check anything else so you might actually want a duplicate in some circumstances but these should be very few and far between.

Convert select statements in scripts into Inline-TVFs automatically

Wed, 25 Nov 2015 22:42:00 +0000

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 Functions. Taking select statements and turning them into in-line TVF’s is actually really really simple using the TSql ScriptDom so here is another tool for the SSDT Dev Pack that lets you highlight a select statement in SSDT and generate a TVF and replace the statement with a call to the TVF.

ScriptDomVisualizer - Now Displays TSqlParserTokens

Wed, 25 Nov 2015 17:07:00 +0000

New ScriptDom visualizer now also shows the tokens that are found in a sql script. It also highlights the part of the query that is covered by the token so it is really easy to see where you are in the script.

Honestly if you are doing anything with the T-SQL Script Dom this is really useful to help reverse engineer t-sql into ScriptDom objects :)

Grab it from:

https://github.com/GoEddie/ScriptDomVisualizer/tree/master/release

Changing case of T-SQL Keywords

Tue, 24 Nov 2015 20:44:00 +0000

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 Ernie” anyway I digress, whether you are working by yourself or in a team you sometimes want to change the case of all the keywords in some code.

If you are using SSDT you can use my new SSDT-DevPack tool to do it for you:

Quickly deploying procedure/functions from SSDT (Bypass Publish)

Tue, 24 Nov 2015 12:28:00 +0000

SSDT is great but one thing that is not so great is the performance of the publish. I am not complaining, I understand it does a lot and is well worth the wait when publishing however, when you are writing and testing code having this process really kills productivity:

1. Write code
2. Build projects
3. Publish projects
4. Wait for publish to complete
5. Check publish messages for errors
6. Test your changes

If you develop like that you will be very slow so instead I have added a “Quick Deploy” to SSDT - it is nothing new, I had one before and Dave Ballyntyne had one (http://dataidol.com/davebally/2014/03/01/ssdt-fast-deploy/) but I wanted to do a couple of things to mine, firstly add it to the SSDT-DevPack (whoop whoop) and secondly make it so you do not have to save the file to deploy any changes so it uses the Visual Studio API’s to get the contents of the current document and deploy that to a database.

Using the ScriptDom without losing comments

Tue, 24 Nov 2015 00:20:00 +0000

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). This is really cool but if you round-trip the T-SQL into an AST and back you lose some important information like comments so, if you do something like:

var original = @"create procedure [dbo].[get_employee](@employee_id int) as --ssssss

select 100;

select * from a a1 where a1.a like ‘%fff%’;
“;
var parser = new TSql120Parser(false);

SSDT DevPack - Highlight expensive queries

Sun, 22 Nov 2015 22:35:00 +0000

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 volumes and indexes whether they could be a problem.

The idea of this new tool is to highlight in either yellow or red (yellow is quite expensive, red is really expensive) any statements that have a high cost. This isn’t an actual profiler so it is important to understand what it does and its limitations.

Create stub tSQLt tests in SSDT from stored procedures

Tue, 17 Nov 2015 20:51:00 +0000

I have released another tool as part of the ssdt dev pack, what this does is create tSQLt classes and stub tests. If you open a stored procedure in ssdt and then do “Tools–> SSDT Dev Pack–> Create tSQLt Class” or you can create a keyboard mapping in “tools->options->keyboard”, what I do on my machine is map “Tools.CreatetSQLtClass” to Ctrl+K Ctrl+S - it will create a schema with the name of your stored procedure and the correct extended property for tSQLt to register it as a test class.

Automatically name primary key constraints in SSDT

Mon, 16 Nov 2015 13:23:00 +0000

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 that the primary key is given an automatic name which will look like “PK__t__3213E83F5F141958” - this means comparing database schemas for differences becomes hard as the constraints have different names.

New Version of MergeUi

Thu, 12 Nov 2015 07:41:00 +0000

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

The way MergeUi used to work was that it enumerated the schemas and tables in a project and let you create a merge statement in the post-deploy script. The problem with that is that you may want to have different versions of the table for different environments or you may want to put the merge in a different script and either reference it using :r imports or not reference it at all.

How to get started with the ScriptDom

Sat, 07 Nov 2015 21:29:00 +0000

What is the ScriptDom?

The ScriptDom is an api for taking t-sql scripts, converting them into an AST or taking an AST and generating t-sql.

What is an AST?

Read this: https://en.wikipedia.org/wiki/Abstract_syntax_tree

Think about this:

select col from tablea
select col from tablea
select col /*from not_table*/ from tablea
select –not_col
col from tablea

and then something more complicated like:

select col from (select a as col from something /*else*/) a

ScriptDom Visualizer V2

Fri, 06 Nov 2015 21:56:00 +0000

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/Scrip…

Blocking SSDT Deployments When You Dont Like Something

Fri, 06 Nov 2015 07:46:00 +0000

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. I have been pretty pleased with how they went but want to make the testing side better.

This is an example contributor that is different to all the others in that the previous contributors enumerated the list of steps and either modified one or swapped it with another but this one uses the context object that is passed in to find all changes to stored procedures and then based on the dacpac and deployment database made a decision about whether to fail the deployment or not:

SQL Server Edition aware SSDT deployment scripts

Thu, 05 Nov 2015 21:40:00 +0000

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

This one came about because I was talking to Peter Schott (b|t) over at the deployment contributor gitter room and he has dacpac he wants to deploy to different editions of sql server and enable different options when deploying to enterprise edition.

One of the things I found out is that from a contributor, although you have the source and destination models you do not have access to any information about the server other than the version. To get around this you can either pass in an arg to say which edition the compare is running against or if you leave that off then the create index statement is duplicated and wrapped on an check against @@version to determine the edition which is more t-sql than I would like but should work.

Deploy SSDT INSERTS in Batches

Wed, 04 Nov 2015 11:45:00 +0000

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 each other. If you don’t know what a deployment contributor is then you are missing a real treat. A deployment contributor lets you modify the t-sql that SSDT creates to upgrade a database from one version to the next.

ScriptDom Visualizer

Tue, 03 Nov 2015 16:27:00 +0000

Writing code to generate TSql using the ScriptDom is hard. There is a seemingly bewildering array of objects you can use and where you use different types is unclear, I think this is due to the complexity of the task, it really isn’t simple and dealing with t-sql gets complicated very quickly - to be clear the ScriptDom is a great tool and although is difficult to use is 1 million times better than writing custom parsers / generators for t-sql.

Shared SSDT Deployment Contributor Repository and Free Help

Mon, 02 Nov 2015 16:26:00 +0000

One of the best features of SSDT is deployment contributors. They are so exciting I am going to say it again with more gusto. One of the best features of SSDT is deployment contributors.

What these do is let you examine or modify the deployment script that is generated when SSDT compares a project to a database.

why is that cool?

Please imagine for a minute a scenario where you have used SSDT to generate a deployment script and you have looked at it and scoffed that you could have written a better upgrade script. Perhaps you have seen that it has done a table migration and uses a single insert to move all the rows from one table to the next. You have looked up from your script, gazed out of the window and dreamed of a world where instead of a single insert the insert happened in batches. You throw down your script, grab your keyboard from your desk and manually write the upgrade script. A few days later you need to do the same thing so again gaze out of the window dreaming of a batched insert script at which point you grab your keyboard and bash out another script.

Looking at SSDT upgrade scripts

Sun, 01 Nov 2015 22:49:00 +0000

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.

It is however a concern that a tool generates the deployment script rather than a person and to move to a continuous deployment environment we need to be sure that the tooling is better than a person. To be clear I am already in that camp but wanted to spend some time exploring what upgrade scripts get generated in which scenarios.

Dir2Dac and github2dacpac - create dacpacs from script folders or the internet

Fri, 16 Oct 2015 11:11:00 +0000

I like writing go (golang not the batch separator) and one of the things that I like is that if you want to use something from github you literally type “go get https://github.com/user/repo” and the code is downloaded and installed ready to be used.

I also like write t-sql using ssdt and sometimes I want to reference other things like tSQLt or maybe a set of admin scripts or something but unlike go, to reference them what I need to do is:

New SSDT Projects default to the latest version of SQL Server

Wed, 14 Oct 2015 16:10:00 +0000

When you create a new SSDT project it is created (at the moment) as a SQL Server 2014 project which is annoying when you are on something else.

However, if after you have created the project you import the code from an existing database and check the box “Import Database Settings” it will set it to the correct version, which is much less annoying than trying to deploy, failing and fixing it and the deploying.

Video of sqlrelay talk - build a ci pipeline in 55 minutes

Fri, 09 Oct 2015 21:39:00 +0000

My session yesterday was recorded, apart from the fact that me and mark were having a chat for about 25 minutes before it kicked off it seemed to work quite well:

Skip forward to 24:32 to get to the actual content.

The lovely Mark at SQLRelay has edited the video and put it on youtube so you miss our chat at the beginning:

https://www.youtube.com/watch?v=9YJQTx3bPek

Any questions please shout!

How do you choose a source control system for your SQL Server database

Fri, 09 Oct 2015 15:43:00 +0000

If you already have a source code repository for your app code or other databases or you have another team that uses source control then just use theirs.

If you don’t then use whatever the members of your team have used most, they all basically do the same thing and having your database under source control is a million times better than not having it under source control.

There is an exception, if you use visual source safe and I know people do still use it then use something else, it hasn’t had a release in over 10 years and in my opinion is no longer fit for purpose, especially when you consider the other free options available to you.

SQLRelay Reading - Build a CI Pipeline in 55 Minutes Talk

Fri, 09 Oct 2015 15:29:00 +0000

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).

Deployment Contributor Logger

Wed, 30 Sep 2015 15:47:00 +0000

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.

Deployment Contributor KeepTableColumns Filter

Wed, 23 Sep 2015 20:52:00 +0000

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)

Inside an SSDT Deployment Contributor

Wed, 23 Sep 2015 20:24:00 +0000

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).

Setting up a new CI server to build SSDT projects

Wed, 23 Sep 2015 10:37:00 +0000

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.

ScriptDom versioning issues

Tue, 15 Sep 2015 06:07:00 +0000

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:

Why is my tSQLt test not running?

Thu, 10 Sep 2015 15:31:00 +0000

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

Slides for my pass virtualisation virtual chapter talk on how sql fits into a containered world

Thu, 10 Sep 2015 06:31:00 +0000

The slides are up:

https://github.com/GoEddie/SQL-Server-Containers

Any questions shout!

We can do professional SQL Server deployments using SSDT

Wed, 02 Sep 2015 15:16:00 +0000

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…

Slides and Demos for SQLSupper August meeting

Wed, 26 Aug 2015 12:30:00 +0000

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 :)

I dont use the Visual Studio SSDT Publish

Wed, 19 Aug 2015 06:23:00 +0000

I see questions pretty regularly on stack overflow and the ssdt msdn forum that is some variation on “I am doing a publish through visual studio and it doesn’t quite do what I want” or “I want to publish the database when I press F5 except when I don’t want it to publish”. The second variation I particularly enjoy because I have always though that what I want is a computer that I can think something and it will do it - I guess we are a little way off but one day hopefully that will be possible.

Ignore a warning on a dacpac reference at your peril

Mon, 17 Aug 2015 15:28:00 +0000

I worked on a project where we had two SSDT projects with different versions of SQL, one 2012 and one 2008. The 2012 project referenced the 2008 project as a “this database” reference and while this seemed like it worked, the reference in solution explorer had a little warning sign and it caused nothing but problems.

The first thing was that the reference didn’t work on anyone else’s machine when they checked out the solution, the other developers had to delete and re-add the reference once., Once it had been done at least once it always worked after that which is the first odd thing.

Shared databases or private databases?

Mon, 17 Aug 2015 06:53:00 +0000

Traditionally database developers have shared a database and while this certainly made sense when everyone had a limited amount of ram and a few hundred megabytes of hard disk space it has been a while since we have had a development machines without enough resources to easily run a few copies of visual studio and SQL Server - if this isn’t the case then you really need to re-evaluate the cost of a new machine versus the cost of developers time.

AdventureworksCI Step 5 Adding tSQLt to the solution

Wed, 22 Jul 2015 22:43:00 +0000

This is part of a series on how to take the Adventureworks database and bring it in line with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.

AdventureworksCI Step 4 Pushing to GitHub

Wed, 22 Jul 2015 06:40:00 +0000

Keep your environments in-sync and your upgrade scripts fresh

Tue, 21 Jul 2015 06:38:00 +0000

Whether we use a tool to generate upgrade scripts or we write manual test scripts for database changes there are a few things that we should bear in mind to ensure we do not break existing databases.

Why is it important?

When you upgrade an application you just compile and then deploy the code in a single operation, the old code is removed and the new code put in place, simple.

Pre-Compare & Pre-Deployment Scripts to SSDT

Mon, 20 Jul 2015 15:56:00 +0000

When you use SSDT to deploy database changes you can include a pre/post deploy script which is run after the dacpac and database has been deployed. The process looks like:

1. sqlpackage.exe compares dacpac to database
2. sqlpackage.exe generates a deploy script
3. sqlpackage.exe runs pre-deployment script
4. sqlpackage.exe runs deploy script (generated in step 2)
5. sqlpackage.exe runs post-deploy script

This happens whether you get sqlpackage.exe to do the deployment or whether you generate a script for later deployment.

WaitForSql (to start)

Fri, 17 Jul 2015 13:07:00 +0000

In the spirit of having apps that do just one thing I have put a really small .net app on github that tries to create a connection to a SQL Server database and just keeps retrying until it is available:

https://github.com/GoEddie/WaitForSQL

What? Huh?

If you have a CI build and you start a new instance or database or azure db etc it can take different amounts of time to be available so this just sits trying until it can get a valid connection and you can continue your CI loveliness.

Layer a database into a SQL Server container

Wed, 08 Jul 2015 22:06:00 +0000

In my previous post on running SQL Server in a container I showed how you could use the Spoon (https://spoon.net) products to start an instance of SQL Server without having to install it: https://the.agilesql.club/Blog/Ed-Elliott/Containers/Run-SQL-Server-In-A…

In this post I am going to talk about images and how to layer them together and why SQL Server is perfect for this sort of container technology!

What is an image

An image is the files and registry keys that make up an application, the sqlserver/sqlserver2012-express image was created by taking taking all of the files and registry keys that were added to a machine during the SQL Server install. This normally happens using Spoon Studio which takes a snapshot of the system, you then do your install and configure SQL Server (or whatever app it is) however you like and then you take a post snapshot, Spoon Studio gathers all the changes and creates an image.

AdventureworksCI Step 3 Handling data with SSDT

Tue, 07 Jul 2015 18:53:00 +0000

Run SQL Server In A Container Today July 2015

Tue, 07 Jul 2015 09:33:00 +0000

Containers are one of the big things at the moment, Microsoft has recently announced that they will be bringing official support to Windows. While this is great news it is unlikely to be something most IT departments can use in the near term mainly because it takes time for everyone to get up to date with the latest version of windows, which in this case, is still to be written.

Download SSMS, SSDT or SSDT-BI

Tue, 30 Jun 2015 19:00:00 +0000

It is a constant source of frustration for some people that it is hard to download SSDT or SSDT-BI or you download the wrong thing.

This is an example of some frustration: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c82354d0-2f88-4…

Anyway, this is now sorted as there is a single page to download SSMS or SSDT:

https://msdn.microsoft.com/en-us/library/mt238365.aspx?f=255&MSPPError=-…

and SSDT and SSDT-BI:

https://msdn.microsoft.com/en-us/library/mt204009.aspx

Feel free to grab Preview or RTM versions!

Why does sqlpackage.exe not compare after the pre deployment script has run?

Wed, 24 Jun 2015 16:55:00 +0000

It isn’t a Pre compare script

When you use SSDT to deploy your dacpac, a pre deployment script lets you run some T-SQL scripts before the script which brings your database up to date with your dacpac.

The important thing to note is that the process is:

sqlpackage compares the dacpac to the database
sqlpackage generates a list of changes to make
sqlpackage generates the T-SQL statements to make those changes
sql package either creates a script for later execution or deploys the changes

The script that is created *or* is executed contains, 0 or 1 pre deployment scripts, the statements to generate the required changes and then 0 or 1 post deployment scripts.

AdventureWorksCI Step 2 From MDF to Dot Sql Files

Mon, 22 Jun 2015 21:29:00 +0000

AdventureWorksCI Step 1 planning

Fri, 12 Jun 2015 06:39:00 +0000

This is part of a series on how to take the Adventureworks database and bring it inline with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.

A reference implementation of a continuous integration pipeline for legacy SQL Server databases

Wed, 10 Jun 2015 00:00:00 +0000

What am I doing?

Creating a publically available version of the adventureworks 2012 oltp database but by putting it under source control, implementing unit tests and improving the design and code to fit modern development guidelines.

Why?

There are two aims, firstly to show how to take an existing database and safely get it into source control. Once it is under source control we can start to document how it works and write unit tests which will allow us to make changes to it knowing that we are not breaking backwards compatibility or losing any functionality.

Why is SSDT so slow?

Tue, 02 Jun 2015 18:00:00 +0000

I see the same question again and again “I am using SSDT but it takes a long time to publish changes, how do I make it faster?” or the ever useful “SSDT is too slow”.

Anyone who has used SSDT knows that doing a publish can take time, how much time typically varies depending on a number of factors:

MergeUi is on github.com

Thu, 21 May 2015 06:17:00 +0000

I have put the code for MergeUi on github now:

https://github.com/GoEddie/MergeUi

That is all.

MergUi 0.1.9

Wed, 20 May 2015 18:00:00 +0000

What is MergeUi?

A Visual Studio Addin to create and edit MERGE statements in SSDT projects.

What have you done?

I have released a new version of MergeUi, thanks to Luca Zavarella for prodding me into getting this release out and for pointing out some bugs!

This has a number of bug fixes and a new UI, the old one was pretty ugly, although this still isn’t 100% perfect but it should be more usable.

Putting the ORM debate to bed

Wed, 20 May 2015 16:41:00 +0000

Hopefully this should put the ORM debate to bed, either use an ORM or don’t but please don’t access tables directly…

There are obviously reasons behind all of this but if you follow this golden rule you will have a happy DBA and user base.

Comments:

Anonymous

May 21, 2015 - 17:28

well - no one wants a sad

well - no one wants a sad panda.

Approaches to deploying Sql Server database code

Mon, 27 Apr 2015 22:10:00 +0000

When you deploy your Sql Server code, there are two approaches, there is the manual way and the automated “compare & deploy” way.

Manual

The manual way is where you track what changes you have made and when you want to release, build a script or set of scripts which contain the updates. If you are modifying objects then you should use alter or drop/create and you need to manually create rollback scripts (should you need them).

BIML is better even for simple packages

Mon, 27 Apr 2015 21:24:00 +0000

BIML, you either love it or hate it - either you use it to totally generate hundreds of packages or a bucket load of data flow components all at the push of a button or you are perfectly happy using the SSIS editor, you can see how the package is made up and drag the little lines from one task to another and it is simple right, why bother using xml that is more complicated, you only need to create a single package with a few tasks on it, maybe a script component here or there.

Sql Saturday Talk

Sat, 25 Apr 2015 11:26:00 +0000

I had the honour of talking at the excellent Sql Saturday Exeter this morning and wanted to share my slides and say thanks to the organisers, sponsors and everyone who came along to my session - I really enjoyed it and I hope you did to.

The slides are available:

https://the.agilesql.club/assets/talks/sqlsat15/SC-SSDT-CI.pptx

Enjoy!

What Permissions do I need to generate a deploy script with SSDT?

Tue, 21 Apr 2015 21:28:00 +0000

It is sometimes useful to be able to use sqlpackage.exe to create a script that can be deployed manually or still automatically but at a later date plus you may not want to give the user or process that does this sysadmin permissions just so that it can generate a deploy script. The permissions you need in this case are:

SELECT permissions on dbo.__Refactorlog

VIEW DEFINITION at the database level

HOWTO-Get-T-SQL-Into-SSDT

Wed, 08 Apr 2015 21:45:00 +0000

Ok so you have created an SSDT project and are currently congratulating yourself on how awesome and cool you are but then you realise that your project is like an empty void, completely devoid of any emotion, love or joy - there is no schema and code in the project and you have all that lovely T-SQL sitting in a database, now how the hell do you get it into SSDT?

Generating tSQLt tests from a dacpac

Thu, 02 Apr 2015 21:37:00 +0000

UPDATE

I went and built this into my add-in for SSDT, more details https://the.agilesql.club/blog/Ed-Elliott/2015-11-17/Create-Stub-tSQLt-t… and https://the.agilesql.club/Projects/SSDT-Dev-Pack

Generating tSQLt tests from a dacpac

I use SSDT and tSQLt in my work and I am always interested in productivity tools to help speed up development and make it more streamlined, if there is something that can help us develop better or faster then we should use it.

I have been a great fan of the DacFx for a while and the sample Dacpac Explorer seems to get quite a lot of visits with one mention from ms devs internally using it as a learning reference!

SSDT Myths 1 - SSDT only works with TFS

Thu, 02 Apr 2015 16:47:00 +0000

I was reading a white paper from a Sql Server tools vendor about how to integrate their tools with SSDT and while it was very interesting there were a couple of inaccuracies and I wanted to clear one of them up and also to help people understand that although SSDT is integrated to Visual Studio it is not tied to it in any way to TFS. You can use SSDT with any source code provider such as TFS, Git, Mercurial, Svn or Source Safe etc although you really should not use source safe for anything as it is the least likely tool to keep your source safe!

Free Professional Advice on Setting up Continuous Integration for Sql Server Databases

Thu, 19 Mar 2015 21:49:00 +0000

Calling all London/Sussex Sql Developers does this sound like you?

Would you like to set up Continuous Integration or Continuous Deployment
Do you want to get your databases under source control
Have a high code coverage of your app but zero tests for your database code?

Maybe you have thought how good it would be but you are a little lost at the options available, do you use SSDT and SqlPackage to deploy or SSMS with the Redgate source control addin, how do you deploy your code to different environments safely?

Unit Test Sql Server Code

Tue, 03 Mar 2015 18:25:00 +0000

In part 3 of this series of blogs on “getting more agile” I am going to look at unit testing and why we need to do it.

My code is perfect, I need no stinking tests

Ah you are like me, my code is perfect as well, good point. blog end.

actually…your code may well be perfect but:

a) It only got perfect by you writing the code and writing a test script to make sure it worked (you did at least test it once, didn’t you?)
b) Everyone else in the world (basically everyone other that you and me who are the only people, dear reader, who write perfect code) write horrible code and do terrible things like change table and column definitions, the blighters
c) Those other horrible developers and DBAs need examples of how to use the code
d) They also need documentation on how it works, what it returns in all cases and what side effects it has as they are too simple to work it out for themselves, the welps

ok, I admit it, I am one of those welps - what is more, I make changes to code without examining every stored procedure, every function, every piece of application code to see if it will cause any issues - I am literally a renegade throwing caution to the wind plus I write very little documentation, I certainly never explain what I have done to a stored procedure, I don’t need to. I can do this because I write unit tests and make sure that every piece of code that can run is covered by a unit test.

Introducing MergeUI

Sun, 01 Mar 2015 00:07:00 +0000

What is it?

MergeUi is a addin (VSPackage) for Visual Studio / Sql Server Data Tools projects. It is a ui to create and edit T-Sql merge statements in pre and post deployment scripts.

In this screen shot you can see the merg statement that is generated using the data in the grid which is based on a table created using the metadata in the dacpac about the table:

Source control isnt just a backup

Thu, 19 Feb 2015 23:49:00 +0000

In the second part of this series on getting more agile and bringing our Sql Server development practices up to date I am going to talk about why you should get your database under source control.

First a little rambling story, I was at the Redgate Sql in the city event in London last October and the speakers asked a few times who had their databases under source control, betwen 5 and 10 percent of the people there had their databases under source control. One speaker asked “who uses source control”, the person sitting next to me didn’t put his hand up and then the speaker asked “who has their databases under source control”, the gentleman then said he thought the first question was about databases, of course he had his code under source control, he would be stupid not to!

COOL Projects!

Mon, 16 Feb 2015 17:38:00 +0000

K-SSIS-ed

A re-envisioned IDE for SSIS packages, jump here

SSDT Dev Pack

A few helpers to make using SSDT slightly better, the killer feature for me is quickly deploying the proc or view you are looking at to a database - this means you can write code, quick deploy it and run your tests - red, green, refactor for T-SQL! jump to it

SQLCover

Code coverage reporting for T-SQL, ideally you would use it with tSQLt but works with anything, grab it from: here and remember code coverage tells you where you need more tests, it isn’t a weapon to beat yourself or your employees with.

SSDT all the way

Mon, 16 Feb 2015 17:38:00 +0000

I generally write T-SQL code in SSDT (Sql Server Data Tools) and find that I often publish to my local database instance and then test the actual code in SSMS, or at least debug the code in SSMS - whether this is using the Sql debugger or modifying procs to output additional data (debugging Sql is so old fasioned!).

It occured to me that I am wasting time switching between windows when SSDT should do everything I want already so I am investigating whether I can move away from SSMS to just SSDT and Visual Studio - is it possible? Is it better or worse?

Steps to become more agile with Sql development

Thu, 12 Feb 2015 21:26:00 +0000

In this series of posts I am going to talk about what it is we need to do, as Sql developers and DBAs to improve our working practices so that we take advantage of modern development patterns and practices to improve the quality of our work, the time it takes to implement features, lower the risk of deployments and have more fun as well.

This blog is focused on becoming more agile, but even in a strict waterfall environment you can still take the methods we will talk about to improve your processes, just because you have created your design up front and had it signed off, doesn’t mean that you cannot write unit tests and have an integration server, it just means you are more formal in your design, your ability to handle changes and your documentation which isn’t necessarily a bad thing.

Choosing a Development IDE for Sql Development

Thu, 12 Feb 2015 07:17:00 +0000

NOTE: THIS WAS WRITTEN IN 2015, it is now 2020 - that is 5 years so please consider this a historical artifact that is probably out of date.

In this series of blog posts, I am talking about how to use agile or modern development practices to help us become better Sql developers.

The first step on the list is a development IDE and it really is the first thing to think about, sure getting your database in source control is critical and if you haven’t already then you should get that done (really go do it now) but how you get your schema and code into source control and how you will manage that going forwards depends a lot on which IDE you choose.

Enable SSDT and DacFx logging

Thu, 05 Feb 2015 21:36:00 +0000

Update

I have noticed that these cmd files are missing from recent ssdt builds, to enable logging see Kevin’s comment:

http://blogs.msdn.com/b/ssdt/archive/2014/07/15/sql-server-data-tools-ju…

**/update

I was helping someone get a deployment filter working in SSDT recently and the issue was that they had configured the publish profile to include filter but when they did a publish or generate script, an error occurred with no more detail than “Contributor initialization error.”:

Being stuck at what to do next I had a poke around and found the SsdtTrace.cmd in the Dac folder:

Why does SqlPackage keep dropping and recreating constraints?

Fri, 16 Jan 2015 20:14:00 +0000

I sometimes see this when deploying via sqlpackager.exe:

Analyzing deployment plan (Complete) Updating database (Start) Dropping DF_XXX... Creating DF_XXX... Update complete. Updating database (Complete) Successfully published database.

This happens everytime I publish, even though there have not been any changes.

Typically this is caused by Sql taking the create statement from SSDT and changing it, it does things like changes GETDATE to getdate and (0) to ((0)), to be honest I am not 100% sure what it will and won’t change but my fix for this when I get it happening is to go to SSMS and script out the table and copy and paste the constraint back into SSDT, then deploy and it should stop.

HOWTO: Filter Dacpac Deployments

Wed, 14 Jan 2015 00:00:00 +0000

Updated 2015-02-02

I have changed the details about passing multiple filters at the bottom, see the codepex site for full details.

tldr;

Download the filter from: https://agilesqlclub.codeplex.com/releases/view/610727 put the dll into the same folder as sqlpackage.exe and add these command line parameters to your deployment:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments=“SqlPackageFilter=IgnoreSchema(BLAH)”

This will neither deploy, drop or alter anything in the BLAH schema.

More Info

I recently discovered deployment contributors which let you write .net code to interrupt the dacpac deployment process and modify either the steps involved or the actual scripts themselves. They are documented: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.deployme…

Enforcing T-SQL quality with SSDT Analysis Extensions

Tue, 30 Dec 2014 00:00:00 +0000

What?

Simply put, it is possible to include checks in the SSDT (Sql Server Data Tools) build process - you come up with a set of rules or start with these (https://tsqlsmellsssdt.codeplex.com/) and when you check-in your code, as part of your normal build or CI build you run the code analysis - the code analysis verifies that the code is good and either continues the build or fails it if you have committed a crime against your rules.

DacFX Links

Sun, 07 Dec 2014 21:55:00 +0000

I thought it would be a good idea to gather together a list of links for DacFx, blogs code or articles - if you have anything that you think should be included in this list please send it and I will include it.

The first thing to read is the “DacFx Public Model Tutorial” this by Kevin Cunnane from the Microsoft SSDT team and is highly recommended:

http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutor…

Then there are the DacFx samples on codeplex:

Make Visual Studio Setup Slightly Faster

Sun, 07 Dec 2014 21:34:00 +0000

If you get the Visual Studio 2012 or 2013 web installer then it uses the BITS service to download the setup files.

The BITS service is used to download files but throttles the download so it doesn’t overwhelm your bandwidth.

If you are also downloading lots of other things like windows updates then the visual studio downloads have to take their time in the queue with everything else.

If you want to download to go quicker, you can disable and stop the BITS service, the installer will fall back to traditional HTTP can be much faster (depending on what other things are trying to use BITS).

Running tSQLt tests from C/VB.Net/Java/Whatever

Thu, 20 Nov 2014 07:19:00 +0000

When you already have a test framework setup such as MSTest or NUnit and possibly a CI build in place and you want to add Sql Server tests it is often wise to call the tests from the framework you already have setup.

I do this at the moment using C# and I have a helper method that calls tSQLt.Run/RunClass/RunAll passing in the name of the test(s) to run - this means that from within visual studio, I can see and run all of my tSQLt tests alongside my existing NUnit / Moq tests.

For better Sql code use fake tables

Tue, 18 Nov 2014 23:13:00 +0000

When unit testing code there is a really powerful feature that is either called mocking or faking or sometimes using stubs and what these mean is creating objects that look like real objects but aren’t.

Why would you want something that looks like something but isn’t?

Unit testing means breaking your code into small chunks and testing that the small chunk does what it is meant to. Testing small chunks of code in isolation is difficult as they often call other chunks of code so we test and pretend that the other thing is correct and to validate those other small chunks, we unit test them.

Unit tests help document your code!

Sun, 02 Nov 2014 20:00:00 +0000

I recently had the chance honour to spend twenty minutes talking to Boris Hristov about testing with Sql Server for his Google Hangouts series, catch the video here
One of the things I mentioned was that having a suite of unit tests helps to document the code and in this post I would like to demonstrate that. If we take the first stored procedure in the AdventureWorks2012 database to see how it is documented, if we take the procedure definition, we get:

What collation variables take on in T-SQL

Wed, 22 Oct 2014 17:46:00 +0000

I was asked an interesting question about collations in sql server recently about where variables in a script got their collation’s from. I really wasn’t too sure of the answer, I generally try to ensure that databases are in the company default collation although I realise that this is not always possible.

Given how important this is I thought I would investigate it a little:

The first question I had was what collaction is the actual T-Sql script compiled in, is it the database or server?

DBAs - Step away from production

Tue, 21 Oct 2014 22:00:00 +0000

I have worked as a DBA and also as a Sql Server developer and in many roles had full access to production, it is OK though I know what I am doing and I normally don’t make mistakes (we’ll come back to this!).

To be clear, this isn’t a moan at DBA’s, I am one and can and do make changes myself - I am trying to improve processes that I have input into. This also applies to anyone who has access to production, ops teams tweaking config settings take note!

DevDev

Mon, 20 Oct 2014 22:11:00 +0000

There has been a gigantic shift in attitudes between development and operations, the devops movement, to create a streamlined, automated and high performing team of developers and infrastructure techs which has really helped transform many operations and projects.

It occurs to me that when building a development team or planning out a project we should include resources for developers to write tools and improve processes for the other developers in the team. I have seen this work very well in a small team which constantly had one developer on rotation spend a week just writing tools for developers.

DevDev

Mon, 20 Oct 2014 22:11:00 +0000

Blog Redirected

Mon, 20 Oct 2014 20:02:00 +0000

I have moved my blog over to here, expect exciting posts about the things that interest me my previous blog was at: http://sqlserverfunctions.wordpress.com.

I hope you find something you enjoy and please comment or contact me for anything!

About Ed Elliott

Sat, 12 Feb 2000 14:32:37 +0000

Hi,

My name is Ed Elliott, I live in Sussex, England with my family. I work in and around data and when people ask me what I do for a living I say “I move data around so people can look at it”. In more depth, I work between the world of data engineering and devops so you’ll see over the course of this blog (since 2014) I started writing more about databasey, devopsey type topics (DacFX FTW) and now I am tending to write more about data engineeringey types of things.

Search

Sat, 12 Feb 2000 14:32:37 +0000

Search via duck duck go:

Contact

Fri, 11 Feb 2000 00:00:00 +0000

ETL Testing

Fri, 11 Feb 2000 00:00:00 +0000

There is only one thing guaranteed when writing ETL pipelines and that is that upstream changes WILL occur and WILL break your ETL pipeline.

As Data Engineers we MUST make sure that we test well so that we can identify breaking changes, rapidly, AND fix and deploy just as fast. To do this we need to use automated testing.

To get started here are links some links to a four part blog series I wrote on testing ETL pipelines, the theory is the same whether your pipelines are SSIS packages, Informatica, Python / Airflow or anything you can dream of:

Ed Elliott's Agile SQL Club

Runing tSQLt Tests with Claude

tSQLt Test Adapter for Visual Studio 2022+

Github Copilot in SSMS can include data in its memory, simple prompt injections ahead

Delta Lake over Spark Connect

ADF: Publish suddenly includes everything where it used to be incremental changes since the last publish

Spark Connect Dotnet November 2024 Where are we?

Introduction

Spark Connect Dotnet Variant Data Type

Sample data

Creating the Variant Column

Delta Lake from .NET in a Spark Connect gRPC world

What to do?

Apache Spark from PHP - it is not just a .NET thing

Goal of this post

Setup

Implementing functions and more fun in Spark Connect using gRPC and .NET

Goal of this post

Moving towards the DataFrame API using the Spark Connect gRPC API in .NET

Goal of this post

Exploring the Spark Connect gRPC API more

Goal of this post

Exploring the Spark Connect gRPC API

Goal of this post

SparkConnectService.SparkConnectServiceClient

Using Spark Connect from .NET to run Spark jobs on Databricks

Goal of this post

Spark Connect Dotnet First Major Milestone

Using Spark Connect from .NET

Introductory Ramble

Spark Connect

Me

Spark Connect

Spark Connect

References

Reference Implementations

Source gRPC proto files

Useful Videos

Spark Connect From .NET

Privacy Policy

The Agile SQL CLub Privacy Policy (google Made me do it)

ADF: Error trying to debug pipeline: BadRequest

ADF: Querying JSON documents

ADF, XML objects and XPath in the expression language

Synapse Analytics and .NET for Apache Spark Example 4 - JOINS

T-SQL

Spark SQL

DataFrame API (C#)

T-SQL LEFT OUTER JOIN

Spark SQL

DataFrame API (C#)

T-SQL RIGHT OUTER JOIN

Spark SQL

DataFrame API (C#)

T-SQL FULL OUTER JOIN

Spark SQL

DataFrame API (C#)

T-SQL CROSS JOIN

Spark SQL

DataFrame API (C#)

Synapse Analytics and .NET for Apache Spark Example 3 - CTE()

T-SQL

Spark SQL

DataFrame API (C#)

Synapse Analytics and .NET for Apache Spark Example 2 - ROW_NUMBER()

T-SQL

Spark SQL

DataFrame API (C#)

Synapse Analytics and .NET for Apache Spark Example 1 - Group By

How to get data in a DataFrame via .NET for Apache Spark

Git in 10 commands to do 99% of gitting and keeping out of trouble

TF-IDF in .NET for Apache Spark Using Spark ML v2

Spark ML in .NET for Apache Spark

Blog .NET for Apache Spark UDFs Missing Shared State

The Problem

TF-IDF in .NET for Apache Spark Using Spark ML

Spark ML in .NET for Apache Spark

Approaches to running Databricks ETL code from Azure ADF

The four tenets of ETL testing

Passing status messages and results back from Databricks to ADF

`SparkConnectService.SparkConnectServiceClient`