July 31, 2012 at 12:29 pm
What would be the best way to maintain a copy of a live db on another server to let the BI tool to use?
I would like to have a 15 minutes to 30 minutes interval between updates.
I read about log shipping and confused when i see a "limited read only access" and db status in "RECOVERY" or "STANDBY" mode in article
http://msdn.microsoft.com/en-us/library/ms187103.aspx
TIA
July 31, 2012 at 12:44 pm
Best depends on your environment and requirements.
Log shipping does stay locked in read only, which has some pretty big dissadvantages among them is often getting good query execution plans. But it's easy to setup and it's durable.
July 31, 2012 at 2:29 pm
July 31, 2012 at 8:55 pm
Thanks for the replies
Do you think the replication will allow the users to access both DBs (separate users/purposes) while replicating?
Never done these before and need to read more on these technologies.
August 1, 2012 at 12:32 am
Like others have said, it depends on your configuration...
Logshipping is very easy to do and you can place the DB's in stand-by mode (which will disconnect users when the logs are restoring, but you can turn this off - I've successfully tested it before but never tried it in a production setting) - Logshipping will ship EVERYTHING over to the secondary server...therefore what happens on your primary server will get shipped and applied to your secondary server...again, EVERYTHING.
Replication (transactional or snapshot) is also pretty easy to set up. In these scenarios you are pretty much only sending over data to the server. You can limit/filter your publications to say...only replicate the past 6 months of data, and so forth...
You can also change settings to send of schema and procedure changes but that can be tricky. I personally prefer transactional replication...mostly for its simplicity and reliability. Like everything else in SQL there are some things to watch out for...For instance, in transaction replication you can only replicate tables that have primary keys (for others you would need snapshot replication or merge), you'd want to DISABLE the immediate_sync and allow_anonymous settings (otherwise every time you add/drop a new table from your publication every table gets a new snapshot - which is bad for a busy production server)...
Replication will easily allow your BI users to access both locations at the same time. Replicated data is available all the time (well, as long as your log reader agents are running without errors)
Based upon your configuration, if I were you I'd read up some on the resources each would require and make a decision from there. They're both easy to set up, but how well they will work for you will depend on hardware, etc, and how you set it up
Hope this helps!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply