Ok, so in a week or so I will be setting up a database mirroring scenario in order to facilitate a server move (i.e. physical move to new location) with low downtime amounts. We've got our plan pretty much down, very abridged version is: * Backup primary * Restore to secondary * Set up mirroring from primary to secondary, and wait for SYNCHRONIZED * Move server * Catch up the secondary * Move production traffic to the secondary So - any gotchas that you've experienced in the past? Just want to make sure we don't need our GTH plan :)
You may want to add a step after backup primary to backup primary tlog, then a step after restore to secondary for restoring tlog to secondary. For some reason there still seems to be a glitch where you have to also restore a log. I have only tested in 2005 and 2008. 2008R2 may have fixed this. The only other gotcha would be anything hard coded to the primary host name. If you use DNS aliases or virtual ip addresses you should be fine. Does the primary server have any ETL processes or shared folders? What is your plan for migrating your users? Several processes exist for a one time move but I am not aware of anything that will keep the SID's in sync other than a drop and create of the user object. I almost forgot, you asked about experiences. I used mirroring a lot when migrating to a new data center and just last week for a DR test. The only gotcha's I have encountered are user access. Keeping the bloody accounts in sync. Fortunately the majority of the accounts I deal with are server type SQL accounts where the password doesn't change and active directory groups. However there are those systems where we have local SQL accounts where the user changes their password. Those have been a pain to work with.
We mirror between Head Office and our DR centre. The biggest issue we have is the time taken to get the backup(s) over to the DR centre. Our network isnt too fast and it is hours in the process, then the log backup needs to be done and copied and etc etc. We usually schedule this for a weekend so that the transfer doesnt affect normal business traffic on the network and there are fewer updates during the copy and the log backup is then much smaller. My advice - practice it at least once. Even if its on AdventureWorks, just so as you know what the steps are and what does, doesnt fit with the BOL version. Its a rock solid tech though, we get very few issues and are pleased we changed to it from replication. From SQLBits Klaus Aschenbrenner said that its actually service broker under the covers, I guess he should know! We dont use auto failover as we dont want false positives and the secondary at the DR centre becoming live. the backup solution would then have to be swapped over too and all sorts of extra work would be required. We dont have a witness either, mostly as we are not wanting auto-failover.
Ok, I have some points to share now: When you set up mirroring, and get this error: > The server network address TCP://..com: can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) You might, maybe, be tempted to believe that the network address was wrong, or that one side or other couldn't connect to the port. However, it can also mean * That when you restored the backup from the primary to do the initial seed of the mirroring, you did not restore it `WITH NORECOVERY` and therefore mirroring couldn't keep restoring the logs from the primary * That the security settings for the account under which SQL Server is running are causing it to have authentication problems when talking to the other server So yeah, they had me bummed out for a while... :)