David Atkinson

18 June 2020

• SQL Source Control
• Database Source Control

Database Source Control in Azure Data Studio

Many development teams have been developing SQL Server databases for many years, working mainly in SQL Server Management Studio. Many of these teams have been weighing the benefits of moving to a multi-platform IDE for SQL Server, such as Azure Data Studio. How smooth will the transition be? How easy will it be to transfer or replicate their existing processes for Git database version control, for example? With Azure Data Studio’s tight integration with Git and a tool like SQL Compare, it should be straightforward.

The rise of lightweight, cross-platform IDEs

Over the past couple of years, we’ve seen a steady rise in the popularity of fast, lightweight IDEs that run on multiple platforms. Azure Data Studio and VS Code now represent over 15% of editor usage according to a recent SQL Server Central poll.

Which editors do you use when working with SQL Server?

While SQL Server Management Studio remains the most popular IDE for working with SQL Server, it’s also becoming more common that it’s not the only IDE teams use. For some developments, and some developers, the newer breeds of SQL editor, with attractive features such as Notebook support, are increasingly credible alternatives.

So, what does this mean for database developers who previously relied on SSMS add-ins, such as SQL Source Control? If all work on a project is moving to Azure Data Studio, for example, then requiring database developers to run SSMS alongside it, just to manage their SQL Source Control projects somewhat defeats the point. They will want to use the existing Source Control project as the starting point for continued development work in ADS, and they will still need a way to modify their development databases, and then update the version control repository with their latest changes.

Fortunately, SQL Compare makes this process very easy.

Schema comparisons and SQL script generation

Redgate released its first database version control tool in 2007. No, this wasn’t SQL Source Control, but SQL Compare Pro v6, which introduced support for running schema comparisons with a scripts folder as the source or target, in addition to databases.

Database developers would modify their development database, as they had done before, but rather than deploy these changes directly to another database, such as Staging or Production, they would instead save the changes to the scripts folder. In this folder, SQL Compare would now create and maintain a hierarchy of object creation scripts (.sql) in sub folders, by object type. Teams could maintain the latest database version alongside the corresponding application code, and these changes would make their way together to production in a much more controlled fashion.

Given the dominance of SQL Server Management Studio, SQL Source Control was SQL Compare’s natural successor. It adopted SQL Compare’s comparison engine and the scripts folder workflow, integrated it tightly into SSMS, and improved it. It made it much easier for teams to spot potentially conflicting changes to the same object, and therefore to avoid merge conflicts. It also had one-click check-ins, rather than relying on external VCS clients.

Fast forward ten years and SQL Source Control remains a versatile yet simple interface between a SQL Server development database and version control. However, times have changed, and many developers now make database changes in ADS.

How to use Azure Data Studio with a SQL Source Control project

Fortunately, with SQL Compare, the team’s SQL source control project can move with them. For ongoing development in Azure Data Studio, they can capture development changes with SQL Compare, and then, thanks to Azure Data Studio’s built in Git connectivity, can commit them directly into version control. Incidentally, Git now accounts for more than 50% of the VCS market and is still growing fast!

The following example shows a SQL Compare Pro project where the development database is the source and the scripts folder, from your existing SQL Source Control project, is the target. You can find your Git repository’s scripts folder referenced in SQL Source Control’s Setup screen.

Save the project and click Compare now and you’re presented with the familiar screen detailing any schema differences between the current development database and what’s in version control.

Select a subset of changes to save (or deploy) to the project. Running through the Deploy wizard will update the scripts folder.

In Azure Data Studio, simply use the built-in Git connectivity to open your Git repository.

This is a one-off task as Azure Data Studio will open the git folder automatically next time. And, lo and behold, the schema changes are available for you to stage, commit and push from within the IDE.

When you’ve made changes to the database and are ready to save them to the project, I simply click Refresh in SQL Compare and run through the Deploy wizard again.

When I pull or merge changes made by others into my local scripts folder, I will need to apply them to my development database. To do this, simply reverse the direction of the Deploy, by double clicking in the header bar (the one with the Deploy button in it).

This can also be employed when changes need to be undone in the database.

Conclusion

For the purposes of database versioning, SQL Source Control and SQL Compare Pro can be regarded as analogous. The key difference is that SQL Source Control is embedded in SSMS, whereas SQL Compare Pro is a standalone app. When combined with Azure Data Studio’s git connectivity, SQL Compare Pro provides a near-equivalent experience, which is unsurprising to those who understand the lineage of the tools.