Insufficient data from Andrew Fryer

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

October, 2008

  • Governance, Risk Management & Compliance

    Blog posts which just reference whitepapers etc. can annoy some of you so I try and limit these, but if you are one of the many IT managers or DBA’s worried about compliance then please read on.


    The IT Compliance Guide   is a comprehensive guide to all of those annoying acronyms that seem to get in the way of our work, like SOX, GLBA, HIPAA, EUDPD, PCI DSS, ISO 27002, COBIT 4.1, and AICPA GAPP.  Some of these (SOX HIPAAS AICPA) will only apply to those of you who work for an American firm (like me then!), but the EU data protection Directive (EUDPD), and the ISO Code of practice for information security management (ISO27002), are closer to home so this is definitely worth looking at.  It also includes an excel workbook to guide you through the processes.

    The whole thing has been signed off by auditors Grant Thornton and is free to download.

  • SQL Server 2008 Cluster on Windows Server 2008 in Hyper-V

    I remember the old batman shows had a “don’t try this at home” disclaimer after every show, and this came to mind as I started playing with Windows Server 2008, Hyper-V and SQL Server 2008  on my new shuttle PC (4 cores, 8gb RAM, 1TB on three disks)

    Here’s what I’ve done

    Installed Windows Server 2008 enterprise (you’ll see why in a minute) and added the Hyper-V role and then rebooted.

    Added four virtual machines (hence the need for enterprise as it supports four vm’s):

    • W2K8DC is a domain controller with a side serving of ISCSI, so 2 networks will be needed (one external one internal). Leave to install and then add 4 devices (quorum, Data Disk, Log Disk and one for MSDTC).
    • W2K8Node1 Windows Server 2008 and SQL Server 2008 as this isn’t a cluster this a SQL Server cluster (apologies to M&S).
    • Create node 2  like node 1
    • SQL08Demo Another SQL Server virtual machine, but with three instances of SQL Server on it  where they are the principle, mirror and witness.

    With this rig you can now play with disaster recovery and high availability in SQL Server and work out what’s best for you.  For example log ship from the cluster, log ship to the cluster form the mirror, snapshot, and replicate yourself silly.The cost is £1600 for the shuttle and £300 odd (depending on the offer you get) for a Tech|Net subscription so you have the software you need and the knowledge base  to use it properly.

    I also have to confess I built mine with a lot of help from Daniel Sol (a SQL Server support supremo) so that I can show all of this off at TechEd Barcelona on Monday.  If you want to find out more about this and you are going then come along to DAT302, and if you’re not Daniel and I will be showing you how when I get back via a series of blogcasts.

    Finally the disclaimers..

    • Don’t try this in production
    • No databases were harmed or otherwise corrupted during the making of this demo.
  • SQL Server 2008 Filestream FAQs

    I get a lot of questions about how filestream works with the other new features in SQL Server, so here’s what I know.

    First for those that don’t know, filestream is a new data type in SQL server 2008 that  handles large unstructured data files like they are in the database. The clever bit is that it also allows them to be streamed out very quickly using win32 api as though they were files on a file system. 

    This is done by creating a special filestream filegroup, which actually creates a directory tree for the files to be stored but they now have filenames of guids controlled by SQL server. Filestream thus differs from Varbinary(max) where the data is chopped up into SQL Server 8k pages which then have to be stuck back together for retrieval. 

    General FAQs:   

    How do Transactions work?  You can actually roll back a transaction where you have attempted to update a filestream object and it will revert to the previous version.  This is because the update will copy in the new object and preserve the original until you commit.  Needless to say lots of disk space could be needed here for example if your are storing HD videos in your database. 

    How are Backups affected? Backing up and restoring the database is exactly the same. This is important as it allows the structured part of the row to be exactly in step with the filestream object after a restore.  Additionally because it is filegroup it can be backed up by itself or excluded form other backups as required.

    What if I need my Filestream data in a high availability scenario?

    • Mirroring. Most importantly Mirroring isn’t supported (so no filestream filegroup will be allowed on the principal or mirror), so either you continue to use Varbinary(max) in the same way as in SQL Server 2005 for your unstructured files, or you go for…
    • Clustering. This is supported, but the filestream filegroup must be on a shared disk (so maybe you have to rely on your storage to give you high availability here). I would add that 2 node clusters are allowed in standard edition.
    • Log shipping  is also supported, but both ends must be running SQL Server 2008 with Filestream turned on.

    SQL Server 2008 interoperability with other new features..

    What about backup compression? Backup compression doesn’t work with filestream.  generally the files will already be compressed (think jpeg, divx etc.)

    What about Transparent Data Encryption? TDE doesn’t apply to filestream and in any case is only in enterprise edition.  If you need to protect the files in a filestream filegroup use the Encrypted Files System (EFS) .btw filestream filegroups can only be created on NTFS volumes.

    Can I uses Integrated Full Text Search (IFTS) with Filestream? Yes in exactly the same way as you can for normal varbinary (max) data in SQL Server 2005/8.  So you must have a unique identifier for each row and a separate column on each row to identify the type of data being stored (pdf, xlxs, docx, html and so on) for the ifilters to work.

    Which editions of SQL Server does it work with? Filestream applies to SQL Server Express up to Enterprise edition. btw the filestream filegroup does not count as part of the 4Gb limit to the size of an Express database and these special filegroup can be as large as you like. 

    For further reading go to TechNet’s Books on Line for SQL Server 2008

  • Security Update MS08-067

    There’s no easy way to say this but whatever version of windows you are running, the first thing you want to do today is install update MS08-067.  To quote from the article…

    This security update (MS08-067) resolves a vulnerability in the Server service that affects all currently supported versions of Windows. Windows XP and older versions are rated as “Critical” while Windows Vista and newer versions are rated as “Important”. Because the vulnerability is potentially wormable on those older versions of Windows, we’re encouraging customers to test and deploy the update as soon as possible.

    Full details here:

    The point here is that while Microsoft works very hard to build in security to its operating systems, new threats are always appearing and so there’s a whole other bunch of people called the Microsoft Security Response Centre (MSRC), who are constantly on the lookout for those and how to defend against them.


    Right that’s that done,back to SQL Server

    Technorati Tags: ,
  • Help for Small Business

    It’s good to see that the government has noticed that small business are essential to the lifeblood of the British economy and are putting in measures to ensure that they are paid within 10 days of submitting a bill to any public body.  Even the Sun is getting behind small businesses, and encouraging us all to what we can to help them.

    So what’s Microsoft doing then? Three things..

    1. Delivering the tools a small business needs without constraining the business.  These tools can be hosted by Microsoft (e.g. Office Live) or a specialist hosting partner, so apart from a laptop, PC or two there is no infrastructure in the business at all.  Of course you might not be ready to trust all of your information to a third party so there are a range of small business solutions to suit businesses with 1 – 250 employees like small and essential business server.  

    2.   Providing support to small business low cost and appropriate training and support.  This is not just Microsoft at work here it is also a large group of small businesses who are in business as Small Business Technology Specialists to help other businesses. In addition to that there are also loads of blogs, articles, videos, and free hands on labs as well as community events where like minded individuals can collaborate and network.

    3. Making it affordable.  This is itself about helping you to choose the appropriate solution and then assisting in financing that option. 

    So where do you get all of this from?  The Microsoft Small Business site.  Given that 68% of IT professionals work for small businesses (and like I said, many of Microsoft’s partners fall into this category) it’s something I wanted to mention here.

    Finally Viral and I are doing our bit by running TechNet events to show how to get the most out of the aforementioned small and essential business servers, the next event is in our London Offices in Victoria on the afternoon of 29th October.

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

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

  • Barriers to upgrading to SQL Server 2008

    Most IT Professionals like to use the latest (stable) version of a product, so what’s holding up your adoption of SQL Server 2008?

    Hardware. If you are moving from SQL Server 2000 to 2008 that’s probably going to mean a new server as you’ll probably want to move to 64 bit hardware and that could be tricky to get through in the current economical climate. However the vastly increased power of that new server should mean that you can consolidate this cutting power and valuable rack space in your server room.  

    Third Party Applications. It will be a while before some third party applications will e supported to run on SQL Server 2008.  The key word is supported as the majority of them won’t actually need to be changed that much, but they will need to be extensively tested.  Microsoft is no exception to this and I wanted to give you three examples:

    • SharePoint 2007 is only supported form sp1 (both Windows Sharepoint Services and MOSS), as per this article


    • Performance Point Server (PPS) will only be supported from sp2 which won’t be out at the end of the year.  I couldn’t find an online reference for this (except my own posts), but I have this on authority from the product team and our support engineers.


    • System Center Configuration Manager 2007 (RTM and SP1) now supports the use of SQL Server 2008 as a site database.  In order to upgrade a site-server database to SQL 2008 there are 2 hotfixes required:
    • · ConfigMgr 2007 RTM customers must apply hotfix KB955229

      · ConfigMgr 2007 SP1 customers must apply hotfix KB955262

      The following are requirements when performing a clean install on a SQL Server 2008 database:

      · A clean install of ConfigMgr 2007 RTM on a SQL Server 2008 database is not supported.  You must first install SQL Server 2005, upgrade to SQL Server 2008 and then apply hotfix KB955229 

      · A clean install of ConfigMgr 2007 SP1 on a SQL Server 2008 database is supported, but should apply hotfix KB955262

    Other Third Party vendors will have a similar approach. If your an ISV and reading this, then there are workshops run by my good friend Keith Burns to help make the transition less painful.

    Testing Effort. Your own time is the other barrier to upgrading to SQL Server 2008.  You could do what some early adopters have done and just do the upgrade and phone support when it goes wrong, but hopefully you will run the upgrade advisor and act on what it’s telling you  before you upgrade. 

    Obviously all of these are facets of the cost of making the upgrade, and need to be weighed against the benefits to you organisation.  In my opinion the management bits I have been presenting on My TechNet Road show are justification in themselves, and so my recommendation is to install the client tools on your own PC and see some of this for yourselves.

  • SQL for Girls

    My good friend James has coined a phrase “Work is what we do, not somewhere we go”, so I have just got off the phone to my niece who asked me to explain databases for her homework.  It reminded that I used to ask my IT dad what he did and he never really exactly explained, which put me off going into IT. So I was determined to help her and try and make databases sound interesting and fun. 

    I won’t bore you with my explanation as I am sure many of you could do a better job, but the point here is that we all have a responsibility in our industry to encourage young people to get into IT, so that the UK can continue compete and innovate. 

    So my suggestion is to get involved with your local school, guide or scout unit, etc. and pass on your skills, so that there will be another generation of grumpy old DBA’s like Colin

  • Virtualization Unplugged Tour

    I know many DBA’s have strong opinions on the pros and cons of virtualization especially when applied to SQL Server in a production environment, but I am concerned that a lot of myth and uncertainty surrounds the topic. 


    If you want to find out from the experts, and form your own opinion then I would recommend coming to one of the events on the Virtualization unplugged tour:

    Not all of this will apply to directly to DBA’s, but it’s good to know about some of the other virtualization technologies, such as terminal services particularly if your developing and managing BI infrastructure, or have an interest in sustainable IT.
  • SQL Server 2008 Free Training

    The UK SQL Server community have come up with a cunning plan to connect more people who are serious about SQL Server like they are.  They are offering free SQL Server 2008 training on any aspect you are interested in return for you setting up open community meetings at your company.  By open I mean that anyone interested in the meeting could attend i.e. not just limited to your co-workers.

    To qualify you would need to hold at least one meeting before the end of the year.  I think is a really good idea particularly outside of London, where it can be difficult to build up the community and find venues.

    If you are interested then please contact Simon Sabin, the MVP who is setting this up and giving up even more of his time to make this happen. 

  • SQL Server 2008 – Getting tough with Policy Management

    Policy management can work in a number of ways from being prescriptive to warning you either on demand or by schedule, that all is not as it should be.

    However only certain policies can be enforced i.e. they will have the option “On Change Prevent” applied to them and so you won’t get the option in evaluation Mode drop down at the bottom of the UI like this one.


    This is because to enforce policies there needs to be a ddl trigger for the thing (fact) you are trying to enforce policy on. For example this policy to prevent any tables being created in the dbo schema does have this option..


    So how do you find out what you can’t enforce? use the syspolicy_management_facets dmv:

    SELECT as 'Facet Name',
    OnDemand = CASE facet.execution_mode & 4
                      WHEN 4 THEN 'On Demand; On Schedule'
    OnChangeLogOnly = CASE facet.execution_mode & 2
                      WHEN 2 THEN 'On Change, Log Only'
    OnChangePrevent = CASE facet.execution_mode & 1
                      WHEN 1 THEN 'On Change, Prevent'
    FROM msdb.dbo.syspolicy_management_facets facet

    the execution mode is also a property of the policy as well so you could also substitute the dmv for them, syspolicy_policies.

    Bear in mind that running these on demand will have a small performance hit for each one, so it might be appropriate to apply policies like the dbo one above to your dev servers, on the theory that test and production will only inherit code from dev.

  • Left hand of the damned

    Eileen is always quite “rightly” focused on getting more women into IT, but what equality cause can a middle aged anglo-saxon guy promote, and the answer is in the previous sentence. I am left handed and we live in a right thinking society, anything from the left or “sinister” (from sinistre in Italian) is not “a droit” (French).

    Eileen has published some research on use of the right brain on this post and how women make use of this more than men.  Being left handed like Eileen and I, means you have this built in irrespective of gender. It means we think about problems differently, but here’s the thing, if the world only comprised left handed women then we’d still be missing something (apart from needing some nifty biotech to keep the species going), namely the ordered and logical approach. 

    So in my opinion it’s the combination of the two ways of thinking in a collaborative team that really works and that diversity is why I like working in Eileen’s team, and why we need all sorts of people working in IT, not just the stereotypical propeller heads.

    OK enough of this, back to the day job back to the right and left joins in SQL Server!

    Technorati Tags: ,
  • Virtualizing SQL Server 2008

    Those of you at last months SQL Server unleashed events may remember Prem Maher form the SQL Customer Advisory Team (SQLCAT),  talking about a whitepaper on virtualizing SQL Server, well it’s finally out and available here

    This white paper, unlike some I have read, is a really deep study on the whole issue as you can see..

    image .

    The scenario used, takes  a 4 instance physical machine as a baseline under low medium and high workloads.  The is then compared with virtualizing this setup as one virtual machine using Windows server Hyper-V, one one physical machine, and then with creating a virtual machine for each of the four instances and putting those on the same physical machine.

    It is awash with detailed analysis of waits and throughput and is well worth ploughing through whether you’re using SQL Server 2005 or running SQL Server on another Virtualization platform (apparently there are others and they are even supported to run windows server 2008 and sql server 2008).

    The key general recommendations are:

    • Utilize either pass-through disks or fixed VHDs for your guest virtual machine storage. These are the best option for performance, and they should provide the best results for SQL Server workloads. Dynamic VHDs are not recommended due to performance reasons.
    • Avoid using emulated devices and instead ensure that integration components for Hyper-V have been installed and synthetic devices are being used for I/O, network, and so on. Synthetic devices will provide the best performance with lowest amount of CPU overhead.
    • The ability to use some of these techniques will depend on the hardware capabilities.
    • For workloads that make heavy use of network resources, refer to the Virtualization and Network sections of the Windows Performance Tuning guide for best practices on optimizing network for your particular configuration. Test the performance with of your workload, as workload characteristics can vary greatly.

    These are also good guidelines for any high performance application like Exchange, SharePoint etc. but don’t take my word for it or that of the SQL CAT, download the stuff and try it.

  • SQL Server 2008, TechNet Road-show answers

    Following Tuesday’s TechNet event in Manchester, I promised to answer the questions in my blog that I couldn’t answer immediately, so without further ado…

    Does data collector work with all editions specifically express edition? 

    Data collection is where historical performance information is collected and stored in a database.  It was introduced in SQL Server 2008 and doesn’t work in earlier versions.  To answer the question, No, statistics cannot be gathered  for express edition nor can it be the home for the data collection database (aka performance warehouse, management data warehouse).

    Compression and Transparent Data Encryption (TDE)

    I got into a bit of a discussion at the end of the event about whether there was any benefit in compressing data in an encrypted database. Data compression works before the data is encrypted and both occur before the data is written to disk.  However backup compression won’t work well, because that will read the encrypted data and try and compress that which could actually make it bigger as there are no repeatable patterns in encrypted data for the compression algorithm to work with.

    BTW while I am on this subject TDE doesn’t increase the size of the database and you can expect about a 5% cpu hit for using it.

    SAN support

    Our support team don’t care what SAN you have to run SQL Server 2008 on. It’s not a case of not working on certain SANs, it will just be slow if it’s not configured properly. In fact one of my friends on the support desk was telling me that one customer was filling up each of the disks in their SAN in turn and so were not getting the performance of reading across all the disks in the SAN.

    GeoClustering support

    Clustering isn’t currently supported across different subnets, and I haven’t found out when it will be supported, so this will have to wait for another post. I have to confess this flawed me at the event as it’s embarrassing that somebody knew about this, and none of of my normal SQL spies in the office knew (T-shirt to whoever it was, if you tell me who you are).

    If you were there and I have missed anything please drop me an e-mail. 

  • Microsoft Kilimanjaro - nothing to see, please move along

    There are a ton of new project names coming out of the Microsoft BI conference in Seattle, which might seem a bit confusing, so I thought I would share my take on this for those who can’t justify the time or carbon to be in the states this week.

    Kilimanjaro is a bigger mountain than Katmai,  but the project is not the next release of SQL Server 2008, it’s a BI set of features that integrate into SQL Server and comprises two projects that you may have heard about..

    • Gemini.  This is a set of tools to further empower the user to create their own reports and analytics, leaving the IT guys and DBA’s to focus on the collection and performance of the data.  This will all be integrated into SharePoint, like Reporting Services already in SQL Server.
    • Madison is the project to integrate the DATAllegro technology acquired by Microsoft a couple of months ago and will enable to SQL Server to support the predicted largest data warehouses.  Another part of this project will be to add in the other recent acquisition.

    The title of my post refers to the fact that like a lot of conference announcements none of this is going to be ready for some time and even the earliest betas are a long way away, so the only impact it should have on deployments to day is that this will just add more useful stuff to the BI projects built on SQL Server.  

  • SQL Server 2008 Merge and Integration Services

    The new Merge T-SQL command has great potential in a data warehouse scenario and Integration Services is the tool in SQL Server to load the data warehouse so how do they fit together in SQL Server 2008?

    The answer is not really and I am not sure they should, so let me explain why.

    Integration Services works by extracting data from a source and then the transformations are done in memory before being written out (or loaded) to a target.  The in memory transformations have nothing to do with the relational database on either the source or target.  Of course you can drop out of integration services and run SQL scripts, but then you are not really using Integration Services. 

    Merge is a SQL statement, so yes you could put it in a task to use this command, and it might even be faster in certain circumstances, but this is part of the workflow not a transformation. If do this you may want to pick up some logging output so you know that this part of the overall process is working as expected (e.g. are the row counts what you expect)

    This is totally different from the ways the predecessor to integration services worked, data transformation services (DTS). In this tool you typically (well I did anyway) used the workflow in DTS to link together SQL views and stored procedures.  This is one of many reasons why the move to integration services is hard, you have to completely change the way you use the tool.

    For an introduction on when and how to use Merge with Integration Services check this part of SQL Server Books On Line, but to conclude it’s the usual Microsoft situation with lots of options and whenever you ask which one to use the answer is “it depends…”,

  • Free SQL Server 2008 e-book

    I love e-books, it’s so embarrassing turning up on a client site with a pile of manuals (even worse if you are a Microsofty!), as they assume you have all of books on line (BOL) and the TechNet library stored in memory. An e-book allows you to be more discrete and saves you blowing your baggage allowance or doing your back in as go from site to site.

    So here’s one for free form MS Press - Introducing Microsoft SQL Server 2008 by Peter DeBetta, Greg Low and Mark Whitehorn. To get follow this link.

    My other tip is to pull down BOL from TechNet, so you have it to hand even if you haven’t got SQL installed on your won machine.

  • Windows Live Writer just keeps getting cooler

    Every couple of months as I write my blog in Windows Live Writer, it pops up and says “would you like to try the latest beta?”, and throwing caution to the wind I say OK. It just quietly keeps getting better, so when I joined Microsoft last July you could put in a picture very easily without a degree in html or worrying about copying that image up to your site.  Now it lets you modify the image in all sorts of interesting ways such as crop tilt rotate recolour and even add a watermark…


    Technorati Tags: ,

    You can now embed video and maps as well, leaving my small brain free to concentrate on the article (although sometimes my spelling would get me a job on the Guardian!) and not worry too much about the technicalities.

    Strangely Windows Live Writer is free, as is a Microsoft Live Spaces where you can blog your socks off at no expense, except you do need to get out a bit to find something to blog about.

  • SQL Server 2008 Experience

    The SQL Server Experience is a new slick one stop shop for all things SQL Server 2008. It’s not all black gloss, there’s some good stuff for DBA’s such as interviews with the product team, training discounts and some very quirky humour (no Bill Gates/ Jerry Seinfeld though)


    Finally If you are running sql events of your own in the UK let me know and I’ll get them up there.