SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

  • Comments posted to this topic are about the item SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

    Farhan F. Sabzaali

  • Farhan, thanks for the contribution.

    No doubt it will nudge many to make the move to mirroring. And for those that have not started to use mirroring, get going. It has little downside, and huge upside.

    Based on my non-scientific observation, the passive mirroring process uses around 2-3% of system resources (maybe less). So, if you have multiple production database servers, you can expand their use and set them up as a passive mirror for other production systems. The corollary to this is that a server dedicated solely as a passive database mirror is a waste of resources.

    The more you are prepared, the less you need it.

  • Hello Everyone,

    Thanks for the great script! I think this will help everyone when using mirroring. It would be interesting to see how many DBA use mirroring instead of log shipping or other replication software like HP-Double Take or CA-XOsoft. What about active/active clustering? There is so many options out there each having pros and cons.

    Hopefully Microsoft (or a 3rd party) will make a GUI that will help DBAs in mirroring many databases on many servers. It seems to me that this alone could be a plug-in or stand alone software to help achieve this configuration. If you have a smaller environment it's not a problem but if you work in a large environment it become a large task.

    Again thanks for your time and efforts on this topic.



  • Good script. I've been meaning to get around to setuping a T-SQL script for mirroring to speed up the process, so this will give me a good start point. 🙂

    Mirroring rocks! and Andrew Peterson-472853 - if you've got two servers and your running SSAS / SSRS, potentially you could use 1 server for production system databases and the other for reporting & spread the load 🙂

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • I got follow result:


    --SQL Server Mirror Setup Script - Step 1: Add Login For EndPoint

    --Run On Principal Server

    --Step 1 Start

    USE [Master];


    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name='spcph\algadmin')


    CREATE LOGIN [asasa\admin] FROM WIND

    and database mirroring doesn't work

    on line 70: SET @Step1 = @Step1 + @NewLine + '

    does it really need this quote ' ?

  • I did create the SP it was created sucesfully

    And when i execute the SP with the follwoing commands

    -- Index Rebuild/Reorganize

    EXEC master.dbo.usp_ReorgRebuildIndexes

    @databasename ='MyDBName', -- Leave the contents with in quotes blank to run --against all DBs

    @FragCheck = 10.0,

    @DensityCheck = 75.0,

    @RebuildThreshold = 30.0,

    @online = 1,

    @runrebuild = 1,

    @DBMirrorPerf = 1,

    @ChangeDBRecovery = 1,

    @SendEmail = 1,

    @SendSummaryOnly = 0,

    @MaxDaysofLog = 14,

    @MaxErrors = 10

    It took about 20 secs n gave a result of "Mail queued."

    What does this mean? How can i monitor what percentage of indexes are rebuilt ?


    Are they being rebuilt/Reorg at all ?

    Is there a way i can monitor the process?

    I dont see anything active in my activity monitor .. except a SELECT INTO from DBCC inpbuffer.

    Please suggest ..

    When i checked with the log that is created in master DB it said

    DatabaseName JobRunStartDateTime JobRunEndDateTime JobIndexCount JobStatus JobLog

    -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    MyDBNAME 2011-01-27 17:44:01.337 2011-01-27 17:44:01.980 1 Ok


    -- START OF INDEX DEFRAG FOR DATABASE Solicitations AT 2011-01-27 17:44:01

    -- No of processes with connections active for the last 15 minutes in DB Solicitations

    (1 row(s) affected)

    Today is the tomorrow you worried about yesterday:-)

  • Thanks for the script.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply