Twelve is a Magic Number


A massive congratulations to the SQLBits team for announcing what will become the 12th iteration of the UKs finest and largest SQL Server conference. Putting on events at this scale by such a small core team demands huge respect and I sincerely thank them for doing so. I have personally learnt a ton of information and grown as a person and as a SQL Professional because of their efforts and I know many others in the industry feel the same way too.

The location this year is Telford, and I must confess to not knowing a great deal about this Town in the West Midlands. After a close inspection of Wikipedia, it tells me that Telford is a new town formed in the 1960s and ’70s from a collection of neighbouring smaller towns and close by is the famous landmark known as Ironbridge Gorge which was the first Iron Bridge of its kind in the World! I was already familiar with this due to my Mother raving to me many years ago about a Sweet shop that can be found there where you could buy lots of Olde World favourites such as Poor Bens, Coltsfoot Rock and Lemon Sherberts :)

As always, I look forward to see what the organisers have planned for us all, and encourage you to check out the event as the details start trickling through and perhaps consider submitting a session? I know you won’t regret it and hope to see you there!

If you need any further encouragement (really!?), I recommend that you check out my following SQLBits related posts:

T-SQL Tuesday #41 – I ♥ SQLBits
SQLBits 8 – finis!
Standing upon the shoulders of Giants
SQLBits 7 the forgotten photos part 1
SQLBits 7 the forgotten photos part 2
SQLRally and beyond
Speaker Training day

Posted in personaldevelopment, publicspeaking, sql, sqlbits, SQLServerPedia Syndication, Training | Leave a comment

Read your old CHM files from network storage on Windows 8

I was just recently going through my old SQL Server documents and reference material and attempting to organize and tidy things up, when I re-discovered a huge collection of literature that even now is incredibly useful. The only problem is that many of these old electronic books and files are stored in CHM format. Those old timers of you will remember that this was how SQL Server Books online was deployed up to and including SQL Server 2000.

So I tried opening one of these documents and I got the classic (and expected) symptom of a totally blank document body pane.

Totally blank reading pane

Try reading this sucker!

Now this wasn’t really a big surprise since the restriction was actually put in place by Microsoft many years ago to prevent content loading from remote and untrusted sources (i.e. the Internet or Network/ UNC paths) and resulting in an infection to your machine. In the past I seemed to remember that it was a simple registry hack, and this time around various searches across the internet were again suggesting that this would be the case. Unfortunately no single article  appeared to join all the dots but after a frustrating period of time I finally managed to figure it out again. This fix works on Windows 8/ 8.1 and should also work on prior versions of Windows going back to XP but I have not tested it on these platforms.

I have decided to blog it, not only to help anyone else struggling getting your remote CHM files working, but also so I don’t have to waste one single minute more trying to figure out what the fix is. Assuming you trust the Remote source and the files therein, you have two choices:

  1. Copy the remote CHM file/s locally.
  2. Implement the fix described below.

Since I hate having to duplicate reference material, I want to implement the fix. So the first piece of advice you will see quite frequently if you perform a Google or Bing search for this problem, is that you need to add the following registry key into your machine:

Windows Registry Editor Version 5.00


Now the important line here is the MaxAllowedZone DWORD (line 4) created under the ItssRestrictions registry key. I first struggled to find any description of what MaxAllowedZone value did, but the following values describe the purpose of each:

0 = My Computer
1 = Local Intranet Zone
2 = Trusted sites Zone
3 = Internet Zone
4 = Restricted Sites Zone

It was only really when I discovered these meanings that the next step fell into place. Essentially these values are setting the maximum zone in which you trust the remote CHM content. So a setting of 1 essentially tells Windows to allow any CHM content that is considered to be part of the Local Intranet Zone (and by default this will not include your network paths). So setting a value of 1 by itself would not (in this case) solve your issue. We could in fact change the value to 3 (Internet Zone) which would fix the problem since this is the Zone that my remote drive is considered to be part of, but that is obviously putting my machine at risk.

MaxAllowedZone key value is set to a value of 1

MaxAllowedZone key value is set to 1

The solution then is to ensure that the MaxAllowedZone is set to a value of 1 and to ensure that your network path is added into the “Local Intranet Zone” and we do this through Internet Explorer:

Internet Sites

  1. Select Internet Options via it’s Tools menu (Alt+x).
  2. Click the Security tab and click on the Local Intranet Zone.
  3. Next click the Sites button and then click the Advanced button from the Local intranet dialog.
  4. Ensure that you deselect “Require server verification (https:) for all sites in this zone” option.
  5. Finally all you need to do is type the path to your network drive. In my case I have mapped the network path to my Z:\ drive, so I simply type Z:\ in the “Add this website to the zone:” textbox. The path will be resolved to its real hostname.
  6. Click the Close button and click any resulting OK buttons and close Internet Explorer.

Now when you try loading your file all of the content loads! I now have Ken Henderson on tap again!

I want you back for good!

I want you back for good!

Posted in networking, personaldevelopment, sqlhelp, storage, Training, windows | 3 Comments

Passionate about SQLpassion

If you are new or experienced to the world of SQL Server you will be aware that one of SQL Server’s greatest strengths is the copious amounts of literature and videos available to you covering subjects ranging from the basic to the very complex. I am no stranger to many of these sources of material and regularly watch, read and consume numerous articles, white papers and training videos relating to SQL Server in some way on an almost daily (if not hourly!) basis. And since I am always on the lookout for high quality sources of training, I was therefore very pleased to hear that the SQLpassion Academy headed by its founder Klaus Aschenbrenner was set to release an ongoing series of materials for a low cost.

I was therefore very happy to get my grubby little hands on the first three videos currently available through the Academy and would like to share with you a quick review of them.

Check out the wallpaper!

Check out the wallpaper!

For those of you who are not familiar with Klaus, he is a regular speaker at SQL Server conferences, is a Microsoft Certified Master, and the author of the popular (and largely only) book on SQL Server 2008 Service Broker. Some of you may know that Klaus first dipped his toes into the training video market with his amusingly named “SQL Server Quickies” videos. These can be freely accessed via his YouTube channel. They are essentially 15 minute short video offerings covering anything from niche SQL Server subjects to more widely understood topics, but always delivered with expert insight and advice.

With the SQLpassion Academy, Klaus has decided to provide longer and more detailed videos and is I believe looking to fill a gap in the market in which he can provide expert training at very low cost. He has kicked off his SQLpassion Academy by releasing the first three videos in the course “Choosing the right Clustered Key“, “Index Impact Analysis” and “Parameter Sniffing“. Each video is approximately 55 minutes long and I found this time to be an almost perfect length for technical training videos. It means that you do not get information overload and allows Klaus to hold your attention and interest right up to the end.

You might be wondering what makes these videos different? I believe Klaus has chosen a really nice unique approach and avoids switching between video footage of himself and on screen demos (like the AppDev training videos) and instead he uses a live insert of himself talking along to the demonstration or slide decks. This gives a really nice feeling of getting a one-to-one with him and is a really nice and personal touch. Furthermore, in-between the demos or slide deck narrative, Klaus also switches to his flipchart where he describes the technical information and sketches out any important information in diagrammatic format. All three types of delivery work really well together and keeps the momentum of the training flowing and your interest right till the very end.

Having watched all three of the videos, Klaus is almost flawless in his delivery and I didn’t manage to hear him say the dreaded words of “Umm” not once, and I suppose this is no surprise given his experience of presenting. For not only is his delivery very fluent, is doesn’t feel like he is reading from a script (in a good way), but it is obvious that Klaus is telling a story and he has structured the video content very well to this effect. I particularly liked the brief elements of humor that Klaus introduces such as the “Badoom” noise to highlight in a funny way that he has just done (or demonstrated) something clever. I would like to see more of this humor creeping into his videos over time because I think Klaus has got a wicked sense of humor. For instance, if you were fortunate enough to hear him speak at the SQLPASS 2012 Summit, you would have heard him explain that he was Austrian -and highlight (to the largely American audience) that “no, we don’t have Kangaroos” .

Klaus is talking about the metric system :)

Klaus is talking about the metric system :)

In his video “Parameter Sniffing“, Klaus initially starts out by proposing a simple problem in SQL Server performance without giving the whole game away there and then. For he knows that the whole picture is rather more complicated than you would first think and is keen to give you a nice background and supporting material first in order to help you understand what is coming. What I really liked about this video, is the fact that Klaus covers an awful lot of aspects to SQL Server performance (all of which is relevant to parameter sniffing) without complicating the delivery. He gives us “the what”, “the why” and “the when” to parameter sniffing without forgetting the solution. In this video you will come away understanding a range of material from Bookmark Lookups, Clustered and Non-Clustered Index Structure, Lookups and Scans, Data Distribution, Tipping points, Query Compilation, Statistics, Troubleshooting and much more -all in 52 minutes! Very good stuff!

Moving swiftly onto his second video “Choosing the right Clustered Key” Klaus wastes absolutely no time by giving you an “it depends” statement . However it is not very long before he is demoing a scenario in which he explains one situation where “it depends” does not apply. This video is very much an excellent supporting video to the previous one and goes into further detail explaining index structure before moving onto how all of this can effect latch contention and other disadvantages if you get it all wrong! The material is very thought provoking and it really is a shame that this amount of thought usually never goes into clustered index selection. If you are looking to design a high performance OLTP environment, then this video is essential viewing.

Finally we take a look at the last video in the series “Index Impact Analysis“. This initial offering of videos would not be complete without instruction of how you can understand if you have got things right! Klaus explains the strategies you should take so that you can baseline, implement, replay and repeat (or reject) your indexing strategies and improvements. This is a very powerful video and he demos several techniques and applications that you can use to achieve these aims. He not only demonstrates existing utilities (including SQL 2012 tools), but also looks at external and third party tools. I was very pleased to see that rather than reuse one of the tools he demoed in his “Choosing the right Clustered Key” video he demonstrates others instead. By doing so, Klaus is cleverly delivering the largest amount of knowledge possible in the time given.

In summary, I very much enjoyed watching these videos and think that they are an excellent start to his Academy. I watched them very much from the perspective of whether the videos would be suitable for MCM level candidates or whether they would be suitable for SQL Server beginners. I think Klaus has targeted them perfectly. If you are completely new to SQL Server you may struggle, however if you are a junior or mid level DBA, the material is explained in such a methodical and cleverly paced fashion that you will not be left behind. MCM level candidates will also get a lot from them since some of the material will re-enforce much of the information you should already know, but will also fill in many of the gaps.

Well done Klaus!

Posted in sql, performance, parallelism, SQLServerPedia Syndication, personaldevelopment, Concurrency, SQLPassion, Training | Leave a comment