Why Use SSDT?

Ed Elliott has a three-part series on database projects in SQL Server Data Tools.

Part 1:  What is SSDT?

The SSOX or SQL Server Object Explorer is a cool utility that lets you connect to a live database and do things to it like debug stored procedures or update individual objects. It also lets you see a view of you projects after all references have been resolved so if you use “Same Database” references you can see how your end project will end up – really useful.

Part 2:  Deploying projects

Using the DacServices via whatever method you want (schema compare, sqlpackage, powershell, something else?) really makes it simple to spend your time writing code and tests rather than manual migration steps. It constantly amazes me who well rounded the deployment side of things is. Every time I use something obscure, something other than a table or procedure I half expect the deployment to fail but it just always works.

Over the last couple of years I must have created hundreds if not thousands of builds all with their own release scripts across tens of databases in different environments and I haven’t yet been able to break the ssdt deployment bits without it acyually being my fault or something stupid like a merge that goes haywire (that’s one reason to have tests).

Part 3:  the .Net APIs

The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.

The second way it can be used is to take objects and create T-SQL.

I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn’t guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table’s deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.

This is a great series with a lot of informative links.

Related Posts

CI With SQL Server And Jenkins

Chris Adkin shows how to auto-deploy SQL Server Data Tools projects to a SQL Server instance using Jenkins: The aim of this blog post is twofold, it is to explain how: A “Self building pipeline” for the deployment of a SQL Server Data Tools project can be implemented using open source tools A build pipeline can be […]

Read More

Continuous Deployment In A Box

Ed Elliott has been working on a very interesting project: What does this do? Unblock-File *.ps1 – removes a flag that windows puts on files to stop them being run if they have been downloaded over the internet. .\ContinuousDeploymentFTW.ps1 – runs the install script which actually: Downloads chocolatey Installs git Installs Jenkins 2 Guides you […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031