An easy way to synchronize data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » An easy way to synchronize data

25 posts, Page 1 of 3

1 2 3 »»»

An easy way to synchronize data

Rate Topic

Display Mode

Topic Options

Author

Message

imani_technology

Posted Friday, January 25, 2013 7:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:42 PM
Points: 192, Visits: 506

We are about to begin User Acceptance Testing. The business users want to use a copy of "live" production data in the test environment. Basically, they want to compare their test reports to the reports they currently use in the production system. To support them, I would like to have the data from the production back end SQL Server 2008 R2 database copied over to the test environment. These will only be incremental things, since syncing will happen daily.

What is an easy way of doing this? I would like to avoid using replication if possible since this is only a temporary situation. Are there any recommended third party tools?

Post #1411968

Gazareth

Posted Saturday, January 26, 2013 4:30 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Do you need to retain changes made on the test system? Or just have a copy of live data to work on?

Post #1412005

imani_technology

Posted Sunday, January 27, 2013 10:42 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:42 PM
Points: 192, Visits: 506

I just need a copy of the live data in the test environment. I hope to get a fresh set of data for the test environment once a day.

Post #1412184

Vedran Kesegic

Posted Monday, January 28, 2013 2:40 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 343, Visits: 1,089

Take full backup once.
Daily take differential backups and restore them to test after a full backup restore.
Turn "instant file initialization" and backup compression on to speed-up the process.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1412276

Bhuvnesh

Posted Monday, January 28, 2013 3:37 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

imani_technology (1/25/2013)

What is an easy way of doing this? I would like to avoid using replication if possible since this is only a temporary situation. Are there any recommended third party tools?

you can consider database mirroring too , easy to handle

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1412298

Gazareth

Posted Monday, January 28, 2013 3:53 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Taking a backup of live and restoring to the test system will be the easiest way to do this.
Cheers

Post #1412310

imani_technology

Posted Monday, January 28, 2013 10:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:42 PM
Points: 192, Visits: 506

Is there an automated way to backup and restore? Also, the test environment is on a totally different server from the production environment.

Post #1412564

imani_technology

Posted Monday, January 28, 2013 11:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:42 PM
Points: 192, Visits: 506

From what I understand about database mirroring, you cannot use the mirrored database directly. Is that correct?

Post #1412587

SQLSalas

Posted Monday, January 28, 2013 12:02 PM

Valued Member

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:41 PM
Points: 72, Visits: 298

That is correct, you cannot use the mirrored database *directly*.

However, you can create a snapshot of your mirrored database and have the report pointed to that Snapshot database (different database name).
Whenever you wish to have an updated snapshot (to reflect the updates/changes in your production DB), you can just drop and recreate your snapshot.

Hope that helps.

Post #1412591

imani_technology

Posted Monday, January 28, 2013 12:04 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:42 PM
Points: 192, Visits: 506

I'm considering mirroring a "staging" database that's in the production environment. Can I use SSIS to extract data from the mirrored database directly or will I need to have the SSIS package access a snapshot?

Post #1412592

« Prev Topic | Next Topic »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions