February 15, 2016 at 10:06 pm
Comments posted to this topic are about the item Should Production Databases Be in a VCS?
February 15, 2016 at 11:04 pm
This person asked if we could put the production database code into a VCS. Not as a way of tracking development tasks, but as a way of auditing production and ensuring that any changes in the VCS match with a separate VCS repository. This person noted that they would even like a process that automatically scripts objects and checks changes into a VCS. It's an interesting idea, and one I hadn't considered. However as I think about it, keeping a separate repo for production makes some sense.
It feels like a workaround that would mask the real issue, a broken SCM model. It could even exacerbate the problem by in effect keeping two versions of the truth, one for developers and one for DBAs. That said, if the DBA team cannot or will not trust the VCS development is running out of and it would benefit in troubleshooting production, or policing the developer VCS in hopes of getting closer to the spirit of the intended SCM model, then go for it.
Ironically, in situations like this, it would likely be the DBAs themselves making the uncontrolled changes to the database server in production. If they won't use the main VCS (no pun intended) then would they really want to use an auxiliary one?
In environments where the DBAs would like a VCS to refer to but are up against organizational challenges that prevent them from ever trusting anything but the server, you could have a hidden market there. But in organizations like that, is there really funding for tools like this or time to install and support them?
If I had it in place I would script things nightly right into Main in the development VCS. If there were no changes, then no changeset (TFS speak) would be generated. If there were a change, and that object was being modified in a dev branch, when merging happened, merge conflict!, which would be the desired result. It would mean development began with a copy of the code that was out of date, or that the object changed on the server since they began their development. Either way you want that to become visible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 16, 2016 at 1:31 am
The answer to the Editorial's question is no. Not in a different VCS repository. It should be in the same repository but in a separate branch. This allows for the use of existing VCS techniques and tools to merge the changes from the production branch into any other in a controlled manner. You maintain the ability to check production against a known single VCS version. Also, there is an easy path to a new production branch with each release.
With the exception of historical/archive VCS repositories, for any given piece of code it should only ever exist in a single VCS repository.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 16, 2016 at 7:03 am
Gary Varga (2/16/2016)
The answer to the Editorial's question is no. Not in a different VCS repository. It should be in the same repository but in a separate branch. This allows for the use of existing VCS techniques and tools to merge the changes from the production branch into any other in a controlled manner. You maintain the ability to check production against a known single VCS version. Also, there is an easy path to a new production branch with each release.With the exception of historical/archive VCS repositories, for any given piece of code it should only ever exist in a single VCS repository.
+1
This is what we do -- or try to do at least. It's certainly what we do with our application code, not so much with most our database code yet but I'm working on that.
For those databases where we have done this, it makes it easy to compare the production database with the production code in VCS and when we've done what we're supposed to (branch and merge), one can see what's changed between versions pretty easily. Using SSDT I can also (usually) audit/compare production code with what's in the VCS, and there's been a few times I've caught changes that went to production but weren't in the VCS.
February 16, 2016 at 11:22 am
I tend to agree with you that there should be a central VCS and one set of code for an application/database. Branching when needed, but contained.
However, the person that talked about this had specific concerns and issues. One is that the developers don't use a VCS for database code. Certainly that's a bad idea, but in that case, organizational inertia can limit your ability to get development to change. In that case, is it a bad idea to keep production in your own production VCS?
Cost isn't much of an issue. The space requirements are small, and Git/SVN/Mercurial are free to obtain, so it's a little time. Whether you use SQL Source Control or SQL Compare from Redgate, or another tool, or a script to get all code and check it in, at least you are then tracking code changes. Certainly this can be helpful for upgrades.
The other concern is what if there is a v2.0 branch in development that mirrors production. What prevents a developer from accidentally checking code into this branch during normal development. Maybe they deployed a bug fix and made a change, and forgot to switch branches. The DBA that does look at the VCS in a crisis might see the wrong version of code. This is less of an issue in development since you find an issue, go back in history, check in the previous version of good code and move on. For production DBAs, this is an issue. When you are in a crisis, you must be able to count on your code.
I don't have a great solution, but I certainly see the production side of this being a concern.
February 16, 2016 at 12:12 pm
Versioning database scripts is good. However, attempting to use the developer's code repository as a basis for a schema comparison to production is logistically difficult, if not impossible when each object is maintained as a separate DDL script (which I reccomend generally speaking). So I guess I'm saying, if you need to confirm what changes have occurred to the database schema over time, you can't see the full forest for all the trees and branches, especially when there are more than 50 app developers, BI developers, DBAs, and DevOps team members who all contribute to the T-SQL code base.
I have my own VCS sub-folder, where I'll keep DBA scripts, operational documentation, etc., and what I do is periodically export out the entire production database into a single DDL script and keep it versioned along with comments on what change orders were deployed since the last version. It's a powerful tool when one can compare the database as an aggregate whole against a prior version of itself using WinMerge or the text comparison feature built into the VCS. When someone wants to know what changed in a database, I or another DBA can answer the question precisely using only a few mouse clicks. Of course I can't tell them *why* something was submitted for deployment or what *should* have been submitted instead, but I can at least tell them actually what did get modified or added.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 16, 2016 at 1:46 pm
Could? yes, of course.
Should? dunno. depends on the goal.
As for using VCS to compare the databases, I'd say it depends... We cannot because our Production database is very separate from development - geographically 5000 miles, and it's behind a separate firewall in a datacenter.
We've opted to use SQL Compare to periodically check the drift on our 40+ databases.
We keep our Development database schema in VCS (actually twice: once using Redgate Source Control for developer level tracking, and once using a Powershell script daily to write all object-scripts to a source-controlled folder - a holdover from when Enterprise Manager dropped its VSS support. I keep it going because I'm 'conservative' that way. And yes, I've been source-controlling my databases since the good old days of 6.0/6.5)
As for auditing: On both Dev and Prod a daily job runs a query against the default trace logs to capture and log object changes ... I can see who changed what and when. (I review this log daily for security purposes)
Mark
Just a cog in the wheel.
February 16, 2016 at 4:13 pm
Steve Jones - SSC Editor (2/16/2016)
...The other concern is what if there is a v2.0 branch in development that mirrors production. What prevents a developer from accidentally checking code into this branch during normal development...
Permissions. It is just a basic security. The VCS should have this built in.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 16, 2016 at 4:27 pm
Eric M Russell (2/16/2016)
...attempting to use the developer's code repository as a basis for a schema comparison to production is logistically difficult, if not impossible...
It isn't. It has been done. It is achievable through scripting the VCS and the database to retrieve/generate the two versions of the schema. A script that compares both the files and their contents achieves the desired outcome.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 16, 2016 at 8:24 pm
Your DB objects should always be in a VCS. Period. it saves a load of headaches later on. As for what is currently deployed into the Production environment, that needs to be controlled through a tightened deployment process. The article alluded to the fact that break fixes and other things do happen in the real world, but those should be the exception and not the rule.
Even if you have an existing DB schema that isn't in a VCS, it's worth the effort to change that. Getting all of the objects organized, by DB and by type, makes maintaining things so easy. The trunk should end up being the code that is deployed in the development environment, and the code that is going to be released to the production environment should be tagged. From that tag (or branch, depending on your VCS flavor), an authorized user, whether it's a deployment tool or a manual deployment, should run a script for all of the changed objects. This way, production mimics what is in the current "release".
February 16, 2016 at 8:33 pm
Gary Varga (2/16/2016)
Steve Jones - SSC Editor (2/16/2016)
...The other concern is what if there is a v2.0 branch in development that mirrors production. What prevents a developer from accidentally checking code into this branch during normal development...Permissions. It is just a basic security. The VCS should have this built in.
It is built in. We deploy today, do we always change permissions for the release branch? Or the new dev branch?
I agree with you, but this isn't simple to get set up along with a culture change. There will be breakage.
February 17, 2016 at 1:42 am
Steve Jones - SSC Editor (2/16/2016)
Gary Varga (2/16/2016)
Steve Jones - SSC Editor (2/16/2016)
...The other concern is what if there is a v2.0 branch in development that mirrors production. What prevents a developer from accidentally checking code into this branch during normal development...Permissions. It is just a basic security. The VCS should have this built in.
It is built in. We deploy today, do we always change permissions for the release branch? Or the new dev branch?
I agree with you, but this isn't simple to get set up along with a culture change. There will be breakage.
Culture change...now there is the issue. And, of course, it is not a technological one.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2016 at 1:48 am
Steve Jones - SSC Editor (2/16/2016)
...do we always change permissions for the release branch? Or the new dev branch?...
The model used should be, as we all know, minimise permissions generally and grant specific permissions only as required.
So in this case default to no permission (possibly read only as the user is authenticated [assumption]) then add modification privileges to the trunk to the production DBAs and to each branch to the relevant developers when it is created. Best done using user groups, especially if the VCS has AD integration.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2016 at 10:29 am
Gary Varga (2/16/2016)
Eric M Russell (2/16/2016)
...attempting to use the developer's code repository as a basis for a schema comparison to production is logistically difficult, if not impossible...It isn't. It has been done. It is achievable through scripting the VCS and the database to retrieve/generate the two versions of the schema. A script that compares both the files and their contents achieves the desired outcome.
But the process you describe above isn't comparing VCS against production; it's comparing one version set of scripts from VCS against another version set of the same scripts. It doesn't answer the question: What changed in production?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 17, 2016 at 10:57 am
I don't argue that its possible, just hard. Hard if they aren't already doing it, hard if they aren't 100% vested in it. I have a simple quiz to gauge that trust level. Say we have a minor defect that is discovered on Black Friday that is impacting sales and we can fix it by adding a hint to a proc. Will you change the proc, or change it in source control and re-deploy. If you're not confident enough to deploy, I'd argue you're not there yet:-)
But back to what Steve mentioned, DBA's have long scripted out objects as a separate safety net. It's faster than a restore if someone changes something incorrectly, and it has no dependency on dev (who may or many not be using vcs). Whether that is a branch or separate repo or a folder somewhere seems less interesting than whether that behavior is useful. It doesn't cost much at any level, arguably it adds a place where things may not match. My take is that "if" you do this, it's better than no safety net, and the closer to real time detection of changes the better. Does it go away if there is real confidence in the process of deploying from VCS?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply