Post reply

Creating a reporting database - log shipping vs replication

  • October 18, 2005 at 12:46 pm

    #102870

    Hi all,

    I need to setup a "real-time", or as close to that as possible, copy of our database for reporting purposes.  I have a seperate server and was advised at work to use log shipping.  That was a "buggy" thing to get running, but it is now working. 

    However, now that it is (nightly full restore and transaction logs every 15 minutes) it kicks out any users and fails any jobs when the transaction logs are loading.  After doing more research I see that it needs exclusive rights to do the transaction logs.  Not only that, but the default users that the front-end application needs to access the reporting database are not being restored properly either so the front-end can't access the database.  These are both serious problems for me...

    Does anyone know a way around this or should I look to a form of replication?  I would appreciate any expertise on the matter

     

    Thank you,

    Aria

  • October 18, 2005 at 12:52 pm

    #598040

    If the "real-time" part is "very real" you have no choice but replication!!!

     


    * Noel

  • October 18, 2005 at 2:33 pm

    #598064

    (1) What noeld said. We had a system like this and it worked well, but we could get away with updating it once a day. If they need up to the minute reports, log shipping will be way too inconvenient. (Too, if you only need a small fraction of the database for reporting requirements, you might be better off replicating over only that which you need.)

    (2) Barring that, you'd need to configure log shipping to only apply t-logs every X hours. As for the (SQL authenticated, right?) logins, you need to set them such that the "internal IDs" assigned to them match on each server. (This doesn't apply to NT logins, as SQL doesn't store their security tokens.) The "proper" way to manage this is (I believe) with the import/export wizards; the perhaps more expedient way can be to read the SID from master..sysLogins and pass that as the @sid parameter in sp_addLogin (though this will require dropping and recreating the login).

    (3) I once toyed with a round-robin methodology, where you'd have two servers (or maybe two instances of SQL Server on one box), and one would be avaliable for active use while the other was loading the next set of data. It'd take a bit of fancy footwork on the data access layer to track which server a user should talk to at any given time. No clue how effective this might be...

       Philip

     

  • October 19, 2005 at 9:40 am

    #598331

    Hello

    I have been going through the same process on my end. What I ended up doing is doing a nightly backup of my DB that overwrites itself everynight with the same name. After it's done backing up I added a 2nd job that copy that DB to a hidden share on my other sql server. I have a job that runs on the second server that goes and finds the copy of the DB>restores it>replaces the copy of the DB that is on that server. This happens 1 x every night. It works great! The data is 24 hours old but if you need to you can run above steps more often. I have replication setup as well for real time on our dev environment that works great as well. I currently at home but if you need, I can email you the steps or post the scripts that I created for the jobs. Let me know.

    Thanks,

    Lawrence

  • October 19, 2005 at 10:10 pm

    #598567

    Hi Lawrence , I am interested in automating restores to a reporting database on the same server. Can you post scripts please? How do you deal with adding users back to reporting DB ?

     

    Thanks ,

    Mike 

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply