DROP all you know about DB development (or: What is SSDT?)

TL;DR – I am using SSDT for developing a new SQL Server Database, and it changed my life. Everything is version-controlled, scripts are being generated automatically and DB deployment is finally a piece of cake. And SO much more. Using SSDT allows rapid iterations, while changing my Database as easy as my code. I finally have a full-stack solution where i can develop all my layers in one place, one flow and one source.

(The ideas and practices are general, but the practical tools are from the Microsoft stack: Visual Studio and SQL Server).

CaveManDB

 

I was recently tasked with extending a legacy SQL Server database, and build a new Web Service on top of it. While planning my tasks for the upcoming sprint, I asked myself the usual questions:

  • How can I easily track my changes made to my DB schema?
  • How can I minimize human error when developing scripts for various DB changes?
  • How can I easily deploy my new DB?
  • How can I quickly iterate over and over again with rapid changes to the schema and stored procedures?

 

DB as code

All those points above got me thinking, why on earth won’t we love our Databases like we love our code? I mean, it is almost unacceptable to NOT use some kind of version-control for your code-base or NOT use some kind of an IDE with intellisense and code completion.  Moreover, it is becoming a global standard for teams to employ CI and CD practices (Continuous Integration and Continuous Deployment respectively).

So how come it is almost 2017, and we are still writing SQL scripts in notepads and deploy it manually to our DB servers? Seriously… Stop this cave-man madness…

 

Enter SSDT

SSDT stands for SQL Server Data Tools. It is Microsoft’s shot at solving ALL those issues I mentioned above and much much more.

Now that we got that out of the way, let’s dive deeper. SSDT is yet another project in your Visual Studio solution that will let you easily manage your entire SQL server locally and deploy the whole thing with 1 click. Ah, yes, for every change that you make in the schema or any other object in your Database project, SSDT will generate the appropriate scripts that will replay the appropriate changes on your server.

This seemed amazing from the get-go, however it’s power turned to be much greater. A game changer in our dev community, no less.

 

Projectify your Database

It is Monday morning, you had your morning coffee, you read this blog-post and decided it is a good time for some cool and quick experiment. To take SSDT for a ride, create a Database project in Visual Studio and see what happens. You will be amazed.

Create new project:

CreateProj

 

Now the important thing to notice here, is that along with the DB project in the Solution Explorer, there is a database instance that was automatically generated for you, and you can see it in the ‘SQL Server Object Explorer’.

SQLExplorer

Do you see what is going on here? You have a local Database in 3 seconds! That is all you need for your development.

Now you can add different Database objects (like tables, stored procedures etc…) and publish your project to update your local database. I hope you realize how amazing this is.

Lets add a table to our DB project:

CreateTestTable

Note that a beautiful designer opens up, and anything you change in the UI, will generate an appropriate SQL script that will be replayed later on your Database schema.

ssdtdesigner

Now how do you update your local Database instance? Easy peasy – right click on the project node, and choose publish. You will get this window, so just choose your local Database and press ‘Publish’:

publish

After 3 seconds, you should refresh your SQL Server Object Explorer and wallah, our DB is updated, and we see that we now are proud owners of a new table:

wallah

 

I am so excited, I can’t stress this enough! You now have an updated Database instance on your dev machine, local DB that you can program against and update the schema on the fly with friendly designers.

There are many (MANY!) other options and configurations like live Database debugging, advanced DB configurations, visual designers etc…  Almost anything you can do manually on your server, you can now configure with SSDT and the appropriate scripts will be generated in real-time, leaving you happy and filled with free time for the important things.

 

I will probably post more on SSDT in the future, but for now you can extend your knowledge with the following resources:

 

I hope you are excited as I am, and hopefully, we will all soon move to a new era where we treat our Databases as equals to our code 🙂

Shonn Lyga.

 

About Shonn Lyga

Obsessed with anything and everything in Software Engineering, Technology and Science
This entry was posted in Databases, Uncategorized and tagged , , . Bookmark the permalink.

10 Responses to DROP all you know about DB development (or: What is SSDT?)

  1. amit says:

    looks a bit like ssms moved into visual studio.
    why is this approch better than using migrations? (let;s say u use nice c# fluent migrator scripts…)
    what about data manipulations? u still need to write SQL/Migrator scripts right?

    Like

    • Shonn Lyga says:

      Hi Amit, if you are not a DB oriented developer, and you spend most of your time in VS, than by all means SSDT is your friend (as opposed to SSMS).

      There are many reasons to prefer SSDT over migrations, since migrations demand more manual work, they don’t let me manage my DB schema just like I manage my source code and in large Enterprises migrations are banned. My DBA prefers to see a source-controlled snapshot of the Database, and generate the appropriate scripts from the changes automatically.

      Not sure what you mean by ‘data manipulation’. Do you mean for example if you need to delete 42 rows from a table? If that is the case, then I don’t think SSDT will be of a big help. However, it is better to consult the docs since i haven’t had a chance to look at all the tools that SSDT provides.

      Like

      • amit says:

        thanks
        i’m definitely a DB oriented developer =)
        however, the thing i like about fluent migrator is that it’s easy to manage different versions of your scheme. and it’s a bunch of c# classes – so u can treat it like any other project in your solution.

        i guess managing your DB without leaving visual studio can be cool, definitely gonna give it a go!

        Like

      • Shonn Lyga says:

        Cool! Try it together with a ‘localdb’ feature and hopefully you will fall in love like I did 🙂

        Like

    • Shonn Lyga says:

      Amit, with SSDT you can use PreDeployment and PostDeployment scripts for data manipulation. See here http://schottsql.blogspot.co.il/2012/11/ssdt-pre-and-post-deploy-scripts.html?m=1

      Like

  2. Nice feature, is it new ?

    Like

    • Shonn Lyga says:

      Hi Ilya, it is not brand new, however, as you probably know, it takes time for such tools to get popular enough mainly because they change the way you look at the whole dev process.

      Believe it or not, there are people that refuse to use version control. In 2016. They say they have been successful without it for 40 years, and they don’t see any reason to bother themselves with such nonsense as version control.

      Like

  3. Pingback: Self-Hosted WebApi Testing with Owin/Katana | PoZeCode

  4. Antonio Cortina says:

    Hi, great introduction.!!. I hope people realize about its benefits. We are using SSDT too, and created a set of custom static code analysis rules extension easily integrated with SSDT.

    Thanks for your time and effort!!!

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s