Is SQL Source Control meant to be used in production?
ToddWill
Posts: 1 New member
We are a very small team (five) that have a number of SQL Server DBs that we manage for an internal set of users. We have a development server and a production server. Sometimes changes are made in production for expedience. Should the source code control be set up against the production DBs, and if so is there overhead imposed by doing this? Will a production DB slow down because SQL Source Control is attached to it?
Tagged:
Answers
Hello Todd,
Thank you for your query. Given your situation I'll give two answers.
Official
As you likely know, good practice says you should never make changes directly in production and the whys and wherefores of such have been written about exhaustively. But as reality is rarely so neat, please my next paragraph for a compromise.
Yes there is an overhead, depending on your actual needs and what options you select, resources available and system load, that could range anywhere from negligible to noticeable.
Given it's going to be a live server, your considerations of using the shared model vs dedicated model and their subsequent ramifications also become more involved. That's before you've got to merge conflict resolution.
Practical compromise
In your scenario I'd Source Control my development environment as that's the principle purpose of source control (opposed to version control) and will give your devs the flexibility to work independently as intended. Then, where the need arises to make a change in production, you can do so and then use the Comparison tools in the toolbelt to deploy those changes back to the dev instance. This gives the best of both worlds along with consistency.
Lastly, should you wish to grow out your deployment in future to a full CI/CD pipeline, you'll already be ready to do so rather than having to re-engineer it.
Further general reading
The 'environments' section of this might be useful to you, but it all applies.
https://www.red-gate.com/simple-talk/databases/sql-server/learn/change-management-and-source-control/
Kind regards,
Peter
Redgate Product Support