We have a SQL Server 2012 instance that contains a database with data for many product lines.

We are looking for a way to take subsets of this data into another database on the same instance.

This doesn't have to be continuous as we are looking for ways to make it easy to export backups of the subsets that we can send out to vendors (we have a company that loads this data into their own sql server to produce a product we sell, naturally its huge and we want to trim it down to just the product lines they work with on a regular enough basis) or use to load SQLite DB's for use with offline applications.

ProductDB - the superset of all product line data.

ProductLine1DB - A subset identical in structure to ProductDB, but with data only relevant to product line 1.

ProductLine2DB - A subset identical in structure to ProductDB, but with data only relevant to product line 2.

This can be done nightly, weekly, as often as we need to but not a continuous replication.

We don't want to add in distributor servers or even extra subscriber servers. We would like the replication to happen on the same server with the subscriber databases being on the same server.

Is this a good candidate for transactional replication?

Why or why not?

We obviously have other options to load this data, but it seems like this might be a good case to use something like transactional replication. I've heard its powerful but prone to issues when not setup optimally.

Note: I am not a DBA, simply a developer, but we are currently looking for a new DBA and some of us are taking over duties like this temporarily.


Is this a good candidate for transactional replication?

I would use SSIS to incrementally load data (if the data set is too big) or just freshly load the data (if the data is less) every night (or anytime).

This way you dont have to create any publication, distribution database or subscription.

Easy to manage when you do upgrades on your main database - especially changing schema.

Another option would be to use BCP OUT and BULK INSERT IN. I have a script to help if you go with this. Make sure to adjust the script to filter out the data you need using where clause :-)

Note: Don't get me wrong, as I am not telling, you can't use T-Rep, but I don't see it to fit perfectly in your situation. There are better tools that you can use to cater your scenario.

  • 1
    I'm going to look into these approaches further and I'll come back for acceptance. ;) Exactly the kind of advice I was looking for. I had a feeling I was trying to use a power saw to open a can of beans. Feb 12 '15 at 19:47
  • Would the fact that many of the tables have foreign keys make either of these approaches more useful than the other? Feb 12 '15 at 21:50
  • @AndrewKluthe depends on the data size, but SSIS or BCP can handle that very efficiently.
    – Kin Shah
    Feb 21 '15 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.