As I have mentioned before Reporting Services (SSRS) in SQL Server 2008 has been completely re-architected, but how does that affect its performance.
Firstly it’s important to understand what what reporting services is – a web service to render and deliver data in a structured manner either in tables or as graphs charts etc. So the first thing to note is that the speed of the report depends on the speed at which this service can get the data from the source.
Once the data is returned the rendering can begin. In previous versions of SQL Server SSRS would render the whole report before displaying it to the user, and so that could take some time. In SSRS 2008 the engine tries to work on the first page asap, although you will appreciate that all of the data will still be needed to cope with aggregations and pagination. It is also much more parallel and pipelined, so the users will see bigger performance gains on long and complex reports. Another niggle was that some rendering outputs (e.g. excel) took longer than others and this has also been fixed in SSRS2008.
But how fast does SSRS need to be? I think 20 seconds is a reasonable target especially for a long report because for me reporting is not an interactive task in that I don’t run report after report to get to the information I need. Taking this further many reports are required to meet deadlines for meetings or review which are known well in advance allowing them to be scheduled (provided you allow them to do that) which can further alleviate pressure on the server during the working day. In fact the only time I really care about how long a report takes is when I design it and so it makes good sense to have some test data for that in a different server/database.
So then the question becomes how many users can an instance of SSRS support and for this the SQL Customer Advisory Team (SQLCAT) have done some work here. There is a vital section at the end of this post abut configuring the database used for caching and collating data during the rendering process (normally called reportservertempdb) and a note to the effect that Windows server 2008 (as opposed to Windows Server 2003) will have a further beneficial effect.
I often get asked about hardware for SQL Server, especially now with momentum for SQL Server 2008 migrations picking up, but where’s the official advice?
As is often the case it’s all on the Microsoft site and specifically TechNet:
What is with operating systems? Nothing for ages then two on the same day, Windows 7 Beta and now Windows 2008 r2 Beta? Well sort of as Windows Server 2008 shares a lot with Vista so Windows Server 2008 r2 has a lot in common with Windows 7.
Obviously DBA’s will be a bit more interested in the Server and possibly Server core as this now has enough of the .Net framework in to run SQL Server. Running on SQL Server on core means:
You will need to install SQL Server from the command line as there is no UI in Core and remotely manage the database by putting the client tools elsewhere. Also bear in mind that Core itself is tricky to administer so you might want to practice in a virtual machine, for example you need to open up the firewall for SQL Server and you can’t do this remotely until you have configured Core to allow you to do this remotely.
When I get 5 minutes with James O’Neill we’ll show you how in the meantime if you are interested in some of the other stuff in Windows Server 2008 r2 like powershell (now with a GUI), undo for active directory and you have a TechNet subscription you can download it here by selecting and expanding operating systems. as with Windows 7 Beta this will expire in August 2009.
The idea of letting users design their own BI fills many experts with equal dread and delight. On the one hand users know exactly what they want and the IT professional is freed from the drudgery of endlessly tinkering with content. On the other end users don’t really understand the intricacies of their data, and will have difficulty in joining disparate sources together accurately.
Microsoft already has some capability in this area from Excel Services, to SQL Server Report Builder and Performance Point. However there is a dedicated project (Gemini) to specifically address this area. So the TechNet team have arranged for me to show this off at a TechNet roadshow in Edinburgh on the morning of 24th February. So please register here and bring along your questions and opinions.
The remaining videos in my quest to show 12 cool free things that you can use form Microsoft have been made by good friend Viral. Viral is the Office and Sharepoint evangelist so it is entirely appropriate that he shows you PPTPlex, a PowerPoint add-in to pimp your presentations.
It really comes into its own if you have to give a more unstructured presentation or tutorial, and you can get from the OfficeLabs site here.
Viral has made another video for my Christmas series, this time on Windows Live Sync, which allows you to aggregate and share your digital life..
and sorry it’s a little late!
The OfficeLabs guys have been busy in the run up to Christmas and have release a gizmo called Thumbtack. It’s a sort of web based OneNote and allows you to quickly aggregate research from lots of web sites. My wife Juliet explains it better than me…
I can see this being used by lots of kids for homework (hopefully not wholesale plagiarism), and by the rest of us for such things as comparative shopping.
I recently got a comment on my blog complaining about all of the editions of SQL Server, so why don’t Microsoft just stick to one?
SQL Server gets everywhere, from a smart phone to a data centre and now the cloud (with SQL Sever data services). If you are using media player the metadata is in SQL Server and if you follow any of my links to a Microsoft.com resource then chances are that the link is a row in a SQL Server database. My point is that the scale of the underlying database and the platform it runs on are very different. So the editions reflect the intended use of that edition and have the appropriate features included. For example only standard and enterprise editions include analysis services, as small workgroups, web servers and developers simply won’t use the tool.
The next bit of flak I usually get on this is around all of the goodies in Enterprise edition i.e. “why isn’t feature X in standard edition?” I don’t believe it’s about forcing users to adopt enterprise edition I think it’s about recognising that in many organisations the database engine is just an appliance that is left in the corner, it’s not under significant load and there’s no dedicated support for it. In this very common scenario all of these features aren’t going to be used and if they are they may well cause more problems than they solve..
So the editions are there so you only pay for what you use, if indeed you do pay as two of them are free (compact and express), but again intended for different uses. I have pasted a short summary of the editions below from the SQL Server site:
* Web Edition is only available for web workloads. Please see the licensing section for more details.
** Developer Edition to be used for Application Development and Testing only. Please see the licensing section for more details.
and for more details on what is in each edition go this page on TechNet.
The hugely successful SQL Bits event is coming to Manchester on 28th March. Hopefully the more northerly venue will attract some new faces while not putting off the hardcore who have 2 or even three SQL Bits polo shirts in their wardrobe.
Being involved in this event is one of my favourite parts of my job, but the organisers are very keen that the same old faces (and I have an old face!) stand aside for some new talent. So if you’re passionate about some aspect of SQL server then get an abstract together and submit it here (You’ll need to register yourself as a speaker and submit a bit about yourself as well). If you need more convincing here’s Chris Testa O’Neill, a new speaker at SQL Bits cubed …
Once you have submitted your session it then gets voted on (top tip get all your mates to vote for you!) and then you’re on! Also bear in mind that the organisers are trying to arrange for the event to be filmed again so you can share your hour of glory with your partner/ mum / boss / favourite pet what you do all day at work.
It might also be good to check out the videos of the sessions from SQL Bits cubed to get some ideas.
My wife Juliet asked me a stupid question about all those Christmas Videos Viral, James and I made.. where’s the stuff? What she meant was where are the links to try it yourself so here you are Mrs Fryer…
You need to watch a lot of these as they are updated from time to time to add more features, Windows Live writer and Live Photo Gallery being good examples of this.
I have published quite a few posts on how to upgrade from SQL 2000 to 2005/8 since I started blogging, but I still get asked a lot of questions about how and why so Alex (Editor of the TechNet Newsletter) and I though an event might be in order.
However this is a TechNet event with a difference in that you don’t have to go anywhere as we’ll be doing it via a live webcast. So it is interactive if you catch it live, but you can also get to watch it all again if you aren’t free at the time.
We are running this on hour session at 16:00 on 17 February and you can register for it here.
BTW if you want to watch it live you’ll need the live meeting client which you can download here, if not you can just watch it offline in media player.
In my final video of free stuff from Microsoft, Viral is showing my Community Clips a screencasting tool that we actually use to make most of our videos. However the Community Clips site is also a great resource of videos for learning Office …
I got my first look at Windows 7 just after TechEd EMEA last November, however it wasn’t until I saw it on an atom powered sub notebook that I got excited as these things (e.g. the Asus Eee, Dell Mini X, Samsung NC10 etc.) are selling in spades despite or because of the current economic climate.
So what’s all the fuss about? Basically for the IT professional it’s a better citizen on the corporate network:
The UI is more vista so if you liked that you’ll like this.
I mention all this now because the Beta is out there for TechNet subscribers by going here. Be aware of a few things if you are planning to have a go:
Finally if you don’t have spare kit or space on a Virtual machine environment you can keep up to date with all things Windows 7 here.
Live Mesh is the brainchild of Ray Ozzie and is cooler than the weather outside at the moment. To see why Viral has put together a short video to explain all..