Replication Options with data subsets

We have an application that makes of of a SQL Server 2012 (ServerA) database. Some data that we need to display, though, comes from another database, but it's SQL Server 2008 (ServerSOURCE). The database is also on a separate server.

The plan is to create a replication database on the same server as ServerA, called ServerREP, and replicate data from ServerSOURCE into ServerREP. But we only want certain tables from ServerSOURCE. It's a small subset of the tables. ServerSOURCE has hundreds of tables. We only need around 10.

Additionally, the data in the tables could be reduced. For example, say we have a Person table, which has a PersonTypeId. We only want rows where PersonTypeId = x.

Can this be achieved with replication? Can we get subsets of data from a subset of tables from the source, into our new replicated database? Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?

Can this be achieved with replication?

Yes, Transactional replication can be used in your case.


Can we get subsets of data from a subset of tables from the source, into our new replicated database?

Yes you can just replicate the tables (articles) that you want along with its subset of data.

e.g. where PersonTypeId = x --> You need to use static row filter as it uses a WHERE clause to select the appropriate data to be published.

selecting specific articles (tables) :

Filtering what data to publish :


Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?

No. See above answer to use static row filter when you are publishing the articles.

StackExchange Database Administrators Q#45031, answer score: 5