SQL Server Performance

2 Questions about SQL 2000 Replication

Discussion in 'Performance Tuning for SQL Server Replication' started by Edward Buchholz, Mar 5, 2004.

  1. Edward Buchholz New Member

    I am currently in the middle of Implementing transactional replication between two SQL 2000 servers as a measure of disater recovery, but I need a way to replicate new tables / views / storedprocedures that may be created in the publisher database. Is there any easy way to do this, or do I need to create a log shipping procedure, and script changes to the replication whenever a new object is added? I'm fairly new at this, and kind of got pushed into the project, so please forgive my newbism.
  2. Twan New Member


    Hi ya,

    if you want to replicate object creation, then you'd have to move to log shipping. Replication is only for data replication, not object replication

    Log shipping will essentially periodically restore the database/log to another server, so no additional replication is needed (or indeed possible) between those two databases.

    Cheers
    Twan
  3. satya Moderator

    Log shipping will duplicate the entire database. With Replication you can select a subset of your database. With Transactional replication you can only replicate tables with PK's. With Merge replication you can replicate every table.

    With Log shipping, once it is setup, it normally requires little maintenace.Replication can be complicated to set up, and typically requires more maintenance, although not always.

    Log shipping "replicates" everything - ie all system objects - users, tables, views, sp's, udf, user defined datatypes, etc. Replication can replicate tables, views, sp's only.

    As suggested Log shipping will be suited to the current situation and any new object creation will be taken care by restoring log on standby server.

    Moreover, LS is only available on Enterprise Edition of SQL or if not you can deploy your own log shipping as specified in this linkhttp://www.sql-server-performance.com/sql_server_log_shipping.asp on this website.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page