SQL Server 2008 Replication vs Manual Database Updates

Question

Our scenario:

We have a main database that stores company-wide information. We have several retail locations which have their own databases they work off of. These locations need to use information in the company-wide database, and I do not want our main application to run cross database queries because if the main database has issues (locks or otherwise), I do not want it to cause the application to cease to function.

That being said, we're considering 2 things:

1. Replicate the data
2. Run an automated script to update the tables in each database on our own

I'm leaning towards replication, but I have to admit, I've never done it before (we do replicate data though, I just haven't handled it). I've been told that replication can be pretty intensive for large tables. Any thoughts, suggestions, or good articles about this?

Answer 1

It is generally best to use a standard tool to do something than building your own code.

Best in this case being less cost and less chance of errors.

SQL Replication is built to do exactly this job, so I would recommend that you use it.