SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Farhan Sabzaali » SQL 2005 Disaster Recovery Scripts: To Setup...

SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

Rate Topic

Display Mode

Topic Options

Author

Message

Farhan Sabzaali

Posted Wednesday, March 18, 2009 11:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:21 AM
Points: 76, Visits: 135

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

Post #679154

Andrew Peterson-472853

Posted Wednesday, April 08, 2009 7:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136, Visits: 259

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.

Post #693067

Rudy Panigas

Posted Wednesday, April 08, 2009 8:25 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 306, Visits: 1,013

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

Post #693164

dave-dj

Posted Monday, June 07, 2010 3:06 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 380, Visits: 1,014

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

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 Smile

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)

Post #933379

freetah 59299

Posted Tuesday, July 27, 2010 7:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 8:23 AM
Points: 1, Visits: 6

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

Post #959396

Minnesota - Viking

Posted Thursday, January 27, 2011 11:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:54 AM
Points: 201, Visits: 456

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)

================================== Smile

Today is the tomorrow you worried about yesterday

Post #1054776

« Prev Topic | Next Topic »

Permissions