I spent a couple of hours this morning with a partner who wants to work with the Microsoft platform to deliver spatial business intelligence.  Unlike some companies who have BI skills they are trying to extend into the spatial world, these guys are spatial experts who want to understand Microsoft’s capabilities.  I mention this because they are concerned that in a few years their would be capabilities in SQL Server which would largely replace some of their offerings.  I am not going to speculate in any detail on vnext of SQL Server, but I think that while there may be more spatial features in the next release, there are a few of things that can’t really be fixed:

• In each country there are differing spatial standards which you really need to convert to latitude/longitude as this is what GPS, Google and Microsoft understand. As I have mentioned before in the UK this means the eastings and northings in the Ordnance Survey and there is a paid for tool for this form SAFE
• Some systems like UK postcodes boundaries are proprietary – they have to be paid for.
• How can you clean this kind of data? compare one spatial reference with another and check for a proximity error?
• As Simon Munro pointed out at SQLBits and on his blog, how do you put spatial data into meaningful hierarchies. For example postcodes might not be that useful to the RSPB if the data they are analysing is bird populations rather than customer marketing.

While spatial data is now a first class citizen in SQL Server and Report Builder surfaces this data really well, you’ll still need spatial expertise to get the underlying data into shape (pun intended). That’s where partners come in and I think this is very similar to the early days of BI : Businesses knew they wanted something and the vendors made various capability claims, but it was the partner ecosystem that made it work.  Later on as BI matured more self service offerings appeared and more of the presentation layer work was put into the hands of the user.  However after ten years of BI there’s a very healthy partner ecosystem out there and I see the same thing happening as spatial BI evolves.

I confess to only being an interested amateur in this field so if you want to know more check out the Bing Maps Development resources and follow blogs from the real Microsoft experts in this field:

‘Spatial’ Ed Katibah

Johannes Kebeck on the Bing Maps team

Whether your thing is windows management or virtual machines there’s interesting user group activity coming up in the next few weeks..

The virtual machine user group (VMUG) in Leeds gets together on 16th November from 12-6pm at the Leeds Park Plaza hotel ..

You’ll find me presenting on SQL Server in a virtual world, and I’ll be on hand afterwards to dodge the rotten tomatoes and to buy everyone a pint. You can register for this here.

However I won’t be at the Windows Management User Group (WMUG) on 1st November, which is a shame as I am a System Center newbie and I could do with some pointers from my good friend Wally Mead from the System Center Product team. Also presenting is Randy Roffey from Silect giving a presentation of Silects latest offering, ConfigWise as well as spending some time checking out their other ConfigMgr centric product CP Studio. This is in  Microsoft Offices at Cardinal Place, Victoria London from 11:30 til 6pm, click here to register..

Share and enjoy!

One of the reasons SQL server can spread through your organisation is that it underpins so many applications, including quite a few candidates from Microsoft, like Project Enterprise , Visual Studio Team Server, and the System Center line-up.  All of these use SQL Server to store metadata, for example in the case of System Center Virtual Machine Manager (SCVMM) the catalogue and state of the virtual machines under control of SCVMM is stored in a series of tables.  This is a good thing as SQL server is being used for the right reasons and in the right way:

• The data is typically quite compact and structured lending it self to a relational database. 
• With SQL server already installed the additional benefits of reporting services can be used to monitor the application.
• SQL Server can be used to store authorisation information – who can do what to the various parts of the application.  In this case the database can be protected with a certificate to ensure it is secure.
• Multiple  (concurrent) user access to the tools can occur while maintaining this data in a consistent known state

However in the case of System Center Essentials (SCE pronounced as “ski”) which is designed for the small medium sized business there probably isn’t going to be a dedicated database administrator in the business and this might end up being the first exposure to SQL Server.  I mention this because one of the killer reasons to use a database for storing all this data is that it is pretty simple to backup the data.

SCE 2010 actually uses a number of databases, as it is a cut-down combined version of its bigger brothers like SCVMM:

• SUSDB has all the deployment data about updates
• VirtualManagerDB has all the metadata about the virtual machine templates, which is similar to Virtual Machine Manager
• OperationsManagerDW all the monitoring event data in a format that can be used for reporting
• SystemCenterEssentials has everything else in so the monitoring data, security and even a data warehouse scheme used by the reports

System Center products like SCE aren’t  mission critical applications but that doesn’t mean you shouldn’t back them up as the history of what has been done and how systems are behaving could be important in future either for troubleshooting, asset management, or other compliance work.  SCE Books online has some advice on making backups, but I am not sure who would dig down into that so here’s a cut & paste of the important bit:

Notice the additional entries here for the non-SCE databases Master, msdb & ReportServer.  If SCE is not sharing the installation of SQL Server with other applications then don’t assume these are being backed up unless you as the SCE administrator are doing it.  Actually you might want to check anyway as everyone on a shared SQL Server might assume that someone else is doing it. 

One final thing is that there other data outside these databases to consider including encryption keys and certificates as well as parts of active directory that should be backed up to ensure you can recover from every type of failure that could affect SCE which is also detailed in books on line here.

I did an internet safety presentation to the association for retired police officers on Friday, and it still amazes me  how little my generation know about social media and the internet in general.  There are basically three camps when it comes to awareness of the dangers the internet poses to children..

and my mission is to move everyone into the middle, i.e informed but concerned.  The biggest problem I have is with the group on the right as they are minded to completely block all access to the internet for children and this simply doesn’t work for a number of reasons.

• Banning anything often makes it more attractive, especially for more vulnerable children
• Locking down the home computer with all sorts of parental guidance software, doesn’t prevent access at a friend’s house or from a smart phone
• At some point children are going to have to fend for themselves and work out how to spot threats in much the same way they need to learn how to cross the road safely. Most of what I talk about can also be applied to parents as well so their online lives are safe from such things as identity theft.

Sadly there is no one piece of software, or one technique I can recommend for you or your children to be safe online, so what I talk about is what to be aware of and how to discuss this difficult area with you children.  I can’t claim any credit for any of this, the program is worked out by CEOP and volunteer organisations like the NSPCC, all I do with like minded individuals is to deliver the excellent content they provide. 

One other area that  I talked about was cyber bullying, and here I must put myself in the blissfully ignorant camp as I thought this was really only a child related problem. However after my session a number of these retired officers told me they were working on cases of internal cyber bullying in the organisations they now work for.  The approach in both cases is the same, as the law doesn’t really make any distinction between how harassment occurs, however with cyber-bullying, you can be traced and the evidence cannot easily be destroyed as it’s in the cloud.  The only challenge is to give the victims the confidence to come forward and the reassurance that their complaints will be treated seriously and sensitively.

So the other key thing that CEOP provide is an extensive program for children to report abuse sensitively and confidentially wherever they see this sign.

BTW all the CEOP advice is on line and embedded into sites like Facebook

When Steve Ballmer says “We’re all in the cloud”  this should be viewed in the same way as when  Bill Gates talked about “a PC in every home”.  I could quote statistics on the investment Microsoft is making in the cloud or show you some cool pictures of containers in one of the data centres, but I think this whitepaper on internal adoption of the cloud by Microsoft’s internal IT department is in many ways more compelling.

Why? because if you asked anyone working in IT what the major concerns are about adopting cloud services, I can guarantee that security and compliance will come up.  So the fact that Microsoft is putting two of its most important systems in the cloud..

the performance and bonus HR stuff, and the volume licensing service manager is proof that this should not be seen as a barrier to moving to the cloud.

Actually Microsoft already has loads of personal data about us on its cloud servers, I say us because if you have

• An account with BT Vision
• XBox Live
• Hotmail
• Skydrive

..those are Microsoft cloud services for consumers.  It’s a similar story with Microsoft’s business facing services like BPOS and Azure.  What all these services have in common is that they are all delivered form the same infrastructure.  So it’s a logical next step for Microsoft to use this platform for its own internal services including those with sensitive personal data in them.

However what makes this interesting is that this decision was not based on some corporate mantra, it is based on cold hard economics as the return on investment calculations in the whitepaper show. These are based on the standard commercial charging for Azure that any business would be charged compared to the cost of running those services in house, and so the same numbers could be used to look at the value you could get from using cloud services.

So is it time to consider a cloud first rather than a virtualisation first strategy in your organisation?

Many of us in the technical world seem to get caught up in the devices we use rather than what we use them for and how often.  for example it doesn’t really matter which ebook reader you use what matters is what books you read.  Repeat that for the films you watch with your home cinema setup, and the quality of the photographs you took on your last holiday…

Simon and I took a day off work to setup some old laptops for a charity.  Yes they were pretty ancient e.g Dell Latitude D400, and D600 but they all ran Windows 7 just fine after a bit of digging for the odd driver. More importantly for the users we got Office Professional Plus 2010 on there plus Live Essentials and Microsoft Security Essentials to protect them. However they weren’t well received because the PCs themselves weren’t new, so we bought them home again, which is why I am wondering which is more important, the device or the content?

I would go for the content, based on my wife’s horrendous office laptop, it’s a lovely little HP and should be rocket fast, but it’s running Office XP and Office 2003. Actually they aren’t the real problem, the issue is the encryption, VPN and anti-virus that are part of the standard build where she works to make XP as secure as Windows 7. To add insult to the injury of having an unreliable and slow machine she really has to put in the hours to get her work done using this older generation of software.

As for our work on those laptops, we’ve found a community centre that can make really good use of them to raise digital literacy levels in a very deprived area, so it all turned out alright in the end. Not only that but Windows 7 has given these and many other machines a second life, which is also a good sustainability story.

In my continuing saga on the Woodland Trust I was lucky enough to go to the formal opening of their new building last week.  Unfortunately while all the IT is going smoothly it is the lack of water that is holding up the move, and this means that they can’t bring the new servers online because they are water cooled. 

The data centre may look fairly conventional but only the cabinets are air conditioned not the whole room.

To quite their IT director Lionel Wilson on this..

“If you asked an IT Professional to stop milk from going off they would put the pint in the middle of a room and turn the air con rather than storing it in a fridge". 

BTW those circular things on the right aren’t a bank of speakers, they’re the cooling fans which spin up based on sensors in front of each server rack, and actually sound more like jet engines.

As I have said before sustainability is key part of the new building but despite the high tech look and features in this building it has only cost 10% more than a conventional steel and glass carbuncle of the same size ..

BTW It may seem odd for the Woodland Trust to be using wood the thing they are trying to preserve to make their building but the wood used is sustainable fast growing larch.

However back to I, their 200 users remote desktop services session will run from a cluster of three physical servers. I am pretty sure you couldn’t get that server density from any VDI solution, but the experience is still very good ,each user has dual 24” LED monitors to deliver a rich corporate desktop. The rest of their key services;  Exchange , and their CRM system all run on Hyper-V and are managed by the System Center suite of tools.

I had hoped that Lionel would be on hand to discuss this at IP Expo as his passion and knowledge for sustainable IT makes him an excellent speaker, but the slip in move in dates has prevented that so he has made a short video for the event organisers you can watch here.

Following on from my my post on Monday I wanted to show you how to link reports together using the mapping control in Report Builder 3.  This is actually pretty easy to do as any polygon or point on a map can have an action associated with it and one of the available action types allows you to call another report.The clever bit is that the polygon has data associated with it so you can pass this information as a parameter to another report.  In my example I have a US State report which is run merely by clicking on the state in the USA wide overall sales map I created my last post.

I have made  a screencast for you to follow and if you want to try it for yourself then please refer to part 1 I posted on Monday as you’ll need understand that first..

The old Catch 22 situation of not having experience nor having anyone who is wiling to provide that experience still exists today as it did when I stated work.  If I reverse that argument every company in the current economic climate could argue that there is no budget to train staff so we have to recruit experienced staff.  This results in companies having the additional worry that if they do train staff, they will loose staff to their competitors once that employee has enough experience. The statistics bear this corporate behaviour out -  a massive 88% of UK companies don’t have a formal work experience program.

Microsoft solution to this dilemma is the Britain Works  initiative with the aim of getting 500,000 more people into IT by 2012.  A year in the scheme has worked for 104,000 so not quite on track but making a difference to quite a few people. The scheme now has a special section Young Britain Works offering advice and apprenticeships.  There are training vouchers for IT Professionals to cross train or upgrade their skills as well as sections for Start-ups for charities who are interested in the program. Social Media is also important to connect al of this so you’ll find Britain Works on @britainworks on Twitter and there are groups on LinkedIn and Facebook.

I realise it’s not always possible to do this in a partner or small business, but can I at least ask you to take a look and possibly connect with some of the candidates on here who are looking to work in our IT industry

No I haven’t got the numbers the wrong way round, the three clouds I am referring to are the three tracks we have put together for TechDays on 8th October and the screen is where you’ll be watching it as this is Online rather than in person. Those three tracks are:

• Cirrus Room - A high-level summary of the key technologies in the Windows Azure Platform such as App Fabric from Microsoft UK experts
• Altocumulus – Hear from companies who have already developed for Azure including myworldcup.com
• Stratocumulus – Get down to the low level clouds in our deeper technical sessions

While this is a developer focused event, IT Professionals need to understand some of this as the cloud isn’t going away and Azure services still need to be managed in order to integrate with the on premise infrastructure. So there will certainly be sessions in the top two tracks that will be of interest. 

As ever the sessions will be recorded if you can’t make the date but the value of listening live is that you can participate with questions to the speakers. I’ll be there to ensure those questions are answered by our panel of experts , and to learn some of the deeper stuff myself.

In my last post I went through a possible approach for showing spatial and olap data that the RSPB are planning to evaluate, now I want to show you how this might look in an example.

First of all an apology, I am not going to pay the £1500 to license UK postcodes as this is just a demo, rather I am going to show how it might work with example data from the US Census boundaries. Not only is this free but the data I am using is available on Codeplex (here.) so you can recreate what I am doing to get the idea.

These files are pretty big sql  scripts, so the best way to load them in is to create a database (mines called USCensus) and then use SQLCMD to run the script for each SQL file:

SQLCMD –S “MIAMI\PowerPivot” –E  -d”USCensus” –i “d:\samples\US Census\postcode.table.zip”

where:

-S is the server instance

-E signifies windows authentication

-d is the database

-i is the sql file you want to run (change and rerun this for each of the four files in the census)

The State, City and County tables have the relevant boundary in as a geography column, and county and city both show which state they’re in.

I can then combine this withe the adventure works sample (the version for SQL Server 2008 R2 )cube which I can also get from CodePlex here, as this has a geography dimension.

Now I have the data I need I can start to use Reporting Services, specifically by opening Report Builder 3 and using the map wizard.  Unlike all the other controls in Reporting Builder the map control allows you to combine different sets of data in the one control.  In my case I need the state polygons from the US Census database I have created and analytical data form the cube.  The wizard guides you through this process including identifying how the data sets are to be joined. In this case the join is between the  state name in the state table to the State Province attribute in the Adventure Works cube.

Rather than grab every screen I have made a short video which you can pause and replay to see what I have done..

There’s also a bunch of sessions on Report Builder and spatial data at the next SQLBits in York  1 –2 October 2010.

I spent a very interesting afternoon last week with the IT team at the RSPB to explore what they could do to combine their mapping data with the olap cubes they have in analysis services. The only out of the box solution in SQL Server for spatial data is the map control in reporting Services (in SQL Server 2008 R2).  This map control allows you to create the appearance of drilling down to more details as you can pass details of where on the map is being clicked to another report, much like passing parameters in previous versions of reporting services.  The other interesting thing about the map control is that it does allow you to combine sets of data in the one control.  This would allow the RSPB to combine a relational query with the spatial data with an MDX query and then join them on  a common field.

The spatial hierarchy would use the standard UK postcode:

[changed 20/09/10 as I got the labellign of the postcodoes mixed up - thanks to Robert Edgson for picking this up]

Postal Town/ London area( (1st 1or 2 characters) –> outbound code(the group before the space)  –> sector( 1st number after the space)  –> full postcode

for example for Microsoft UK:

RG –>RG6 –> RG 6 1-> RG6 1WG which equates to

This would then translate into three linked reports:

• A static UK wide report with all the postal town boundaries on e.g. RG – Reading.
• A postal town report which takes a parameter of postal town (e.g. RG). and shows the inbound codes in that postal town/London area
• An inbound report which takes a parameter of an inbound code shows the postcodes and or sectors in the area specified by that inbound code.

I would then publish the map report parts for these maps, another new feature in SQL Server 2008 R2 Reporting Services.

The olap data in the cube has a location dimension with the parts of the postcode in a postcode hierarchy; postal town –> Inbound code –>sector –>postcode. The parameter passed into the postal town report would then filter the map to the town and because the olap data is inner joined to that town you’ll only get the olap data for that town.

It is then up to the users as to which visualisation they wish to use to express high and low values e.g. heat colour the polygons or add symbols with different colours or sizes to show the measures in the cube.

What you loose in surfacing BI in this way is the ad hoc interaction you have with analysis services proper, however what you gain is a quick method of seeing data on a map from a cube, that can be created by an end user.

In the meantime here’s a gratuitous painting I did of a merlin to show I have more than a casual interest  in the success of the RSPB:

I’ll keep you posted on how the RSPB goes (subject to their permission of course)  and in my next couple of posts I’ll show you how to do some of this using freely available (and unfortunately American based) data.

I am out of the office at the moment, but my spies tell me the IE9 beta is here.

I’ll be doing a bit of icon design on the side when I get back, as these come in quite useful when you tear off tabs and put them on the Windows 7 taskbar .

The security community continually despairs of how bad we are at keeping our systems up to date with the latest security updates be they from Microsoft, Oracle, Adobe etc. In fact this trend probably matches the classic bell curve of adoption of any new technology so there are a few early adopters, the mainstream followed by a small but long tail of those who cannot , don’t know about or can’t be bothered to move away form old technologies like XP and IE 6.  This is a cultural thing and is therefore very hard to change. While a cautious approach to many technologies might be justified, I just can’t see any justification for not keeping up to date with security patches, with the possible exception of concerns about reliability. 

Given that security and critical updates are a fact of life, I would then expect a company to have a process for checking the reliability of security updates, and if a particular update cause problems this would enable the precise issue to be flagged back to the vendor for resolution. What I can’t understand is a vague notion that some new update might  be unreliable and hanging back to see if anyone else has an issue with it. I am curious about this approach as I am not sure what event or time lag ensures that applying the patch is more likely to work than when it was released.

Vendors obviously want to ensure these patches are reliable but they will have access to only so many testing environments and this can’t cover the variety of environments that are out there.  Microsoft tackles this problem by recognising that there are early adopters out there who want sight of the patches asap for testing and has a process in place to make use of this,  the Security Update Validation Program (SUVP).  Selected customers are invited to join and they get these patched priori to release in order to test them out (i.e. not put them into production) , which means they can deploy patches immediately they are released as they will have already done their testing, while Microsoft gets feedback on the reliability form all the SUVP customers that the patch is OK to release.

So your choices are for applying security updates are:

1. You are relying on the quality of the patches and deploying them as soon as they arrive.
2. You’re doing your own deep testing when applying security patches as soon as they’re released, and deploy after testing.
3. You understand the risk of not deploying these patches immediately and have balanced the cost of not testing and applying them against the risk and cost of losing service and /or data as the result of an attack and got sign off for this approach from the business.
4. Your cv is update

Further Reading:

Microsoft Security Centre

In my post yesterday I showed I gave you an introduction to Remote Desktop Services, today I want to contrast and compare a traditional remote desktop approach with the shiny new thing that is Virtual Desktop Infrastructure (VDI),  in order to understand when to use them.

First of all why would you use either of them?

• Efficiency.  You might decide remote desktops are a simple way of provisioning IT in hot desk areas ,branch offices, everywhere and deploy thin clients in place of PCs.  An added advantage is that any user can use any desktop.   
• Environment. You might achieve better control of your power usage by deploying low power thin clients on desks and pulling the computing power needed into a well managed data centre.  However I am cautious about this as I think the savings are marginal unless you are also doing this for reasons like moving up to newer operating systems and doing a lot of consolidation as well as the energy and real costs of replacing hardware well have a long pay back period.
• Security. With a remote desktop solution all the data is in the data centre, so data loss whether accidental or deliberate is less of an issue.
• Manageability.  Virtualising desktops is seen as a way of controlling the desktop by the IT department.  

RDS has long been used as a solution to these issues, so what makes VDI better or indeed worse?

• Efficiency and the Environment.  The idea behind any desktop virtualisation solution is to put all the computing power used by your users in the data centre with only the screen , keyboard and mouse on the users actual desk.  The question then is how many concurrent desktops can I cram into one server in my data centre, as this will determines the energy and costs saving that can be made.

If I elect to use VDI I create a virtual machine (VM) for each of my users so the number of users I can support will depend on how many VMs I can run concurrently.  Each VM will have its own operating system , plus any applications the users want to run. RDS works completely differently as each user session is not another copy of the OS with all its applications.  Buried in the   Remote Desktop Session capacity planning guidelines there is is this table..

Contemporary VDI solutions would be pushed to support 20 users on this, based on the memory alone,  so RDS is at least 5X as efficient as VDI for this kind of workload and I am being very generous to VDI here. 

Security. Both Microsoft RDS and VDI are equally secure as they both use the same protocol (RDP Remote Desktop Protocol) to connect to the desktop from the local device.

Manageability.  In RDS you manage the server and the set of applications that are installed on it for use by the users. Also the user state doesn’t persist so the desktop returns to a known state at the end of a session. In VDI the operating system and the user applications are installed in each VM, all of which needs to patched updated and monitored.  The fact you are running users’ desktops in VDI doesn’t actually change the manageability problem at all; and any tools like System Center you would use to do this would work equally well on physical desktops or VDI.  BTW there is nothing to stop you running the Remote Desktop Session Servers as vms to get the benefits of server virtualisation.

So why would use VDI at all ? 

RDS works well where you have a large community of users with similar and basic needs e.g. Information Workers who need to use Office , access the company intranet and internal applications like ERP and thus have  one of a limited number of standard desktops with no persistent user state. 

VDI gives each user a very personal desktop and the users state persists between sessions. This is might be essential for some power users with specialised needs, for example contractors who need special tools like Visual Studio and Expression, Actuaries, specialist financial staff and so on. VDI enables these users to be able to use the same thin clients as their colleagues, and to work remotely (where there are reasonable communications), from thin clients or low power PCs. 

With the way the new component parts  of the Microsoft Remote Desktop infrastructure works these two classes of users will have the same user experience and will not know whether they are in fact using RDS or VDI.

To conclude

• For most thin client solutions RDS is much more efficient than VDI, but there are niche uses cases where VDI makes sense
• VDI is not any more easier to manage than if that users desktop were running on the equivalent physical PC, in fact the same management tools work in both cases     

Further Reading

• Directions on Microsoft whitepaper on RDS & VDI
• Microsoft RDS
• Microsoft VDI

I am sometimes confused when things get renamed, but it made a lot of sense for Microsoft to  renamed Terminal Services to Remote Desktop Services as it has changed out of all recognition from where it started.  In fact we made this simple video to show how much has changed in Windows Server 2008 R2 for its launch last year..

With this release of Windows Server there is a one stop shop, the Remote Desktop Web Portal, to bring all of the different options; a remote application, a remote desktop or the desktop of an individual’s own virtual machine using VDI (virtual desktop infrastructure).  Our simple pin board diagram gets the basics across well enough, but if you want to see the full details of what’s under the covers now there’s a poster you can download from here.

This might seem complicated but all the bits are in Windows server 2008 R2 as is, and the simple portal for the users I put in this video shows that ..

you’ll also see how remote applications appear in windows 7 , so the users don’t even need to go to his portal to see them once they register it.

Like all things Microsoft you might well be confused about when to use which, so I’ll try and clear that up in my next post.

It’s not always Microsoft’s fault…

For the last month or so, my mum’s Asus Revo mini desktop PC thing kept starting windows repair intermittently on boot and her local tech support man (I couldn’t get down to her) thought Windows updates were the problem.  Also when it did power up it was taking ages to start.  My inclination was a hard disk problem and as I couldn’t get it to start Windows at all I stripped the whole thing down (not the easiest machine to do this on), swapped out the hard disk and proceeded to install Windows 7 and the other stuff she would need:

• Office 2010 
• Windows Live Essentials 2011 for the its free tools:
  • Live Photo Gallery to allow her to do basic edits and tag her photos
  • Live Messenger for her to keep in contact with the family and friends
• Microsoft Security Essentials, Microsoft’s free anti-malware solution to protect her machine

It all took about 4 hours, and yes it rebooted several times and installed upwards of fifty updates, but I was out having meal during that bit, and when I came back it was all done first time. I then put the old hard disk in a drive bay enclosure and got her data off it and onto the new hard disk and so now the only reason I need to visit is as a loving son rather than an IT Professional.

The other deployment scenario I worked on at home recently was to upgrade our home machines with the acquisition of two barebones systems from Novatech. These machines were both running  Windows 7 and so all I had to do was move the old system hard disks across and fire up the new barebones machines for everything to work. Despite what I have read on some forums including Microsoft social you definitely don’t need to do a reinstall with Vista or Windows 7 as they  just auto detect the hardware, reboots and asks you to reactivate your Windows license.  The limiting factor on my windows experience score is now those hard disks, and I would need to reinstall windows if I wanted to swap those out too, of course.

While I would defer to my colleague Simon and the excellent Springboard resources for mass deployments of Windows 7, it was simple enough for me to do all of this.  So you could gain some kudos by doing your own local deployments like this for your neighbours, friends and family and perhaps spend less time fixing their issues and more time socialising with them as a result.

I recently interviewed one of our interns, Jonathan Lickiss (here), about his aspirations to be an IT Professional (IT Pro) and to callout all the useful resources at the TechNet On portal.  Some would say that Jonathan is mad for selecting this as a career, and that the TechNet On site is just a piece of cynical marketing given Microsoft’s drive to provide all of its services in the cloud, and therefore make  IT Pros redundant. 

If you look at what has happened to the work done by an IT Pro since my dad’s time in IT (1960’s), the trend has been to automate or outsource any repeatable  task. So you could just as easily point to DHCP, DNS, PowerShell, System Center or Windows 7 as sounding the death knell for the ITPro as they all get rid of this kind of work.  There have also been concerns about outsourcing and taking various IT work off shore, before the cloud came along.  However the number of IT jobs in the UK is still buoyant and we are still short of these skills in the UK despite the recession, if e-mail recruitment spam is anything to go by.

The main attraction of cloud is cost management, just to be clear not necessarily a reduction in cost, but management of that cost.  This essentially means moving costs from capex to opex and only paying only for what is actually being used. For example, rather than trying to predict license costs and the number of servers needs and buying that upfront the business only pays for the number of active users , the amount of data used and so on.  This smooths cashflow and means a business has to borrow less money from reluctant banks, i.e. Microsoft takes the risk and cost out of the business.  This also improves agility as the business can rent more of what it needs when it needs it and so pay less at quiet times when the services aren’t needed. 

With those kind of attractions, any finance director will want to evaluate this as an option in the same way that many businesses end up selling their offices and then renting them back, so whether Microsoft provide this service or not, the cloud is coming to a business near you.

For the IT Pro what the cloud does is remove more drudgery, such as fiddling around with actual kit.  In Microsoft the intention is to go further than this and remove the need to worry about the operating system and some of the key Microsoft applications be that Exchange, SharePoint, or SQL Server.  However what neither Microsoft nor any cloud provider will do for you is manage the actual data or connections to it.   

It’s also important to understand that the cloud doesn’t affect some parts of the IT Pro world, like end user support.  Yes there is InTune that simplifies the process (see Simon’s blog for more on this), but the helpdesk ITPro must still work with the end user to fix their problem.  

The public cloud is not going to be the right answer for certain businesses, for a number of reasons be it compliance or culture.  Also it will be some time before the cloud predominates and IT Pros will need to be on hand to help with transitions and integration of on and off premise services.  Some services might never make it into the cloud, and a mixed economy will be the norm for some time to come, and this too will require careful management by competent IT Pros.   

I think this will mean that the modern IT Pro role will evolve to one where we are much closer to the business. This might even see the end of the traditional IT department with the IT Pro becoming another specialist in a multifunction business team.  This sort of IT Pro will have to put more  thought, imagination, planning into their work, but will be trusted advisors to that team.

I think this will be rewarding and challenging work , and I for one can see why this is attractive to new entrants like Jonathan, as it was the boring stuff that my dad did that put me off IT.

Discuss!

I forgot one thing on my recent post on Reporting services interop, Report Builder.  What is Report Builder? it’s an end user (information worker in Microsoft speak), tool that creates a report that can run in SQL Server reporting services.  What this actually means is that it creates a xml file with an .rdl extension that is a set of instructions to run the report in a structure known as report definition language (RDL – hence the extension name of the file)

Report Builder 1 (RB1) came out with SQL Server 2005 Reporting Services and allowed end users to create simple reports from a report model . It was a click once application, and for me it was flawed because if you used BI Development Studio  (BIDS) to tweak a report originally created in RB1 it could no longer be opened in RB1, and it only worked off report models – a semantic layer over the underlying data which defines joins and calculations to be used in the RB1 reports.    

With the arrival of SQL Server 2008 , reporting services got a complete overhaul and a completely new report builder, Report Builder 2 (RB2).  This was created by skinning the report designer in BIDS  with an office 2007 style ribbon and then making this a click once application that could be downloaded from Report Manager/ SharePoint (if you have reporting services running in integrated mode)..

So RB2 had the same functionality as Report designer in BIDS, you could use all the new charts, write queries from any source etc.

SQL Server 2008 R2 has now been released and this is essentially an update and enhancement to the BI tools in SQL Server, including reporting services. So the new Report Builder (RB3) supports maps, new charts like sparklines and allows parts of a report to be saved off and re-used..

Each version of Report Builder only works with its equivalent version of SQL Server as the features in a particular version of Report Builder depend in turn on the features available in a specific version of Reporting Services e.g. the new charts in RB2 are only available in the report definition language in Reporting Services 2008

Hopefully this all makes sense, but I have been asked this before and it still turns up on internal e-mail threads, hence this post.

Finally what of the future?  All I know is that there are plans for Reporting Services to be included in SQL Azure (I don’t know when) and in that scenario the version of Report Builder you’ll need will again be specific to that version and so I am not sure what that will be so I am not going to guess.

I have got the highest respect for Hyper-V. It’s simple to use I don’t need to learn any new interface (it’s just another mmc snap-in)  in which to mange the virtual world.  However when someone asks me if they can install other roles alongside hyper-V in production I say “Oi mate NO don’t do it”.

The problem here is that it is possible to do this with Hyper-V, for example to run SQL Server  alongside hyper-v  in the host operating system.  It’s not  a FAQ but do get asked about the advisability of this by customers and I have seen internal threads asking the same thing.  Rather than ask why this a bad idea let me debunk some “myths” about why people think it is a good idea:

• You’ll get more performance from the system this way.  The theory runs that SQL will run only slightly slower on a virtual machine (vm) than it will on the same physical hardware.  However if you are after performance then SQL Server is now going to have to contend for resources the hosted virtual machines on the same server, the hypervisor expects to get the whole physical server and works underneath the host operating system anyway and so will still affect SQL server performance either way. In fact the host operating system is bottom of the list for memory resources the virtual machine get priority and you can’t reserve cpu for the physical machine either.
• It’s cheaper from a licensing perspective. No it’s not. If you use the host operating system for anything other than hyper-v you must license that operating system.  You won’t be able to use any fewer licenses of any other application like SQL Server doing this either. Microsoft’s licensing as applied to virtualisation takes the decision to virtualise or not out of the equation.
• It’s best practice. Really? send me a link to the site that says so and I’ll send you some swag.
• I’ve been told to.  Please tell me who told you do this so I can enlighten them.

In fact the only reason I put applications alongside hyper-V is for ease of use and a better experience when I am showing how infrastructure stuff works in my demo environment.  So I have Office installed on the base OS along with on using hyper-V for demos.

If I was putting a virtual machine infrastructure into production, I would use the free Hyper-V Server in as the host operating system and remotely manage this with server manager or System Center on a client machine, as this is lightweight and secure and still easy to manage.

When I saw SQL Server redundancy on a recent internal e-mail I wondered if it was about a replacement for SQL Server , DBA job losses, or the rise of the NoSQL movement.  It turned out to be nothing more than a general ask about the pros and cons of clustering and mirroring. If there are internal Microsoft staff who don’t know when to use what, then I imagine there must still be confusion in the real world about these two approaches. 

First some introductions:

SQL Server clustering is built  on Windows Server clustering, which use a number of servers (nodes) and shared storage to automatically recover a service from a server failure.  The service doesn’t have to be SQL Server it could be IIS, or Hyper-V for example.  It’s also important to understand that the recovery form failure (failover) is not instantaneous and can take several minutes. 

As far a s SQL Server is concerned there are good things about clustering:

• Clustering operates at the instance level so all of the databases in the instance that are clustered are protected in the same way including the system databases.  This means protecting an application that uses more than one database is straightforward and also all the metadata, logins etc, associated with the application that are in the system database are also protected.
• The whole cluster looks like a  SQL Server instance which doesn’t change no matter which server is is the active one.  So any application connection can be used and this doesn’t need to be changed when a server failover occurs

And some things be aware of:

• There is only one copy of any database in a cluster it’s just that this located on shared storage.  This means there is no protection against logical error occurs like you or a user accidentally deleting  a row , table etc. so clustering is not a replacement for backups.
• Recovery and switchover to a new server in the cluster is not instantaneous and can take several minutes.  During this time an application might time out.  You could therefore lose a transaction that wasn’t committed.

SQL Server Mirroring  is a process by which a copy of a database is kept up to date on another (mirror) server, and this mirror server can take over as the lead (principal) server at anytime should the first server fail.

The good things about mirroring are:

• It’s really simple to setup
• Failover to the mirror in the event the principal fails is very quick.
• Mirroring comes in several flavours like synchronous and asynchronous depending on whether you want to wait for the transaction to be applied to the mirror beofr it commits on the principal.

and the things to be aware of are:

• Mirroring only protects one database at a time, so if you create logins and other system objects you’ll need to have a separate process (e.g. a script in sql agent) to keep these in synch on the mirror server.
• Although there is a second copy of the database on the mirror server, if you have mirroring setup in synchronous mode any logical errors on the principal will be copied to the mirror so mirroring is not a replacement for a good backup strategy.
• You can only mirror a database to one other server.
• Each of the servers in a principal mirror pair has it’s own server name ip address etc. so for the application to know which one to connect to you do need to use the ado.net native connection (SNAC) as when the application starts it doesn’t ‘know’ which server is the principal.

In summary here are the key comparisons..

Hopefully these strengths and weaknesses will guide you in when to use what but I also want to add in 2 other factors:

• You might also consider using both technologies together e.g. you could have a cluster on site with mirroring to remote location.
• Virtualisation technologies like Hyper-V allows virtual machines to run in a cluster, but there is only the one copy of the virtual machine shared between the cluster nodes.

I will defer to the SQL Server Customer Advisory Team (SQLCAT) for a detailed discussion on this and their whitepapers on getting the most out of mirroring and clustering as this is just a gentle introduction to the world of SQL redundancy, or high availability as it more commonly referred to.

Finally just to be clear there is no substitute for database backups whatever else you decide to use.

I think it takes a lot of effort and courage to stand up in front of an audience  and give a presentation.  Adding a demo into this mix only makes this worse, and so the SQLBIts organisers setup a speaker training day at our offices yesterday ran by Guy Smith-Ferrier.  Part of this was getting your laptop ready to present and I wanted to add a couple of tips to the excellent resources Guy as made available everyone on his site.

Hopefully most of you know that the windows logo key(logo) P will bring up the projector settings..

but logo X (initially seen in Vista) is even more useful and not as well known..

Note Dell  have added some goodness to my screen here as other vendors do, but the top section is part of Windows

You might want to check some of these setting well before you start to present:

• Are you running on mains power at full performance?
• Turn on presenter mode this can be further customised by clicking on it:

  

  To set an optional background for your presentation as not everyone needs to see your loved one(s) , Ferrari, football team etc. and clean backgrounds during demos make it easier to see what’s going on. You can also turn off your screensaver and set a different volume level.

  • Connectivity may be important to you but is a risk in any demo so have a backup plan.

  Guy also emphasised the need to set a nice big font in all the tools used in a demo be that Visual Studio, PowerShell, Command line. That can take time to do and makes it awkward to use your machine for normal work while it’s set like that. One of the audience in the training session was to create a demo user account and have all those settings in that profile leaving your normal profile for normal work. You’ll want to be clever with permissions here but i think that’s a great idea.

  Another approach is to use the built in Windows 7 zoom keys to focus on an area of the screen: logo  + zooms in, logo – zooms out and logo Esc exits.  Don’t do it too often though as it can make some people feel ill. 

• My additional Windows 7 demo top tips relate to display and remote desktop:

  • While Windows 7 does a pretty good job at picking up the projector capabilities, I have seen it get thrown by some of the newer graphics outputs, such as VDI out and the new display port. Some of these don’t work properly unless the port is connected so your only option is to boot with the projector connected if this is the case.  I did have a machine like this and ditched in favour of one with the trusty old vga out on the back (although it does have VDI too). 
  • Also be aware that remote desktop or any connection to a virtual machine will get thrown when you resize your actual desktop for the projection size, so I try and find out what size the projector will work at and manually set this up first, both in the physical and in Remote Desktop as per my Remote Desktop post a couple of weeks ago.
  • Be prepared for 1024 x 768 as this is the lowest common denominator for projectors out there.

  I have learnt most of this the hard way, so read this and Guy’s stuff so you don’t have to!

With a catchy name like Relational On Line Analytical Processing, is it any wonder that this technology hasn’t really entered mainstream use. Firstly what is it?

OLAP (On Line Analytical Processing) is  a data store organised for ad hoc query and analysis, a good way to think about this is as a very big Excel PivotTable which can be shared by many users.  The Relational refers to the fact that the data resides in a conventional relational database as opposed to the other kind – MOLAP (Multi-Dimensional) OLAP. In MOLAP the data store is structured differently and as the name suggest based on the dimensions the  data relates to using a coordinate system.  There is also HOLAP (Hybrid) which mixes the first two. 

So why have two ways of doing this?  Traditionally ROLAP has been able to handle much larger volumes of data while MOLAP is faster because MOLAP makes extensive use of RAM to store the dimensions and aggregations, as will as sophisticated indexes. In the case of SQL Server Analysis Services there is also some very clever compression which uses a bit more cpu usage but less slow moving IO. 

HOLAP tries to provide the best of both worlds by storing the most frequently referenced data in MOLAP and the rest in ROLAP. For example you might have your current year’s sales in MOLAP and prior years in ROLAP.

So in very large BI projects ROLAP has it’s place and some vendors only use this technology, while Microsoft offers all three flavours of OLAP in Analysis Services, with a common interface to use whichever one you need.  

When it comes to database and BI scale, Microsoft also has the SQL Customer Advisory Team (SQLCAT) and they have just published a paper on ROLAP best practice .  This whitepaper is pretty deep, but with a bit of work the SQLCAT team managed to get ROLAP to out perform MOLAP on the same data using the same hardware in certain cases.  However the work involved was considerable so I am not suggesting for a minute that you should stop using MOLAP as I find MOLAP solutions far easier to build and maintain. 

If you just want to know what to do for your ROLAP solution, rather then the details of the why, then the recommendations are summarised at the end of the paper.

SQL Server Reporting Services is nearly ubiquitous as SQL Server itself, it is in nearly all versions of SQL Server from Express (with Advanced services) to DataCenter edition and will soon be available in SQL Azure.   It’s been around for 8 years and if Microsoft used traditional version numbers it would now be on V5.  This wide spread availability and long history does cause some confusion when trying to work out what works with what.

So here are the FAQs I commonly see and get asked about :

FAQ 1.  Can Reporting Services report on data stored in [ insert relevant database platform here]?

Although Reporting Services is part of SQL Server it can consume data from pretty well any structured data source e.g Excel, xml, and anything you can get an ADO.Net,OLEDB or ODBC connector for. To design your reports you’ll need these database connectors (drivers) on the machine you’re designing the reports on and on the server they’ll be run from. 

Also don’t forget that if your source data is in a different version of SQL Server that doesn’t matter either, providing you again use the right connector/drivers.

FAQ 2. Can I design a report in the SQL Server 2XXX and then run it on Server 2YYY?

A report can generally be run on a later version of SQL server than it was designed for (unless you have custom code or rare authentication or security setup). Once you save it to the newer version of reporting services it will be automatically updated. However you can never do this the other way around i.e. run a report on an older version an the one it was designed in.  This is because the report definition language (RDL) gracefully changes in each release, so that older reports can run.

Other resources on upgrading to SQL Server 2008 R2 can be found here

FAQ 3 Can I use and older version of SQL server to host the reporting server databases

Reporting services uses 2 SQL Server databases, one to hold the metadata about the reports and the other as a temporary workspace.  You can use a different version of SQL Server to store these databases and the interop matrix looks like this

FAQ 4. Which versions of Reporting Services work with which versions of SharePoint?

FAQ 5.  Which versions of Visual Studio (VS) work with which version of Reporting Services?

When SQL Server 2005 came out it introduced the BI development studio (BIDS) which is essentially a cut down version of Visual Studio in that case 2005.  When SQL Server 20087 came out BIDS was built on VS2008.  However (BIDS) in SQL Server 2008 R2 is also still built on VS 2008 , but it’s simple to have another version alongside e.g. VS 2010.

FAQ 5.  Which versions of Visual Studio do I use to embed my reports into my applications? 

An ancillary question to this relates to the report viewer control that developers can use to embed reports in their projects. 

• For SQL Server 2005 there is a report viewer tool, described  here, that works in Visual Studio 2005
• If you followed that link you’ll notice there is an other version option that tales you to similar details about the report viewer control that works in VS 2008 but still against SQL Server 2005.
• The report viewer in VS2010 the only works against SQL Server 2008 and SQL Server 2008 R2 as detailed here.

in summary the allowed permutations are:

FAQ 6 How is Reporting Services licensed?

Probably a post in it’s own right, but to summarise: If you install a server component of SQL Server (so reporting services, analysis services the database engine etc.) on a server then you must license that server for SQL Server, either by CPU or user CAL just as you would the DB engine itself.

This covered in the SQL Server licensing quick reference guide on the SQL Server 2008 R2 licensing page   

This is a classic example of why I  blog,  I  will be asked about what this post relates to again and again and I’ll forget where the answer is unless I page it to a post!

Whether I am reading Chocky by John Wyndham or the 12 Caesers by Suetonius the illogical nature of a calendars can be annoying . In Chocky the alien wonders why we don’t have 16 months instead of 12 and why weeks aren’t made up of 8 days, and in the 12 Caesers successive emperors try to make sure that a year really is a year by adding extra months(July and August).  Months can also catch you out in SQL Server:

If I run this

SELECT DATEADD(MONTH,-1,’20100930’)

I will get back 

2010-08-30

because SQL Server has simply subtracted one month of the month number .  However what I was probably hoping for was the last day of the previous month as the 30th September is the last day of the month.  if you want to be fire proof and make no assumption about the day of the month that’s passed in then you could:

• ignore the day of the month that’s passed in by replacing it with the first of the month i.e. ‘01’
• take a day of this to give you the last day of the previous month

which in T-SQL looks like this

SELECT DATEADD(dd,-1,LEFT(CONVERT(VARCHAR(10),’20100930’,112),6) + ‘01’)

For extra credit can anyone  tell me what this should return and why , assuming you are in the UK like I am?

SELECT DATEADD(MONTH,-1,’17521010’)