Emmanuel's Blog

Update 9/1: George Huey released an amazing tool, the “SQL Azure Migration Wizard”!  Here’s more info:

  SQL Azure Migration Wizard – Information and Screencast
  http://blog.wadewegner.com/index.php/2009/09/01/sql-azure-migration-wizard/

  SQL Azure Migration Wizard – Source Code and Binaries
  http://sqlazuremw.codeplex.com/

Good times!

A couple of days ago, I published my notes on what steps need to be taken to clean up a SQL script generated on an on-premise SQL Server 2008 so it can run on SQL Azure.

As I started creating a real schema with primary keys, indexes, foreign keys, Stored Procedures, etc…  I realized it was a pain to manually have to clean up these scripts each time I wanted to update my SQL Azure database.

I thought of a few solutions and decided to go with a fast and easy way to clean up the SQL scripts using Windows PowerShell.  Here’s the content of my CleanUpScriptForSQLAzure.ps1 script:

function Replace-String($find, $replace, $path) 
{ 
    echo "Replacing string `"$find`" with string `"$replace`" in file contents and file names of path: $path" 
    ls $path | select-string $find -list |% { echo "Processing contents of $($_.Path)"; (get-content $_.Path) |% { $_ -replace $find, $replace } | set-content $_.Path -Force } 
    ls $path\*$find* |% { echo "Renaming $($_.FullName) to $($_.FullName.Replace($find, $replace))";mv $_.FullName $_.FullName.Replace($find, $replace) } 
} 

# After generating a script with SSMS we need to clean it up before running it on SQL Azure 

Replace-String "SET ANSI_NULLS ON" "" "c:\data\sqlazurescripts" 

Replace-String "SET ANSI_NULLS OFF" "" "c:\data\sqlazurescripts" 

Replace-String "SET ANSI_PADDING ON" "" "c:\data\sqlazurescripts" 

Replace-String "SET ANSI_PADDING OFF" "" "c:\data\sqlazurescripts" 

Replace-String "ON \[PRIMARY\]" "" "c:\data\sqlazurescripts" 

Replace-String "\(PAD_INDEX  = OFF," "(" "c:\data\sqlazurescripts" 

Replace-String ",PAD_INDEX  = OFF" "" "c:\data\sqlazurescripts" 

Replace-String ", PAD_INDEX  = OFF" "" "c:\data\sqlazurescripts" 

Replace-String "PAD_INDEX  = OFF" "" "c:\data\sqlazurescripts" 

Replace-String ",SORT_IN_TEMPDB = OFF" "" "c:\data\sqlazurescripts" 

Replace-String ", SORT_IN_TEMPDB = OFF" "" "c:\data\sqlazurescripts" 

Replace-String "SORT_IN_TEMPDB = OFF" "" "c:\data\sqlazurescripts" 

Replace-String ",ALLOW_ROW_LOCKS  = ON" "" "c:\data\sqlazurescripts" 

Replace-String ", ALLOW_ROW_LOCKS  = ON" "" "c:\data\sqlazurescripts" 

Replace-String "ALLOW_ROW_LOCKS  = ON" "" "c:\data\sqlazurescripts" 

Replace-String ",ALLOW_PAGE_LOCKS  = ON" "" "c:\data\sqlazurescripts" 

Replace-String ", ALLOW_PAGE_LOCKS  = ON" "" "c:\data\sqlazurescripts"

Replace-String "ALLOW_PAGE_LOCKS  = ON" "" "c:\data\sqlazurescripts"

To use the above script, generate the SQL scripts using SQL Server Management Studio pointing to an on-premise SQL Server and save the scripts in the “c:\data\sqlazurescripts” folder (or any other folder you prefer).

Then run CleanUpScriptForSQLAzure.ps1 and you should see something like this:

I have found that this PowerShell script cleans up all of the scripts I’ve generated so far – including both scripts to generate schema and data, and scripts to create the stored procedures.  But there are some commands not yet included in the above script (see my published notes for more details).

Until Microsoft gives us a better tool we can use these PowerShell commands to clean up the SQL scripts.  You may also want to check out the SQL Azure Manager CTP on hanssens.org.

Final note: if you get errors executing the CleanUpScriptForSQLAzure.ps1 script in Powershell, you will need to update your execution policy.  Here’s a good page with pointers on how to do that:

  Introduction to Windows PowerShell Cmdlets
  http://computerperformance.co.uk/powershell/powershell_cmdlet.htm

Setting “ExecutionPolicy = RemoteSigned” in HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell worked for me.