Why I Don’t Like Shared Development Databases

  • Kendra Little

    SSC Enthusiast

    Points: 117

    Comments posted to this topic are about the item Why I Don’t Like Shared Development Databases

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    As someone who doesn't know any better, how does a team of developers then share the standardized objects that might allow them to be more productive? (views/unit tests/stored procs/etc.) Is everyone just spinning up a copy of the latest ….uh...Production Development (?) database before every single change is being worked?

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • David.Poole

    SSC Guru

    Points: 75363

    In my organisation the DBs are under source control so every morning it is a case of

    git pull
    ./launch-latest.sh

     

  • Grant Fritchey

    SSC Guru

    Points: 396551

    jonathan.crawford wrote:

    As someone who doesn't know any better, how does a team of developers then share the standardized objects that might allow them to be more productive? (views/unit tests/stored procs/etc.) Is everyone just spinning up a copy of the latest ….uh...Production Development (?) database before every single change is being worked?

    Well, it depends (you knew that was coming). I've done it two ways. I've had a clean copy of the production database that all development started from. This way, any given developer has the same known start point. I've also just had a mechanism to build the database from source control, so you can start from the appropriate for you & your team start point. Either way, everything goes through source control and yes, at some point you have integration testing to see if the stuff I've done works with the stuff you've done.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • frederico_fonseca

    SSChampion

    Points: 14686

    what everyone else said - plus in some of the teams we have a database per change request being done.

    one of our teams has almost always over 100 active databases on DEV and 80+ in Test ... and a single one in Prod

    All source control, web utility to create new db's and copy sample data from a PII masked production copy.

  • jarick 15608

    Ten Centuries

    Points: 1250

    A lot of developers are not at all familiar with managing their own instances.  This is more of a problem with Oracle where it's not as easy to restore databases like SQL Server is.  Some can't even do that.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    jarick 15608 wrote:

    A lot of developers are not at all familiar with managing their own instances.  This is more of a problem with Oracle where it's not as easy to restore databases like SQL Server is.  Some can't even do that.

    Which is why you should push button automate their development machine provisioning. Don't assume they know how to set up a database. Script it all out so it just happens.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Rod at work

    SSC-Dedicated

    Points: 33376

    Very interesting editorial, Kendra. You've mentioned a lot that I've never heard of before, such as SQL Provision or other tooling to mask/de-identify production data. I've never worked anywhere, where developers had a local copy of the database. I'm familiar with the concept, but reputation only. In my current, state government job, I see either a mix of production/test databases, or production/test/development databases. The last of which is the oddest because the development database is shared so I see little difference between a shared test database and a shared development database, with the exception that the test database is one that users are supposed to run tests against.

    On my team my boss is the only one who can make changes to the database. That is a very strict policy he's made. At best we can only make suggestions. And he makes those changes through TOAD, which as far as I know doesn't concern itself with testing different indices configurations looking for something that performs better.

    At this point, whether a local dev database is better than a shared one is academic, to me. What is the proof of it?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    Rod at work wrote:

    Very interesting editorial, Kendra. You've mentioned a lot that I've never heard of before, such as SQL Provision or other tooling to mask/de-identify production data. I've never worked anywhere, where developers had a local copy of the database. I'm familiar with the concept, but reputation only. In my current, state government job, I see either a mix of production/test databases, or production/test/development databases. The last of which is the oddest because the development database is shared so I see little difference between a shared test database and a shared development database, with the exception that the test database is one that users are supposed to run tests against.

    On my team my boss is the only one who can make changes to the database. That is a very strict policy he's made. At best we can only make suggestions. And he makes those changes through TOAD, which as far as I know doesn't concern itself with testing different indices configurations looking for something that performs better.

    At this point, whether a local dev database is better than a shared one is academic, to me. What is the proof of it?

    Purely anecdotal, but a team member was upgrading a process that I developed, while I continued to support the existing one. He made a change to the field names in the target table and broke my import process. If he'd been messing in his own copy (or even in his own schema, like he was supposed to be lol) it would have been fine. But since our dev database is shared (and also used as our production database - I know...process horribilus) he screwed me up and we both had to spend extra time to fix it.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Rod at work

    SSC-Dedicated

    Points: 33376

    Thank you for sharing your experience, Jonathan. I can see how, if you had separate dev databases it would have made it easier for you. In my environment there's a lot of bureaucracy. For example, to add a column to a table requires committee approval, which could take up to two weeks.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Chris Harshman

    SSC-Forever

    Points: 42104

    frederico_fonseca wrote:

    what everyone else said - plus in some of the teams we have a database per change request being done.

    one of our teams has almost always over 100 active databases on DEV and 80+ in Test ... and a single one in Prod

    All source control, web utility to create new db's and copy sample data from a PII masked production copy.

    Sounds like a nightmare to me.  With that many moving parts, maintaining it all and coordinating which changes need to be synchronized to which other environments and which changes need to be isolated from other changes must be a full time job for a person.  I've seen development groups have problems managing just 3 or 4 environments, no where near 100.

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    Chris Harshman wrote:

    frederico_fonseca wrote:

    what everyone else said - plus in some of the teams we have a database per change request being done.

    one of our teams has almost always over 100 active databases on DEV and 80+ in Test ... and a single one in Prod

    All source control, web utility to create new db's and copy sample data from a PII masked production copy.

    Sounds like a nightmare to me.  With that many moving parts, maintaining it all and coordinating which changes need to be synchronized to which other environments and which changes need to be isolated from other changes must be a full time job for a person.  I've seen development groups have problems managing just 3 or 4 environments, no where near 100.

    that's why the source control though, right? because the code branching/merging happens during that process, so the end result is always the same target. doesn't matter how many people are working on it (the 100 db's) they are all shooting at the same target. Since they will all get merged in sequence, then all is (theoretically) fine

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Chris Harshman

    SSC-Forever

    Points: 42104

    jonathan.crawford wrote:

    Chris Harshman wrote:

    Sounds like a nightmare to me.  With that many moving parts, maintaining it all and coordinating which changes need to be synchronized to which other environments and which changes need to be isolated from other changes must be a full time job for a person.  I've seen development groups have problems managing just 3 or 4 environments, no where near 100.

    that's why the source control though, right? because the code branching/merging happens during that process, so the end result is always the same target. doesn't matter how many people are working on it (the 100 db's) they are all shooting at the same target. Since they will all get merged in sequence, then all is (theoretically) fine

    That's the part that requires a lot of time and effort to coordinate.  If you set it up that everyone is getting everything from source control every day like others have said here, then you've created a lot of work to simulate in effect what is a combined environment.  I'm not saying it can't be done, I'm saying it would take more work than people realize to do it in a meaningful way.

  • frederico_fonseca

    SSChampion

    Points: 14686

    jonathan.crawford wrote:

    Chris Harshman wrote:

    frederico_fonseca wrote:

    what everyone else said - plus in some of the teams we have a database per change request being done.

    one of our teams has almost always over 100 active databases on DEV and 80+ in Test ... and a single one in Prod

    All source control, web utility to create new db's and copy sample data from a PII masked production copy.

    Sounds like a nightmare to me.  With that many moving parts, maintaining it all and coordinating which changes need to be synchronized to which other environments and which changes need to be isolated from other changes must be a full time job for a person.  I've seen development groups have problems managing just 3 or 4 environments, no where near 100.

    that's why the source control though, right? because the code branching/merging happens during that process, so the end result is always the same target. doesn't matter how many people are working on it (the 100 db's) they are all shooting at the same target. Since they will all get merged in sequence, then all is (theoretically) fine

    It took awhile to set up and tune - but it runs smoothly now.

    so from those db's /code branches (this is a .net/COBOL app) the developer merges into one of the dev branches when code is ready for initial integration - this goes into the nightly build and is available for testers to do initial testing.

    once it is decide which changes will go  up (some can go this release cycle, others on one of the next ones) then it gets merged onto the main DEVbranch, goes through one integration run and then gets merged onto the TEST branch were it is subject to further testing. Once signed off testing it goes to UAT for full integration until the cycle put live date (once every 2 weeks for standard changes, faster for some urgent ones)

    nightly builds and full runs on each of the dev, test and uat branches.

    all this under Subversion and Jira - and the status on Jira determines if the code can be merged automatically onto the next "environment"

     

  • Grant Fritchey

    SSC Guru

    Points: 396551

    Very much mirrors my experience. Once people know how things work, the actual labor to make it work is pretty simple.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply