When I first started at the company I’m at now, I was a little surprised to see how updates to the database schema were handled. You had to ensure that you created a SQL script for all of the changes, and then put them in a shared folder. Each script would have a file name like ’01 – Alter some table.sql’, ’02 – Create some sproc.sql’, etc.
It was a bit of a nightmare especially when it came to syncing to multiple environments such as local, development, staging and production.
That’s when I introduced Microsoft’s SQL Server Data Tools. SQL Server Data Tools allows you to create a database project in Visual Studio which has all of the objects you would have in SQL Server, such as tables, views, stored procedures, linked servers, functions, custom types, certificates, etc. The bonus feature is that Visual Studio will tell you if you have any errors, for example, a stored procedure that references an invalid column. Traditionally, if you just made an update to a table, such as renaming or dropping a column, all of your dependent objects like stored procedures would break, but you wouldn’t know until it’s too late.
Within your database project, Visual Studio will tell you if you have any broken stored procedures. Here’s what happened when I renamed a column in Visual Studio:
I now know right away that I broke the stored procedure AdmGetUsersByClient.
Source control your schema
One of my favorite features of having a database project in Visual Studio is that you can now add it to source control like Git or TFS. This gives you the ability to easily manage changes to the schema and also see a history of who change what. You have a historical view of your database schema as well. This was probably one of the greatest benefits that our company received when I introduced the database project concept.
Integrate with your continuous build server
In our workflow, we push out a nightly build to our QA environment. This wasn’t possible before since the database had to be manually updated, but now with the database project, we can automatically push changes out of our application and database schema. The more processes that you can automate, the less chance you have for messing something up.
Deploy changes easily
When it comes time to push our application to our production environment, it becomes the DBA’s responsibility to update the database schema for us since the development team doesn’t have access to the production environment. This is common practice for many companies and helps to minimize security vulnerabilities, and also puts the burden of being responsible for the production database on the DBA’s shoulders. Using the SQL Server data tools, you can create DACPAC files, which are essentially ZIP files that contain a snapshot of your database project. The DBA can then publish these using the sqlpackage command line tool.