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!

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?

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.

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.

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

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.

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…

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.

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:

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