Insufficient data from Andrew Fryer

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

June, 2010

  • Free Tickets to the SQL Master Class

    Here’s a problem how do you give away 2 tickets to the SQL Server master class with Kimberly Trip  and Paul Randall?  The logical answer is to ask a technical question, but if it’s too hard the winner who answers it won’t get the same benefit from going to the master class.  Of course if it’s too easy like the questions after a TV show I’ll be inundated with answers without the benefit of the revenue from all those premium rate phone calls.

    So my cunning plan is to ask you to identify the truth in the following, in the sprit of the Unbelievable Truth on radio 4 (which is a personal favourite):

    1. PHP can’t talk to SQL Server 2008
    2. SQL Server 2008 isn’t supported by Microsoft if you install it on a Vmware or Citrix Xen virtual machine
    3. If you migrate from SQL Server 2000 to SQL Server 2008 R2 you’ll have to replace all your old DTS packages with integration services.
    4. If you install SQL Server 2008 on Windows Server 2008 R2 you’ll need to add the application server role to windows server first
    5. Kimberley Tripp was on the engineering team that developed the DBCC diagnostic engine in SQL Server

    If you are free for the course which is on 17th June at the Radisson Edwardian Heathrow,  let me know via a comment or Twitter which of the above is true (1-5) and I’ll send the first 2 a ticket.  If you fancy answering anyway but can’t make it , then I have a couple of  SQL Server 2088 R2 polo shirts I can lay my hands on.

    The legal small print is that this is for non-MSFT people in the UK, which should be enough as this is just a bit of fun and I am not giving away super valuable stuff .

  • IE 9 it’s not an upside down IE6

    My first love is art and the great thing about my current role is that I can use my creativity to help explain what can be done with Microsoft technology.Just last week our marketing team decided to give me a whole stand to draw all over at the Web Directions event last week at the Southbank Centre in London, to explain what Internet Explorer 9 is all about…

    I think there are three themes at work in the next release any one of which would be interesting:

    • Standards.  HTML 5 + CSS 3 + SVG 1(.1) equals IE 9. Actually it’s more complicated than my arithmetic, in that in that there are lots of other standards. The most interesting one is support for scalable vector graphics (i.e. SVG 1.1)  like the tweet cloud and asteroid example on the IE9 test drive site.
    • Speed.  IE9 makes use of DirectX 11 in Windows 7 to improve 2 graphics performance , which will depend on the GPU in your machine, but even on the modest machines we had at the event was much faster than IE8 on my rocket fast home rig.
    • Developer friendly.  I don’t expect many people reading this count themselves as  developers but being able to write one piece of code and know it will work well in most browsers means that the next generation of applications can be delivered more quickly. Also the handy F12 button brings up all the underlying code to show what’s running

    If you want to see IE9 in action then there is a technical preview here. Note this is designed to test sites and has been deliberately crippled (there’s no tabbed browsing or even a back button) as it is not intended to replace your current browser at the moment. It is updated every 8 weeks and in each release you’ll be able to see how the browser is being developed across the three themes I have described particular in the cased of standards. For example the scored for ACID 3 has gone up from 68 to 86/100 in the third preview released yesterday, as well as showing more demos of what this means for the user experience.The latest preview also has <canvas> support as in this example..

    image

    What does this mean for the IT Pro?

    Firstly IE9 will be popular for the reasons I have described, and we have had a lot positive feedback from the developer community despite initial scepticism, so this should translate into a desire to adopt it because there will be an army of sites that will leverage these new standards.

    This won’t be a problem if you are running Windows 7 or Vista as it will simply be a case of upgrading the browser possibly as part of software update, but you need to be aware that IE9 is not compatible with Windows XP.  This is mainly down to the GPU acceleration, but I suspect there would be other issues such as security even if this was overcome.  Hopefully the number of in house mission critical apps that depend on IE6 is dwindling as XP and IE6 move into extended support however it will be possible to run Win XP/IE6 inside Win 7 / IE9.  For consumers and small businesses there is XP Mode and for larger customers Microsoft Enterprise Desktop Virtualisation (MED-V) is included with the Microsoft Desktop Optimisation Pack (MDOP), that’s included in the software assurance agreement.

  • CPU Over-Commit in Hyper-V running SQL Server

    When Hyper-V was first launched there was a lot of fear and uncertainty about running SQL Server on it some inertia around virtualisation of database in general. Now it’s been around for a while production virtualisation is mainstream for small and medium workloads that need up to 4 logical processors.  There’s a lot of guidance on best practice out there and one early piece of guidance was that you shouldn’t over commit processors. Before I get into that we have ..

    image

    The virtual processor is what you assign to a virtual machine and then shows up as a CPU in your virtual machine. it is roughly equivalent to a core and there are rules on how many logical processors you can assign..

    image

     

    For example on my shuttle, Binky, I have a single CPU with four cores. so I can assign 4 virtual processors to any VM.  If I assign 4 virtual processors to 2 virtual machines (which you can definitely do) then I am over committing the processing capability I have.  Note this doesn’t apply to RAM i.e. if I have 8Gb RAM I can’t assign more RAM than I have to the sum of running VM’s and the RAM used by the host operating system.

    Anyway back to over committing the cores; in early best practice it was recommended that you didn’t do this for VMs running SQL Server and other intensive workloads e.g.SharePoint & Exchange. However I have just found this whitepaper on High Performance SQL Server Workloads on Hyper-V, and this now recommends you don’t over commit by more than 2:1.  There is a graph explaining this in the whitepaper but I found this on a recent TechEd deck which explains a little better..

    clip_image002

    What you can see here is SQL Server running the TPC-E benchmark. To the right of the graph where over commit occurs there is still near linear performance increase. So how is this possible?

    The answer is in the small print at the top of the slide – SLAT on the AMD CPU (EPT in the intel world) and support for this new feature in Windows Server 2008 R2. Essentially this is a way of mapping physical memory to virtual memory to improve performance. The downside is you need the latest generation of CPUs to get this and of course Windows Server 2008 R2, but the benefit is even higher virtual machine density on a given physical server, even running workloads like SQL Server.

    So I am still confused as to there is still resistance to virtualisation of SQL Server in general and to Hyper-V in particular, perhaps someone can enlighten me?

  • What is ADFS, and why you should care?

    Active Directory Federated Services (ADFS) doesn’t sound like the most exciting topic for a post, but I am going to post on it anyway precisely because it is boring.

    First let me log in to windows with a Live ID..

    image

    You’ll notice I have used my Microsoft alias to sign in, and I haven’t entered my password yet (my cursor is still in the Windows Live ID dialog box.  What password should I enter?  Putting my domain password in here might seem like a good choice, or it could be a password I have set up for this site like we all do on some social media sites.  None of these is  a good story for the user and it would be better not to have to enter a password at all.  This is what I get when I tab to the password field..

    image 
    ..  the Live service is asking me to sign in at Microsoft.com which is my domain.  I click on this and I get to my SQL Azure account screen.

    image

     

    What you have just seen is ADFS in action.  Essentially  Live has been told to trust the Microsoft domain to authenticate users rather than have all the Microsoft logins duplicated in Live.  Live is still responsible for authorisation i.e. deciding what I can do once I get in , and in this case the only Live service I can use with this account is Azure.

    In the real (non-Microsoft world) this is very useful stuff..

    • Today you want to allow users from another business to access your resources (SharePoint sites , e-mail etc), for example when trading with them or because they are contractors , or because you have outsourced some business function to them.

     

    • In future as you move some parts of your IT to private or public cloud infrastructure you want all the security to be seamless for the users.  A good example of this in Microsoft is that some of our agency staff have been moved over to use BPOS (the catchily named Business Productivity Online Suite) for Exchange etc. but are not aware that this has happened.  For your own applications Access Control services in Azure  will be integrated with ADFS v2 

    Of course this technology isn’t much use unless you can also pass identity information across platforms as well as federating credentials across different active directory domains.  ADFS can also be integrated with other federation platforms including IBM Tivoli, Novell Access manager, Sun Open SSO and CA (Site Minder and Federation Manager) using SAML (Security Assertion Mark-up Language). Also Microsoft’s is one of the co founders of OpenID, the organisation that is promoting standards in identity management.

    Exactly where is ADFS?  The first version of this was simply a  role that you configured in Windows Server 2008 /2008 R2, and to store any additional data above and beyond active directory there is a separate data store which is of course SQL Server (Express is fine for this). The latest version that does all the stuff I have described here is a separate download from here along with all the whitepapers step by step guides you’ll need to get started.

    To conclude, ADFS is boring for the users because there’s nothing to see, but is a good example of the sort of work that will continue to be done by IT professionals over the next few years as business transitions between traditional on premise solutions and a mix of these plus private / public cloud based services.

  • Open Competition

    Success is rarely achieved by underestimating the competition. 

    Imagine you work for software Company Z, they have no respect and hate software company X to the point that none of Company X’s stuff is ever used by Company Z, no matter how useful it is.  This is good in that Company Z is not helping its rival to be more profitable, and of course the stuff Company Z uses is more than adequate to do everything it needs. However the staff of Company Z have no idea how good their rival is, moreover trashing the competition by rote rather than from an appreciation of it is naive, even if this is just done at Company Z’s internal meetings.

    This approach also makes talking to customers difficult for two reasons:

    1. Customers may have already bought rival solutions from Company X and rubbishing them would be insulting to these customers.
    2. In the real world many customers run mixed environments, which might be a strategy (e.g. buying best of breed) or simply as the result of a merger or devolved purchasing power letting each department/division choose what it needs.

    Life is different at Company X because they don’t have any mantra or policy about using non company X software and devices. Rather employees are encouraged to use the home grown stuff , but also to evaluate, learn and test what else is out there, not just in some test lab but right across the business.  This creates respect for what else is out there, and gives a better understanding of what customers need.   This should not translate into, “ooh they have a button that can do this so we should do the same” , rather it should be about “this is good, but where is it not so good” , and “this has  taken off and we need to at least provide interoperability with it”, and possibly “that’s a great fit with what we do - let’s get together”.

    I could also argue that by being more respectful to others, Company X will probably be a nice place to work, but actually I don’t have to argue because I work for Company X.

  • Careless Talk costs…

     

    Online phishing scams are a bit passe these days, and organised crime is still keen on traditional methods for getting their hands on your identity and credit card details.  This is simple economics (even criminals have to keep down overheads in a tough economy) , based on who is likely to fall for the scam and target audience size.  This is in part because the IT industry has done a pretty good job of raising awareness around on line fraud.

     

    So what really annoys me about this is that this often means specifically targeting the most vulnerable in our society, such as the elderly and those with mental health problems.  To get these peoples’ attention the hoax usually purports to be from a well known charity, which has the potential to damage their reputation as well.

    Fake calls from utilities and banks are also common and even Microsoft’s name has been used before. However no one from Microsoft or anyone on behalf of Microsoft will ever ask you for your credit card details, be that to help you upgrade Office or Windows, as  part of a technical support call or to inform you you have won our lottery, (BTW there is no Microsoft lottery). 

    This sort of scam can obviously apply to any  respected institution, and so my approach to any call from anyone asking for my personal details is simply to phone them back on the number I trust. I have had some bizarre conversations with my Bank about this as I won’t entertain going through their security verification process without verifying them first, but they usually get the point even if they think I am odd.

    I mention all this because while it is second nature to you, and you could probably be doing a little more to educate your relatives, and your neighbours.

     

    Technorati Tags: ,
  • Tricking a client into connecting to a SQL Server Instance

    One of the challenges of consolidation and upgrades is that some applications might have hard coded or difficult to find references to the server instance they are connecting to.

    When you want to point this hypothetical application to the non-default instance on a new server you can often use DNS to create an alia to a specific server \instance combination providing the alias (i.e. the legacy server) is no longer around.

    However for side by side upgrade in a non virtual world, another simple tip is to fiddle with the SQL Server connection manager:

    • Disable all the server network protocols except TCP/IP
    • Change all the IPs of default instance to listen on port 1435 (from 1433 – default)
    • Change all the IPs port of the named instance you want to be the default to listen on port 1433
    • Restarted both default and named instance. 

    Now I can connect to the named instance by simply typing host server name (or “.”) through SSMS or the legacy application.

    I wouldn’t recommend this approach for any internet facing server as you will probably not want to use the default port of 1433 at all.

  • Cloud Security

    The biggest blocker to public cloud usage is concern about security and privacy.  This usually falls into one or all of these particular concerns:

    • Do I trust cloud provider X to look after my data?
    • Can I legally use cloud provider X given the regulatory framework in the UK?
    • Can I setup good security to get at my data/application without my users having to jump through extra authentication hoops to use it?

    Finding definitive answers to these can be hard, and although I can answer these:

    • You are probably already trusting Microsoft’s cloud services  with key data about you/ and your family  e.g. Hotmail, MSN Messenger, XBox live, SkyDrive etc.
    • We have data centres inside the EU, and some parts of government, and banks are already using Microsoft’s cloud services having satisfied themselves that they will still be compliant.
    • As I have mentioned before ADFS v2 allows credentials to be trusted from your on premise environment into the Microsoft’s cloud services.

    ..realistically you are going to want more reassurance than I can provide here.

    So you might have noticed there is a new banner at the top of the main UK TechNet page today..

    image

    ..which will take you to a one stop shop to get the answers at TechNet ON.

    This is a two weekly initiative to give you complete coverage of a particular topic from one place, in this case the complete word on Cloud Security, from subject matter experts like David Chappell, J.D. Meier and our own Dave Gristwood

    My final comment on this is that your data might actually be more secure in the cloud:

    • A physical attack (i.e. breaking in and literally grabbing the data) is going to be far harder as the Microsoft data centres are not that easy to get into , and you aren’t going to have to worry as much about a disgruntled employee wandering off with your key data
    • An online attack  is also going to be difficult given that Microsoft is well used to just about every attack known to man. 
    • As far as code vulnerability is concerned you will be running the same code as before so this no worse than before.
  • SQL Server 2008 Performance Tuning

    In an ideal world a database engine would self heal and self tune, and the SQL Server product team work long and hard on each release to try and realise this.  However in parallel with this they recognise that they can’t cater for every eventuality and so have provided more and more diagnostics and tools for the DBA to do tuning themselves.

    I know about some of this stuff so I would always defer to the experts like the Premier Field Engineers and the support team we have (GTSC).  There’s also a lot of expertise out there among the SQL Server community and partners so you shouldn’t ever be stuck with a problem.  However if you would rather not get a man in to fix & tune your application issues or you work for  a partner then you could decide to skill up in this area.

    A good place to start is this excellent 3 day level 400 Performance Monitoring & Tuning Workshop run by Ramesh Meyyappan, one of the highest scoring speakers at the recent SQL Server 2008 R2 Tech Day and the last SQL Bits.  It’s in London June 22-24  and you can register for it here.

  • A different kind of pivot

    BI is as much about making sense of information as getting the right information to the right people.  I am still amazed at the amount of reports and spreadsheets that are pure numbers, however amazement would turn to fear if pilots were using spreadsheets to look at heading altitude, climb rate, bank angle as per these examples

    Factor Reading
    Heading 180
    Altitude 3000.00
    Climb rate 5
    Bank 4.5
    lateral G 0.1
    Speed 600

     

    which looks a bit like

    Factor Reading
    Heading 18
    Altitude 300.00
    Climb rate -5
    Bank 45
    lateral G 0.3
    Speed 160

    but in the bottom example the aircraft is very low & slow while turning hard and sinking.  Pilots certainly aren’t stupid but they have a lot of decisions to make and so need to see their key information and their cockpit layout is full of visual  queues, so surely the same would apply to any decision maker with a lot on their plate.

    This is why I have blogged a fair bit about different visualisation over the last couple of years, and after a confusing chat with my manager Marc Holmes I discovered Live Pivot.  I was confused because I thought Marc had simply got the the name of PowerPivot wrong but he was actually referring to something new namely Live Pivot from Microsoft Office Labs. It’s a very visual BI tool which groups and categories images based on their metadata.  A good existing example of this on the Live Pivot site is the new car collection.. 

    image

    It’s a bit like a cheat system for Top Trumps where you can rank cars based on price, insurance group, fuel costs etc.  The collections are there to stimulate thought, but what would be the practical implications? My initial thoughts were:

    • Performance management of people , like sales staff, children , doctors etc. would come to life more if you saw who you were dealing with in the context of their peers rather than just looking at the numbers.
    • Product performance , availability in store etc.
    • competitor analysis would also benefit for this sort of visualisation.

    I have to confess I am not sure how this will play out commercially; at the moment it’s all free for you to use.   The process of building a collection is all a bit manual although  there are full instructions on the site (here) plus a tutorial. Like PowerPivot there is also an add-in for Excel, but this one for Live Pivot helps you to round up the metadata for each image, and the path to the relevant image for that row of data. It wouldn’t need a rocket scientist to write  a query against a database with these images in and surface that in Excel for this template to use.

    So definitely worth a look as it shows a completely new way to work with visual data, at worst you’ll spend an afternoon on it and realise that although it not for you it is very cool, and there’s nothing wrong with being the cool person in the office who tried something new.