Managing a lot of SQL servers does not have to to be a burden. One or a hundred, should be the same, right?
This is how you can quickly update (on a bunch of servers) the IndexOptimize job which is created if you install the 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.