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,

  • 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?
    – NotMe
    Oct 21 '11 at 21:30

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)

  • 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. Oct 21 '11 at 22:58

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.

  • 1
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy