SQLBits 2016 In Review

IMG_20160507_072440On 7th May 2016 in Liverpool, the 15th annual SQLBits event took place in the new Liverpool Exhibition Centre.  The event had actually been running since Wednesday 4th, however, as with all other SQLBits events, the Saturday is a free, community day.

This particular SQLBits was rather special, as Microsoft had selected the event as the UK launch event for SQL Server 2016.  As such the entire conference had a very large Microsoft presence.

Since the event was in my home town, I didn’t have too far to travel to get to the venue.  That said, I did have to set my alarm for 6am (a full 45 minutes earlier than I usually do on a working weekday!) to ensure I could get the two different trains required to get me to the venue in good time.  The Saturday day is jam packed with content and as such, the event opened at the eye-watering time of 7:30am!

IMG_20160507_072440After arriving at the venue just as it was opening at 7:30am, I heading straight to the registration booth to confirm my registration and collect my conference lanyard.  Once collected, it was time to head into the main hall.  The theme for this years SQLBits was “SQLBits in Space” so the entire hall had the various rooms where the sessions would take place as giant inflatable white domes.  In between the domes and around the main hall there was plenty of space and sci-fi themed objects.

After a short while, the venue staff started to wheel out the morning refreshments of tea & coffee, shortly followed by the obligatory bacon, sausage and egg sandwiches!

After enjoying the delicious breakfast, it was soon time to head off the the relevant “dome” for the first session of the day.  The SQLBits Saturday event had 9 different tracks, so choosing what talk to attend was difficult and there was always bound to be clashes of interesting content throughout the day.  For the first session, I decided to attend Aaron Bertrand’s T-SQL: Bad Habits and Best Practices.

Aaron’s talk is all about the various bad habits that we can sometimes pick up when writing T-SQL code and also the myths that have built up around certain approaches to achieving specific things with T-SQL.  Aaron starts by stating that we should ensure that we don’t make blind assumptions about anything in SQL Server.  We can’t always say that a seek is better than a scan (or vice-versa) or that a clustered index is better than a non-clustered one.  It always depends.  The first big myth we encounter is that it’s often stated that using SELECT * when retrieving all columns from a database is bad practice (instead of naming all columns individually).  This can be bad practice as we don’t know exactly what columns we’ll be getting – e.g. future added columns will be returned in the query, however, it’s often stated that another reason it’s bad practice is due to SQL Server having to look up the database meta data to figure out the column names.  The reality is that SQL Server will do this anyway, even with named columns!

Next, Aaron shows us a little tip using SQL Server Management Studio.  It’s something that many audience members already knew, bit it was new to me. He showed how you can drag-and-drop the “Columns” node from the left-hand treeview into a query window and it will add a comma-separated list of all of the tables columns to the query text!

Aaron continues by warning us about omitting explicit lengths from varchar/nvarchar data types.  Without specifying explicit lengths, varchars can very easily be truncated to a single character as this simple T-SQL shows:

DECLARE @x VARCHAR = 'testing'; 
SELECT [myCol] = @x;

We’re told that we should always use the correct data types for our data!  This may seem obvious, but many times we see people storing dates as varchars (strings) simply to ensure they can preserve the exact formatting that they’re using.  This is a presentation concern, though, and doing this means we lose the ability to perform correct sorting and date arithmetic on the values.  Also, avoid using datatype such as MONEY simply because it sounds appropriate.  MONEY is a particularly bad example and should always be replaced with decimal

Aaron reminds us to always explicitly use a schema prefix when referencing tables and SQL Server objects within our queries (i.e. Use [dbo].[TableName] rather than just [TableName]).  Doing this ensure that, if two different users of our query have different default schemas, there won’t be any strange potential side-effects to our query.

We’re reminded not to abuse the ORDER BY clause.  Using ORDER BY with an Ordinal column number after it can easily break if columns are added, removed or their order in the schema altered.  Be aware of the myth that tables have a “natural order”, they don’t.  Omitting an ORDER BY clause may appear to order the data the same way each time, however, this can easily change if additional indexes are added to the table.

We should always use the SET NOCOUNT ON directive as this cut down on noisy chatter in our application’s communication with SQL Server, but make sure you always test this first.  Applications built using older technologies, such as the original ADO from the Classic ASP era can be reliant upon the additional count message being returned when NOCOUNT is off.

Next, Aaron highlights the cost of poorly written date / range queries.  He tells us that we shouldn’t use non-sargable expressions on a column – for example, if we use a WHERE clause which does something like WHERE YEAR([DateCoulmn]) = 2016, SQL Server will not be able to utilise any indexes that may exist on that column and will have to scan the entire table to compute the YEAR() function for the date column in question – a very expensive operation.  We’re told not use use the BETWEEN keyword as it’s imprecise – does BETWEEN include the boundary conditions or only everything between them?  It’s far better to explicitly use a greater than and less than clause for date ranges – e.g.  WHERE [OrderDate] > ‘1 Feb 2016’ AND [OrderDate] < '1 March 2016'. This ensures we’re not incorrectly including outlying boundary values (i.e. midnight on 28th Feb which is actually 1st March!).  Regarding dates, we should also be aware of date format strings.  Formatting a date with many date format strings can give entirely different values for different languages. The only two “safe” format strings which work the same across all languages are YYYYMMDD and the full ISO 8601 Date format string, “YYYY-MM-DDTHH:MM:SS”.

Aaron continues by reminding us to use the MERGE statement wisely.  We must remember that it effectively turns two statements into one, but this can potentially mess with triggers, especially if they rely on @@ROWCOUNT.  Next up is cursors.  We shouldn’t default to using a cursor if we can help it.  Sometimes, it can be difficult to think in set-based terms to avoid the cursor, but it’s worth the investment of time to see if some computation can be performed in a set-based way.  If you must use a cursor, it’s almost always best to apply the LOCAL FAST_FORWARD qualifier on our cursor definition as the vast majority of cursors we’ll use are “firehose” cursors (i.e. we iterate over each row of data once from start to end in a forward-only manner).  Remember that applying no options to the cursor definition effectively means the cursor is defined with the default options, which are rather “heavy-handed” and not always the most performant.

We’re reminded that we should always use sp_executesql when executing dynamic SQL rather than using the EXEC() statement.  sp_executesql allows the use of strongly-typed parameters (although unfortunately not for dynamic table or column names) which reduces the chances of SQL injection.  It’s not complete protection against injection attacks, but it’s better than nothing.  We’re also reminded not use to CASE or COALESCE in sub-queries.  COALESCE turns into a CASE statement within the query plan which means SQL Server will effectively evaluate the inner query twice.  Aaron asks that we remember to use semi-colons to separate our SQL statements.  It protects against future edits to the query/code and ensures atomic statements continue to operate in that way.

Aaron says that we should not abuse the COUNT() function.  We very often write code such as:

IF (SELECT COUNT(*) FROM [SomeTable]) > 0 THEN …..

when it’s really much more efficient to write:

IF EXISTS (SELECT 1 FROM [SomeTable]) THEN….

We don’t really need the count in the first query so there’s no reason to use it.  Moreover, if you do really need a table count, it’s much better to query the sys.partitions table to get the count:

-- Do this:
SELECT SUM(rows) FROM sys.partitions where index_id IN (0,1)
AND object_id = (SELECT object_id FROM sys.tables WHERE name = 'Addresses')
-- Instead of this:
SELECT COUNT(*) FROM Addresses

Aaron’s final two points are to ensure we don’t overuse the NOLOCK statement.  It’s a magic “go-faster stripes” turbo button for your query but it will produce inaccurate results.  This is fine if, for example, you only  need a “ballpark” row count, however, it’s almost always better to use a scope-levelled READ COMMITED SNAPSHOT isolation level for your query instead.  This must be tested, though, as this can place a heavy load on the tempdb.  Finally, we should remember to always wrap every query we do with a BEGIN TRANSACTION and a COMMIT/ROLLBACK transaction.  Remember – SQL Server doesn’t have an “undo” button!  And it’s perfectly fine to simply BEGIN a transaction when writing ad-hoc queries in SQL Server Management Studio, even if we don’t explicitly close it straight away.  The transaction will remain so long as the connection remains open, so we can always manually perform the commit or the rollback at a slightly later point in time.

And with that, Aaron’s session was over.  An excellent and informative start to the day.

IMG_20160507_073553After a coffee break, during which time there was some left over breakfast bacon sandwiches available for those people who fancied a second breakfast, it was time to head off to the next session.  For this one, I’d chosen something a little leftfield.  This wasn’t a session directly based upon technology, but rather was a session based upon employment within the field of technology.  This was Alex Whittle’s Permy, Contractor Or Freelance.

Alex’s session was focused on how we might get employed within the technology sector, the various options open to us in gaining meaningful employment and the pros and cons associated with each approach.

Alex starts his talk by introducing himself and talking us through his own career history so far.  He started as an employee developer, then a team lead and then director of software before branching out on his own to become a contractor.  From there, he became a freelancer and finally started his own consultancy company.

Alex talks about an employer’s expectations for the various types of working relationship.  For permanent employees, the focus is very much on your overall personality, attitude and ability to learn.  Employers are making a long term bet with a permanent employee.  For contractors, it’s your existing experience in a given technology or specific industry that will appeal most to the client.  They’re looking for someone who can deliver without needing any training “on-the-job” although you’ll get time to “figure it out” whilst you’re there.  You’ll also have “tech-level” conversations with your client, so largely avoiding the politics that can come with a permanent role.  Finally, as a freelancer, you’ll be engaged because of your technical expertise and your ability to deliver quickly.  You’re expected to be a business  expert too and you’re engagement will revolve around “senior management/CxO” level conversations with the client.

Alex moves on to discuss the various ways of marketing yourself based upon the working relationship.  For permanent employees its recruitment agencies, LinkedIn and keeping your CV up to date.  You’re main marketing point is your stability so you’re CV needs to show a list of jobs with good lengths of tenure for each one.  One or two shorter tenures is acceptable, but you’ll need to be able to potentially explain it well to a prospective employer.  For contractors, it’s much the same avenues for marketing, recruitment agencies, LinkedIn and a good CV, but here the focus is quite different.  Firstly, a contractor’s CV can be much longer than a permanent employee’s CV, which is usually limited to 3 pages.  A contractors CV can be up to 4-6 pages long and should highlight relevant technical and industry experience as well as show contract extensions and renewals (although older roles should be in summary only).  For freelancers, it’s not really about your CV at all.  Clients are now not interesting in you per-say, they’re interested in your company.  This is where company reputation and you’re ability to really sell the company itself has the biggest impact.  For all working relationships, one of the biggest factors is networking.  Networking will lead to contacts, which will lead to roles.  Never underestimate the power of simply speaking to people!

We then move on to talk about cash flow in the various types of working relationship.  Alex states how for permanent employees, there’s long term stability, holiday and sickness pay and also a pension.  It’s the “safest” and lowest stress option.  For contractors, cash flow has medium term stability.  There’s no holiday or sickness pay and you’d need to pay for your own pension.  You need to build a good cash buffer of at least 6 months living expenses, but you can probably get started on the contracting road with only 1 or 2 months of cash buffer.  Finally, the freelance option is the least “safe” and has the least stability of cash flow.  It’s often very “spiky” and can range of short periods of good income interspersed with longer periods of little or no income.  For this reason, it’s essential to build a cash buffer of at least 12 months living expenses, although the quieter income periods can be mitigated by taking on short term contracts.

Alex shares details on the time when he had quit his permanent job to go contracting.  He says he sent around 20-30 CV’s to various contract job per week for the first 3 weeks but didn’t get a single interview.  A helpful recruiter eventually told him that it was probably largely to do with the layout of his CV.  This recruiter spent 30 minutes with him on the phone, helping him to reformat his CV after which he sent out another 10 CV to various contract roles and got almost 10 interviews as a result!

We continue by looking into differences in accounting structures between the various working types.  As a permanent employee, there’s nothing to worry about at all here, it’s all sorted for you as a PAYE employee.  As a contractor, you’ll send out invoices usually once a month, but since you’ll rarely have more than one client at a time, the invoicing requirements are fairly simple.  You will need to do real-time PAYE returns as you’ll be both a director and employee of your Ltd. company and you’ll need to perform year-end tax returns and quarterly VAT returns, however, you can use the flat-rate VAT scheme if it’s applicable to you.  This can boost your income as you charge your clients VAT at 20% but only have to pay 14.5% to HMRC!  As a freelancer, you’ll also be sending out invoices, however, you may have more than one client at a time so you may have multiple invoices per month thereby requiring better management of them (such software as Xero or Quickbooks can help here).  One useful tip that Alex shares at this point is that, as a freelancer, it can be very beneficial to join the Federation of Small Businesses (FSB) as they can help to pay for things like tax investigations, should you ever receive one.

Alex then talks about how you can, as an independent contractor, either operate as a sole-trader, work for an umbrella company, or can run your own Limited company.  Limited company is usually the best route to go down as Limited companies are entirely separate legal entities so you’re more protected personally (although not from things like malpractice), however, the previous tax efficiency of company dividends that used to be enjoyed by Ltd’s no longer applies due to the loophole in the law being closed.  As a sole trader, you are the company – the same legal entity, so you can’t be VAT registered and your not personally protected from liability.  When working for an umbrella company, you become a permanent employee of the umbrella company. They invoice on your behalf and pay your PAYE.  This affords you the same protection as any other employee and takes away some of the management of invoicing etc. however, this is probably the least cost efficient way of working since the umbrella company will take a cut of your earnings.

We then move onto the thorny issue of IR35. This is legislation that designed to catch contractors who are really operating more as “disguised employees”.  IR35 is constantly evolving and application by HMRC can be inconsistent.  The best ways to mitigate being “caught” inside of IR35 legislation are to perform tasks that an employee does not do.  For example, advertising your business differentiates you from an employee, ensuring your contracts have a “right of substitution” (whereby the actual worker/person performing the work can be changed), having multiple contracts at any one time – whilst sometimes difficult for a contractor to achieve - can greatly help, showing that you are taking on risk (especially financial risk) along with being able to show that you don’t receive any benefits from the engagement as an employee would do (for example, no sick pay).

Finally, Alex asks, “When should you change?”  He puts a number of questions forward that we’d each need to answer for ourselves.  Are you happy with your current way of working?  Understand the relative balance of income versus stress from the various working practices.  Define your goals regarding work/life balance.  Ask yourself why you would want to change, how do you stand to benefit?  Where do you live?  Be aware that very often, contracts may not be readily available in your area, and that you may need to travel considerable distance (or even stay away from home during the working week), and finally, Alex asks that you ask yourself, “Are you good enough?”.  Alex closes by re-stating the key takeaways.  Enjoy your job, figure out your goals, increase your profile, network, remember that change can be good – but only for the right reasons, and start now – don’t wait.

IMG_20160507_105119

After another coffee break following Alex’s session, it’s time for the next one.  This one was Lori Edwards’ SQL Server Statistics – What are the chances?

Lori opens by asking “What are statistics?”.  Just as Indexes provide a “path” to find some data, usually based upon a single column, statistics contain information relating to the distribution of data within a column across the entire set of rows within the table.  Statistics are always created when you create an index, but you can create statistics without needing an index.

Statistics can help with predicates in your SQL Server queries.  Predicates are the conditions within your WHERE or ORDER BY clauses.  Statistics contain information about density, which refers to the number of unique values in the column along with cardinality which refers to the uniqueness of a given value.  There’s a number of different ways to create statistics, you can simply add an index, you can use AUTO CREATE STATISTICS and CREATE STATISTICS directives as well as using a system stored procedure, sp_createstats.  If you’re querying on a column, statistics for that column will be automatically created for you if they don’t already exist, however, if you anticipate heavy querying utilising a given column, it’s best to ensure that statistics are created ahead of time.

Statistics are quite small and don’t take up as much space as indexes.  You can view statistics by running the sp_helpstats system stored procedure or you can query the sys.stats system table or even the sys.dm_db_stats table.  The best way of examining statistics, though, is to use the database console command, DBCC SHOW_STATISTICS.  When viewing statistics, low density values indicate a low level of uniqueness.  Statistics histograms show a lot of data, RANGE_HI_KEY is the highest key value, whilst RANGE_ROWS indicates how many rows there are between the HI_KEYS in different column values.

The SQL Server Query Optimizer uses statistics heavily to generate the optimized query plan.  Note, though, that optimized query plans are necessarily optimal for every situation, they’re the most optimal general purpose plans.  It’s purpose is to come up with a good plan, fast, and statistics are necessary for this to be able to happen.  To make the most of the cardinality estimates from statistics, it’s best ensure you use parameters to queries and stored procedures, use temp tables where necessary and keep column orders consistent.  Table variables and table-valued parameters can negatively affect cardinality.  Whether the query optimizer selects a serial or parallel plan can be affected by cardinality, as can the choice to use an index seek versus an index scan.  Join algorithms (i.e. hash match, nested loops etc.) can also be affected.

From here, the query optimizer will decide how much memory it thinks it needs for a given plan, so memory grants are important.  Memory grants are effectively the cost of the operation multiplied by the number of rows that the operation is performed against, therefore, it’s important for the query optimizer to have accurate row count data from the statistics. 

2016-06-07 21_42_25-I5xLz.png (766×432)One handy tip that Lori shares is in interpreting some of the data from the “yellow pop-up” box when hovering over certain parts of a query plan in SQL Server Management Studio.  She states how the “Estimated Number Of Rows” is what the table’s statistics say there are, whilst the “Actual Number Of Rows” are what the query plan actually encountered within the table.  If there’s a big discrepancy between these values, you’ll probably need to update the statistics for the table!

Statistics are automatically updating by SQL Server, although, they’re only updated after a certain amount of data has been added or updated within the table.  You can manually update statistics yourself by calling the sp_updatestats system stored procedure.

By default, tables inside a database will have AUTO UPDATE STATISTICS switched on, which is what causes the statistics to be updated automatically by SQL Server occasionally – usually after around 500 rows or 20% of the size of the table have been added/modified.  It’s usually best to leave this turned on, however, if you’re dealing with a table that contains a very large number of rows and has either many new rows added or many rows modified, it may be better to turn off the automatic updating of statistics and perform the updates manually after either a specific number of modifications or at certain appropriate times.

Finally, it’s important to remember that whenever statistics are updated or recomputed, any execution plans built on those statistics that were previously cached will be invalidated.  They’ll need to be recompiled and re-cached.

After Lori’s session, there’s another quick coffee break, and then it’s on to the next session.  This one was Mark Broadbent’s Lock, Block & Two Smoking Barrels.  Mark’s session focused on SQL Server locks.  Different types of locks, how they’re acquired and how to best design our queries and applications to ensure we don’t lock data for any longer than we need to.

Mark first talks about SQL Server’s transactions.  He explains that transactions are not committed to the the transaction logs immediately.  They are processed through in-memory buffers first before being flushed to disk.  Moreover, the logs need to grow to a certain size before they get flushed to disk so there’s always a possibility of executing a COMMIT TRANSACTION statement yet the transaction isn’t visible within the transaction log until sometime later.  The transaction being available in the transaction log is the D in ACID – Durability, but Mark highlights that it’s really delayed durability.

IMG_20160507_125814Next, Mark talks about concurrency versus correctness. He reminds us of some of the laws of concurrency control.  The first is that concurrent execution should not cause application programs to malfunction. The second is that concurrent execution should not have lower throughput or higher response times than serial execution.  To balance concurrency and correctness, SQL Server uses isolation, and there are numerous isolation levels available available to us, all of which offer differing levels of concurrency versus correctness.

Mark continues by stating that SQL Server attempts to perform our queries in as serial a manner as possible, and it uses a technique called transaction interleaving in order to achieve this between multiple concurrent and independent transactions.  Isolation levels attempt to solve the interleaving dependency problems.  They can’t completely cure them, but they can reduce the issues caused by interleaving dependencies.  Isolation levels can be set at the statement, transaction or session levels.  There are 4 types defined by the ANSI standards, but SQL Server 2005 (and above) offer a fifth level.  It’s important to remember that not all isolation levels can be used everywhere, for example, the FILESTREAM data type is limited in the isolation levels that it supports.

We’re told how SQL Server’s locks are two-phased and are considered so if every LOCK is succeeded by an UNLOCK.  SQL Server has different levels of locks, and they can exist at a various levels of granularity from row locks, to page locks all the way up to table locks.  When SQL Server has to examine existing locks in order to acquire a new or additional lock, it will only ever compare locks on the same resource.  This means that row locks are only ever compared to other row locks, page locks compared to other page locks and table locks to other table locks.  They’re all separate.  That said, SQL Server will automatically perform lock escalation when certain conditions occur, so when SQL Server has acquired more than 5000 other locks of either row or page type, it will escalate those locks to a single table level lock.  Table locks are the least granular kind of lock and a very bad for performance and concurrency within SQL Server – basically the one query that holds the table level lock prevents any other query from accessing that table.  For this reason, it’s important to ensure that our queries are written in such a way as to minimize the locks that they need, and to ensure that when they do require locks that those locks as granular as can be.  Update locks will allow multiple updates against the same table and/or rows.  They’re compatible with shared locks but not other update locks or exclusive locks so it’s worth bearing in mind how many concurrent writes we attempt to make to our data.

Mark continues to show us some sample query code that demonstrates how some simple looking queries can cause concurrency problems and can result in lost updates to our data.  For example, Mark shows us the following query:

BEGIN TRAN T1
SELECT @newquantity = quantity FROM basket
SET @newquantity = @newquantity + 1
UPDATE some_other_table SET quantity = @newquantity
COMMIT TRAN T1

The above query can fail badly, with the required UPDATE being lost if multiple running transaction perform this query at the same time.  This is due to transaction interleaving.  This results in two SELECTs which happen simultaneously and acquire the quantity value, but the two UPDATEs get performed in interleaved transactions which means that the second UPDATE that runs is using stale data to update, effectively “overwriting” the first UPDATE (so the final newquantity value is one less than it should be).  The solution to this problem is to perform the quantity incrementing in-line within the UPDATE statement itself:

BEGIN TRAN T1
UPDATE some_other_table SET quantity = t2.newquantity FROM (SELECT quantity + 1 FROM basket) t2
COMMIT TRAN T1

Reducing the number of statements needed to perform some given function on our data is always the best approach.  It means our queries are being as granular as they can be, proving us with better atomic isolation and thereby reducing the necessity to interleave transactions.

IMG_20160507_133930After Mark’s session was over, it was time for lunch.  Lunch at the SQLBits conferences in previous years has always been excellent with a number of choices of hot, cooked food being available and this year was no different.  There was a choice of 3 meals, cottage pie with potato wedges, Moroccan chicken with couscous or a vegetarian option (I’m not quite sure what that was, unfortunately), each of which could be finished off with one of a wide selection of cakes and desserts!

IMG_20160507_123500I elected to go for the Moroccan chicken, which was delicious, and plumped for a very nice raspberry ripple creamy yoghurt.  An excellent lunch, as ever!

During lunch, I managed to catch up with a few old friends and colleagues who had also attended the conference, as well as talking to a few newly made acquaintances whilst wandering around the conference floor and the various sponsors stands.

After a good wander around, during which I was able to acquire ever more swag from the sponsors, it was soon time for the afternoon’s sessions.  There were only two more sessions left within the day, it now being around 14:30 after the late lunch hour was over, I headed off to find the correct “dome” for the first of the afternoon’s sessions, Erland Sommarskog’s Dynamic Search Conditions.

Erland’s talk will highlight the best approaches when dealing with dynamic WHERE and ORDER BY clauses in SQL Server queries, something that I’m sure most developers have had to deal with at some time or another.  For this talk, Erland will use his own Northgale database, which is the same schema as Microsoft’s old Northwind database, but with a huge amount of additional data added to it!

Erland first starts off by warning us about filtered indexes.  These are indexes that themselves have a WHERE condition attached to them (i.e. WHERE value <> [somevalue]) as these tend not to play very well with dynamic queries.  Erland continues by talking about how SQL Server will deal with parameters to queries.  It will perform parameter “sniffing” to determine how best to optimize a static query by closely examining the actual parameters we’re supplying.  Erland shows us both a good and bad example:  WHERE xxx = ISNULL(@xxx,xxx) versus WHERE xxx = (xxx = @xxx OR xxx IS NULL).  He explains how the intended query will fail if you use ISNULL in this situation.  We’re told how the SQL Server query optimizer doesn’t look at the stored procedure itself, so it really has no way of knowing if any parameters we pass in to it are altered or modified in any way by the stored procedures code.  For this reason, SQL Server must generate a query plan that is optimized for any and all possible values.  This is likely to be somewhat suboptimal for most of the parameters we’re likely to supply.  It’s for this reason that the execution plan can show things like an index scan against an index on a “FromDate” datetime column even if that parameter is not being passed to the stored procedure.  When we’re supplying only a subset of parameters for a stored procedure with many optional parameters, it’s often best to use the OPTION RECOMPILE statement to force a recompilation of the query every time it’s called.  This way, the execution plan is regenerated based upon the exact parameters in use for that call.  It’s important to note, however, that recompiling queries is an expensive operation, so it’s best to measure exactly how often you’ll need to perform such queries.  If you’re calling this query very frequently, you may well get the best performance from using purely dynamic SQL.

Erland then moves on to discuss dynamically ordering data.  He states that the CASE statement inside the ORDER BY clause is the best way to achieve this, for example: ORDER BY CASE @sortcolumn WHEN ‘OrderID’ THEN [Id] END, CASE @sortcolumn = ‘OrderDate’ THEN [Date] END…..etc.  This is a great way to achieve sorting my dynamic columns, however, there’s a gotcha with this method and that is that you have to be very careful of datatype differences between the columns in the different case clauses as this can often lead to error.

Next, we look at the permissions required in order to use such dynamic SQL and Erland says that it’s important to remember that any user who wishes to run such a dynamic query will require permissions to access the underlying table(s) upon which the dynamic query is based.  This differs from (say) a stored procedure where the user only need permissions to the stored procedure and not necessarily the underlying table upon which the stored procedure is based.  One trick that can be used to gain somewhat the best of both of these approaches is to use the sp_executesql system stored procedure.  Using this will create a nameless stored procedure from your query, it will cache it and execute it.  The stored cache can then be re-used on subsequent calls to the query with the nameless stored procedure being identified based upon a hash of the the query content itself.

Another good point that Erland mentions is to ensure that all SQL server objects (tables, functions etc.) referenced within a dynamic query should always be prefixed with the full schema name and not just referenced by the object name (i.e. use [dbo].[SomeTable] rather than [SomeTable]).  This is important as different users who run your dynamic SQL code could be using different default schemas – if they are and you haven’t specified the schema explicitly, the query will fail.

Erland also mentions that one very handy tip with dynamic queries is to always include a @debug input parameter of datatype bit, that can have a default setting of 0 (off).  It’ll allow you to always specify this parameter and pass in a value of 1 (on) to ensure that code such as IF @debug PRINT @sql will be run allowing you to output the actual T-SQL query generated by the dynamic code.  Erland says that you will need this eventually, so it’s always best to build it in from the start.

When building up your dynamic WHERE clause, one tricky condition is to know whether to add an AND at the beginning of the condition if you’re adding the 2nd or higher condition (the first condition of the WHERE clause won’t need the AND to be prepended of course).  One simple way around this is to make it so that all of the dynamically added WHERE clauses are always the 2nd or higher numbered condition by statically creating the first WHERE clause condition in your code as something benign such as “WHERE 1 = 1”.  This, of course, matches all records and all subsequently added WHERE clauses can always be prefixed with an AND, for example, “IF @CustomerPostCode THEN @sql += “ AND Postcode LIKE …..”, also it’s important to always add parameters into the dynamic SQL rather than concatenating values (i.e. avoid doing @sql += ‘ AND OrderId = ‘” + @OrderId + “’) as this will mess with the query optimizer and your generated queries will be less efficient overall as a result.  Moreover, raw concatenation of values can be a vector for SQL injection attacks.  For this same reason, you should always translate the values that you’ll use for WHERE and ORDER BY clauses that are passed into your stored procedure.  Translate the passed parameter value to a specific hard-coded value that you explicitly control.  Don’t just use the passed in parameter value directly.

Occasionally, it can be a useful optimization to inline some WHERE clause values in order to force a whole new query plan to be cached.  This is useful in the scenario when, for example, you're querying by order city and 60% of all orders are in same city.  You can inline that one city value to have a cached plan just for that city and a different single cached plan for all other cities.

Finally, for complex grouping, aggregation and the dynamic selection of the columns returned from the query, Erland says is often easiest to and more robust to construct these kind of queries in the client application code rather than in a dynamic SQL producing stored procedure.  One caveat around this is to ensure that you perform the entirety of your query client-side (or entirely server-side if you must) – don’t try to mix and match by performing some client-side and some server-side.

IMG_20160507_154920And with this, Erland’s session on dynamic SQL search conditions is complete.  After yet another short coffee break, we’re ready for the final session of what has been a long, but information-packed day.  And for the final session, I decided to attend Simon D’Morias’ “What is DevOps For Databases?”

Simon starts with explaining the term “DevOps” and reminds us that it’s the blurring of lines between the two traditionally separate disciplines of development and operations.  DevOps means that developers are far closer to the “operations” side of applications which frequently means getting involved with deployments, infrastructure and a continuous delivery process.  DevOps is about automation of application integration and deployment, provably and reliably.

Simon shows the three pillars upon which a successful DevOps process is built.  Develop, Deploy & Measure.  We develop some software, deploy it and the then measure the performance and reliability of the deployment.   From this measurement we can better plan and can thus feed this back into the next iteration of the cycle.  We’re told that to make these iterations work successfully, we need to keep changes small. From small changes, rather than larger ones, we can keep deployment simple and fast.  It allows us to gather frequent feedback on the process and allows continuous improvement of the deployment process itself.  With the teams behind the software (development, operations etc.) being more integrated, there’s a greater spread of knowledge about the software itself, the changes to the software in a given development/deployment cycle which improves early feedback.  Automation of these systems also ensures that the deployment is made easier and thus also contributes to better and earlier feedback.

When it comes to databases, DBA’s and other database professionals are frequently nervous about automating any changes to production databases, however, by keeping changes small and to a minimum within a given deployment cycle, and by having a continuously improving robust process for performing that deployment, we can ensure that each change is less risky than if we performed a single large change or upgrade to the system.  Continuous deployments also allow for detecting failures fast, which is a good thing to have.  We don’t want failures caused by changes to take a long time before they surface and we’re made aware of them.  Failing fast allows easy rollback and reliability of the process enables automation which further reduces risk.  Naturally, monitoring plays a large part of this and a comprehensive monitoring infrastructure allows detection of issues and failures and allows improves in reliability over time which, again, further reduces risk.

Simon moves on to discuss the things that can break DevOps.  Unreliability is one major factor that can break a DevOps process as even something running at 95% reliability is no good.  That 5% failure rate will kill you.  Requiring approval within the deployment chain (i.e. some manual approval, governance or compliance process) will break continuity and is a potential bottleneck for a successful DevOps deployment iteration also.  A greater “distance” between the development, operations and other teams will impact their ability to be knowledgeable about the changes being made and deployed.  This will negatively impact the team’s ability to troubleshoot and issues in the process, hindering the improvement of reliability.

IMG_20160507_164439It can often be difficult to know where to start with moving to an automated and continuous DevOps process.  The first step to to ensure we can “build ourselves a pipeline to live” – this is a complete end-to-end automated continuous integration process.  There are numerous tools available to help with this and SQL Server itself assists in this regard with the ability to package SQL server objects into a DACPAC package.  Simon insists that attempting to proceed with only a partial implementation of this process will not work.  It’s an all or nothing endeavour. Automating deployments to development and test environments, but not to the production environment (as some more nervous people may be inclined to do) is like building only half and bridge across a chasm!  Half a bridge is no bridge at all!

Simon concludes by showing us a quick demo of a simple continuous deployment process using Visual Studio to make some local database changes, which are committed to version control using Git and then pushed to Visual Studio Team Services (previously known as Visual Studio Online) which performs the “build” of the database objects and packages this into a DACPAC package.  This package is then automatically pushed to an Azure DB for deployment.

Finally, Simon suggests that one of the the best ways to ensure that our continuous deployment process is consistent and reliable is to ensure that there are minimal differences (ideally, no differences) between our various environments (development, test, staging, production etc.), and especially between our staging and production environments.

After Simon’s session was over, it was time for all of the conference attendees to gather in the main part of the exhibition hall and listen as one of the conference organisers read out those people who had won prizes by filling in forms and entering competitions run by each of the conference sponsors.  I didn’t win a prize, and actually, had entered very few competitions having been far too busy either attending the many sessions or drinking copious amounts of coffee in between them!  Once the prizes were all dished out, it was time for yet another fantastic SQLBits conference to sadly end.  It had been a long, but fantastic day at another superbly organised and run SQLBits conference.  Here’s hoping next year’s conference is even better!

Stacked 2015 In Review

IMG_20151118_170749 On Wednesday 18th November 2015, the third Stacked event was held.  The Stacked events are community events primarily based around Windows development.  The events are free to attend and are organised by a collective group of folks from Mando Group and Microsoft UK with sponsorship from additional companies.  The last two Stacked events were held in Liverpool in 2013 and after a year off in 2014, Stacked returned in 2015 with an impressive line-up of speakers and talk and to a new venue at the Comedy Store at Deansgate Locks in Manchester.

Being in Manchester, it was only a short train ride for me to arrive bright and early on the morning of the conference.  Registration was taking place from 8:30am to 9:10am, and I’d arrived just around 9am.  After checking in and receiving my conference lanyard, I proceeded to the bar area where complimentary tea and coffee was on offer.  After only having time for a quick cup of coffee, we were called into the main area of the venue, which was the actual stage area of the comedy club, for the first session of the day.

The first session was Mike Taulty’s Windows 10 and the Universal Windows Platform for Modern Apps. Mike’s session was dedicated to showing us how simple it is to create applications on the Universal Windows Platform.  Mike first starts by defining the Universal Windows Platform (UWP).  The UWP is a way of writing applications using a combination of one of the .NET languages (C# or VB.NET) along with a specific “universal” version of the .NET runtime, known as .NET Core.  Mike explains that, as Windows 10 is available on so many devices and different categories of devices (PC’s, Laptops, Tablets, Phones and even tiny IoT devices such as Raspberry Pi’s!), the UWP sits “on top” of the different editions of Windows 10 and provides an abstraction layer allowing largely unified development on top of the UWP.  Obviously, not every “family” of devices share the same functionality, so API’s are grouped into “contracts”, with different “contracts” being available for different classes of device.

Building a UWP application is similar to how you might build a Windows WPF application.  We use XAML for the mark-up of the user interface, and C#/VB.NET for the code behind.  Similar to WPF applications, a UWP application has an app.xaml start-up class.  Mike decides he’s going to plunge straight into a demo to show us how to create a UWP application.  His demo application will be an application that connects, via Bluetooth, to a SpheroBall (which is a great toy – it’s a small motorized ball that can be “driven” wirelessly and has lights that can light up in various RGB colours).  Mike will show this same application running on a number of different devices.

IMG_20151118_091002 Mike first explains about the make-up and structure of a UWP application.  The files we’ll find inside a UWP app – such as assets, pictures, resources etc. - are separated by "device family" (i.e. PC, tablet, phone etc.) - so we’d have different versions of each image for each device family we're targeting.   Mike explains how UWP (really XAML)  applications can be "adaptive" – this is the same as a "responsive" web site.  Mike builds up his application using some pre-built snippets of code and fills in the blanks, showing us how using compiler directives we can have certain code only invoked if we’re running on a specific device.  Mike demos his app first on a laptop PC, then a Windows 10 phone and finally a Raspberry Pi.  Mike shows how we can deploy to, and control, the Raspberry Pi – which is running Windows 10 Core - by either remote PowerShell or alternatively, via a web UI built into Windows 10 Core on the device.

Mike says that when we’re building an app for IoT devices (such as the Raspberry Pi, Arduino etc.) we will often need a reference to an extension library that is specific to the Iot Core platform. This extension library, which is referenced from within our UWP project separately, allows access to additional types that wouldn't ordinarily exist within the UWP platform itself.  By checking such things as  Windows.Foundation.MetaData.ApiInformation.IsAPIContractPresent we write code that only targets and is only invoked on specific classes of device.

Mike then shows us his application running on the RaspBerry Pi, but being controlled via a BlueTooth connected XBox One controller.  After this Mike explains that Windows 10, on devices equipped with touch-sensitive screens, has built in handwriting and “ink” recognition, so the demo proceeds to show the SpheroBall being controlled by a stylus writing on the touch-sensitive screen of Mike’s laptop.  Finally, Mike talks about Windows 10’s built-in speech recognition and shows us how, with only a few extra lines of code, we cannot control the SpheroBall via voice commands!

In rounding up, Mike mentions a new part of Windows 10, which is an open connectivity technology allowing network discovery of APIs, called "AllJoyn".  It's an open, cross-platform technology and Mike says how there’s even light bulbs you can currently buy that will connect to your home network via the AllJoyn technology so you can control your home lighting via network commands!

After Mike’s session, we all left the theatre area and went back to the main bar area where there was more tea and coffee available for our refreshments.  After a short 15-20 minutes break, we headed back to the theatre area to take our seats for the next session, which was Jeff Burtoft’s Windows 10 Web Platform.

Jeff starts by talking about the history of Internet Explorer with its Trident engine and Strict & Quirks mode - two rendering engines to render either quirks (i.e. old style, IE specific) or strict mode (to be more standard compliant).  Jeff says how this was Ok in the past as lots of sites were written specifically for Internet Explorer, but these days, we're pretty much all standards compliant.  As a result, Microsoft decided to completely abandon the old Internet Explorer browser and gave birth to the fully standards compliant Edge browser.   Jeff then shows a slide from a study done by a website called quirksmode.com which is all about the proliferation of different versions of Chromium-based browsers.  Chromium is used both by Google’s Chrome browser, but it’s also the basis for a lot of “stock” browsers that ship on smartphones.  Many of these browsers are rarely, if ever, updated.  Jeff states that some features of IE were actually implemented exactly to the HTML specification whilst other browser’s implementations weren't exactly compliant with the W3C specification.  These browsers are now far more common, but Jeff states how Microsoft, with the Edge browser, will render things "like other browsers" even if not quite to spec.  This creates a better parity between all possible browsers so that developing for web apps is more consistent across platforms.

Jeff shows a demo using the new Web Audio API and shows 3 different sound files being played on a web page and perfectly synchronised, each with their own volume controls.  Jeff then shows a demo of a FPS game in the browser and controlled by the XBox one controller.  The demo is using 3 major APIs for this.  WebGL, Web Audio API and XBOX Controller API and manages a very impressive 40-50 frames per second, even though Jeff’s laptop isn’t the fastest laptop and the demo is running entirely inside the browser.

Next, Jeff talks about how we can write a HTML/JavaScript app (ala Windows 8) that are HTML and JavaScript and can be "bundled" with the EdgeHTML.dll library (the rendering engine for Edge browser) and Chakra (the JavaScript engine of Edge browser).   Apps developed like this can be "packaged" and deployed to run just like a desktop application, or can be "hosted" by using a "WebView control" - this allows a web app on a phone to look and act almost exact like a native app.

Jeff then talks about a Microsoft developed, but open-source, JavaScript library called ManifoldJS.  This library is the simplest way to create hosted apps across platforms and devices.  It allows the hosted web app to be truly cross-platform across devices and browsers.  For example, packaging up your own HTML/JavaScript application using ManifoldJS would allow the same package to be deployed to the desktop, but also deployed to (for example) an Android-based smartphone where the same app would use the Cordova framework to provide native-like performance as well as allowing access to device specific features, such as GPS and other sensors etc.

Jeff demos packaging an application using ManifoldJS and creates a hosted web app, running as a "desktop" application on Windows 10, which has pulled down the HTML, CSS and JavaScript from a number of pages from the BBC Sport website including all assets (images etc.) and wrapped it up nicely into an application that runs in a desktop window and functions the same as the website itself.

Finally, Jeff also demos another hosted web app that uses his Microsoft Band and is responsive gesture controls to automate sending specific, pre-composed tweets whilst drinking beer!  :)

IMG_20151118_153356 After Jeff’s session, there was another break.   This time, we were treated to some nice biscuits to go with our tea and coffee!   After another 15 minutes it was time for the final session of the morning.  This one was slightly unusual as it had two presenters and was split into two halves.  The session was by Jonathan Seal & Mike Taulty and was Towards A More Personal Computing Experience.

Jonathan was first to the stage and started by saying that his idea behind making computing “more personal” is largely geared around how we interact with machines and devices.  He notes how interactions are, and have been until recently, very fixed – using a keyboard and mouse to control our computers has been the norm for many years.  Now, though, he says that we’re starting to open up new ways of interaction.  These are speech and gesture controls.  Jonathan then talks about something called the “technological teller”.  This is the phenomenon whereby man takes an old way of doing something and applies it to new technology.  He shows a slide which indicates that the very first motorcars used by the US Mail service were steered using a rudder-like device, extended to the front side of the vehicle but controlling the the rear wheels.  This was implemented as, at that time, we were used to “steering” something with a rudder as all we’d had before the car that needed steering was boats!  He explains how it was many years before the invention of the steering wheel and placing the steering controls closer to where the user would actually steer the vehicle.

Jonathan shows some videos of new gesture control mechanisms in new cars that are shortly coming onto the market.  He also shows a video of a person controlling a robotic ball (similar to the SpheroBall used earlier by Mike Taulty) by using advanced facial recognition, which not only detected faces, but could detect emotional expressions in order to control the robotic ball.  For example, with a “happy” expression; the ball would roll towards the user, whilst with a “sad” or “angry” expression, the ball would roll away from the user.

After these videos, Jonathan invites Mike Taulty to the stage to show some of the facial recognition in action.   Mike first talks about something called Windows Hello, which is an alternative mechanism of authentication rather than having to enter a password.  Windows Hello works primarily on facial recognition.

Mike proceeds to show a demo of some very simple code that targets the facial recognition SDK that exists within Windows 10 and which allows, using only a dozen or so lines of code, to get the rectangles around faces captured from the webcam.  Mike also shows that same image which can be sent to an online Microsoft Research project called Project Oxford, which further analyses the facial image and can detect all of the elements of the face (eyes, eyebrows, node, mouth etc.) as well as provide feedback on the detected expression shown on the face (i.e. Happy, sad, angry, confused etc.)  Using Project Oxford, you can, in real-time, not only detect things like emotion from facial expressions but can also detect the person’s heart rate from the detected facial data!

Mike says that the best detection requires a “depth camera”.  He has one attached to his laptop.  It’s an Intel RealSense camera which costs around £100.  Mike also shows usage of a Kinect camera to detect a full person with 25 points across all bodily limbs.  The Kinect camera can detect and track entire skeletal frame of the entire body.  From this, software can use not only facial expressions, but entire body gestures to control software.

Mike also shows an application that interacts with Cortana – Microsoft’s personal assistant.  Mike shows a demo of some simple software that he’s written that interacts with Cortana allowing Mike to prefix spoken commands with some specific words that allow Cortana to interact with Mike’s software so that specific logic can be performed.   Mike asks Cortana, "Picture Search - show me pictures of cats".  The “Picture Search” prefix is a specifically coded prefix which instructs Cortana to interact with Mike’s program.  From here pictures matching “Cats” are retrieved from the internet and displayed; however, using the facial and expression detection technology, Mike can narrow his search down to show only “happy cats”!

IMG_20151118_131429 After this session, it was lunchtime.  In previous years, lunchtime at the Stacked events was not catered and lunch was often acquired at a local sandwich shop.  However, this year, with the event being bigger and better, a lunch was provided.  And it was a lovely lunch, too!  Lunch at conferences such as these are usually “brown bag” affairs with a sandwich, crisps etc. however on this occasion, we were treated to a full plate of hot food!  There was a choice of 3 different curries, a vegetable curry and a mild and a spicy chicken curry.  All served with pilau rice, a naan bread along with dips, sides of salad and a poppadum!  After queueing for the food, I took a table downstairs where there was more room and enjoyed a very delicious lunch.

As I was anticipating having to provide me own lunch, I’d brought along some cheese sandwiches and a banana, but after the lovely curry for lunch, I decided the these would make a nice snack on my train ride home at the end of the day!

After our lunch-break, it was time for the first session of the afternoon and the penultimate session of the day.  This was Mary J. Foley’s Microsoft & Developers – Now & Next.

Mary starts by saying that she’s not technical.  She’s a technology journalist, but she’s been following Microsoft for nearly 30 years.  She says that, with Windows 10, she really wanted to talk about 10 things.  But the more she tried to come up with 10 things; she could only come up with 3.  She says that firstly, there's been 3 CEO's of Microsoft.  And today, there are 3 business units - there used to be many more – Windows Division, Applications & Services Division and Cloud & Enterprise Division.  Mary says that previous CEO’s of Microsoft have “bet” on numerous things, some of which have not worked out.  With the current CEO, Satya Nadella, Microsoft now has only 3 big bets.  These are: More personal computing, Productivity & Business Processes and the Intelligent Cloud.  There are also 3 platforms - Windows, Office 365, Cloud.

Mary takes the opportunity to call out some of the technologies and projects that Microsoft is currently working on.  She first mentions the “Microsoft Graph” which is a grand, unified API that allows access to all other API’s provided by Microsoft i.e. Office 365 API, Azure etc. Developers can use the Microsoft Graph to extend the functionality of Office 365 and its related applications, for example.

Mary mentions she loves codenames.   She says she found out about Project Kratos - which is new, as-yet-unannounced technology building on top of Office365 and Azure called "PowerApps".  Not much is known about Project Kratos as yet, however, it appears to be a loose set of micro-services allowing non-programmers to extend and enhance the functionality of Office365.  It sounds like a very interesting proposition for business power users.

Mary talks about the future for cloud, and something known as PaaS 2.0 (Platform as a Service) which is also called Azure Service Fabric.  This is essentially lots of pre-built micro-services that can be consumed by developers.  Mary then quickly discusses one of her favourite project codenames from the past, “Red Dog”.  She says it was the codename for what eventually became Azure.  She says the codename originally came from some of the team members who were aware of a local strip club called the “Pink Poodle”, and so “Red Dog” was born!

Next, Mary goes on to talk about Bing.  She says that Bing is not just a search engine but is actually a whole developer platform as there are quite a lot of Bing related API’s. Bing has been around for quite some time, however, as a developer platform, it never really took off.  Mary says that Microsoft is now giving the Bing platform another “push”.  She mentions Project Satori, which is an “entity engine” and allows Bing and newer technology such as Cortana to better understand the web and search (i.e. a distributed knowledge graph).

IMG_20151118_140514 Mary then proceeds to mention that Microsoft has a team known as the "deep tech team" within the Developer Division.  She says how their job is to go out to companies that may have difficult technology problems that require solutions and to help those companies solve the problems.  Interestingly, the team are free to solve those problems using non-Microsoft technology as well as Microsoft technologies – whatever is the best solution to the problem.  The team will even help companies who are already committed to non-Microsoft technologies (i.e. pure Linux “shops” or pure Apple shops).  She says they have a series of videos on YouTube and Channel 9 known as the “Decoded” series, and that these videos are well worth checking out.

Mary then talks about another project, codenamed “Red Stone”.  This is the codename for what is effectively Windows 11, but which will be released as a significant update to Windows 10 (similar to Threshold2, however Red Stone is predicted to be 2 or 3 updates on from Threshold2).  She also talks about a few rumours within Microsoft.  One is that Microsoft may produce a Surface Phone, whilst the other is that Microsoft, if Windows Phone doesn’t gain significantly more market share, may switch their mobile phone operating systems to Android!

Finally, Mary talks about another imminent new technology from Microsoft called “GigJam”.  It’s billed as “a blank canvas you can fill with information and actions from your business systems.”  Mary says it’s one of those technologies that’s very difficult to explain, but once you’ve seen and used it, it very impressive.  Another one to watch!

After Mary’s session, there was a final coffee break after which was the last session of the day.  This session was Martin Beeby’s My Little Edge Case And IoT.   Martin had created something called "Edge Case", which was built to help him solve one of his own business problems that he has as a developer evangelist.  He needed a unique and interesting "call to action" from the events that he attends.  Edge Case is a sort of arcade cabinet sized device that allows users to enter a URL that would be sent to Microsoft’s SiteScan website in order to test the rendering of that URL.  The device is a steampunk style machine complete with old fashioned typewriter keyboard for input, old pixelated LCD displays and valve based lightbulbs for output and even a smoke machine! 

Martin outsourced the building of the machine to a specialist company.  He mentions the company name and their Italian domain, we wemakeawesomesh.it which raises a few laughs in the audience.  Martin talks about how, after the full machine was built, he wanted to create a "micro" edge case, essentially a miniaturized version of the real thing, running on a single Raspberry Pi and made such that it could fit inside an old orange juice carton!.  Martin mentions that he’s placed the code for his small IoT (Internet of things) device on Github.

Martin demos the final micro edge case on stage.  Firstly, he asks the audience to send an SMS message using their phones to a specific phone number which he puts up on the big screen.  He asks that the SMS message simply contain a URL in the text.  Next, Martin uses his mini device to connect to the internet and access an API provided by Twilio in order to retrieve the SMS messages, one at a time, previously sent by the audience members.  The little device takes each URL and displays it to a small LCD screen built into the front of the micro edge case.  Martin reads out those URL’s and after a slight delay whilst the device sends the URL to the SiteScan service, Martin finally tells us how those URL’s have been rated by SiteScan, again, displayed on the small LCD screen of the micro edge case.

After Martin’s session was over, we were at the end of the day.  There was a further session later in the evening whereby Mary J. Foley was recording her Windows Weekly podcast live from the event; however, I had to leave to catch my train back home.  Stacked 2015 was another great event in the IT conference calendar, and here’s hoping the event will return again in 2016!

DDD North 2015 In Review

IMG_20151024_082240

On Saturday 24th October 2015, DDD North held its 5th annual Developer Developer Developer event.  This time the event was held in the North-East, at the University of Sunderland.

As is customary for me now, I had arrived the evening before the event and stayed with family in nearby Newcastle-Upon-Tyne.  This allowed me to get to the University of Sunderland bright and early for registration on the morning of the event.

IMG_20151024_083559 After checking in and receiving my badge, I proceeded to the most important area of the communal reception area, the tea and coffee urns!  After grabbing a cup of coffee and waiting patiently whilst further attendees arrived, there was soon a shout that breakfast was ready.  Once again, DDD North and the University of Sunderland provided us all with a lovely breakfast baguette, with a choice of either bacon or sausage.  

After enjoying my bacon baguette and washing it down with a second cup of coffee, it was soon time for the first session of the day. The first session slot was a tricky one, as all of the five tracks of sessions appealed to me, however, I could only attend one session, so decided somewhat at the last minute it would be Rik Hepworth’s The ART of Modern Azure Deployments.

IMG_20151024_093119 The main thrust of Rik’s session is to explain Azure Resource Templates (ART).  Rik says he’s going to explain the What, the Why and the How of ART’s.  Rik first reminds us that every resource in Azure (from virtual networks, to storage accounts, to complete virtual machines) is handled by the Azure Resource Manager.  This manager can be used and made to perform the creation of resources in an ad-hoc manner using numerous fairly arcane PowerShell commands, however, for repeatability in creating entire environments of Azure resources, we need Azure Resource Templates.

Rik first explains the What of ART’s.  They’re quite simply a JSON format document that conforms to the required ART schema.  They can be split into multiple files, one which supplies the “questions” (i.e. the template of the required resource – say a virtual network) and the other file can supply the “answers” to fill-in-the-blanks of the question file. (i.e. the parameterized IP address range of the required virtual network).  They are idempotent too, which means that the templates can be run against the Azure Resource Manager multiple times without fear of creating more resources than are required or destroying resources that already exist.

Rik proceeds with the Why of ART’s.   Well, firstly since they’re just JSON documents and text files, they can be version controlled.  This fits in very nicely with the “DevOps” culture of “configuration as code”, managed and controlled in the same way as our application source code is.  And being JSON documents, they’re much easier to write, use and maintain than large and cumbersome PowerShell scripts composed of many PowerShell commands with difficult to remember parameters.  Furthermore, Rik tells us that, eventually, Azure Resource Templates will be the only way to manage and configure complete environments of resources within Azure in the future.

Finally, we talk about the How of ART’s.  Well, they can be composed with Visual Studio 2013/2015. The only other tooling required is the Azure SDK and PowerShell.  Rik does mentions some caveats here as the Azure Resource API – against which the ART’s run – is currently moving and changing at a very fast pace.  As a result of this, there’s frequent updates to both the Azure SDK and the version of PowerShell needed to support the latest Azure Resource API version.  It’s important to ensure you keep this tooling up-to-date and in sync in order to have it all work correctly.

Rik goes on to talk about how monitoring of running the resource templates has improved vastly.  We can now monitor the progress of a running (or previously run) template file from portal.azure.com and resource.azure.com, which is the Resource Manager in the Azure portal.  This shows the complete JSON of the final templates, which may have consisted of a number of “question” and “answer” files that are subsequently merged together to form the final file of configuration data.  From here, we can also inspect each of the individual various resources that have been created as part of running the template, for example, virtual machines etc.

Rik then mentions something called DSC.  This is Desired State Configuration.   This is now an engineering requirement for all MS products that will be cloud-based.  DSC effectively means that the “product” can be entirely configured by declarative things such as scripts, command line commands and parameters. etc.  Everything can be set and configured from here without needing to resort to any GUI.

IMG_20151024_095414 Rik talks about how to start creating your own templates.  He says the best place to start is probably the Azure Quickstart Templates that are available from a GitHub repository.  They contain both very simple templates to ease you into getting started with something simple, but also contain some quite complex templates which will help you should you need to create a template to deploy a complete environment of numerous resources.  Rik also mentions that next year will see the release of something called the “Azure Stack” which will make it even easier to create scripts and templates that will automate the creation and management of your entire IT infrastructure, both in the cloud and on-premise, too.

As well as supporting basic parameterization of values within an Azure Resource Template, you can also define entire sections of JSON that define a complete resource (i.e. an entire virtual machine complete with an instance of SQL Server running on it).  This JSON document can then be referenced from within other ART files, allowing individual resources to be scripted once and reused many times.  As part of this, Azure resources support many different types of extensions for extending state configuration into other products.  For example, there is an extension that allows an Azure VM to be created with an Octopus Deploy tentacle pre-installed, as well as an extension that allows a Chef client to be pre-installed on the VM, for example.

Rik shows us a sample layout of a basic Azure Resource Template project within Visual Studio.  It consists of 3 folders, Scripts, Templates and Tools.  There's a blank template in the template folder and this defines the basic "shape" of the template document.  To get started within a simple template for example, a Windows VM needs a Storage account (which can be an existing one, or can create new) and a Virtual Network before the VM can be created.

We can use the GUI tooling within Visual Studio to create the basic JSON document with the correct properties, but can then manually tweak the values required in order to script our resource creation.  This is often the best way to get started.  Once we’ve used the GUI tooling to generate the basics of the template, we can then remove duplication by "collapsing" lots of the properties and extracting them into separate files to be included within the main template script.  (i.e. deploy location is repeated for each and every VM.  If we’re deploying multiple VMs, we can remove this duplication by extracting into a separate file that is referenced by each VM).

One thing to remember when running and deploying ART’s, Rik warns us, is that the default lifetime of an Azure Access Token is only 1 hour.  Azure Access Tokens are required by the template in order to prove authorisation for creating Azure resources.  However, in the event that the ART is deploying a complete environment consisting of numerous resources, this can be a time-consuming process – often taking a few hours.  For this reason, it’s best to extend the lifetime of the Azure Access Tokens, at least during development of the templates, otherwise the tokens will expire during the running of the template, thereby making the resource creation fail.

Rik wraps us with a summary, and opens the floor to any questions.  One question that is posed is whether existing Azure Resources can be “reverse-engineered” to ART scripts.  Rik states that so long the existing resources are v2 resources (that have been created with Azure Resource Manager) then you can turn these resources into templates BUT, if existing resources are V1 (also known as Classic resources and created using the older Azure Service Management) they can't be reverse-engineered into templates.

IMG_20151024_105058 After a short coffee break back in the main communal area, it’s time for the second session of the day.  For this session, I decided to go with Gary Short’s Deep Dive into Deep Learning.

Gary’s session was all about the field of data science and of things like neural networks and deep learning.   Gary starts by asking who knows what Neural Networks are and asks what Deep Learning is and the difference between them.  Not very many people know the difference here, but Gary assures us that by the end of his talk, we all will.

Gary tells us that his talk’s agenda is about looking at Neural Networks, being the first real mechanism by which “deep learning” was first implemented, but how today’s “deep learning” has improved on the early Neural Networks.  We first learn that the phrase “deep learning” is itself far too broad to really mean anything!

So, what is a Neural Network?  It’s a “thing” in data science.  It’s a statistical learning model and can be used to estimate functions that can depend on a large number of inputs.  Well, that’s a rather dry explanation so Gary gives us an example.  The correlation between temperature over the summer months and ice cream sales over the summer months.  We could use a Neural Network to predict the ice cream sales based upon the temperature variance.  This is, of course, a very simplistic example and we can simply guess ourselves that as the temperature rises, ice cream sales would predictably rise too.  It’s a simplistic example as there’s exactly one input and exactly one output, so it’s very easy for us to reason about the outcome with really relying upon a Neural Network.  However, in a more realistic example using “big data”, we’d likely have hundreds if not many thousands of inputs for which we wish to find a meaningful output.

Gary proceeds to explain that a Neural Network is really a weighted directed graph.  This is a graph of nodes and the connections between those nodes.  The connections are in a specific direction, from one node to another, and that same node can have a connection back to the originating node.  Each connection has a “weight” or a probability.  In the diagram to the left we can see that node A has a connection to node E and also a separate connection to node F.  The “weight” of the connection to node F is 0.9 whilst the weight of the connection to node E is 0.1.  This means there a 10% chance that a message or data coming from node A will be directed to node E and a 90% chance that a message coming from node A will be directed to node F.  The overall combination of nodes and connections between the nodes overall gives us the Neural Network.

Gary tells us how Neural Networks are not new, they were invented in 1943 by two mathematicians, Warren McCulloch and Walter Pitts.  Back then, they weren’t referred to as Neural Networks, but were known as “Threshold Logic”.  Later on, in the late 1940's, Donald Hebb created a hypothesis based on "neural plasticity" which is the ability of a Neural Network to be able to “heal itself” around “injuries” or bad connections between nodes.  This is now known as Hebbian Learning.  In 1958, mathematicians Farley and Wesley A. Clark used a calculator to simulate a Hebbian Machine at MIT (Massachusetts Institute of Technology).

So, just how did today’s “Deep Learning” improve upon Neural Networks.  Well, Neural Networks originally had two key limitations.  Firstly, they couldn't process exclusive or (XOR) logic in a single-layer network, and secondly, computers (or rather calculators) simply weren't really powerful enough to perform the extensive processing required.  Eventually, in 1975, a mathematician named Werbos discovered something called “back propagation”, which is the backwards propagation of error states allowing originating nodes to learn of errors further down a processing chain and perform corrective measures (self-learning) to mitigate further errors.  Back propagation helped to solve the XOR problem.  It was only through the passage of a large amount of time, though, that yesterday’s calculators became today’s computers – which got ever more powerful with every passing year – and allowed Neural Networks to come into their own.  So, although people in academia were teaching the concepts of Neural Networks, they weren’t really being used, preferring instead alternative learning mechanisms like “Support Vector Machines” (SVM) which could work with the level of computing that was available at that time.  With the advent of more powerful computers, however, Neural Networks really started to take off after the year 2000.

So, as Neural Networks started to get used, another limitation was found with them.  It took a long time to “train” the model with the input data.  Gary tells us of a Neural Network in the USA, used by the USPS (United States Postal Service) that was designed to help recognise hand-written zip codes.  Whilst this model was effective at it’s job, it took 3 full days to train the model with input data!  This had to be repeated continually as new “styles” of hand-writing needed to be recognised by the Neural Network.

Gary continues by telling us that by the year 2006, the phrase “deep learning” had started to take off, and this arose out of the work of two mathematicians called Geoffrey Hinton and Ruslan Salakhutdinov and showed that many-layered, feed-forward Neural Networks could be trained far more effectively, thus reducing the time required to train the network.  So really, “deep learning” is really just modern day Neural Networks, but ones that have been vastly improved over the original inventions of the 1940’s. 

Gary talks about generative models and stochastic models.   Generative models will “generate” things in a random way, whilst stochastic model will generate things in an unpredictable way. Very often this is the very same thing.  It’s this random unpredictability that exists in the problem of voice recognition.  This is now a largely “solved” problem from around 2010.  It’s given rise to Apple’s Siri, Google’s Google Now and most recently, and apparently most advanced, Microsoft’s Cortana.

At this point, Gary shows us a demo of some code that will categorise Iris plants based upon a diverse dataset of a number of different criteria.  The demo is implemented using the F# language, however, Gary states that the "go to" language for Data Science is R.  Gary says that whilst it’s powerful, it not a very nice language and this is primarily put down to the fact that whilst languages like C, C#, F# etc. are designed by computer scientists, R is designed by mathematicians.  Gary’s demo can use F# as it has a “type provider” mechanism which allows it to “wrap” and therefore largely abstract away the R language.  This can be downloaded from NuGet and you’ll also need the FsLab NuGet package.

IMG_20151024_113640 Gary explains that the categorisation of Irises is the canonical example for data science categorisation.  He shows the raw data and how the untrained system initially thinks that there are three classifications of irises when we know there's only really two.  Gary then explains that, in order to train our Neural Network to better understand our data, we need to start by "predicting the past".  This is simply what is says, for example, by looking at the past results of (say) football matches, we can use that data to to help predict future results.

Gary continues and shows how after "predicting the past" and using the resulting data to then train the Neural Network, we can once again examine the original data.  The graph this time is correctly showing only two different categorisations of irises.  Looking closer at the results and we can see that of a data set that contains numerous metrics for 45 different iris plants, our Neural Network was able to correctly classify 43 out of the 45 irises, with only two failures.  Looking into the specific failures, we see that they were unable to be classified due to their data being very close between the two different classifications.  Gary says how we could probably “fine tune” our Neural Network by looking further info the data and could well eradicate the two classification failures.

IMG_20151024_104709 After Gary’s session, it’s time for another tea and coffee break in the communal area, after which it’s time for the 3rd and final session before lunch.  There had been a couple of last-minute cancellations of a couple of sessions due to speaker ill health, and one of those sessions was unfortunately the one I had wanted to attend in this particular time slot, Stephen Turner’s “Be Reactive, Think Reactive”.  This session was rescheduled with Robert Hogg delivering a presentation on Enterprise IoT (Internet of Things), however, the session I decided to attend, was Peter Shaw’s Microservice Architecture, What It Is, Why It Matters And How To Implement It In .NET.

Peter starts his presentation with a look at the talk’s agenda.   He’s going to define what Microservices are and their benefits and drawbacks.  He’ll explain how, within the .NET world, OWIN and Katana help us with building Microservices, and finally he is going to show a demo of some code that uses OWIN running on top of IIS7 to implement a Microservice.

IMG_20151024_120837 Firstly, Peter tells us that Microservices are not a software design pattern, they’re an architectural pattern.  They represent a 100-foot view of your entire application, not the 10-foot view, and moreover, Microservices provide a set of guidelines for deployment of your project.

Peter then talks about monolithic codebases and how we scale them by duplicating entire systems.  This can be wasteful when you only need to scale up one particular module as you’ll end up duplicating far more than you need.  Microservices is about being able to scale only what you need, but you need to find the right balance of how much to break down the application into it’s constituent modules or discreet chunks of functionality.  Break it down too much and  you'll get nano-services – a common anti-pattern - and will then have far too much complexity in managing too many small parts.  Break it down too little, and you’re not left with Microservices.  You’ve still got a largely monolithic application, albeit a slightly smaller one.

Next, Peter talks about how Microservices communicate to each other.  He states how there’s two schools of thought to approaching the communication problem.  One school of thought is to use an ESB (Enterprise Service Bus).  The benefits of using an ESB are that it’s a robust communications channel for all of the Microservices, however, a drawback is that it’s also a single point of failure.  The other school of thought is to use simple RESTful/HTTP communications directly between the various Microservices.  This removes the single point of failure, but does add the overhead of requiring the ability of each service to be able to “discover” other services (their availability and location for example) on the network.  This usually involves an additional tool, something like Consul, for example.

Some of the benefits of adopting a Microservices architecture are that software development teams can be formed around each individual service.  These would be full teams with developers, project managers etc. rather than having specific technical silos within one large team.  Other benefits are that applications become far more flexible and modular and can be composed and changed easily by simply swopping out one Microservice for another.

Some of the drawbacks of Microservices are that they have a potentially higher maintenance cost as your application will often be deployed across different and more expansive platforms/servers.  Other drawbacks are the potential for “data islands” to form.  This is where your application’s data becomes disjointed and more distributed due to the nature of the architecture.  Furthermore, Microservices, if they are to be successful, will require extensive monitoring.  Monitoring of every available metric of the applications and the communications between them is essential to enable effective support of the application as a whole.

After this, Peter moves on to show us some demo code, built using OWIN and NancyFX.  OWIN is the Open Web Interface for .NET and is an open framework for decoupling .NET web applications from the underlying web server that powers the application.  Peter tells us that Microsoft’s own implementation of the OWIN standard is called KatanaNancyFX is a lightweight web framework for .NET, and is built on top of the OWIN standard, thus decoupling the Nancy code from the underlying web server (i.e. there’s no direct references to HttpContext or other such objects).

Peter shows us how simple some of Nancy’s code can be:

public dynamic Something(){
    var result = GetSomeData();
    return result==null ? 404 : Result.AsJson();    
}

The last line of the code is most interesting.   Since the method returns a dynamic type, returning an integer that has the same value as a HTTP Status Code will be inferred by the Nancy framework to actually return that status code from the method!

Peter shows us some more code, most of which is very simple and tells us that the complete demo example is available to download from GitHub.

IMG_20151024_130929 After Peter’s talk wrapped up, it was time for lunch.  Lunch at the DDD events is usually a “brown bag” affair with a sandwich, crisps, some fruit and/or chocolate.  The catering at DDD North, and especially at the University of Sunderland, is always excellent and this year was no exception.   There was a large selection of various combinations of crisp flavours, chocolate bars and fruit along with a large selection of very nice sandwiches, including some of the more “basic” sandwich fillings for fusspots like me!  I don’t like mayonnaise, so pre-packed sandwiches are usually a tricky proposition.  This year, though, they had “plain” cheese and ham sandwiches with no additional condiments, which was excellent for me.

The excellent food was accompanied by a drink. I opted for water.  After collecting my lunch, I went off to find somewhere to sit and eat as well as somewhere that would be fairly close to a power point as I needed to charge my laptop.

IMG_20151024_131246 I duly found a spot that allowed me to both eat my lunch, charge my laptop and look out of the window onto the River Wear at what was a very nice day outside in sunny Sunderland!

IMG_20151024_131609 After fairly quickly eating my lunch, it was time for some lunchtime Grok Talks.  These are the 15-minute, usually fairly informal talks that often take place over lunch hour at many of these type of conferences and especially at DDD conferences.  During the last few DDD’s that I’d attended, I’d missed most of the Grok Talks for various reasons, but today, having already consumed my delicious lunch, I decided that I’d try to take them in.

By the time I’d reached the auditorium for the Grok Talks, I’d missed the first few minutes of the talk by Jeff Johnson all about Microsoft Azure and the role of Cloud Solution Architect at Microsoft.

Jeff first describes what Azure is, and explains that it’s Microsoft’s cloud platform offering numerous services and resources to individuals and companies of all sizes to be able to host their entire IT infrastructure – should they so choose – in the cloud. 

IMG_20151024_133830 Next, Jeff shows us some impressive statistics on how Azure has grown in only a few short years.  He says that the biggest problem that Microsoft faces with Azure right now is that they simply can’t scale their infrastructure quick enough to keep up with demand.  And it’s a demand that is continuing to grow at a very fast rate.  He says that Microsoft’s budget on expenditure for expanding and growing Azure is around 5-6 billion dollars per annum, and that Azure has a very large number of users even today.

Jeff proceeds by talking about the role of Cloud Solutions Architect within Microsoft.  He explains that the role involves working very closely with customers, or more accurately potential customers to help find projects within the customers’ inventory that can be migrated to the cloud for either increased scalability, general improvement of the application, or to make the application more cost effective.  Customers are not charged for the services of a Cloud Solutions Architect, and the Cloud Solutions Architects themselves seek out and identify potential customers to see if they can be brought onboard with Azure.

Finally, Jeff talks about life at Microsoft.  He states how Microsoft in the UK has a number of “hubs”, one each in Edinburgh, Manchester and London, but that Microsoft UK employees can live anywhere.  They’ll use the “hub” only occasionally, and will often work remotely, either from home or from a customer’s site.

After Jeff’s talk, we had Peter Bull and his In The Groove talk all about developing for Microsoft’s Groove Music.  Peter explains that Groove Music is Microsoft’s equivalent to Apple’s iTunes and Google’s Google Play Music and was formerly called Xbox Music.  Peter states that Groove Music is very amenable to allowing developers to create new applications using Groove Music as it offers both an API and an SDK.  The SDK is effectively a wrapper around the raw API.  Peter then shows us a quick demo of some of the nice touches to the API which includes the retrieval of album artwork.  The API allows retrieving album artwork of varying sizes and he shows us how, when requesting a small version of some album artwork that, for example, contains a face, the Groove API will use face detection algorithms to ensure that when dynamically resizing the artwork, the face remains visible and is not cropped out of the picture.

IMG_20151024_140031 The next Grok talk was by John Stovin and was all about a unit testing framework called Fixie.  John starts by asking, Why another unit testing framework?  He explains that Fixie is quite different from other unit testing frameworks such as NUnit or xUnit.  The creator of Fixie, Patrick Lioi, stated that he created Fixie as he wanted as much flexibility in his unit testing framework as he had with other frameworks he was using in his projects.  To this end, Fixie does not ship with any assertion framework, unlike NUnit and xUnit, allowing each Fixie user to choose his or her own Assertion framework.  Fixie is also very simple in how you author tests.   There’s no [Test] style attributes, no using Fixie statements at the top of test classes.   Each test class is simply a standard public class and each test method is simply a public method whos name ends in “Test”.  Test setup and teardown is similar to xUnit in that it simply uses the class constructor and Dispose methods to perform these functions.

IMG_20151024_140406 Interestingly, Fixie tests can inherit from a “Convention” base class which can change the behaviour of Fixie.  For example, a custom convention class can be implemented very simply to alter the behaviour of Fixie to be more like that of NUnit, with test classes decorated by a [TestFixture] attribute and test methods decorated by a [Test] attribute.  Conventions can control the discovery of tests, how tests are parameterized, how tests are executed and also how test output is displayed.

Fixie currently has lots of existing test-runners, including a command-line runner and a runner for the Visual Studio test explorer.  There’s currently a plug-in to allow ReSharper 8 to run Fixie tests, and a new plug-in/extension is currently being developed to work with ReSharper 10.  Fixie is open-source and is available on GitHub.

After John’s talk, we had the final Grok Talk of the lunch time, which was Steve Higgs’s ES6 Right Here, Right Now.  Steve’s talk is how developers can best use and leverage ES6 (ECMAScript 6 aka JavaScript 2015) today.  Steve starts by stating that, contrary to some beliefs, ES6 is no longer the “next” version of JavaScript, but is actually the “current” version.  The standard has been completely ratified, but most browsers don’t yet fully support it.

Steve talks about some of the nice features of ES6, many of which had to be implemented with 3rd-party libraries and frameworks.  ES6 has “modules” baked right in, so there’s no longer any need to use a 3rd-party module manager.  However, if we’re targeting today's browsers and writing JavaScript targeting ES5, we can use 3rd-party libraries to emulate these new ES6 features (for example, require.js for module management).

Steve continues by stating that ES6 will now (finally) have built-in classes.  Unfortunately, they’re not “full-featured” classes like we get in many other languages (such as C#, Java etc.) as they only support constructors and public methods, and have no support for things like private methods yet.  Steve does state that private methods can be “faked” in a bit of a nasty, hacky way, but ES6 classes definitely do not have support for private variables.  Steve states that this will come in the future, in ES7.

ES6 gets “arrow functions”, which are effectively lambda functions that we know and love from C#/LINQ, for example:

var a = [
  "Hydrogen",
  "Helium",
  "Lithium",
  "Beryl­lium"
];

// Old method to return length of each element.
var a2 = a.map(function(s){ return s.length });

// New method using the new "arrow functions".
var a3 = a.map( s => s.length );

Steve continues by stating that ES6 introduces the let and const keywords.  let gives a variable block scoping rather than JavaScript’s default function scoping.  This is a welcome addition, and helps those of us who are used to working with languages such as C# etc. where our variable scoping is always block scoped.  const allows JavaScript to declare a constant.

ES6 now also has default parameters which allow us to define a default value for a function’s parameter in the event that code calling the function does not supply a value:

function doAlert(a=1) {
    alert(a);
}

// Calling doAlert without passing a value will use the
// default value supplied in the function definition.
doAlert();

Steve also mentions how ES6 now has string interpolation, also known a “template strings”, so that we can finally write code such as this:

// Old way of outputting a variable in a string.
var a = 5;
var b = 10;
console.log("Fifteen is " + (a + b) + " and\nnot " + (2 * a + b) + ".");

// New ES6 way with string interpolation, or "template strings".
var a = 5;
var b = 10;
console.log(`Fifteen is ${a + b} and\nnot ${2 * a + b}.`);

One important point to note with string interpolation is that your string must be quoted using backticks (`) rather than the normal single-quote (‘) or double-quote (“) characters!  This is something that will likely catch a lot of people out when first using this new feature.

Steve rounds off his talk by stating that there’s lots of other features in ES6, and it’s best to simply browse through them all on one of the many sites that detail them.  Steve says that we can get started with ES6 today by using something like babeljs.io, which is a JavaScript compiler (or transpiler) and allows you to transpile JavaScript code that targets ES6 into JavaScript code that is compatible with the ES5 that is fully supported by today’s browsers.

After Steve’s talk, the Grok Talks were over, and with it the lunch break was almost over too.  There was a few minutes left to head back to the communal area and grab a cup of coffee and a bottle of water to keep me going through the two afternoon sessions and the two final sessions of the day.

IMG_20151024_143656 The first session of the afternoon was another change to the advertised session due to the previously mentioned cancellations.  This session was Pete Smith’s Beyond Responsive Design.  Pete’s session was aimed at design for modern web and mobile applications.  Pete starts with looking at a brief history of web development.  He says that the web started solely on the desktop and was very basic at first, but very quickly grew to become better and better.  Eventually, the Smartphone came along and all of these good looking desktop websites suddenly didn’t look so good anymore.

So then, Responsive Design came along.  This attempted to address the disconnect and inconsistencies between the designs required for the desktop and designs required for the mobile.  However, Responsive Design brought with it it’s own problems.  Our designs became awash with extensive media queries in order to determine which screen size we were rendering for, as well as became dependent upon homogenous (and often large) frameworks such as Zurb’s Foundation and Bootstrap.  Pete says that this is the focus of going “beyond” responsive design.  We can solve these problems by going back to basics and simplifying what we do.

So, how do we know if we've got a problem?  Well, Pete explains that there are some sites that work great on both desktop and mobile, but overall, they’re not as widespread as we would like given where we are in our web evolution.  Pete then shows some of the issues.  Firstly, we have what Pete calls the "teeny tiny" problem.  This is  where the entire desktop site is scaled and shrunk down to display on the smaller mobile screen size.  Then there's another problem that Pete calls "Indiana’s phone and the temple of zoom" which is where a desktop site, rendered on a mobile screen, can be zoomed in continuously until it becomes completely unusable.

Pete asks “what is a page on today’s modern web?”  Well, he says there’s no such thing as a single-page application.  There’s really no difference between SPA’s and non-SPA sites that use some JavaScript to perform AJAX requests to retrieve data from the server.  Pete states that there’s really no good guiding design principles.  When we’re writing apps for Android or iOS, there’s a wealth of design principles that developers are expected to follow, and it’s very easy for them to do so.  A shining example of this is Google’s Material Design.  When we’re designed for the web, though, not so much.

Dynamic-Data-Maksing-IbizaSo how do we improve?  Pete says we need to “design from the ground up”.  We need to select user-interface patterns that work well on both the desktop and on mobile.  Pete gives examples and states that UI elements like modal pop-ups and alerts work great on the desktop, but often not so well on mobile.  An example of a UI pattern that does work very well on both platforms are the “panes” (sometimes referred to as property sheets) that slide in from the side of the screen.  We see this used extensively on mobile due to the limited screen real estate, but not so much on the desktop, despite the pattern working well here also.  A great example of effective use of this design pattern is the new Microsoft Azure Preview Portal.  Pete states we should avoid using frameworks like Bootstrap or Foundation.  We should do it all ourselves and we should only revert to “responsive design” when there is a specific pattern that clearly works better on one medium than another and where no other pattern exists that works well on all mediums.

At this point in the talk, Pete moves on to show us some demo code for a website that he’s built to show off the very design patterns and features that he’s been discussing.  The code is freely available from Pete’s GitHub repository.  Pete shows his website first running on a desktop browser, then he shows the same website running on an iPad and then on a Smartphone.  Each time, due to clever use of design patterns that work well across screens of differing form factors, the website looks and feels very similar.  Obviously there are some differences, but overall, the site is very consistent.

Pete shows the code for the site and examines the CSS/LESS styles.  He says that absolute positioning for creating these kind of sites is essential.  It allows us to ensure that certain page elements (i.e.. the left-hand menu bar) are always displayed correctly and in their entirety.  He then shows how he's used CSS3 transforms to implement the slide in/out panels or “property sheets”, simply transforming them with either +100% or -100% of their horizontal positioning to display to the left or right of the element’s original, absolute position.  Pete notes how there’s extensive use of HTML5 semantic tags, such as <nav> <content> <footer> etc.  Pete reminds us that there’s no real behaviour attached to using these tags but that they make things far easier to reason about than simply using <div> tags for everything.

Finally, Pete summarises and says that if there’s only one word to take away from his talk it’s “Simplify”.  He talks about the future and mentions that the next “big thing” to help with building sites that work well across all of the platforms that we use to consume the web is Web Components.  Web Components aid encapsulation and re-usability.  They’re available to use today, however, they’re not yet fully supported.  In fact, they are only currently supported in Chrome and Opera browsers and need a third-party JavaScript library, Polymer.js, in order to work.

IMG_20151024_155657 The final session of the day was Richard Fennell’s Monitoring and Addressing Technical Debt With SonarQube.

Richard starts his session by defining technical debt.  He says it’s something that builds up very slowly, almost sneaks up on you.  It’s the little “cut corners” of our codebases where we’ve implemented code that seems to do the job, but is sub-optimal code.  Richard says that technical debt can grow to become so large that it can stop you in your tracks and prevent you from progressing with a project.

He then discusses the available tools that we currently have to address technical debt, but specifically within the world of Microsoft’s tooling.  Well, firstly we have compiler errors.  These are very easy to fix as we simply can’t ship our software with compiler errors, and they’ll provide immediate feedback to help us fix the problem.  Whilst compiler errors can’t be ignored, Richard says that it’s really not uncommon to come across projects that have many compiler warnings.  Compiler warnings aren’t errors as such, and as they don’t necessarily prevent us from shipping our code, we can often live with them for a long time.  Richard mentions the tools Visual Studio Code Analysis (previously known as FXCop) and StyleCop.  Code Analysis/FxCop works on your compiled code to determine potential problems or maintenance issues with the code, whilst StyleCop works on the raw source code, analysing it for style issues and conformance against a set of coding standards.  Richard says that both of these tools are great, but offer only a simple “snapshot in time” of the state of our source code.  What we really need is a much better “dashboard” to monitor the state of our code.

Richard asks, “So what would Microsoft do?”.  He continues to explain that the “old” Microsoft would go off and create their own solution to the problem, however, the “new” Microsoft, being far more amenable to adopting already-existing open source solutions, has decided to adopt the existing de-facto standard solution for analysing technical debt, a product called SonarQube by SonarSource.

Richard introduces SonarQube and states that, firstly, we must understand that it’s a Java based product.  This brings some interesting “gotchas” to the .NET developers when trying to set up a SonarQube solution as we’ll see shortly.  Richard states that SonarQube’s architecture is based upon it having a backend database to store the results of its analysis, and it also has plug-in analyzers that analyze source code.  Of course, being a Java-based product, SonarQube’s analyzers are written in Java too.  The Analyzers examine our source code and create data that is written into the SonarQube database.  From this data, a web-based front-end part of the SonarQube product can render a nice dashboard of this data in ways that help us to "visualise" our technical debt.   Richard points out that analyzers exist for many different languages and technologies, but he also offers a word of caution.  Not all analyzers are free and open source.  He states that the .NET ones currently are but (for example) the COBOL & C++ analyzers have a cost associated with them.

Richard then talks about getting an installation of SonarQube up and running.  As it’s a Java product, there’s very little in the way of nice wizards during the installation process to help us.  Lots of the configuration of the product is performed via manual editing of configuration files.  Due to this, Microsoft’s ALM Rangers group have produced a very helpful guide to help in installing the product.  The system requirements for installing SonarQube are a server running either Windows or Linux with a minimum of 1GB of RAM.  The server will need to have .NET framework 4.5.2 installed also, as this is required by the MSBuild runner which is used to run the .NET analyzer.  As it’s a Java product, obviously, Java is required to be installed on the server – either Oracle’s JRE 7 (or higher) or OpenJDK 7 (or higher).  For the required backend database SonarQube will, by default, install a database called H2, however this can be changed (and probably should be changed) to something more suited to .NET folks such as Microsoft’s SQL Server.  It’s worth noting that the free SQL Server Express will work just fine also.  Richard points out that there are some “gotchas” around the setup of the database, too.  As a Java-based product, SonarQube will be using JDBC drivers to connect to the database, and these place some restrictions on the database itself.  The database must have it’s collation set to Case Sensitive (CS) and Accent Sensitive (AS).  Without this, it simply won’t work!

After setup of the software, Richard explains that we’ll only get an analyzer and runner for Java source code out-of-the-box.  From here we can download and install the analyzer and runner we’ll need for analyzing C# source code.  He then shows how we need to add a special file called sonar-project.properties to the root of our project that will be analyzed.  This file contains four key values that are required in order for SonarQube to perform it’s analysis.  Ideally, we’d set up our installation of SonarQube on a build server, and there we’d also edit the SonarQube.Analyzers.xml file to reflect the correct database connection string to be used.

image3 Richard now moves onto showing us a demo.  He uses the OWASP demo project, WebGoat.NET for his demonstration.  This is an intentionally “broken” ASP.NET application which will allow SonarQube to highlight multiple technical debt issues with the code.  Richard shows SonarQube being integrated into Visual Studio Team Foundation Server 2015 as part of its build process.  Richard further explains that SonarQube analyzers are based upon processing complete folders or wildcards for file names.  He shows the  default SonarQube dashboard and explains how most of the errors encountered can often be found in the various “standard” libraries that we frequently include in our projects, such as jQuery etc.  As a result of this, it’s best to really think about how we structure our solutions as it’s beneficial to keep third-party libraries in folders separate from our own code.  This way we can instruct SonarQube to ignore those folders.

Richard shows us the rules that exist in SonarQube.  There are a number of built-in rules provided by SonarQube itself, but the C# analyzer plug-in will add many of it’s own.  These built-in SonarQube rules are called the “Sonar Way” rules and represent the expected Sonar way of writing code.  These are very Java-centric so may only be of limited use when analyzing C# code.  The various C# rule-sets are obviously more aligned with the C# language.  Some rules are prefixed with “CA” in the rule-set list and these are the FxCop rules, whilst other rules are prefixed with “S” in the rule-set list.  These are the C# language rules and use Roslyn to perform the code analysis (hence the requirement for the .NET framework 4.5.2 to be installed)

Richard continues by showing how we can set up “quality gates” to show if one of our builds is passing or failing in quality.  This is an analysis of our code by SonarQube as part of a build process.  We can set conditions on the history of the analyses that have been run to ensure that, for example, each successive build should have no more than 98% of known bugs of the previous release.  This way, we can reason that our builds are getting progressively better in quality each time.

Finally, Richard sums up by introducing a new companion product to SonarQube called SonarLint. SonarLint is based upon the same .NET Compiler platform, Roslyn, that provides SonarQube’s analysis, however SonarLint is designed to be run inside the Visual Studio IDE and provides near real-time instant feedback on the quality of our source code as we’re editing it.  SonarLint is open source and available on Github.

IMG_20151024_170210 After Richard’s talk was over, it was time for all of the conference attendees to gather in the main lecture hall for the final wrap-up presentation.  During the presentation, the various sponsors were thanked for all of their support.  The conference organisers did also mention how there had been a number of “no-shows” to the conference (people who’d registered to attend but had not shown up on the day and hadn’t cancelled their tickets despite repeated communication requesting people who can no longer attend to do so).  The organisers told us how every no-show not only costs the conference around £15 per person but also prevents those who were on the waiting list from being able to attend, and there was quite an extensive waiting list for the conference this year.  Here’s hoping that future DDD Conferences have less no-shows.

It was mentioned that DDD North is now the biggest of all of the regional DDD events, with some 450 (approx.) attendees this year – a growth on last year’s numbers – with still over 100 more people on the waiting list.  The organisers told us that they could, if it weren’t for space/venue size considerations, have run the conference for around 600-700 people.  That’s quite some numbers and shows just how popular the DDD conferences, and especially the DDD North conference, are.

2015-11-02 14_35_26-Technical whizzes set to share expertise in Sunderland - Sunderland EchoOne especially nice touch was that I did receive a quick mention from Andy Westgarth, the main organiser of DDD North, during the final wrap-up presentation for the use of one of my pictures for an article that had appeared in the local newspaper, the Sunderland Echo, that very day.  The picture used was one I’d taken in the same lecture hall at DDD North 2013, two years earlier.  The article is available to read online, too.

After the wrapping up came the prize draw.  As always, there was some nice prizes to be given away by both the conference organisers themselves as well as prizes to be given away by the individual sponsors including a Nexus 9 tablet, a Surface Pro 3 and a whole host of other goodies.  As was usual, I didn’t win anything, but I’d had a fantastic day at yet another superb DDD North.  Many thanks to the organisers and the various sponsors for enabling such a brilliant event to happen.

IMG_20151024_175352 But…  It wasn’t over just yet!   There is usually a “Geek Dinner” after the DDD conferences however on this occasion there was to be a food and drink reception graciously hosted by Sunderland Software City.  So as we shuffled out of the Sunderland University campus, I headed to my car to drive the short distance to Sunderland Software City.

IMG_20151024_175438 Upon arrival there was, unfortunately, no pop-up bar from Vaux Brewery as there had been two years prior.  This was a shame as I’m quite partial to a nice pint of real ale, however, the kind folks at Sunderland Software City had provided us with a number of buckets of ice-cold beers, wines and other beverages.  Of course, I was driving so I had to stick to the soft drinks anyway!

I was one of the first people to arrive at the Sunderland Software City venue as I’d driven the short distance from the University to get there, whereas most other people who were attending the reception were walking from the University.  I grabbed myself a can of Diet Coke and quickly got chatting to some fellow conference attendees sharing experiences about our day and getting to know each other and finding out what we do for a living and all about the work we do.

IMG_20151024_180512 Not too long after getting chatting, a few of the staff from the Centre were scurrying towards the door.  What we soon realised was that the “food” element of the “food & drink” reception was arriving.  We were being treated to what must be the single largest amount of pizza I’ve ever seen in one place.  76 delicious pizzas delivered from Pizza Hut!  Check out the photo of this magnificent sight!  (Oh, and those boxes of pizza are stacked two deep, too!)

So, once the pizzas had all been delivered and laid out for us on the extensive table top, we all got stuck in.  A few slices of pizza later and an additional can of Diet Coke to wash it down and it was back to mingling with the crowd for some more networking.

Before leaving, I managed to have a natter with Andy Westgarth, the main conference organiser about the trials and tribulations of running a conference like DDD North.  Despite the fact that Andy should be living and working in the USA by the time the next DDD North conference rolls around, he did assure me that the conference was in very safe hands and should continue on next year.

After some more conversation, it was finally time for me to leave and head off back to my in-laws in Newcastle.  And with that another superb DDD North conference was over.  Here’s looking forward to next year!