Insufficient data from Andrew Fryer

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

March, 2008

  • SQL Server 2008 Configuration Servers

    Following on from my last post, when DBA’s have to manage multiple SQL Server 2000 instances you would normally see all of them registered on their local desktop in enterprise manager under a variety of groups such as dev, prod, test etc.

    With the advent of SQL Server 2005 this practice seems to have died off, perhaps because no one knows that this option exists in Management Studio, or because there’s no advantage in doing so.  But that changes in SQL Server 2008, with the introduction of Configuration Servers.

    So in this screen shot I have opened up the registered servers by selecting View –> Registered Servers (CTRL+ALT+G):


    As you can see there is a node for configuration servers and the first step is to add a server (in the same way as you register a new server). Then create a group underneath it and finally register the servers this configuration server will apply to ( again in the same as normal registered servers are added):



    Having done that, you can now apply policies to all of the servers in the group or a T-SQL command by running them from the configuration server.

    So in this exercise if I run this

    USE master
    SELECT * FROM sysdatabases;

    on the configuration server (by creating a new query from the configuration server) I would get a message:

    An error occurred while executing batch.
    Error message is: The server is not connected.

    DBSERVER(DBSERVER\Administrator): (11 row(s) affected)

    DBSERVER\prod(DBSERVER\Administrator): (8 row(s) affected)

    and all of the databases in each server will be in a grid in the results tab. Note the error above is because dbserver/inst03 is not running (as you can see from the management console screen shot above).

    In the same way you can right click on the configuration server and run a policy over all of the servers in the group. 

    So like lord of the rings you can have one server to control them all.

  • SQL Server 2008 Best Practice

    In SQL Server 2000 and 2005 there is a really useful utility called the Best Practice Analyzer which you can download here.  Essentially it looks at your database configuration and matches this against a set of rules to inform you you of what improvements to make, based on experience gained by Microsoft Consultants and Premier Field Engineers.

    However this tool is not going to exist as such in SQL Server 2008. Instead there will be a set of policies that you can import and use in Policy Management (btw this feature was previously known as the Declarative Management Framework -DMF).

    Policies comprise three things:

    • A Facet. This is what you are trying to manage e.g. the CLR.
    • A Condition, which you want the facet to be in e.g. you want the CLR to be on because you have written some cool string functions using the CLR that you need to implement.  
    • An execution mode.  This is how you want to test for the condition and the four options are:
      • On Demand. This mode evaluates the policy when directly specified by the user.
      • On Change - Prevent. This automated mode uses DDL triggers to prevent policy violations.
      • On Change - Log Only. This automated mode uses event notification to evaluate a policy when a relevant change occurs and logs policy violations.
      • On Schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy. The mode logs policy violations.

    You can export and import policies as xml files and then apply them to all of your servers to maintain consistency among them, but that is a needlessly manual exercise and there is a better way, by using a configuration server to run commands against a group of SQL Server instances (which I will cover in a later post).

    If you have installed CTP6 of SQL Server 2008 you will have access to a whole bunch of policies which you can import and use. They’re in:

    Program Files\Microsoft SQL Server\100\Tools\Policies

    It is these polices which replace the Best Practices Analyzer.  Policies also partly replace the Surface Area Configuration introduced in SQL Server 2005, the ability to turn the CLR off and on being a good example of this. 

    This policy based approach is very similar to how systems administrators work on the windows servers that SQL Server runs on and thus provides a consistent way of working wether you have to do both roles in a smaller organisation or collaborate with a systems administrator in a larger one.

    Finally to answer one FAQ, You can only use Policy Management on SQL Server 2008 i.e. NOT SQL Server 2000 or 2005

  • SQL Server Upgrade Assistant

    I want to mention another tool for obtaining a more thorough analysis of upgrade issues developed in conjunction with Scalability Experts and Microsoft, The SQL Server Upgrade Assistant (SUA).  This tool is free but expensive. By that I mean that although it is free to use, it requires a lot of effort, even though it’s as automated as possible, and that’s what makes it expensive.  It’s probably best suited for application developers to thoroughly test every feature against the back-end database.

    So how is this done?

    Basically by following a simple wizard in the tool, the hardest step of which is to create a  playback to be used for testing.  This consists of a backup of the database to be upgraded and a trace file capturing the spread of work performed against that database.  The tool helps you with the backup and sets up the trace profile, but you need to have the workload to run while the trace is on. Of course you could just watch a production system but this is not recommended for two reasons:

    • Performance will be impacted on a production system. 
    • Just watching what happens on a production system over a short period of time will miss certain events such as month or year end runs. Another concern might be that when your application is installed the scripts it runs may contain code not supported in a later release so you want to capture this as well.

    Ideally you would have some sort of standard test harness that is used for any regression testing as you develop the application. 

    Below is the SUA in action


    The rest of the steps for using the SUA are as per the left hand side of the dialogue above. The key step in the process is the final analysis where you compare the traces for the playback against the database before and after upgrade:


    Note that I have shown the the 2008 version here but there is also one for 2005, although the user guide for this seems to have vanished form the scalability experts site.

    So if you are in an environment organisation where you do need this kind of thorough test of an upgrade then download the tool and user guides here.

  • Upgrading SQL Server Business Intelligence

    Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence components of SQL Server, so here’s a simple grid that I have adapted from Scalability Experts for use in the upgrade workshops and presentations I give:


    Along the top is the upgrade path e.g. SQL Server 2000 - 2005, with the various components down the left hand side.  Green is good red means significant re-work while amber is somewhere in between.

    Analysis Services had a complete redesign in SQL Server 2005, and although you can convert a cube during upgrade the result will not be an optimal design or make best use of the powerful new features in SQL Server 2005/8.  SQL Server 2008 just has extra features on top of 2005, such as performance improvements and better design tools.  So in my opinion you need to completely redesign your cubes, and then adapt the calculated members to account for the subtle changes in MDX in SQL Server 2005.

    Reporting Services was developed for SQL Server 2005, but then bought out for SQL 2000 before 2005 was released.  The basic story here is that reports can be upgrade all the way along form 2000/5/8 but if you have been using custom security or making calls to the 2000 SOAP API then there this work will need to be redone as the architecture of SQL Server 2008 is completely different e.g. there is no dependency on IIS.

    Data Transformation Services (DTS)  was replaced by Integration services (SSIS) in SQL Server 2005.  You can still run DTS packages in SQL Server 2005/8 as they were but there were a number of reasons for the replacement:

    • Performance SSIS runs in memory in the same way as many other tools of this type and so is much faster.
    • Manageability.  It was difficult to debug DTS packages and it was quite difficult to move them around
    • Flexibility.  Loops and other control flows were very difficult to get working in conjunction with good error reporting.
    • Collaboration. Only one person could work on a DTS package at a time to the point that if you left the package open it would prevent it being run by someone else.
    • SSIS Fixes all that and all that has happened in SQL Server 2008 is that certain type of process are much faster and you can write your own transformations in C# using Visual Studio Tools for Applications (VSTA).

    Of all of these it is DTS that is the most widely used and is the biggest barrier to upgrading.  The interface and approach to design is very different and the huge amount of work that has gone into to DTS are the other contributors to this.  DTS packages can be converted to SSIS during a SQL Server upgrade and this will work for about 70% of the time.  Of course it is the most complex packages that won't upgrade; those with custom activeX transformations for example. 

    So as I posted a few days ago you could leverage the power of DTS xChange or continue to run the packages as is until they need to be changed anyway.  There are intermediate options like calling a DTS package from SSIS, but if you are going to start to do any work like this wouldn't it make sense to start with a clean sheet of paper and leverage the power of SSIS form the outset?

  • 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: ,
  • SQL Server Compatibility Mode

    drive 023

    I drew the short straw at the 2008 Launch event last week in Birmingham; speak for one hour on upgrading SQL Server.  I found this very difficult because on the one hand you can usually just do the upgrade, once you have done all the pre-requisite impact analysis and planning and also there isn't a huge  amount to show, before during or after the upgrade.

    One point I did want to stress is to get read of all the deadwood and redundant code as part of the exercise.  If you are moving from 2000 to 2005 or have done so already you can leave the newly upgrade database 2000 compatibility mode, and there is even a 2000 compatibility mode in SQL Server 2008 (as well as one for 2005).  Great! just back up that old 2000 database and restore to 2005/8 and relax. I wouldn't do this myself unless there was a third party application that isn't yet 2005 compliant.  Why?

    • It isn't going to run as quickly
    • The longer you leave the proper migration work the less likelihood there is that anyone will know what the code does as they will have moved on.

    The reason I do like compatibility mode is that while it is on you can then set up profiler to watch for any events that use this feature.  This can then be used to track down the stuff you need to change..

    And why the picture of 25 tons of rubble? Well I am upgrading my drive so I got rid of all the redundant drives (2 of them each 100mm thick!) before installing drive v3 as the new one would crack if laid on top of previously cracked layers.  Like upgrading SQL Server it's boring, but worth it in the end.

  • SQL Server Jobs

    After the tax man, probably the most hated profession in the IT industry is the recruitment business; everyone seems to have a horror story to tell from both the employer and candidate perspective.

    At SQL Bits a couple of weeks ago there was a session by a SQL Server recruitment specialist, James Proctor from Huntress Technology, so I thought I would have a rest from T-SQL and see what this brave chap had to say, especially as he was going to be between the DBA's and their beer and pizza.

    I have to say I found the whole presentation very interesting, even though I have the best job in the world and don't need another one.  One particular set of metrics also showed how SQL Server has taken off over the last few years:

    • 14% of all contract adverts posted on UK job boards cited SQL server as a key technology as opposed to 6% for Oracle
    • 24% of all permanent adverts posted on UK job boards cited SQL server as a key technology as opposed to 5% for Oracle
    • While the job market for most of the common BI platforms is flat, growth in demand for all aspects of the SQL Server BI platform is up by at least 40% and in the case of Integration Services by 300% on last year. 

    For everyone else in the room, James then proceeded to give a simple but effective guide to getting a slice of the action, either in a permanent position or as a contractor.  It was all blindingly obvious , but we all have a blind spot about ourselves and I have seen all of James's advice on good practice ignored in the past, the most obvious one being not telling the person interviewing you that you actually want the job!

    The same sort of thing applies to employers, James spent a good five minutes explaining to one unhappy lady that you will get a applicants with the skills you ask for, so if you are vague you will get a wide range of potentially unsuitable candidates.  Again all very obvious, but strangely this practice also continues and recruitment agents don't get paid by the number of people they put forward so they don't win out of this either.

    So don't shoot the messenger, (or James the recruitment agent).

    Technorati Tags: ,
  • SQL Server 2008 on Windows Server 2008


    I am a huge fan of Windows Server 2008.  I am lucky enough to have a laptop that supports Hyper-V and has 4Gb of RAM so I have a dual boot setup for Vista and Windows Server 2008.  I could just have Windows Server 2008 installed and then virtualise Vista on top, but there there aren't the integration components (like virtual machines additions for virtual PC, but for Hyper-V)  for Vista yet  so although it would run it wouldn't be that fast.  Also James tells me that their will be some upgrade issues moving a hyper-V virtual machine created in the beta version included in the Windows server 2008 rtm to the Hyper-V rtm again around these integration components.

    Anyway back to SQL Server. SQL Server 2008 ctp4 wouldn't work properly on the Windows Server 2008 beta around at the time; you had to remotely manage it if you wanted to use it.  That was fixed in CTP5 and with the release of Windows Server 2008 I  now run my demo's and tests on that. 

    Of course it's easy to setup a demo environment, you run everything as administrator and everything just works. In the real world you are going to need to secure and manage your data, and with many IT Pro's doing more than one job (part time dba, exchange and network managers to name a few) I thought it  be worth having a look at this white paper on running SQL Server 2008 over Windows Server 2008.  It's a high level document showing you what to use in each server to make your lives easier, if you are not an expert in the other server (like me!).

     better together

    In a bizarre postscript I have a just built a virtual machine with 3 instances of SQL  Server running on 32 bit windows server 2008, not that amazing except that the instances are called shiloh, yukon and katmai in honour of the version of SQL Server they relate to; so I have SQL Server 2000 sp4 running over Windows Server 2008.  It gives me one place to show all the upgrade tools you can use to move from SQL Server 2000 to 2005 and or 2008 at next week's big launch event at the Birmingham ICC. 

  • Word Reports from SQL Server 2008

    I was doing a demo to the final year business students Aston University today , and I noticed that you can now render reports to Word from Reporting Services (it's in CTP 6 of SQL Server 2008). About time some of you would say but this really needed Word 2007 to make it work. The down side for now is that the cunning plan to design reports in Excel & Word didn't make the cut for SQL Server 2008, so we just have the report designer to let the users make their own rpeorts. 

    However the students I showed this to today were impressed with the report designer and the BI stuff in excel (connecting to cubes and the data mining add-ins), which is a good litmus test for their usability as they have all been exposed to different tools in their year out placements and they will be tomorrows information workers. 

    Bizarrely, my offer of swag for the best answer to a question was refused as it turned out that the two students concerned were ex-Microsoft interns and owned up because they were already fully kitted out. But fair play to them as the question I asked was to explain what the balanced scorecard was and not anything to do with Microsofty stuff.

  • European PASS Conference 2008


    If you can't make the launch, and weren't at SQL Bits a couple of weeks ago then why not convince your manager, wife, accountant that you need to go Germany to get SQL fix.  The Professional Association PASS for SQL Server and BI are holding their European Conference at Neuss, 14-16 April.  So all of the fun of PASS without all that jet lag, and all but one of the tracks is in English so the only languages you will need are SQL, MDX and possibly some LINQ.

    I notice that some of the UK MVP's (Alan Mitchell & Chris Webb) will be flying the flag. I would also recommend the sessions by Steffen Krause (Microsoft Germany), Reed Jacobsen (Hitachi)  and Marco Russo(SQL BI)  who has written the MS Press book on LINQ, but came to my attention for his excellent articles on many to many dimension in Analysis Services.

    Technorati Tags: ,,
  • Upgrading Data Transformation Services (DTS)

    One of the biggest headaches in migrating away from SQL Server 2000 is Data Transformation Services (DTS).  The tool is very widely used and its initial simplicity no doubt contributed to that. A good example is of several Oracle sites where SQL Server was sat in a corner simply to move data between Oracle databases using DTS.

    But once you wanted to do anything sophisticated in DTS then its initial appeal rapidly waned, for example loops and proper error handling where often impossible to implement together.

    So in SQL Server 2005 the whole thing was replaced by Integration Services (SSIS) and although there was a migration tool the resultant packages were hard to understand and reminiscent of using the wizard to design packages in DTS.

    So my advice is to keep things running in native DTS mode, which will also be available in SQL Server 2008, until such time as you need to re-engineer a package because it needs to change anyway, perhaps because of a schema change in the source or target.  This approach will mean that in three years time when the next version of SQL Server is released you won't need to worry that DTS is no longer supported in that version, because you will have done all the migration already.

    I have noticed another approach and that is to use a third party tool, DTS xChange, which is very comprehensive solution to the problem, and as far as I'm aware is the only tool in this space.  I was impressed that it allows for packages to be converted but also for them to be  logged and to be transaction aware as required.  There's a good on-line demo on the site:


    So if you have more than about 20 migrations to do this tool would probably be worth investing as it will save you more time than it costs.

  • Excel OLAP add-in

    Excel already has always had excellent support for analysis services, but to get the best form it you need to use a version of office later than than the version of SQL Server you are connecting to.  So for analysis services 2000 excel 2003 is OK but if you connect to analysis services 2005 then you are a lot better of with excel 2007 as this version understands the newer features.

    One thing you can't do in any version of Excel is write your own analysis services (mdx) formulae.  There's no technical reason for this as all excel is doing in getting the data is sending an mdx query in XML/A form.  That has been fixed with an OLAP add-in for Excel project that has just been posted on Codeplex. The tool also allows you to see the mdx that's passed to analysis services, and my top tip for this would be to used it to design queries based on cubes for reporting services reports.


    One word of caution about keeping analysis services calculations away from the cube is that they can only be seen by going through excel and so I would see this tool being used as a prototyping tool for new calculations, and when these are ready to go in to production they are stored in the cube and available for all to use.  Why? One version of the truth - hard to achieve and very easy to break.

  • SQL Server 2008 Resource Kit

    I was at an evening  event to kick of specialist Finance based chapter of PASS (Professional Association for SQL Server) at the NYSE offices in London and I was asked about the resource kits Microsoft used to put out with SQL Server up until 2000.  The DBA asking the question was annoyed that they had gone and wanted Microsoft to reinstate them for SQL Server 2008.

    In my opinion a lot of the sort of stuff that used to be in resource kits for whatever platform is now appearing on Codeplex i.e. useful stuff written by Microsoft Consultants or MVP's that is free to use but comes with no support or warranty.  The other way tools become available is from third party partners such as  Quest, Red Gate, Idera to name a few.  Many of these have free evaluation periods or free cut down versions to get you to buy-in to them.

    This will continue with  the release of SQL Server 2008 and I can see user friendly add-ins for policy management, the performance data warehouse and so on.

  • SSDS

    A quick scan of the web will pick up meanings for SSDS:

    • Secure Software Distribution System
    • Self Service Development System
    • Sensor Surveillance Data System
    • Sexual Self-Disclosure Scale
    • Ship Self Defense System
    • Silo Self Defense System
    • Single-Ship Deep-Sweep
    • Software System Development Specification
    • Space Science Data System (NASA)
    • Space Station Data System
    • Sudden Sniffing Death Syndrome
    • Supply System Design Specification.

    Yesterday it acquired another meaning SQL Server Data Servicesimage

    Now in addition to pay as you go phones, hotels (in Japan), Motorway tolls, SSDS is a pay as you go database hosted by Microsoft and as the catchy title suggests is built on SQL Server.

    It's essentially there to support the development of flexible web  2.0 applications and so it understands all of the latest in connectivity such as SOAP, REST and LINQ.  Both VB and C# programmers and can work with it and it's in its beta form to try now.

    Many of the production details are still being worked out, such as how much will it cost, what's the SLA and so on, so join the beta and post tons of feedback on Connect.

  • Zune?

    Occasionally in the dark corners of e-bay, and amazon you'll find this odd mp3 player for sale called a Zune, Originally it looked a bit like a brick as it came with a lot of in built armour, but in its latest guise it looks more like any other well known player albeit in some odd colours.  Like a lot of hi-tech wizardry they aren't supposed to be  on sale in backwater Britain, for a number of reasons including the vagaries of media licensing and the fact that they fall foul of our strange rules on radio (they transmit and receive content out of the box and have an fm radio on board).  

    So to promote globalisation I bought mine in Seattle and so far I don't think I have upset any ambulances, aircraft or the fight against terrorism as a result of using it.  I am loving the clean interface and the big screen, but I guess I won't be sharing my music like in this video

    anytime soon as they are so rare in the UK.

    Incidentally the video has been around internally in Microsoft for a while, but I had not seen it before outside the blue firewall.  The official Microsoft guide to your lifestyle in the UK (sans Zune) is here.

    Technorati Tags:
  • SQL Server Reporting Services - Report Designer

    I was at SQL Bits (the SQL) at Aston University on Saturday and as usual if I can't answer a question on the day you will find it here:

    1. Does SQL Express 2008 include Report Designer? Yes and in fact that's all you get as BIDS only comes with standard and up (details are here).
    2. Can I use CSS with Reporting Services?    There is some support for CSS in Reporting Services 2008.  You can create your own styles for the report viewer toolbar and override the way that report manager looks in Reporting Services.css. Finally you can specify your own style sheet as a parameter when calling a report:



    The details of how to do all of this are in Books on Line (local help reference)

    If you didn't make my session there is a new clinic coming out in the next few days on Reporting Services 2008 here. And if you didn't make the event at all it was another resounding success with 325 attendees despite freight on the line between London and Birmingham, so well done to Tony Rogerson, Simon Sabin, Martin Bell, Alan Mitchell and the rest of the team.

    Anyway keep an eye out on the UK SQL community site for the next one, and be aware they also run stuff in the evenings, in case you love of SQL Server collides with other lesser interests such as DIY, extreme sports, retail etc.