December 1, 2008 at 12:22 pm
Hi,
There may be threads on this but i'm not sure what to search. I have a database in one physical location, that the developers would like to access a daily copy of for testing and reporting. We have found that SSMS over the WAN can be somewhat slow, so I was thinking of a log shipping type of thing to the remote site nightly so they can access a non production database and the speed would be fast.
Does this make sense?
December 1, 2008 at 12:31 pm
Log shipping has worked pretty well for me in the past. I even had one place in which we had to FedEx the log files on tape to a secondary site and restore them each day for reporting.
It is pretty solid as long as you try to stick to the defaults. Once you get outside of the defaults, MSSQL replication gets complicated quickly - even log shipping.
December 1, 2008 at 12:58 pm
If I remember correctly log shipping wouldn't really work so well for this circumstance as you'd need to keep the remote db in the restoring state for log shipping to continue to work. Also, you'd need to be careful with your backup chain.
If you have Ent Ed you could look into snapshots either of the source database, or you could set up mirroring and take snaps of the mirror to allow access to the mirror on your remote site...
Also there's always the whole, compress a backup and send it over the wire bit, but that depends on your db size and connection bandwidth to your remote site.
-Luke.
December 1, 2008 at 2:53 pm
hmmm thanks for the replies so far....
i guess this will be harder than I thought
December 2, 2008 at 3:20 pm
Anythign else besides compressing the backup and sending over the wire? I think that might be where i'm stuck..its about a 16GB database
December 3, 2008 at 1:25 pm
SQL Server Backup files compress very well. Log Shipping is probably appropriate for you.
* Noel
December 3, 2008 at 1:52 pm
But didn't a previous poster say that I'd need to have the remote database in a constant receiving state?
December 3, 2008 at 1:59 pm
if the developers are ok with the database being read_only log shipping will work for you. Just choose the option to restore the logs in standby mode, then the database will be readable but not updateable.
If you schedule the log shipping and copy jobs carefully you can continue to copy logs over during the day at intervals but only restore them out of hours. Be sure to also select the option to disconnect users before a tran log restore.
IF users need to update the copy of the database then you are stuck with a full nightly restore (16GB is not that big so i would have thought that would be possible), or mirroring with snapshots (you need enterprize edition for that)
---------------------------------------------------------------------
December 3, 2008 at 2:02 pm
apologies, brain freeze, mirrored snapshots would be read only as well......................
---------------------------------------------------------------------
December 3, 2008 at 2:03 pm
alrightie...so backup/zip/copy/restore would be the option if they need to update...
thanks guys
December 3, 2008 at 2:16 pm
Matt Rose (12/3/2008)
alrightie...so backup/zip/copy/restore would be the option if they need to update...thanks guys
you forgot unzip 🙂
---------------------------------------------------------------------
December 3, 2008 at 2:17 pm
hah, I suppose that would help!
December 4, 2008 at 9:01 am
Matt Rose (12/3/2008)
alrightie...so backup/zip/copy/restore would be the option if they need to update...thanks guys
I think Transaction Replication with Update Subscription or Merge Replication would work if you will need to update at the SUBSCRIBER location
http://msdn.microsoft.com/en-us/library/ms151827(SQL.90).aspx
Data changes are not tracked for snapshot replication; each time a snapshot is applied, it completely overwrites the existing data. Transactional replication tracks changes through the SQL Server transaction log, and merge replication tracks changes through triggers and metadata tables.
December 4, 2008 at 9:12 am
i was just beginning to look through the replication idea...this does sound promising, but I can't find any good webpages describing the right setup....
December 4, 2008 at 9:29 am
snapshot replication could work for you, but would be more complex to set up and maintain then the good old backup and restore.
replication is really for scale out, where you want to spread the production load across servers. i.e the updates made on the copy would be reflected onto the primary. It does not sound like that is what you want so again backup and restore is still best for your needs.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply