0

Currently in Azure, I have a website (app service) pointing a database (Sql Server). So far so good! Everything is still in development so nothing is live yet. I use EF code first migration with all my development, so I make an entity change, create a migration file, then update the db.

Now I want to get ready to go live! Or at least get closer to going live and create a new dev site/db for new features.

Problem - If I create a 2nd database, it's schema, data, etc won't match my first db.

QUESTION 1 - Do I want to mirror, copy, replicate, copy then sync, etc, my 1st db (prod) as my 2nd db (dev)?

QUESTION 2 - If I do a copy, what's it like if my db starts to get large, +TB's. Will it take a long time to copy ex. 1+ hr? How does a company like Airbnb create and manage development db's? The reason I ask is my site is similar in that I have profiles, listings, scheduled events, lots of micro services, credit cards, bank account transactions, etc.

I do understand that I will eventually push changes from my 2nd database (dev or QA) to my 1st database (prod), so maybe I have this thought process backwards? Either way I need help in understanding the correct way in how to set this up.

REQUIREMENT - I want my development and prod db to be the same until I make changes to the dev db and then push those changes to the prod db. I also need data that is either production data in the development db (I know this isn't recommended) or some other data that can be generated for testing purposes. Most of the data in my db is pretty complex, ids returned from micro services, etc, so writing a simple seed script to generate and fill test data into the development db might be difficult to do.

Please let me know what the prescribed way to do this is and if there is a better way as I'm new to all the Azure features?

Improve this question
1
  • Can you be more specific about the flavor of SQL you're using in Azure?
    – Brendan McCaffrey
    Commented Jul 22, 2022 at 21:54

2 Answers 2

Reset to default
2

Look at the documentation for PowerShell's New-AzSqlDatabaseCopy.

As you run dev code against this copy, dev and prod databases will gradually drift out of sync. Whether this is tolerable is application-dependent.

In my own case, I create new dev databases and qa databases every morning by copying from production, automated with Azure runbooks.

Changes are under version control. Changes move from version control to dev, qa, uat, and production. Changes never move from one database to another database.

If you don't want to use PowerShell, the Azure portal has convenient buttons for copying or restoring databases, too.

All your options will incur some cost.

Later . . . referring to comments.

What happens when I ramp up developer's to 5+ or 100+, does this mean I need to create a copy of my database N+ times?

If your development workflow requires each developer to work on her own copy of your database (schema plus terabytes of data), then yes, you need one or more copies per developer. But that would be a Bad Thing.

Can I refresh the dev db without doing a new full copy of prod? Ex. just refresh the difference between the 2 db's

Some tools can do this. RedGate Compare is one of them.

But you're going down the wrong path. Find a different way.

Improve this answer
12
  • Note that you typically need to perform a schema upgrade to the production database after copying or restoring to a lower environment, as the lower environment is typically on a higher version of the application. Ideally you have a deployment pipeline that you can run after the production database is restored.
    – David Browne - Microsoft
    Commented Jul 14, 2022 at 22:14
  • Wouldn't this be very expensive/time consuming to copy all the data? What if my db gets to multiple TB's? Wouldn't replication be the best option for creating a dev/test env. that has a replica of prod with prod data to use to test against? Because then if I make a change today I can have it backup/restore tonight at midnight to whatever is in prod? Con't...
    – user1186050
    Commented Jul 14, 2022 at 23:07
  • 1
    "My concern isn't necessarily the schema or structure of the db" - The thing is, you will need to be concerned about the schema just as well. Because as Mike and David pointed out, over time you'll likely have made schema changes in your DEV environment that haven't been released to PROD yet. If you do a refresh from PROD to DEV (regardless of the mechanism - for the most part) the schema in your new DEV environment will be replaced with the schema from PROD. Now your application(s) in DEV will fail because they're not coded to that schema. You'd only be safe if your cadence was to do a...
    – J.D.
    Commented Jul 15, 2022 at 11:32
  • 1
    "Wouldn't this be very expensive/time consuming to copy all the data?" Expense is application-dependent; it depends on how your database and servers are implemented in Azure, storage service, etc. It can be time consuming, but I seem to recall that you can configure Azure SQL so your upper bound on time to restore from backup is about 12 hours. (Azure SQL is one way to implement SQL Server databases; there are others.)
    – Mike Sherrill 'Cat Recall'
    Commented Jul 15, 2022 at 12:06
  • 1
    @user1186050 it's a misconception that you need production data to test new features in Dev. All you really need is a sample of data that mimics production. the schema is always the most important part. As you mention, what if prod grows to muliti-terabytes. Do you want to pay that much for a dev region?
    – Brendan McCaffrey
    Commented Jul 18, 2022 at 10:49
0

Your best approach would be to perform a backup and restore. At the beginning of each development cycle, take a fresh backup from prod and restore to dev. For the type of work you're talking about doing, there is no need to set up replication between the two regions. It will be a lot of overhead for a dev database that doesn't really need to be completely up to date with prod data anyways. Plus, you'd being introducing unneeded overhead and complexity on your production database.

This can also become more complex if you have overlapping development cycles. I.E. Dev team A is making changes in Dev while Dev team B is also making changes. You'd want to be careful your Dev refresh doesn't overwrite each team's progress. I've worked in some very big shops, 100's of applications and 1000s of SQL Servers, and it's always been rare to copy prod to Dev, let alone sync it. I some cases, it's not even compliant with the data we hold. It's more common to create Dev and Prod, and version control them. When you make a change in Dev, check it in when it is moved to Prod. The only time you should ever need a refresh from Prod is when you feel you've drifted from your baseline in Dev, and no longer match prod. This happens when support teams work outside the process begin fixing bugs directly in Prod.

Improve this answer
1
  • "At the beginning of each development cycle, take a fresh backup from prod and restore to dev" do you have any links or guides/tutorials that show how to do this in Azure? What's the difference between what I have already done in Azure, which was make a copy of my prod db to use as dev and then sync with prod when desired, and doing a backup and restore to dev? Does Azure have an actual feature called backup somewhere in the portal or would this be the same thing as Azure's copy and sync feature???
    – user1186050
    Commented Jul 20, 2022 at 23:04

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.