Some performance feedback and regex changes

Dec 21, 2010 at 12:23 AM

I would first like to add that this is a very useful tool and wonderful contribution to the community.  I just wanted to share some experience, usage information, and some minor issues I ran in to migrating a 2GB database with ~4000 tables.

1) DB_NAME() shows up as a migration issue and actually seems to work in SQL Azure today.

2) Select Field1, Field2, Field3, SpinTomato FROM FS180 throws raises a "SELECT INTO" migration issue.

3) As far as performance is concerned... I made it about 15% in to the analysis before I had to kill the tool due to performance.  After a few quick tweaks to the tool to write out to a file instead of appending to the textbox I was able to run through the analysis pretty quickly.  Also, I was unable to complete the migration of schema/data to SQL Azure from my desktop/server... nothing to do with the tool here, and I gave up after 4 hours.  With a larger database there is a HUGE performance benefit to completing this part from a VM-Role; I'm sure you could do by spinning up another temp role with TS as well if you don't have access to the CTP yet.  What took me giving up at 4+ hours and runnign took me less than 25min from an Azure VM role.

 

Coordinator
Jan 12, 2011 at 12:27 AM


Hi,

 

I just wanted to say thanks for the feedback on SQLAzureMW.  In regards to your items:

 

1)    DB_NAME() (and DB_ID()) both work in SQL Azure.  You are correct.  I removed it from NotSupportedByAzureFile.config and it will reflected in the next release.  For now, if you want, find the MetadataFunction tag and remove the second child with “"DB_(ID|NAME)\s*\” in it.

2)    Agreed.  I changed the regex in NotSupportedByAzureFile.config to force a space before INTO.  You can modify NotSupportedByAzureFile.config and look under GeneralTSQL (about 6 child down) and modify it to look like “SELECT[\w\W][^;]*\sINTO”.  This will also be in my next release.

3)    Yea.  I agree.  I had actually written this both ways (one to file and one to textbox) and found the same thing.  I choose writing to the textbox so people could see something happening and cancel if they wish.  I ran into the issue of writing to a textbox (or in this case rich textbox) a long time ago and there was a way to tell the textbox not to refresh (something … It has been so long I can’t remember) and remove the performance hit.  I will revisit this issue and see if I can come up with a good idea.

 

In regards to VM-Role or TS, you lost me.  Were you able to somehow make SQLAzureMW a non-interactive batch process?  I would love to understand what you did here.  Going from many hours to less than 25 min is quite awesome.

 

Thanks again for your feedback!

George