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 12»»

Database Sync Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2008 12:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 431, Visits: 337
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
Posted Wednesday, April 16, 2008 1:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Wednesday, April 16, 2008 4:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
Posted Thursday, April 17, 2008 5:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 3:44 AM
Points: 179, 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.
Post #486289
Posted Thursday, April 17, 2008 7:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 431, Visits: 337
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
Posted Thursday, April 17, 2008 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Posted Thursday, April 17, 2008 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Thursday, April 17, 2008 7:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 3:44 AM
Points: 179, 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.
Post #486434
Posted Tuesday, August 03, 2010 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, August 04, 2010 1:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:45 AM
Points: 2,385, Visits: 1,843
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 »

Add to briefcase 12»»

Permissions Expand / Collapse