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!