SQL Server Performance Forum – Threads Archive
2 Questions about SQL 2000 ReplicationI 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.
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
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
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.