Product articles SQL Source Control Database Source Control
Using SQL Source Control projects in…

Using SQL Source Control projects in Azure Data Studio

David Atkinson describes how developers who were previously writing and managing SQL source code in SSMS can make a smooth transition to Azure Data Studio.

Imagine that your team have been developing a SQL Server database over several years, working in SQL Server Management Studio, and using tools such as SQL Source Control, SQL Prompt and SQL Compare to write T-SQL, manage the source, deploy new builds, and so on. However, some developers on the team are hoping to move to Microsoft’s new SQL IDE, Azure Data Studio, and perform the same tasks there.

How smooth will the transition be?

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.

From SQL Compare to SQL Source Control (a brief history)

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.