1

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?

3

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"
  • I'm using a named instance and I'm completely confused on the path. If I put in this path, it cannot find the path. Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\MainAg\AvailabilityReplicas\MainReplica" -WhatIf – Geoff Dawdy Dec 14 '16 at 16:55
  • It looks like my issue is with the path.... This path location works SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\ but then I try using this path location and it fails. SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\MainReplica – Geoff Dawdy Dec 14 '16 at 17:01
  • I think most of the confusion surrounds which parts of the path from the example should be replaced with my specific environment object names. For instance, should MainReplica be replaced? I've tried leaving it and replacing it with FooHostname\FooInstance and it still can't find the path. – Geoff Dawdy Dec 14 '16 at 18:15
  • @GeoffDawdy ok, I see what you are talking about now. Found named instance and get the same issue. Let me see what I can figure out. – Shawn Melton Dec 14 '16 at 18:29
  • @GeoffDawdy You could also run DIR | test-sqlavailabilityreplica – Sean Gallardy Dec 14 '16 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.