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?
LVL 16
Michael OrtegaSales & Systems EngineerAsked:
Who is Participating?
Kobe_LenjouConnect With a Mentor Commented:
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).

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?
Alpesh PatelConnect With a Mentor Assistant 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.