Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

An easy way to synchronize data Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 7:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
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
Posted Saturday, January 26, 2013 4:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
Do you need to retain changes made on the test system? Or just have a copy of live data to work on?
Post #1412005
Posted Sunday, January 27, 2013 10:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
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
Posted Monday, January 28, 2013 2:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1412276
Posted Monday, January 28, 2013 3:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1412298
Posted Monday, January 28, 2013 3:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
Taking a backup of live and restoring to the test system will be the easiest way to do this.
Cheers
Post #1412310
Posted Monday, January 28, 2013 10:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
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
Posted Monday, January 28, 2013 11:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
From what I understand about database mirroring, you cannot use the mirrored database directly. Is that correct?
Post #1412587
Posted Monday, January 28, 2013 12:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:25 PM
Points: 72, Visits: 434
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
Posted Monday, January 28, 2013 12:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse