Insufficient data from Andrew Fryer

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

August, 2011

  • Beyond Relational - Installing SQL Server FileTable

    A long time ago in a land far far away, somebody had the bright idea of using a database as part of a file system and the Windows Future System (WinFS) was born – but this is not that story.  However the idea and some of the team moved to the SQL Server engineering team and FileStream was the result in SQL Server 2008.   I have several posts on FileStream (here) and in essence it allows a win32 call to create read update and delete files form within the context of a SQL Server transaction.  This preserves the referential integrity between the data about the objects in the database and the objects themselves and backing up and restoring the database also picks up the objects as well.  The most interesting use case of FileStream is SQLRace developed by McLaren Electronics to monitor the performance of F1 cars not just for them but for the FIA and other manufacturers.

    In the next release of SQL Server (Project Denali) there is a new feature built on top of this called FileTable.  This provides the ability to read write and update the FileStream objects directly for example from explorer via a UNC share.  The way this works is that you declare a share for the database instance and when you create or modify a database for use with FileTable you associate a directory name with it.  Underneath that directory there is then a directory for each FileTable.  FileTables are like ordinary database tables except that their schema is predefined and cannot be changed.

    I have a short video here on exactly how to do this ..


    In subsequent posts I’ll show you what you can do with FileTable data both from inside and outside of SQL Server.


    If want to try this yourself all you’ll need SQL Server Denali ctp 3

    I created the database with this script..

    CREATE DATABASE [Denali_FileTable]
    ( NAME = N'Denali_FileTable', 
       FILENAME = N'C:\Program Files\Microsoft SQL Server\
       MSSQL11.MSSQLSERVER\MSSQL\DATA\Denali_FileTable.mdf' , 
       SIZE = 102400KB , FILEGROWTH = 1024KB ),
       (name= FilestreamFG, FILENAME= 'C:\FileStremFG')
    LOG ON 
       ( NAME = N'Denali_FileTable_log', 
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.
        MSSQLSERVER\MSSQL\DATA\Denali_FileTable_log.ldf' , 
        SIZE = 1024KB , FILEGROWTH = 10%)
         DIRECTORY_NAME = N'FileTable')

    at the time of writing books online has doesn’t mention the need to create a FileStream file group (in my case FilestreamFG) – and this will get changed as we get nearer to the release of Denali

    The script to create my FileTable looks like this..

    USE Denali_FileTable
    CREATE TABLE MyDecks AS FileTable
        WITH ( 
              FileTable_Directory = 'My Decks',
              FileTable_Collate_Filename = database_default
  • SQL Server Reporting Services–if it’s important look after it

    Before I can commit funds to a new project be it at work or at home I need to know I can afford it; specifically is there enough money in the budget and to find that out I need to run a report.  This is what’s meant by operational reporting and this distinction means that this report can be considered to be mission critical.  Whole rafts or reports could be considered to be mission critical and this begs the questions how do I make them highly available if they are so important.

    The answer depends on how you are using reporting services and which version you have:

    SQL Server 20005(1), 2008, 2008 R2 Reporting Services in standalone mode

    In this type of deployment there are two moving parts, the web service and the databases and both can be configured to make reporting services highly available.The databases can be clustered but as there two of them:

    • The metadata database (typically called ReportServer) that stores the reports, security, schedules etc..
    • There is also a special temporary database called ReportServerTempDB for calculations and snapshotted reports.

    mirroring won’t work properly, so clustering is your best option here.  The web service of SQL Server can then be network load balanced (aka scale out deployment) where multiple reporting services servers reference the same databases, giving you redundancy if one fails.  The complete how to on this is on TechNet, but note there is nothing actually in Reporting Services that does the Load balancing bit itself.

    SQL Server Denali in standalone mode

    As above but you cold use the new AlwaysOn feature to protect the databases in an availability Group.  This uses clustering services but not shared storage, to continuously replicate the databases multiple locations and keep them in synch.  I have a short post on AlwaysOn here and the definitive word is on TechNet

    SQL Server 2008, 2008 R2 Reporting Services in SharePoint mode

    Here there is an extra layer , SharePoint, but otherwise things remain the same, there’s still the databases to cluster and the reporting services servers can be load balanced.  However there is a raft of options to increase scalability and availability in Reporting Services Books Online:

    Example deployment with many servers. 

    Note that the reporting services add-in referred to here provides the report viewer web parts and integration components to make reports behave like any other document in SharePoint.

    SQL Server Denali Reporting Services in SharePoint mode. 

    What is different here is that Reporting Services is now just like any other application web services in SharePoint. so the  The difference is the way your configuration is done – it’s all done through SharePoint administration apart from selecting the installation options when you actually install SQL Server, as per my last post


    So a quick 101 run through of your high availability options for reporting services, and just to be clear there is nothing in reporting services itself to support high availability (clustering or network load balancing), but the service can be configure to use these features.

    (1) SQL Server 2005 is now in extended support, so unless you are paying for that service you aren’t really in a highly available environment as support is part of the equation

  • Installing Denali ctp3–how hard can it be

    l forget sometimes that installing SQL Server isn’t that straightforward, in part because it is now so much more than the database engine, that was SQL Server 7 when I started.  I also think its important that you understand the choices you are making when installing it although many of these can be undone by reconfiguration later on.  I was wondering how to make this easier and I decided that my usual approach of recording an installation might not be that helpful so I have made a PowerPoint deck instead and stuck it on my SkyDrive account.

    The deck covers four separate tasks to help you put together a complete Denali based  BI demo on one server:

    Part 1.  the install everything to the default instance, so the database engine plus all the BI stuff, specifically:

    • Analysis services in its new tabular mode
    • Reporting Services in SharePoint integrated mode

    Part 2. Install SharePoint and then install SQL Server Analysis Services in SharePoint integrated mode to its named instance of PowerPivot

    Part 3. Another installation of Analysis Services this time in multidimensional (OLAP) mode to another instance of SQL Server.

    Part 4. Setup reporting services in SharePoint integrated mode. This is done in a very different way to previous version of SQL Server; With Denali Reporting Services is an application service in SharePoint

    I have included screenshots for every step of the process and annotated these in the notes at the bottom of each slide and you should end up with this in management studio..


    plus having PowerPivot working in SharePoint as well as integrated reporting Services.

    If you are trying that out for yourself then you’ll need:

    Finally you might need some data , and reports to see all of this working and that’s on Codeplex.

  • SQL Server on Windows Server Core part 4 – Sysprep & SQL Server

    Installing SQL Server can be tedious and boring, and now that we live in a virtual world surely there’s a better way to give developers, application managers and even tech savvy business users theSQL love without the putting your self through installation hell each time?

    I imagine some of you have resorted to copying, renaming the server and then hacking around SQL Server to get it recognise the change of name, but there is a cleaner and supported way of doing this called image preparation. Essentially you do a pre install of the features of SQL Server you want, sysprep the machine and then use this as a template for as many installations as you need.  For each copy you then fire it up join it whatever domain and infrastructure you need and then run a script to complete the SQL Server installation. 

    If you aren’t familiar with sysprep, it makes an installation of windows anonymous, it is no longer has a name, or more importantly an SSID ,( the way each machine is uniquely identified to a domain). So sysprep allows virtual machines to be copied and then each copy can be joined to the same domain.  

    Creating a template like this makes even more sense if you use windows server core as it will take up less space, and the original template will need less patching, (as I covered in part 1 of this series).

    In the accompanying video demo,


    • I have installed SQL Server with the prepare image installation action:







    • sysprepped the virtual machine
    • restarted it and joined it to  a domain (part 2 of this series covers this in more detail)
    • finished the SQL Server installation with the complete image installation action:












    Image preparation does look like a lot of extra work the way I have shown it here,but in reality the process of configuring it post sysprep would be fully scripted, and handled by System Center, specifically Virtual Machine Manager, possibly in combination with Service Manager to handle the request for a new SQL Server and Orchestrator to handle the workflow. This final step is exactly what the private cloud is all about, and so I do want to make a final video to show this in action, but I want to wait until the System Center 2102 lineup is all released to beta in the autmn.



    • Sysprep doesn’t remove settings or patches, so the SQL Server firewall rule (to open TCP port 1433) I configured in part 2 is still there so I can connect to it remotely.
    • You’ll need an evaluation copy of Windows Server 2008 R2 (this has sp1 included) for this if you haven’t got a TechNet subscription.
    • You'll also need SQL Server Denali ctp 3
    • The video is part 4 of a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible. However there will be accompanying posts and notes to help as well .
  • SQL Server on Windows Server Core part 3–Installing SQL Server

    In my previous two posts in this series I have installed and configured Win Server Core, and now I am ready to install SQL Server.  There are a number of things to be aware of here:

    • Only SQL Server Denali is supported for server core.
    • SQL Server depends on the .Net framework
    • SQL Server needs the the .Net 4 framework.
    • You can only install SQL Server on Server Core using the command line.
    • Only certain parts of SQL Server will run on Windows Server Core:
      • the database engine
      • full text search
      • analysis services
      • replication
      • client tools connectivity
      • integration services server

    Turning that lot into a step by step guide works like this:

    • Install server core as per part 1 of this series
    • Install service pack 1 for Windows Server 2008 R2, if it’s not part already included in the media you used to install Server Core (known as a slipstream install).
    • Install the .Net Framework 4.  There’s a special download for Serve Core for this.
    • Join the server to you domain.
    • Install SQL Server from the command line:












    Note:  the last switch enables TCP for you for remote access to SQ Server

    added 22 August 2011: SQL Server Denali will add in the .Net features automatically if they aren't already there, although in the video I did all this up front in part 2

    • Open the firewall to allow you to connect to SQL server remotely..

    netsh [enter]

    advfirewall firewall [enter]

    add rule name=”SQL Server default port 1433” dir=in action=allow protocol=TCP localport=1433 [enter]

    Here’s my video of doing all of that


    • You’ll need an evaluation copy of Windows Server 2008 R2  (this has sp1 included) for this if you haven’t got a TechNet subscription.
    • Service Pack 1 for Windows server 2008 R2 is available here if you need it.
    • .Net Framework 4 for Server Core is available here 
    • You'll also need SQL Server Denali ctp 3
    • The video is part 3 of a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible. However there will be accompanying posts and notes to help as well .
  • SQL Server on Windows Server Core part 2–Configuring Server Core

    Windows Server Core is a lightweight installation of any edition of Windows Server, and because it is so lightweight initial setup can be a bit tricky, so here’s what you need to know:

    • Adding roles & features. There’s no Server Manager in Server Core and in any case you might want to do some configuration from the command line.  The modern (new for Windows 7 way to do this) is with DISM (Disk Image Service Management) which can also be used to do this to a virtual machine without turning it on.  This command adds in everything you need for SQL Server:

    DISM /online /enable-feature

    • Joining a domain.  NetDom used to be the way to do that but now there are PowerShell commands you can use.  Also there is a built in shell SConfig which enable you to do this:


    • General configuration.  There’s a Core Configurator application on Codeplex which is a simple GUI fronting lots of clever PowerShell and so doesn’t add to the weight of Server core but will turn on the .Net framework and enable PowerShell as these are needed to make this work.


    • Remote Management.  This can be done the hard way in netsh, or in either of the utilities above.  I couldn’t see a way of just opening a port with these tools, so if you want to install SQWL Server and access it remotely you’ll need to open a port for it (the default of 1433 in my case).

    netsh [enter]

    advfirewall firewall [enter]

    add rule name=”SQL Server default port 1433” dir=in action=allow protocol=TCP localport=1433 [enter]

    I have gone over some of this in this 5 minute video…


  • SQL Server on Windows Server Core - part 1 Server Core installation

    A while ago I got SQL Server (only the database engine) running on Server core and then found out that this wasn’t going to  be a supported scenario for SQL Server 2008 R2.  However with the launch of SQL Server Denali this will be supported scenario but now it’s a little more complicated to do.

    Before I go into that what is Windows Server Core and why does this evangelist consider it important?

    It is a lightweight version of windows with the bare minimum of components needed to run infrastructure services like domains, file and print, and IIS.  Originally only asp sites could be supported but with the launch of Windows Server 2008 R2 there was enough of the .Net 3.5 framework in there to support asp.Net websites and to run SQL Server.  In fact it is so lightweight there are only a few interfaces in there:

    • Registry Editor
    • Notepad
    • Task Manager
    • Command line
    • PowerShell (Note:you have to do some post setup installation to enable this)

    While this cripples the server if you are trying to work on it directly it gives a number of advantages:

    • Maintenance. Less than half of the patches needed for Windows Server will be applicable to a server core installation
    • Availability. Windows Server 2008 R2 is the most secure Microsoft operating system anyway but, less operating system means less to go wrong and less to attack, for example there’s no browser in here.
    • Footprint.  Server Core will happily run in 256Mb and takes less disk space than it’s full fat sibling so you can pack more virtual machines like this onto a physical server.   

    Server Core is also pretty fast to install just over 5 minutes from a clean virtual machine on my admittedly awesome Dell Precision M6500 and I hope this equally short video (under 4 mins) will help if you want to try it yourself:


    • You’ll need an evaluation copy of Windows Server 2008 R2 for this if you haven’t got a TechNet subscription.
    • The video is part 1 of  a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible.  However there will be accompanying posts and notes to help as well
  • Back to Skool

    Getting time, and resources to stay current with technology is always hard for IT professionals even when that training applies directly to your role.  If you are thinking of cross training because for whatever reason you can’t see a future in doing what you are currently doing then you’re probably going to end up investing your own time and money to reskill.  It can also be hard to work out what to learn about, and if it’s the Microsoft stack you’re interested in there’s just so much stuff it can be hard to know where to begin. 

    The Microsoft Virtual Academy is designed to help with this by providing introductory training in a variety of cloud related technologies, but is it any good?  I wasn’t totally sure so I decided to give the course in the private cloud track a spin and see if they are any good.

    The private cloud track has four separate courses:

    Each course is made up of a number of modules and  each module has various videos and technical articles in it plus an assessment test.  The videos are often on other Microsoft channels such as TechNet Edge, and ShowCase and you get points by following the links , and points for completing the assessment and all module in the course.

    Note: don’t watch all the videos by clicking in the TechNet Edge portal, go back to MVA and click on the links there to get credit for each module

    So I have registered (you’ll need a LiveID ) logged in and have got going..


    To be honest I cheated on the Success with the Private Cloud course and tested out as I am pretty comfortable with our cloud messaging and was able to get good scores without watching the videos again..

    mva vdi exam

    The other three courses are well worth watching in their entirety even though that’s going to tack a day to do. There’s lots of anecdotal stories from the field along with the high level stuff and the demos really get the concepts across and there’s not too much PowerPoint in there either.  When it came to the assessment questions the level jumped into considerable detail- for example on the Hyper-V deployment module in the Hyper-V for Vmware professionals – the platform course I confess that I did have to rely on what I just learned to get past some of the networking questions.

    Finally There are several other track in MVA , around Azure , Office 365 etc. and that  content is continually being added to the MVA platform and I will be looking forward to deeper exploration of the new cloud stuff coming in the next wave of System Center (2012) that is now in beta.

  • Query Across the Mersey

    SQLBits Logo

    SQL Bits 9, Adelphi Hotel Liverpool 29th September to 1 October 2011, what else do you need to know?

    Well possibly a few things if you are now reading the second line of this post or you haven’t been to them before..

    1. It’s the biggest and best SQL event in Europe, I know this because we get people coming from around europe to it , not to mention the Microsoft engineers who come over from Seattle to attend and present.
    2. It’s actually several different types of event all tied together
      • a training day of deep dives which you have to pay for
      • a paid for conference with subject matter experts including MVPs and Microsoft technical experts form the engineering team and SQL CAT, sharing their best practices and experiences.
      • a free community day where anyone can post an abstract for a session and then attendees vote on which ones get accepted and are part of the day.  It’s split into 4 tracks DBA, Developer, BI and Denali so the other challenge you can face is which session to go to.
    3. It’s a massive networking opportunity,  you might want some technical help you might be in the market for a career move or you need a good DBA yourself. 
    4. There’s some interesting sponsors there including all the major tools vendors (Attunity , Idera, Quest, SQL Sentry) so you can contrast and compare in one visit.  Experts like Coeo also attend plus the game changing hardware specialists FusionIO with their solid state storage solutions, that almost make it pointless to worry about any other kind of performance tuning.
    5. We’ll be there, when I say we, I mean some hardcore technical experts form Microsoft from our customer and support teams, who also have share a passion for all things SQL Server, plus of course this evangelist, who still likes to keep his hand in.

    I could mention the good food, prizes, games etc. but I have hopefully given you incentive enough to be there if you possibly can.

  • Running out of space

    One of the best parts of my job is helping people, and although I am not a Microsoft support guy, I will respond if I can to requests on my blog , twitter etc. a particularly if it’s from people new to a particular technology.   This is partly out of a recognition that we’re all experts in something and even when it comes to the IT department not everyone is deep into SQL Server, System Center or Windows.  SQL Server actually presents it’s own problems as  it often comes with a business application which hasn’t ben procured through IT, and somewhat naturally when the business asks for help IT will push back.  However application like this can be mission critical and in one case (I am not allowed to name names) this resulted in a user running into trouble with a 2TB (yes that is 2 Terrabytes) database filling up his desktop machine.

    It was clear from the tone of the e-mail that although this chap is highly technical and has some basic knowledge of IT he hadn’t really done anything with SQL Server before, and was feeling his way.  His database turned out to be so large because it was mostly Filestream, a new data type in SQL Server 2008 and later for storing large unstructured data files in such a way as to keep them inside SQL Server’s control while still essentially being separate files capable of being accessed individually through the win32api.  So if you looked the disk you would see the files for the database itself, the log file and then a folder with these Filestream files in in a set of SQL Server generated folders and file names i.e. they won’t have the same names as when they were added to the database.

    There wasn’t time or space on this machine to backup this database but fortunately the user was using a test database to test how to move it and reuse it elsewhere.  I recommended detach –> copy –> attach without realising that Filestream doesn’t like that if you just do it as is,  the attach left the database in an unusable state as it was still looking for the Filestream data in its original location.  Fortunately running detach form T-SQL:

    USE [master]
    USE [master]
    EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

    where MyDatabase is the name of  my database.  If you are new to SQL Server those file will be in something like

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

    and that’s part of the problem, a default install of SQL Server will drop these data files on the C Drive along with the SQL Server program and the operating system. Anyway you would then copy the database files to a new larger hard disk and then run this..

    USE [master]
    ( FILENAME = N'D:\DATA\MyDatabase.mdf' ),
    ( FILENAME = N'D:\\DATA\MyDatabase_log.ldf' ),
    ( NAME = N'MyDatabase_FSData', FILENAME = N'D:\DATA\FileStreamData' )

    where D:\data  is where you have copied the files to in my case D:\Data

    The next problem we hit that his new server came back with an error,

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.

    Msg 948, Level 20, State 1, Line 1

    The database 'MyDatabase' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.

    This is pretty simple – the new server was running an older version of SQL Server and while you can take on old database detach it copy and attach it to a newer server (which is a supported way of upgrading a database)  you can’t go backward to an older version.  In this case the original database is SQL Server 2008 R2 and the second server was running SQL Server 2008.  Given this is an end user scenario it’s not surprising that he had differing version around, and my final tip is to run this to find out what you’ve got on a server by server basis:

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')


    The version is 10 for SQL Server 2008 , 10.5… for SQL Server 2008 R2. 

    If you have lots of servers to perform SQL Server discovery on then you’ll be much better off using the free Microsoft Assessment & Planning Toolkit, which will also return operating system and server information plus a host of other information as you need.


    So imminent disaster averted, user fairly happy, but all I have done is put some gaffer tape over the problem in reality this data need to be migrated to proper server so that as the database grows it will perform and be available to the whole team and hopefully be properly protected by the IT guys.

  • Data Mining not dead, but possibly forgotten

    With the launch of SQL Server 2008 R2 and SQL Server Denali ctp3 there has been ever more BI functionality built in but nothing has been mentioned about the strange world of data mining. Data mining has been in SQL Server since analysis services was launched but I have rarely seen it being used - it doesn’t seem to have entered mainstream consciousness in the way that some of the cool reporting front end tools have possibly because it smacks a little of black magic, or is seen as unnecessary or hard. 

    So here’s a quick post to get you thinking and hopefully encourage you to take it for a spin.

    What exactly is Data Mining? 

    The simple answer is the ability to derive insights from your data that you didn’t know were there.  Classic examples of use are customers who bought this also bought that (what is called basket analysis in the retail world) and trying to understand what factors influence a purchase.

    How does it work?

    There are a number of approaches, possibly the most famous being neural networking.  In fact there are six different techniques built into analysis services each designed to do a particular job:

    • Association  is used for that basket analysis I mention and recommending additional items customers may want to purchase.
    • Clustering  groups such things as customers by detecting what is not obvious form casual observation.  You may have heard a story a few years ago of the banks segregating customers into cherries (good customers) through to lemons (bad customers) and this might been done better using clustering rather than a trivial exercise around what revenue they bring in.
    • Decision Trees try and predict the outcome of an attribute by understanding what factors determine the value of that attribute. The way the prediction works depends on what is being predicted and whether this is discrete (values such as yes or no, red green or blue and so on) or continuous (e.g. a number like value or price).  So this could be used to work out what factors make a up a decision to purchase e.g. age gender, income, homeowner and so on.
    • Linear Regression.  You may remember this one form matches at school, the process of trying to draw a line through a series of dots to best represent the trend of those dots on a graph. This is used by the decision trees algorithm where the predicted column is continuous.
    • Naive Bayes  is the method for determine which customers would most likely respond to a marketing campaign, the naive term means that no relationship is assumed between any of the factors affecting the outcome.
    • Logistic Regression. Every week you’ll hear something like “women drivers over 40  are 50% less likely to have a car accident” and this would be the algorithm that would be used to discover that relationship. It can also be used to discover which are the most important factors in determining an outcome.
    • Sequence Clustering is similar to clustering but the data contains a sequence of events and this enables you to explore such things as a users navigation of a web site (known as click stream analysis).
    • Time Series is used for forecasting a continuous value such as sales  or profit.
    • Neural Network is the non of the above option for really complicated input data.  The mechanism for determining the outcome mimics the way neurons work in the brain and doesn’t actually show you how its results are derived at.  Text mining is one example that uses this technique.

    In all of these techniques you identify a sample set of data which has the values of the object you are trying to predict, from which you create a model.  You then apply this model to live data to do your prediction and analysis. 

    How do I do this?

    There are three ways of creating models:

    1.  You can create mining models in BI development studio as part of an analysis services project in SQL Server 2008 R2 or in the beta of the new version Project Denali and there are examples mining models in the sample adventure works analysis service project from Codeplex:

    2.  You can code a model directly in Data Mining Expressions (DMX) which might be appropriate for embedding models into an application such as an ecommerce site.

    3. There’s an Excel add-in for data mining :


    This works by taking the data in a spreadsheet and then posting it to an instance of analysis services for processing and then returning the results back to Excel.  Originally designed for Excel 2007 it does work in Excel 2010 but only for 32bit installations, and while it can’t use data in the PowerPivot window in  PowerPivot for Excel it can consume data in Excel pivot tables derived from PowerPivot data. Note that when I mention analysis services here, I mean classic analysis services in olap mode not the new shiny tabular based analysis services that also exists in SQL Server Denali.

    Why Bother?

    First of all you are probably licensed for all of this now, you’ll have SQL Server standard or higher around somewhere you can use this as the mining engine and a copy of Excel for the front end (the excel add-in for data mining isn’t a separate license). However this might all seem a bit abstract and you might have a hard time convincing yourself never mind your users that this is worth the effort. One idea I have is  what if you took the data out of some of the data you have access to see how this stuff works, for example data from System Center, or whatever data centre management tools you have (dare I mention VSphere?) your helpdesk system or asset management tools to get insight on what is really going on.  This might be a useful project in it’s own right but as the data is not sensitive you can share it as an example of what’s possible and possibly impress your manager as well as the business owners.