February 2, 2016 at 4:40 pm
Eric M Russell (2/2/2016)
From the perspective of a DBA, I'm sure most of us would prefer that stored procedures an views be stored in clear text and that we have the option to script out the DDL.
Why the scripting-out part? Application developers (as I am classifying them on this thread) do not seem to have the same preference. Is it conditioning? Is there a real constraint in not being able to script out the source code for a piece of compiled code running on the database server?
Is this why the barrier for adoption of Extended Stored Procedures (prior to them being deprecated) and their successor SQLCLR Objects have such a high barrier for adoption?
Clearly I am only picking on the preference since I think it would be impractical from an admin perspective not to be able to see the query text in the plan cache, etc. But from a code management perspective, why do we need the ability to script objects out of the server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 7:22 am
Orlando Colamatteo (2/2/2016)
Eric M Russell (2/2/2016)
From the perspective of a DBA, I'm sure most of us would prefer that stored procedures an views be stored in clear text and that we have the option to script out the DDL.Why the scripting-out part? Application developers (as I am classifying them on this thread) do not seem to have the same preference. Is it conditioning? Is there a real constraint in not being able to script out the source code for a piece of compiled code running on the database server?
Is this why the barrier for adoption of Extended Stored Procedures (prior to them being deprecated) and their successor SQLCLR Objects have such a high barrier for adoption?
Clearly I am only picking on the preference since I think it would be impractical from an admin perspective not to be able to see the query text in the plan cache, etc. But from a code management perspective, why do we need the ability to script objects out of the server?
At least in my universe, a DBA owns the T-SQL code running in production. For example, there may be occasions where a DBA is struggling to resolve a critical preformance issue and in the process adds (or more likely remove) a query hint or tweak a join. The development team is then notififed of modifiation soon after the fact, so it gets looped back into development. Another example is when a development team has no version control system, or maybe they have one but the contractor wasn't using it, or maybe it's just a disorganized version control system. In any event, developers on occasion request to see what version of a stored procedure is in production for confirmation or maybe even request that the DBA script out the version in production so they can use it to branch from.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2016 at 7:37 am
Orlando, your last question seems backwards - why wouldn't we want to be able to script out/modify individual objects? Isn't that a strength? I think about 'real' code deployed on a web server, wouldn't it be nice to just change a single class or method without changing anything else?
February 3, 2016 at 9:32 am
Thanks for posting back. This is a great topic. Thanks for raising it Steve. I am hoping there are a bunch of folks enjoying their popcorn watching this one because this is a great thread 🙂 Jump in where you fit in!
Eric M Russell (2/3/2016)
Orlando Colamatteo (2/2/2016)
Eric M Russell (2/2/2016)
From the perspective of a DBA, I'm sure most of us would prefer that stored procedures an views be stored in clear text and that we have the option to script out the DDL.Why the scripting-out part? Application developers (as I am classifying them on this thread) do not seem to have the same preference. Is it conditioning? Is there a real constraint in not being able to script out the source code for a piece of compiled code running on the database server?
Is this why the barrier for adoption of Extended Stored Procedures (prior to them being deprecated) and their successor SQLCLR Objects have such a high barrier for adoption?
Clearly I am only picking on the preference since I think it would be impractical from an admin perspective not to be able to see the query text in the plan cache, etc. But from a code management perspective, why do we need the ability to script objects out of the server?
At least in my universe, a DBA owns the T-SQL code running in production.
You're not alone.
For example, there may be occasions where a DBA is struggling to resolve a critical preformance issue and in the process adds (or more likely remove) a query hint or tweak a join. The development team is then notififed of modifiation soon after the fact, so it gets looped back into development.
The scenario you described, in terms of an SCM Model where the VCS must be placed first, points to a broken process. I am aware that this scenario is not only a daily occurrence but a matter of procedure in a lot of shops and therein lies the problem in terms of getting to a place where the VCS is always honored as Gold, always. So, why even go to the server...for troubleshooting and profiling, yes, but the moment you decide a system change is required then the VCS should be your next stop. Making a change and then letting the dev team know is not acceptable. Either you make the change in VCS and deploy it (then hopefully document it, or better, document then deploy) or you open a ticket for the dev team to make code changes and they'll submit them back to you for review and deployment. Separation of responsibility in terms of dev and deploy is a key tenet of proper change management.
Another example is when a development team has no version control system, or maybe they have one but the contractor wasn't using it, or maybe it's just a disorganized version control system. In any event, developers on occasion request to see what version of a stored procedure is in production for confirmation or maybe even request that the DBA script out the version in production so they can use it to branch from.
Again, many things you are describing point to a severely broken SCM model. From a change management perspective this is an environment that is likely unmanageable, or manageable only by brute force by a select few that would likely fail under the scrutiny of a change-audit. Step 1 is to get the dev team on VCS that is honored as Gold. Actually, Step 1 is admitting there is a problem 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 9:58 am
What matters most is outcome. At the risk of sounding like an apostate; I'll assert that methodologies, souce control repositories, and roles are just frameworks and tools that exist only to serve as a means to that end. As a DBA, if I get a 2am call that a critical application is down, because a select in a stored procedure is missing a join operator, then I'm making that change myself and then notifying the development team. The following day, we can check the corrected code into source control and perhaps even adjust the change control process as needed to better meet the needs of the organization. Yes, it means the system is broken, but what's important is that the process itself evolves. For example, there would need to be better unit testing and QA to prevent broken code from getting as far as production in the first place. However, organizing a 2am conference call and then waiting several more hours for the "right" person to make the "right" fix may not be what's best for the organization.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2016 at 10:06 am
Andy Warren (2/3/2016)
Orlando, your last question seems backwards - why wouldn't we want to be able to script out/modify individual objects? Isn't that a strength?
It is a strength and a weakness.
I think about 'real' code deployed on a web server, wouldn't it be nice to just change a single class or method without changing anything else?
We sort of have this now since .NET came along and replaced COM where we can introduce a new version of a DLL and the app will use the new version on next use but that DLL is still compiled code. What you are saying about scripting out individual objects directly from a compiled binary, changing it, and then putting it back into play comes across as non-sensical from the perspective of a compiled application. What I mean is, navigating to a server directory where a web site's compiled binaries are being hosted for IIS to serve and expecting to be able to 1) "script out" in plain-text a class-definition where you think a logic error exists, 2) making a change to that class definition in a text editor, and 3) saving the class definition so the next web site request will use the new version of the code is not a scenario that occurs for application developers or web server admins dealing with compiled applications.
By the very nature of how a compiled application's deployment model works the developer or admin is compelled to go to a VCS where the plain-text class and Visual Studio (VS) Solution and Project files reside, open the Solution/Project in VS, make the code change and then after steps like unit testing, checking code in, checking the complete build still works, having the code reviewed, etc. they can build and re-deploy of the application to the web server to get that change into play. All of these steps practically write themselves as a byproduct of the code build-and-deploy model. With databases we can just skip all of that and for those comfortable doing so, signing up for placing a VCS first (always) is a tough sell and naturally will be met with some opposition.
This build-and-deploy model is actually what SQL Server Data Tools (SSDT) Database Projects implements and is the paradigm-shift that lends itself to placing a VCS first in the lifecycle. It is not a coincidence that SSDT Database Projects are implemented in Visual Studio. Have you heard some of the vitriolic comments about SSDT Database Projects? It is a threat to a lot of folks because it takes power away from that database professional sitting at the top of the food chain in a lot of shops and shifts that power to the change management folks (i.e. managers) and the development team at large. Lot's of the vitriole translates to "not on my watch! they cannot be trusted!" From my perspective it is all rooted in fear and insecurity and is comical, actually.
You're correct in that the two positions, server as Gold vs. VCS as Gold, are practically polar opposites and therein lies the debate. It takes discipline to put a VCS first in a database environment because in effect, is viewed mostly as optional due to is not being introduced early in our academic and professional education and therefore enters later in our learning after habits have already been formed. In a compiled application environment a VCS is still technically optional however the build and deploy model practically compels app devs to employ a VCS. Imagine if after a compiled application is deployed a bug is found and the original source code was only stored on the original developer's workstation and his machine has since crashed and burned. In this case the only recourse is to find a decompiler and try to recreate the original source code files and Project (build instructions) to be able to recapture something that can be modified and redeployed. Some decompilers are better than others but this is not really a strategy for managing code, it's a get out of jail free card, a bailout.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 12:09 pm
When working in the game industry, we commonly referred to the production server as gold simply because we had functionality that allowed the client to create content that remained persistent to the game. That means, they effectively are developing the game for us as part of the game. In these instances, you can test the tools to create content. The game does not rely on the content to run the code. But being the client (e.g.: players) added said content, they don't feel the game is complete if it mysteriously broke and was removed. Thus, the game--the product is enhanced by the client (player) where the one true source is production.
Regardless of thoughts. I personally have always disliked that databases have not widely been adopted into VCS. It's absolutely silly to think we shouldn't. Even though our pipeline is different, there are plenty of modified ones that make sense for databases too. When I create an object, it should be submitted, reviewed, tested and then pushed onto production. Any changes that avoid this process is like playing with fire. You either successfully throw water on the fire or you add more gasoline when you make rogue changes.
February 3, 2016 at 12:55 pm
Orlando, it's far from comical, though it does often have to do with trust and fear. We're the guardians. Sometimes we take that role too far, sometimes not far enough. But we're the ones who get called when something is wrong, or goes wrong, so we like to know what changed and why. What can appear to be a simple change can have a lot of impact - adding a column to a select can change the query plan. Devs don't typically think about it, or worry about it - that's a trivial example. A less trivial one is rollback planning. It's rarely good enough to say that the rollback plan is a restore, and it's rarely as simple as just running the last known good version of the db code. Fixing the code is easy. The data impact is what takes time and adds risk. My job is to add value, not to block progress. The lack of understanding about what the DBA is supposed to do and why causes a lot of friction, and a lot of missed opportunities too.
None of that rules out using VCS and using it well, but I'm not sure we're anywhere close to just hitting deploy from Visual Studio. I'd be thrilled to be in a place where I could diff to the gold copy in VCS and generate change scripts, share, review, and execute them with the appropriate rollback planning. The question is what stops us from getting there? Is the the devs, or the DBA's? From my perspective adapting to VCS first would take a little time to get used to, but it's not hard to figure out a change, test it, then check it in before 'deploying'. I also struggle to understand how I can show granular change management if I'm clicking deploy, vs attaching scripts to a change ticket that is reviewed and approved.
My made up maturity models:
0 - Nothing about the DB in source control
1 - DB objects checked in on some triggered basis (daily, deployment, whatever, either by dev or dba)
2 - All changes deployed via scripts generated by doing a diff between VCS and the target (generated by dev or dba)
3 - All changes deployed directly from the project/VCS (which I would think is #2, just fully automated)
I wonder if that's a fair set? What holds us up from making the leap to 2 or 3. Is this really a DBA problem to solve, or is it dev?
Possibly hard to tell from comments, but I'd like to see the issue figured out and solid patterns set. I've never seen it solved well. Anything short of "always works" is problematic. Not as an excuse, but as a practical issue.
February 3, 2016 at 1:23 pm
Let's assume that the business requests a high priority modification to functionality currently in production. Let's also assume that the development team has recently made an unrelated bug fix or added extended error handling that's currently passed QA and pending deployment. You now have a decision to make regarding which version to branch from. But, once this decision has been made (with approval from the business and QA), it's still good practice for a developer to perform a difference comparison between what has been chosen as the branch versus a script out of what's actually in production. In a perfect world, if you've chosen to branch from the latest production release (what we're calling the "golden" version), you would expect no differences. But I've been surpised on occasion. It can happen when there are multiple database developers working on projects and bug fixes for which usage of a specific stored procedure can overlap.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2016 at 2:25 pm
xsevensinzx (2/3/2016)
When I create an object, it should be submitted, reviewed, tested and then pushed onto production. Any changes that avoid this process is like playing with fire. You either successfully throw water on the fire or you add more gasoline when you make rogue changes.
It really is that black and white for me as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 2:54 pm
Andy Warren (2/3/2016)
Orlando, it's far from comical, though it does often have to do with trust and fear. We're the guardians. Sometimes we take that role too far, sometimes not far enough. But we're the ones who get called when something is wrong, or goes wrong, so we like to know what changed and why. What can appear to be a simple change can have a lot of impact - adding a column to a select can change the query plan. Devs don't typically think about it, or worry about it - that's a trivial example. A less trivial one is rollback planning. It's rarely good enough to say that the rollback plan is a restore, and it's rarely as simple as just running the last known good version of the db code. Fixing the code is easy. The data impact is what takes time and adds risk. My job is to add value, not to block progress. The lack of understanding about what the DBA is supposed to do and why causes a lot of friction, and a lot of missed opportunities too.None of that rules out using VCS and using it well...
+1,000,000
We must add value, reduce risk and as a matter of course concede that even our own actions introduce risk, i.e. despite the fact that we are called in to fix those unfixable problems it does not absolve us from following a process that allows for oversight and division of responsibility. Nor does it entitle us to make changes outside a VCS.
...but I'm not sure we're anywhere close to just hitting deploy from Visual Studio.
That may work for some, or maybe all eventually as their processes evolve and they gain comfort with the tool, but I am not advocating that anyone start out targeting a production database from Visual Studio.
I'd be thrilled to be in a place where I could diff to the gold copy in VCS and generate change scripts, share, review, and execute them with the appropriate rollback planning.
What you are describing here I see as a sensible approach and where I too would start the deployment discussion.
I also struggle to understand how I can show granular change management if I'm clicking deploy, vs attaching scripts to a change ticket that is reviewed and approved.
The build phase yields a change script. To reiterate, I would advocate against having Visual Studio immediately deploy that change script to a production instance without a chance for a review and proper rollback plan to be put into place. As an extra level of documentation I too would store the change script as an artifact of the change even though technically it may not be necessary.
My made up maturity models:
0 - Nothing about the DB in source control
1 - DB objects checked in on some triggered basis (daily, deployment, whatever, either by dev or dba)
2 - All changes deployed via scripts generated by doing a diff between VCS and the target (generated by dev or dba)
3 - All changes deployed directly from the project/VCS (which I would think is #2, just fully automated)
I wonder if that's a fair set?
Fair enough for discussion although I would add one between 1 and 2, say 1.5. I see 1 as still having the server positioned as the gold copy. For 1.5 I would drop the "triggered basis" from the equation and this immediately says the VCS is now the gold copy. All objects would first be mastered in VCS and then all development (and emergency prod fixes) from that point forward would be made starting with objects or scripts in the VCS, i.e. no need to bring objects back into the VCS from a server because the VCS is gold and always comes first.
What holds us up from making the leap to 2 or 3. Is this really a DBA problem to solve, or is it dev?
To me, it is a management problem. We have a lot of influence over the goings on, however, including the ability to scare managers into avoiding the discussion or compelling us to position a VCS first.
Possibly hard to tell from comments, but I'd like to see the issue figured out and solid patterns set. I've never seen it solved well. Anything short of "always works" is problematic. Not as an excuse, but as a practical issue.
Indeed it is a tough problem to solve and walks the line between data management and schema and code development. I believe Microsoft took a legitimate shot at it with Database Projects but with any complex area of software there are going to be drawbacks and limitations in any toolset. That said, every manual process I have seen has its drawbacks and limitations as well. I have my own homegrown deployment process in place but am trying to move towards Database Projects. Regardless of the actual solution the underpinnings must be a VCS that is honored as the gold copy otherwise it all falls apart.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 3:03 pm
Eric M Russell (2/3/2016)
Let's assume that the business requests a high priority modification to functionality currently in production. Let's also assume that the development team has recently made an unrelated bug fix or added extended error handling that's currently passed QA and pending deployment. You now have a decision to make regarding which version to branch from.
I don't see there being a decision to make. If you're making a change to production you should start with the gold copy in VCS, not some modified version in a branch containing an unrelated bug fix. Once you make the change to the production version in the VCS, check it into the VCS and deploy it to the production server the team working on the unrelated bug fix in a branch will be compelled to merge your change into their branch and send it all back through QA since your change will have invalidated their testing. This is all part of a good branching and merging model. the process practically writes itself.
But, once this decision has been made (with approval from the business and QA), it's still good practice for a developer to perform a difference comparison between what has been chosen as the branch versus a script out of what's actually in production. In a perfect world, if you've chosen to branch from the latest production release (what we're calling the "golden" version), you would expect no differences. But I've been surpised on occasion. It can happen when there are multiple database developers working on projects and bug fixes for which usage of a specific stored procedure can overlap.
Again, a good branching and merging model handles all of these concerns. Concurrent development is a way of life in a shop with a non-trivial amount of new development and production support going on. Ditching a VCS as not capable of being trusted as gold is not a valid SCM strategy.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 3:30 pm
Orlando Colamatteo (2/3/2016)
Eric M Russell (2/3/2016)
Let's assume that the business requests a high priority modification to functionality currently in production. Let's also assume that the development team has recently made an unrelated bug fix or added extended error handling that's currently passed QA and pending deployment. You now have a decision to make regarding which version to branch from.I don't see there being a decision to make. If you're making a change to production you should start with the gold copy in VCS, not some modified version in a branch containing an unrelated bug fix. Once you make the change to the production version in the VCS, check it into the VCS and deploy it to the production server the team working on the unrelated bug fix in a branch will be compelled to merge your change into their branch and send it all back through QA since your change will have invalidated their testing. This is all part of a good branching and merging model. the process practically writes itself.
But, once this decision has been made (with approval from the business and QA), it's still good practice for a developer to perform a difference comparison between what has been chosen as the branch versus a script out of what's actually in production. In a perfect world, if you've chosen to branch from the latest production release (what we're calling the "golden" version), you would expect no differences. But I've been surpised on occasion. It can happen when there are multiple database developers working on projects and bug fixes for which usage of a specific stored procedure can overlap.
Again, a good branching and merging model handles all of these concerns. Concurrent development is a way of life in a shop with a non-trivial amount of new development and production support going on. Ditching a VCS as not capable of being trusted as gold is not a valid SCM strategy.
On the job, how many times a day do you explain this? 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2016 at 4:01 pm
Eric M Russell (2/3/2016)
Orlando Colamatteo (2/3/2016)
Eric M Russell (2/3/2016)
Let's assume that the business requests a high priority modification to functionality currently in production. Let's also assume that the development team has recently made an unrelated bug fix or added extended error handling that's currently passed QA and pending deployment. You now have a decision to make regarding which version to branch from.I don't see there being a decision to make. If you're making a change to production you should start with the gold copy in VCS, not some modified version in a branch containing an unrelated bug fix. Once you make the change to the production version in the VCS, check it into the VCS and deploy it to the production server the team working on the unrelated bug fix in a branch will be compelled to merge your change into their branch and send it all back through QA since your change will have invalidated their testing. This is all part of a good branching and merging model. the process practically writes itself.
But, once this decision has been made (with approval from the business and QA), it's still good practice for a developer to perform a difference comparison between what has been chosen as the branch versus a script out of what's actually in production. In a perfect world, if you've chosen to branch from the latest production release (what we're calling the "golden" version), you would expect no differences. But I've been surpised on occasion. It can happen when there are multiple database developers working on projects and bug fixes for which usage of a specific stored procedure can overlap.
Again, a good branching and merging model handles all of these concerns. Concurrent development is a way of life in a shop with a non-trivial amount of new development and production support going on. Ditching a VCS as not capable of being trusted as gold is not a valid SCM strategy.
On the job, how many times a day do you explain this? 🙂
Not much these days but I am starting to have flashbacks going back to 3 years ago hanging out on this thread 😛
It's a process and just like any culture shift it requires a lot of advocacy and coaching to get there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 4:22 pm
Orlando Colamatteo (2/3/2016)
By the very nature of how a compiled application's deployment model works the developer or admin is compelled to go to a VCS where the plain-text class and Visual Studio (VS) Solution and Project files reside, open the Solution/Project in VS, make the code change and then after steps like unit testing, checking code in, checking the complete build still works, having the code reviewed, etc. they can build and re-deploy of the application to the web server to get that change into play. All of these steps practically write themselves as a byproduct of the code build-and-deploy model. With databases we can just skip all of that and for those comfortable doing so, signing up for placing a VCS first (always) is a tough sell and naturally will be met with some opposition.This build-and-deploy model is actually what SQL Server Data Tools (SSDT) Database Projects implements and is the paradigm-shift that lends itself to placing a VCS first in the lifecycle. It is not a coincidence that SSDT Database Projects are implemented in Visual Studio. Have you heard some of the vitriolic comments about SSDT Database Projects? It is a threat to a lot of folks because it takes power away from that database professional sitting at the top of the food chain in a lot of shops and shifts that power to the change management folks (i.e. managers) and the development team at large. Lot's of the vitriole translates to "not on my watch! they cannot be trusted!" From my perspective it is all rooted in fear and insecurity and is comical, actually.
You're correct in that the two positions, server as Gold vs. VCS as Gold, are practically polar opposites and therein lies the debate. It takes discipline to put a VCS first in a database environment because in effect, is viewed mostly as optional due to is not being introduced early in our academic and professional education and therefore enters later in our learning after habits have already been formed. In a compiled application environment a VCS is still technically optional however the build and deploy model practically compels app devs to employ a VCS. Imagine if after a compiled application is deployed a bug is found and the original source code was only stored on the original developer's workstation and his machine has since crashed and burned. In this case the only recourse is to find a decompiler and try to recreate the original source code files and Project (build instructions) to be able to recapture something that can be modified and redeployed. Some decompilers are better than others but this is not really a strategy for managing code, it's a get out of jail free card, a bailout.
This right here is why Database deployment is different to Compiled Application deployment. You cannot work without an offline source repository, whatever the nature of it is, when developing and maintaining a compiled application. Even the best .net decompilation doesn't get you back to the True Version of Code. A Database (excluding CLR/extended procedures) contains an (the) online version of the code, the schema, the configuration data. It is self contained, you can modify it, and as long as someone can modify it, nothing else can ever be 100% trusted to be the True Version of Code - without a quick refresh. You may achieve 99% trust and you may believe you have 100% trust in a VCS for database code, but the truth is I could change something without you knowing and that change could affect data in such a way that a deployment from VCS, without comparing to the database first, could make things very broken.
I believe this is why, until we reach the point where no-one can modify the database without deploying from the VCS, the database will be gold.
That said, I wouldn't work without a VCS - I use it (somewhat haphazardly I admit) for everything - Application Development, SSRS, SSIS, batch files, READMEs, Database scripts, documentation, and I would love to work in the way you describe, I just don't have the tools or the environment to do that right now.
As things stand, I use VCS in a production database environment mostly as a way of monitoring changes and capturing deployed versions as I don't have 100% trust that it will always be the right code.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 46 through 60 (of 99 total)
You must be logged in to reply to this topic. Login to reply