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?
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?
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: Where do I get it?
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.
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?
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.
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 .