SQL Server Performance

Replication issue

Discussion in 'Performance Tuning for SQL Server Replication' started by itbhushan, Jul 18, 2003.

  1. itbhushan New Member

    Hi,

    (1) Can anyone tell me if 2 different sites involved for merge or transactional replication then is it possible to add new fields or edit an existing field of a table already in use for replication(even after temporary stopping the process of replication)?, is there any extended stored procedure or a system stored procedure used for the same.
    (2) What are the additional feature included in SQL Server 2000 as compared to SQL Server 7.0


    Thanks

    Bhushan
  2. satya Moderator

    1) For any schema changes its always suggested to redefine the replication settings to overcome some issues after the changes made between sites.
    2)Replication is an area where improvements have been made in performance, reliability and feature set. If you are using replication, seriously consider adopting SQL Server 2000. Replication now supports Queued Updating Subscribers.

    Similar to transactional replication, the subscriber gets updated via a transactional message queue. This allows a subscriber to spend a large amount of time disconnected from the network while ensuring that replication occurs in a transactionally consistent manner. And also ease the use of replication is the support for schema replication, namely ALTER and DROP columns statements. This support is for transactional and merge replication and will not require a publication to be redefined.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. itbhushan New Member

    Thank you for your information Satya, but what i wanted to know specifically is it possible to make changes to the table schema without breaking the replication process, this is important for me to know because i have to plan for a setup which invloves the process of replication. I have read somewhere that it is possible by using some stored procedure which i do not remeber & iam not sure whether it is possible or not. Kindly help if anyone has the answer.

    Thanks

    Bhushan
  4. satya Moderator

  5. itbhushan New Member

    Ya thats possibly what i was looking for it clearly says that it is possible to modify an existing schema by adding a new column or by dropping an existing column even if the tables are used for publication ie :- without breaking the process of replication. But it says that it is possible for SQL Server 2000, i have also checked for the stored procedure "sp_repladdcolumn" in SQL Server 7.0 used for actually adding a column but is not to be found leading me to a conclusion that editing a schema used for publication is not possible on SQL Server 7.0.

    Can u also please share your comments or view points on the same.

    Thanks

    Bhushan
  6. satya Moderator

    With respect to above referred article and my practical experience with 7, I confirm its not possible in SQL version 7. So if any schema changes you should re-assign the replication to take the effect of the change.

    As you can see this is foremost enhancement in replication between 7 and 2K.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page