December 14, 2011 at 5:10 pm
In the coming weekend we will be taking down our primary production database servers for several hours for a major hardware upgrade. All our prod servers are on SQL 2008, and the databases are mirrored (async) to a DR site. Database snapshots are generated from the DR mirrors for business reporting and ad-hoc queries by our users.
I am trying to anticipate and avoid a nightmare scenario by which mirroring becomes disconnected for all our databases and we in the DBA team are spending all of next day resetting it from scratch on all databases and servers. :sick:
So my question is this:
Should I pause mirroring for all databases before the outage to avoid it from becoming disconnected?
Will I be able to simply resume it after the outage?
As I mentioned, I want to avoid having to reset mirroring from scratch for all databases after the outage.
This would be very painful and time consuming for our DBA team and would greatly affect our users who depend on the mirror snapshots for their reporting.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 15, 2011 at 8:53 am
Yes, you can pause the mirroring, but pausing the mirroring will cause the transaction logs on your primary server to fill, as they are unable to be truncated (even with a database backup) because they are not sending information to the mirror. You stated that the hardware upgrade will take several hours so you'll need enough disk space to accommodate the logs filling.
IIRC, you can simply bring the mirror offline and do the upgrade, then bring it back and the mirroring will resume automatically. I'll try and test that later.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
December 15, 2011 at 8:59 am
Do you have automatic failover? Are you expecting people to work off the mirror while the primary one is down? If so, account for the backup and restore to fail back.
If not, when you take down the primary, then the mirrors just wait for it to come back up. They don't do anything. I'm not sure what your hourly snapshots will do. My guess it they will just recreate the same data over and over until mirroring is back up.
If the primary is down, the logs don't change. No work is being done on the primary.
December 15, 2011 at 12:17 pm
Thank you both for your answers.
Actually, I wasn't accurate in my original description of the scenario.
There will actually be 2 outages.
(1) At 4 pm we are taking down our DR environment (where the mirrors reside) for the hardware upgrade.
This will take 3 hrs.
During this time the mirroring will be suspended on the prod side, as the mirrors will no longer be available.
(2) At 7 pm we are taking down the prod environment for the upgrade for another 3 hrs. til 10 pm.
I suspect, based on what was mentioned above, that from 4 til 7 pm the logs in the prod dbs will grow, as the mirroring will be suspended and the logs will not be truncated.
Am I correct in this interpretation?
Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 15, 2011 at 12:37 pm
yes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply