can replication be limited to just some tables in the db?

db042190 1,516 Reputation points
2023-01-26T13:10:53.7766667+00:00

hi , can replication be limited to just some tables on the db or must it be on the entire source db? and if the source has timestamps, will there be any problem for sql to keep them as is on the target/replicated db?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,323 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 99,546 Reputation points MVP
    2023-01-27T18:54:56.1+00:00

    and if the source has timestamps, will there be any problem for sql to keep them as is on the target/replicated db?

    I don't know this on the top of my head, and I would have to set up a test to find out. But setting replication for a quick test is a little messy, and some litter remain. So I have a different idea: You set up a test.

    It's actually quite simple. If the original columns has the timestamp/rowversion data type, the columns in the replicated database must be binary(8). If the column in the subscriber is also timestamp/rowversion, the subscriber will have new values.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Leandro Ezequiel Molinari 26 Reputation points
    2023-01-26T15:00:51.2933333+00:00

    Hi, good day how are you?

    you can limit replication just to some tables from publication properties. I let below a link with Microsoft documentation that talks about it.

    [https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/view-and-modify-article-properties?view=sql-server-ver16#to-view-and-modify-article-properties

    About the timestamps, the same should be replicated as well as the data source DB if there is no filter or something like that.

    Hope that this can help.

    Greetings.

    0 comments No comments

  2. Seeya Xi-MSFT 16,416 Reputation points
    2023-01-27T03:12:41.98+00:00

    Hi db042190,

    Question 1:

    First, let me introduce you to a concept.

    Articles are the basic units to be replicated, such as tables, procedures, functions and views(not the entire source db). Articles can be scaled vertically or horizontally by using filters. Multiple articles can be created for the same object.

    Question 2:

    No. For detailed reason, you can learn about the logic of replication.

    For example: Snapshot replication does not track any data changes after the snapshot is generated, so to synchronize data changes you need to apply a new snapshot to the subscription server each time, completely overwriting the existing data. Transactional replication tracks changes through SQL Server's transaction log, and replication sends changes to the subscription server on a transaction-by-transaction basis. Merge replication, on the other hand, tracks data and architectural changes through triggers and system tables.

    You can start from this: https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver16

    Best regards,

    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

  3. db042190 1,516 Reputation points
    2023-01-31T13:47:52.47+00:00

    hi seeya. for some reason this post took some time to show up on this forum. i'm just getting back to it now. unfortunately i got conflicting answers on the timestamp question possibly because you misunderstood the question so i'll need to try it myself like erland suggested in order to know which answer is most accurate. the question about timestamps is not if they'll increment automatically but if they'll copy over to the replicated db "as is" from the source.

    0 comments No comments