Insufficient data from Andrew Fryer

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

Insufficient data from Andrew Fryer

  • Management Reporter - the new FRx


    One of the worst reporting headaches is producing output for the finance department.  The reports need to meet strict standards, be penny perfect, and the end user always wants the report to be in excel.  With reporting services in SQL Server 2005 this could be done by the IT department but the real solution is to let the finance types do it for themselves.  Performance Point went some way to addressing this and for those customers with Dynamics there was FRx.  So three ways of doing things - a typical Microsoft approach.

    Then very quietly, in fact so quietly that you would only know about it by searching for it by name, Management Reporter quietly launched down the Microsoft slipway on 29th January.  Management Reporter is a free add-on to Performance Point users with software assurance. It's specifically designed for finance users to do reporting in Excel.  It has a lot of financial intelligence built in and support for regulatory compliance e.g. Sarbannes Oxley (SOX), International Financial Reporting Standards (IFRS), and Generally Accepted Accounting Practices (GAAP) to name a few.

    Unlike FRx, your accounts don't have to be in Dynamics to use it and you can save the design as a reporting services report so information can be disseminated to other parts of the business who don't have Performance Point.

  • SQL Server 2008 - Transparent Data Encryption

    One of the new things you can do in CTP5 of SQL Server 2008 is to encrypt your databases so that they are protected at rest and so are any backups made from them.  So this prevents anybody from accessing a database without going through the server it belongs to.

    To move an encrypted database from one server to another you would need to move the key that encrypted it as well. For example you might send the key be e-mail and then send the database on CD's in the post.

    First you need a master key and then a certificate:

    USE master;
    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate for Sensitive Data' GO

    Then you can use this to encrypt the database with this:

    USE Retail_DWH

    The encryption process runs as a background task and the database is available during the process.

    It's a really good idea to back up the certificate as without it you can't get the database or backups back - whihc of course is the whole objective! 

    This script backs up the certificate to a temp folder as an example:

    TO FILE = 'c:\temp\MyServerCert'
    WITH PRIVATE KEY (file='c:\temp\MyServerCertKey',
    ENCRYPTION BY PASSWORD='UseStrongPassword1!')

    To move this to another instance or server the first step is to create a master key on the new server:

    create master key encryption by password = 'UseDifferentStrongPassword1!'

    The certificate can then be restored like this:

    create certificate MyServerCert
    from file='c:\temp\MyServerCert'
    with private key (
    file = 'c:\temp\MyServerCertKey',
    decryption by password='UseStrongPassword1!')

    So very simple to setup, easy to use

    Technorati Tags: ,
  • Report Builder 2.0 released

    Typical, you spend a day off line at a team building exercise and then you find that the SQL Server team have released Report Builder 2.0 to a fanfare of silence. 

    To rewind a bit Report Builder 2.0 sort of replaces Report Builder 1.0 that came with SQL Server 2005.  The two tools are as different as black and white TV to a bluray DVD, partly due to the Office like UI…


    and partly due to all the good stuff you now have in Reporting Services 2008 like charts and the new tablix data control (replacing tables and matrix).

    There are two puzzling things about Report Builder 2.0, one is that Report Builder 1 still ships with SQL Server 2008, as a click once application from Report Manager and the other is that this isn’t (as yet) a click once application.

    Anyway if you have been using this at RC0 or RC1 stage then please replace those betas with this as there’s more to it than the fact that the start logo top left is no longer the office logo.

    If you haven’t tried this before then you can deploy it to your users for them to write their reports or at least collaborate in the report design process.  Needless to say it will only allow you to deploy reports to a SQL Server 2008 reporting services installation (although the data can from any database you can get a .Net provider for).

  • SQL Server Migration

    I knew that there was a toolkit to help move an application from Oracle to SQL Server, but I hadn’t appreciated that there was one for Sybase and Access these also now come in two flavours, one to move you to SQL Server 2005, and one for SQL Server 2008:

    SQL Server 2005 Migration Assistant for:

    SQL Server 2008 Migration Assistant for:

    Before you ask, there aren’t any assistants fromMicrosoft to do migrations to these platforms from SQL Server,  although I am sure that Oracle and Sybase will be happy to help with that.  What I can’t understand is why anyone would go this way, so post some comments and enlighten me.  

  • SQL Server 2008 Sparse Columns

    Sparse Columns are another new feature of SQL Server 2008 and are included in the February CTP (CTP6). They pretty much do what they say on the tin; offering a trade-off between taking more space to hold data, but none at all when they are empty.  They don't get you over the 1024 column limit, but could mean you can squeeze more columns into the 8,060 byte row limit for SQL Server.

    Like everything in SQL Server you need to know when they add value and when to avoid them like the plague. Fortunately one of the non-sparse areas of Books On-Line is the section covering sparse columns here

    So the good news first:

    • Storing a null in a sparse column takes up no space at all.
    • To any external application the column will behave the same
    • Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
    • You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set.  The column set behaves like a column itself. Note: you can only have one column set per table.
    • Change Data Capture and Transactional replication both work, but not the column sets feature.

    And the downsides.

    • If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
    • Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse. (Changed17/5/2009 thanks Alex for spotting the typo)
    • computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
    • You can't apply rules or have default values.
    • Sparse columns cannot form part of a clustered index.  If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
    • Merge replication doesn't work.
    • Data compression doesn't work.
    • Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.

    As you can see from Books On-Line there is a really useful guide to when to use them for a particular data type e.g. if more than 64% of your values are null in an int column then use sparse columns, and basically the longer the data type the lower the threshold for using sparse columns.

    So how does it work? Just put the keyword SPARSE into a create table statement:

    CREATE TABLE CustomerDemographics

    (CusomterID int PRIMARY KEY,

    Gender varchar(7) NOT NULL,

    EducationLevel varchar(20) SPARSE NULL,

    SalaryBand varchar(10) SPARSE NULL)

    Selects against this table will work exactly as for normal columns whether the sparse column is included as a column in the select column or a filter in a where clause.

    Optionally to create a column set for this table append this to the end of the create table statement:


    The column set DemographicSet is then treated like any xml column i.e. it can be selected and also be used for updates and inserts, Note if you do use a column set for updating data sparse columns not specifically declared in the update well be set to null.

    Finally if you are wondering why this feature was developed, the simple answer is to support future versions SharePoint which was also one of the drivers behind FileStream.  I can see it being applied to any content management system over SQL Server and also as I have mentioned before for reducing the overhead of storing customer demographics or product catalogs where not every column applies to every product or customer.

  • SQL Server 2008 Reporting Services no longer depends on IIS

    It sometimes seems impossible to keep everyone happy all the time.  The Reporting Services predict team thought it would be a good idea to remove the dependency on IIS in SQL Server 2008, to make it easier to configure and to reduce the attack surface of the report server.

    However, there have been some concerns raised about this, e.g. is it secure, how do I know how it's configured etc.

    So what's not to like?

  • Missing Snipping Tool in Windows 7

    I live my little snipping tool to capture bits of screen to share tips and stuff on this blog and in my decks, but since I moved to in Windows 7 it’s gone aaarrghh! Annoyingly all my mates (both of them) have got it on their machines so where did it go?

    Apparently it’s part of the tablet specific optional features and I didn’t have this selected. So to fix it (and now I have the tool installed I can show you!) go to programs and features (you can just type that in on the search bar to go straight to it) and from there select turn windows features on or off ..


    to get this dialog


    ..and check the option for Tablet PC Components.

    For my blog I also use a lot of the picture editing tools in Windows Live Writer especially crop  and border style you can get the latest version of this free blogging tool here.

  • Performance Point 2007 and ProClarity Licensing

    Digit AlAs many of you know Whitehaven has gone digital today amongst quite a lot of confusion.  I must admit I am struggling to to see the point of staying analog and quite what the fuss is about given that a freeview box costs as little as £20.

    The story around Performance Point and ProClarity still seems to be causing similar confusion.


    As I have mentioned before Microsoft Performance Point 2007 is a combination of Three Things

    • Monitoring
    • Analysis
    • Planning

    ProClarity is the analysis part of Performance Point.  The confusion is around migration and upgrades. Essentially if you have the existing ProClarity Analytics Server and maintenance or software assurance you get an equivalent license of Performance Point. 

    If you aren't ready for Performance Point you can still continue to buy more ProClarity licenses. There is no immediate pressure to upgrade as the ProClarity will be supported for another ten years.

    So a bit different from the digital switchover, and the monitoring and planning stuff you get with Performance Point are a lot more useful than the shopping channels on freeview.

    Full details and FAQ's are on the performance point site. Failing that contact me and I will try my best to answer your questions.

  • SQL Server 2008 and end user ad hoc reporting

    In my opinion the weak link in reporting services is that there is no tool for information workers to design their own reports and so IT professioanls have to spend a lot of time doing it for them.  I can see there is a place for these reports:

    • They will be accurate or at least give the user what they asked for but not necessarily what they want. 
    • They will leverage all of the cool features in the tool such as document map, linked reports and actions to other tools.  But there is often a long gestation period for reports written in that way as the users insist on the correct formatting and layout and usually 'refine' the spec in the process.

    Reporting Services in its 2005 release does have a lightweight tool for end user reporting, called Report Builder and I have implemented this for a very simple application.  The great thing about it is that relies on a Report Model, which is a bit like a unified dimensional model in analysis services in that it insulates the end user from the underlying relationships in the source database and has derived calculations in. A Report Model can either be built from the ground up against a relational source in the BI Development Studio (BIDS) or be generated from a cube in Report Manager by creating a data source to the cube and then selecting  generate model.  Once a Report Model is deployed to Report Manager the user can then immediately create their own reports.

    However this tool is far too simplistic and there are no plans to enhance it in SQL server 2008.  Instead there is a new tool called Report Designer Preview (Name not determined yet).  It's included in CTP 4, which gives an idea of how it will turn out ,but at this stage it is severely handicapped:

    • It only connects to a SQL Server database. 
    • It has got no help and some of the menu options are not populated.

    Hence the Preview, it is not supposed to be fully formed and this has been done to illicit feedback from those of you adventurous enough to try it.  For me what is really important about it are three killer features two of which you can see in the CTP and one you can't! 

    Firstly It's a standalone install (not a smart client like report builder) and business users don't have to battle with Visual Studio. 

    Then there is tablix. Although it looks from the tool that you still only get table and matrix reports in fact there is only one tool the cunningly named tablix (table + matrix) this gives the benefits of both as you can see from this:

    So data can be show in one grid from two dimensions across the dimension they have in common and that is only one of the problems that can be cracked using this tool.  Note that you have to use the report designer preview as the designer inside BIDS hasn't changed to be tablix aware. 

    The bit I can't show you is how this will help users and that's because in CTP4 the tool can only source data from SQL server and that means writing a query which isn't good for the business user. I understand that the plan is allow the tool to use report models, as well as source data from cubes and the usual raft of data providers that reporting services currently supports. 

    So serious end user reporting will be available in SQL server 2008 reporting services.  Which means that while the users design their own reports we'll have a little bit more time to keep up with all the other new stuff in this release.

  • SQL Server 2012 – Always On

    There have always been several ways to do high availability in SQL Server, but choosing the right one has always been difficult as each approach has obvious benefits coupled with unavoidable limitations:

    Clustering looks after a whole instance of SQL Server containing many databases and is completely transparent to an application. However shared storage adds cost and complexity and there is only the one copy of the database(s) on that shared storage.

    Mirroring creates a continuously updating replica of a given database, failover is really fast and it’s easy for a DBA to setup. However mirroring has several significant limitations:

    • A special connection (SQL Server native client )is needed to mirroring so not all applications can work with it
    • Protecting multiple databases so that if one fails they all fail over is not really possible.
    • There is only one mirror of the database
    • The mirror is not directly usable it just sits there unless you are prepared to work with snapshots.

    Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one.  This is more difficult to setup and failover is not automatic you have to reset all of this yourself.

    To build a better SQL Server mousetrap, you would want a solution that:

    • Looks like a cluster to any application i.e. there is a DNS entry to the cluster to which the application connects without ‘knowing’ which node SQL Server is running on
    • You would want to treat a group of databases as an object so that they can be failed over etc. as needed in one go. 
    • As with log shipping, there wouldn’t just be one other node behind the primary there would be multiple mirrors/secondaries
    • The mirror could be read only and therefore available for reporting
    • You could opt to have some nodes connecting asynchronously and thus have a remote replica of your databases without needlessly slowing down the primary.

    Up until know that meant that we would have use more than one feature in concert e.g. mirroring and clustering together to achieve the high availability we wanted. What SQL Server 2012  AlwaysOn  does is to provide this combination in one single feature:

    It uses the Windows Failover Cluster feature in Windows Server but doesn’t use any shared storage. A normal install of SQL Server 2012 is then done on each node and the SQL Server 2012 service is then configured to use the cluster..


    Having done that you then tell the SQL Server service on each node to use the cluster the new AlwaysOn High Availability tab in the properties for the service..


    However AlwaysOn is actually doing something very similar to mirroring under the covers, in that there are replicated copies of the databases being protected not just one copy on shared storage as there is for clustering – and AlwaysOn doesn’t need to use shared storage. You’ll also notice that for databases to be protected by AlwaysOn they need to be in full recovery mode and backed up (preferably to a share that’s visible from the other nodes). However with AlwaysOn you can have multiple secondaries and you create availability groups, which are sets of the databases you want to keep together.

    There’s a wizard in SQL Server Management Studio for this where you can specify the nodes, the databases and the options for accessing each node. Note this uses TCPIP ports like mirroring does (so port 5022 by default) and these need to be opened in the firewall for this all to work.

    There’s a dashboard to confirm all is well ..


    There is also an option to create a TCPIP listener which provides an address and DNS entry for the cluster.  If you set this up you can  connect directly to the cluster from any tool that can connect to SQL Server, in this case I have connected to the TechNet cluster from management studio in the same way I would connect to any other instance or cluster..


    However you can also connect directly to the primary or secondary as well and for a read only secondary that’s how you would do reporting.

    I have a short (8 min) AlwaysOn screen cast if you want to know more or have a guide to help you try it yourself.


    Finally be aware that this is not replacing clustering, mirroring or log shipping but it is only going to be available in SQL Server 2012 Enterprise edition.

  • Virtualized SQL Server


    [Note the information in this post is now way out of date, so please refer to my post here instead..

    Thanks Andrew]

    I get more and more questions about SQL Server running in a Virtual machine so here’s 3 FAQs to start with…

    1. Is there Support for SQL Server on non Microsoft Virtualization platforms (e.g.VMWare)? The definitive document on the is here.  In summary Microsoft doesn’t test this or support it, unless you have premier support in which case “Microsoft will use commercially reasonable efforts to investigate potential issues with Microsoft software running together with non-Microsoft hardware virtualization software”.

    Of course the supported versions SQL Server (i.e.not 2000) and other Microsoft applications (Exchange, SharePoint etc.) will be fully supported on Hyper-V when Hyper-V is released. 

    2. How does licensing work?  The exact terms are in this white paper, and in summary

    • For processor licensing each physical or active virtual machine running SQL Server must have a processor license (the number of chips not the number of cores) for each processor the virtual machine uses.  However if you have licensed enterprise edition for all of the processors on a physical machine you can run any number of SQL Server virtual machines on that box.
    • For CAL licensing each physical or active virtual machine requires the server license.  The exception to this is enterprise edition which just needs to be licensed per physical machine

    Note that the licensing applies to SQL Server 2008 as well and to running SQL server on Microsoft’s Hyper-V platform in Windows Server 2008.

    3. Performance. This is very easy to answer but not very helpful… Microsoft can’t test and publish how well SQL server performs on VMWare for example so I simply don’t know.  On Hyper-V this can be done as soon as Hyper-V is released.  There is always going to be some loss of performance and this will be the price you pay for improved manageability.

    Finally don’t be put off by Virtual PC or VMWare demo’s you may have seen on laptops, you should get a better virtual experience using any virtualisation platform in a production environment, but the performance loss is going to vary depending on the physical infrastructure (SAN, number of Cores RAM etc.) and the workload you are virtualizing.

    Since writing this post Microsoft has introduced the Windows Server Virtualization Validation Program (SVVP).  Essentially this means that the currently supported versions of SQL Server are now fully supported on Hyper-V, VMWare and other leading virtualisation platforms as part of this program.
  • SQL Server 2008 Mirroring in Standard Edition

    I opened the data management track at TechEd EMEA yesterday, to a full room and as ever if I am unsure of a question I research it and put it on this post the next day.

    I was pretty sure of the differences in the way database mirroring in standard and enterprise edition of SQL Server 2008 which are:

    Standard Edition you can only use mirroring in full safety mode i.e. synchronous mirroring optionally with a witness to enable automatic failover. btw the witness can be express, but the mirror must be standard as well. 

    Enterprise Edition.

    • Mirroring can be run in high performance (asynchronous mode) which can be useful in a geo-mirror configuration over a slow network, where synchronous mirroring would create unacceptable delays for the user.
    • Torn page detection allows the principal to discover and automatically copy a damaged page from the mirror to the principal.
    • log compression dramatically improves the performance of mirroring in several ways, the send queue is reduced the transaction per/sec transmitted to the mirror are up by 2.5x on a 10mbs network (lees on faster ones). There is some CPU overhead for this

    There are no restrictions on how many mirrored databases can be setup on the mirror in standard or enterprise (which could come from different principal servers), however there are some things to remember:

    • An instance can only have one endpoint which could be a bottleneck if there are lots of databases in the one instance.
    • It is not recommended to have more than ten databases mirrored to one server, I have not seen that change for SQL Server 2008 but the safe limit should be higher for Enterprise edition given the compression feature.

    If you are TechEd I have now got the shuttle working like this


    … so that the database in the cluster is mirrored to another server which in turn is log shipped to another instance, so come and say hello on the SQL Server Ask the Experts stand.

  • SQL Server 2008 - Hierarchy ID Data Type

    To show this we need to create a table with the new data type..

    create table [dbo].Organisation (
      DepartmentID int primary key nonclustered,
      DepartmentName varchar(100) not null,   
      DepartmentHierarchyNode hierarchyid not null,
      DepartmentHierarchyLevel as DepartmentHierarchyNode.GetLevel() persisted
      -- plus any other columns you need

    Note the level is a derived function of getLevel() which returns how deep a node is in the structure in this case it’s the only node and so will return 0.

    Now we need to put the some data in starting at the top of the tree i.e the whole company – which in this example is Contoso..

    insert Organisation(DepartmentID, DepartmentHierarchyNode, DepartmentName)
    values (1, hierarchyid::GetRoot(), 'Contoso')

    note the getroot() function which does what is says and gets the root node of the hierarchy and since this is the first node in the hierarchy this is simply itself.

    Adding in new rows to this table is best done with a stored procedure like this one which needs to know the id for the new department the name of it and the id of the parent department it belongs to ..


    procedure AddDepartment

    @DepartmentID int,

    @DepartmentName varchar(100),

    @ParentID int



    declare @ParentNode hierarchyid,

    @MaxChildNode hierarchyid

    begin transaction

    -- get the node for the parent of the new

    -- department

    select @ParentNode = DepartmentHierarchyNode

    from Organisation

    where DepartmentID = @ParentID

    -- get the last department for the parent

    select @MaxChildNode = max(DepartmentHierarchyNode)

    from Organisation

    where @ParentNode = DepartmentHierarchyNode.GetAncestor(1)

    -- add the new department

    -- Parent.GetDescendant(MaxChild, null) returns the

    -- next descendent

    insert Organisation (DepartmentID, DepartmentHierarchyNode, DepartmentName)

    values (@DepartmentID, @ParentNode.GetDescendant(@MaxChildNode, null), @DepartmentName)




    Here you can se more obviously named functions of a HeirarchyID like GetAncestor, GetDescendant and MaxChild.  I have to admit I have no problem with these as they are quite similar to what you use in MDX in Analysis Services.

    Here’s some data to go into the new table..

    exec AddDepartment 2, 'Operations', 1
    exec AddDepartment 3, 'Development', 1
    exec AddDepartment 4, 'Parking', 1
    exec AddDepartment 5, 'Home Operations', 2
    exec AddDepartment 6, 'Field Operations', 2
    exec AddDepartment 7, 'North Territory', 6
    exec AddDepartment 8, 'South Territory', 6
    exec AddDepartment 9,   'Database', 3
    exec AddDepartment 10,  'Services', 3
    exec AddDepartment 11,  'Applications', 3
    exec AddDepartment 12, 'Windows', 11
    exec AddDepartment 13, 'Internet', 11
    exec AddDepartment 14, 'Self', 4
    exec AddDepartment 15, 'Valet', 4

    selecting from this will give you the levels and so on:


    This procedure will show a tree of the structure using a single select statement

    create procedure ShowDepartmentChart
    @DepartmentName varchar(50)

      declare @TopNode hierarchyid,
              @TopLevel int

      select @TopNode = DepartmentHierarchyNode,
             @TopLevel = DepartmentHierarchyLevel
        from Organisation
       where DepartmentName = @DepartmentName

      -- note that parent is descendent of itself
      select DepartmentHierarchyNode.ToString() NodeText, space((DepartmentHierarchyLevel - @TopLevel) * 5) + DepartmentName Department
        from Organisation
       where DepartmentHierarchyNode.IsDescendantOf(@TopNode) = 1
       order by DepartmentHierarchyNode




    and then use this like this..

    exec ShowDepartmentChart 'Contoso'


    The common toSting() function returns the human readable form of the hierarchyID node.

    You can (and should!) index Hierarchy ID’s in two ways..

    1. Create a "breadth first" index,  to speed up selecting of nodes at the same level, all the sub departments that belong to a parent department

    create clustered index Organisation_Breadth_First
    on [dbo].Organisation( DepartmentHierarchyLevel, DepartmentHierarchyNode )

    2. Create a "depth first" index, to speed up selecting a sub-tree
    of nodes, e.g. all the departments beneath a particular parent department

    create unique index Orgisation_Depth_First
    on [dbo].Organisation(DepartmentHierarchyNode )

    Hierarchy ID’s are faster than self joins, not least because no recursive SQL is needed and in my opinion more intuitive.  However one important thing to remember is that don’t enforce any kind of integrity so like with self joins you can end up with orphans. One area like this where you can stuff things up is if you use the reparent function. this moves a node in the structure to a differnet parent ( for example if a mmeber of staff has a new manager), but it doesn't move the nodes underneath the moved node as well, you must do this yourself. 

  • Windows Server 2008 Foundation - a new home for SQL Server

    A new edition of Windows Server 2008 got launched yesterday Windows Server 2008 Foundation which will only be shipped on a range of appropriate servers.  It supports 15 users, 8gb RAM and 1 processor  (but no core limit). 

    I can see that many small business might want to use this as their central server where they may have some of their data in the cloud (the obvious example being e-mail) but want some information such as the company accounts on premise. I can also see Foundation as being a logical home for SQL Express or Workgroup edition, probably bought along with the aforementioned accounts or a small ERP system.

    Companies wanting to do a little bit more on premise would probably be better off with Small Business Server (SBS) which has exchange and SQL Server built in as well as a basic set of tools for monitoring the health and security of the server and the PC attached to it.

    As a final thought even though April 1st was yesterday, I wonder if this would be a good platform to host LAN parties!


  • SQL Server Upgrade Advisor

    One of the things that made me smile at my launch presentation on upgrading SQL Server was that when I asked if everyone had heard of the upgrade advisor everyone in the room put their hands up. This is because most of the support issues raised by customers about upgrade use this template…

    DBA: My upgrade didn’t work properly [insert detail here]

    Support: What did the Upgrade Advisor report when you ran it?

    DBA: What the [insert favourite swear word here] is Upgrade Advisor?

    This isn’t necessarily the fault of the DBA, it is another illustration of the fact that there is usually a tool or some help on, but that it can be hard to find.  In the case of Upgrade Advisor, the tool isn’t included in the download or media for SQL Server so you have to download it, also it didn’t come out until SQL Server 2005 sp1. 

    So  for those of you considering upgrading SQL Server in any combination of  2000, 2005 and 2008, your first port of call should be Upgrade Advisor.  It is essentially a reporting tool that takes the following as input:

    • a database
    • a text file containing SQL
    • a SQL trace

    The last two are just as important as not all of the SQL that hits the database is in the database e.g. applications, web services, stored procs called from isql batch files and you need to either find where the code is or ruun profiler to get traces of what is actually running against your database.


    The output is a report that tells you how severe the problem is and when it should be fixed.  It can be run from any client with .NET 2.0 framework installed and does not affect the targeted databases when it is run.  It can take a while to run as it has to check each object in the database so the more there are the longer you have to wait.  I mention this because one of the first thing you want to do when you decide to bite the bullet and upgrade is to get rid of all the redundant code and object in your database.

    Technorati Tags: ,
  • Performance Point Server Planning Dependency on SQL Server Enterprise Edition

    Sorry about the catchy title, but I wanted to clarify why the planning component of Performance Point Server in turn depends on SQL Server Enterprise edition.

    I pointed out in my recent TechNet presentations that Performance Point doesn't make use of the cube write-back feature of analysis services, which is one of the extra features in enterprise edition. Well I have done some digging around and it does and doesn't use cube write-back.

    One of the main problems with cube write-back in SQL Server is that although the write -back itself is fast query performance degrades and more and more writes are done to the cube.  So Performance Point only uses write back to do what if analysis i.e. when changes are submitted to a model they are then written back to the database which then refreshes the cube.

    To get this new plan data into the cube Performance Point uses another Enterprise only feature of analysis services, pro-active caching.  This is where the cube is automatically updated when the underlying data is changed.

    So hopefully that clears up the confusion between what my good friend Ben Tamblyn and I have been saying about write-back.

  • SQL Server 2008- Backup Compression

    SQL Server 2008 will have the ability to compress a database, but that functionality is not in the latest beta release (CTP5).  However CTP5 does have the ability to compress backups by simply extending the existing backup command:

    backup database adventureworks to disk='C:\abc\adventureworks_compress.bak'
        with compression

    What's interesting about this is that it doesn't just compress the backup to less than a quarter of the uncompressed size (for this database), it does it fater -  the compressed backup took 8.9 seconds compared to 20.1 seconds for an uncompressed backup on my virtual machine. 

    It get's better as the restore process is also faster - restoring the compressed backup in 12.3s compared to restoring the uncompressed backup in 19.7s.

    Note the restore command is the same as in SQL server 2005 i.e. you don't need to tell restore the backup is compressed.

    So why wouldn't you compress your backups?

    I have absolutely no idea!

  • A small problem with Managed Service Accounts

    I am very fortunate to always have the latest versions of Microsoft stuff to play with such as SQL Server 2008 R2, but occasionally something doesn’t work, and so I need to tell the relevant product team what the problem is.

    In this case I was trying to get the SQL Server 2008 R2 November CTP to run with the new Managed Service Accounts in Windows Server 2008 R2 Active Directory.  They are a bit like the NTAUTHORITY/LocalService account in that they don’t have passwords that you need to specify to the service, however they are a totally different beast in that they are domain accounts  designed to be used for services, like IIS 7.5. Essentially the passwords are managed internally by active directory and are designed for use with Kerberos. For more on this have a look at this article on TechNet 

    However they are so new there is no way you can create one without using PowerShell (with the active directory module loaded)..

    New-ADServiceAccount SQLService

    Then you tell AD where you want to use the account on the server SQL08R2Demo..

    Add-ADComputerServiceAccount -Identity SQL08R2DEMO -ServiceAccount SQLService

    finally you deploy it to the computer that will use it

    Install-ADServiceAccount -Identity SQLService

    Now you can see this account in Active Directory under the new managed service account folder..

    ad service account 2

    I can now use an account like this to run IIS by going to services and running IIS with the new account (note you don’t supply a password for this account).

    However when Iu try and use it to run a SQL Server service, it doesn’t show up in the SQL Server configuration tool as it doesn’t see this kind of account at all even though you can search the managed service accounts folder.  Of course if I go into services proper I can change SQL Server services to use it…

    ad service account 9

    but that is not supported, as it will foul up the permissions needed to do things like backup. BTW I didn’t supply the dollar sign suffix

    so I have found something wrong and now I need to tell someone to fix it and the place where you do that for Microsoft stuff , irrespective of whether or not you work for them is on Connect (

    You register suggestions and bugs by first searching to see if it’s there already and then you can create your own..


    Here’s that bug, and notice that no one’s voted on it yet.  Votes are primarily there to influence feature requests, but bear in mind that what you or I consider to be a bug, maybe there by design and so could in fact really be a feature request. Anyway the more votes there are coupled with the severity of the problem the more likely it will be fixed

    So if you think something needs to be changed then use the site as the product teams are pretty good but are not known for their telepathic abilities.

    In the meantime I hope that this gets fixed for the release of SQL Server 2008 R2.


    Update 28 November 2011

    Managed Service Accounts didn't get supported in SQL Server 2008 R2, but are a feature in SQL Server 2012 as per this TechNet article.  The method is exactly the same



  • SQL Nugget - Dates are illogical

    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 


    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’)

  • 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.

  • MCSE for SQL Server

    I have to be honest I gave up getting certified before I joined Microsoft mainly because the exams didn’t keep up with the release of SQL Server specifically the Business intelligence exams. I then had the same problem as an evangelist I was working on the next release of the SQL or Windows Server.

    However that’s changed there are already exams out for SQL Server 2012 and also MCSE is back not just for Server and Desktop but also for the wider private cloud and for SQL Server.  These exams are hard and you’ll need to take five of them to get the MCSE badge. Also there are tow MCSE qualifications relevant to SQL Server; data platform and  business intelligence which I also wish had existed back in the day.

    Unlike some simpler certifications, Microsoft don’t insist you take a course and then get a simple test out at the end of it – how you get up to speed is up to you and we all learn differently so there is no right answer here.  This should see an end to the guy who has the certification but can’t really use the relevant technology in the real world.  Please come back to me on that only if you have taken one of these new exams yourself.

    So if they are hard why bother? You wouldn’t let anyone service the brakes on your car, or fix the electrics in your home and so why shouldn’t we as profession be asked to be competent at what we do as well.

    Some employers don’t like to pay for training as they might not see the value or be worried that you will leave as soon as you’re qualified, but training and exams are free in my role, and that’s a huge incentive to stay.  If you are a contractor retraining should get you better day rates and given that SQL Server 2012 is just you would have a definite edge. 

    Indeed there are loads of DBAs out there who may still not even know what is in SQL Server 2012, so I have been asked by Global Knowledge to do a couple of overview webinars on what is new .  So if you have missed me at SQL BITS, SQL Relay then please register on 12th September from 13:00 – 14:00  for an hour’s overview of the database enhancements and/or  register for the session on  10th October 13:00-14:00 if your focus is business intelligence.

  • Microsoft Business Intelligence without SharePoint

    This might be a strange topic given that the release of SharePoint 2010 is just around the corner, but I wanted to respond to a couple of questions and debates I have been involved with in my temporary role in the partner team.

    I would like to start by going back to the early days of Microsoft BI, analysis services (and OLAP services before it) were just getting established but there was no analysis services client from Microsoft to give users full access to the cubes in analysis services.  There were two third party contenders out there Panorama NovaView and ProClarity.  These were both web clients and as they evolved they both developed portals form which reports and content could be accessed and changed.  They also had security baked into them on top of what was in analysis services itself.  ProClarity was acquired by Microsoft and the descendants visualisations in that tool (such as the decomposition tree) are now in SharePoint 2010 enterprise.

    Panorama continues to be a Microsoft partner and is a good choice for business who just want a web client to get at their analysis services cubes, or even to get more functionality out of PowerPivot.  They also continue to have their own portal or can integrate with SharePoint as desired.  There also another 40+ products form partners out there which also provide web access to cubes, and here is a good a list as I’ve found of them

    The other key Microsoft BI offering is reporting services which either has its own portal complete with security (Report Manager) or can be integrated into SharePoint.

    So you don’t have to use SharePoint to provide Microsoft based BI to your users, but I would submit you are going to need some sort of portal, even if this is just a set of web pages where users can see content they are allowed to see and add more content to it (again subject to security).

    However if you want to provide access to reporting and analytics to your users then SharePoint or a similar dedicated portal would be a better option as you can then provide a single point of entry and a single security mechanism to control access to BI.

    On the question of cost you could just use SharePoint Foundation (the successor to Windows SharePoint Services), and SQL Server Standard edition, this would give you reporting services integration, and the other key parts of the Microsoft BI stack (analysis services and integration services). However you won’t get the performance point monitoring and analytics (which is in SharePoint enterprise) so the money you save by doing this must be off set against the need to buy a third party tool (like those I have listed above) to replace this functionality.  You might not actually save any money and the solution could be more complex, but it might be exactly what your users need.  This is a good thing, both for business and for Microsoft:

    • Its good for business as you have a choice in which BI client tools you want to use and you can choose how locked into SharePoint you want to be.
    • It’s good for Microsoft as this ecosystem helps put Microsoft at the forefront of BI vendors and offers a wider set of capabilities than Microsoft alone can provide.

    What I would suggest is to at least have a look at SharePoint 2010 as it’s scarily easy to set up and use even for this old DBA.

  • 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

  • SQL Server 2008 Resource Governor

    The feature of SQL Server 2008 that seems to get  the most attention from DBA's is the Resource Governor.  It basically does what it says on the tin; for example you may want to reserve a portion of CPU or other resource for a user, process etc.

    At the top level Resource Governor has Resource Pools, and there is always  default resource pool

    Below this you create Workload groups:


    These workload groups will be belong to the default resource pool, and for this introduction I will keep it simple, by leaving it like that. It is then a matter of assigning whatever you want to the those groups by using a function like this:

        DECLARE @grp_name AS SYSNAME
          IF (SUSER_NAME() = 'sa')
              SET @grp_name = 'groupAdmin'
              OR (APP_NAME() LIKE '%QUERY ANALYZER%')
              SET @grp_name = 'groupAdhoc'
              SET @grp_name = 'groupReports'
        RETURN @grp_name

    Notice that you can use any rule you like to create an association with a workload group e.g. users or the application.

    This function is then needs to be applied to the resource governor like this:


    You are now ready to configure which workload group gets which resources with this syntax:




    Finally the changes need to be applied to the resource governor process running in memory


    Changes can be applied to the Resource Governor at will and take effect immediately.  The function can be changed to move objects in to different workload groups as required and all of this will affect processes already running.

    To try this you will need CTP of SQL server 2008 which is available here.

  • SQL Server 2008 Notification Services

    Notification services is one of the deprecated features in SQL Server 2008, essentially it is supported for now but will not be in the next release of SQL Server. If you are using notification services currently and you are looking to upgrade to SQL Server 2008 then you can get it from here.

    But why deprecate the feature? Basically not many people were using and it wasn’t seen as being easy to use or flexible enough.  But what are your migration options if you are using it?

    That’s going to depend on what you are using it for:

    • Alerting of system health. A good alternative here would be use the policy management features in SQL Server 2008 and either schedule them (which is built into the UI) or set them to OnDemand: notify if the policy supports that (as per this post of mine.
    • Audit.  You can trap audit details to file, or the event logs (including the security log)in SQL Server 2008.  From there you could run a report on a schedule to show you any issues or use the capabilities of the event logs.  Change Tracking can do similar things for reporting on changes to actual data
    • Performance.  Extended events and the Data collection elements of SQL Server 2008 will allow you to trap detailed telemetry of what's happening to your database and the wider context of what’s happening server.

    Having collected the information you want to track, the challenge then is to get this information back to you when things go wrong.  This could simply be a case of making use of an agent job to do a test to see if there’s a problem, and then to conditionally running a reporting services report of the problem(s), or a send mail procedure to do it that way.  DDL triggers might also be an option, so there are lots of options but no obvious single thing to take your existing setup and migrate it to any of the above I’m afraid.

    So I would be interested in the comments this generates, and as ever if you have some ideas or an issue with this then Microsoft Connect is the forum for that.