SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Sync


Database Sync

Author
Message
Nita Reddy
Nita Reddy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 413
I have 2 sets of database one for Prod and other for reporting purpose

marketing
marketing_Report

I want my reporting database (marketing_report) to be sync everynight with production (marketing) they both are on same server. I do not want to use replication. What is the best method to do. I want to automate this on daily basis.

I can do backup and restore but is there any other way to do?
Michael Earl-395764
Michael Earl-395764
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27029 Visits: 23078
It is going to depend on your exact requirements. Does the report database have schema specifically for the reports? It would be common to want different indexes, stored procedures and views in this database.
If that is the case, you would need to build something to either clear the data and copy fresh data or make the necessary updates. SSIS would be well suited for this purpose, or since it is all on one server, you may want to use a group of T-SQL stored procedures. I think I would lean toward SSIS making the assumption that eventually the databases would be on different servers and I would want to avoid using linked servers at that point.
If they will have the same structure, why have two databases? If you are trying to have a point-in-time version of the database, you may want to look into database snapshots.
If you are trying to offload work, having them on the same server will only help with resource locking (and with snapshot isolation you should be able to avoid anyway). However, if this is what you are trying to do, if you are already doing a backup to disk on the server every night, restoring the backup would probably be the least-intrusive solution to your existing processes and give you the benefit of testing your backup daily. You could also use log-shipping to get some built-in tools for this type of solution.

If you do a restore operation, make sure you have a way to ensure nothing is connected to the database or you will have someone leave Management Studio open on their desktop overnight and break your reporting solution.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68438 Visits: 9519
Why don't you want to do Backup/Restore? It is probably your best choice.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Chandrachurh Ghosh
Chandrachurh Ghosh
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 331
Points worth noting -

For Backup/Restore
1> As the databases are on the same server, the necessity for moving data is not there. But, in future, if you plan to move the secondary database to any other system, network issues effect the process.
2> Each time you restore, scripts has to be run to take care of orphaned users.

For Replication
1> For transactional replication only the changes are migrated
2> All the tables for replications have to have a primary key.
3> You can have reporting services running on the replicated database

For Log shipping
1> Only the change move through the network (if on separate servers)
2> Reporting services cannot be setup

Copy database
1> Not recommended

There are other techniques using scripts and logic......which are tidious, time taking and needs thorough testing, but worthy enough

SP + SQL Job
Trigger

Hope this helps......

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Nita Reddy
Nita Reddy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 413
Thanks all for your valuable information.

What about Snapshot is it worth using that, how does it work? I need this basically for reporting purpose that to on same server. I mean need to generate report for high level people.

Thanks
Nita
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68303 Visits: 17516
Nita

There's plenty of information on database snapshots in Books Online. Have a read through that, and post again if anything doesn't make sense to you.

John
Michael Earl-395764
Michael Earl-395764
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27029 Visits: 23078
"Snapshot Replication" makes a copy of the entire database on a scheduled basis. This may be an option if you are trying to make a second database just for your reporting.

"Database Snapshot" is not actually a copy of a database, it is a collection of the database pages before they are modified - basically giving you the ability to look at what your database looked at when the snapshot was taken. This would be useful if you wanted to ensure re-running reports would always yield the same results.

I think this thread has illustrated that there are a bunch of options. You should get the requirements together before choosing. Determine how much latency is acceptable, if the users expect re-running reports throughout the day will give the same results, if your reporting database schema needs to be different than that of your OLTP system, how and how much the OLTP system and reporting system will conflict for SQL and hardware resources. Then you need to look at the available options and try to find the peg that best fits the hole for your situation. Take the time to do some testing and be sure to think about the future a bit.
Chandrachurh Ghosh
Chandrachurh Ghosh
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 331
If your intention is to setup a report database, I will strongly suggest the SQL Server Reporting Services....you will be overwhelmed by the features and things you are able to do with it.....and it is really simple.....just download a white paper or tutorial on reporting services and get going.....Best of luck

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
fstanzini 98683
fstanzini 98683
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 5
What version Of SQL Server are you ruinning? If you are using 2008 think about useing the MERGE command and SSIS to move data from your one server to the other. MERGE is a new feature and will allow you to insert, update and delete all in one command as long as you have good primary keys
free_mascot
free_mascot
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13409 Visits: 2250
If the database size is small than you can go with the 'Snapshot Replication' to achieve your goal. Which is also easy to maintain compare to other soultions.

Backup and restore restore will fail if some of the users are connected; else you need to kill the users and don't know howlong it will take? In fact, you may not receive any orphan user issue as you are restoring it on the same instance.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search