SQL Server Magazine

Hi!

We have three installs of SQL Server 2008 Enterprise and would like to replicate them to a fourth SQL Server 2008 Enterprise server. 2 of the installs are remote (different city) and available via a 10 MB network connection. We would like to sync changes from those databases once a day. 1 of the installs is in the same building and it has access to a fiber connection. We would like to sync changes from this database say every 15 minutes.

The purpose of the 4th server is strictly for staging the three databases, writing a converter, and creating a completely new database schema with populated data. The new database will eventually be shipped to yet another server, so you can think of this specific server as a processing server only.

The databases are not very large nor transaction heavy; about 500 users across all 3 databases. However, they do need to remain semi-responsive 24/7, so we don't want to grind them to a halt with a replication process.

What is the best mechanism for replicating these databases? Is Log Shipping where we should be looking, or the traditional publish-subscribe functionality? Any advice would be greatly appeciated.

Thank you for your help!
Michael

Hi!

There is no user access to the databases that are on the staging server; we will be authoring an SSIS package that will monitor for changes (change tracking) and convert changing into the new database (on the same server.) We would then replicate this out to another server when the time comes.

Thanks!
Michael

There is a fair amount of conversion and massaging of data that we felt it merited it's own server that is in isolation. The change tracking has some performance overhead as well (based on our testing.) [I know replication does too.] It seemed easier for us to bring the data from three locations into one server, especially because two of the sites are remote on a slow connection. (The plan is to dial up the update interval from once a day to something more often at the two locations and measure the performance.) We also felt that propogating the data into the server does create another potential backup location (though not the driving factor here.) I am ok considering running change tracking at three locations, though I was all excited about managing the code and processing from a single, detached location when I started.

We can run the SSIS conversion package on our staging server say every 15m; it does not have to be real time. So I would assume the connection issue could be managed. I was just curious if you or anyone out here had experience with log shipping vs. other Microsoft approaches for syncing remote databases; it sounds like we have a few options with SQL Server and I didn't want to waste the time testing every possible approach if there was a consensus on what works best. If our scenario is not typical, or I can't adequately define the problem out here, we can certainly try everything. Just trying to save some time.

Thanks!