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?
Best Answer
Yes, Transactional replication can be used in your case.
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 :
No. See above answer to use static row filter when you are publishing the articles.