Friday, 30 September 2016

SQL Friday: Weekly Round up 30th September

This week has been a relatively relaxed week for me. I had a one-day onsite event in Cardiff on Wednesday for one of Learning Tree's clients. I have been there on several occasions over the summer and each one of these events has been great fun with some good discussions.

Next week is SQL Relay and David Postlethwaite is delivering his SQL Server 2016 new features  along with his azure talk. If you are going, go and say hello.He's at all the events bar Nottingham.

SQL Server

R Programming

The Financial Times uses R for Quantitive Journalism


Contacting/Professional development

Thursday, 29 September 2016

Want to Win a Free @LearningTree Course? #LtreRox - @tangentc is the first ever twitter winner!!!

As regular readers of this blog will know I teach some data courses for Learning Tree. Recently Learning Tree has introduced a new Twitter competition. Attendees current, past and present who tweet about their experiences on their respective Learning Tree courses. What they have learned, key take-away points or if you haven't been on a course yet what you want to learn on a future course. Mark you tweet with the #LtreRox hashtag and also tag @LearningTree in your post and you will be entered into a draw to win a free @learningtree course.

Win a free Learning Tree Course

This week's winner, the first ever winner of this competition is @tangentc. Carolina's winning tweet is here:

Tweet of the first ever winner
The reason behind my excitement, Carolina was an attendee on the course I delivered in London a week back. The course was 534 Developing SQL Queries for SQL Server and Carolina attended via Anyware...She was an online student.

It'll be interesting to see what course Carolina picks as her free course. Ironically i was speaking with Carolina earlier in the week about the 534 course and discussing future courses that she might attend. I'm not a mind a reader but I think she might opt for the 535 Developing High Performance SQL Databases... However if she reads this post, please do let me know what course you picked in the comments.

If you want to be the next winner, you know what to do...get tweeting!

Wednesday, 28 September 2016

Connecting to Twitter with R and RStudio

Social Media and Analytics is always an interesting combination. Twitter has an API that you can so you can connect to  using R and RStudio (and a whole bunch of other languages too) to analyse twitter data.

In this post we will simply look at how we go about connecting to twitter with R and Studio

Assuming you R and R Studio downloaded and installed

You need a Developer Account

Well actually, first up you'll need a twitter account. I'll assume that you have one. If you don't head over to twitter and signup. 

With your twitter account setup the first things first you need to do is create an application in twitter get  access to the Twitter API. This is a simple process. To do this you need to visit signing with your twitter account if you haven't done so already . When you have signed in, click on the create APP button.  

You will see a form called Create an Application. You will need to provide some application details:
  • Name - give your app a name
  • Description - describe the application
  • URL - this can be anything. I used my blog URL
  • Call back URL - I didn't provide this
The first three in the list are mandatory the callback url in optional.

Create a twitter application

Install TwitteR

This is great package for working with Twitter and the Twitter API. You need to install from the CRAN repository. Allow all  the dependant packages to be installed too.

Install twitteR Application
With twitteR installed the next you need to do is connect using R. To do this you need generate some access key from the twitter application that we created above. Go to the application and click on the the Key and Access Token

Access token and secret

API key and secret

The next step is to open up a new session in R Studio. The following code loads the TwitteR and DevTools packages and authenticates using your application keys

#Load libraries

#Authenticate with twitter
api_key <- font="" your_api_key_here="">
api_secret <- font="" your_api_secret_here="">
access_token <- font="" your_access_token_here="">
access_token_secret <- font="" your_access_token_here="">

Then You can use the twitteR package to work with Twitter. The following updates your status. We will look at what else we can do with this package in a later post.

updateStatus("Another Morning of #R...#RStudio and twitteR")

Here's my tweet from RStudio

My tweet from Studio

R and twitteR can be a really useful combination in analysing your twitter feed. We'll look at a practical use for this in a later post.

Tuesday, 27 September 2016

SQL Relay 2016

It less than week to SQL Relay - a week long SQL conference that is visiting Birmingham, Cardiff, Reading, Nottingham and Leeds. 

See if you can persuade your boss to let you out to receive some top notch training from industry experts and speakers.

David will be there speaking on SQL 2016 new features and SQL Azure database

Check out the schedule at

Update: David's session Schedule

Monday 3rd October Birmingham – SQL Server 2016 New Features 9:30

Tuesday 4th October Cardiff – Putting Your head in the Cloud - Working with SQL Azure 12:00

Wednesday 5th October Reading – SQL Server 2016 New Features 9:30

Thursday 6th October Nottingham – David has a Rest Day

Friday 7th October Leeds – SQL Server 2016 New Features 14:30

Friday, 23 September 2016

TGI SQL Friday: Weekly Roundup 23rd September

Its been another Learning Tree week for me this week and a very interesting one at that. I'm on the Learning Tree IAT...IAT stands for Instructor Advisory Team, the team consists of Learning Tree instructors and Learning Tree senior management  and its purpose is to come up with ideas and suggestions to improve how Learning Tree does things and how it does business. A number of great ideas have come out of this so far and the meeting on Monday was also a very productive meeting with further ideas shared, watch this space to see as these ideas get implemented.

I have also been teaching an onsite for a client in Warwick, a rather pleasant part of the world. I was teaching the 534 Developing SQL Queries for SQL Server

534 Developing SQL Queries
This is one of my favourite courses and the class this week has been great, lots of great questions and a really great students.

What I've been reading this week. Well we've had a few posts this week show casing David postlethwaite's work including

SQL Server 

How to setup Kerberos correctly has David's talk Taming the Beast: Kerberos for the SQL DBA

David has also produced an ebook/white paper that is still in draft form about setting up an Oracle/Linux virtual machine on Hyper-V David would appreciate some feedback on this paper. You can follow the link to download

SQL Server Management Studio 16.4 has been released

Power BI for Data Modelling by Adam Aspin

SQL Server Career: Gaining Experience By Gethyn Ellis (Learning Tree Newsletter)


Analyzing Stack Overflow questions and tags with the StackLite dataset By Marco Pasin

Forecasting Opportunities By quintuitive

Presidential Election Predictions 2016 (an ASA competition) By Jo Hardin


Backlog Refinement Takes you From Vision to Value  by Ellen Gottesdiener and Jeff Sutherland


Thursday, 22 September 2016

Have you Upgraded From SQL Server 2005?

My first Learning Tree SQL Server Newsletter that was sent out back in August was titled SQL Server 2016: Keeping up with Technology.  In that post I talked about the reasons why some people/organisations may have been or are reluctant to upgrade to the latest and greatest version of SQL Server, which at this time is SQL Server 2016. In that post I touched on the fact I have clients who still have old versions of SQL Server such as  SQL Server 2000 and SQL Server 2005 instances running in production, even though both versions are now well and truly out of support with Microsoft.

Does your SQL Server estate need a refresh?

SQL Server 2005 went out of support with Microsoft in April this year (2016). I encourage all my clients, and also, I would encourage anyone reading this blog who have SQL Server 2005 instances to upgrade. I'm sure most have done so already but there will be some SQL Server 2005 instances out there in the wild. If you need any help or assistance with the migration/upgrade process check out the Consulting page and feel free to get in touch.

The reason for this blog today was prompted by an email I got from a Learning Tree account manager asking me which courses cover Availability Groups, a client of his  was asking about a suitable course. Learning Tree's 141 course SQL Server High Availability which covers, unsurprisingly as the title suggest, the high availability technology available in SQL Server including Availability Groups.

The other course that covers availability is 2105 SQL Server Database Administration Skills Upgrade course. This course focuses on the 'new' features introduced in SQL Server 2012, while SQL Server 2016 may be the latest and greatest  version if you are running SQL Server 2005 or even SQL Server 2008/R2 then 2105 SQL Server Database Administration Skills Upgrade course will still be of benefit to you...It is actually running in North America this Autumn. Its running in Toronto starting on October 26th.  You can attend in class or online. It also running in New York in November 16th to18th. If you are based in the UK or Europe and don't fancy the flight or it's just not possible to travel...You can attend online, it'll run from 2pm until 9.30pm UK time. So you can do work in the morning if want to!

Tuesday, 20 September 2016

How to Setup Kerberos Correctly

David was in Copenhagen this weekend delivering his Kerberos talk Taming the Beast: Kerberos for the SQL DBA to SQL Saturday Denmark. I have had a quick chat with him via email since he got back and he said he had a great time. The event was very well attended with 280+ attendees and his talk was well attended.

I think David is planning submitting a few sessions to SQL Saturday events in Europe in the next few months so look out for him there and we'll keep you posted as to his whereabouts when schedules get finalised later in the year.

David has pre-recorded his Kerberos talk. You can watch on you tube and I have also embedded it in this post if you want to see what his kerberos talk  covers...

If we can help you with a SQL Sever problem visit our SQL Server Consulting page or contact us

Monday, 19 September 2016

Building Oracle 12c on Linux 7 on Hyper-V

David has written a white paper, or short ebook titled building oracle 12C on Linux 7 VM residing on Hyper-V. The white paper is in draft and has been tested but David would welcome further feedback.

As with everything on this site...The owner of will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

Basically don't do this for production, it might not work for you...although the process works for us we would appreciate any feedback in the comments section.

Oracle and Linux on Hyper-V

You can download the pdf here.

Friday, 16 September 2016

TGI SQL Friday - 16th September 2016 Weekly roundup

I have had an interesting week teaching Learning Tree course 534 Developing SQL Queries for SQL Server. I had an interesting group from a variety of technical background all wanting to improve their skills in writing queries. Today's the last day, we have had some great questions as the week has gone on and no doubt we'll get some further questions today. The answers to some of these question will likely make for a blog post or two down the road.

534 Developing SQL Queries for SQL Server
534 Developing SQL Queries for SQL Server

Anyway, here's what I have been reading this week.

SQL Server

Cool new tools from called Paste the Plan

Program Management

Thursday, 15 September 2016

Great Value IT Training - Awesome ITIL offers

This week's Great Value IT Offers are in. If you want to your ITIL learning on this autumn we have some great deals for you :

995 ITIL® Intermediate: Operational Support and Analysis    3rd October 16
252 Strategic Planning and Implementation                  5th October 16
973 Programming C# Extended Features                 11th October 16
997 ITIL® Intermediate: Planning, Protection and Optimisation 10th October 16
925 SQL Programming Language Introduction                 26th October 16
280 Finance and Accounting for Nonfinancial Managers        16th November 16

If you are interested in attending any of the courses listed send me an email and we'll get you signed up

Tuesday, 13 September 2016

Top 5 Books for Database Developers

My Top 5 series of books has proved very popular. The first post  titled Top 5 books for Enterprise Architecture was the blogs most popular post in August. My post last week about my  Top 5 SQL Server Books for the DBA has also been well received. This week's post is the Top 5 Books for the Database Developer, again these are the books on my physical and virtual bookshelf.

TSQL Querying - By Itzik Ben-Gan Et al
TSQL Fundamentals - By Itzik Ben-Gan
Microsoft SQL Server 2012 High Performance T-SQL Using Window Functions - By Itzik Ben-Gan
SQL Server 2012 TSQL Recipes - By Jason Brimhall and Wayne Sheffield
Microsoft SQL Server 2012 Internals - Karen Delaney and Bob Beauchemin
Windowing Functions
Windowing Functions

You might see an pattern developing with the author of some of these books Itzik's books are very good you can't help but learn a lot from them. The other two are really useful too

Update 13th September 2016

Thanks to Steve Hindmarsh for his comments. Steve recommends the following books. I have read the Antipatterns book a while ago...although i don't still have a copy. It is a good book.

SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin

Steve also recommended the following

Defensive Database Programming - By Alex Kuznetsov.

I have read this book too. You can get a printed copy from amazon by following the link. You can however get the ebook in pdf format free from its well worth a read

Monday, 12 September 2016

Never miss a Great Value IT Training Deal Again

As regular readers of the blog will be aware, over the summer of 2016 here at we have teamed up with LearningTree to offer some great value IT Training courses at some amazing prices.

The Great Value IT Training offer has gone down really well with some clients and customers. The one thing that has struck me since we started the offer is that sometimes people can and have missed out on a deal on a particular course because they haven’t seen or missed a post. So I have decided to set up and email newsletter, whereby I can deliver the offers to you weekly in the form of email. Then if you want to book onto the course you can simply reply to the email and we can get your course booked for you

The courses offered up on the deal change on a week by week basis. So the email will be sent weekly. If you would like to receive email you the new offers as I receive them, please sign up using the form below. There are other sign in forms on the Great Value IT Training page the Newsletter page too

We all know the benefits of have well trained knowledgeable staff including

  • Increased contribution to the team
  • Can play a factor in promotion and career development
  • Can help retain good employees
  • Can help improve staff motivation

Why not get all the benefits of well training staff and extremely reasonable price?

Great Value IT Training will help develop your staff

Turn you cygnets into swans and sign up for the weekly email

Friday, 9 September 2016

TGI SQL Friday - Weekly Roundup 9th September 2016

Welcome to the TGI SQL Friday for the 9th September 2016. Here you will find the links of the posts and articles that  I have been reading this week.

On the work front I have been busy, teaching a two day SSIS  customised course onsite with a client on Monday and Tuesday.

SSIS Classroom
SSIS Customised Course Setup

On Wednesday I delivered a one-day class for another client in Cardiff. Check out the Great Value IT Training page for this week's latest offers.

SQL Server

SQL Server Diagnostic Information Queries for September 2016 - By Glenn Berry

Released: Public Preview for System Center Management Packs for SQL Server and Dashboards - By SQL Server Engineering Team

JSON is Generally available in Azure SQL Database - By  Jovan Popovic

Disable SQL Server Triggers with PowerShell - By Tim Smith

New Drop If Exists Syntax in SQL Server 2016 - by John Miner



Apple launched the iPhone 7 this week. The major news is there is no traditional headphone socket. If you want to continue to use traditional headphones you can use the lightning connector ...Which will require an adapter, or you can use wireless ear phones. Apple also announced its own range of the these called Airpods. The reaction of the people I have spoken to about the new iPhone suggests a mixed response. Numerous people have complained to me about the lack of 3.5mm headphone jack. With iPhone sales already in decline it will be interesting to see if this helps buck that trends or increases the decline?

If you have a SQL Server problem, check out our SQL Server Consulting page to see if we can help.

Thursday, 8 September 2016

Great Value IT Training 8th September

As some of you know, here at we have partnered with Learning Tree to offer you a range of courses running at the London Education Centre over the next month or two at a great price. The courses on offer this week can be seen below. If you would like to book yourself a place on one of these great courses please email

1906 Python Best Practices and Design Patterns    27th September 16
396   UNIX and Linux Tools and Utilities              4th October 16
1531  SharePoint 2013 Technologies Introduction  4th October 16
1197  Achieving ITIL® Foundation Certification  10th October 16
284   Critical Thinking and Creative Problem Solving  19th October 16
1198  Achieving ITIL® Practitioner Certification  3rd November 16
3634  PRINCE2 Agile® Achieving Practitioner Certification  30th November 16
346   Project Leadership: Building High-Performance Teams  6th December 16
397   C++ Best Practices and Design Patterns  6th December 16
3631  MSP®: Achieving Practitioner Certification  12th December 16

Are you going to SQL Saturday Denmark? #SQLSaturdayDenmark

Are you going to SQL Saturday Denmark, which is running on Saturday 17th September? If you are David Postlethwaite or @postledm  if you follow him on twitter and a frequent contributor to this blog is delivering his talk on kerberos. David's talk is called

"Taming the Beast – How a SQL DBA can keep Kerberos under control"
David's abstract for this talk goes something like this

"The word Kerberos can strike fear into a SQL DBA as well as many Windows Server Administrators. 
What should be a straight forward and simple process can lead to all sorts of issues and trying to resolve them can turn into a nightmare.
This talk looks at the principle of Kerberos, how it applies to SQL Server and what we need to do ensure it works."

If you want to learn more about kerberos and David's session visit the SQL Saturday Denmark website.

David Delivering his Azure talk at SQL Saturday Iceland
I have heard this talk a few times and its really good. If you want to go and learn about kerberos and the doubt-hop issue then call in and see David in action. He is schedule to deliver in room 5 between 4pm and 5pm on Saturday 17th September. Hopefully David will see you there.

Wednesday, 7 September 2016

Top 5 SQL Server Books for the DBA

The Top 5 series has proved rather popular. My Top 5 books for Enterprise Architecture was the most popular post in the last month. With this in mind I have a little series of my top 5 books on various technologies  that I will share in the coming weeks.

My Book Cover

These are the Top 5 books for a SQL Server DBA. All these are on my virtual bookshelf and provide a wealth of knowledge and advice.

Microsoft SQL Server Unleashed - by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein

Troubleshooting SQL Server - A Guide for the Accidental DBA - By Jonathan Kehayias, Ted Krueger

Professional Microsoft SQL Server 2014 Administration - By Adam Jorgensen et al.

Getting Started with SQL Server 2014 Administration - By  Gethyn Ellis, ( Me - shameless plug :-)

SQL Server Deep Dives - By Various. This is not a normal SQL Server book and although quite old these days still has a wealth of information. Volume 1 was first published back in 2009. There was a SQL Server Deep Dives Volume 2 in 2011. Each chapter is written by a different MVP all sharing some excellent knowledge.

If you need any help with SQL Server problem, a database migration, performance issues or maybe the BI stack, then please do check out our SQL Server Consulting page and do not hesitate to  contact us

Friday, 2 September 2016

TGI SQL Friday - Weekly Roundup 2nd September 2016

Weekly Summary

Last week I was holiday in Cyprus. The weather was plus 30 degrees celsius all week. The pool was awesome the beach was great, the beer was cold.

Cyprus Holiday

Monday was a return to reality, and Tuesday was a  return to work.Although its been a short week, its been pretty busy.  I've been customising Learning Tree SSIS course 134 and 4555 to deliver onsite for a client next week. I have also delivered a one day course 4559 Powerful SQL Server Queries for a client in Cardiff.

I have made some time to catch up with some reading, this weeks links are below

Weekly Links

SQL Server

Enterprise Architecture 

Other News

Don forget to check out out our Great Value IT Training Page for the latest training offers

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...