Unanswered: transactional replication v log shipping
Hi, Ive a few questions and would be grateful for any assistance/comments from experience as Ive not that much dba experience , we have 3 servers all containing sql 2000 standard edition, two (prod and standby) located in the U.S. and Im hoping to set up the third as a reporting server locally in Europe...presumably having the data close as possible and as current as possible for reporting?...We dont have the resources to implement clustering so presumably I envisage keeping the standy server using either Log shipping or replication (i think log shipping being the best approach here?). My question is whats the best approach for getting the data across the network from the U.S to Europe (differing domains but internal network).. Is transactional replication albeit push or pull the best approach to get the data across, ie. with log shipping it seems that you will have to copy across the bckups nightly using xcopy /ftp transaction log backups scheduled, does transactional replication just propogate the changes and not the entire backup daily. if its just the changes does it note just the changes made to the standby server every day and just replicate these across? (i.e. a backup restore is done daily with log shipping to the standby server..With transactional replication will it note that some of this data has been replicated already..) Any help greatly appreciated..
Log shipping is definately the best approach for a standby server. Replication will not bring over schema changes automatically (last I checked), so any upgrades you make could be lost in a replication scheme.
As for reporting servers, you are probably more interested in the data rather than the code, so you can go either way with that. If you get into replicating views, you may be faced with working out dependencies, as the synchronization process will attempt to create them alphabetically. You should be able to create the views on the reporting server manually, however.
Transactional replication will send just the changes to individual tables, so if you have tables that constantly change, which you are not interested in reporting off of, you could go with replication. Log shipping will only send over transaction backups, and not the full backup nightly.
Thanks for the reply, if its just the transaction logs backups that are copied and restored subsequently to the initial backup and restore of the database for log shipping , will this conflict with other backups that may occur i.e. backups outside of log shipping?
Logshipping will not conflict with full backups, but it will very much conflict with any transaction log backups that you may take. If you rely on the sql maintenance plan, you will have to make sure that no one takes a backup of the transaction log, because that will put a hole in the restore sequence for your standby system. Since log backups are done every 15 minutes by default in log shipping (and you can change that), then you should not see a need for backing up the log separately. Just be careful of backup packages like veritas, and omniback that may be preset to do log backups.
Another thing to keep in mind here is with log shipping, the database you are shipping to must be kept in read-only mode. No updates inserts or deletes can happen on the server being log shipped to. Furthermore, Every time a log is applied to the target server, all other connections to that database will be severed. Nothing will be able to access it while the logs are applying.
What type of application are you trying to set up?
The purpose of the standby server with which Im using log shipping is to be used for some read only queries that are scheduled to run at off peak hours etc, at times where the restores are complete, furthermore the standby server is going to be used as a staging area for changes
to be copied/replicated to a reporting server locally (the standby database and prod database are located in the U.S. whilst we need a reporting server locally)..thanks for the responses ...