{"id":91415,"date":"2021-06-28T17:00:15","date_gmt":"2021-06-28T17:00:15","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91415"},"modified":"2021-06-29T11:58:00","modified_gmt":"2021-06-29T11:58:00","slug":"azure-sql-automation-improving-the-indexing-job","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-automation-improving-the-indexing-job\/","title":{"rendered":"Azure SQL Automation: Improving the Indexing Job"},"content":{"rendered":"<p>The <strong>Azure SQL Databases<\/strong> don\u2019t give us access to the <strong>SQL Server Agent<\/strong>. Usually, we use <strong>SQL Server Agent<\/strong> to schedule jobs on premise, but in the cloud, we need a different solution.<\/p>\n<p>In the <a href=\"https:\/\/www.youtube.com\/watch?v=0BFtyQ35juA&amp;list=PLNbt9tnNIlQ7Qg3oUwgoH3h30zbccHwiJ\">technical sessions I deliver<\/a> I usually explain about <strong>Azure Automation<\/strong> and how we can create runbooks to execute scheduled tasks on <strong>SQL Server<\/strong>.<\/p>\n<p>We can start from many ready-to-use runbooks of different kinds provided for us on the <strong>Automation Gallery<\/strong>. We choose one, import it and start from this point.<\/p>\n<h2>Publishing to the Automation Gallery<\/h2>\n<p>These runbooks are not provided only by Microsoft. Anyone can create a repository in github and the repository will appear inside the runbook gallery.<\/p>\n<p>Microsoft provided instructions about <a href=\"https:\/\/techcommunity.microsoft.com\/t5\/azure-governance-and-management\/azure-automation-runbooks-moving-to-github\/ba-p\/2039337\">how to contribute with the <strong>Automation Gallery<\/strong><\/a>. We need to follow some rules, but basically it involves including one specific tag on the github repo. <strong>Azure<\/strong> will identify the repos with this tag and import the content of the repo to the <strong>Automation Gallery<\/strong>, within something like 12 or 24 hours of delay.<\/p>\n<p>There is one small catch: the <strong>Automation Account<\/strong> supports 4 kinds of runbook, but although the instructions say it is possible, I only managed to contribute with one of the types. Considering that no one outside Microsoft ever contributed with different types as well, maybe there is something missing on the instructions.<\/p>\n<p>The 4 kinds of runbook supported by the <strong>Automation Account are<\/strong>:<\/p>\n<ul>\n<li>Powershell script<\/li>\n<li>Graphical<\/li>\n<li>Powershell Workflow<\/li>\n<li>Python Script<\/li>\n<\/ul>\n<p>This is the filter of the gallery inside the <strong>Automation Account<\/strong>:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"222\" height=\"234\" class=\"wp-image-91416\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/graphical-user-interface-application-description.png\" alt=\"Graphical user interface, application\n\nDescription automatically generated\" \/><\/p>\n<p>Python Runbook has two \u201csub-types\u201d, Python 2 and Python 3. Graphical runbook can be a regular graphical one or a Graphical Powershell Workflow.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"374\" height=\"349\" class=\"wp-image-91417\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/graphical-user-interface-text-application-email-2.png\" alt=\"Graphical user interface, text, application, email\n\nDescription automatically generated\" \/><\/p>\n<p>The only kind of runbook repository we are able to create is the PowerShell script runbook. All the others are not recognized by the Gallery, at least until now no one managed to publish a different type.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1628\" height=\"264\" class=\"wp-image-91418\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/graphical-user-interface-description-automaticall.png\" alt=\"Graphical user interface\n\nDescription automatically generated with low confidence\" \/><\/p>\n<h2>The Existing Azure SQL indexing script<\/h2>\n<p>There is an Azure SQL indexing <a href=\"https:\/\/github.com\/azureautomation\/indexes-tables-in-an-azure-database-if-they-have-a-high-fragmentation\">script created by the Azure Automation team<\/a>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1637\" height=\"193\" class=\"wp-image-91419\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-61.png\" \/><\/p>\n<p>This script is very interesting. These are some details:<\/p>\n<ul>\n<li>It\u2019s flexible. It has parameters to make the script very flexible, good for many different situations.<\/li>\n<li>It checks the fragmentation to decide if it will reindex or not.<\/li>\n<li>The fragmentation value used to decide for the reindex is parameterized.<\/li>\n<li>We can use the script to reindex all the tables or a single one.<\/li>\n<\/ul>\n<p>Unfortunately, the script has one bug: It doesn\u2019t use the table schema in the code. This is not a small bug. Any table with a schema different than <strong>dbo<\/strong> will cause an error on the script.<\/p>\n<p>Of course, it\u2019s not only about <strong>dbo<\/strong> schema. It\u2019s about the default schema configured for the login used to execute the script. Tables on the default schema will work, tables on different schemas will fail. But we don\u2019t need to go so in deep on <strong>Azure SQL<\/strong> technical details.<\/p>\n<h2>The Solution \u2013 a new reindexing Runbook for Azure SQL<\/h2>\n<p>I made a fork and a pull request to the original github repository which contains the runbook and the code, contributing with a fix to the code.<\/p>\n<p>However, the review of the pull request will take a while. In the meantime, I received permission from the developer to publish my changes in a new repository if I would like, creating a new runbook template in the gallery.<\/p>\n<p>Unlike the original one, a PowerShell Workflow runbook, the one I created is recognized only as a PowerShell Runbook, but this is already enough to achieve the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1538\" height=\"161\" class=\"wp-image-91420\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-62.png\" \/><\/p>\n<p>The code of the new Runbook is this:<\/p>\n<pre class=\"crayon:false\"><span class=\"PowerShellColorizedScript\" style=\"color: #006400\"><\/span>            \r\n            \r\n    <span style=\"color: #00008b\">param<\/span><span style=\"color: #000000\">(<\/span>            \r\n            \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$True<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[string]<\/span> <span style=\"color: #a82d00\">$SqlServer<\/span><span style=\"color: #696969\">,<\/span>            \r\n            \r\n               \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$True<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[string]<\/span> <span style=\"color: #a82d00\">$Database<\/span><span style=\"color: #696969\">,<\/span>            \r\n            \r\n                \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$True<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[string]<\/span> <span style=\"color: #a82d00\">$SQLCredentialName<\/span><span style=\"color: #696969\">,<\/span>            \r\n                    \r\n            \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$False<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[int]<\/span> <span style=\"color: #a82d00\">$FragPercentage<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #800080\">20<\/span><span style=\"color: #696969\">,<\/span>            \r\n            \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$False<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[int]<\/span> <span style=\"color: #a82d00\">$SqlServerPort<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #800080\">1433<\/span><span style=\"color: #696969\">,<\/span>            \r\n            \r\n                    \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$False<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[boolean]<\/span> <span style=\"color: #a82d00\">$RebuildOffline<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #a82d00\">$False<\/span><span style=\"color: #696969\">,<\/span>            \r\n            \r\n        <span style=\"color: #696969\">[<\/span><span style=\"color: #00bfff\">parameter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">Mandatory<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$False<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">]<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[string]<\/span> <span style=\"color: #a82d00\">$Table<\/span>            \r\n            \r\n                              \r\n            \r\n    <span style=\"color: #000000\">)<\/span>            \r\n            \r\n    <span style=\"color: #006400\">#\u00a0Get\u00a0the\u00a0stored\u00a0username\u00a0and\u00a0password\u00a0from\u00a0the\u00a0Automation\u00a0credential<\/span>            \r\n            \r\n    <span style=\"color: #a82d00\">$SqlCredential<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #0000ff\">Get-AutomationPSCredential<\/span> <span style=\"color: #000080\">-Name<\/span> <span style=\"color: #a82d00\">$SQLCredentialName<\/span>            \r\n            \r\n    <span style=\"color: #00008b\">if<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$SqlCredential<\/span> <span style=\"color: #696969\">-eq<\/span> <span style=\"color: #a82d00\">$null<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n    <span style=\"color: #000000\">{<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">throw<\/span> <span style=\"color: #8b0000\">\"Could\u00a0not\u00a0retrieve\u00a0'$SQLCredentialName'\u00a0credential\u00a0asset.\u00a0Check\u00a0that\u00a0you\u00a0created\u00a0this\u00a0first\u00a0in\u00a0the\u00a0Automation\u00a0service.\"<\/span>            \r\n            \r\n    <span style=\"color: #000000\">}<\/span>            \r\n            \r\n                \r\n            \r\n    <span style=\"color: #a82d00\">$SqlUsername<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #a82d00\">$SqlCredential<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">UserName<\/span>             \r\n            \r\n    <span style=\"color: #a82d00\">$SqlPass<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #a82d00\">$SqlCredential<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">GetNetworkCredential<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Password<\/span>            \r\n            \r\n                \r\n            \r\n    <span style=\"color: #a82d00\">$TableNames<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #0000ff\">Inlinescript<\/span> <span style=\"color: #000000\">{<\/span>            \r\n            \r\n                  \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Define\u00a0the\u00a0connection\u00a0to\u00a0the\u00a0SQL\u00a0Database<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">System.Data.SqlClient.SqlConnection<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User\u00a0ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection\u00a0Timeout=30;\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                     \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Open\u00a0the\u00a0SQL\u00a0connection<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Open<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                    \r\n            \r\n        <span style=\"color: #006400\">#\u00a0SQL\u00a0command\u00a0to\u00a0find\u00a0tables\u00a0and\u00a0their\u00a0average\u00a0fragmentation<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$SQLCommandString<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #8b0000\">@\"\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0a.object_id,so.name\u00a0as\u00a0TableName,\u00a0sc.name\u00a0as\u00a0schemaName,avg_fragmentation_in_percent\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0sys.dm_db_index_physical_stats\u00a0(\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DB_ID(N'$Database')\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,\u00a0OBJECT_ID(0)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,\u00a0NULL\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,\u00a0NULL\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,\u00a0NULL)\u00a0AS\u00a0a\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN\u00a0sys.indexes\u00a0AS\u00a0b\u00a0\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0a.object_id\u00a0=\u00a0b.object_id\u00a0AND\u00a0a.index_id\u00a0=\u00a0b.index_id\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0join\u00a0sys.objects\u00a0so\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0a.object_id=so.object_id\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0join\u00a0sys.schemas\u00a0sc\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0on\u00a0so.schema_id=sc.schema_id\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0so.type_desc='USER_TABLE'\r\n\r\n\"@<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Return\u00a0the\u00a0tables\u00a0with\u00a0their\u00a0corresponding\u00a0average\u00a0fragmentation<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">new-object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlCommand<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$SQLCommandString<\/span><span style=\"color: #696969\">,<\/span> <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">CommandTimeout<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #800080\">120<\/span>            \r\n            \r\n                    \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Execute\u00a0the\u00a0SQL\u00a0command<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$FragmentedTable<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.DataSet<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlDataAdapter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #006161\">[void]<\/span><span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">fill<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$FragmentedTable<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Return\u00a0the\u00a0table\u00a0names\u00a0that\u00a0have\u00a0high\u00a0fragmentation<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">ForEach<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$FragTable<\/span> <span style=\"color: #00008b\">in<\/span> <span style=\"color: #a82d00\">$FragmentedTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Tables<\/span><span style=\"color: #696969\">[<\/span><span style=\"color: #800080\">0<\/span><span style=\"color: #696969\">]<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #000000\">{<\/span>            \r\n            \r\n            <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Table\u00a0Object\u00a0ID:\"<\/span> <span style=\"color: #696969\">+<\/span> <span style=\"color: #a82d00\">$FragTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Item<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"object_id\"<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Fragmentation:\"<\/span> <span style=\"color: #696969\">+<\/span> <span style=\"color: #a82d00\">$FragTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Item<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"avg_fragmentation_in_percent\"<\/span><span style=\"color: #000000\">)<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                        \r\n            \r\n            <span style=\"color: #00008b\">If<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$FragTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">avg_fragmentation_in_percent<\/span> <span style=\"color: #696969\">-ge<\/span> <span style=\"color: #a82d00\">$Using:FragPercentage<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #000000\">{<\/span>            \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Table\u00a0is\u00a0fragmented.\u00a0Return\u00a0this\u00a0table\u00a0for\u00a0indexing\u00a0by\u00a0finding\u00a0its\u00a0name<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$result<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #a82d00\">$FragTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Item<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"schemaName\"<\/span><span style=\"color: #000000\">)<\/span>  <span style=\"color: #696969\">+<\/span> <span style=\"color: #8b0000\">\".\"<\/span> <span style=\"color: #696969\">+<\/span> <span style=\"color: #a82d00\">$FragTable<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Item<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"TableName\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$result<\/span>                            \r\n            \r\n            <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Close<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n    <span style=\"color: #000000\">}<\/span>            \r\n            \r\n    <span style=\"color: #006400\">#\u00a0If\u00a0a\u00a0specific\u00a0table\u00a0was\u00a0specified,\u00a0then\u00a0find\u00a0this\u00a0table\u00a0if\u00a0it\u00a0needs\u00a0to\u00a0indexed,\u00a0otherwise<\/span>            \r\n            \r\n    <span style=\"color: #006400\">#\u00a0set\u00a0the\u00a0TableNames\u00a0to\u00a0$null\u00a0since\u00a0we\u00a0shouldn't\u00a0process\u00a0any\u00a0other\u00a0tables.<\/span>            \r\n            \r\n    <span style=\"color: #00008b\">If<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Table<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n    <span style=\"color: #000000\">{<\/span>            \r\n            \r\n        <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Single\u00a0Table\u00a0specified:\u00a0$Table\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">If<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$TableNames<\/span> <span style=\"color: #696969\">-contains<\/span> <span style=\"color: #a82d00\">$Table<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #000000\">{<\/span>            \r\n            \r\n            <span style=\"color: #a82d00\">$TableNames<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #a82d00\">$Table<\/span>            \r\n            \r\n        <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">Else<\/span>            \r\n            \r\n        <span style=\"color: #000000\">{<\/span>            \r\n            \r\n            <span style=\"color: #006400\">#\u00a0Remove\u00a0other\u00a0tables\u00a0since\u00a0only\u00a0a\u00a0specific\u00a0table\u00a0was\u00a0specified.<\/span>            \r\n            \r\n            <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Table\u00a0not\u00a0found:\u00a0$Table\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #a82d00\">$TableNames<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #a82d00\">$Null<\/span>            \r\n            \r\n        <span style=\"color: #000000\">}<\/span>            \r\n            \r\n    <span style=\"color: #000000\">}<\/span>            \r\n            \r\n    <span style=\"color: #006400\">#\u00a0Interate\u00a0through\u00a0tables\u00a0with\u00a0high\u00a0fragmentation\u00a0and\u00a0rebuild\u00a0indexes<\/span>            \r\n            \r\n    <span style=\"color: #00008b\">ForEach<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$TableName<\/span> <span style=\"color: #00008b\">in<\/span> <span style=\"color: #a82d00\">$TableNames<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n    <span style=\"color: #000000\">{<\/span>            \r\n            \r\n      <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #8b0000\">\"Creating\u00a0checkpoint\"<\/span>            \r\n            \r\n      <span style=\"color: #0000ff\">Checkpoint-Workflow<\/span>            \r\n            \r\n      <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #8b0000\">\"Indexing\u00a0Table\u00a0$TableName...\"<\/span>            \r\n            \r\n                  \r\n            \r\n      <span style=\"color: #0000ff\">InlineScript<\/span> <span style=\"color: #000000\">{<\/span>            \r\n            \r\n                      \r\n            \r\n        <span style=\"color: #a82d00\">$SQLCommandString<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #8b0000\">@\"\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXEC('ALTER\u00a0INDEX\u00a0ALL\u00a0ON\u00a0$Using:TableName\u00a0REBUILD\u00a0with\u00a0(ONLINE=ON)')\r\n\r\n\"@<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Define\u00a0the\u00a0connection\u00a0to\u00a0the\u00a0SQL\u00a0Database<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">System.Data.SqlClient.SqlConnection<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User\u00a0ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection\u00a0Timeout=30;\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                    \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Open\u00a0the\u00a0SQL\u00a0connection<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Open<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Define\u00a0the\u00a0SQL\u00a0command\u00a0to\u00a0run.\u00a0In\u00a0this\u00a0case\u00a0we\u00a0are\u00a0getting\u00a0the\u00a0number\u00a0of\u00a0rows\u00a0in\u00a0the\u00a0table<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">new-object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlCommand<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$SQLCommandString<\/span><span style=\"color: #696969\">,<\/span> <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Set\u00a0the\u00a0Timeout\u00a0to\u00a0be\u00a0less\u00a0than\u00a030\u00a0minutes\u00a0since\u00a0the\u00a0job\u00a0will\u00a0get\u00a0queued\u00a0if\u00a0&gt;\u00a030<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Setting\u00a0to\u00a025\u00a0minutes\u00a0to\u00a0be\u00a0safe.<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">CommandTimeout<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #800080\">1500<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Execute\u00a0the\u00a0SQL\u00a0command<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">Try<\/span>             \r\n            \r\n        <span style=\"color: #000000\">{<\/span>            \r\n            \r\n            <span style=\"color: #a82d00\">$Ds<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.DataSet<\/span>            \r\n            \r\n            <span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlDataAdapter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #006161\">[void]<\/span><span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">fill<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Ds<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n        <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #00008b\">Catch<\/span>            \r\n            \r\n        <span style=\"color: #000000\">{<\/span>            \r\n            \r\n            <span style=\"color: #00008b\">if<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$_<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Exception<\/span> <span style=\"color: #696969\">-match<\/span> <span style=\"color: #8b0000\">\"offline\"<\/span><span style=\"color: #000000\">)<\/span> <span style=\"color: #696969\">-and<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Using:RebuildOffline<\/span><span style=\"color: #000000\">)<\/span> <span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #000000\">{<\/span>            \r\n            \r\n                <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #000000\">(<\/span><span style=\"color: #8b0000\">\"Building\u00a0table\u00a0$Using:TableName\u00a0offline\"<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$SQLCommandString<\/span> <span style=\"color: #696969\">=<\/span> <span style=\"color: #8b0000\">@\"\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXEC('ALTER\u00a0INDEX\u00a0ALL\u00a0ON\u00a0$Using:TableName\u00a0REBUILD')\r\n\r\n\"@<\/span>                          \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Define\u00a0the\u00a0SQL\u00a0command\u00a0to\u00a0run.\u00a0<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">new-object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlCommand<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$SQLCommandString<\/span><span style=\"color: #696969\">,<\/span> <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Set\u00a0the\u00a0Timeout\u00a0to\u00a0be\u00a0less\u00a0than\u00a030\u00a0minutes\u00a0since\u00a0the\u00a0job\u00a0will\u00a0get\u00a0queued\u00a0if\u00a0&gt;\u00a030<\/span>            \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Setting\u00a0to\u00a025\u00a0minutes\u00a0to\u00a0be\u00a0safe.<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">CommandTimeout<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #800080\">1500<\/span>            \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Execute\u00a0the\u00a0SQL\u00a0command<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$Ds<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.DataSet<\/span>            \r\n            \r\n                <span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">=<\/span><span style=\"color: #0000ff\">New-Object<\/span> <span style=\"color: #8a2be2\">system.Data.SqlClient.SqlDataAdapter<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Cmd<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n                <span style=\"color: #006161\">[void]<\/span><span style=\"color: #a82d00\">$Da<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">fill<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #a82d00\">$Ds<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n            <span style=\"color: #000000\">}<\/span>            \r\n            \r\n            <span style=\"color: #00008b\">Else<\/span>            \r\n            \r\n            <span style=\"color: #000000\">{<\/span>            \r\n            \r\n                <span style=\"color: #006400\">#\u00a0Will\u00a0catch\u00a0the\u00a0exception\u00a0here\u00a0so\u00a0other\u00a0tables\u00a0can\u00a0be\u00a0processed.<\/span>            \r\n            \r\n                <span style=\"color: #0000ff\">Write-Error<\/span> <span style=\"color: #8b0000\">\"Table\u00a0$Using:TableName\u00a0could\u00a0not\u00a0be\u00a0indexed.\u00a0Investigate\u00a0indexing\u00a0each\u00a0index\u00a0instead\u00a0of\u00a0the\u00a0complete\u00a0table\u00a0$_\"<\/span>            \r\n            \r\n             <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #000000\">}<\/span>            \r\n            \r\n        <span style=\"color: #006400\">#\u00a0Close\u00a0the\u00a0SQL\u00a0connection<\/span>            \r\n            \r\n        <span style=\"color: #a82d00\">$Conn<\/span><span style=\"color: #696969\">.<\/span><span style=\"color: #000000\">Close<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #000000\">)<\/span>            \r\n            \r\n      <span style=\"color: #000000\">}<\/span>              \r\n            \r\n    <span style=\"color: #000000\">}<\/span>            \r\n            \r\n    <span style=\"color: #0000ff\">Write-Verbose<\/span> <span style=\"color: #8b0000\">\"Finished\u00a0Indexing\"<\/span><\/pre>\n<p>These are the main differences between this new script and the original one:<\/p>\n<ul>\n<li>The original one is a powershell workflow, while the new one is only a powershell script, because the gallery doesn&#8217;t recognize workflows runbooks.<\/li>\n<li>The new script changed the SQL query to include the <strong>sys.schema<\/strong> DMV, making the correct joins to retrieve the table schema<\/li>\n<li>The retrieved schema is concatenated with the table name to build the name which will be used during the reindexing.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>The <strong>Azure Automation Account<\/strong> is a great feature to schedule tasks on <strong>Azure SQL<\/strong> and all other <strong>Azure<\/strong> services. The fact we can contribute to this feature building runbooks templates on github and offering them to the entire community makes this feature even better.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Azure SQL Databases don\u2019t give us access to the SQL Server Agent. Usually, we use SQL Server Agent to schedule jobs on premise, but in the cloud, we need a different solution. In the technical sessions I deliver I usually explain about Azure Automation and how we can create runbooks to execute scheduled tasks&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[143578,143576,4635,143577],"coauthors":[6810],"class_list":["post-91415","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-automation-gallery","tag-azure-automation","tag-powershell","tag-runbooks"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91415","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=91415"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91415\/revisions"}],"predecessor-version":[{"id":91437,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91415\/revisions\/91437"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=91415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=91415"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=91415"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=91415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}