segunda-feira, 15 de dezembro de 2014

Index Rebuild RUNBOOK on SQL AZURE

As from last week I started a database migration from Amazon RDS to Microsoft SQL Azure. After migrating the DB Structure and some basic data for testing (we are not in production yeat), I had to come up with some kind of workaround on some SQL AZURE limitations, and one of them is the lack of SQL JOBS.

In order to perform a INDEX REBUILD job, I had to use Automated Tasks from Microsoft Azure, using a PowerShell like script.

This is the draft from what I did:

workflow IndexRebuild 

     
    inlinescript { 
        # Define the connection to the SQL Database
        $server = "yourEndPointHere" 
        $db     = "yourDatabaseNameHere"
        $usr    = "yourUserHere"
        $psw    = "yourPasswordHere"
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=$server;Database=$db;User ID=$usr;Password=$psw;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;") 
         
        # Open the SQL connection 
        $Conn.Open() 

        # Here you can add your defrag proc... I added one bellow... 
        $Cmd=new-object system.Data.SqlClient.SqlCommand("Exec SpDefragAllIndex;", $Conn) 
        $Cmd.CommandTimeout=120 

        # Execute the SQL command 
        $Ds=New-Object system.Data.DataSet 
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) 
        [void]$Da.fill($Ds) 

        # Output the count 
        $Ds.Tables.Column1 

        # Close the SQL connection 
        $Conn.Close() 
    } 
}

That is just the Task script. You need a index rebuild proc to perform the task. Here is what i'm using on AZURE:

ALTER PROC Spdefragallindex 
AS 
  BEGIN 
      DECLARE @TableName VARCHAR(255) 
      DECLARE tablecursor CURSOR FOR 
        (SELECT '[' + IST.table_schema + '].[' + IST.table_name 
                + ']' AS [TableName] 
         FROM   information_schema.tables IST 
         WHERE  IST.table_type = 'BASE TABLE') 

      OPEN tablecursor 

      FETCH next FROM tablecursor INTO @TableName 

      WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT( 'Rebuilding Indexes on ' + @TableName ) 

            BEGIN try 
                EXEC('ALTER INDEX ALL ON ' + @TableName + 
                ' REBUILD with (ONLINE=ON)') 
            --PRINT 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)' 
            END try 

            BEGIN catch 
                PRINT( 'Rebuild with Online=On ' + @TableName 
                       + ' UnSuccessful, removing ONLINE' ) 

                EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') 
            END catch 

            FETCH next FROM tablecursor INTO @TableName 
        END 

      CLOSE tablecursor 

      DEALLOCATE tablecursor 

      SELECT Cast(1 AS INT) AS [Success] 
  END 

As we are not in production, I'm not using a selective table list based on average index defragmentation. You can change this script as you please, suited to your needs.

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC

Nenhum comentário:

Postar um comentário

Leave your comment here!