Insufficient data from Andrew Fryer

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

April, 2008

  • SQL Server 2008 & Windows Server 2008 RODC

    One of the many interesting new features in Windows Server 2008 is the Read Only Domain Controller (RODC).  Simply put , the RODC can only be updated by another Domain Controller i.e. NOT directly.  It is designed for the branch office and caches the credentials of the users in that branch and saves users then having to round trip to the main domain controllers back at head office. This really comes into when the link to Head office breaks as the local users can still login using the RODC. 

    In the same way these branch offices often need to run SQL Server locally, this might then have replication set back to head office to give maximum local performance when the links to head office are slow and/or unreliable.

    There is some debate at the moment about the need for SQL Server 2008 will be supported on Windows Server 2008 when it runs the RODC role.

    I am using the word supported here in the Microsoft sense of the word i.e. if it’s broken Microsoft will fix it and support you in the process.

    In my opinion SQL Server 2008 needs to be supported on an RODC as you really only want one server in the branch.  One way to shoehorn all of a branches applications onto physical server would be to use Hyper-V i.e. to create a virtual machine on top of the physical RODC server with all the application stuff. Virtualising SQL Server in a production environment is a frequently debated topic in its own right, but might work in this scenario as SQL Server is unlikely to be maxed out in this scenario.

    So if branch offices figure in your SQL server estate, please let me know what you think about any of this.

  • The Technology Garden


    I’m at the Insight Architects Conference today and I have just been sitting in on the keynote from John Collins of Freeform Dynamics. He was going through his latest research on the perceptions and take up of Service Orientated Architecture (SOA) and Software as a Service (SaaS), and his findings are that successful companies are leveraging these approaches and also that these same companies generally see IT as an enabler rather than an overhead.

    His team have also distiiled the interviews of CIO IT managers and the business into a handy book, in the same way as Stephen Covey did for his seven habits of highly effective people.  This book the Technology Garden has six habits for successful IT managers:

    • IT need to get the basics right
    • Create a common language between business and IT
    • Establish a joint understanding between business and IT
    • Work together towards co-oridinated goals and objectives
    • Manage IT as a business driven portfolio
    • Foster relationships with key IT suppliers

    This also fits in with Ralph Kimball’s checklist for successful BI, but the point is here that it applies to all aspects of IT in the business.

  • Big Dimensions – Just because you can …

    One of the limitations of Analysis Services in SQL Server 2000 (AS 2000) was that a member in a dimension could only have  64,000 children, which was removed in SQL Server 2005 (SSAS 2005). So now you can create a customer hierarchy for your 1 million customers which has [all] at the top with each individual  customer underneath.

    The problem hasn’t really gone away, Excel will probably give up if you expand this dimension even if you have Excel 2007 which can have a million row spreadsheet. In fact problem arises at about the 500,000 row mark.  But suppose this was possible in a future release of Excel (Note this is pure hypothesis, I know nothing about Excel 14 OK), how useful would that be to the end user?

    The only way I can seeing this being of use is if the non–empty filter is on and the filters and selections made before this dimension is expanded severely limit the number of customers returned.  If the user wants to find an individual customer then they can search for it, otherwise they will be overwhelmed by the huge grid they are presented with

    My AS2000 best practice was to create extra levels to make navigation easier. For customers I would take the first letter of surname for personal customers and the first word of a commercial customer unless this was 'The'.  Of course with a really large dimension you might several of these levels. Similar approaches work for post codes, ip addresses etc.

    So make use of deep hierarchies for big dimensions and your users will thank you in the long run.

  • TechNet Subscription- Shameless Plug

    One of the challenges of modern working life is reconciling individual effort with team achievement.  I have the good fortune to work in a great team, but we are not above a little competition every now and then.

    We have all been given a code which you can use when buying your TechNet Plus subscription and mine is UKITPRO4.   The evangelist with the most subscriptions, get loads of kudos and a small prize .To use the code just go to the TechNet subscription page or call 0800 281 221.

    What’s in it for you? That would be the 30% discount that you get when using this code.  Of course 30% off of something you don’t use is no bargain, so why lash out £163.80 for a direct or £281.40 for the DVD version? My top feature would be the 2 support calls you get a year, which I have used on many occasions before joining Microsoft to get me out of jail.  I still use the software from it and the magazine helps me to stay current with all the other platforms.

    If this doesn’t appeal how does a free subscription sound?  If you are planning to evaluate SQL Server 2008 and want help to do this then there is the momentum program for early adopters of all of the new platforms.  This is by invitation and the details are here.

  • SQL Server Starvation

    During our current round of TechNet road shows we are focusing on Windows Server 2008 and I open my session with a trailer from Maclaren. This shows how they use SQL Server 2008 and I did this because there will be a lot more SQL Server content on the next road show which should be around the time SQL Server 2008 is released.

    If you can’t wait that long then you’ll have to rely on the UK SQL community for events:

    • The UK SQL Server community publish their events  at their site here
    • The UK finance chapter of the Professional Association for SQL Server (PASS) are meeting on 26th June in London and the details are here

    Anyway it’s back to Windows Server 2008 for me as the team get ready for the Manchester road show next week, it’s great stuff but it’s not SQL Server.

    Technorati Tags: ,,,
  • SQL Server Home Grown

    It seems very fashionable to buy in technology these days, and some of you must if anyone is actually writing their own stuff these days.  I have met quite a few of the product team and they are real people, Like Boris Baryshnikov who wrote resource governor, and nearly all of the stuff in SQL Server 2005 and 2008  is home grown, the only exceptions I can think of are the charts from Dundas and the rendering to Word from SoftArtisans in the 2008 version of reporting services.

    But it’s also important to understand what to develop,  feedback from MVP’s and the SQL community can help and of course it’s important to see what features are appearing in other platforms in order to compete. 

    But thought leadership is also needed; for example nobody asked for text messaging indeed it was widely ridiculed when it came out, but who would buy a phone that couldn’t sms now?

    So in order to stay ahead of the game Microsoft has started up a dedicated R&D centre for database research the Jim Gray Systems Lab

    image If you haven’t heard of Jim Gray, he was a Turing prize winner who was a technical fellow at Microsoft before going missing last year off San Fransisco in his boat Tenacious. The new lab is to be headed up by another database guru David DeWitt who has published numerous works on high performance databases.

    So just like my vegetable garden at home, it’s much more rewarding to grow your own than go out and buy stuff in.

  • SQL Server Disk I/O on Windows Server 2008

    Imagine you work in a library and it’s your turn to put the books away that have just been returned. You have a complete catalogue of where every book should go but it’s not quite right, because someone didn’t notice that the first book in the list is supposed to be in two volumes , but actually is just one slightly bigger book.  So every time you put a book away it’s location is one place out.  This would slow you down especially if you couldn’t remember this problem existed each time you put a book away (like a goldfish with a 6 second memory). 

    Now imagine you SQL server and every time you access the disk you are one track out, that would slow you down as well woudn’t it.  This can be quite a common problem and can slow disk I/O down by anywhere from 15 -30%. So why is this happening, how do find out if it affecting it and how do you fix it?

    Traditionally windows has 63 hidden sectors on the front of a disk to store such things as the partition table and master boot record.  The problem is that it claims to be using 64! So When Windows writes the first chunk of data to a new partition:

    • It writes the first 512Bytes to the 64th sector of the first track.
    • It writes the rest to the second track.
    • This single write request requires access of both tracks.
    • To read this data requires accessing both tracks

    So is this affecting you and what can you do about it? Check out the disk partition tool for windows server 2003.  Here’s a handy quote from that link:

    “Disk alignment is a required optimization and must be applied by OEMs during Setup. Disk alignment provides a significant increase in system performance. Failure to perform disk alignment can decrease performance by 10 to 15 percent in RAID array systems.”

    Although this is pitched at OEM's it might be worth checking your systems as the performance tuning consultants I know often see this when they are on client sites.

    My concern and expertise is SQL Server , but this will affect any I/O operations, so other I/O intensive applications like Exchange will also be affected as well as third party databases, such as Oracle and MySQL

    Finally the reason I included Windows Server 2008 in this post  is that provided you have started from bare disks or have bought a new server then this shouldn't affect you.


  • Mini Computing

    stuff 016

    The IT Pro team will be talking to over 2,000 of you over the next couple weeks as we embark on our sold out TechNet road show.  The bread van (as the rest of the team refer to my eccentric choice in transport) will be putting the mini into mini computing as we haul our server farm form city to city.  Disaster recovery is important so not all the gear goes in one car so Steve will be using his electric skateboard (Prius) to move the other half of the demo cluster.

    This time around the shows will not be quite as exciting as they could be as there is no SQL Server content. We are concentrating on WTF (as we refer to the Windows Server 2008 Top Features).  My next few posts will explain what these mean for DBAs, so you can understand why it might be a good idea to beat up your server team to slide Windows Server 2008 underneath SQL Server.

  • SQL Community and the Curse of The Cursors

    So following form my last post, Eric Alsop’s session for the SQL Community last night was on cursors of course.  His simple but effective presentation showed up a couple of scenarios where cursors are still useful weapons in an DBA’s armoury despite all the new stuff put into SQL standards and SQL Server over the years.  Basically Cursors are still the only way to perform operations on a dataset where you need to understand the position of a record in a set, particularly where this is fed into aggregations, for example in running totals.

    What was also good about his talk was the lively debate that it started and this is where being there is important. Podcasts and blogs have their place (otherwise why would i be writing this?), but they are not nearly as interactive as a good chat over beer and pizza.

    We also got a very good session on 64bit computing from Christian Bolton of COEO, which help to demystify the use of AWE in a 64 bit installation, and another lively debate on page file sizes on a 64-bit memory.  The top tip here is to set a realistic max memory setting for SQL Server on any 64 bit machine leaving enough for worker threads, the rest of the OS and any other applications  on the box such as third party backup, anti-virus software, minesweeper etc.

    To find out more about these sessions check the events on SQL Community site in the next couple of days (when the decks are posted).  Oh and one other thing a date for your diaries SQL Bits vNext Hatfield polytechnic 13th September.

  • The Cursors of SQLStein

    The wacky title of this post is courtesy of a friend of mine Eric Alsop of IMGroup;  it's the title of his talk at the UK SQL Community meeting tomorrow night at Microsoft Cardinal Place offices Near Victoria station from6-9pm.

    Map image

    If like me your not sure exactly what this is going to be about, then like me you'll be turning up out of curiosity, and not because of the free beers and pizza! You can just turn up, but the organisers do like to plan so please check the SQL Community Site and register for future events. These run every month and alternate between Microsoft's Reading and London offices, and there are also separate events in Scotland.

    Technorati Tags:
  • Using Oracle data with Microsoft Business Intelligence

    Before I joined Microsoft, I was working on a large project to build SQL Server 2005 analysis services cubes from data in Oracle 10g.  The fun we had trying to get the data out and setup the servers! 

    Getting the OLEDB drivers and the Oracle client tools working in a 64-bit environment is at the heart of this issue and so I was very pleased to see a simple post by Jorge Perz Campo (a Spanish Microsoft support engineer) explaining what needs to be done.  Don’t be confused that this talks about Integration Services as they both use the same drivers to access Oracle data.

    The core of the problem is that you need to use the 32-bit drivers to develop, because the BI Development studio uses them, and 64-bit drivers to run in production.  It all gets properly sorted out at runtime as the appropriate driver is used automatically by integration and analysis services.

    One thing to note is that in any 64 bit version of Windows 32 bit apps get put into Program Files (x86) while the true 64 bit apps install to Program Files.  Some Oracle  software doesn’t like paths with brackets so the 32 bit version needs to be installed to something like Program files X86 instead. This issue is discussed on the Oracle forums here.

  • Upgrading to SQL Server 2008

    Back on 19th March I did a presentation on upgrading to SQL Server 2008, as part of the UK Launch event in Birmingham.  I have now got the feedback from this session and I thought it would be interesting to share some of it.  Microsoft’s privacy policy is far more stringent than any organisation I know so I have no idea who these comments came from, but that is the main reason it takes so long to get the surveys.

    My favourite was:

    Upgrade from 2005 looked easy therefore left and went to lunch and exhibition.”  That was the plan for SQL Server 2008; make it so easy so that IT Pro’s get to have lunch.

    Also we got back

    “As we currently use 2005 infrastructure knowing what to do when the migration is required . Very informative by both presenters. Thankyou I found the session very informative and I took a lot of notes in preparation”

    But on the downside

    Too dumbed down not enough technical content.”

    No clear indication of upgrade tools to download.”

    So you can’t please all the people all the time and for those that didn’t see the last slide of this session :


    And the links from this slide are:

    The point of all of this is that your comments are picked up, your evaluations do matter, so when we’re on the TechNet Road Shows next week please fill in the feedback as it’s more fun for us and for you if you get the road shows you want. 

  • Encryption Everywhere

    I often show the Transparent Data Encryption (TDE) feature in SQL Server 2008, and then I get asked “Is that in Standard edition?” and it’s only in Enterprise.  Also TDE is very prescriptive; if you apply it to your database, it automatically applies to your backups, which then can’t be compressed (another Enterprise Edition only feature),  Also TDE doesn’t work on filestream filegroups. So where does that leave you if you can’t justify Enterprise edition in your business or you can’t use it for other reasons?

    You have 2 choices:

    • If you’re running Windows Server 2003 you can use the Encrypted File System (EFS).   This is good because you can decide which files to lock down, but takes 5-8% overhead on your system.   This will work on any file used by SQL Server any version, any edition.
    • If you are using Windows Server 2008, then there is the BitLocker feature (introduced in Vista).  This encrypts a whole volume and will work on all IDE & e-SATA volumes attached to your server. BTW why is a Vista feature in Windows Server 2008?  Simply because of customer demand, apparently servers get stolen from small offices as well as the PC’s and laptops, and the tea money.

    Finally if you only want to protect part of your database you can encrypt individual columns in SQL Server 2005. This would require you to re-write the access mechanisms to this data, where the other options above are transparent, provided the data is where it is supposed to be. The details on how to do apply encryption to individual columns are here. I would add that column encryption might be needed in addition to other forms of encryption described above to protect such things as credit card details , NI numbers and personal address information, from unauthorised access by parts of the business who aren’t allowed access.

    So lot’s of choice, as ever with Microsoft, but also lots of advice on the sites above. There is  also our very own evangelist Steve Lamb on hand for advice as well.

  • 2008 Launch Videos

    Blogging is easy to do, there’s no one watching you and you can martial your thoughts.  The problem is you don’t get any feedback until you’ve published your post.

    Presenting is much harder, apart form the demo demons, there’s a real audience and you can’t really run away.

    For me that’s relatively easy compared to being filmed as I found when I was recorded by Computer Weekly as part of the Launch event in Birmingham on 19th March.   There’s all the pressure of knowing you are going to be seen, but no feedback to work with.  Fortunately most the time the IT Pro evangelist team get to point cameras and podcast devices at other people, which is just as well as apart from Eileen, we have radio faces

    Anyway if you do want to see me sweating under the spotlights, the link on the Computer Weekly Site is here.  This will lead you into the Virtual Launch Experience where there is a computer Weekly stand and on that stand are all the videos. 

  • End of mainstream support for SQL Server 2000

    My first ever blog post was about the features of SQL Server 2000 that won’t work in SQL Server 2008, and as I have mentioned before,  SQL Server 2000 will come out of Mainstream support today!  What this means is that SQL Server 2000 will still be the subject of security fixes, and you can still get paid support for it, but other fixes will only be available if you take out extended support, which you have to pay for.

    I know of several large organisations who will take out extended support and have planned for this.  However if you weren’t aware of this then all I can suggest is that you look to migrate as soon as possible and in the meantime consider taking out extended support in the meantime if your database is mission critical.

    The key resources for upgrade have been the subject of several of my recent posts but too summarise:

    • Compatibility Mode enables SQL Server to emulate the functionality of an earlier release
    • Upgrade Advisor reports on what you need to do before and after an upgrade and can be downloaded here.
    • TechNet Forums will show allow you to see what issues others have come across and how these were resolved. The forum for SQL server install and upgrade is here
    Technorati Tags: ,
  • SQL Server Consolidation

    Whether you are into virtualisation or not, most organisations want to reduce the number of SQL Server Servers they have (and some don’t even know how many they have).  So here’s a new white paper on consolidation for SQL Server 2008.

    One of the questions I do get asked a lot about is virtualising databases as  a means of consolidation, specifically what the performance and what’s supported.

    The support question is easy and the answer is here, In reality Microsoft support make best efforts to support virtualisation of its application servers including SQL Server, but this can often mean being asked by support to reproduce the problem in a physical environment to eliminate the virtual environment form the equation.  I would also add that SQL Server 2005 is supported on Virtual Server 2005.

    No one seems to have done any work on virtual Database performance though and hopefully Microsoft will be able to do this when Hyper-V and SQL Server 2008 are released.  

    In the meantime SQL Server 2008 is a good base for cutting down the servers as it has 2 new tools for keeping lots of databases on one server running smoothly:

    • Resource governor allows you allocate resources to applications and users on the fly
    • the performance data collector (aka Performance data warehouse) is a way of checking and maintaining performance over a longer period of time, by allowing counters to be saved toa special schema that you can report from.
  • SQL Server 2008 Policy Management Blogcast

    Here’s another SQL Server 2008 blogcast on manageability, this time on using Policy Management, to implement best practice in SQL Server:


  • SQL Server 2008 Configuration Servers – Blogcast

    Following on form my post on configuration servers in SQL Server 200, I thought you might like to see it in action so here’s a 5 minute blogcast on it…


  • Microsoft Licensing

    Along with particle physics and rail travel, software licensing is at the forefront of modern mathematics.  I am not  a rocket scientist so I have had a couple of issues in the past around licensing:

    • At HM Customs I gave an application to another government and I didn’t realise that I needed to license the database it was using (anyone remember Ingres, so fast but so badly marketed).
    • I was ordering SQL Server for a new four way box and I wanted CPU licensing for it,  Unfortunately when this went down to purchasing they bought 4 copies of SQL Server Enterprise not 4 CPU licenses, because it was a fraction of the price. 

    So if you are struggling with the complexities of licensing, why not come along to this event on An Introduction and Overview of Microsoft Licensing on 15 April 2008 18:30 - 21:20  at our office in Reading:

    Map image

    Unfortunately there isn’t an event to get the best rail ticket, but there is a free bus from Reading Station to our campus!

    Technorati Tags:
  • SQL Server 2008 editions

    One of the things I think is odd about Microsoft is the hard line placed between developers and IT Professionals as each discipline needs to understand the other to a certain extent and also in smaller organisations one person ends up doing both roles.

    So I was pleased to be asked to a NxtGen (A developer user group)  meeting in Coventry to take part in their mini launch of the Windows / SQL Server / Visual Studio 2008. As you can see they even went to the trouble of handing out chocolate (even though I am not a dementor) for the occasion.


    There were a  a couple of questions in my session were about what is in each edition of SQL Server 2008. There is one page on the SQL Server 2008 site for this here

    And to answer the specific questions, Resource Governor, Transparent Data Encryption and Compression (both for the database and backups) are only in enterprise edition.  One other thing I didn’t mention was that enterprise edition is licensed for unlimited virtualisation, so you license the physical server and then you can run as many virtual machines on it as you need.