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:
- Backup / Take a snapshot
- Upgrade the data warehouse
- 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.
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