Recherche de réplicas principaux pour les groupes de disponibilité AlwaysOn SQL Server 2012 avec PowerShell – Bien choisir son serveur d impression
Sommaire
Par: Brian P ODwyer | Mis à jour: 2014-04-14 | Commentaires (4) | Connexes: Plus> Groupes de disponibilité
Problème
Nous avons de nombreux groupes de disponibilité SQL 2012 dans notre environnement et d'autres devraient être mis en ligne. Le problème auquel nous sommes confrontés est que nous voulons trouver quelle instance SQL (nœud de cluster) héberge le réplica principal. Vous pouvez utiliser SSMS pour rechercher les informations soit avec le DashBoard, soit en développant chaque groupe de disponibilité. Cela vous montrera les informations, mais lorsque vous obtenez plus de 2-3 groupes de disponibilité, il peut être problématique d'avoir tous les onglets dans SSMS pour chaque tableau de bord ou d'ouvrir chaque instance dans SSMS. Une meilleure méthode consiste à se tourner vers PowerShell pour nous aider à trouver une solution au problème. Nous pouvons utiliser les DMV système pour les groupes de disponibilité pour créer une requête T-SQL, puis utiliser PowerShell pour parcourir une liste de serveurs SQL et les interroger à leur tour en stockant les résultats dans un objet d'ensemble de données. Nous utiliserons le coffre à outils .NET pour nous connecter et exécuter T-SQL sur nos serveurs, renvoyant des résultats uniquement pour les serveurs des groupes de disponibilité et filtrant les réplicas principaux.
Solution
Ma solution utilise PowerShell pour exécuter un script T-SQL sur une liste d'instances SQL et renvoyer le nœud de cluster contenant le réplica principal pour le groupe de disponibilité. Il fonctionnera contre SQL 2005 à SQL 2012 (non testé sur SQL 2014, mais aucune raison qu'il échouerait) cependant, puisque nous nous concentrons sur SQL 2012 et plus, nous devons conserver la liste des instances pour les versions prenant en charge les groupes de disponibilité uniquement. La liste est intégrée dans PowerShell, mais nous pourrions utiliser un fichier ou une table SQL pour alimenter les instances SQL dans le script à parcourir. Je laisserai cela à ceux qui en ont besoin, la modification nécessaire étant de changer la méthode d'entrée pour placer la liste d'instances dans une variable en tant que collection d'objets à parcourir.
Les pièces dont nous avons besoin sont le T-SQL pour déterminer si l'instance d'un nœud de cluster contient un réplica principal et le PowerShell pour créer une collection d'objets d'instance SQL, se connecter à l'instance, exécuter le code T-SQL et renvoyer les résultats dans un base de données. Enfin, nous imprimons l'ensemble de données à l'écran montrant le réplica principal pour chaque groupe de disponibilité. Nous pourrions télécharger les résultats dans une table SQL ou écrire dans un fichier sur le disque, nous pourrions même l'envoyer par e-mail, quelle que soit la meilleure méthode pour diffuser les informations à ceux qui en ont besoin.
T-SQL pour rechercher si l'instance SQL est un réplica principal
Nous utiliserons plusieurs DMV système pour créer une requête T-SQL qui déterminera si l'instance SQL locale sur le nœud de cluster est le réplica principal d'un groupe de disponibilité.
SI SERVERPROPERTY ('IsHadrEnabled') = 1
COMMENCER
SÉLECTIONNER
AGC.name - Groupe de disponibilité
, RCS.replica_server_name - Nom du nœud du cluster SQL
, ARS.role_desc - Rôle de réplique
, AGL.dns_name - Nom de l'écouteur
DE
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
SUR
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
SUR
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
SUR
AGL.group_id = ARS.group_id
OÙ
ARS.role_desc = 'PRIMAIRE'
FIN
Les résultats de la requête ci-dessus sur une instance SQL qui est le réplica principal sont présentés ci-dessous.
C'est tout ce que nous devons exécuter sur une instance pour voir s'il existe des réplicas principaux pour un groupe de disponibilité sur cette instance. Étant donné que tout groupe de disponibilité doit être composé de deux instances SQL ou plus (cas dégénéré d'un cluster de nœuds ignoré), nous devons vérifier toutes les instances SQL du WSFC qui composent le cluster hébergeant le groupe de disponibilité. PowerShell à la rescousse!
Script PowerShell pour itérer sur une collection d'instances SQL et exécuter du code T-SQL
Le script PowerShell s'exécute sur la version 2 et supérieure et n'utilise aucun complément ou fournisseur uniquement la fonctionnalité .NET dans PowerShell lui-même. La partie qui est nouvelle pour la plupart des utilisateurs sera la fonctionnalité .NET OLDEB qui utilise OleDbDataAdapter pour exécuter une commande T-SQL et prendre les résultats et remplir un ensemble de données.
## Configurer l'ensemble de données pour conserver les résultats
$ dataset = New-Object System.Data.DataSet
## remplir la variable avec une collection d'instances SQL
$ serverlist = 'DBS04A', 'DBS04B', 'DBS05A', 'DBS05B', 'DBS06A', 'DBS06B'
## Configurer la connexion au serveur SQL dans la boucle et exécuter T-SQL sur l'instance
foreach ($ Server dans $ serverlist)
$ connectionString = "Provider = sqloledb; Data Source = $ Server; Initial Catalog = Master; Integrated Security = SSPI;"
## place le T-SQL dans la variable à exécuter par la méthode OLEDB
$ sqlcommand = "
SI SERVERPROPERTY ('IsHadrEnabled') = 1
COMMENCER
SÉLECTIONNER
AGC.name
, RCS.replica_server_name
, ARS.role_desc
, AGL.dns_name
DE
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
SUR
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
SUR
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
SUR
AGL.group_id = ARS.group_id
OÙ
ARS.role_desc = 'PRIMAIRE'
FIN
"
## Connectez-vous à la source de données et ouvrez-la
$ connection = New-Object System.Data.OleDb.OleDbConnection $ connectionString
$ command = New-Object System.Data.OleDb.OleDbCommand $ sqlCommand, $ connection
$ connection.Open ()
## Exécuter la commande T-SQL dans une variable, récupérer les résultats et fermer la connexion
$ adapter = New-Object System.Data.OleDb.OleDbDataAdapter $ command
# $ dataset = New-Object System.Data.DataSet
[void] $ adapter.Fill ($ dataSet)
$ connection.Close ()
## Renvoie toutes les lignes de l'objet d'ensemble de données
$ dataSet.Tables | FT -AutoSize
Nous pouvons examiner les parties de script par fonctionnalité. La première section est l'endroit où nous créons l'ensemble de données pour conserver les résultats des requêtes T-SQL et créons la collection d'instances SQL à parcourir.
## Configurer l'ensemble de données pour conserver les résultats $ dataset = New-Object System.Data.DataSet ## remplir la variable avec une collection d'instances SQL $ serverlist = 'DBS04A', 'DBS04B', 'DBS05A', 'DBS05B', 'DBS06A', 'DBS06B'
La section suivante est où nous parcourons la collection d'instances SQL dans la variable, en configurant d'abord la chaîne de connexion puis en utilisant une variable pour contenir la commande T-SQL à exécuter. Enfin, nous nous connectons à l'instance SQL, exécutons le T-SQL et prenons tous les enregistrements renvoyés et les plaçons dans l'objet d'ensemble de données. Une fois que la boucle est terminée en parcourant la collection d'objets, nous vidons les lignes sur l'écran de la console de commande.
## Configurer la connexion au serveur SQL dans la boucle et exécuter T-SQL sur l'instance
foreach ($ Server dans $ serverlist)
$ connectionString = "Provider = sqloledb; Data Source = $ Server; Initial Catalog = Master; Integrated Security = SSPI;"
## place le T-SQL dans la variable à exécuter par la méthode OLEDB
$ sqlcommand = "
SI SERVERPROPERTY ('IsHadrEnabled') = 1
COMMENCER
SÉLECTIONNER
AGC.name
, RCS.replica_server_name
, ARS.role_desc
, AGL.dns_name
DE
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
SUR
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
SUR
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
SUR
AGL.group_id = ARS.group_id
OÙ
ARS.role_desc = 'PRIMAIRE'
FIN
"
## Connectez-vous à la source de données et ouvrez-la
$ connection = New-Object System.Data.OleDb.OleDbConnection $ connectionString
$ command = New-Object System.Data.OleDb.OleDbCommand $ sqlCommand, $ connection
$ connection.Open ()
## Exécuter la commande T-SQL dans une variable, récupérer les résultats et fermer la connexion
$ adapter = New-Object System.Data.OleDb.OleDbDataAdapter $ command
# $ dataset = New-Object System.Data.DataSet
[void] $ adapter.Fill ($ dataSet)
$ connection.Close ()
## Renvoie toutes les lignes de l'objet d'ensemble de données
$ dataSet.Tables | FT -AutoSize
À ce stade, nous avons terminé et pouvons lire la liste à l'écran. Alternativement, nous pourrions écrire dans un fichier sur le disque, pousser les données dans une table SQL ou même les envoyer par e-mail.
Code pour envoyer un fichier sur le disque en pièce jointe
Si vous voulez un exemple d'envoi, voici un extrait de code qui enverra un fichier sur le disque sous forme de pièce jointe.
$ from = New-Object System.Net.Mail.MailAddress "[email protected]"
$ to = New-Object System.Net.Mail.MailAddress "[email protected]"
# Créer un message
$ message = new-object System.Net.Mail.MailMessage $ from, $ to
$ message.Subject = "Liste principale du groupe de disponibilité"
$ message.Body = "Liste principale du groupe de disponibilité pour nos serveurs SQL"
$ Attachment = New-Object Net.Mail.Attachment ('c: temp SQLAvailGroupPrimary.txt', 'text / plain')
$ message.Attachments.Add ($ Attachment)
# Définir le serveur SMTP et créer un client SMTP
$ server = "smtp.mydomain.com"
$ client = new-object system.net.mail.smtpclient $ server
# Envoyez le message
"Envoi d'un message électronique à 0 à l'aide de l'hôte SMTP 1 port 2." -f $ to.ToString (), $ client.Host, $ client.Port
essayez
$ client.Send ($ message)
"Message à: 0, de: 1 a été envoyé avec succès" -f $ de, $ à
capture
"Exception interceptée dans CreateTestMessage: 0" -f $ Error.ToString ()
Exemples de résultats du tableau de bord et du script PowerShell
Les images ci-dessous montrent ce que nous pouvons trouver dans le tableau de bord ou SSMS et ce qui est renvoyé par le script PowerShell. Choisissez Dashboard sur le nœud du groupe de disponibilité plutôt qu'un niveau plus bas, nous obtiendrons les informations que nous voulons, mais pour chaque cluster, nous aurions besoin d'un onglet pour les groupes de disponibilité qu'il contient.
Nous pouvons obtenir plus de données du T-SQL, nous avons juste besoin de décider ce que nous voulons et comment l'obtenir. S'il existe un autre DMV qui a d'autres informations que celles déjà utilisées, nous pouvons les utiliser. Nous pourrions également utiliser une liste de textes ou une table SQL pour contenir les instances à utiliser dans la collection à parcourir. Nous pouvons concevoir n'importe quelle méthode pour alimenter les objets de la collection tant qu'elle extrait les instances SQL 2012 du groupe de disponibilité. Enfin, nous pouvons supprimer la clause WHERE et renvoyer tous les réplicas dans le code T-SQL.
Si vous remarquez dans le code PowerShell, la variable contenant le code T-SQL se trouve à l'intérieur de la boucle mais ne doit pas nécessairement être à l'intérieur de la boucle. Nous pouvons le placer en dehors de la boucle mais le laisser à l'intérieur nous permettrait d'utiliser des variables pour modifier la chaîne T-SQL.
Dernière mise à jour: 2014-04-14
A propos de l'auteur
Brian P ODwyer est un analyste BI au Cook Children's Health Care System qui connaît à la fois l'infrastructure et le serveur SQL
Voir tous mes conseils
Commentaires
Laisser un commentaire