Azure, data warehouse, Random Inno, SQL

Upgrading The Azure Data warehouse To Gen2

The Data warehouse has recently been upgraded to the next version called Gen2. The good news is that the upgrade process is relatively seamless.

Upgrading is supposed to be a one step process but I have decided to add 2 extra steps:

  1. Backup / Take a snapshot
  2. Upgrade the data warehouse
  3. Rebuild all indexes

Backup / Take s snapshot

PS /Users/piro>New-AzureRmSqlDatabaseRestorePoint -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -RestorePointLabel "RestorePoint01"

For more information refer to New-AzureRmSqlDatabaseRestorePoint.

Upgrade the data warehouse

At present the only way to upgrade is to use the portal. To upgrade login into the portal and navigate the Azure SQL database. A new option to upgrade will be available in the database overview blade.

Screen Shot 2018-09-20 at 22.44.59.png

Rebuild all indexes


IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
CREATE TABLE #tables
WITH(DISTRIBUTION = ROUND_ROBIN, HEAP)
AS
SELECT s.name AS table_schema, t.name AS table_name FROM [sys].[tables] t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.[is_external] = 0;

DECLARE @tableName VARCHAR(MAX)
DECLARE @schemaName VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

WHILE EXISTS (SELECT 1 FROM #tables)
BEGIN
SET @tableName = (SELECT TOP(1) table_name FROM #tables ORDER BY table_schema ASC, table_name ASC)
SET @schemaName = (SELECT TOP(1) table_schema FROM #tables ORDER BY table_schema ASC, table_name ASC)
SET @sql = 'ALTER INDEX ALL ON [' + @schemaName + '].['+ @tableName + '] REBUILD'
EXEC(@sql)
DELETE #tables WHERE table_name = @tableName and table_schema = @schemaName
END
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables

 

Link to GitHub Code, RebuildIndex.sql

More information here, Optimize performance by upgrading SQL Data Warehouse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s