December 12, 2011 at 1:49 pm
Hello
A project came up today to have a SQL 2005 database copied over daily from a remote location into our SQL 2008 environment- port 1433 is open - the data and the data structure needs to be copied over daily.
Is transactional replication the best solution?
They have a mirrored server - would it be best to set up transactional replication on the mirrored server?
I'm researching now but I need an answer fast and anything anyone could suggest would be appreciated - I have zero experience with replication and I want to make sure I'm on the right track.
Thanks in advance
Dave
December 12, 2011 at 1:58 pm
Transactional replication is more about real-time or near-real-time synchronization.
What you want is either snapshot replication, log shipping, or just plain ship a full backup over and restore it nightly. A lot of that will depend on size and transactionality of the database.
Small database, I'd just copy a backup over and restore that and be done with it.
Bigger database, with few updates (mostly inserts), probably snapshot replication.
Big database with lots of inserts/updates/deletes, probably log shipping.
Of course, it also depends on what state you need the copy to be in. Is it just there for possible disaster recovery, or do you need to connect users to it for querying data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 2:08 pm
Thanks Anon - the db is estimated at 6gb and it is being copied over so it can be imported into Oracle. Not sure on the amount of activity - I will keep investigating - thank you for your quick reply.
Dave
December 12, 2011 at 2:18 pm
You're welcome.
In that case (size of DB), either log shipping or snapshot replication will be best. Probably log shipping, using the Standby options so the database can be queried (read-only), but it'll depend on the results of testing both options.
By the way, "Anon" is the attribution of a quote at the end of my signature. I'm either "GSquared" (per the sig), or "Gus" (the actual name). Pleased to meet you. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2011 at 6:19 am
I would test you can copy files to the remote location before deciding on log shipping since all you mentioned was port 1433.
Another option could be mirroring with database snapshots if you're using enterprise edition. I'd veer away from snapshot replication due to the overhead involved in generating the snapshot (table locking etc). With logshipping you'd only have to apply the changes made since yesterday.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply