Insufficient data from Andrew Fryer

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

May, 2010

  • Installing SQL Server 2008 R2 Express

    In this article I wanted to throught the dialogs you have to complete to try and clarify some of the questions you’re asked.

    I am using the basic install of SQL Server Express and the first step is to download it from here. In my example I am installing to a virtual machine running windows 7 and will be using the 32bit (x86) version.

    Right Click on the download (SQLEXPR32_x86_ENU.exe) and select run as administrator. You’ll get a standard user account control popup asking to confirm you wish to run the program. The EXE will then unpack itself to a temp directory and then launch the installer ..





    This screen is common to all editions of SQL Server 2008 R2, and most of it doesn’t apply to Express edition.  It’s the installation option I want so I select installation on the left and then the top option New installation or add features to a new installation.

    Now I get the usual license agreement screen..


    I click on I accept the the license terms and click next. This starts the installation of the setup support files and then takes you the feature selection screen..


    where I can choose what I want installed and where it will be put.  For Express edition it’s limited..

    • the database engine itself
    • replication ( the ability to publish and or subscribe to changes to selected tables in another database)
    • Client SDK

    I’m going to grab everything and click next, which brings up the Instance Configuration screen..


    If you don’t now what an instance is I can see why this is confusing.  An instance is an installation of SQL Server; and this concept exists because it’s possible to install SQL Server multiple times on the same machine and each of these can be different versions of SQL Server (e.g. 2005, 200, 2008 and so on).  Each instance has an ID ( for internal use) and a name.  to connect to particular instance you would specify MachineName\InstanceName.  In the screen above an instance name of SQL Express is set. I could select default instance and this would simply be refeenceced by the MachineName

    However I am going to leave the instance name as SQL Express and click next.  Now I have to specify what account will be used to run the two SQL Sever services.  BTW the browser service is there to browse the instances of SQL Server on the machine and is only enabled where there is more than one instance installed.

    If this was a production environment and/or sitting behind an internet facing application then a low privilege account is recommended (i.e. not a local admin account).  the default is perfectly acceptable and I would only change if need to adhere to corporate policy which would typically be to use a domain account.

    Note there is a collation tab at the top of the screen which displays the collation options..


    which describes how terms will be matched in queries e.g. case sensitvity etc.I am happy with this and I click Next  to bring up the Account Provisioning  tab of the Server Configuration screen..


    Firstly you need to decide on the authentication mode.  There are two ways users can log into SQL Server and be granted appropriate rights:

    • using windows authentication i.e. you are logged onto the local machine (as I am here) or more usually you are logging on to a domain.
    • using mixed mode allows users to be authenticated by SQL Server itself  in addition to windows authentication which is always available. SQL Server authentication is useful if you are accessing SQL Server from a non domain joined machine. It requires you to have a SQL Server administrator account and to give this a password (BTW you can change the name of this account to make your system harder to hack). I am going with mixed mode, and so I am using a strong password.


    BTW if your a local admin on your machine and you can’t get into your SQL Server express system then there is a script on the SQL Server express blog to do that

    There are other tabs here are:

    • Data Directories which is where the database you create and the system databases are stored
    • User instances allows child instances of SQL Server to be run by users in their own security context. For more on this refer to this post from the SQL Express team
    • Filestream enables SQL Server to handle large unstructured files.  These reside in special directories rather than in the database, and in Express edition this means that these files don’t affect the 10Gb limit on database size. more on filestream can be found here.


    I am leaving everything as is before clicking next to bring up the error reporting  screen..


    which simply asks if you are happy to report errors to Microsoft. I click next  the installation proper begins.  After about 4 minutes the install completed successfully




    click close to finish the installation. Before you close the installation program you might want to check out some of the links on the resources tab.

    Hopefully that wasn’t to painful and will encourage you to install SQL Server express and try it out as the database platform for your applications

  • Getting Started with SQL Server 2008 R2 Express

    One of the criticism of SQL Server I hear on occasion is that it’s too big and therefore takes too to long to download and install.  This is partly due to the inclusion of the client tools and other add-ons that are included, for example the BI development studio and SQL Server Management Studio (SSMS)  are cut down versions of visual studio, and then there the various BI components such as reporting services.

    SQL Server Express is the lightest of the versions, but still has database compatibility with is paid for premium editions. Apart from it being free it also has a more modular download and as you can see below the management tools are bigger than the DB Engine itself.


    Even with my paltry broadband (1.0Mb if there is no sunspot activity or pigeons on the cable) I can get installed and running in 20 minutes, provided I don’t want the footprint of extra tools.

    One thing here did slip my notice and that is that in SQL Server 2008 R2 express edition now has a 10Gb database size limit (up from 4Gb in the previous version).  This is so that SQL Server Express and SQL Azure can exchange databases as they both have the same size limitation on them at the moment.

    Of course if you can’t be bothered to download any of this at all there is SQL Azure (as per previous posts) but the only free offer at the time of writing is for a 1Gb (aka web edition database) with limited traffic for three months.

    Back to SQL Server Express. Notice that in the screenshot above that with the advanced services option also gives you reporting services, however in express this is limited to only being able to report on databases in SQL Express itself, where in other edition you can not only hook reporting services up to other SQL Server databases, you can also report against virtually any ole DB or ADO.Net source.

    Another installation option is to use the Web Platform Installer which will also pull down Visual Studio 2010 express and a variety of tools.  I have already tried that in a previous post and that was pretty painless albeit it was all beta tools at the time where now the released products are included.

    The next objection I get is that the installation process is hard. I can only assume this refers to all the questions you get asked and rather than lengthen this post any more there is a separate article on my blog here which goes through the installation of SQL Express, with my take on those questions, if that helps.

  • Beta Decay

    I am pretty sure the term beta in software isn’t related to atomic decay, but there are some similarities in that an atom that decays is unstable and decays after a period of time to something more stable e.g. Carbon14 to Nitrogen14.  In the Microsoft world, the time to decay is usually 180 days (compared to a half life of 5,730 years for Carbon 14 to decay) and this results in fallout-  the loss of bugs identified during the beat period, and some performance improvements and small enhancements leading to a very stable released product:


    Following all of the recent product launches, I have now spent a couple of days tearing down my BI demos and rebuilding them to use the released products of SQL Server 2008 R2, SharePoint 2010, Office 2010 Visual Studio 2010, and of course PowerPivot.  The problem with this process is that it’s never going to be a supported upgrade path so I have had to resort to rip and replacing everything short of the operating systems. 

    Also, You do need to beware of beta blogs, tweets screen casts etc. where some of the advice, screenshots etc. will have changed now the products have been released.

    The good news here is that all the installation is a lot easier, and in that sense SQL Server 2008 R2 for instance is less painful than getting SQL Server 7 or 2000 installed albeit that the install is larger. I also had a lot less trouble in getting PowerPivot working, in that it worked out of the box with me downloading hot-fixes and fiddling with permissions.  However it is important that you don’t try and mix and match beta  and rtm code e.g. putting the beta add-in of PowerPivot for Excel on top of the rtm edition Excel 2010 is not going to work.

    Finally remember the beta clock is always ticking, so your demo/proof of concept will decay which could have some fallout for you, but unlike atomic decay it’s down to you to fix. 

  • SQL Azure – Back to the Future

    SQL Azure Logo

    As I write this I am setting up a SQL Azure database to use as the source for some of my BI demos over the coming months, and the experience takes me back to when I first started in business intelligence.

    If you haven’t come across SQL Azure, imagine someone  has set up SQL Server for you on a server and all you have to do is create your databases, database objects and permissions for your users to use them.  The someone is Microsoft, and the service is available on a pay per use basis. It’s sufficiently like SQL Server that you can use all the tools you are familiar with to manage it and access data from it.  But there are differences..

    The lack of tools and capabilities in SQL Azure reminds me particularly of SQL Server 6.5, hence the title of the post. For example there’s no mirroring or clustering, as in SQL Azure there is triple failover built in, it’s just the user doesn’t have any control over it.

    Some of the key new features of SQL Server 2008 are also missing some of which is good and some bad.  It doesn’t matter that the management features like Policy Based Management and Resource Governor aren’t there as they are also irrelevant in an Azure world where Microsoft is managing the instance and underlying server for you. However other things are still being added like the new data types introduced in SQL Server 2008.  (like geospatial, hierarchy and the other clr data types).

    Coming back to the subject of tools, what is not obvious is that management studio in the recently released SQL Server 2008 R2  allows you to also manage your SQL Azure databases.


    simply connect..


    and it business as usual. (not the odd version number for SQL Azure -  10.25!)

    BTW my good friend Eric Nelson has an excellent post on that here .

    The good news here is that there is a web based Silverlight management application in the works, called project Houston, which will allow you to do all the basic stuff in your browser like managing tables and there are third party tools also out there as well.

    This leads me to one of the best things about SQL Azure namely upgrades.  As SQL Azure is enhanced with features like geospatial, project Houston etc. your database gets upgraded which takes the major headache of upgrades out of the equation.  The releases for SQL Azure are every 90 days compared with 3 years for SQL Server that you install on your server.  Next up for release in June is the geospatial data type and where databases are currently limited to 1 or 10Gb (and priced accordingly) there will be a new 50gb version.

    To quote my good friend Keith Burns at the recent TechDays event, “SQL Azure is actually pretty boring” and when it comes to databases boring is good,so this SQL Azure thing might well be the future.

  • SQL Server 2008 R2 Overview

    I have a fair few posts on SQL Server 2008 R2 on my blog, but I have got so into individual posts I thought a short summary of the new release might be useful for the casual reader/ IT manager, etc.

    SQL Server 2008 R2 has now been released to manufacture, but you may be wondering what is different about the new version and why it is branded R2 and not SQL Server 2010. All of the new capabilities in this version are new add-ons or changes to add-ons rather than changes to the database engine with a couple of small extensions. These extra capabilities are mainly in the business intelligence area and include:

    • PowerPivot – an in memory analysis engine that is an add-in to Excel 2010 and SharePoint 2010, to provide self-service analysis for business users from multiple sources.
    • Reporting Services has been extended to include report parts and new visualisations such as maps and sparklines. There is also a corresponding new version of Report Builder for information workers to work with these new features.
    • Master Data Services – is a master data management tool to allow control of reference data (customer, products etc.) in larger enterprises where this information is held and updated in multiple systems.
    • StreamInsight – complex event processing in memory to allow data streams of many thousands of events per second to be analysed and acted upon in near real time.

    There are also better tools for managing applications and multiple servers in this release.

    The subtle changes to the database engine are to do with compression. Compression can really improve database performance in certain scenarios like virtualisation where a small hit on CPU at the expense of reduced IO to get data onto and off disk is more than acceptable. The changes themselves are:

    • Enterprise edition now supports compression of Unicode data, which is particularly beneficial on systems like SAP which make extensive use of this data type.
    • Backup compression is now in Standard edition and this not only reduces the size of backups but reduces the time to make them and more importantly restore them.

    There are also changes to the editions of SQL Server 2008 R2 including a new Data Center edition. This is actually simplifies licensing because the all the higher editions of SQL Server correspond exactly to the Windows Server editions i.e. Standard, Enterprise and the new Data Center editions of SQL Server 2008 R2 have the same memory, CPU and virtualisation limits as the corresponding edition of Windows Server 2008 R2. Full details of these changes are here.

  • Boot from VHD – help is at hand

    I have mentioned the process of booting to a VHD before and if you have tried it you’ll know it’s a bit fiddly and there is potential to cripple your machine if you’re not careful.  I was discussing this with one of the newer members of my division Software Architect Dave Bishop and he has adapted a PowerShell script from Devhawk which takes the guess work out of the process.

    # Script name: Create-BCD-Entry.ps1
    # Created on: 2010-03-09
    # Author: David Bishop
    # Extended version based on DevHawk posting

    # Purpose: Creating a BCD Entry for a VHD
    # Make sure to add the file extension to the VHD name when prompted

    $local:windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
    $local:windowsPrincipal = new-object 'System.Security.Principal.WindowsPrincipal' $local:windowsIdentity
    if ($local:windowsPrincipal.IsInRole("Administrators") -ne 1)
      throw "Create-BCD-Entry must be run from an administrator account"

    Write-Host "Information needed to create BCD entries for the VHD Image"
    $name = Read-Host "What do you want the image called at boot-up?"

    $file = Read-Host "enter the path and name of the vhd (include the.vhd extension)?"

    $a = new-object -comobject
    $intAnswer = $a.popup("A BCD entry will be created called $name and pointing to $file. Do you want to continue?", `
    0,"Create BCD Entry",4)
    If ($intAnswer -eq 6) {

        $f= resolve-path $file
        $vhdvalue = "[$($f.Drive.Name):]$($f.Path.Substring(2))"

    So now you can just run this from PowerShell and answer the two questions, what do you want to call the boot entry ( Boot to myVHD) and the VHD you want to boot to complete with the VHD extension. (e.g. c:\VHD\MyVM.VHD).

    Now I just need to persuade someone to let my buy an SSD and some more RAM to get my laptop to run like a server and I’m all set.

  • How they know you know

    I have just seen this hilarious video showing some fictitious IT job interviews on the Microsoft learning site. What makes it hilarious for me is that I have conducted interviews like this and had some of the bizarre answers this poor IT manager had and worse. At a couple of companies we’d set our own tests, but I do agree with the point of this video that certification is a force for good, and can be used to at least filter out unsuitable candidates.  Why because it shows me that a candidate at least has a good  knowledge of the subject and has taken time out to study and pass these exams. Of course there are candidates who have just crammed every exam under the sun and like in one of the interviews in the video doesn’t like working with people but that’s why you also interview them and not hand the job over to anyone with an exam.

    If we are talking about professionalism then I would argue that some of the most professional IT Professionals can be found in the services, and I was lucky enough to run into some seriously certified Warrant Officers in Royal Signals a few months ago and each of these foremen, as they are known, were both MCSE and CCNA qualified as part of their role.

    So if you’re working on Microsoft based infrastructure  and not certified in some way, can I politely ask why not?

  • SQL Skills

    The term master class is often overused but probably not in the case I am about to refer to.  Two of the authorities on SQL Server are over in the UK courtesy of SQL Server Magazine to run a SQL server master class on 17th June at the Radisson Edwardian Heathrow.

    Mentioning two of the authorities here somewhat gives the game away to those in the know as this would be Kimberly Tripp and Paul Randall, aka SQL Skills

    They probably know more about the SQL Server db engine, than anyone I know but what makes them unique IMO is their ability to make deep technical stuff understandable, although I don’t know anyone else with blade servers in their garage either!

    So if you’re like me and prefer to learn in a classroom environment, rather than wading through videos and blogs and books, then this could well be the best £200- £250 (depending on how fast you book) you will spend on career development this year.

    The link to register is here, and I will see you there as we are sponsoring the event.

  • A last word on Policy based Management

    If I search my blog for Policy Based Management in SQL Server, I notice there are a fair few posts on the matter. I am not going to apologise for this as it’s a vital tool in any DBA’s armoury in the world of ever increasing databases compliance and risk management.  My blog is also like paging to disk, so when my brain is full of stuff I write to blog so I can fetch it later. A good example of this is so I can find the links for the resources I am often asked about.

    Pro SQL Server 2008 Policy-Based Management book coverHowever I no longer need to really bother blogging about this topic any more because the definitive work has just landed on my desk,Pro SQL Server 2008 Policy Based Management from Apress.



    There are several reasons to I would recommend this book specifically:

    • It covers all the bases including building your own reporting solution around BPMusing powershell, SQL server tables and, or using the Enterprise Policy Management Framework on codeplex.
    • It is very practical and covers the wider topics around compliance very well.
    • It is is written in English, not techno babble and isn’t overburdened with lists of features and functions which I can get from books on line or a system view.

    If you’re serious about securing and managing SQL Server, then start looking at this topic, and if you hate watching screencasts or reading blogs to find out how to get stuff done then the book is definitely going to help.

  • SQL Server 2008 R2 online Q&A

    I have to confess that I’m not a huge fan of certain type of social media, maybe this is an age thing but perhaps not. I just want to get stuff done and bizarrely at the end of the day I get out my paint brushes, play guitar, or dig for victory in my back garden none of which are related to, computers or my evangelist role at Microsoft.

    When I am at work I use blogs, and twitter to get stuff done , but I have never really bough into the forums thing, either to seek help or to give it, as it seems a bit disconnected.  On the help front I am never going to resolve your installation woes unless we actually meet up and I sit in front of your machine, I am more into the sort of help around capabilities, integration of Microsoft’s newer servers.

    So ironically I have been volunteered for an online Q&A on the SQL Server magazine Microsoft Forum on 19th May for the day (that’s a UK time zone based day just to be clear).  Actually this makes more sense than a traditional forum as we should be able to field pretty well any question on SQL Server on the day.  Except that is those support type questions, which shouldn’t be an issue as we have an excellent support team who are available all of the time for that.

    So if you are confused by master data services, concerned about BI chaos with PowerPivot or  convinced that the cloud is the spawn of satan please feel free to quiz and comment away.