Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

We have main database, through out day we insert/update this DB through main application(windows). We have another web application which is suppose to just display content from main DB but instead we have created new DB which we update through out day either manually or from code.

I want to get rid of this manual/code process of updating one DB to another DB. What are the options? Eliminate second DB and just read from main DB? Replication? Mirroring?

It has to be real time update. Thanks,

share|improve this question
Check out "Log Shipping" between SQL servers. That's something I've done in the past for DB replication. msdn.microsoft.com/en-us/library/ms190640.aspx and support.microsoft.com/kb/314515 – John Batdorf Oct 21 '11 at 21:04
1  
Which version of Sql Server do you have? – Kris Krause Oct 21 '11 at 21:22
Replication/log shipping or snapshop might be the way to go. You can also utilize Red Gate's Sql Data Compare (I do not work for them or have any vested interest) from the command line. – Kris Krause Oct 21 '11 at 21:24
Are both databases on the same subnet? – Kris Krause Oct 21 '11 at 21:26
Why the second database? – Chris Lively Oct 21 '11 at 21:30
show 3 more comments

migrated from stackoverflow.com Oct 24 '11 at 14:00

2 Answers

up vote 2 down vote

You could implementation Replication if there is a subset of data that you are looking to read from. Replication publishes articles to subscribers. It's a great way for a subset of data that needs to be recent data.

If you are looking for a read-only copy of the whole database, though, and the data doesn't need realtime accuracy, you could create snapshots every morning with a job. That way the OLAP can be done against the snapshot. Or if you need the data to be a maximum of 4 hours old you can do that. (or whatever schedule you find the need to "refresh" the data)

share|improve this answer
we are looking fro real-time accuracy, we copy db almost instantly currently from one to another DB. I have been thinking about replication too. Thanks, – user570715 Oct 21 '11 at 21:25
@user570715 ok, then your best bet is definitely replication. Good luck, and let me know if you have any questions setting that up. – Thomas Stringer Oct 21 '11 at 22:58
up vote 1 down vote

I think this really depends on what happens during the process of updating the "read only" copy of the database.

If it's a straight copy, then log shipping is a great way of doing things.

If the process does some computation to create different views of the data for the site, then you'll need something with a bit more flexibility.

For example, you have one database were everything is entered and another called a "Data Warehouse" where you archive it to. Part of the archival process is doing a lot of roll up of the data to simplify reporting.

Usually SSIS packages are created to move that data and run the appropriate data changes. SSIS packages can be scheduled to execute however often you want and provide a tremendous amount of flexibility in what you can do.

share|improve this answer
I cringe every time I hear SSIS. – Kris Krause Oct 21 '11 at 21:25
WE are looking for real time update, not sure if warehousing or log shipping will work.... – user570715 Oct 21 '11 at 21:27
@KrisKrause: why??? – marc_s Oct 21 '11 at 21:32
I've just had bad past experiences with SSIS. And yes, most of it had to do with the implementation more than the product. – Kris Krause Oct 21 '11 at 21:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.