Restore SQL Azure Bacpac in local SQL SERVER

You can restore the bacpac file from a SQL Azure Database in a local SQL SERVER Deploy, using the Management Studio.

1. Just Right click on Connection > Databases and select "Import Data-tier application..."
2. Select "Next" on the introduction step.
3. Click Browse to locate the bacpac file
4. Alternatively, you can import from a bacpac file in the AZURE storage, but I wouldn't recommend that due to performance issues
5. Click Next

An you are all set


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

Error connecting Pentaho with Azure: "Server name cannot be determined. It must appear as the first segment of the server's dns name..."

I was creating a ETL package with Pentaho and surprisingly I got this message while connecting to SQL Azure: "Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match."

Reading the message closely, I decided to change the user name to a "user@servername" format, and that worked instantly.

This is unusual, given that a common SQL SERVER connection doesn't need that.

I'm using MS SQL SERVER (NATIVE) connection type (JDBC), and Pentaho 5.3.0.0-213.

Re-writing my usual defrag procedure for SQL AZURE

My usual IndexRebuild wouldnt work on SQL Azure for two reasons:

• Absence of sys.partitions
• SQL Azure doesn't take SELECT INTO (I was using that in my usual Rebuild Proc)

Working around that, I came up with this:

ALTER PROC [dbo].[Spdefragallindex] 
AS 
  BEGIN 
      CREATE TABLE #tmpfrag 
        ( 
           tbname VARCHAR(200) 
        ) 

      INSERT INTO #tmpfrag 
      SELECT TOP 20 Schema_name(o.schema_id) + '.' 
                    + Object_name(ps.object_id) AS TableName 
      FROM   sys.dm_db_partition_stats ps 
             INNER JOIN sys.indexes i 
                     ON ps.object_id = i.object_id 
                        AND ps.index_id = i.index_id 
             INNER JOIN sys.objects o 
                     ON ( i.object_id = o.object_id ) 
             CROSS apply sys.Dm_db_index_physical_stats(Db_id(), ps.object_id, 
                         ps.index_id, 
                         NULL, 
                         'LIMITED') ips 
      GROUP  BY Schema_name(o.schema_id) + '.' 
                + Object_name(ps.object_id) 
      ORDER  BY Avg(ips.avg_fragmentation_in_percent) DESC 

      DECLARE @TableName VARCHAR(255) 
      DECLARE tablecursor CURSOR FOR 
        (SELECT * 
         FROM   #tmpfrag) 

      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 

      DROP TABLE #tmpfrag 

      INSERT INTO logrebuild 
                  (desclogrebuild) 
      VALUES      ('Sucesso') 

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

go 

It was based on another blog post on MSDN: http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx

This is not the actual procedure I use for REBUILDING on SQL SERVER, that will be another post... :)

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

Tarefa de INDEX REBUILD no SQL AZURE

A partir da semana passada comecei a migração do banco de dados da Amazon RDS para Microsoft SQL Azure. Após a migração da estrutura do banco e alguns dados básicos para o teste (não estamos em produção ainda), precisei resolver algumas limitações do SQL Azure e um deles é a falta de SQL JOBS.

Para realizar um trabalho INDEX REBUILD, eu tive que usar as TAREFAS AUTOMATIZADAS do Microsoft Azure, usando um script baseado em PowerShell.

Este é o rascunho do que eu fiz:

workflow IndexRebuild 
{ 
     
    inlinescript { 
        # Define the connection to the SQL Database
        $server = "informeURLSQLAzure" 
        $db     = "informeNomedoBanco"
        $usr    = "informeUsuarioBanco"
        $psw    = "informeSenhaBanco"

        $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() 
    } 
}

O script acima é apenas para a tarefa. Abaixo o script da procedure de REBUILD que estou usando no 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 

Como não estamos em produção ainda, não estou usando uma lista de tabelas baseado no tamanho ou na média de fragmentação, o que seria mais adequado. Altere a procedure como for melhor a sua necessidade.

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

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