Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring


SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring

Author
Message
Farhan Sabzaali
Farhan Sabzaali
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 152
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
Andrew Peterson
Andrew Peterson
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 Visits: 713
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
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 1304
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
dave-dj
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 1149
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
freetah 59299
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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 ' ?
Minnesota - Viking
Minnesota - Viking
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 564
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)




Today is the tomorrow you worried about yesterday:-)

Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9946 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search