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).
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…
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:
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’.
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:
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.
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’:
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:
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 🙂