Discussion :

[Boubou2020]

Bonjour a tous ,

je cherche une méthode pour forcer mon boucle Foreach a continuer même en cas d'erreur de connectivité sur un de mes serveurs présent dans une liste

En fait le script il s'arrête a chaque fois il exits un problème de connexion alors que moi je veux qu'il passe a la serveur suivant même en cas erreur

merci pour vos aide

Code PowerShell :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
Import-Module SqlServer
 
$server = "xxxxxx"
$inventoryDB = "xxxxx"
 
#This is the definition of the table that will contain the values for each instance you wish to collect information from  
$resourcesUsageTable = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')
CREATE TABLE CPU_Memory_Usage(
   [server] [varchar](128) NOT NULL,
   [max_server_memory] [int] NOT NULL,
   [sql_memory_usage] [int] NOT NULL,
   [physical_memory] [int] NOT NULL,
   [available_memory] [int] NOT NULL,
   [system_memory_state] [varchar](255) NOT NULL,
   [page_life_expectancy] [int] NOT NULL,
   [cache hit ratio] NUMERIC(10,6) NOT NULL,
   [cpu_usage_30] [int] NOT NULL,
   [cpu_usage_15] [int] NOT NULL,
   [cpu_usage_10] [int] NOT NULL,
   [cpu_usage_5] [int] NOT NULL,
   [data_sample_timestamp] [datetime] NULL
) ON [PRIMARY]
"
 
#Make sure you create this table in your central environment, where you wish to gather the information from all the desired instances
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable
 
 
#/* Récupérer List Serveur Pour leur parcours */
$instanceLookupQuery = "SELECT name  FROM servers" 
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
 
$resourcesQuery = "
WITH SQLProcessCPU
AS(
   SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
   FROM ( 
         SELECT 
           record.value('(./Record/@id)[1]', 'int') AS record_id,
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], 
           [timestamp] 
         FROM ( 
              SELECT [timestamp], CONVERT(xml, record) AS [record] 
              FROM sys.dm_os_ring_buffers 
              WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
              AND record LIKE '%<SystemHealth>%'
              ) AS x 
        ) AS y
) 
 
SELECT 
   SERVERPROPERTY('SERVERNAME') AS 'Instance',
   (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory',
   (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)',
   (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)',
   (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)',
   (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
   (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
      (SELECT CAST(
        (
          SELECT CAST (cntr_value AS BIGINT)
          FROM sys.dm_os_performance_counters  
          WHERE counter_name = 'Buffer cache hit ratio'
        )* 100.00
        /
        (
          SELECT CAST (cntr_value AS BIGINT)
          FROM sys.dm_os_performance_counters  
          WHERE counter_name = 'Buffer cache hit ratio base'
        ) AS NUMERIC(6,3)
      ))as [cache hit ratio] ,
   (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 30) AS 'SQLProcessUtilization30',
   (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 15) AS 'SQLProcessUtilization15',
   (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 10) AS 'SQLProcessUtilization10',
   (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 5)  AS 'SQLProcessUtilization5',
GETDATE() AS 'Data Sample Timestamp'
 
"
Try
{
#Parcours Lists serveur par Foreach 
foreach ($instance in $instances){
   Write-Host "Fetching CPU/RAM information for instance" $instance.name
   $results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
 
   #Insert statement 
   if($results.Length -ne 0){    
      $insert = "INSERT INTO CPU_Memory_Usage VALUES"
      foreach($result in $results){        
         $insert += "
         (
         '"+$result['Instance']+"',
         "+$result['Max Server Memory']+",
         "+$result['SQL Server Memory Usage (MB)']+",
         "+$result['Physical Memory (MB)']+",
         "+$result['Available Memory (MB)']+",
            '"+$result['System Memory State']+"',
            "+$result['Page Life Expectancy']+",
            "+$result['cache hit ratio']+",
            "+$result['SQLProcessUtilization30']+",
            "+$result['SQLProcessUtilization15']+",
            "+$result['SQLProcessUtilization10']+",
            "+$result['SQLProcessUtilization5']+",
            GETDATE()
            ),
       "
       }
 
   #Perform the INSERT in the central table
   Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   }
}
 
Write-Host "Done!" 
}
catch
{
write-host  "Error de connectivité" $instance.name
}

[ericlm128]

Tu peux jouer avec ErrorAction pour définir comment traiter les cas d'erreur.
Ou alors laisser l’erreur se produite et l'attraper avec un bloc Try/Catch