Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

June, 2009

  • Excel add ins for Analysis Services

    Excel and analysis services are pretty open technologies so it’s very easier for anyone to grow their own stuff in for a richer experience, and even Microsoft provide things like the data mining add-ins which follow this principle.

    These add-ins are not confined to Microsoft and they are not always free.  What caught my eye recently is PTPower which free from SQL Server Power.  This is actually a Microsoft guy,Tyler Chessman.  What it does is to allow you to create calculated members and edit MDX from within Excel…


    the Show MDX button above brings up this dialog box..


    I would often want to cut and paste this MDX and use it as the basis of a reporting services report so that I could deliver a fixed view of a cube consistent with the analytical view in excel.

    The Calculations option underneath it gives you this …

    clip_image001[8] edit your calculated members and this all looks spookily familiar.  Of course the ability to do this would normally mean you would need to have permission to alter the cube, but Tyler has done this in such a way that the calculated members get saved in the workbook not the back-end cube.   This could cause chaos as your business logic is now in a local excel workbook which is what you were probably trying to avoid by using analysis  services in the first place! However you can mitigate this by publishing this spreadsheet in excel services.

    This kind of functionality will also come in useful when the in memory analytics come out in SQL Server 2008 r2/ Excel 2010 and no doubt Tyler will adapt all of this to work with the new release when it comes out next year (if indeed he needs to).

  • Vista and Windows 7 on the Atom

     Acer Aspire Revo R3600 - Micro PC featuring NVIDIA® ION graphicsNot everyone needs a flat out uber PC and I have just got my mum to buy an atom powered desktop from Ace (aspire Revo)..

    It’s just a big bigger than my router and it very quiet. More importantly it’s all mum needs for  a bit of surfing type an occasional document and keep up to date with e-mail from her friends and family.  Finally it cost about half of what the old one did without allowing for inflation.


    It came with Vista, and I was surprised how long it took to start up, that is until I looked at what was installed on it…

    acer installed

    There’s a lot of redundant stuff here and simply ripping all this stuff out cut the boot time to under half of what it was.  It’s not that there’s anything wrong with any particular item of  software in this list it’s simply that my mum won’t need the games, Adobe Reader, anti-Virus etc.  So I spent about an hour tidying up the mess and now the machine comes to life in about 30 seconds, which still beats her old XP machine. However with sleep and hibernate she won’t need to do this so often.

    In America CostCo will strip your new machine down to the bare operating system for an extra $50. However I am in the UK part of the EU, I mention this because when Windows 7 ships in the EU it won’t include Internet Explorer for compliance reasons.  So this initial setup is going to be even more fun as the manufacturer’s (OEM’s) like Acer can choose which browser to install so you might see just one or a selection in this list and internet explorer might well not be there at all. 

    This choice is great if you know what you’re doing, and deals between software houses and these OEMs may mean that new PCs are a little cheaper if they come shipped with all this stuff, but it doesn’t look like a lot of fun for the casual users out there. 

    I don’t see an immediate answer to this problem of choice and complexity, except that it is a great opportunity for IT professionals to some community service or run a service like the CostCo one I mentioned earlier.  A final thought might be to do what my friend James does by starting with a clean sheet and do a fast clean install from a USB stick and this post of his explains how to do this

    Technorati Tags: ,,
  • SQL Server upgrade perception and reality

    I had two contrasting conversation about upgrade to SQL Server 2008 yesterday. A good friend of mine Darren is keen to use SQL Server 2008 for his new projects because he understands how it makes much better use of the many cores available on his new servers and works well with Windows Server 2008.  Conversely Viral is working with a customer who is starting a new project but wants to uses SQL Server 2005 as they think it’s less resource intensive than SQL Server 2008.

    So which is correct?

    If you take a workload running on SQL server 2005 and upgrade it on the same machine to SQL server 2008, it will be a bit faster possibly 5-10%.  Will it use more CPU and memory? Only if you let it, and bizarrely the customer in question has a server with 8gb RAM in it but is only running 32 bit windows server 2003 on it so even with AWE support SQL can really only use 2Gb of that (leaving 1Gb for the operating system). 

    If performance was an issue then they would be better moving to 64 bit Windows Server 2008, reserve 2Gb for the operating system and give the other 6Gb available to SQL Server(i.e. 3x what they have now).  Windows Server 2008 has a much better networking stack and won’t suffer partition alignment issues (as per this post) that can also slow SQL Server down on old windows server versions.

    However my main reason for concern with this customer is that in developing for an old release the work they are doing will have a shorter supported shelf life.  I suspect fear of the unknown may be at play here, but the tools to mange both versions are the same and setup is easier if anything in SQL Server 2008.  My final point was that if these issues did exist then a quick scan of the forums and boards would quickly reveal this, and all I could find was one guy having some connectivity issues on his Windows XP laptop, which he subsequently fixed by using the right version of the SQL native client.

    My advice on this is to at least try it, download the evaluation copy or pull down the full version if you have a TechNet subscription.

  • Try it Now, Morro, Microsoft Security Essentials

    It’s a sad fact that anti virus software is an essential part of using a computer in toady’s connected world, and many people baulk at paying extra for this , although it has never been that expensive.  Microsoft have released a beta of what was Morro, now known as Microsoft Security Essentials (MSE).  It’s in beta now but this program is only open to the first 750,0000 registrations.

    It has a lot in common with Forefront Client Security except that as a free consumer orientated product, it is missing all the manageability of it’s licensed sibling;

    • it can’t be controlled by group policy 
    • it isn’t integrated with System Center
    • it isn’t integrated with Network Access Protection (NAP).

    This common lineage means it is fit for purpose; the anti-spyware/anti-virus capabilities in Forefront Client Security are good enough to protect 50,000+ of Microsoft’s own PC’s and laptops including mine.

    Of course it would be a simple matter to include all of this in the upcoming Windows 7 operating system, but you can imagine that the anti-virus industry and the EU would take a dim view of this so this will be a separate download when it is released.

    Note 24/6/09 I have noticed that the site to register is already closed, and I think it may have just been for the US, so sorry about that.  When I know more I'll put up another post

  • TechNet Virtual Conference, Thanks for watching

    Our first effort at running a TechNet type road show as a virtual event worked pretty well but this platform did have a few niggles which I hope didn’t ruin your day too much.  This was mainly around the moderated chat which we couldn’t get into ourselves for most of the day. However judging by all the activity on twitter the content itself was vey well received e.g. these three popped up while I am typing this:

    • INTJohn: @benwatt put on a geeky T shirt, hold a couple of carrier bags, and sit next to the laundry basket for more conference realism #technetvconf
    • QUEEG505: #technetvconf Some excellent content, like the R2 stuff and Powershell 2.0 Have F1 prac live timing on iphone next to me , so happy :-)
    • ian0x0r: well thats its for me #technetvconf . Thanks for some good IT pro videos.

    Early indications are that about 4,000 are tuned in.  However if you couldn’t make it we will be leaving the videos and other content on the site for the next 3 months so you can still watch my sessions on Windows Server 2008 r2 and Data Protection Manager on the site here.

    BTW we’ll be doing this again once we’ve ironed out the rough spots as we’ve also had a lot of feedback from the far flung corners of the UK where TechNet road shows would never go.

  • Web Development & SQL Server Express part 3 - Development

    In part 3 of my odyssey into the world of express development I’m ready to actually start development.  I have a virtual machine (vm) with Microsoft’s free web development kit installed which comprises Visual Studio 2008 express for development and SQL Server 2008 express for my data repository.  On top of this I also selected the DotNetNuke application as I wanted to quickly setup some sort of data driven application and this seemed to do the trick.  However I quickly realised that while this is a cool bit of kit it would be better if I grew my own.

    At this point I must confess I am not a developer, so I resorted to the tutorials on web development on MSDN to see what they can do for me.  I jumped in at lesson 7 as this is the one where you start to play with data.  The good thing about this is that you get a couple of videos, a transcript and solution code to help you get started. I was puzzled that the tutorial is for Visual Studio 2005 and SQL Server 2005 so when I tried to open it I got this..


    This is actually a good thing; the latest .Net framework fully supports earlier versions and Visual Studio allows you to develop in the latest one (3.5 sp1) or .Net 2.  I am going to say yes and upgrade because I ‘m an evangelist.

    The scary thing for this ancient dba about this project is that it includes a database which doesn’t show up at all in the SQL Server Express management console, it is simply dynamically attached as part of the project, again this is a good thing if you’re a developer as you have your own sandbox where you can play.  Better yet I can do everything (add tables alter definitions etc.)  I need to in Visual Studio database explorer tab without going to the separate management studio…


    It’s also a really simple to bind data controls to this data, in the sample web site Lesson07exercise there is already a blank page (default2.aspx) where we can add some data bound controls. To do this I opened it in design view and from the toolbox select a SQLDataSource..


    Which you then configure to pint to that same database, by selecting the Microsoft SQL Dataset file and then browsing to the Grades.mdf file.  You can (and should) elect to save the connection string to the Application Configuration File.  Now I can configure what data I want to select from the tables and columns available, and I can sort and filter these as needed without writing an SQL and there’s a query builder if I want to take data from a combination of tables.  For this noddy demo I’m just going to get a list of the grades for each student.


    Now I can bind a data aware control to that data source, in this case I’ll take the gridview control by simply dragging it onto the SQL Data Source I already created.  I still have to choose the data source and then it picks up the data.

    I can test this at any time by debugging it , and when I do I get my grid shown up ina browser…


    Hopefully that’s convinced you that this is all pretty straightforward and that the Microsoft Web Platform although free has a lot of power behind it.  I would emphasis that if this is really new to you then you will want to do those MSDN tutorials in more depth and follow the work of experts like the resident web developer on our team, Mike Ormond.

  • Web Development & SQL Server Express part 2 Install

    My development skills are so old that the term 4GL and client server were the buzzwords of the day back then,  so I thought it was about time I had look at what’s out there now. A quick search on Microsoft web development on Bing got me to the Microsoft Web Platform Installer site…  

    This is a free set of tools and from this site you have two choices v1 and v2 beta with extra stuff in (which I’ll come back to later) being evangelist I am addicted to the word beta so  from the new virtual machine I created yesterday I fired up the installer and after a few seconds I get this dialog..


    I found this a bit confusing as it show all the new stuff- and it’s all new to me anyway. However clicking on the Web Platform Tab makes a lot more sense..


    it’s all free so I’ll have everything.  Checking the Web applications tab gives me even more stuff..


    I’ve selected the phot gallery and DotNetNuke options.  Now I can review what I’ve got..


    and you can see I’ve picked up some interesting options such as PHP and MySQL connectivity (although not the engine itself which you’ll have to get yourself from here).  However I am a SQL Server guy so I am staying with the express edition.

    Before installation starts I am asked for the the SQL Server sa password and whether I want mixed mode or windows integrated authentication.   I am going for mixed mode as I am not sure I want to rely on windows to authenticate incoming users to my database.


    and now the actual installation starts and I can get another cuppa.

    I did get one warning about SQL Server 2008 compatibility with Windows 7, but as I am installing sp1 anyway this is a spurious message.


    At least it didn’t kill the install and once it’s installed you are then in a position to setup your sites..


    My application will be DeepFat (my twitter handle) and then I can move onto setting up the backend database..


    Finally the DotNetNuke application for content management installs an we’re good to go. It all lokks good as there’s a folder in programs for SQL Server 2008..


    and firing up management studio express show the database I created..


    SO it’s all pretty straightforward to install on Windows 7 despite the fact that the installer and the operating system are in beta.

    Next up a little bit of development…

  • Web Development & SQL Server Express part 1 Virtual Machine

    I see a lot of tweets, and questions on forums about SQL Server Express from developers, particularly around installing it on client versions of windows, so I though I’ll give it a go and see if there is nay truth to the rumours.

    Rather than try it on  my physical desktop I decided to use a clean copy of Windows 7 RC and to do that I created a virtual machine in Windows Virtual PC beta , the version that is a download option with Windows 7 (from here).

    This look a bit like a folder when you open it but with an extra buttons to create virtual machine…


    ..The controls keys and general behaviour betray its lineage essentially a cut down 32 bit only version of Hyper-V so CTRL-ALT-END is equivalent to CTRL-ALT-DEL and CTRL-ALT-LEFT ARROW will release the mouse if the integration components aren’t installed. BTW it still uses the ubiquitous VHD file for it’s hard disks so you can migrate an old virtual machine (or VPC as they’re sometime called) to this version.  For this post I simply created a new virtual machine and mounted the windows 7 RC .iso file and left it to install while I had a nice cuppa.  After that I installed the integration components just like in the old version of virtual PC to get some proper speed out of the virtual machine and to enable folder sharing and networking.  Having done that I am now all set with a machine to play with..


    and from settings in the top menu I can enable integration components and configure the Virtual Machine..


    With shared drives checked I can get back to my physical hard disk from explorer in the virtual machine and pick up the network which is why you can see my blog in the second screenshot.


    So it’s all pretty painless and is way way faster than Virtual PC  2007 as it makes much better use of the cpu virtualisation from AMD / Intel.

    In my next post we’re going to fire up SQL Server Express with Advanced Services and Visual Studio Express.

  • MDX Analyzer

    Compared to T-SQL and the database engine , MDX and analysis services is a somewhat poor relation and is usually catching up. There’s no resource governor in SQL Server 2008 or intellisense for MDX in management studio.  I expect this is because while T-SQL is used by far more people than those using MDX.

    However despite it’s original intentions MDX is much harder than SQL in the same way as piloting an aircraft is harder than driving a car, you have  more dimensions to play with.  So in developing your own MDX queries you need all the help you can get.

    Enter MDX studio by Mosha Pasumansky (one of the guys who invented MDX), a free tool that give you query analyser for MDX (If you remember back to SQL Server 2000). Thius gives you intellisense, and query performance telemetry in a simple .exe you don’t even need to install!

    I have to confess I only found out about this by listening to Ashwani Roy (IMGroup) presenting at last night’s BI community event.  If you missed it Tony Rogerson  will be putting up Live Meeting recordings of the sessions on the same site.  Do watch his site for upcoming meetings, I guarantee you will learn something useful if you attend or watch them online.

  • Free stuff SQL Server Courses & Software

    I knew that there were a few course on SQL Server 2008 on the Microsoft training site:

    • Course 2778A: Writing Queries Using Microsoft SQL Server 2008 Transact—SQL

    • Course 6231A: Maintaining a Microsoft SQL Server 2008 Database

    • Course 6232A: Implementing a Microsoft SQL Server 2008 Database

    • Course 6234A: Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services

    • Course 6235A: Implementing and Maintaining Microsoft SQL Server 2008 Integration Services

    • Course 6236A: Implementing and Maintaining Microsoft SQL Server 2008 Reporting Service

    • Course 6158C: Updating Your SQL 2005 Skills to SQL Server 2008

    • Course 6317: Upgrading Your SQL Server 2000 Skills to SQL Server 2008

    and I like the idea of the last one as there are probably loads of SQL Server 2000 DBA contemplating jumping straight to SQL Server 2008.

    However I was surprised to see that on the MS Learning site there is a further incentive for doing any one of them before 30th June – a free copy of SQL Server 2008 standard edition albeit a single CAL so you can continue to train on it. 

    If you need more than that like an operating system license to run it on or other parts of the Microsoft stack to interoperate with it then a TechNet subscription would be a good place to start.  If you’re in the UK and haven’t got one but would like one e-mail me  telling me why and I will see what I can do.

  • Reinventing the Wheel



    A lot of the things you need to do with SQL Server may well have been done before even if it’s using the new features in the new version , SQL Server 2008.  The challenge is to find this stuff and be able to make use of that earlier effort and a key resource for this is codeplex an open source portal of stuff you can use on the Microsoft platform.  I say open source because this stuff is free to use under some recognised license such as , GNU GPL, Microsoft Public License etc.

    A good example of the good stuff on here is all the projects that use the new spatial capabilities of SQL Server.  There are projects containing the freely to use US census data, map point integration, various type conversions and a start  on providing routing functionality. I also found a good example of how to use merge and table value parameters in integration services.

    Of course this shouldn’t be one way traffic; a lot of the code here is provided by Microsoft, but MVP’s and partners also contribute, but there are no rules to stop you helping out as well.  This might just be to comment and suggest improvements to modifying a project in some way to writing your own.

    At the very least it should stop you doing so much wheel reinvention.


  • Putting SQL Server on the MAP

    Usually when I mention SQL Server and map in the same blog it’s a post on geospatial data but not today; today I want to talk about assessment and planning your SQL Server infrastructure. 

    I know of several DBA’s who don’t know how many SQL Servers they have much less what stare they are in i.e. which patches and fixes been applied and which components have been installed.  This is not there fault it is often the case that new applications get installed that require SQL Server without the DBA knowing.  For example I met this business user at an even who works for the atomic weapons inspectorate and he had to get special permission to install SQL server as it’s not a standard and he would be the first to use it.  Once he got approval and turned on the client tools on his desktop to look for his new server he was amazed to find 50+ instances already out there.

    So how do you get a grip on this,  there really isn’t anything out of the box in SQL Server itself , you can get clever with dynamic management views (dmv’s), and policy management (if you have SQL Server 2008) but that’s about it. You are in a good place if you have System Centre Configuration Manager (SCCM) or a similar tool form a third party.

    However this also a free alternative the Microsoft Assessment and Planning (MAP) Toolkit, which has a special version for SQL Server as well as for other products like Windows Vista, 7 Server 2008 and 2008r2 and for initiatives like security and Green IT.

    MAP is designed to be used as part of a consolidation or upgrade/migration exercise, but there’s nothing to stop you using at regularly, though I would advise always downloading the latest one.

    The tool gives you a detailed  assessment spreadsheet of what’s on your server with a tab for each instance …


    with columns for everything form is clustered to service pack details, operating system and hardware.  You also get another tab with all the components on:


    I can’t think of another simple way of getting at this so easily and quickly for no money.  You also get a proposal document, which you can wave under your managers nose should you actually use this for its intended purpose of assisting in the planning of a consolidation and upgrade project.

    MAP uses WMI and so it doesn’t matter if SQL Server has been configured to use non default ports (which of course is best practice).This does need to make sure that your servers have some ports open for this:

    • TCP port 135  - remote administration. If you have another host firewall installed, then you will need to allow network traffic through this port.
    • TCP ports 139 and 445, and UDP ports 137 and 138 – File and Printer sharing.

    All of the gathered telemetry is stored in a SQL Server (naturelment)  express database on the machine you install the MAP toolkit on.  MAP can use another edition of SQL Server for this but you must create a specific instance called MAP (yes instance not database) so I don’t see the point.  What you can do is to backup  the created database.  There is a file SupportBackup.cmd in the “\Help\Support Files” subdirectory of the MAP installation folder in Program Files. Run SupportBackup.cmd without any parameters to get help. If you’re using Windows Vista or Windows Server 2008, be sure to run the command prompt using “Run as Administrator.”

    Pass the name of the database you want to back up to this batch file. After the backup completes, a new folder will be created in the Support Files directory with the same name as your database. In that folder, there will be a file called databasename.bkp. This is your database backup. Copy this file to the machine you want to restore the database on.

    To restore a database: Use standard restore T-SQL or management studio to where you have SQL Server already installed.

    On final thought this will also play across virtual machines, and report what resources the virtual machine has been given,even if this isn’t using Microsoft’s virtualisation (Hyper-V).  There is a specific toolkit for Hyper-V as well which is there to help you plan your server consolidation irrespective of the workloads they are running.


  • Windows 7 rumours and facts

    The latest rumours around Windows 7 can be found on this Reuters article citing a public release in October and I’ll leave you to judge the voracity of that.  However for those of you that haven’t tried it then it might be just as important to know why you should bother.

    My good friends James O’Neill and Jamie Burgess  have recorded a set of discussions and demos called the Road to Windows 7  to help you do just that. 


    James also has excellent blogs on installing Windows 7 from a USB stick and how well it plays on those netbook fashion accessories  

    I would also point out that Windows Server 2008 r2 is also being released at the same time and I have SQL Server 2005 and 2008 running just fine on that.

    Technorati Tags: ,,
  • Complex Event Processing

    Life isn’t fair, Microsoft bring out some new fangled gizmo, but I needed it five years ago. Back then I was working for several utility companies who were managing such things as water quality & half hour electricity meter readings for large customers.  Trapping and modeling the data either meant a lot of cludgy coding or forking out for some bespoke application that more or less did what you needed.

    In 2010 all of this will be much easier to do thanks to Complex Event Processing (CEP) built on SQL Server:


    Apologies for the quality of the diagram, but hopefully you can see the CEP engine in the big box on the middle which has input adapters  to connect to all sorts of sources and output adapter which can issue commands based on the events that have occurred in the form needed by the output device.  SQL Server sits underneath all of this to allow events to be aggregated and paired.

    So what could you use it for:

    • clickstream analysis (web traffic),
    • telemetry including energy consumption as CEP can handle up to 10,000 events a second
    • electronic trading.
    • Possibly even Robotics.

    If this is something you might be interested in then there is a whitepaper on it here, if it’s something you’re really interested in there is a special TAP program you can contact  Torsten Grabs, Microsoft Senior Program Manager for CEP.

  • SQL Server BI Community Evening, 10th June, Reading

    I wanted to call out the BI evening on 10th June because they’ve given me a slot to talk about SQL Server 2008 r2 and specifically the BI features you can expect.   There’s also sessions on Data Modeling and information architecture from Alex Pratt & Andrew de Rozairo (Sybase) and one on Attribute relationships in MDX studio by Ashwani Roy (IMGroup). 

    The evening starts at 5:30 (registration) and finishes at 9pm at the Microsoft Campus (Building 3) in Reading. Click here to register.

    The other upcoming events can all be found on the UK SQL Server community site SQL Server FAQ:

    •    Wednesday 3rd June, LEEDS Evening Meeting

    SQL Server Performance Troubleshooting Techniques - Sponsored by Quest Software

    •    Thursday 4th June Online LIVE MEETING

    Integrating Data Mining into your BI Solution; Allan Mitchell SQL Server MVP

    •    Thursday 11th June Online LIVE MEETING

    SQL Server Basics (Part 2 : An introduction to using the product - "What is SQL Server? The component parts: cache, databases, files, logs."); Tony Rogerson, SQL Server MVP

    •    Thursday 18th June Online LIVE MEETING
    Creating a star schema and OLAP cube - Trevor Dwyer

    •    Thursday 18th June LONDON Evening Meeting 

    SQL Server User Group - Evening Event

    •    Monday 6th July CAMBRIDGE Evening Meeting

    SQL Server User Group - sponsored by Redgate

    •    Thursday 16th July MANCHESTER Evening Meeting

    Manchester SQL Server User Group Meeting - Analysis Services evening with Chris Testa-O'Neill and Allan Mitchell SQL Server MVP.

    •    Thursday 30th July Online LIVE MEETING

    TBC: Microsoft on Communities

    •    Thursday 6th August LONDON Evening Meeting

    SQL Server Usergroup does the Great British Beer Festival

    •    Wednesday 9th September READING Evening Meeting

    Database Administration/Management/Engine Focused Event - Agenda TBC

    •    Thursday 10th September MANCHESTER Evening Meeting

    Manchester SQL Server User Group Meeting - provisional date - Database developer focused event - Agenda to be confirmed

    I know the organisers are always on the lookout for speakers to share their experiences, even if it’s only for a 5-10 minute nugget so please ping Tony Rogerson if you want to help out.