Wednesday 12 August 2015

The joys of Azure Site and Database Migration

Many Many moons ago... In a coastal town far away... I ran an internet café. Three C's Internet Café (Which stood for Coffee, Cocktails & Computers. Nobody got it, even though it was written in foot high letters across the front of the building!) Three C's had a web site (www.threecs.net) which had many visitors!
I sold the internet café in 2010, they renamed it Karma Café and carried on in their own way. The website however continued, partly because I was lazy, partly because it still had many many visitors!
I decided today to migrate it off the old Rackspace hosting to Azure, this should be easy... or so I thought.
The Three C's website is very old-fashioned, a frames site using classic ASP to a little SQL Server Express database (migrated from Access) with a few JavaScript tricks and ASP hacks. Webmatrix let me migrate the site easily enough (whilst not telling me it was ignoring some of the web.config settings!) then came the joy of migrating the database.
As with all proper sites, the database server backs itself up every night (I have a series of very useful SQL Express automated backup scripts.) Normally to migrate servers I would restore one of these scripts to the server, reconnect the users and everything would be good.
Failure #1 - You cant restore a SQL database backup to Azure (bummer)
OK, try connecting from the the server using SQL Management Studio, success! Ah, but half the menu items are missing (mostly around data exporting and backups) and of course the version on my web server is SQL 2008 R2 Express, you need 2012 to get to all the toys.
Failure #2 - You need SQL Server 2012 to use all the toys.
OK, I have SQL 2012 on my local PC, restore the database locally an deploy/export it from there. Easy... Except it wont deploy, SQL Azure must have a clustered index on all tables, well it is more efficient, but it wasn't a requirement with on-prem SQL Server.
Failure #3 - You need clustered indexes on all tables with SQL Azure
This actually took me a while to solve, many solutions presented themselves.
  • Exporting the data instead (same clustered index problem)
  • Creating the tables with scripts, edited to have clustered indexes
Failure #4 - you cant have disk references in your scripts (Azure doesn't have disks in the same way as on-prem SQL Server)
  • Create the tables with scripts, modifying the Primary Keys to be clustered in scripting, then export the data with identities switched off
Failure #5 - Importing data can muck up your referential integrity (Which I guessed, I was hoping that switching off identities would help... nope!)
Inevitably I had been a good boy and used proper referential integrity. Yes this complicates the database, no I didn't want to switch it off.
  • Fix the database locally (making clustered indexes) then use the SQL 2012 Deployment wizard
Failure #6 - Exporting from access creates some read only columns (my own fault)
  • Delete the Access specific fields
Failure #7 - SQL Azure doesn't like Extended properties

It would appear that the combination of upgrading from Access to SQL Server, and then updating whenever a new version came out (in combination to masochistic tendencies) creates many extended properties on a database, in many different places, all of which need deleting :(
I found a query online that would create another set of queries to delete the extended properties:

/* This script will generate calls to sp_dropextendedproperty for every extended property that exists in your database. Actually, a caveat: I don't promise that it will catch each and every extended property that exists, but I'm confident it will catch most of them! It is based on this: http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/ by Angelo Hongens. Also had lots of help from this: http://www.sqlservercentral.com/articles/Metadata/72609/ by Adam Aspin Adam actually provides a script at that link to do something very similar but when I ran it I got an error: Msg 468, Level 16, State 9, Line 78 Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation. So I put together this version instead. Use at your own risk. Jamie Thomson 2012-03-25 */ /*Are there any extended properties? Let's take a look*/ select *,OBJECT_NAME(major_id) from sys.extended_properties xp /*Now let's generate sp_dropextendedproperty statements for all of them.*/ --tables set nocount on; select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''table'' ,@level1name = ''' + object_name(xp.major_id) + '''' from sys.extended_properties xp join sys.tables t on xp.major_id = t.object_id where xp.class_desc = 'OBJECT_OR_COLUMN' and xp.minor_id = 0 union --columns select 'EXEC sp_dropextendedproperty @name = '''+sys.extended_properties.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(extended_properties.major_id) + ''' ,@level1type = ''table'' ,@level1name = ''' + object_name(extended_properties.major_id) + ''' ,@level2type = ''column'' ,@level2name = ''' + columns.name + '''' from sys.extended_properties join sys.columns on columns.object_id = extended_properties.major_id and columns.column_id = extended_properties.minor_id where extended_properties.class_desc = 'OBJECT_OR_COLUMN' and extended_properties.minor_id > 0 union --check constraints select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''table'' ,@level1name = ''' + object_name(cc.parent_object_id) + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' from sys.extended_properties xp join sys.check_constraints cc on xp.major_id = cc.object_id union --check constraints select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''table'' ,@level1name = ''' + object_name(cc.parent_object_id) + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' from sys.extended_properties xp join sys.default_constraints cc on xp.major_id = cc.object_id union --views select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''view'' ,@level1name = ''' + object_name(xp.major_id) + '''' from sys.extended_properties xp join sys.views t on xp.major_id = t.object_id where xp.class_desc = 'OBJECT_OR_COLUMN' and xp.minor_id = 0 union --sprocs select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''procedure'' ,@level1name = ''' + object_name(xp.major_id) + '''' from sys.extended_properties xp join sys.procedures t on xp.major_id = t.object_id where xp.class_desc = 'OBJECT_OR_COLUMN' and xp.minor_id = 0 union --FKs select 'EXEC sp_dropextendedproperty @name = '''+xp.name+''' ,@level0type = ''schema'' ,@level0name = ''' + object_schema_name(xp.major_id) + ''' ,@level1type = ''table'' ,@level1name = ''' + object_name(cc.parent_object_id) + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' from sys.extended_properties xp join sys.foreign_keys cc on xp.major_id = cc.object_id union --PKs SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SKC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.tables TBL INNER JOIN sys.schemas SCH ON TBL.schema_id = SCH.schema_id INNER JOIN sys.extended_properties SEP INNER JOIN sys.key_constraints SKC ON SEP.major_id = SKC.object_id ON TBL.object_id = SKC.parent_object_id WHERE SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT' union --Table triggers SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''TRIGGER'', @level2name = [' + TRG.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.tables TBL INNER JOIN sys.triggers TRG ON TBL.object_id = TRG.parent_id INNER JOIN sys.extended_properties SEP ON TRG.object_id = SEP.major_id INNER JOIN sys.schemas SCH ON TBL.schema_id = SCH.schema_id union --UDF params SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP INNER JOIN sys.objects OBJ ON SEP.major_id = OBJ.object_id INNER JOIN sys.schemas SCH ON OBJ.schema_id = SCH.schema_id INNER JOIN sys.parameters PRM ON SEP.major_id = PRM.object_id AND SEP.minor_id = PRM.parameter_id WHERE SEP.class_desc = N'PARAMETER' AND OBJ.type IN ('FN', 'IF', 'TF') union --sp params SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''PROCEDURE'', @level1name = [' + SPR.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP INNER JOIN sys.procedures SPR ON SEP.major_id = SPR.object_id INNER JOIN sys.schemas SCH ON SPR.schema_id = SCH.schema_id INNER JOIN sys.parameters PRM ON SEP.major_id = PRM.object_id AND SEP.minor_id = PRM.parameter_id WHERE SEP.class_desc = N'PARAMETER' union --DB SELECT 'EXEC sys.sp_dropextendedproperty @name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP WHERE class_desc = N'DATABASE' union --schema SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP INNER JOIN sys.schemas SCH ON SEP.major_id = SCH.schema_id WHERE SEP.class_desc = N'SCHEMA' union --DATABASE_FILE SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + DBF.name + ' ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP INNER JOIN sys.database_files DBF ON SEP.major_id = DBF.file_id INNER JOIN sys.data_spaces DSP ON DBF.data_space_id = DSP.data_space_id WHERE SEP.class_desc = N'DATABASE_FILE' union --filegroup SELECT 'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + '''' FROM sys.extended_properties SEP INNER JOIN sys.data_spaces DSP ON SEP.major_id = DSP.data_space_id WHERE DSP.type_desc = 'ROWS_FILEGROUP'

It returns a series of rows that you simply copy and paste into another query window then run, which deletes all the extended properties.

Finally, run the deployment wizard - preferably with your fingers crossed!

It does run, I now have my cocktails database installed on a free instance of SQL Azure, and it is very quick! It was also very painful, I probably would have been better off re-writing from scratch and importing the data as cut and paste for this small data-set. But I've done it now, I've recorded it here and I can now do it again... Right up until Microsoft change their minds on how it works!

Wednesday 25 February 2015

Numeric characters in circles

Having searched the web for ages (and found lots of conflicting posts) I decided to document the [ALT] key sequences for alphanumeric character enclosed in a circle:

9312
9313
9314
9315
9316
9317
9318
9319
9320
9321
9322
9323
9324
9325
9326
9327
9328
9329
9330
9331
9332
9333
9334
9335
9336
9337
9338
9339
9340
9341
9342
9343
9344
9345
9346
9347
9348
9349
9350
9351
9352
9353
9354
9355
9356
9357
9358
9359
9360
9361
9362
9363
9364
9365
9366
9367
9368
9369
9370
9371
9372
9373
9374
9375
9376
9377
9378
9379
9380
9381
9382
9383
9384
9385
9386
9387
9388
9389
9390
9391
9392
9393
9394
9395
9396
9397
9398
9399
9400
9401
9402
9403
9404
9405
9406
9407
9408
9409
9410
9411
9412
9413
9414
9415
9416
9417
9418
9419
9420
9421
9422
9423
9424
9425
9426
9427
9428
9429
9430
9431
9432
9433
9434
9435
9436
9437
9438
9439
9440
9441
9442
9443
9444
9445
9446
9447
9448
9449
9450
9451
9452
9453
9454
9455
9456
9457
9458
9459
9460
9461
9462
9463
9464
9465
9466
9467
9468
9469
9470
9471