December 1, 2006 at 6:29 am
Hi Guys/Gals.. We are going to start working on a new method of refreshing a DB daily on several servers and I was hoping to draw on the expertise here.. this is the situation..
We currently run a process nightly that truncates the tables in a DB (on its own server) (serverA) and repopulates after doing the necessary processing on another box(serverB). Now this updates one server and then we run the same process pointing at another server (they should mirror each other when done as they are loadbalanced) If there are any discrepancies it could cause issues in the application. So we are thinking of changing this process to a Backup/restore instead of the nightly process being run twice. We are also introducing 2 additional servers (ServerC/ServerD) at another site which will also need to match. (Keep in mind the DBs are <=1GB in size. We use litespeed so backups are under a minute. And there must ALWAYS be one of these DBs populated at all times at both sites.)
So my question is.. should we pursue running the process on ServerA then doing a backup and restoring to Servers B,C,D or would Replication be a viable option? Any ideas about another way to do this?
As always.. Thanks!
Rich
December 1, 2006 at 11:10 am
At first glance, I'd say that replication would be a good option. You can replicate to multiple subscribers and if you use transactional replication, the databases would be more syncronized than when using a once-a-day restore or reload. I assume that the databases on servers B,C, and D aren't used for OLTP since they're only reloaded daily, so you could use snapshot replication.
You might also consider log shipping or (in SQL 2005) database mirroring.
Greg
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply