Here's my personal tweaked settings for deploying Minionware's fantastic Reindex & Backup jobs. In the development environment, I wanted to have some scheduled jobs running to provide a safety net, as well ensure updated statistics, but there were a few default settings I wanted to adjust. In particular, I tweaked the default fill factor back to 0/100. I also installed all the objects to a new "minion" database instead of in master, as I'm beginning to be a fan of isolating these type of maintenance jobs with logging to their own isolated database to easy portability. I also adjusted the default retain days on backups to 30.
You can use this template as a guide to help you adjust the default backup settings to fit your environment a little better.
There has been various forms of discussion on the adjustments of Fill Factor for example on the defaults. For more detailed explanation, see Brentozar.com post An Introduction to Fillfactor in SQL Server. For my usage, I wanted to leave the fill factors as default, so the install scripts flips these back to my desired settings. I also run the sp_config command to ensure backup compression is enabled to save some space.
Maybe this will help you get up to speed if you want to try out this great solution, but tweak a few defaults.
The ease of installation across multiple instances makes this my current favorite solution, followed by the fantastic Ola Hallengren solution.
########################################################################## | |
########################################################################## | |
################### Minion Reindex Mass Install ########################## | |
########################################################################## | |
########################################################################## | |
# !!!!!!!!!!!!!!!! Read all comments before proceeding !!!!!!!!!!!!!!!!! | |
# Use this script to deploy MinionReindex to several servers. | |
# Change the servers. I can't anticipate how you're going to fill that variable so you can fill it from a table, or from a file, etc. | |
# $DBName is the name of the DB where you want the Minion objects to be installed. | |
# Don't forget to change the file location. | |
Import-Module SQLPS -DisableNameChecking; | |
$Servers = "SERVERNAMEHERE" #can provide more servers with comma delimited quoted format. ie "SERVER1","SERVER2","SERVER3" | |
$DBName = "Minion" | |
$MinionInstallFile = [io.path]::Combine($PSScriptRoot,'MinionReindexInstall.sql') | |
$Servers | %{ | |
$currServer = $_; | |
$currServer | |
$QueryCreateDb = @' | |
if not exists (select * from sys.databases where name = 'Minion') | |
begin | |
print 'Creating database MinionWare per not exists for log tables' | |
create database [Minion]; | |
end | |
else | |
begin | |
print 'Database [Minion] already exists, bypassed creation' | |
end | |
'@ | |
$AdjustDefaults = @' | |
print 'Adjusting minion defaults for fill factor back to taking server defaults' | |
update | |
Minion.IndexSettingsDB | |
set | |
FILLFACTORopt = null; | |
update | |
Minion.IndexSettingsTable | |
set | |
FILLFACTORopt = null; | |
update | |
Minion.IndexMaintLogDetails | |
set | |
FILLFACTORopt = null; | |
update | |
Minion.IndexTableFrag | |
set | |
FILLFACTORopt = null; | |
'@ | |
Invoke-Sqlcmd -ServerInstance $currServer -Database 'master' -Query $QueryCreateDb -Verbose -QueryTimeout 500 | |
Invoke-Sqlcmd -ServerInstance $currServer -Database $DBName -InputFile $MinionInstallFile | |
Invoke-Sqlcmd -ServerInstance $currServer -Database $DBName -Query $AdjustDefaults | |
} |
########################################################################## | |
########################################################################## | |
################### Minion Reindex Mass Install ########################## | |
########################################################################## | |
########################################################################## | |
# !!!!!!!!!!!!!!!! Read all comments before proceeding !!!!!!!!!!!!!!!!! | |
# Use this script to deploy MinionReindex to several servers. | |
# Change the servers. I can't anticipate how you're going to fill that variable so you can fill it from a table, or from a file, etc. | |
# $DBName is the name of the DB where you want the Minion objects to be installed. | |
# Don't forget to change the file location. | |
clear-host | |
Import-Module SqlServer -DisableNameChecking -Force; | |
$Servers = "SERVERNAMEHERE" #can provide more servers with comma delimited quoted format. ie "SERVER1","SERVER2","SERVER3" | |
$DBName = "Minion" | |
$MinionInstallFile = [io.path]::Combine($PSScriptRoot,'MinionBackupInstall.sql') | |
write-host "Install file: $MinionInstallFile" | |
$Servers | %{ | |
$currServer = $_; | |
$currServer | |
$QueryCreateDb = @' | |
if not exists (select * from sys.databases where name = 'Minion') | |
begin | |
print 'Creating database MinionWare per not exists for log tables' | |
create database [Minion]; | |
end | |
else | |
begin | |
print 'Database [Minion] already exists, bypassed creation' | |
end | |
'@ | |
$UpdateDefaults = @' | |
print 'Changing Defaults to maintain only limited amount of backups' | |
update | |
Minion.BackupSettings | |
set | |
RetainDays = 30 | |
,Verify = 'AfterBatch' | |
where | |
DBName = 'MinionDefault' | |
and BackupType = 'All'; | |
print 'Enabling backup compression by default' | |
------------------------- Various configuration tweaks for better defaults ------------------------- | |
exec sys.sp_configure 'advanced' | |
,'1' | |
reconfigure | |
exec sys.sp_configure N'backup compression default' | |
,N'1' | |
reconfigure with override; | |
'@ | |
Invoke-Sqlcmd -ServerInstance $currServer -Database 'master' -Query $QueryCreateDb -Verbose -QueryTimeout 500 | |
Invoke-Sqlcmd -ServerInstance $currServer -Database $DBName -InputFile $MinionInstallFile -DisableVariables | |
Invoke-Sqlcmd -ServerInstance $currServer -Database $DBName -Query $UpdateDefaults -DisableVariables -Verbose | |
} | |