February 18, 2016 at 1:58 am
Eric M Russell (2/17/2016)
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?
I disagree that it does not answer the question. What tool can compare a script with the ACTUAL stored procedure? Most access is coded regardless e.g. a SQL statement can be executed to return the stored procedure but that too is a facsimile.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 18, 2016 at 1:59 am
Andy Warren (2/17/2016)
...Hard if they aren't already doing it, hard if they aren't 100% vested in it...
Very, very true.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 18, 2016 at 7:17 am
Gary Varga (2/18/2016)
Eric M Russell (2/17/2016)
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?
I disagree that it does not answer the question. What tool can compare a script with the ACTUAL stored procedure? Most access is coded regardless e.g. a SQL statement can be executed to return the stored procedure but that too is a facsimile.
Yes, that's my point exactly: what tool can compare scripts in VSC versus deployed in production? To pull that off developers would have to follow a regid pattern in terms of how they divide up and format their DDL scripts. It's possible, and a VSC or schema compare tool could support it, but it logistically improbable that it would work in practice.
So what is the solution? If you're a DBA responsible for managing a database, and you want to objectively determine what might have changed in production environment, then you compare a scripted image of production versus a prior version of the same. The DBA should version these scripts in VSC specifically for this purpose.
Does that imply that DBA suspects the organization's versioning and release process could be broken? Yes, it does. In fact, as a DBA, it should part of your job description to be skeptical and independently perform point in time audits what changes actually take place in the production schema and then compare that to what changes the production release process documents.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2016 at 8:04 am
I am surely controversial in my opinions about version control systems here and that is fine by me.
Let me start with this. I consider complete models, including the associated seed data, part of a solutions source-code and as such it should be store-able and version-able, isolated from the actual operational data.
As for VCS itself, every use i seen in the last few decades, worked as a change log system and did virtually nothing with respect to maintaining versions and/or defining versions (let alone upfront).
Let us be real and admit that versions in practice are very ill defined. Hell even even individual changes/issues often are. In database terms, does adding a non-clustered index for performance reasons, constitute a new version? Does it even count as official when not part of the design during the modelling phase? What about configured values in lookup tables, which are usually scripted at design time? Do such changes always matter to the applications running on top of a database? Do the applications and the database always need to be in sync and only together form a version?
There really is no "one" good answer here! Even when one thinks there is, there are huge "buts" and there will be costs/compromises associated with that belief. And even then reality will pop the dream, eventually!
Yet I really do like the idea of having easy access to snapshorts or automated change log of models, constraint definitions, indexes and base tables with important lookup values of my databases. It really should be a feature in SQL Server itself. Call it "pure model backup" and then simply tag specific tables (and/or records) as essential part of the model/design itself. Automated check-in of snapshot scripts or delta's of said scripts would be a logical extension to this and it be fairly straightforward to make.
Overall, and here comes the controversy, current version control systems really are used as file and file collection revision systems. People submit changes to a certain branch, complete or not, working or not just to "not loose their work". It is a form of saving ones work regardless of its finalization state. Even when done in specific branches, those branches are either very course (debug/release) or very ill defined themselves. It all ends up being rather cosmetic and as ill defined as the definition of versions.
As such, over the years I developed a dislike for some, so called "best practice" techniques and "best practice" tools. More often then not, following/using those costs a ton of time, brings lots of discussion and new problems, while honestly they are just liked for the "feel good" / "feel safe" factors and thus loved mostly by the most inexperienced.
I have come to see such things as distractions from the actual substance people need to bring to their work. Writing software of questionable quality, but doing it following an almost religious recipe that most will follow and then feel good/superior about it, has become an epidemic in my eyes.
Tools are nice and all, but they should not define our work. Tools are a means to an end, professionals need to be well able to define and they need to be in control (not the tool)! When a tool brings high costs in price, pre-conditions or technical constraints, a less advanced more down to earth solution looks preferable to me.
We do not live in a perfect world and it would be much better when more people realize that and not try to make it perfect, one way or another. Because rest assured, everyone of us will get perfect wrong in some crucial way!
February 18, 2016 at 9:44 am
Eric M Russell (2/18/2016)
Gary Varga (2/18/2016)
Eric M Russell (2/17/2016)
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?
I disagree that it does not answer the question. What tool can compare a script with the ACTUAL stored procedure? Most access is coded regardless e.g. a SQL statement can be executed to return the stored procedure but that too is a facsimile.
Yes, that's my point exactly: what tool can compare scripts in VSC versus deployed in production? To pull that off developers would have to follow a regid pattern in terms of how they divide up and format their DDL scripts. It's possible, and a VSC or schema compare tool could support it, but it logistically improbable that it would work in practice.
So what is the solution? If you're a DBA responsible for managing a database, and you want to objectively determine what might have changed in production environment, then you compare a scripted image of production versus a prior version of the same. The DBA should version these scripts in VSC specifically for this purpose.
Does that imply that DBA suspects the organization's versioning and release process could be broken? Yes, it does. In fact, as a DBA, it should part of your job description to be skeptical and independently perform point in time audits what changes actually take place in the production schema and then compare that to what changes the production release process documents.
I am going to have to suggest that we agree to disagree.
logistically improbable that it would work in practice
Got the T-shirt.
The DBA should version these scripts in VSC specifically for this purpose.
Not the DBA per se but the deployment process.
In fact, as a DBA, it should part of your job description to be skeptical and independently perform point in time audits what changes actually take place in the production schema and then compare that to what changes the production release process documents.
...by an automated process thus removing the element of human error.
Automation removes the menial, repetitive tasks away from people who make it error prone thus releasing those people to utilise their skills for the more creative tasks which are almost impossible to automate and thus not cost effective to do.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 18, 2016 at 10:48 am
Should Production Databases Be in a VCS?
Yes, database administrators should periodically script out the schema for each database and keep it versioned separately under VCS for point in time comparisons, or even disaster recovery. That's common sense. However, apparently it offends the sensibility of some folks to think that the DBA would be sitting in the background independently verifying what they consider to be a trustworthy VCS and release managment process.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2016 at 12:13 pm
Eric M Russell (2/18/2016)
Yes, that's my point exactly: what tool can compare scripts in VSC versus deployed in production? To pull that off developers would have to follow a regid pattern in terms of how they divide up and format their DDL scripts. It's possible, and a VSC or schema compare tool could support it, but it logistically improbable that it would work in practice.
SQL Source Control from Redgate puts the DDL in a VCS in a format and does this compare. SQL Compare does this as well, allowing you to compare db to db, db to vcs, vcs to vcs.
February 18, 2016 at 3:04 pm
peter-757102 (2/18/2016)
I am surely controversial in my opinions about version control systems here and that is fine by me.Let me start with this. I consider complete models, including the associated seed data, part of a solutions source-code and as such it should be store-able and version-able, isolated from the actual operational data.
As for VCS itself, every use i seen in the last few decades, worked as a change log system and did virtually nothing with respect to maintaining versions and/or defining versions (let alone upfront).
I think that's a problem of people misusing VCS rather than a lack of versioning capability in VCS - unless the VCSs you have seen have been much inferior to ones I've seen.
Let us be real and admit that versions in practice are very ill defined. Hell even even individual changes/issues often are. In database terms, does adding a non-clustered index for performance reasons, constitute a new version? Does it even count as official when not part of the design during the modelling phase? What about configured values in lookup tables, which are usually scripted at design time? Do such changes always matter to the applications running on top of a database? Do the applications and the database always need to be in sync and only together form a version?
I think you are conflating configuration management with version control here. which versions of which pplications and compatible with which versiond of which databases is a configuration management issue, not a version control issue.
...
As such, over the years I developed a dislike for some, so called "best practice" techniques and "best practice" tools. More often then not, following/using those costs a ton of time, brings lots of discussion and new problems, while honestly they are just liked for the "feel good" / "feel safe" factors and thus loved mostly by the most inexperienced.
I have come to see such things as distractions from the actual substance people need to bring to their work. Writing software of questionable quality, but doing it following an almost religious recipe that most will follow and then feel good/superior about it, has become an epidemic in my eyes.
Tools are nice and all, but they should not define our work. Tools are a means to an end, professionals need to be well able to define and they need to be in control (not the tool)! When a tool brings high costs in price, pre-conditions or technical constraints, a less advanced more down to earth solution looks preferable to me.
We do not live in a perfect world and it would be much better when more people realize that and not try to make it perfect, one way or another. Because rest assured, everyone of us will get perfect wrong in some crucial way!
Yes, so called best practice is often rather unsatisfactory practice. That's even more true for project management tools and methods than for development tools and methods. As engineers (or DBAs or developers or scientists or technologists or whatever we call ourselves) we need tools that assist us to do what we as professionals determine needs doing to achieve our commitments and satisfy our custoers and employers, not tools that prevent us from doing what in our professional judgement needs to be done.
Tom
February 19, 2016 at 9:23 am
In our case, our VCS is in a different, untrusted domain from production servers. So, while the idea has some merit, we would likely not have any direct way to implement it.
Gerald Britton, Pluralsight courses
February 22, 2016 at 12:02 pm
g.britton (2/19/2016)
In our case, our VCS is in a different, untrusted domain from production servers. So, while the idea has some merit, we would likely not have any direct way to implement it.
You could make a separate repo in the trusted domain. There are tools to merge from one to another repo, or you could use something like SQL Compare to go to/from VCS/Db.
February 22, 2016 at 12:23 pm
I use a simple product called dbSchema which can suck the design out of any database and into a diagram and file you can save as a file. And then you can compare the diagram to another database. You can then decide the direction in which to push each difference. It would be perfect for seeing the differences in each version of your database/app in dev/test/prod.
February 22, 2016 at 2:57 pm
Steve Jones - SSC Editor (2/22/2016)
g.britton (2/19/2016)
In our case, our VCS is in a different, untrusted domain from production servers. So, while the idea has some merit, we would likely not have any direct way to implement it.You could make a separate repo in the trusted domain. There are tools to merge from one to another repo, or you could use something like SQL Compare to go to/from VCS/Db.
Does SQL Compare handle jobs and packages? That is, since jobs and packages are stored in msdb, can SQL Compare understand which of those tables need to be updated?
Gerald Britton, Pluralsight courses
February 22, 2016 at 3:21 pm
No, SQL Compare does schema.
Data Compare does data, which is what jobs and packages are rows in the tables, but it doesn't work with system tables. Asking someone to see if it's possible.
Really, jobs/packages/alerts/operators/sp_configure needs to be saved separately. It can be in a VCS as .sql files, and most VCS tools can let you know if things have changed, but that would be separate from any tool I know of.
March 8, 2016 at 5:00 pm
We don't put the database itself into VCS, just the app code that uses it.
March 8, 2016 at 8:42 pm
Iwas Bornready (3/8/2016)
We don't put the database itself into VCS, just the app code that uses it.
Wow. That seems analog to washing and vacuuming a car, never changing the oil yet getting by thinking the car is properly maintained. Is this approach imposed on you by the org? Any plans to start managing the app code that happens to live in a database in the VCS too?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply