Managing a lot of SQL servers does not have to to be a burden. One or a hundred, should be the same, right?MaintenenceSolution from Ola Hallengren:
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
$servers = @ (
'server_01' ,
'server_02' ,
'server_03' ,
'server_04' ,
'server_nn'
)
$newCommand = @'
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES' ,
@FragmentationLow = NULL ,
@FragmentationMedium = NULL ,
@FragmentationHigh = NULL ,
@UpdateStatistics = 'ALL' ,
@OnlyModifiedStatistics = N'Y' ,
@LogToTable = N'N';
'@
$ports = 10001. . 10005
foreach ( $node in $servers ) {
foreach ( $port in $ports ) {
$instance = "$node,$port"
$jobName = 'IndexOptimize - USER_DATABASES'
$job = Get-DbaAgentJob -SqlInstance "$instance" -Job $jobName
foreach ( $Step in $Job . jobsteps . Where { $_ . Name -eq $jobName } ) {
$Step . Command = $newCommand
$Step . Alter ( )
}
}
}
Erik Darling (t |b ) has written a longer post here .
PS. Check out how to quickly install Ola’s maintenance solution on the dbatools.io website.