Home
%3CLINGO-SUB%20id%3D%22lingo-sub-828111%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828111%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Yochanan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20setup%20this%20database%20maintenance%20job%20using%20Runbooks%20and%20I%20am%20getting%20the%20following%20errors%20and%20I%20am%20new%20to%20Azure%20and%20its%20not%20clear%20why%20I%20am%20getting%20these%20two%20errors.%26nbsp%3B%20Our%20server%20is%20set%20to%20allow%20remote%20connections%20and%20the%20server%20does%20exist.%20Could%20you%20share%20your%20thoughts%3F%20Thanks%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EError%201%3A%3C%2FP%3E%3CP%3EAzureSQLServerName%20%3A%20The%20term%20'AzureSQLServerName'%20is%20not%20recognized%20as%20the%20name%20of%20a%20cmdlet%2C%20function%2C%20script%20file%2C%20or%3CBR%20%2F%3Eoperable%20program.%20Check%20the%20spelling%20of%20the%20name%2C%20or%20if%20a%20path%20was%20included%2C%20verify%20that%20the%20path%20is%20correct%20and%20try%3CBR%20%2F%3Eagain.%3CBR%20%2F%3EAt%20line%3A1%20char%3A1%3CBR%20%2F%3E%2B%20AzureSQLServerName%20%3D%20%22*****.database.window%20...%3CBR%20%2F%3E%2B%20~~~~~~~~~~~~~~~~~~%3CBR%20%2F%3E%2B%20CategoryInfo%20%3A%20ObjectNotFound%3A%20(AzureSQLServerName%3AString)%20%5B%5D%2C%20CommandNotFoundException%3CBR%20%2F%3E%2B%20FullyQualifiedErrorId%20%3A%20CommandNotFoundException%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2nd%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EInvoke-Sqlcmd%20%3A%20A%20network-related%20or%20instance-specific%20error%20occurred%20while%20establishing%20a%20connection%20to%20SQL%20Server.%20The%20server%20was%20not%20found%20or%20was%20not%20accessible.%20Verify%20that%20the%20instance%20name%20is%20correct%20and%20that%20SQL%20Server%20is%20configured%20to%20allow%20remote%20connections.%20(provider%3A%20TCP%20Provider%2C%20error%3A%200%20-%20No%20such%20host%20is%20known.)%20At%20line%3A6%20char%3A16%20%2B%20...%20LOutput%20%3D%20%24(Invoke-Sqlcmd%20-ServerInstance%20%24AzureSQLServerName%20-Userna%20...%20%2B%20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%20%2B%20CategoryInfo%20%3A%20InvalidOperation%3A%20(%3A)%20%5BInvoke-Sqlcmd%5D%2C%20SqlException%20%2B%20FullyQualifiedErrorId%20%3A%20SqlExceptionError%2CMicrosoft.SqlServer.Management.PowerShell.GetScriptCommand%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EKumar.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-368974%22%20slang%3D%22en-US%22%3EAutomating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2015%2C%202018%20%3C%2FSTRONG%3E%3CBR%20%2F%3ETo%20provide%20complete%20solution%20to%20maintain%20you%20Azure%20SQL%20DB%20statistics%20and%20maintenance%20we%20provide%20our%20maintenance%20script%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2016%2F07%2F03%2Fhow-to-maintain-azure-sql-indexes-and-statistics%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CSTRONG%3Ehere%20%3C%2FSTRONG%3E%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20this%20article%2C%20we%20will%20explain%20step%20by%20step%20how%20to%20automate%20this%20maintenance%20on%20Azure%20(You%20can%20also%20use%20that%20to%20automate%20your%20own%20T-SQL%20tasks)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EGeneral%20steps%3A%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3ECreate%20Azure%20automation%20account%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EImport%20SQLServer%20module%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EAdd%20Credentials%20to%20access%20SQL%20DB%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EAdd%20a%20runbook%20to%20run%20the%20maintenance%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3ESchedule%20task%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3E%20Step%20by%20step%20instructions%3A%20%3CBR%20%2F%3E%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%20Create%20new%20automation%20account%20%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3ELogin%20to%20your%20Azure%20portal%20and%20click%20%22New%22%20(the%20green%20plus%20sign)%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EType%20%22automation%22%20in%20the%20search%20box%2C%20and%20choose%20automation.%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88966i3908AE13B16FBF05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%201%20%E2%80%93%20new%20automation%20account%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EClick%20%22create%22%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EFill%20the%20form%2C%20choose%20a%20name%20for%20your%20automation%20account%2C%20and%20choose%20in%20which%20resource%20group%20it%20will%20be%20placed.%3C%2FDIV%3E%3CBR%20%2F%3Emake%20sure%20you%20choose%20%22YES%22%20for%20the%20Create%20Azure%20Run%20As%20account.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20315px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88967i451E5F2B4257AE33%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%202%20%E2%80%93%20add%20automation%20account%20form.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EClick%20%22create%22%20and%20wait%20for%20the%20account%20to%20be%20created.%20The%20new%20automation%20configuration%20blade%20will%20be%20opened%20once%20the%20provision%20completed.%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%20Import%20SQLServer%20module%20%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EClick%20on%20%22Modules%22%20at%20the%20left%20options%20panel%2C%20and%20then%20click%20on%20%22Browse%20Gallery%22%20and%20search%20for%20%22SQLServer%22%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20859px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88968i9991C0BE08BEAEF0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%203%20%E2%80%93%20add%20module%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EChoose%20%22SqlServer%22%20by%20matteot_msft%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20835px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88969i0B1F3645596FF8C9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%204%20%E2%80%93%20module%20name%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EThen%20click%20on%20%22import%22%20and%20the%20%22OK%22%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EWait%20for%20the%20import%20to%20complete%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%20Add%20Credentials%20to%20access%20SQL%20DB%20%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EThis%20will%20use%20secure%20way%20to%20hold%20login%20name%20and%20password%20that%20will%20be%20used%20to%20access%20Azure%20SQL%20DB%3C%2FDIV%3E%3CBR%20%2F%3EYou%20can%20skip%20this%20and%20use%20it%20as%20clear%20text%20if%20you%20like%20to%20use%20clear%20text%20skip%20to%20the%20next%20step.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EUnder%20%22Shared%20Resources%22%20click%20on%20credentials%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20220px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88970i19C92FE2F634D810%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%205%20%E2%80%93%20Add%20new%20credential%20object%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EThen%20click%20on%20%22Add%20Credential%22%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EType%20%22SQLLogin%22%20as%20the%20name%20of%20the%20credential.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EIn%20the%20username%20field%20type%20the%20SQL%20Login%20that%20will%20be%20used%20for%20maintenance%20and%20its%20password.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EClick%20%22Create%22%3C%2FDIV%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%20Add%20a%20runbook%20to%20run%20the%20maintenance%20%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EClick%20on%20%22runbooks%22%20at%20the%20left%20panel%20and%20then%20click%20%22add%20a%20runbook%22%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20613px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88971i415DA6C68BDF1BFA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%206%20%E2%80%93%20Add%20a%20runbook%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EChoose%20%22create%20a%20new%20runbook%22%20and%20then%20give%20it%20a%20name%20and%20choose%20%22Powershell%22%20as%20the%20type%20of%20the%20runbook%20and%20then%20click%20on%20%22create%22%3C%2FDIV%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20623px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88972i52E49ABDB7FC3D10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%207%20%E2%80%93%20add%20new%20PowerShell%20runbook%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%3CDIV%3ECopy%20and%20paste%20the%20following%20row%20to%20the%20new%20runbook.%3C%2FDIV%3E%3CBR%20%2F%3EMake%20sure%20you%20change%20your%20database%20properties.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBR%20%2F%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%24AzureSQLServerName%20%3D%20%22%3CSERVERNAME%3E%22%20%0A%24AzureSQLDatabaseName%20%3D%20%22%3CDATABASENAME%3E%22%20%0A%0A%24AzureSQLServerName%20%3D%20%24AzureSQLServerName%20%2B%20%22.database.windows.net%22%20%0A%24Cred%20%3D%20Get-AutomationPSCredential%20-Name%20%22SQLLogin%22%20%0A%24SQLOutput%20%3D%20%24(Invoke-Sqlcmd%20-ServerInstance%20%24AzureSQLServerName%20-Username%20%24Cred.UserName%20-Password%20%24Cred.GetNetworkCredential().Password%20-Database%20%24AzureSQLDatabaseName%20-Query%20%22exec%20%5Bdbo%5D.%5BAzureSQLMaintenance%5D%20%40Operation%3D'all'%20%2C%40LogToTable%3D1%22%20-QueryTimeout%2065535%20-ConnectionTimeout%2060%20-Verbose)%204%26gt%3B%26amp%3B1%20%0A%0AWrite-Output%20%24SQLOutput%E2%80%8B%3C%2FDATABASENAME%3E%3C%2FSERVERNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3ETIP%20%3C%2FSTRONG%3E%3A%20to%20redirect%20the%20verbose%20output%20to%20the%20runbook%20log%20we%20use%20the%20technique%20as%20described%20%3CA%20href%3D%22https%3A%2F%2Fconnect.microsoft.com%2FPowerShell%2Ffeedbackdetail%2Fview%2F297055%2Fcapture-warning-verbose-debug-and-host-output-via-alternate-streams%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EClick%20on%20Publish%20and%20confirm.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%20Schedule%20task%20%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%0A%3CDIV%3EClick%20on%20Schedules%20%3C%2FDIV%3E%0A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20256px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88973iA19C4DE632522D89%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%208%20%E2%80%93%20Schedules%20%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3EClick%20on%20%22Add%20a%20schedule%22%20and%20follow%20the%20instructions%20to%20choose%20existing%20schedule%20or%20create%20a%20new%20schedule.%20%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3COL%3E%0A%3CLI%3E%3CBR%20%2F%3E%0A%3CDIV%3EChoose%20a%20time%20when%20the%20application%20is%20in%20the%20idlest%20figure%2C%20as%20running%20the%20maintenance%20might%20impact%20on%20performance%20while%20it's%20executing.%20%3C%2FDIV%3E%0A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20994px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88974iA7E60F3E758DB264%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%209%20%E2%80%93%20Create%20new%20Schedule%20%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3EMonitoring%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3Eyou%20can%20monitor%20the%20success%20of%20the%20job%20by%20reviewing%20the%20Automation%20overview%20page%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20608px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88975i53D3F5B503A38E9F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%2010%20%E2%80%93%20Job%20Overview.%20%3CBR%20%2F%3E%3CBR%20%2F%3Ethen%20you%20can%20click%20on%20each%20category%20and%20drill%20down...%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88976iAD6DFF3324D8AB72%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%2011%20%E2%80%93%20Job%20executions%20%3CBR%20%2F%3E%3CBR%20%2F%3Ethen%20you%20can%20click%20on%20a%20specific%20execution%20and%20get%20more%20details%20about%20it%20including%20the%20output%20of%20the%20script%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20998px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88977i21F7FA22E53731FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFigure%2012%20%E2%80%93%20Details%20of%20job%20execution%20and%20output%20information.%20%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20you%20enjoy%20this%20post%2C%20please%20share%20any%20thoughts%20on%20a%20comment%20here%20in%20this%20post.%20%3CBR%20%2F%3E%3CBR%20%2F%3ENotes%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3EAutomation%20account%20has%20a%20limit%20of%20500%20minutes%20of%20execution%20time%20per%20subscription%20per%20month%20on%20its%20free%20tier.%20More%20information%20about%20automation%20account%20limits%20can%20be%20found%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-subscription-service-limits%23automation-limits%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20here%3C%2FA%3E%20%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMore%20information%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fautomation%2Fautomation-offering-get-started%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGetting%20Started%20with%20Azure%20Automation%20%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fautomation%2Fautomation-create-standalone-account%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECreate%20a%20standalone%20Azure%20Automation%20account%20%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fautomation%2Fautomation-first-runbook-textual-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMy%20first%20PowerShell%20runbook%3C%2FA%3E%20%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368974%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Jan%2015%2C%202018%20To%20provide%20complete%20solution%20to%20maintain%20you%20Azure%20SQL%20DB%20statistics%20and%20maintenance%20we%20provide%20our%20maintenance%20script%20hereIn%20this%20article%2C%20we%20will%20explain%20step%20by%20step%20how%20to%20automate%20this%20maintenance%20on%20Azure%20(You%20can%20also%20use%20that%20to%20automate%20your%20own%20T-SQL%20tasks)General%20steps%3A%20Create%20Azure%20automation%20account%20Import%20SQLServer%20module%20Add%20Credentials%20to%20access%20SQL%20DB%20Add%20a%20runbook%20to%20run%20the%20maintenance%20Schedule%20taskStep%20by%20step%20instructions%3A%20Create%20new%20automation%20account%20Login%20to%20your%20Azure%20portal%20and%20click%20%22New%22%20(the%20green%20plus%20sign)%20Type%20%22automation%22%20in%20the%20search%20box%2C%20and%20choose%20automation.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890868%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890868%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20need%20add%20modules%20under%20automation%20account.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-938092%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-938092%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20I%20can%20exclude%20a%20particular%20table%20from%20this%20maintenance%20job%3F%20There%20is%20a%20large%20table%20in%20our%20database%20and%20collecting%20stats%20and%20rebuilding%20indexes%20on%20them%20takes%20forever%20and%20the%20job%20never%20completes.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EKumar.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-938095%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-938095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-942188%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-942188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399945%22%20target%3D%22_blank%22%3E%40Kumar_Vivekanandam%3C%2FA%3E%26nbsp%3B-%20at%20this%20time%20we%20do%20not%20have%20that%20option%20for%20the%20maintenance%2C%20however%20you%20can%20edit%20the%20maintenance%20procedure%20and%20exclude%20the%20table%20you%20like%20to%20exclude.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-943827%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-943827%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B.%20I%20will%20review%20the%20code%20to%20check%20where%20the%20table%20needs%20to%20be%20excluded.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EKumar.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

First published on MSDN on Jan 15, 2018
To provide complete solution to maintain you Azure SQL DB statistics and maintenance we provide our maintenance script here


In this article, we will explain step by step how to automate this maintenance on Azure (You can also use that to automate your own T-SQL tasks)


General steps:


    1. Create Azure automation account

 

    1. Import SQLServer module

 

    1. Add Credentials to access SQL DB

 

    1. Add a runbook to run the maintenance

 

    1. Schedule task



Step by step instructions:


    1. Create new automation account

 

    1. Login to your Azure portal and click "New" (the green plus sign)

 


    1. Type "automation" in the search box, and choose automation.



      Figure 1 – new automation account

 

    1. Click "create"

 


    1. Fill the form, choose a name for your automation account, and choose in which resource group it will be placed.

      make sure you choose "YES" for the Create Azure Run As account.




      Figure 2 – add automation account form.

 


    1. Click "create" and wait for the account to be created. The new automation configuration blade will be opened once the provision completed.

 

    1. Import SQLServer module

 


    1. Click on "Modules" at the left options panel, and then click on "Browse Gallery" and search for "SQLServer"



      Figure 3 – add module

 


    1. Choose "SqlServer" by matteot_msft



      Figure 4 – module name

 

    1. Then click on "import" and the "OK"

 


    1. Wait for the import to complete

 

    1. Add Credentials to access SQL DB

 


    1. This will use secure way to hold login name and password that will be used to access Azure SQL DB

      You can skip this and use it as clear text if you like to use clear text skip to the next step.

 


    1. Under "Shared Resources" click on credentials



      Figure 5 – Add new credential object

 

    1. Then click on "Add Credential"

 

    1. Type "SQLLogin" as the name of the credential.

 

    1. In the username field type the SQL Login that will be used for maintenance and its password.

 


    1. Click "Create"

 

    1. Add a runbook to run the maintenance

 


    1. Click on "runbooks" at the left panel and then click "add a runbook"



      Figure 6 – Add a runbook

 


    1. Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"



      Figure 7 – add new PowerShell runbook

 


    1. Copy and paste the following row to the new runbook.

      Make sure you change your database properties.

       


      $AzureSQLServerName = "<ServerName>" 
      $AzureSQLDatabaseName = "<DatabaseName>" 
      
      $AzureSQLServerName = $AzureSQLServerName + ".database.windows.net" 
      $Cred = Get-AutomationPSCredential -Name "SQLLogin" 
      $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 
      
      Write-Output $SQLOutput​




      TIP : to redirect the verbose output to the runbook log we use the technique as described here

 

    1. Click on Publish and confirm.

 

    1. Schedule task

 


    1. Click on Schedules



      Figure 8 – Schedules

 

    1. Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.

 


    1. Choose a time when the application is in the idlest figure, as running the maintenance might impact on performance while it's executing.



      Figure 9 – Create new Schedule



Monitoring

you can monitor the success of the job by reviewing the Automation overview page



Figure 10 – Job Overview.

then you can click on each category and drill down...



Figure 11 – Job executions

then you can click on a specific execution and get more details about it including the output of the script



Figure 12 – Details of job execution and output information.

I hope you enjoy this post, please share any thoughts on a comment here in this post.

Notes:

    • Automation account has a limit of 500 minutes of execution time per subscription per month on its free tier. More information about automation account limits can be found here



More information:

Getting Started with Azure Automation

Create a standalone Azure Automation account

My first PowerShell runbook

6 Comments
Occasional Visitor

Hi Yochanan,

 

I am trying to setup this database maintenance job using Runbooks and I am getting the following errors and I am new to Azure and its not clear why I am getting these two errors.  Our server is set to allow remote connections and the server does exist. Could you share your thoughts? Thanks for the help.

 

Error 1:

AzureSQLServerName : The term 'AzureSQLServerName' is not recognized as the name of a cmdlet, function, script file, or
operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At line:1 char:1
+ AzureSQLServerName = "*****.database.window ...
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (AzureSQLServerName:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

 

2nd error:

 

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) At line:6 char:16 + ... LOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Userna ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand  

 

Thanks,

Kumar.

Occasional Visitor

You need add modules under automation account. 

 

Occasional Visitor

Is there a way I can exclude a particular table from this maintenance job? There is a large table in our database and collecting stats and rebuilding indexes on them takes forever and the job never completes. 

 

Thanks,

Kumar.

Occasional Visitor
Microsoft

@Kumar_Vivekanandam - at this time we do not have that option for the maintenance, however you can edit the maintenance procedure and exclude the table you like to exclude.

 

Occasional Visitor

Thank you @Yochanan_MSFT . I will review the code to check where the table needs to be excluded.

 

Thanks,

Kumar.