Database Sync

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Database Sync

14 posts, Page 1 of 2

1 2 »»

Database Sync

Rate Topic

Display Mode

Topic Options

Author

Message

Nita Reddy

Posted Wednesday, April 16, 2008 12:34 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:42 PM
Points: 423, Visits: 315

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?

Post #485886

Michael Earl-395764

Posted Wednesday, April 16, 2008 1:30 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #485924

RBarryYoung

Posted Wednesday, April 16, 2008 4:52 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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."

Post #486025

Chandrachurh Ghosh

Posted Thursday, April 17, 2008 5:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 18, 2012 12:50 AM
Points: 174, Visits: 315

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
INFOSYS Limited
Quality is not an act, it is a habit.

Post #486289

Nita Reddy

Posted Thursday, April 17, 2008 7:27 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:42 PM
Points: 423, Visits: 315

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

Post #486410

John Mitchell-245523

Posted Thursday, April 17, 2008 7:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 4,425, Visits: 7,187

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

Post #486418

Michael Earl-395764

Posted Thursday, April 17, 2008 7:36 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

"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.

Post #486421

Chandrachurh Ghosh

Posted Thursday, April 17, 2008 7:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 18, 2012 12:50 AM
Points: 174, Visits: 315

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
INFOSYS Limited
Quality is not an act, it is a habit.

Post #486434

fstanzini 98683

Posted Tuesday, August 03, 2010 7:15 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 2:06 PM
Points: 10, 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

Post #962772

free_mascot

Posted Wednesday, August 04, 2010 1:37 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768, Visits: 1,312

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."

Post #963265

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions