By: Updated: 2014-04-14 Related: ProblemWe have numerous SQL 2012 Availability Groups in our environment with more projected to come online. The issue we are faced with is that we want to find which SQL Instance (cluster node) is hosting the Primary Replica.

• Facet Powershell Check File

You can use SSMS to find the information either with the DashBoard or by expanding each Availability Group. This will show you the information but when you get over 2-3 Availability Groups then it can be problematic to have all the tabs in SSMS for each Dashboard or to have each instance open in SSMS. A better method is to turn to PowerShell to help us find a solution to the issue. We can use the system DMVs for Availability Groups to create a T-SQL query and then use PowerShell to iterate through a list of SQL Servers and query them in turn storing any results in a dataset object. We will use the.NET tool chest to connect to and run T-SQL against our servers returning results only for Servers in Availability Groups and filtering for Primary Replicas. SolutionMy solution uses PowerShell to execute a T-SQL script against a list of SQL instances and return the cluster node holding the Primary Replica for the Availability Group.

It will run against SQL 2005 to SQL 2012 (not tested on SQL 2014, but no reason it would fail) however since we are focused on SQL 2012 and higher we should keep the list of instances to the versions supporting Availability Groups only. The list is embedded in the PowerShell but we could use a file or a SQL table to feed the SQL instances into the script to be iterated through. I will leave that to those who need it, the modification needed being changing the input method to place the instance list into a variable as the collection of objects to iterate through.The parts we require are the T-SQL to find whether a cluster node's instance is holding a Primary Replica and the PowerShell to create a collection of SQL instance objects, connect to the instance, run the T-SQL code and return the results into a dataset.

Lastly we print the dataset to the screen showing the Primary Replica for each Availability Group. We could upload the results to a SQL table or write to a file on disk, we could even email it, whatever the best method for dispensing the information to those who need it.

T-SQL to Find if the SQL Instance is a Primary ReplicaWe will use several system DMVs to create a T-SQL query that will determine if the local SQL instance on the cluster node is the Primary Replica for an Availability Group. That is all that we need to run on an instance to see if there are any Primary Replicas for a Availability Group on that instance. Since any Availability Group should be composed of two or more SQL instances (degenerate case of one node cluster ignored) we have to check all the SQL instances in the WSFC that make up the cluster hosting the Availability Group. PowerShell to the Rescue!

PowerShell script to iterate against a collection of SQL instances and run T-SQL codeThe PowerShell script runs on version 2 and higher and does not use any add-ins or Providers just the.NET functionality in PowerShell itself. The part that is new to most people will be the.NET OLDEB functionality that uses the OleDbDataAdapter to run a T-SQL command and take the results and populate a dataset. ## Setup dataset to hold results$dataset = New-Object System.Data.DataSet## populate variable with collection of SQL instances$serverlist='DBS04A','DBS04B','DBS05A','DBS05B','DBS06A','DBS06B'The next section is where we loop through the collection of SQL instances in the variable, first setting up the connection string then using a variable to hold the T-SQL command to be executed. Finally we connect to the SQL instance, execute the T-SQL and take any records returned and place them in the dataset object. After the loop is finished going through the collection of objects we dump the rows to the command console screen. Next StepsWe can get more data from the T-SQL, we just need to decide what we want and how to get it.

If there is another DMV that has other information than the ones already used we can use them. We could also use a text list or SQL table to hold instances to be used in the collection to be looped through. We can design any method to feed the objects in the collection as long as it pulls in the SQL 2012 instances in the Availability Group. Finally, we can remove the WHERE clause and return all Replicas in the T-SQL code.If you notice in the PowerShell code the variable holding the T-SQL code is inside the loop but is does not have to be inside the loop. We can place it outside the loop but leaving it inside would allow us to use variables to modify the T-SQL string. Learn more about how to Monitor SQL Server AlwaysOn Availability Groups here:.

To read more about Configuring Alerts for SQL Server AlwaysOn Availability Groups:Last Updated: 2014-04-14. Thursday, November 14, 2019 - 1:53:12 PM - William NyeI was trying to recall how to find whether a DB is primary and ran across this post. I thought I had recently seen an even simpler way, so I looked more and found this: - Check if this is the primary node and if so, proceedIf sys.fnhadrisprimaryreplica ('MyDB' ) 1BEGINRAISERROR ('MyDB is not primary on this server now right now, so quit the job with success since it will run on other server.' , 18,1)ENDGranted, this may not have been available until 2016.Tuesday, December 11, 2018 - 10:56:21 AM - GabiGreat script! Thank youTuesday, November 15, 2016 - 1:55:29 AM - NishadThanks for this useful article!Tuesday, April 22, 2014 - 12:44:02 AM - Venkataraman RamasubramanianThanks.

Very useful article.

Facet Powershell Check File

Yes, I tested them. I was not clear enough in my explanation, sorry for that but here is complete WKS name:SC0 303WKS0051from here I need to get number 303 as $URL without anything else because the app team set up application like that (they could have easily set it to use 0, but they did not).If the WKS has name:SC 3303WKS0051then I would need 3303 as $URL without anything else.That is why I was using$URL = $wks.substring (3,3), it would cut at third number and give me next three numbers which works forSC0 303WKS0051 but not forSC 3303WKS0051 where I need third number to be included.