Insufficient data from Andrew Fryer

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

December, 2007

  • SQL Server 2008 Reporting Services no longer depends on IIS

    It sometimes seems impossible to keep everyone happy all the time.  The Reporting Services predict team thought it would be a good idea to remove the dependency on IIS in SQL Server 2008, to make it easier to configure and to reduce the attack surface of the report server.

    However, there have been some concerns raised about this, e.g. is it secure, how do I know how it's configured etc.

    So what's not to like?

  • Performance Point Server Planning Dependency on SQL Server Enterprise Edition

    Sorry about the catchy title, but I wanted to clarify why the planning component of Performance Point Server in turn depends on SQL Server Enterprise edition.

    I pointed out in my recent TechNet presentations that Performance Point doesn't make use of the cube write-back feature of analysis services, which is one of the extra features in enterprise edition. Well I have done some digging around and it does and doesn't use cube write-back.

    One of the main problems with cube write-back in SQL Server is that although the write -back itself is fast query performance degrades and more and more writes are done to the cube.  So Performance Point only uses write back to do what if analysis i.e. when changes are submitted to a model they are then written back to the database which then refreshes the cube.

    To get this new plan data into the cube Performance Point uses another Enterprise only feature of analysis services, pro-active caching.  This is where the cube is automatically updated when the underlying data is changed.

    So hopefully that clears up the confusion between what my good friend Ben Tamblyn and I have been saying about write-back.

  • Heat maps in SQL Server Reporting Services 2005

    Warm up your users this winter with heat maps in SQL Server 2005:

    clip_image002

    This report was created by one of our testing gurus, Patrice Brassard, and uses a  to show up a dll he has written in c#. The code was used to simplify the process of applying conditional formatting in every cell of the report.

    The DLL contains a static public method:

    public static string GetColor(decimal redStartVal, decimal yellowStartVal, decimal greenStartVal, decimal val)

    where:

    • redStartVal represents the value where a pure red would be used
    • yellowStartVal represents the value where a pure yellow would be used
    • greenStartVal represents the value where a pure green would be used
    • val is the value where you need to get a heat map colour

    The function returns a string corresponding to the Hex value of the colour.

    The example report above represents test results so Patrice wanted only 100.00% to be shown green (i.e.99.99% should be yellow) so he used the following values:

    WLX_SSRS_Utils.HeatMap.GetColor(70.0, 99.99, 100.00, (Sum(Fields!RawPass.Value) / (Sum(Fields!RawPass.Value) + Sum(Fields!RawFail.Value) + Sum(Fields!RawNotRun.Value))) * 100.0)).

    Please note that the attached DLL that needs to be on both your dev machine and the SSRS server:

    • On the dev machine (say your PC), it needs to be deployed in %programfiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
    • On the reporting server, it needs to be deployed in %ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin

  • SQL Server 2008 Reporting Services cascading prompts.

    Here's' a practical example of how Reporting Services (SSRS) in SQL Server 2008 has been modified.  Cascading prompts in SSRS 2005 re-query if the middle of the three parameters changes.  In case you aren't familiar with the term this is where you select a country in a drop-down box and this in turn determines which states/regions appear in the region drop-down box for the user to select (i.e. those in the selected country)

    For example using  these three cascading prompts in AdventureWorksDW:

    • Parameter @Country based on dataset dsCountry = select distinct EnglishCountryRegionName from DimGeography
    • Parameter @State based on dataset dsState = select distinct StateProvinceName from DimGeography where EnglishCountryRegionName=@country
    • Parameter @City based on dataset dsCountry = select distinct city from DimGeography where EnglishCountryRegionName=@country and StateProvinceName=@state

    When a Country is selected then the State drop-down list is populated which is as expected, but when a state is selected the queries for both Country and City are both re-executed which is not necessary.  This situation worsens if you have  a separate dataset for the default value for each of these parameters as this will also cause more re-querying and there is no control you can exercise over that.

    That's all fixed in SSRS 2008; the engine knows which datasets will be affected by a parameter change and only those will re-execute.  Also the results can be shared between the parameter query and the default data set.

    The really good bit is that all that you need to do is to move the reports to SSRS2008.

  • SQL Server 2008 Transparent Data Encryption and Replication

    SQL Server 2008 will have a mechanism to encrypt the database at rest, with little overhead, and you can try this in CTP5.  While it is on the instance where it was encrypted it can be accessed by any application with out modification.  However if it is detached/attached or restored to another instance then the key used to encrypt it will need to be available on the new instance.

    How does that affect replication?

    Well you don't have to encrypt the replicated database, and that maybe OK if you are only replicating a non-sensitive part of the data.  The replication agent will decrypt and encrypt data on the way in and on the way out between the two. However the recommendation is to encrypt the databases involved with a Master Key.

    Finally you should be aware that the data going in and out of a database that has been encrypted is not itself encrypted, that is left to the transport layer so if that's important to you then you will need to apply any of the common approaches for securing traffic i.e. VPN, SSL and IPSec. 

    For further reading check MSDN here 

  • SQL Server 2008 Encryption Keys

    Encryption, Keys and certificates etc. are complicated  and confusing judging by the number of support calls coming in from DBA's.

    As I said in my last post, Transparent Data Encryption is there to stop someone walling off with your data.  A database can be encrypted using a key which is stored in a certificate, again protected by a key in the master database.  So what's to protect your master database? 

    Well that's not the top of the hierarchy for keys as this diagram shows from the security section of SQL Server Books On-Line :

    keys

    So the Service Master key can be encrypted as well and this is done using the Windows Data Protection API.  My good friend Steve Lamb will be posting about this shortly.

    Finally MSDN has the complete up to date books on-line for SQL Server 2008 here, just so you know

  • On the Seventh Day of Christmas an IT Pro sent to me...

    Seven SANs a Spinning

    7 SANs a Spinning

    If you have your SQL Server databases on a SAN, then please admire my cartoon and move along.

    However if you don't you might wish to know that mirroring in SQL Server 2008 is a little bit less painless than in 2005.  The traffic is compressed, so it will take up less bandwidth and the transfer is asynchronous in that a SQL server 2008 mirror works from a hardened (written to disk) copy, and new log records a first hardened. I found this post from SSQA.NET and of course there's books on-line on MSDN.

    Finally you can use forced failover to upgrade your SQL Server 2005 mirror to 2008:

    • Upgrade the mirror form 2005 - 2008
    • Force a failover to get the 2008 mirror to now be the principal
    • Upgrade the new mirror from 2005 - 2008
    • upgrade the witness et viola!

    A bit of testing would of course be a good idea before you do this in production, maybe using virtual machines or instance to check all is well in your environment!

    Technorati Tags: ,

     

  • TechNet Road Shows - So long and thanks for all the questions

    I am still on the road with our TechNet road shows and being the newish guy I get to open the show with Performance Point (9:45 -11:00)  and then conclude with SQL Server 2008(6:30 - 21:20). I was impressed that in Manchester about two thirds of the evening crowd had also been at the Performance Point morning session so thanks for staying.

    Many of you wanted to see my slide decks and these are available here. However my decks are designed to highlight points and I also did a fair amount of demos so I am not sure how useful they are.  I am hoping that I can record my next performance in Newcastle and post this to TechNet Edge which I hope you will find more useful.

    Thanks again for all of you who have come along and for those that didn't we will be back next year

    Technorati Tags:

    !

  • SQL Server 2008 reporting takes off!

    CTP5 has only been out for a week and so I was amazed that I got a comment on my blog from a specialist data and planning company operating in the aviation industry in the US. 

    The company, US Aviation Data and Planning Corp  (USADAP) are migrating their reports to SQL Server 2008 as I write this. They're already using Silverlight to surface that data to end users.  The company maybe fairly new and small but they have serious amounts of data on tap so for example you can find out any detailed stats about  carriers , passenger numbers and fares on any routes in the US to enable market analysts to monitor trends and predict demand.

    To quote John their lead developer -

    "We have found the Nov CTP to be a vast improvement over the existing 2005 RS product and we have also found it to be stable in our test environment with all of our reports. As the features in the Nov CTP RS would vastly increase our products value we would like to Go Live with the current Nov CTP RS and SQL 2005 back-end configuration"

    So much for waiting for service pack 1 after the product comes out!

  • On the Sixth Day of Christmas an IT Pro sent to me...

    Six Geeks a Playing

     6 Geeks a Playing

    The only thing I didn't get for Christmas was Halo 3 for the PC, because it doesn't exist! So while the other evangelists on my team are on the X-Box I will be sticking to my guns in Gears of War and Crysis on the PC.

    Technorati Tags:
  • On the Fourth Day Of Christmas an IT Pro sent to me...

    Four Jolly Nerds

    4 jolly Nerds

    (Nerd no. 4 is drawing the cartoon)

    PowerShell is going to change the way servers are managed when it ships with Windows Serve 2008.  There is already a thriving PowerShell user group in the UK who are itching to play with PowerShell when this is is available in SQL Server 2008 CTP6. 

    I am a little concerned about server guys twiddling knobs in the database, but I can see a very strong case for having a holistic mechanism to monitor a server and the applications on it and this is where PowerShell can definitely help. 

    However when the alarm bells ring please phone the DBA who'll probably use some SQL statements to do deeper analysis and fix whatever the issue is.

    Technorati Tags: ,
  • On the Third Day of Christmas an IT Pro sent to me...

    Three Benchmarks

    3 Bench Marks

    Database performance is much a bit like a major sport, there's a governing body, the Transaction Processing and Performance Council (confusingly abbreviated to TPC) and they set the competition rules and judge the results.  Unlike most sports there's no season and entrants can compete at any time, using the latest combination of hardware and software.  There are four main benchmarks in use but only three apply to comparisons of the back-end database:

    • TPC-App.  is designed for app servers and web services and makes no comparison of database performance.
    • TPC-C. The traditional standard for measuring database performance.
    • TPC-E. The newer standard for OLTP performance, but not widely used yet.
    • TPC-H. Designed to show performance of decision support solutions

    The only ray of sunshine for SQL Server here is the newer TPC-H benchmark, but does any of this matter in the real world.  Apparently not because everyone seems to want to virtualise SQL Server and other database to make them more manageable and this is always going to be at the expense of performance. So perhaps TPC will introduce TPC-V to measure this!

  • On the First Day of Christmas an IT Pro sent to me...

    A cartridge for an Ink-Jet

    1 Cartridge for an Inkjet

    Not many people know this but the ink in proprietary inkjet cartridges can be some of the most expensive fluid in the world.  Other contenders include snake venom and of course various forms forms of alcohol.  Maybe that's why they package it so well.

    The reason the stuff is so expensive is hopefully to make it light fast which could be important if your printing your wedding photo's, but not for your daughter's 5th birthday party invitations.  So if you are investing in a printer check the ink price per ml or whatever and go for the cheapest.  For those mega important pictures send them away to be done professionally.

    Technorati Tags: ,,
  • Performance Point Server - Counting the Cost

    I am ploughing through my inbox now I am not on the road anymore, and I have picked up quite a lot of flak around the costs of Performance Point.  I have passed specific queries on to the licensing guys, but the sort of thing I was getting both on-line and off-line form the TechNet road shows was

    • Can't I just buy ProClarity and therefore cut down my licensing costs?
    • It's going to cost megabucks to roll out in my company so I can't afford it.

    Dealing with each of these in turn:

    ProClarity licensing.  When Microsoft bought this into the Office product stack, the cost was reduced by over half for the whole product and if memory serves Performance Point is scarcely more expensive than ProClarity 6.3.  ProClarity did/does have a dashboard server so this function is now superceded in Performance Point by the functionality of Business Scorecard Manager.

    Performance Point Costs are too high.  I worked for a couple of Gold partners before joining Microsoft in July and I got this sort of response whether I was working with Business Objects, Cognos or Performance Point.  I am not going to debate license costs here, so I will assume they are the same for each product.  However from my own experience Performance Point can be be deployed, developed and maintained for less, in a Microsoft based environment. 

    I realise that this is contentious, but Performance Point builds on well known components:

    • Windows Server 2003r2 including Active Directory and Internet Information Services.
    • SQL Server 2005, including reporting services and analysis services.
    • SharePoint whether this is Windows SharePoint Services 3  (in Windows Server 2003) or SharePoint 2007.
    • Excel 2003 or later.

    So the only unknowns are how to use the front end tools to deliver reporting and analytical content in the Dashboard Designer, and how to model the business in Business Modeller.

    I would note that the true cost of implementing proper performance management in a business is the change management needed to get everyone using the new tools in a coordinated way.  This requires a big investment in education, not just the use of the tool, and applies no matter which vendor you decide on.

    The costs of implementation of a performance management solution, must of course be measured against the benefits and the big challenge is identifying and quantifying those. Only rarely do I see an organisation effectively measuring those benefits before and after new solution is in place, to confirm the project is a success.

    A possible answer is to predict and measure the cost and impact of decisions that will be made with a performance management tool in your business i.e. using the tool to measure its own success as well as that of the business.  Of course any success will have many friends and any failure is usually be the fault of the system, but I would submit the costs of success or failure in  your business are far higher than the cost of the system supporting those decisions.

  • Successful Performance Point

    I did a post a couple of months ago on what makes a successful BI project which includes the use of Microsoft Office Performance Point Server.  To summarise:

    • Business sponsorship form the board
    • Recognition of business need
    • Partnership between business users and the IT department
    • Culture of analysis
    • The right infrastructure.

    Ralph Kimball has published this readiness checklist in his book The Data Warehouse Lifecycle Toolkit which has been on my bookshelf for nearly a decade so nothing new here, except that not many members of the audience at the recent Performance Point sessions of the TechNet road shows had come across this before.  The list fits together so you need to tick all of the boxes not just the ones at the top.

    For example you might think that if the board want to implement a system or some change , whether manual or IT based then all is well.  However without popular support and that recognition of need then  you're be flogging a dead horse.  I have noticed that this practice is still in use in some commercial and public sector organisations so here is some helpful advice:

    1. Buying a stronger whip.
    2. Harnessing several dead horses together to increase the speed.
    3. Providing additional funding and/or training to increase the performance of the dead horse.
    4. Hiring outside contractors to ride the dead horse.
    5. Appointing a committee to study the horse.
    6. Doing a study to see if lighter riders would improve the dead horse’s performance.
    7. Arranging visits to other countries to see how others ride dead horses.
    8. Declaring that as the dead horse does not have to be fed, it is less costly, carries a lower overhead, and therefore contributes substantially more to bottom line budget savings than other horses.
    9. Reclassifying the horse as “living, impaired”.
    10. Promoting the dead horse to a management position – the more senior the better.

    I would add that using Performance Point to see how the horse is doing won't work either!

  • On the Fifth Day of Christmas an IT Pro sent to me...

    Five Acronyms

    5 Acronyms

    One of the worst things about being in IT is doing battle with all the acronyms we use, and having to constantly explain them to people who don't work in our industry.

    Two of the worst things about being in IT is doing battle with all the acronyms we use, having to constantly explain them to people who don't work in our industry, and then being expected to understand all of the abbreviations and acronyms the business use themselves.

    Three of the worst things about being in IT is doing battle with all the acronyms we use, having to constantly explain them to people who don't work in our industry, then being expected to understand all of the abbreviations and acronyms the business use themselves, and being treated like an idiot when you ask what they mean.

    Among the worst things about being in IT is doing battle with all the acronyms we use, having to constantly explain them to people who don't work in our industry, then being expected to understand all of the abbreviations and acronyms the business use themselves, being treated like an idiot when you ask what they mean, and then find out they are using the same acronym as you but it means something totally different to them.

    (after Monty Python) 

     

    Technorati Tags:
  • SQL Server 2008 White Papers

    Hot of the press, three new white papers you will want to check out on the database engine aspects of SQL Server 2008:

  • On the Second Day of Christmas an IT Pro sent to me...

    Two (Deep) Purple WAVs

    2 (deep) Purple WAVs

    Top tip for aspiring guitarists and other musicians, rip the CD that came with that book you got for Christmas onto your PC as it's easier to rewind, stop, pause the tracks from media player than from a CD player.

    When you get bored with the book check out the vast resources for guitarists on line, two I can recommend are:

    • YouTube which has loads of tutorial videos on
    • Music Theory which does what it says on the tin.

    After that start recording, who needs a posh studio in Montreux!

    Technorati Tags: ,,

     

  • SQL Server 2008 for Developers

    There seems to be this divide between developers and DBAs.  Developers need to create databases and objects within them and of course DBAs want everything nice and tidy.

    SQL Server 2008 has quite a few new features fir developers including:

    • ADO.NET entity framework to provide a semantic layer between the code and the physical database.
    • LINQ support to enable native query support from VB and C#.

    There's a new white paper on this here.

    Personally being a bit more of a DBA than a developer, I am much more of a fan of the Declarative Management Framework(DMF), which allows you to set policies to make the database more secure.  However it also allows you to mandate all sorts of standards such as naming conventions for objects e.g. all tables must start with "tbl_".  This can either be done as a rule or as a policy which can be run to check that at server database etc. is in compliance.  It's not world domination but at least DBA's get a bit of control on the databases they have to look after.

  • SQL Server Licensing

    I was talking to one of our reseller partners about licensing for SQL Server at coffee at an event yesterday.  He mentioned a bid he was involved in where a college was under the impression that SQL Server needed to be licensed for each core on the server.  Of course the reality is that if you decide to opt for CPU licensing then you license per chip irrespective of how many cores that chip has on it (check this site) .  So if that college had two four way Intel Xeons that they wanted to put SQL Server on then they would need two CPU licenses not the eight that they thought they would need.

    Which is good to know as I don't want to have to buy 2 copies of Vista for the dual core AMD Opteron PC I use to play on at home.

    Technorati Tags: ,
  • Spam for Evangelists

    Good to see some slightly different spam in the inbox. This is obviously aimed at evangelists, but may be not those at Microsoft:

     

    Dear in Christ,

    Calvary greetings in the name of our Lord Jesus Christ. I am Deacon George Useh, a member of Day Spring Ministry, basically a Prayer and deliverance Ministry.During a Prayer and fasting session in my Ministry, I asked our Lord Jesus Christ to give me the opportunity to redeem my life and purify what remains of my wealth, God delivery revealed to me to Invest in His Kingdom through you and your Ministry.

    As the bible says\\\\\\\\\\\\\\\"Go to the world,preach the gospel,spread his words,heal the sick............\\\\\\\\\\\\\\\"

    I got your email when i was lead by the spirit to be in search of the man of god on Christan search on the Internet.Like i have told you earlier in my last email that the lord minister to me to give to charity.

    I am not interested in the Earthly commissions as my rewards is from the Heaven above.I and my institution are blessed to help the needy and not after the rewards of the world as the bible says if not the lord that buideth the house the laborer labour but in vain...........

    I will like to donate to you/ministry and i will like to donate through a money order of 6,500 dollars for him to cash.Better still,i have some other charities which i wants to donate funds to and i will wants oncashing the cheque to help me donate some part of the money to the other charities or needies as well.

    I am giving you 2,000 dollars out of the money and i wants you on cashing the cheque to help me donate the remaining 4,500 dollars to some other charities or needies whose in formations i will give you when the cheque is cashed in the cash stores.I will want you to furnish me with the following in formations below:

    (1)Name which you wants the check to be addressed (2)Address where you wants me to send the cheque to(NOT P.O BOXES) (3)Your Mobile telephone number for prompt communication.

    As soon as i received this informations,i will go ahead to send you the check.After the successful completion of this first phase of the lords works with you then i can go ahead to send you another cheque and hence the continuous works of the lord.

    I Am Yours In Christ,

    Deacon George Useh

    Hopefully he won't get any takers, but I am guessing that this sort of stuff persists because someone has fallen for it.

    Technorati Tags: ,

     

  • Performance Point Server resources.

    image

    I am easily confused and Performance Point is confusing. Even finding where the the resources are for  using it can be tricky.  TechNet has got  a site with lots of online help on maintaining Performance Point while MSDN has a site dedicated to design models in it. I am not sure that MSDN is the right place for the design side stuff but the sites combined should give you everything you need f a successful deployment.

    Technorati Tags: