SQL DB 2 TB

Question

SQL DB 2 TB

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
January 31, 2024 at 4:16 pm

#4370841

I have a SQL database of 2 TB running on production. There is a need to refresh this database on Q environment on a daily basis. Cannot implement SQL to SQL replication coz InfoSec has an issue doing so (from prod to non prod)...Backup/restore takes incredibly long time.
Any thoughts/suggestion/advice?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Redgate Clone, perhaps? (Link)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ratbak Ten Centuries Points: 1358 More actions · Answer 2

How long do backup & restore take?

Are one or both environments virtualized?

Are they on same LAN? Have disk and network health & performance been tested? Results? What is I/O locally and across network?

Are you restoring from network or copying backup locally to QA server to restore?

ratbak Ten Centuries Points: 1358 More actions · Answer 3

Are there concerns about PII? Does data need to be anonymized?

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

I'd try to use differential backups, assuming you have a large amount of free disk space in qa, or could get it easily.

When you do a full backup in prod -- which is hopefully only periodically, not every day -- then restore that immediately to qa WITH NORECOVERY, but do it *twice*.

Do WITH RECOVERY immediately on one copy as current qa db.

Each day, use the second copy to quickly bring qa up to date using the latest daily diff from prod. Then immediately restore the full backup again WITH NORECOVERY to be ready for tomorrow's recovery. The only really big delay is after you've done a full backup in prod.

If you do full backup in prod every day, this approach only saves you time every other day and therefore may not be worth it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

If InfoSec already has an issue with replication because you are going from Prod to Non-Prod, then using backup/restore is also going to be an issue. Anything that allows production data into a non-production environment will not pass security.

You need to review with the people making the request to refresh the database and determine exactly what they are expecting. If they are looking for a copy of production - then the answer is simple. They can't get that because InfoSec won't allow it and they need to figure out another option.

If they are looking for something different - maybe an anonymized version of the database refreshed every day then you probably cannot meet that requirement. It would require restoring a copy to another system in prod - running scripts to anonymize the data - back up the anonymized database - restore that backup to non-prod.

There are tools available to do that - Redgate has one, but they are very pricey.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

SQL_Surfer wrote:

I have a SQL database of 2 TB running on production. There is a need to refresh this database on Q environment on a daily basis. Cannot implement SQL to SQL replication coz InfoSec has an issue doing so (from prod to non prod)...Backup/restore takes incredibly long time.
Any thoughts/suggestion/advice?

Please consider what you just said... Duplication of prod data to non-prod is an issue because of a data security aspect. Think about it.

--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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Jeffrey Williams wrote:

If InfoSec already has an issue with replication because you are going from Prod to Non-Prod, then using backup/restore is also going to be an issue. Anything that allows production data into a non-production environment will not pass security.

Preach it!

--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)