Database Projects & Azure DevOps – 5 Lessons Learned

Almost 5 years ago I made the switch from “traditional” database development using SQL Server Management Studio to a more flexible way of development by using Database Projects and Source Control. In the first few years, I worked with BitBucket as my code management system and for 2 years I’m using Azure DevOps. In my spare time, I’m using GitHub as well.

During this transition, I came across a couple of bumps, because I wasn’t familiar with Database Projects and I only had a notion about Source Control (Git). In this blog post, I will describe my journey and the lessons learned during the process.

As many of you know by now, I’m a big fan of using Database Projects and Source Control. This really changed my professional life. By making the transition, I gained a lot of time, which I could spend on things that I really want to do.

In the process of getting started, I started my journey by trying to get my existing database into a Database Project. Like all stories of getting started, it didn’t go very smoothly. Because of this, I can share my first lesson learned.

Don’t use hardcoded database names in scripts.

If you require to point to an object in another database, I suggest that you at least make use of SQLCMD variables. What might be useful as well, is to create synonyms that refer to the external objects to make sure that you don’t need to use SQLCMD variables in all of your scripts.

As soon as my first Database Project was set up, I tried to figure out how to work with GIT, learning about basic GIT commands, which brings me to my 2nd lesson.

Get familiar with GIT command line even when you can use a UI

Even when you can use a visual interface to get your GIT work done, make sure you are familiar with the commands you would need to use. This might be helpful if you’re running into GIT issues.

When working with source control, you will run into merge conflicts sooner or later, to minimize the number of merge conflicts, I can get to my 3rd lesson learned.

Pull before you merge your changes to the target branch in case you are executing merges manually.

When you want to merge manually to your development branch, for example, make sure to get the most recent version of the development branch on your machine before merging. I you don’t you get more merge conflicts and GIT will start complaining anyway that you first need to get some changes in before you can push your changes to the remote.

During development, If you added objects or changed the build action for existing objects, make sure you save all your changes before you commit and push.

Save all changes and check if you have staged your .sqlproj file before you commit

I have been in this situation a lot where I forgot to save my project file and didn’t include it in my commit. If you forget, you will not be able to deploy these changes when working with CI/CD pipelines.

Finally, don’t be afraid to make mistakes when getting started, making mistakes will help you to grow your experience with the subject.

Don’t be afraid to make mistakes