Tuesday, 18 April 2017

What happens if your key developer goes under a bus?

OK, a little morbid I know, but it is a question I am asked on far too regular a basis. "What happens if you leave or get in an accident?" Technically it's called key person risk and is the reason you have teams, good documentation and source code control with non-stop backups. 

Technically I have only left a project twice before I wanted to and before a convenient delivery point. 
  • Large public sector integrated web site, knowledge-base and licensing site. I left 1 month before go-live as the contract renewal negotiations broke down. The site eventually went live 18 months after I left.
  • Geospatial data management system - I left when the scrum master decided they knew more than the architect, about a month before an on target delivery. System still not live 1 year later.

The reality is that you don't survive key person risk easily, it's a huge nightmare and usually puts you back about a year. How to avoid it? Have a happy team that communicates and try not to let too much jealousy creep into negotiations.

Story time:
Once upon a time a large digital consultancy was hired by a multi-billion dollar computer chip manufacturer to create an online game. The brief was that the game should have sufficient difficulty that at the end of 8 weeks a winner could be declared and presented with an expensive gaming laptop. The rules of the game were defined by the sales manager as a flying game where the player had to pass various obstacles, the complexity and difficulty of which were beyond human reaction times until the last few weeks of the game.

The project was delayed through negotiations and when it was eventually agreed it just so happened that the star developer who helped the Sales team was on a vacation, his first in 3 years. The sales team decided to hire an external consultancy that they knew and had used before to undertake the work, they did not engage with the in-house engineering team, preferring to keep all the details in one place.

When the star developer returned he was reserved to undertake due diligence on the progress from the external consultancy (part of the ISO27001 quality assurance the agency had.) He asked for the brief, the backlog, the progress against plan and a login to the code repository. It turned out there were a few missing items, notably:
  • There was no brief (it was verbal and conducted in 'brainstorming' sessions)
  • There was no backlog (The sales team simply reviewed the prototypes)
  • There was no plan (The sales team simply set a deadline)
  • There was no code repository (The developers did everything on their laptops)
There was however a working prototype and a copy of the code from 1 week previously. The prototype would work as long as you did not use the controls, it was an early prototype demonstrating the visuals of the game.

The Star Developer called a meeting with the sales team and expressed his concern, raising warning flags that in his opinion they were unlikely to meet the deadline some 6 weeks away, however the sales team had confidence in the external consultancy as the lead salesman only lived down the road from the consultancy, which was run from a converted Victorian house in his street. The Star Developer asked to be included in the next review at the end of the week and asked that the consultancy be informed that a code review was required.

The end of the week came and the external consultancy had to cancel the meeting due to unforeseen circumstances, the following week came and went with no submissions. The sales team where not worried, the prototype was nearly there weeks ago, and there were 4 weeks to the deadline, it wouldn't take long to test surely?

The in-house testers had not written a test plan as there was no brief, after a 1 week of discovery they estimated 2 weeks to write the scenarios and 4 man weeks to test them.

The Head of development called a crisis meeting, attending were:
  • 2 of the sales team
  • A representative from the external consultancy
  • The Star developer
  • The Lead tester
  • The Head of development
At the crisis meeting the external consultancy relayed a sad story. I turned out that their lead developer had been involved in a road traffic accident, he had been hit by a bus whilst cycling to work.

Not to worry, the Star developer was now available, if the consultancy simply supplied the code completed to date, they would complete the project. The external consultancy explained again that their lead developer had been involved in an accident, he was still in hospital.

The Star developer explained that he would be able to continue the work, all he needed was the code completed to date, the external consultancy explained that their lead developer was travelling to work with his laptop, which was destroyed in the accident. The laptop contained the only complete copy of the code.

The development was brought back in house and an attempt was made to continue from the submitted prototype, it turned out that the game parameters had not been documented or understood; if the user simply shook the mouse long enough then they would win the game. The entirety of the codebase had to be thrown away.

The conclusion of the engagement was that the entire project had to start again, the sales team negotiated that the project was put back 3 months and the competition was completed, the engagement still made a small profit but the client rolled future engagements over due to their standard policy of re-competing suppliers every 2 years.

The lessons learnt are numerous and may well be longer that the story, but it does go to prove that if you say "What happens if the developer goes under a bus?" You may get more than you wished for.

Thursday, 13 April 2017

Top 10 tips for Software Development

During a kickoff meeting I was once asked what my Top 10 tips to running a software development project were. Really it's an impossible question, many people have written many books on the subject and made far more money than me in doing so. However here are my current Top Ten Tips for Software Development!

  1. Keep it simple Stupid (KISS) or use Occam's Razor (If there are two explanations for an occurrence, the simpler one is usually better)
    Occam was a 14th century friar who studied logic. The lesson here is, everyone thinks they know better and thinks they thought of it first, but the reality is often much simpler!  
  2. Everyone thinks they have the new greatest idea that no-one has ever thought of before.
    Humour them (See tip 1)
  3. Pick one set of tools and stick to them.
    Ideally everyone should use the same set of tools and there should be as few of them as possible (See tip 1)
  4. A little planning can avoid much waste (James Coplien - Lean Architecture)
    The tip here is don't plan too much, plan enough to get on with the job. Planning too far ahead makes for many assumptions and gets too complicated (See tip 1)
  5. No Battle Plan ever survives contact with the enemy (Helmuth von Moltke the Elder)
    Often misquoted as Dwight David Eisenhower, Napoleon and George Patton who all thought they said it first (See tip 2)
    Basically, don't expect your plan to work as intended (See Tip 1) Even if you think you are special and it will work for you (See tip 2)
  6. Have a plan *before* you start work.
    Making it up as you go can be fun, but is ultimately doomed.
    See this 1997 Dilbert cartoon
    http://dilbert.com/strip/1997-05-09 (See Tip 2)
  7. Success is inversely proportional to the number of people on the committee responsible for it
    There must be a leader and that person must take responsibility for their decisions (See Tip 1)
  8. A developer tests what works, a tester finds what doesn't work
    Or "Never let a developer test their own code" (See Tip 1)
    There is much written about TDD, Unit testing and automated testing and much debate about what works, ultimately if you change something - test it.
  9. Don't be afraid to ask stupid questions
    If you didn't understand what is being said, there is a high likelihood that nobody else did either (See Tip 1)
  10. The second law of thermodynamics states that the total entropy of an isolated system can only increase over time.
    Murphy simplified this to "Anything that can go wrong will go wrong" (See Tip 1)
    There are 10 basic Murphys laws
    1. Anything that can go wrong will go wrong.
    2. Left to themselves, things tend to go from bad to worse
    3. Nothing is as easy as it looks.
    4. Everything takes longer than you think.
    5. If there is a possibility of several things going wrong, the one that will cause the most damage will be the one to go wrong.
      1. Corollary: If there is a worse time for something to go wrong, it will happen then.
    6. If anything simply cannot go wrong, it will anyway.
    7. If you perceive that there are four possible ways in which a procedure can go wrong, and circumvent these, then a fifth way, unprepared for, will promptly develop.
    8. If everything seems to be going well, you have obviously overlooked something.
    9. Whenever you set out to do something, something else must be done first.
    10. Every solution breeds new problems.
Smith's Law: "Murphy was an optimist."

Others tips that didn't make the Top 10:
  • Deadlines are always closer than they appear
    • 20/20 hindsight is wonderful but useless
    • To quote one of the greatest thinkers of our time: “I love deadlines. I love the whooshing noise they make as they go by.”
    • Approach Project retrospectives with a sense of humour and remember that nobody will remember them in the morning
  • Developers will always estimate too low
    • Sales always take the developers most optimistic estimate and half it to please the customer
    • Agile/scrum says story points are impartial, this is called an excuse
    • 1 story point == 1/2 day
  • There is never enough coffee
    • Every hour you work over 8 hours in a day means 10 minutes rework the next day
    • Working more than 14 hours in a day means everything you are doing is pointless
    • When the excrement hits the air movement device stay late and sort it out
    • Make sure the person responsible for the excrement stays as well
  • Sometimes there is a reason nobody has ever done it before
    • Copying other people's work is fine as long as you use the word paradigm in the explanation
    • History repeats itself (See Tip 2)
  • Noisy teams get work done
    • A quiet team is a team too nervous to say they are wrong
    • Conflict is good as long as it is resolved
  • The customer is never wrong
    • The customer may need guidance
  • "Agile" means different things to different people
    • Take a certified scrum master course, it not just for people who have nothing better to do and it means you will be able to argue from an informed perspective with the Agile Zealot on your team
    • There is always an Agile Zealot on your team who says what you are doing is not "Agile" or "Scrum" - Fire them immediately
    • If somebody at the daily agile stand-up is always 'refactoring' or in a 'Spike' - Fire them immediately
    • If somebody in the Agile team quotes the YAGNI principle - Fire them immediately
    • If somebody says you shouldn't measure Agile - Fire them immediately
    • Agile does not mean
      • Quicker
      • Cheaper
      • You can do the requirements later
      • You don't need to be involved
      • The first version works
  • Beware of Jargon
    • Playing buzzword Bingo during meetings can be fun, but be aware of your audience and give out the prizes after the meeting
    • Always learn the latest Jargon
    • RTFM
  • Garbage in, Garbage out.
  • There is a bus roaming the streets of London waiting to run over your team

Wednesday, 16 March 2016

Plug and Play NMEA

For some time now I have been looking at how I get access navigational data for use in my spatial development. That sounds awfully grand, let's try that again!

There are instruments all over modern boats; wind speed, wind direction, depth, vessel heading, location even other vessel locations, all available over a few different communications systems with the title NMEA - Which actually stands for National Marine Electronics Association, so strictly speaking NMEA is not a type of communication, in the same way the Hoover is not is not a vacuum cleaner. It turns out the NMEA set standards for communications. At the time of writing there are essentially 3:
  • NMEA 0183 - V1 The original standard, basically RS232 at 4800 baud
  • NMEA 0183 - V4 The last of the Serial standards working at 38000 baud
  • NMEA 2000 - Essentially CAN bus
The NMEA standard does lots more than just set the protocol, it all defines the messages (or sentences) that are sent over the wire

If you have played around with serial communications before you soon realise that you can only really connect one serial device to another, you can't connect lots; as they will all talk over each other. So either you have devices with lots of serial ports (often you find displays on boats with a few inputs for NMEA) or you have instruments that daisy-chain together to pass the messages along or… you get a multiplexer.

A multiplexer acts like a hub, grouping all the serial messages together and passing them down a single port to be read by your down-stream devices, they are very clever as they buffer all the messages and group them together. However this has some problems as well.

Typically 4800 baud is fine for a single instrument, 4800 bits per second, or 600 8 bit words per second should be enough for a spinning wind vane, even with direction information. Except of course it's not just the data being sent, it's also the tags saying what sort of data. Even if that is only a few letters (e.g. HDG for heading and 180) you are talking 6 letters, plus start, end and error correction. Suddenly you are looking at tens of messages per second. A 10hz GPS updates 10 times per second for location, direction and speed. That on its own could saturate a single NMEA connection. What happens if you have many of these or are transmitting more than 1 instrument? Well the NMEA redefined things and allowed 38000 baud - or roughly 500 messages per second, which is a lot better. But you still have issues connection lots of things together and even then you can use up 500 messages fast!

CAN bus is a relatively new boy in town, it actually comes from the automotive industry as a fault tolerant, high speed communication solution for cars. The idea being you don't need to run a wire for each of the little computers in a car, you just daisy chain them all together and they will cooperate. It's a bit more complicated than that, as it is still a serial communication bus, just a more intelligent one running at up to 1mb per second - or about 128,000 messages per second. That is more like it! You only need one wire between all the instruments and you can have as many receivers or transmitters as you like!

So we have lots of ways to get the instruments to talk together, which one shall we use? Well, there is the quandary; NMEA 0183 has been around the longest and is arguably the easiest to implement, so "Low cost" instruments all use it. NMEA 2000 is obviously the best and easiest to use from user point of view, but it costs more to implement it in the electronics, so typically what you end up with is a mix of all of the protocols:
  • NMEA 0183 @ 4800 baud for depth sounders, logs, autopilot maybe GPS
  • NMEA 0183 @ 38000 baud for AIS and maybe GPS with heading sensors
  • NMEA 2000 for AIS, GPS, autopilot and modern plotters

Anyone who has tried to work with this knows how notorious NMEA 0183 can be for just not working, so I was not looking forward to trying to harvest data from all the disparate busses (busi? Bus'?)

Step up to the mark Actisense they have a range of multiplexers and NMEA bridges that link all this stuff together. I got myself an NDC-4 which lets you plug 4 NMEA 0183 devices together (configurable to 4800 or 38000 with message filtering and priority) + a USB port - which looks like a serial port to any PC. Then I got an NGW-1 which lets you communicate bidirectionally between NMEA 0183 and NMEA 2000. So I can plug the USB into almost any NMEA port. Admittedly I really ought to get the NGT-1 which plugs NMEA 2000 into USB, but all the test software I could get only really talks NMEA 0183 messages, the interface to NMEA 2000 has an SDK and has potential to be fast, but odd. So First things first, prove I can wire all the stuff together and get data!

On my desk I am looking at half a dozen flashing LEDs. I bought a NASA AIS receiver and a B&G GPS and heading sensor - So Location, speed, heading and AIS Data (AIS tells you about other vessels within VHF range at 38000 baud.) The B&G GPS is NMEA 2000 and the AIS is NMEA 0183. So I simply connected the NGW-1 (NMEA 2000 -> NMEA 0183) to the NDC-4 (NMEA Multiplexer) the instructions are in the box with lots of pictures. Remembering that transmit wires on one device connect to receive wires on the other. Then connected the power to the NMEA bus and the NDC, plugged the GPS into the NMEA 2000 bus (plus the terminators and power) and finally plugged a USB cable from the dedicated connector in the NDC-4 into my Windows 10 PC - which automatically detects the NDC as a serial port!

How do you test NMEA networks? Well there are lots of flashing LED's going on here. The handbooks tell me that there is data being exchanged. I can even put a serial port monitor on and capture text flying over the serial ports. But by far the easiest way is to run something that understands these messages. The obvious choice would be OpenCPN, an open source chart viewer with various plug-ins… Unfortunately I have never really had the patience to get on well with OpenCPN, it doesn't choose it's scale band well and the way it renders annoys me intensely. It also suffers from the traditional open-source malaise of only having the sexy bits complete. The hard bits, like declutter, error trapping and reliable import/export all have annoying issues. So I tend to fall back on a reliable (and inexpensive) paid for product, Nuno Navigator.

Nuno Navigator is written by Chersoft who also wrote the digital versions of the UK Hydrographic Office (otherwise known as the Admiralty)  SOLAS Books for radio signals, list of lights, tides and safety publications. That includes not only the user products (known as ADP and Total Tides) but also the systems used internally to manage the products, with an additional layer to license them. Underneath the Admiralty products is a very flexible rendering engine that they also use in their Leisure products - Like Nuno Navigator. I used to work for the UKHO and I have a lot of respect for Chersoft (and more importantly the guys that own and run it!) as people who not only understand their products, but also how they are used by sailors.

Needless to say I quite like Nuno Navigator. I started it up after I had plugged in the NMEA spiders-nest I had created and within a few seconds voila, I had location data and AIS data as an overlay on the chart for Southampton. This all worked first time, no trial and error - just plug and play!

The next step is to see if Signal K (an open source restful interface for marine data) is viable and allows me to do the kind of spatial logging I have planned… But that is for a later blog!

So what is the moral of this rambling blog post?
  • Using products like Actisense NDC-4, NMEA really is now plug and play, in fact it's easy!
  • Nuno Navigator just gets more and more impressive! 

Tuesday, 8 March 2016

So I Bought A Boat

Today I bought the third most expensive thing I have ever paid for… But it wasn't a house. It's funny, immediately after I say yes to any number with more than 3 zeros in it I always get that little pang of self-doubt. All the inertia leading up to the moment washes away and I am left on a little island of "You've done it now" but in this case it's a rather nice little island, with perhaps a palm tree or two.

It has been many years in the planning, a minimum of twenty years, probably closer to thirty, but I have always planned to retire, or at least stop living to work, as close to 50 as I could. I think my father first put the idea in my head and nearly every plan I have made since has involved having everything in place do that. The Mortgage will finish when I'm about 50 and the kids will all be over 18, that's the major things I suppose! I'm not 50 yet mind you, it's all in the planning.

My wife says I'm just trying to copy my dad, in a way I am; but only in the way that you would look at somebody putting their umbrella up and agree that putting on a coat might be a good idea. When my parents separated, my mother went off into Europe with my siblings, I stayed at home making a living and my father decided to buy a steel boat (from memory for about £10K) he then spent another £10K making it ocean going, then, with about £30K in the bank he set off around the world, going sort of the wrong way through the med. He never got all the way around, but he learnt a lot (some of which he told me) and he had a right giggle on the way.

Today I took my own view on my father's bright idea, I bought a second hand Ocean going cruising yacht. My take is I bought one that you could set off in immediately. I'm not going to (set off immediately that is) but I am going to use it as a base to live in whilst I work as a solution architect for Ordnance Survey in Southampton. The idea is I learn all the foibles in a nice safe mooring and get to implement all the bright ideas I've had in an environment that won't kill me! In the meantime I have a place to live very cheaply near my work place (I actually live in North Devon.)

The boat I bought is a Steel Callisto 385, I'll cover why Steel is about the best material in later posts, take it from me it is. This particular boat seems to have been owned by somebody who knew what they were doing, it's actually very difficult to find anything wrong with it; apart from being 19 years old, the previous owners meticulously took care of it, possibly too well, so everything is as original. The standing rigging for instance is original, it is in beautiful condition and fantastically well specified. But it is 19 years old. Really you should change the rig every 10 to 15 years, but the owner took such good care of it you wouldn't be able to tell it from a 5 year old rig unless you looked at the records - that may come back to bite me if the insurance company insists on a set age for the rig, or indeed just one piece has in fact worn.

The Yacht is a Bill Dixon design, built by Croft Marine. The builders went bust in the late 90's because, well, everyone was going bust then; particularly those in the luxury sector. Bill Dixon to this day is one of the worlds most respected designers. The boat itself is fantastically pretty, a round bilge design (meaning it has a smooth rounded hull, rather than the plate chines you normally get with steel.) I may actually have stumbled upon an absolute gem of a boat though, when they built the hull it was sprayed with molten aluminium inside and out, kind of like a high class galvanised coating that heals itself if it gets scratched. We could hardly find any rust on the boat at all! All over the boat everything is extremely well specified if not over specified, from water management to pumps to sails to cupboard space to cookers to heating. In fact we found it very difficult to fault the build in any way! The only areas of doubt on the whole boat were:
  • The teak decks - which had been cleaned so often they were getting too thin
  • The Sole plates (floor boards) - which for some reason were finished with linoleum rather that a nice wood
  • The instruments - which were fairly basic, I suspect because the owner actually knew how to sail properly
  • The Main in-mast furling - which can be troublesome or joyful; but are unloved by cruisers
Overall though nothing would stop you from just getting on the boat and sailing into the sunrise (must resist just a bit longer!)

Paul Fay (www.faymarine.com) helped me do an inspection after my initial viewing, he confirmed my view with proper evidence of the quality of the boat and he will be doing the full survey. Have a look at his site, he has more experience of self builds and steel boats than I care to think about and is massively pragmatic and practical. I would trust my life with him on a boat, indeed I kind of have!

I will write more as my journey progresses, in the meantime Aquamarine of Beaulieu is waiting for me to take ownership. It will be an interesting voyage.

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: