Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
transactional replication qestion

Answered transactional replication qestion

  • Thursday, December 27, 2012 6:35 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am planning to setup transactional replication on one of our databases. I did some research and found the following limitations/conditions with setting up transactional replication on a database:

    1. Each table needs to have a primary key
    2. Schema changes won’t be reflected in the “destination” database
    3. Certain types of operations won’t be allowed on the “source” database(not sure what those operations are).

    Is the above list still accurate? Any advice would be highly appreciated.

    • Moved by Tom Phillips Thursday, December 27, 2012 6:40 PM Replication questions (From:SQL Server Database Engine)
    •  
     

All Replies

  • Thursday, December 27, 2012 6:41 PM
     
     
    Sign In to Vote
    1

    Refer below article for more information about the considerations for Transactional replication

    http://msdn.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

     
  • Thursday, December 27, 2012 7:28 PM
     
     
    Sign In to Vote
    0
    It only answers the first question, do you know anything about #2, #3 ?
     
  • Thursday, December 27, 2012 7:36 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    1)  You are correct, each table will need to have a primary key.

    2)  Schema changes are propagated by default, publication property @replicate_ddl must be set to 1.  Schema changes must be made using T-SQL, not SSMS gui, some restrictions apply.  This is covered in Make Schema Changes on Publication Databases.

    3)  As Yaniv points out in this post, you cannot drop a table if it is an active article, cannot drop a pk, cannot rename a table, and cannot truncate a table.

    Brandon Williams (blog | linkedin)

    • Proposed As Answer by V. Keerthi Deep Thursday, December 27, 2012 7:45 PM
    • Marked As Answer by SnowShine429 Thursday, December 27, 2012 8:13 PM
    •