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.