4

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?

5

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) :

enter image description here

enter image description here

Filtering what data to publish :

enter image description here

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.

  • Thanks @Kin, that did it, 100%. Replication and security was my biggest issue. Snapshot agent couldn't write to a folder... So, I had to grant full access to 'Everyone' (Running as a test on a Win7 machine). And now its working. THanks! Foreign keys aren't replicated, which is understandable if I am filtering... Thanks! – Craig Jun 23 '13 at 4:18
4

SQL Server is very good about configuring 'just enough'. First question is do you need to replicate, or merely query, the other database? Replication means that you will periodically update ServerA as tables change on ServerSource. Query means that you 'fully qualify' your table name to something like this:

update a set a.xyz = b.xyz from tablea a inner join ServerInstanceName.DatabaseName.dbo.tableb b on a.Id = b.Id

This presumes that ServerSOURCE is highly available.

To set up replication, you will need to create a 'publisher' on ServerSource. If you don't have that turned on, it creates a new database to manage replication. You need to create a common domain account, or set up workgroup accounts with aliasing.

ServerREP, in that case, is set up to be a 'subscriber'. After you have set up the publisher and the common account, SSMS on ServerREP can configure the subscription to include the tables and columns you need, and the replication methodology. You can set up 'push' so that ServerSOURCE decides when to update tables on ServerREP, or 'pull' where ServerREP decides when to query ServerSource.

  • Thanks for that. The replication I'm looking at is Transaction Replication, which, I believe, is that when a row is inserted or updated on ServerSOURCE, on a table which is marked as replicated - then it's pushed from the publisher, to the subscriber. That seems right so far? But, can I apply a filter to what gets replicated from that table? i.e. Only rows where 'PersonTypeID = x'? Or will it replicate all rows, no matter what? And then secondly, if that's not possible, can I create a VIEW on ServerSOURCE, and replicate the result of that view, to a table in ServerREP? – Craig Jun 21 '13 at 3:36
  • 1
    You can apply a filter. Anything that would normally apply to a Select - filters, aggregates, columnlists, and subselects all work. – Meredith Poor Jun 21 '13 at 3:42
  • Ah, OK. The one time I set up replication, I just remember selecting the tables I wanted. Somewhere in the process of selecting a table, I can alter the SELECT slightly, to include a WHERE? This sounds like what I need. – Craig Jun 21 '13 at 4:02
  • At this point I'm short of answers, because it's been awhile since I've done this. – Meredith Poor Jun 21 '13 at 4:04
1

Yes, this can be achieved with Transactional Replication by using Static Row Filters. Static row filters use a WHERE clause to select the appropriate data to be published. In your case, your filter clause would be PersonTypeId = x.

Here are some links to get you started:

Filter Published Data

Define and Modify a Static Row Filter

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.