Insufficient data from Andrew Fryer

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

March, 2009

  • Tip of the Day – SQL Server Client tools

    You don’t have to install all of the full SQL Server client tools to get connectivity to SQL Server or analysis services.  They have been broken up and are available individually for download as part of the SQL Server feature pack.

    For example you can pull down:

    There’s also a load of other utilities like backward compatibility stuff e.g. to edit and run data transformation services packages.

    Finally as I have blogged before these tools are not licensed in themselves, it is SQL Server that is licensed for either client access or per cpu (socket NOT core).

  • Update Tuesday – good for your health

    1st of April could well see the resurgence of the infamous Conflicker or downadup worm which some experts reckoned had infected over 3.5 million computers by January.  This is because Conflicker will use a new algorithm to work out which domains to attack. 

    Many of the problems that have been reported were simply because so many systems were not kept up to date, the critical security update being MS08-67  released on 23rd October 2008.  So if you want a quiet relaxing time this week can I suggest that you check the following resources and ensure your systems are in compliance with the advice in them:

    • Microsoft Conficker guidance page for IT Professionals.  The advice on proetection is to :
      • Apply the security update associated with MS08-067. View the security bulletin for more information about the vulnerability, affected software, detection and deployment tools and guidance, and security update deployment information.
      • Make sure you are running up-to-date antivirus software from a trusted vendor, such as Microsoft's Forefront Client Security or Windows Live OneCare. Antivirus software may also be obtained from trusted third parties such as the members of the Virus Information Alliance.
      • Check for updated protections for security software or devices, such as antivirus, network-based intrusion detection systems, or host-based intrusion prevention systems. The Microsoft Active Protection Program (MAPP) provides partners with early access to Microsoft vulnerability information. For a list of partners and links to their active protections, please visit the MAPP Partners page.
      • Isolate legacy systems using the methods outlined in the Microsoft Windows NT 4.0 and Windows 98 Threat Mitigation Guide.
      • Implement strong passwords as outlined in the Creating a Strong Password Policy whitepaper.
      • Disable the AutoPlay feature through the registry or using Group Policies as discussed in Microsoft Knowledge Base Article 967715. Microsoft released Security Advisory 967940 to notify users that the updates to allow users to disable AutoPlay/AutoRun capabilities have been deployed via automatic updating channels.
        NOTE: Windows 2000, Windows XP, and Windows Server 2003 customers must deploy the update associated with Microsoft Knowledge Base Article 967715 to be able to successfully disable the AutoRun feature. Windows Vista and Windows Server 2008 customers must deploy the security update associated with Microsoft Security Bulletin MS08-038 to be able to successfully disable the AutoRun feature.

    There is also advice on the page on how to clean up the mess if you are too late, and the various variants of Conflicker and what to do about them.

    It might also be good to let your friends and family know about this so that you can also relax when you get home and the resources they need are here.

  • Internet Explorer 8 on Vista and Windows 7

    Viral has just pinged me a write up of a hacking contest from the Washington Post (he is American after all) . The "Pwn2Own" contest at the CanSecWest security conference in Vancouver won by a 25 year German student called “Nils”.  He won $15,000 for exposing a vulnerability in IE8 beta.  This is good as this is what betas are there for and this was fixed the next day by Microsoft (as discussed here on the Microsoft Security Research & defence blog) and so the released version is that but more secure.

    However browsers run on operating systems so what’s also interesting in this article is how the cross platform browser Firefox is more secure on Windows Vista / Windows 7 of their data execution prevention (DEP) and address space layout randomization (ASLR) capabilities.  XP has DEP from sp2 but  ASLR only came in with Vista and to quote  Nils “ASLR doesn't appear to be properly implemented between OS X and versions of Safari and Firefox built for that operating system”.

    Finally it is only with IE8 that the browser can block the .NET DEP +ASLR bypass mechanism and so as Nels says in the article

    "It's getting pretty hard to do a lot of this stuff on Windows Vista and Windows 7”  

  • How Green is your SQL Server?

    The possibilities for consolidating SQL Server are many and varied, and one of the key drivers for all of these is the cost of energy which curiously doesn’t seem to have fallen in line with the price of oil & gas.

    Anyway I found this detailed whitepaper on how Microsoft applies the principles of sustainable IT as applied to SQL Server to its data centres.  The numbers in this document are staggering - 5,000 instances of SQL Server providing over 100,000 databases, but what is more interesting is the drive to virtualise a lot of this while recognising that there will be some workloads more suitable than others.

    There’s also a lot of tips that could well apply to other enterprises:

    • Crawl, walk, run. Don’t go for the maximum host consolidation ratio right away. Begin with the smaller workloads, validate your deployment, and refine the plan. Maximize your resource utilization in phases after establishing and evaluating your actual utilization.
    • Use Hyper-V pass-through disk or fixed Virtual Hard Disks (VHDs) for storage. Fixed VHDs offer some manageability benefits but provide slightly lower performance. Moving a guest to another host, for example, is simplified when using VHDs.
    • Do not over commit processors for SQL Server guests. Begin with one logical processor for one physical processor. Verify your performance and refine your configuration as needed. At some point, this may include over commitment, but begin without over commitment to manage risks.
    • Avoid the use of emulated devices in Hyper-V. Favour synthetic devices which provide better performance and lower processor overhead.
    • Establish an operating level agreement (OLA) with performance requirements for your storage provider if they are a separate service. Microsoft IT SQL Server Utility has requirements for 1ms average disk/second read/write for log and 8ms for OLTP data.

    Most of this has come form the SQL CAT paper on best practices for running SQL Server on Hyper-V, so Microsoft does actually follow its own advice!

  • Forefront add-ins

    I have decided to add security to my blogging activities so here goes..

    Microsoft has developed a range of products under its Forefront brand which add security into some of it’s more connected products. In an ideal world these would be built in to each part of the platform, however there is a huge industry in providing security software and if Microsoft started bundling these capabilities into its products there would inevitably be cries of foul, monopoly and anti-trust. 

    So what are these security add-ins and what are they protecting?

    • Office Communications Server (OCS) is all about well er.. communication and this usually occurs when someone like me wants to be on a VOIP call, or an instant messaging (IM) conversation.  To protect against malicious attacks inappropriate use of IM there is Microsoft Forefront Security for Office Communications Server(FSOCS)  (currently at Beta 3) for you to try here.  One thing that made me smile is that this uses OCS itself (IM) to let you know when out of policy events occur!

    Updated 25 March: One of my Twitter followers @Bibbleq has quite rightly pointed out that FSOCS was released on 16 March 2009, sorry about that, Andrew  

    • Exchange  is probably the most common connected Microsoft server and Microsoft Forefront Security for Exchange Server sp1 is available to try here
    • SharePoint is increasingly being opened up to allow external access as part of a business to business solution or to allow remote working without needing a VPN.  In either case the content being updated on the SharePoint sites could become compromised or infected.  So there is yet another bit of Forefront the cunningly named Forefront Security for SharePoint which is now at sp2.  A good example of the interesting world we know live in is that SAS a well known business intelligence provider and competitor to Microsoft in the BI space is actually a case study for this Forefront technology.  Also as far as I know this is the only security product available for SharePoint.

    These products are all individually licensed, and are also included in the Forefront Security Suite or the Enterprise CAL Suite.  I have called out the links to evaluation copies above but all of this is available in non time-bombed form via your TechNet subscription.

  • Five Minutes of Fame


    Speaking at SQL Bits is a cool thing to do but may terrify many of you.  Part of this is that you are on stage for an hour and that means a lot of preparation, both content and rehearsal.  So one of the organisers, Martin Bell has come up with a competition to gradually introduce the idea of speaking to you -  Five Minutes of Fame. 

    You only need to present one simple tip or how to, you’ve got five minutes and there’ll be laptops there for you to use.

    So if you aren’t coming along why not (SQL Bits is on 28th March at the Manchester Metropolitan University)? and if you are already registered why not have a go at this?  

    Technorati Tags: ,
  • 360 Degree Feedback at Microsoft

    Following on from my last post, Microsoft doesn’t have a site where you can rate our performance on an individual basis, instead it relies on surveys.  One example  of this is that you will on occasion be asked to fill out an online survey when you access a Microsoft site. 

    The most important of these is an overall customer satisfaction survey (known internally as the Global Relationship Survey) carried out every six months and sent to over 3.3 million people worldwide.  Recipients are chosen at random from carefully selected categories such as IT professionals, IT managers and developers in each country across various sizes and sectors of business by a third party company specialising in this. 

    This is a huge and expensive exercise, so why bother?  You might think this is because customer satisfaction is a lead indicator,Microsoft can use this data to predict market share and revenue.  But that could be done a lot cheaper than this as the survey has many questions in it on detailed aspects  of your relationship with respect to support, account management, the products themselves  to name just a few.

    The real reason this survey is there is to tune and improve what is working and to address the concerns raised.  So if you get one of these all I would ask is that you complete it to give us you honest and open feedback. 

    Technorati Tags: ,,
  • 360 degree feedback in the NHS

    In pretty well any service industry you can leave feedback about your experience, even Microsoft will pester you to fill in evaluation forms whenever you attend one of our events.   However while the NHS often seems to be awash with scorecards and league tables, feedback from its customers is usually limited to the occasional focus groups.

    Then the BBC picked this announcement up from Gordon Brown on 10 March -

    “We are ushering in a new world of accountability in which parents, patients and local communities shape the services they receive”

    Possibly this was this prompted by the  I want Great Care web site setup by Dr Neil Bacon to allow patients to record their feedback on doctors..


    The worst that can happen with following feedback advice on Ebay or Trip Advisor is that you stay in a crummy hotel or you didn’t get the bargain you were expecting, but with the I Want Great Care site, peoples’ health and doctors’ careers are at stake so there have to be much tighter controls to ensure the quality of the data and that the reviews are really genuine.  Dr Bacon is passionate about good patient feedback so he already has a number of controls to mitigate against this with more on the way.

    As you can imagine that this has created a lot of controversy and interest (for the case for and against check the Times OnLine, and yesterday Sunday Telegraph), but it has also attracted interest from a number of charities including Diabetes UK and the Stroke Association who want to see how these illnesses are being treated. 

    Why am I mentioning this? Because Microsoft helped to make this happen with BizSpark, a complete support service for startups.  In the case of I Want Great Care, this was the provision  of the back end technology and the hosting of a Proof of Concept on 6th March attended by senior NHS officials; Sir Muir Gray, chief knowledge officer of the NHS, Sir Cyril Chantler, chair of The King's Fund and Sir Donald Irvine, chair of Picker Europe, as well as PCT and acute trust directors.

    The site is now out there and even if you don’t use it yourself, it might inspire some thought around customer feedback in your industry, or to think about putting that web 3.0 idea into reality.


  • Virtualising Analysis Services

    Microsoft reckon that 7% of workloads are virtualised, so there’s still a lot of consolidation work to be done, but how high up the list should SQL Server Analysis Services (SSAS) be?

    In order to answer this it’s important to understand what SSAS is there to do, which is to provide a high performance ad hoc query environment for quantative analysis.  To do this effectively SSAS needs RAM and CPU as well as good connectivity, so in many BI projects analysis services is installed on a dedicated server specifically tuned for the task. 

    So it is then important to understand what the motive is for Virtualisation:

    a. We need to consolidate our server workloads

    b. We want to leverage the ease of management that virtualisation can bring, by being able to bring up, roll back and turn off workloads.

    c.  It’s new and shiny and everyone is doing it, so it must be good.

    Applying this to analysis services

    a.  If your cubes are of any size or are serving a large user community then you aren’t going to consolidate. Or you have a smaller business and you already have all the SQL Server features on one box to reduce licenses and fully utilise a single server.  Either way Virtualisation isn’t really going to help.

    b. Updates and patching and switching can bring benefits to SSAS like any platform.

    c. You will ignore this post and press on anyway.

    I haven’t mentioned performance yet and I don’t believe there are any definitive figures on running SSAS on Hyper-V or any other virtualisation platform, but some customers have noticed problems running SSAS in a virtual environment, more around concurrency than response times.

    So my question is do the benefits outweigh the disadvantages? In my opinion virtualisation of SSAS or indeed any OLAP using any virtualisation technology should be right at the end of your list of workloads to virtualise.  Firstly Virtualisation technology is moving on all the time, secondly as more and more people try this there will be more best practice around.

    Having possibly put you off if you are considering doing this using the Microsoft platform (i.e. SSAS over Hyper-V) then the SQL Customer Advisory Team Team (SQLCAT) would love to hear from you so please contact me or them on there blog.

  • SQL Data Services will be more like SQL Server

    I have to say when I got to discussing SQL Server Data Services as was with James Rowland Jones at SQL Bits cubed it left me a bit cold.  I am not a web developer I guess because I am too old, but while the ACE (Authority, Containers and Entities and Flexible Entities) model sounded attractive it looked like back to the drawing board if you wanted to port your application from SQL Server in your data centre to the cloud.

    That has changed and SDS will now support SQL (T-SQL) and understand transactions like any relational database. For a simple soul like me it seems that this is for the better -  If I have written a load of code to work with SQL Server on my phone/ laptop/server shouldn’t I also be able to also opt to deploy that to the cloud, without having to rewrite it? 

    This announcement was only made yesterday and the only public details I could find are on the SDS blog. So many of the other resources on MSDN and the SQL Server micro-site have yet to be changed, and I need to get hold of James to reshoot it hopefully at the next SQL Bits!  

  • Hindsight is a wonderful thing

    It’s time to spring clean the blog as I have realised that a few of my older posts have been overtaken by events and could be misleading if they were acted upon now.

    For example:


    • Performance Point as a separate product will not be developed beyond sp3 coming out later this year. instead the monitoring and Analytics piece will be included in SharePoint 14.

    If you spot anything else please let me know, as IT is confusing enough without me inadvertently adding to it.

  • Microsoft Application Platform Update 6th March

    My good friend Graeme Scott, while being an excellent presenter is not a blogger, but he has had a lot of requests to share his SQL Server session from the Microsoft Application Platform Update on 6th March and I am here to help.

    So if you caught his session last week here’s the link to his deck, and if you didn’t it’s a good introduction to the new features from a developer perspective.

  • Fixes and Patches

    Microsoft has this 3 pronged approach to ensuring your infrastructure is secure, but you have to do stuff as well.  This 3D security goes like this:

    Secure by Design.  According to Wikipedia, “means that the software has been designed from the ground up to be secure. Malicious practices are taken for granted and care is taken to minimize impact when a security vulnerability is discovered or on invalid user input.

    Secure by Default.  Anyone installing SQL Server 2005/8 for the first time will know that having done that, there are further tasks needed to get access to the environment remotely as all the connectivity options are turned off.  This gives the DBA the opportunity to configure security before everybody turns up to use it.

    Secure by Deployment.  This is where Microsoft and some other software suppliers, provide resources to ensure that your systems continue to be secure.  This can take several forms:

    • People. Education and training both general and about specific threats.
    • Process.  Toolkits and resources to ensure your infrastructure is as secure as possible.
    • Technology:
      • Tools to evaluate risks such as the Baseline Security Advisor 
      • Updates and fixes to mitigate security issues in its products.
      • Security tools including free tools like defender and the windows firewall as well as licensed products like Forefront
    • Communications. There are security bulletins which you can subscribe to, and a dedicated e-mail account to report vulnerabilities.

    This last section is all about trust, You trust your Microsoft or whatever vendor to be open and honest about security issues it has detected and to develop the appropriate resolution.  Your vendor and your users need to trust you to implement the advice and resources that have been provided.  I mention this because I noticed this ComputerWorld Security article that cites most (+70%) of Oracle DBA’s are not keeping up with applying patches to their systems.   This isn’t any criticism of Oracle rather it’s illustrative. However I am hoping I am evangelising to the converted to the SQL Server DBA’s here!

  • SQL Injection, still there after all these years

    Security is a thankless task, your best work goes unnoticed and if you let down your guard for a minute you can be front page news, like the Daily Telegraph (interestingly on the Guardian web site).  In this case the method of attack is old chestnut, SQL Injection which dates back to 1998.

    As a DBA there  isn’t been much you can do to stop this as it is the code in the application run by an authenticated user that is doing the work.  There are lots of tools and advice to help developers write secure code which include the following from Microsoft:

    As a DBA you can at least track what’s going on to help mitigate future attacks of this type. In SQL Server 2008 Enterprise edition you can use the audit feature which is  much easier to enable than setting up trace and takes less overhead.  It can also if necessary output to the application or security log as well to to file for later analysis.  For more on audit check this TechNet Article by the SQL Customer Advisory Team (SQLCAT).

    This is just one area where database are under threat and so I will continue this theme throughout the week to cover some of the other things you can do to keep your data secure (and possibly your careers as well!).

  • I didn’t really get the SQL Server 2008 CLR until today.

    By a strange quirk of fate I have not really had to do too much with CLR functions in SQL Server until I started hanging out on Twitter.  Then I saw this question..

    “How do I remove non-numeric characters from a VARCHAR?”

    Easy I thought until I realised that T-SQL isn’t really that good with string functions. Anyway I have cracked it but it isn’t going to be fast against a huge table, because I have a loop inside a scalar function ..

    ALTER FUNCTION production.ufnChartoNumeric
    @String nvarchar(50)
    RETURNS int
        DECLARE @CleanNumber int   -- the final product
        DECLARE @CleanString nvarchar(50) ='' -- the inputstring with only numbers in
        DECLARE @StringLength int = len(@String)+1 -- the length of the string +1
        DECLARE @Loop int = 1 -- Loop counter
        -- If the string is already a number we can assign it to the result and finish
        IF isNumeric(@String) = 1
            SET @CleanString = @String
        -- If itsn't a number we need to loop through the string a character at a time
        -- and test if each character is a number.
        -- if it is append it to our clean string if it isn't move on

            WHILE @Loop < @StringLength  -- loop for each character in the string
            IF isNumeric(Substring(@String,@Loop,1)) = 1 SET @CleanString += Substring(@String,@Loop,1)
            SET @Loop += 1 
       -- if there's a decimal point and you want to keep it, (but which one if there are 2!)
        -- you'll need more code to detect it
        -- and maybe return a float, but the first test will keep it
        -- so for this example I want to eliminate it.

        SET @CleanString = Replace(@CleanString, '-','')
        SET @CleanString = Replace(@CleanString, '+','')
        SET @CleanString = Replace(@CleanString,'.','')
        SET @CleanNumber = Cast(@CleanString as int)
        RETURN @CleanNumber

    As you can see I also had to deal with decimal points and  plus and minus signs as these could occur many times in a string. BTW there are little bits of T-SQL 2008 in here such as ‘+=’ and DECLARE .. = .. which you’ll need to change for older versions.

    It does at least work, (I tested it against a few random string and against production.product.productnumber in AdventureWorks), but the only way I can think of to improve the speed is to rewrite it as a CLR which is much better at handling in row string manipulation. 

    But I leave that for another day as I am not sure what this is exactly needed for, and often something that works now is better than something fast tomorrow.

  • SQL Server 2000 upgrade follow up

    I just wanted to make sure that anyone watching my TechNet webcast last night has all the resources they need, but before I do that I also wanted make the point that Upgrade is a simple task for many environments provided you follow the advice in the resources below:

    Finally if you want really cover your back, then my top tip is to use the support calls included your TechNet + subscription (you get 2 each year).  Please get back to me if you haven’t got one already.

  • Changing Dimensions – Fast or Slow?

    Many resources on data warehousing talk about slowly changing dimensions and how to deal with them but what happens when your dimensions change more quickly and what is does fast or quick mean in in this context?

    First let’s be clear on what is meant by slowly changing dimensions.  Any change to an attribute in a dimension typically happens far less often than changes are made to fact tables. Think of changing product specifications, customer moving address, and organisational change in a business, all of these happen much more rarely than facts are added or changed in a fact table.

    However there are some changes that happen more quickly than others, for example the current stage of a project or process could change daily or even hourly, as could the status of a customer complaint.

    Then there is the matter of how changes to dimensions should be handled.  If history is to be discarded and only the current view of the project etc. is to be kept then this is referred to as type 1 ( check my post here for more on this).  This entails doing an update which can be really slow especially if there are lots of them.  If history is to be kept then (Type 2) the processing is more complex and you end writing a row in for each time a dimension changes.  So if your dimensions are changing a lot then the refresh process is going to be very slow and your dimension are going to be very large.

    What can be done to stop this from happening?  My recommendation would be to change the design by:

    1. Taking out the fast changing attribute (for example project status) and creating a dimension with all of the possible values in.

    2. Creating a factless fact table to record the changes with the following attributes

    • ProjectID a foreign key to the project
    • StatusID a foreign key to the status dimension in point 1. above.
    • DateChangedID foreign key to the time dimension to represent the dat the status changed.
    • Optionally if the stages always go in a set order you could have  fact(s) of days to complete and/or cost to complete so you can measure the performance of each stage of the project.

    What we have lost in the process of doing this is a complete readout of the project in one simple table which makes the job of saying what is the current stage of my project a bit harder to find out. Also if project status is not null (and in my designs no dimension attributes are allowed to be null), then you need to create an initial row in the new fact table every time you create a new project to point to the initial status of the project.

    Hopefully this is useful as there isn’t too much on TechNet about designing a data warehouse. If you do want to know more check the Microsoft data warehousing books in my book list by Ralph Kimball, as this is pretty much what all of the tools in SQL Server are designed around (especially integration and reporting services).