August 5, 2024 at 12:00 am
Comments posted to this topic are about the item Git, GitHub and Visual Studio Code for SQL Server
August 6, 2024 at 4:07 pm
Hmmm, seems like the tail wagging the dog to me. All of what you say works as explained but you haven't really integrated it with SQL Server have you. If you want to add a column to an existing table in SQL Server, you will write an ALTER statement which isn't the full definition of the table so you are now doing double maintenance, once to update the table in SQL Server and once to update the definition in Git. It's inevitable that if Git and SQL Server definitions are maintained separately, they will drift apart. There should always be a single source of truth and in this case it would be SQL Server since that's what's actually in use. If you make changes to the DB based on what's in Git, you'd always run the risk of overwriting a change that someone made in the DB but failed to update in Git which is why developers would quickly learn to only rely on what's in the DB if they want to avoid unexpected headaches.
SCCS software works great for programing languages since the Git repository can be that single source of truth, although users can still break that by deploying code from their development environment without checking code changes back into Git and that's why DevOps automated build environments are so popular since they prevent that problem. Unfortunately SCCS is a square peg in a round hole for DB changes since they can be DB Settings updates, changes to an existing schema object, creation of new schema objects, permissions on objects, and even one time updates to data in things like lookup tables. However, storing all of these change scripts in Git isn't the answer either since they are SQL code statements that you're running to change the DB, not the definitions of the objects themselves. These change scripts would actually be a better indication of the true change history but that's not how Git works.
The key difference is that a program in SCCS is typically a collection of source code that compiles into a standalone set of executables or libraries that can be included in other programs. For SQL Server code, it's not standalone code, it's a collection of definitions that describe a configuration of database environment and you update those objects in place with SQL statements rather than recreating them with a compilation step.
All that aside I have to commend you for trying to find a workable SCCS solution for SQL Server and even more for taking the time to share it with others. I tried to find a solution too and came away with several ideas but no reliable workable solution. Keep writing.
October 24, 2024 at 2:11 am
Near the beginning of this article, you mention "Download the file aworks.sql at https://github.com/dcalbimonte/mydbproject/blob/main/aworks.sql." I don't seem to be able to open that location in GitHub.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply