The Multifunctioning DBA

Jun 22 2011   8:23PM GMT

Fail over Mirror database with Powershell

Colin Smith Colin Smith Profile: Colin Smith

Today I was asked if I could help out by writing a simple script that would fail over a mirrored database from one server to the other and back if and when needed. I thought sure I can. I mean to do the failover with T-SQL is really simple but I can automate it. So here is the T-SQL to fail over.

ALTER DATABASE Your_DBNAME SET PARTNER FAILOVER

Well that is very simple but to automate it will take a bit more work. No problem though. Break out the powershell and away we go.


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

## Step one: Load SMO Assembly

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’ ) | out-null

#Step Two: Define Variables

$failover = 0

$instances = “workdog”, “workdog\test”

##Step Three: find current database that is the principal

foreach ($instance in $instances)

{

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’ ) $instance

$databases = $s.Databases

#$databases | select name, mirroringstatus, status

foreach($db in $databases)

{

$name = $db.name

$mirrorstatus = $db.MirroringStatus

$mirrorstatus = $mirrorstatus.ToString()

$status = $db.Status

if(($mirrorstatus -ne “None”) -and ($Status -eq “Normal”) -and ($failover -eq 0))

{

echo “$name is on $instance”

$query = “ALTER DATABASE $name SET PARTNER FAILOVER”

$failover = 1

Invoke-Sqlcmd -serverinstance $instance -Database master -Query $query

So basically what I am doing is setting up SMO connections to my instances and looping thru all the databases. If I find a database that is mirrored and the primary then I know what instance to run the SQL Query against and I do. I also set $failover = 1 so that the script knows that if it iterates thru and finds that the second instance now has the primary do not run the failover again.

Hope this helps

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: