SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

Question

SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

Farhan Sabzaali

Old Hand

Points: 399
More actions
March 18, 2009 at 11:07 pm

#202441

Comments posted to this topic are about the item SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring
Farhan F. Sabzaali
PMP, MCP, MCDBA, MCSA, MCSE

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

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

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 1

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 2

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.

Rudy

dave-dj SSChampion Points: 11297 More actions · Answer 3

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)

freetah 59299 Grasshopper Points: 19 More actions · Answer 4

I got follow result:

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

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

--Run On Principal Server

--Step 1 Start

USE [Master];

GO

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

BEGIN

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 ' ?

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 5

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 ?

OR

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)

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2016 at 6:49 am

#1880181

Thanks for the script.