0

We have an instance of SQL Server (MSSQL) version 2016, and the database contains programming scripts, e.g., stored procedures and functions.

For the programming scripts of MSSQL, we wonder how to do version control on the source code.

  • For example, dump or export the script in text format SQL and check in to the Git repository.
  • Or please kindly point out if there is a better way of doing the job.

We highly appreciate hints and suggestions.

Improve this question
2
  • 1
    stackoverflow.com/questions/173/…
    – SergeyA
    Commented Aug 4, 2023 at 2:57
  • 2
    One approach would be to import the database definition into an empty SSDT project and add that to source control. Then going forward make changes to the SSDT project and deploy using dacpac deployments.
    – Martin Smith
    Commented Aug 4, 2023 at 8:08

2 Answers 2

Reset to default
3

There are two parts to implementing version control for your SQL Server database objects. One is technical, and the other is mostly process.

  1. As you mentioned, script the objects out into text files and add them to your version control system (git)
    • By the way, you would ideally include the full definition of the database - not just stored procedures and functions, but table and index definitions, etc.
    • There are products available that make this experience better than just having a bunch of text files in a folder - like Microsoft's SSDT database projects, which work with Visual Studio (and now VS Code)
  2. Make sure that the version control system is always up-to-date with the latest code

Item #2 is generally harder for folks to implement than item #1. It generally means that you avoid making changes directly to production databases. Ideally you'll be deploying "from source" - meaning you make the changes to the source code, and then deploy to the database.

Tools exist for doing this in an automated fashion (like SSDT / dacpac deploys). You could also use "change scripts" or "migrations scripts", which would also be stored in git, alongside the updated source files.

A good primer on the different approaches for deploying from source is here: Critiquing two different approaches to delivering databases: Migrations vs state

Improve this answer
1
  • 2
    When visiting your link to workingwithdevs.com, both Edge and Firefox complains about expired certificate.
    – Yano_of_Queenscastle
    Commented Aug 8, 2023 at 11:51
3

This is chapter 5 of a book I wrote with Matt Skelton all about getting databases and their attendent objects into source control and into a DevOps style automation process. This chapter should help, and then all the other chapters expand on the concept. It's all free online, so enjoy.

The trick is figuring out how best to automate the process. Frankly, I'd look to 3rd party tools (and yeah, I work for a vendor who sells those things, being fully open here). There are open source tools that work. Two I can recommend are DBUp and Flyway. There are a couple of other open source options. You want to be sure you're able to capture the changes largely as they happen, although there are tools you can loop back to in order to capture them later (poor approach since you can avoid it).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.