Home / server / Questions / 249046
Accepted
Scott Chamberlain
Asked: 2011-03-19 07:33:02 +0800 CST

Log shipping on select tables

  • 772

I know I am most likely using incorrect terminology so please correct me if I use the wrong terms so I can search better.

We have a very large database at a client's site and we would like to have up to date copies of some of the tables sent across the internet to our servers at our office. We would like to only copy a few of the tables because the bandwidth requirement to do log shipping of the entire database (our current solution) is too high. Also replication directly to our servers is out of the question as our servers are not accessible from the internet and management does not want to do replication (more on that later).

One possible Idea we had is to do some form of replication on the tables we need to another database on the same server and do log shipping of that second smaller database but management is concerned that the clients have broken replication (it was between two servers on their internal network however) on us in the past and would like to stay away from it if possible.

Any recommendations would be greatly appreciated. If using some form of replication is the only solution, I am not against replication, I just need compelling arguments to convince management to do it.

This is to be set up on multiple sites that are running either Sql2005 or Sql2008 we will have both versions on our end to restore the data to so that is not a issue.

Thank you.

sql-server-2005 replication log-shipping

4 Answers

  1. Best Answer

    Have you examined the Microsoft Sync Framework? This is a Microsoft data synchronization technology that supports SQL Server and other data stores, supports synchronizing subsets of those data stores (only specific tables or other things) and of course, Microsoft tries to integrate it well into a number of its technology stacks.

    Here's a related StackOverflow question, "Can I sync only a subset of my tables?". The too long; didn't read answer is yes you can.

    This should save you a significant amount of time over implementing a roll-your-own solution.

    • 1

  2. You have pretty much covered the two options which are available to you. Unless you want to build some sort of SQL Service Broker solution or the like, but that will still require connectivity between the two servers. Based on what you have described SQL Server replication between their server and yours would be the correct solution to the problem.

    • 0

  3. The only thing I can think of is to roll your own solution. This is what I would do:

    A batch that runs on the server that does a bcp out dump of the table, zip it, and put it on an FTP.

    A second batch running at your office checks the FTP on a regular basis, if it finds a new file, unzips it, runs DELETE FROM [tablename] and then a bcp in of the unzipped file.

    • 0

  4. If they can break replication, then they can most likely break anything else you set up. I'd probably go with either transactional or merge replication (with some log shipping if needed), and make sure you script the setup steps for the subscriber so you only have to hit F5 a few times to repair what they foul up.

    • 0