Copy DB from Prod to Test !!

Question

Copy DB from Prod to Test !!

Lidou123

Hall of Fame

Points: 3186
More actions
April 10, 2023 at 9:33 pm

#4171914

Good morning.
I would like your advice. I work on Azure Sql.
We have 3 environments (Dev, Test and Prod) Each environment has a specific tenant.
Every month, I would like to copy the database from Production to the Test environment.
What solution do you recommend to perform this task?
Regards

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Step 1... find everything in the database that will get you fired when someone steals them from your test environment and obfuscate them. You really don't want to read about yourself or you company for a data-breach in the morning.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Jeff Moden wrote:

Step 1... find everything in the database that will get you fired when someone steals them from your test environment and obfuscate them. You really don't want to read about yourself or you company for a data-breach in the morning.

+1

Did this recently, protect your a$$ at all costs 😑

Far away is close at hand in the images of elsewhere.
Anon.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 3

Yes. Indeed, the data must be protected.I will not copy anything confidential in Test environment.

At the technical level, do you have any advice for copying the data to another environment?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Lidou123 wrote:

Yes. Indeed, the data must be protected.I will not copy anything confidential in Test environment. At the technical level, do you have any advice for copying the data to another environment?

Just to be sure, this will be very general in nature because I've never even logged into an Azure system.

Do a restore or snapshot of the prod database to an area protected as well as prod.
Do all of your obfuscations, deletes, and data reduction on that copy (and not on Prod, like some have accidentally done)
Do your restores or snapshots for the non-prod databases from there.
DO NOT RELY ON SIMPLE DATAMASKING!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 5

Why is it acceptable that a test environment is less secure than a production environment?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

ZZartin wrote:

Why is it acceptable that a test environment is less secure than a production environment?

It's not, IMHO, and I don't see anywhere in the thread above where that was suggested. I treat Dev and Test the same as Prod except for the people who have access to each. For example, Developers typically don't have access to Prod. And while the Dev and Test boxes have strict security, like Prod, they also have different people and so the data must be treated a bit differently. For example, there is no reason, whatsoever, that Devs need access to PII.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

For any data you had to delete, anything with personally identifiable information, there are a couple of tools that can generate data for you to use in testing and development.

I've not used this site personally but have it bookmarked in case I ever need to.

https://www.onlinedatagenerator.com

----------------------------------------------------

David Burrows SSC Guru Points: 65142 More actions · Answer 8

Jeff Moden wrote:

ZZartin wrote:
Why is it acceptable that a test environment is less secure than a production environment?
It's not, IMHO, and I don't see anywhere in the thread above where that was suggested. I treat Dev and Test the same as Prod except for the people who have access to each. For example, Developers typically don't have access to Prod. And while the Dev and Test boxes have strict security, like Prod, they also have different people and so the data must be treated a bit differently. For example, there is no reason, whatsoever, that Devs need access to PII.

+1

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

MMartin1 wrote:

For any data you had to delete, anything with personally identifiable information, there are a couple of tools that can generate data for you to use in testing and development.
I've not used this site personally but have it bookmarked in case I ever need to.
https://www.onlinedatagenerator.com

I'm actually pretty good about being able to easily generate tons of "Random Constrained Data" for all sorts of testing. I'm working on updating a presentation on "Crosstabs and Pivots - Reporting on Steroids". The machines nowadays along with the code run so bloody fast that I needed to generate 100 million rows that are 529 bytes wide (not including the 2 bytes for each row in the slot array.

And, with the help of "Minimal Logging" the inserts are done to an empty table that has a Clustered Index on a Datetime and ProductID column in only 2 minutes and 41 seconds. The final table is 52.3 GB and the log file comes out at only 600 MB.

Without "Minimal Logging", it takes 3 seconds short of a whopping 11 minutes and the log file explodes at 146.7 GB!

As a bit of a promotion for an event, the Ohio North Data Training group (formally, the Ohio North PASS chapter) has fired up an SQL Saturday (formally, SQL Saturday, Cleveland) in Akron, Ohio and the even occurs on May the 2oth. I'm doing the presentation that I'm revamping for that event. It also covers a technique called "Pre-Aggregation" (I credit Peter Larrson as the originator of that term) where I create a "Crosstab" report from 20 Million of the 100 Million rows in about 930 milliseconds using "conventional methods". Then, I demonstrate how the proper use of an "Indexed View" can negate the need for a "Data Warehouse" and ALWAYS be up to date without even breathing the abbreviation of "ETL" in 1 millisecond! And, yep... I introduce how to create such data and "Minimal Logging", as well.

Here's the info link for that event...

https://ondt.org/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lidou123 Hall of Fame Points: 3186 More actions · Answer 10

Thks for your help.

I will try this tool.

But users want to test with real data.

I will try this tool for the next project

Lidou123 Hall of Fame Points: 3186 More actions · Answer 11

Here is what I will propose to my Azure architect.

At each release:

1- I will launch a copy of the production to the Test environment.

2- I will limit access to the Test environment only to Dev and Test teams. I'm thinking of performing the database copy with a sql script.

Something like that:

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell#copy-to-a-different-subscription

I'll call this script with Azure Automation.

Another idea is to do it with DevOps. What do you think ?

Michael L John One Orange Chip Points: 27055 More actions · Answer 12

Lidou123 wrote:

Here is what I will propose to my Azure architect.
At each release:
1- I will launch a copy of the production to the Test environment.
2- I will limit access to the Test environment only to Dev and Test teams. I'm thinking of performing the database copy with a sql script.
Something like that:
https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell#copy-to-a-different-subscription
I'll call this script with Azure Automation.
Another idea is to do it with DevOps. What do you think ?

Step 2 needs to be remove access, and step 3 needs to be obfuscate the data.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Lidou123 wrote:

But users want to test with real data.

Then you really, really need to make sure that security is as air-tight as it is in production and management needs to be advised of the instance so that they can back you up on the stiff security requirements.

At no time should things like real social security numbers or bank account numbers be exposed even in a "real data" test environment and so the extra step that Michael point out above is an absolute must. If medical records are involved, then you just can't do it. You need to check HIPAA requirements or fail an audit, which could (and should for flagrant violations) drive you out of business.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

I have a feeling people want to test with real data because I suspect the database is not in a normalized state. That is, there is probably a repeat of data in the other table of things like people's names and such So relating with foreign keys is lacking is my hunch.

----------------------------------------------------