Weeknote 8: Auld Lang Syne (Weeks 50-52, 2017)

The weekly weeknotes are no more… whilst it’s great to catalogue my life, each post was taking 3-4 hours to write (and much of it is on Twitter anyway!). Even in chunks through the week, that’s a lot of time, though I seem to have found another time hoover to fill the void (edging out Twitter): Sim City.

I may still publish some weeknotes in 2018… they might just be in a slightly different format…

So, what’s been happening over the last 3 weeks?

Locked on a train

(After the train arrived at its destination, I sent an email and used the lavatory before getting off but it seems the Train Manager was more keen to get away than I was…)

Azure subscription woes (caused by credit card changes)

A withdrawn credit card led to some worrying emails about my (PAYG) Azure subscription potentially being disabled.

Although this article says:

“When you update the payment method, Azure immediately processes the payment for all outstanding charges”

it needed a support call to get Microsoft to charge the card and put my account back where it should have been…

Some tweets I liked

On ideas/new technology…

On software maintenance…

And a couple I didn’t

Exhibit A:

Exhibit B:

Photography

I got the chance to give a presentation about architecture (in IT form) to some colleagues, which let me draw parallels with architecture (in construction form). That gave me a reason to pull together some images from a visit to Kings Cross and St Pancras International last summer:

I like this image of my son’s (frozen) bike loaded on my car ready for a morning or cyclocross racing:

And then I got creative on Christmas Eve:

Could there be a correlation between the alcoholic theme and my being relaxed enough to see photographic opportunity?

A trip to York

My family had a little mini-break in York between Christmas and New Year (narrowly avoiding the chaos that ensues when the UK gets a few centimetres of snow…). Of course, being on holiday inevitably led to more photos:

This was disappointing though (especially on a bright sunny day with no sign of ice):

This wasn’t – it was a great recommendation from Brian Cain (@BrianCainUC)!

More alcohol…

I didn’t try this place but I will if I’m back in York again…

I really liked this cafe:

And I finished the trip by going full-circle…

Incidentally, the collages are created using Diptic on iOS.

A bit of DIY

Happy New Year

So, that wraps up another year – and 2018 will be this blog’s 15th! Thanks to everyone who keeps reading and who follows me on other social media channels. I wish you all the very best and a happy new year!

Microsoft SQL Server overview

I wrote this post a few months ago… and it crashed my blog. Gone. Needed to be restored from backup…

…hopefully this time I’ll have more luck!

One of the advantages of being in the MVP Reconnect programme is that I occasionally get invited to webcasts that open my eyes to technology I’ve not had a lot to do with previously. For many years, one of the big holes in my knowledge was around Microsoft SQL Server. That was until I saw Brian Kelley (@kbriankelley)’s “Brief overview of SQL Server”. The content’s not restricted, so I thought I’d republish some of it here for others who are getting their head around the major on-premises components of the Microsoft Data Platform.

SQL Server Editions

There are several editions of SQL Server available and these are the key differences (updated for 2017):

  • Express Edition (previously known as MSDE) is a free version, with some limitations around database size, etc.
  • Standard Edition lacks some enterprise features but has high availability and suits many application workloads.
  • Enterprise Edition is the full functionality product (but can be expensive)
  • Developer Edition (not licenced for use in production) offers the full feature set but can also run on a client operating system whereas enterprise will only run on server-based operating systems
  • Web Edition has reduced functionality and is intended for public websites (only available to service providers)
  • Compact Edition is another free version, intended for embedded databases in ASP.NET websites and Windows desktop applications

Although SQL Server is often thought of as an RDBMS product, it’s really a suite of systems, under the SQL Server name. Usually that means the database engine but there are many parts, each of which has a distinct setup (i.e. you don’t need the database service for SQL Server Analysis Services and vice versa).

SQL Server Analysis Services (SSAS)

SSAS (since 2007) is an online analytical and transaction processing (OLAP) tool intended for data warehousing and data mining.

One advantage of OLAP is to run jobs during the night for pre-generated calculations (used for roll-ups – e.g. totals and averages, etc.). It can provide fast results to business users who would otherwise need complex calculations in a transactional system (e.g. sales data based on region, month, quarter, etc. can be done ahead of time).

SSAS is comparable to IBM Cognos or Oracle Essbase (normally packaged with Hyperion for accounting, etc.).

Some SSAS jargon includes:

  • Star schema/snowflake schema – database design differs from transactional design. You can do these things in RDBMS but use SSAS on top.
  • Cubes
  • Dimensions
  • Tabular model
  • Data analysis expressions (DAX) – a language to do things in SSAS

SQL Server Integration Services (SSIS)

SSIS (since 2005) is heavily used for extract, transform and load (ETL) workloads – i.e. to get data from a source, manipulate it and pass it to a destination. It can be used to build a data warehouse, then data marts or to move data between systems. Basically, it’s a back-end batch processing system that performs the data mining.

SSIS is a replacement for Data Transformation Services (DTS). It’s not limited to SQL Server for source/destination so can talk to Oracle, Excel spreadsheets, other ODBC connections, etc.

The drag and drop interface is very powerful with the full functionality and flexibilityof Microsoft.NET behind it.

SSIS is comparable with Informatica (or Clover, etc.).

Some SSIS jargon includes:

  • Packages (whatever is processing, contains all the logic)
  • Tasks (what’s being carried out)
  • Dataflow tasks (how you go from source to destination – could be multiples)
  • Transformation (manipulating data)
  • Business Intelligence Markup Language (BIML)

SQL Server Reporting Services (SSRS)

SSRS was introduced 2005 and became so popular it was ported back to SQL Server 2000!
It is a reporting engine, used to publish reports in-browser. Early versions were built on IIS but since 2008, SSIS has run directly on http.sys.

SSRS can be integrated with SharePoint (for report security based on SharePoint security) or the native, standalone mode is browser-based to look at folders, find reports, and run a report with parameters. Used to print via ActiveX control but now (since 2016) prints to PDF (or opens with a PDF reader).

There are two ways to build reports: Report Builder (a web-side interface for BA-type power user) or Report Designer (a full product for complex designs). There is also a subscription capability so users can subscribe to reports.

SSRS can be compared with IBM Business Objects and Tableau.

SSRS jargon includes:

  • Reports
  • Data sources
  • Datasets
  • ReportServer (API to integrate with other products)
  • Native mode vs. integrated mode (SharePoint)

SQL Server Database Engine

The SQL Server database engine is what most people think of when SQL Server is mentioned.
It is traditionally a relational database management system (RDBMS) although it now contains many other database capabilities. It was originally derived from a Sybase product (until SQL Server 6.5).

SQL Server supports both multiple databases per instance (which can connect and join across) and multiple instances per server (from 2000) – the first is a default instance, then named instances can be created.

SQL Server uses a SQL language variant called T-SQL to interact. A GUI is provided in SQL Server Management Studio but it’s also possible interact via PowerShell.

SQL Server also has a scheduler (the SQL Server Agent), which can alert on success/failure and allows the creation of elaborate scheduling routines with notifications and the ability to run code. It is comparable with IBM DB2, Oracle, PostgreSQL, Sybase, MySQL and MariaDB.

SQL Server 2016 features include:

  • High availability options, including Always On failover clusters; Always On availability groups (which are more flexible because they don’t have to replicate and fail over everything); Database mirroring (one database on multiple systems; deprecated now in favour of availability groups); log shipping.
  • Several encryption options including built-in (certificate, asymmetric keys, symmetric keys); Enterprise Edition also has Transparent Data Encryption (TDE) to encrypt database at rest and stop copies of the database from being loaded elsewhere; connection encryption (SSL/TLS since 2005); Always Encrypted is new for 2016 (transparent to the application and to SQL Server) – data stored in encrypted form within the database.
  • SQL Server and Windows authentication (server or Active Directory). Can have Windows or both, but not just SQL Server-based logins.
  • Replication options to move data between servers.

Other security features include audit objects (who did what?); granular security permissions; login auditing (failed logins are written to the SQL Server Error Log text file and to the application event log); dynamic data masking (depending on who needs to see it – e.g. store social security numbers and only show part of the data; only obfuscation as data is still in clear text); row-level security (to filter rows).

Each new version brings performance enhancements, e.g. columnstore indexes, in-memory OLTP tables, query optimisation.

New Technologies in 2016 include:

  • JSON support. Query and return data in JSON format. Administrators have been able to use SOAP and XML since 2005 but this is now deprecated in favour of JSON (which is popular for RESTful systems).
  • Master data services.
  • Polybase (not to be confused with a clustering solution – it’s about talking to other data sources, e.g. Hadoop, Cloudera and Azure storage, to be expanded to include Oracle, Teradata, Mongo, Spark and more).
  • R Services/R Server (R within the database and also R Server for data science/big data queries).

2017 builds on 2016 to include:

  • Linux and Docker support. Starting with SQL Server 2017, SQL Server is available for either Windows or Linux systems and it’s available as an installable application or for Docker containers.
  • SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to R.

There are many more features in the Microsoft documentation but these are the most significant updates.

But what about the cloud?

This post provided a quick run-down of some of the major on-premises SQL Server components but, just as with Microsoft’s other products, there are cloud alternatives too. I’m planning a follow-up post to cover these so watch this space!

Weeknote 7: Traffic, snow and an awesome party (Week 49, 2017)

Another week means another weeknote and this one finished on a high because last night was the risual Christmas Party!

risual does Christmas parties rather well and I won’t go into the details here but suffice to say, a good time was had. I knew the weather forecast though and needed to be sober today (Mrs W wasn’t too happy about the idea of driving home in the snow) so I can bask in the delight of not-having-a-hangover.

Anyway, it was a joy to wake up in a Country House hotel this morning and see how the landscape was transformed. I took quite a lot of photos on my iPhone but Dropbox is currently refusing to upload them for me, complaining that one is corrupt (but not telling me which one!). I’ll edit them next week and post them then…

Then I drove home in the snow and ice. England can’t cope when it snows (or indeed with any other extremes of weather: too wet; too sunny; too windy). We do mild-grey really well though.

The week

My week was the usual mix of consulting (paid work), training and pre-sales. I was fortunate to spend a day at Microsoft in Reading on Tuesday, topping up my Microsoft 365 (Office 365, plus Windows 10, plus Enterprise Mobility + Security) knowledge. I’m hoping that, time permitting, some blog posts will come out of that.

I’ve been driving around the UK almost 30 years, and driving for work for most of them. This week drove home to me (excuse the pun) just how overloaded the UK’s road infrastructure is, when it took me more than 5 hours to drive to Reading and back (about 75 miles each way), then around 4.5 hours the next day to drive 190 miles on nothing but motorways and trunk A roads (180 miles of which were dual carriageway). It seems likely that the first place autonomous vehicles can take a hold is on major routes like this and I for one can’t wait. Maybe one day we’ll have segregated autonomous driving lanes on the motorways, where the cars can drive closer together (in constant communication and not constrained by human reaction speeds) and maybe even faster (if the law is changed).

This week also saw the quarterly Milton Keynes Geek Night, which I’m proud to have attended every one of! I’ve made a lot of contacts over the 5½ years it’s been running – some of whom I can now count as friends and this event had one of the best set of talks in a long while including:

  • Simon Collison (@colly) on “The Internet of Natural Things”
  • Laura Sutton (@L_Coull)’s “Non-geek’s guide to the Galaxy”
  • Dr Neil Smith (@neilnjae) showing us “Beatles vs. Rolling Stones: using data science to prove which band is best”!
  • Joe Leech (@MrJoe) wrapping up with “UX, Psychology and the Power of 100”

The next one clashes with my wedding anniversary, and Mrs W has even agreed to come along with me as an honourary geek!

At home

We’re hoping to convert our loft next year and the initial notice has been submitted for planning purposes. It seems that, now that councils are better at sharing information, marketers are scraping it to bombard us with offers of storage, home improvements, etc. Apparently, our neighbours will be getting mailshots from lawyers too… all very irksome…

Around town

I missed this event which was part of Milton Keynes’ 50th birthday celebrations – a floating carnival of decorated boats looks like quite a spectacle on the canal!

It’s really noticeable how many people are now sleeping rough on the streets of Milton Keynes. The Bus Shelter are trying to do something about that – but they need to raise funds to convert a double-decker coach to provide emergency shelter. To quote from their website:

“Homelessness can hit anyone – most of us are just three pay packets away from losing our home. With your help we can provide over 5800 safe, warm nights for people forced to sleep on the streets and help them find a positive future.”

Look out for the bus in the Intu part of the Shopping Centre (Midsummer Place) and please donate, if you can:

At the other end of the scale… I passed a man on the mean streets of MK proudly proclaiming that the best place to get a car parking space is the electric charging bays… it may not be illegal to park a petrol/diesel car in an electric space but it is selfish (especially as there are normal spaces free)…

Other stuff

Barclaycard sent me a new credit card “for security reasons”. It was nice to find that my Apple Wallet updated automatically on my phone and my watch. Sadly the many websites where I had the details stored for recurring payments (Apple iTunes, Microsoft, Amazon, M6 Toll, Transport for London, etc.) didn’t.

That reminds me… I wonder when my Curve card will show up… It seems I’ve been stuck with about 1500 people ahead of me in the queue for weeks now… in fact, the number seems to be going up (but if you use my referral link above, I can move back up the queue…)!

On the topic of referrals, my energy switch to Bulb completed this week. It was painless (though I will have to wait to get my credit from First Utility, my previous provider – who seem more interested in selling broadband to me now than reducing my energy bills…). Unfortunately, neither I nor my friend have received our promised referral credits from Bulb. Enquiries are ongoing…

My son and I needed to force-quit an app on my Amazon Fire TV Stick but weren’t sure how. This blog post helped by pointing us to Menu, Settings, Manage All Installed Applications where Force Stop is one of the options.

Sadly, Amazon and Google’s inability to play together nicely means my Fire TV Stick won’t play YouTube videos from 1 Jan 2018. One is not amused:

I’ve been watching Channel 4’s series about Donald Trump’s rise (An American Dream). I’ll leave the politics aside but it’s fascinating to see how the wealthy can grow to take such a position of power…

I noticed that my Nextbase Dashcam was showing the wrong time (1 hour ahead) and every time I changed it, it reverted after the next power cycle… then I realised there was a timezone setting and it was still on GMT+1 (BST). After changing to GMT, all was good. It seems that it picks up the time from the GPS, so the timezone is the important setting…

I’m torn about the use of the new HEIC image format on my iPhone. On the one hand, I want to store the best quality images I can, on the other, I need them to be readable on all my devices

Whilst I was at MK Geek Night this week, I spotted that War Horse is coming to Milton Keynes Theatre next autumn. I enjoyed the film and I’ve heard good things about the National Theatre production too, so tickets are booked for a family cultural treat:

My phone has decided that, when I get in the car, I’m headed for the local Tesco Express… that’s a little worrying (I wonder how does Siri determine my travel patterns?):

Podcasts

I’ve mentioned before that I listen to a lot of podcasts. One of these is the Microsoft Cloud Show. Sometimes, it can be a bit too developer-focused for my tastes but I enjoyed Episode 223, which pretty much encapsulates the conversations I have with customers as an Architect working with the Microsoft cloud!

I also got the chance to catch up with Matt Ballantine this week (one half of the WB-40 Podcast). It’s been a while since Matt and I had a chat but I really enjoyed bouncing around our thoughts on modern collaboration and team working. and team-working. Like Matt, I’m finding myself drawing/writing on screen more (at least when working, using a Surface Pro – my MacBook lacks any sort of touch capability) though it’s also showing how unpracticed I’ve become at writing!

Wrap-up

Anyway, before I get too far into Sunday evening I need to sign-off and check my children haven’t continued building their snow-wall across the neighbours’ drives…

Next week looks like it should feature a lot less travel (at least by road) and a return to cyclocross with my son. I might even be home a few evenings and maybe, just maybe, I’ll write a blog post that’s not one of these weeknotes! I live in hope…

Weeknote 6: User group and MVP events; a new smartwatch; ghost trains; and the start of Christmas (Week 48, 2017)

Milton Keynes – Rochdale – London – Leicester. Not quite New York – London – Paris but those are the towns and cities on my itinerary this week.

Every now and again, I find myself counting down the days to the weekend. This week has been different. It was manic, squeezing work in around lots of other activities but it was mostly enjoyable too.

The week at work

My work week started off with an opportunity to input to a report that I find quite exciting. I can’t say too much at the moment (though it should be released within the next couple of weeks and I’ll be shouting about it then) but it’s one of those activities that makes me think “I’d like to do more of this” (I already get referred to as the extra member of the risual marketing team, which I think they mean as a good thing!).

Bills have to be paid though (i.e. I need to keep my utilisation up!), so I’ve also had some consulting in the mix, writing a strategy for a customer who needs to modernise their datacentre.

On Wednesday evening, I managed to fit in a UK Azure User Group (@UKAzure) meeting in London, with Paul Andrew (@MrPaulAndrew) talking about Azure Data Factory – another opportunity to fill some gaps in my knowledge.

Then, back to work on Thursday, squeezing in a full day’s work before heading to the National Space Centre in Leicester in the afternoon for the UK MVP Community Connection. I’m not an MVP anymore (I haven’t been since 2011) but I am a member of the MVP Reconnect Programme, which means I still get invited to some of the events – and the two I’ve been to so far have been really worthwhile. One of my favourite sessions at the last event was Tony Wells from Resource IT (the guys who create the Microsoft Abbreviation Dictionary) talking about storytelling. This time we had a 3-hour workshop with an opportunity to put some of the techniques into practice.

The evening started with drinks in the space tower, then an IMAX film before dinner (and a quiz) in the Space Centre, surrounded by the exhibits. We returned the next day for a Microsoft business update, talks on ethics and diversity, on extending our audience reach and on mixed reality.

Unfortunately, my Friday afternoon was hijacked by other work… and the work week also spilt over into the weekend – something I generally try to avoid and which took the shine off things somewhat…

Social

I’ve had a full-on week with family too: my eldest son is one of six from Milton Keynes who have been selected to attend the Kandersteg International Scout Centre (KISC) in 2019 and, together with ten more who are off to the World Scout Jamboree in West Virginia, we have a lot of fund-raising to do (about £45,000 in total). That meant selling raffle tickets in the shopping centre for the opportunity to win a car on Monday evening, and a meeting on Tuesday evening to talk about fundraising ideas…

So, that’s out every evening, and a long day every day this week… by Friday I was ready to collapse in a heap.

The weekend

No cyclocross this weekend (well, there was, but it clashed with football), so I was on a different sort of Dad duty, running the line and trying not to anger parents from the other team with my ropey knowledge of the offside rule

It’s also December now, so my family have declared that Christmas celebrations can begin. Right from the moment I returned home on Friday evening I was accused of not being Christmassy enough and I was forced to listen to “Christmas Music” on the drive to my son’s football match (the compromise was that it could be my Christmas playlist).

Even I was amused to be followed in my car by a certain jolly chap:

My part in decorating the house consists of getting everything down from the loft, putting up the tree and lights, and then finding myself somewhere to hide for a couple of hours until it all looks lovely and sparkly. Unfortunately, the hiding time was actually spent polishing a presentation for Monday and fighting with Concur to complete my expenses… not exactly what I had in mind…

New tech

A couple of weeks ago, I mentioned that we now have a teenager in the house and my eldest son has managed to save enough birthday money to buy a smartwatch. He was thinking of a Garmin device until I reminded him how bad their software is when we sync our bike computers so he went for a Samsung Gear Sport. It looks pretty good if you have an Android phone. I have an iPhone and an Apple Watch (as you may recall from my recent tales of woe) but if I was an Android guy, I think the Gear Sport would be my choice…

Ghost trains

I forgot to add this tale to last week’s week note but I was travelling back home from Stafford recently when I noticed a re-branded Virgin Pendolino at the platform. My train wasn’t due for another 10 minutes so I didn’t check out where this one was going, so I was a little surprised to pass it again as I arrived in Milton Keynes two hours later, after I’d gone the long way (via Birmingham) and changed trains…

Checking on Realtime Trains showed me that I could have caught a direct train from Stafford, but it wasn’t on the public timetable. Indeed, although it stops at several stations, it’s listed as an empty coaching stock working (which is presumably why it is pathed on the slow lines including the Northampton loop). So, in addition to trains that stop at Milton Keynes only to set down (southbound) or pick up (northbound), it seems that Virgin run “ghost trains” too!

Listening

I listen to a lot of podcasts when I’m in the car. This week I spent a lot of time in the car. I recommend these two episodes:

Twitter highlights

I’m no GDPR expert but this looked useful:

Company branding is great until it makes the information you give out next-to-useless:

Credit is due to the social media team handling the @PremierInn account for Whitbread, they quickly confirmed that it is a J not an I (though I had worked it out).

@HolidayInn were equally on the ball when I complained about a lack of power sockets (and traffic noise insulation) at their Leicester City Centre hotel. Thankfully they replies were limited to Twitter and email – not midnight calls as my colleague Gavin Morrisson found when he tweeted about another Holiday Inn!

This made me smirk (I haven’t “elevated” my Mac yet…):

If you don’t get the joke, this should provide context.

I like this definition of “digital [transformation]”:

This short video looks at how we need to “debug the gender gap”:

The full film is available to stream/download from various sources… I intend to watch.

And, to wrap up with some humour, I enjoyed Chaz Hutton’s latest Post-it sketch:

(for more like this, check out InstaChaz on Instagram)

Finit

That’s it for now… more next week…