SQL Server – PowerShell Test-SqlAvailabilityReplica Script Never Completes

availability-groupspowershellsql serversql-server-2012

I'm still fairly new to PowerShell so I'm unsure of all the troubleshooting steps I can take to solve this problem. I would like to use the below command to determine if the replica is primary or not.

I believe I have successfully loaded both the provider and smo. Intellisense appears to recognize the command.

If I run the following command

Test-SqlAvailabilityReplica

The script will run but will never finish. I'm running it on one of the replicas so I'm assuming it should just be grabbing the local host. If I add the -WhatIf flag it still runs without finishing.

I've also tried Test-SqlAvailabilityGroup and it never finishes either.

What can I do to get this command to work? How do I find out what is causing it to lock up?

EDIT:
It looks like my issue is with the path…. This path location works 

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\Fo‌​oAg\AvailabilityRepl‌​icas\

but then I try using this path location and it fails. 

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\Fo‌​oAg\AvailabilityRepl‌​icas\MainReplica

Edit2:

Are we sure it's 

<ServerName>

at the end? I can set my location to 

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabiltyGroups\FooAg\AvailabilityReplicas\

just fine. When I do a DIR to see what is available I see my two replicas listed as 

"FooNode1\FooInstance"

and 

"FooNode2\FooInstance"

So for the "MainReplica" part of the path do I replace that with FooNode1 or FooNode1\FooInstance?

Best Answer

The commands around Availability Groups for SQLPS require you to pass in the provider path for the replica, or server itself (if you are building the AG).

As the example in the help documentation shows:

Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\<ServerName>\<InstanceName>\AvailabilityGroups\<AG Name>\AvailabilityReplicas\*<SpecialCase>*"

If you are using a named instance change "DEFAULT" to the name of the instance.

So after some testing I found out the replica name is a special case if you are dealing with named instances. If you are dealing with a named instance you cannot just use: "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer\MyInstance"

You will get path not found errors...here is a kick on how to fix it.

I happened to issue this command against a named instance with an AG:

dir "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\" | select *

The PSChildName and other references to the replica names show that the backslash is being presented as the ASCII hex equivalent value of "5C". So to work with named instances you have to use this path format:

"SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer%5CMyInstance"
Related Question