November 14, 2008 at 6:11 am
Hi all,
Just a question I have never tested to never answered.
We have 4 SQL 2000 servers replicating to each other. THe 4 servers need rebooting for patching but ae 24 hour servers. To take them down I am disabling the distribution agents and the applications so no data is due to replication or being replicated and we will reboot.
BUT what would happen if you just went in and rebooted without this careful planning? How robust is it?
November 14, 2008 at 7:43 am
Hi,
If you are talking about a graceful shutdown i.e. stopping the service and then a Start/Restart you can rest assure that SQL Server will make sure that your data is consistent.
To simplify, there are two processes that will do this for you;
1. WAL (Write Ahead Logging) which ensures that all modifications are written to the transaction log before it gets written to the database
2. The Recovery process that on startup processes the transaction log and will:
- UNDO any transactions that were not committed at the time the server was brought down (i.e. at the last CHECKPOINT)
- REDO transactions that were committed at the time of last checkpoint, but not yet written to the database
You can read more about the various ways of shutting down SQL Server in Books Online in the SHUTDOWN section.
HTH
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 14, 2008 at 9:04 am
No need to disable any distribution agents.
SQL with or without replication can handle expected or unexpected shutdowns without data loss.
With replication, the data to be replicated is copied from the publisher's tran log and written into the distribution database. Once done, those log records are marked as replicated. That's done by the log reader.
The distribution agents then copy that data from the distribution databases to the subscribers and they do keep track of what has and has not been replicated
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply