• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

SQL mirroring and SQL maintenance

We currently have 1 SQL server with a fairly large database. We're running necessary SQL maintenance tasks in the evening that obviously degrade performance when users access the database during the maintenance window.

We'd like to be able to run the maintenance tasks without affecting the user experience. At first glance we thought SQL mirroring and then offloading all user access to the mirror during the maintenance window. The problem is, while failover to the mirrored server can be scripted during the maintenance window, there is no clear method of redirecting those user connections to the mirrored server as long as the primary server is still available on the network. Obviously, the primary server has to be up and active for us to run maintenance, and as long as it's up the user connections will be directed to that primary server.

Any ideas?
Michael Ortega
Michael Ortega
2 Solutions
When you do the SQL mirroring, the MIRROR is a standby and its not accessible (no read/write). You can make it readable by creating snapshot on it.

Michael OrtegaSales & Systems EngineerAuthor Commented:
I realize that the mirror is a standby copy, but was under the impression that it can be used for failover purposes. You can most certainly fail over to the mirror and use it as the new principal, hence the failover option when connecting to the principal instance while mirroring is enabled. I've also reviewed information that suggests this "failover" to the mirror can be scripted, which means we can include that at the beginning of our scheduled SQL maintenance.

Based on what I'm trying to accomplish do you have any other suggestions?
You can failover to the secondary, effectivly putting your primary offline (standy in reality). So you cannot have both online and mirrorring. That would be  be 'scale out' feature, something we don't really have in SQL server.

Depending what part of your maintenance causes problems, several strategies are available.

For example, you could stop taking a backup of your primary system, and have a secundary mirrorr'd system for that (again depending specs & requirements). If rebuilding indexes is the problem (you are doing this on-line I suppose), you might want to limit the resources assigned to the rebuilding/reorg process by using resource governor. You could also limit the amount of indexes to process by picking only those with high fragmentation rate (scripts available by using google).

Alpesh PatelAssistant ConsultantCommented:
for your maintenance plan you can create SSIS package and schedule with SQL Job. Please limit the CPU uses at time on execution also. Please use SQL Job wizard for more help.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now