Transaction Replication & Default Values

  • Hi,

    We have transactional replication set up between 2 SQL 2000 Servers.  I did not set this replication up and when we needed to recover some data last week noticed that the table design does not include the defaults set up on the original table.

    I would like to know if when the table is created during replication, if the default properties will be set in the table design?

    Thank You

    F

  • It depends how your snapshot is configured...

    What properties missing?

    MohammedU
    Microsoft SQL Server MVP

  • The properties missing are only the defaults that have been set for the tables. 

    Am I right in saying that I need to check the Include Extended Properties option?

    Thanks 4 ur response so far!

  • Why would you need default values for the subscriber? When a record is replicated, the entire row will be replicated. Defaults will not come into play.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I think you don't have to worry about it as when transaction will be replicated to subscribers it will take whatever happen to the transaction as a whole default will not come in to picture atleast for the subscribers who are subscribing to the transaction replication ..as other answer suggest the same

  • Hi,

    Thanks for the responses.  I realise that the data will be replicated across regardless with the data entered into the publisher, however, when we experienced a problem last week with the publisher, we wanted to switch data sources to the subscriber so that the users could continue with the processing, and because the table properties for the defaults were not set, we had application problems...

    I hope this makes sense!

  • Then you shouldn't be using transactional replication. You should be using merge replication.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Thanks, its just that no data is entered into the subscriber, so I don;t see how merge replication will be useful.

  • As long as it is transactionally replicated, you will not be able to enter data at the subscriber. If, however, you want to have the ability to switch your application to write to the subscriber, you need to use merge replication.

    Plus, if you want to be able to switch between using the subscriber and the publisher, then it will take a considerable amount of manual intervention to switch back and forth. Using merge replication, you would only have to change the db connection of the application.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply