2

In our production environment we have a large database (SQL Managed Instance), roughly around 400-500 GB. We create a copy of this database and use it to restore in our development environment to create a database (SQL Server 2019) our developers can use. We create a backup of out production database every day, however the restore processor takes a very long time and therefore don't often restore the development database.

With our growing development team we are noticing that we are stepping on each others toes and causing delays. We are looking into creating a single database per developer, as well as updating the frequency in which we restore these databases.

I am writing this to get some advice on how to approach this task. Please let me know if you require more information.

3
  • 5
    Simply. Don't use production database for development. Create a smaller, sample database for development and testing. Use the production database for performance testing and smoke testing only.
    – Euphoric
    Commented Apr 23, 2020 at 8:03
  • In what manner do they step on each other's toes? Change the same entities? Locking queries?
    – Nathan
    Commented Apr 23, 2020 at 21:16
  • An example that comes to mind is a developer changing a stored procedure to work with their code which affects other developer as they are working on there own feature branch but all are pointing to the same DB.
    – Ross
    Commented Apr 24, 2020 at 9:09

1 Answer 1

Reset to default
8

We have a similar size live database, but we don't develop against a full size copy.

Each developer has an almost empty version on their own computer, which is used for most of their work, including a set of integration tests.

Then we have a 'development' copy of the live database, refreshed about once a month. This is used for performance testing. The developers can co-ordinate any breaking changes, but they're rare because most experimental stuff can be done on small, local databases.

Then there's another 'Test' copy, so the test team can test a specific version.

3
  • How do you get the almost empty version. Do you create it from a schema and then populate it with test data or do you strip down the production copy?
    – Ross
    Commented Apr 23, 2020 at 9:28
  • 4
    Why would you want to strip down your production copy? The content of a stripped down copy is unpredictable. It contains a lot of stuff you do not need but is likely to miss the interesting edge cases. A developer test DB should be populated with manually created edge cases. A copy of the production DB belongs into the staging area. (If you notice that your staging has issues that do not turn up in test DB, extend your test DB by those cases)
    – Manziel
    Commented Apr 23, 2020 at 11:06
  • 3
    @Ross I'd always re-create from schema. First, it (smoke) tests the schema. Second, there is no chance of having real data on dev machines, which can get you in problems with GDPR.
    – Robert
    Commented Apr 23, 2020 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.