Sync Production Databases with Stage

Question

Post reply

Sync Production Databases with Stage

Minaz Amin

SSChampion

Points: 11052
More actions
March 8, 2010 at 10:59 pm

#221404

Hi All,
How to sych production and stage environment
Stage Databases are in Read write mode and DEV/ QA team will be carry out their testing, hence need the latest production data.
What will be the best strategies to achive this?
I was thinking to implement full db restore once in a week, but again Stage will be one week behind the production data. Need your suggestions.
"More Green More Oxygen !! Plant a tree today"

Viewing 13 posts - 1 through 12 (of 12 total)

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

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 1

Depending on certain factors and also how your infrastructure is setup. You have options.

You can use backup/restore to refresh the data.

You can use SQL replication.

You can use SQL log shipping.

You can use a software called doubletake.

You can mirror the database as well.

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 2

Dean Jones-454305 (3/9/2010)
Depending on certain factors and also how your infrastructure is setup. You have options.
You can use backup/restore to refresh the data.
You can use SQL replication.
You can use SQL log shipping.
You can use a software called doubletake.
You can mirror the database as well.

The problem with Mirroring and Log shipping is it leaves the database in a recovering state, which I'm guessing is not an option if you testers and QA people need to write to the database.

SQL Data Compare from Redgate, sound like it might be similar to Double Take (although i'm sure), will let you sync data between environments and you can also use SSIS to copy the data over.

If you are developing your database, then restoring from production can be a problem, if your development and test environments change before final changes are made to production then these changes will be lost when the backup of the production database is restored.

Why do your test and QA people need the latest data all the time? Wouldn't a point in time view of the data suffice?

Gethyn Elliswww.gethynellis.com

GTR SSCrazy Eights Points: 8007 More actions · Answer 3

How many databases you have to restore to sync with prod? Are you using EMC SAN?

EnjoY!

Fidel Rodriguez SSC Enthusiast Points: 145 More actions · Answer 4

Hello there:

Depending on the complexity of the database, you may consider drafting an SSIS package to update the tables that contain new information.

I've gone down the path of using Agent to execute some scripts that I wrote using sqlcmd and xp_cmdshell to remotely restore, drop/create/map logins and users, etc after the backup on the source server completed (as opposed to having the target server try and figure out the backup file name, time of completion, etc).

The process became a pain point after the database grew to a size where backups and restores were too time consuming 400+ GB/ 1 hour to backup, 1.5+ hours to restore. This is magnified if the supporting hardware (in this case network cards / disks) aren't of reasonable quality.

Maybe as an interim solution you can do the backup / restore operation (which was our original goal) and then you can opt for a more elegant SSIS package that appends records? This could be permament if the hardware supports the SLA and the database will remain small in size.

We use double-take in my current environment. I've not fully explored the product; however, from what I've seen, it wouldn't be my first choice for DR or replicating data.

---FR

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

If the systems are on a decent SAN, a SAN Clone is a great option. Production system never goes down during the "transfer" and the dev system is only down for minutes even with nearly a Terabyte of data. Check with the folks from OPs in your company and see if they have it. Some SAN company sell it "extra" and others include it. Even if it's extra, it may be very worthwhile for you. It was for us.

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

GTR SSCrazy Eights Points: 8007 More actions · Answer 6

Jeff Moden (3/9/2010)
If the systems are on a decent SAN, a SAN Clone is a great option. Production system never goes down during the "transfer" and the dev system is only down for minutes even with nearly a Terabyte of data. Check with the folks from OPs in your company and see if they have it. Some SAN company sell it "extra" and others include it. Even if it's extra, it may be very worthwhile for you. It was for us.

You are correct that is why i asked if they have EMC SAN, Replication Manager come with package (SAN), you can refresh Terabyte Dev environment minutes.

EnjoY!

Minaz Amin SSChampion Points: 11052 More actions · Answer 7

Thanks everyone for the replies 🙂

So I have two options:

1. Use SAN Clone to refresh the data between Prod and Stage

2. SSIS package to append the data.

Will update the group once implement the process. Let me first speak to the IT team about the SAN Cloning.

"More Green More Oxygen !! Plant a tree today"

Minaz Amin SSChampion Points: 11052 More actions · Answer 8

We do not have SAN , I am going to use SSIS package to append the records. Any other pointers?

"More Green More Oxygen !! Plant a tree today"

Rome1981 SSC-Addicted Points: 445 More actions · Answer 9

Rome1981

SSC-Addicted

Points: 445

March 10, 2010 at 2:53 am

#1131216

Why not implement transactional replication?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Rome1981 (3/10/2010)
Why not implement transactional replication?

That works fine but it's a bit of a PITA to maintain compared to a SAN clone. Also, the purpose of a development server is to test Inserts, Updates, Deletes, etc... Transactional Replication won't "fix" those changes. You frequently need to make a copy of the Production environment to "put things back" and SAN clones do that very, very well.

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

Rome1981 SSC-Addicted Points: 445 More actions · Answer 11

Jeff Moden (3/10/2010)
Rome1981 (3/10/2010)
Why not implement transactional replication?
That works fine but it's a bit of a PITA to maintain compared to a SAN clone. Also, the purpose of a development server is to test Inserts, Updates, Deletes, etc... Transactional Replication won't "fix" those changes. You frequently need to make a copy of the Production environment to "put things back" and SAN clones do that very, very well.

That's the great thing about being a DBA, many ways to do one thing. I personally have never used SAN clones but that might be something to investigate.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Agreed... there are many ways to do one thing. I just think that, for the reasons previously stated, transactional replication from prod to dev would be the wrong thing to do here. If a SAN clone isn't available, a restore might be the thing to use to repair "damage" done by development.

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