SQL mirroring and SQL maintenance

Posted on 2011-05-04
Last Modified: 2012-06-27
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 18

    Expert Comment

    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.

    LVL 16

    Author Comment

    by:Michael Ortega (Internetwerx, Inc.)
    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?
    LVL 8

    Accepted Solution

    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).

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now