One of the things that made me smile at my launch presentation on upgrading SQL Server was that when I asked if everyone had heard of the upgrade advisor everyone in the room put their hands up. This is because most of the support issues raised by customers about upgrade use this template…
DBA: My upgrade didn’t work properly [insert detail here]
Support: What did the Upgrade Advisor report when you ran it?
DBA: What the [insert favourite swear word here] is Upgrade Advisor?
This isn’t necessarily the fault of the DBA, it is another illustration of the fact that there is usually a tool or some help on Microsoft.com, but that it can be hard to find. In the case of Upgrade Advisor, the tool isn’t included in the download or media for SQL Server so you have to download it, also it didn’t come out until SQL Server 2005 sp1.
So for those of you considering upgrading SQL Server in any combination of 2000, 2005 and 2008, your first port of call should be Upgrade Advisor. It is essentially a reporting tool that takes the following as input:
The last two are just as important as not all of the SQL that hits the database is in the database e.g. applications, web services, stored procs called from isql batch files and you need to either find where the code is or ruun profiler to get traces of what is actually running against your database.
The output is a report that tells you how severe the problem is and when it should be fixed. It can be run from any client with .NET 2.0 framework installed and does not affect the targeted databases when it is run. It can take a while to run as it has to check each object in the database so the more there are the longer you have to wait. I mention this because one of the first thing you want to do when you decide to bite the bullet and upgrade is to get rid of all the redundant code and object in your database.
In SQL Server 2000 and 2005 there is a really useful utility called the Best Practice Analyzer which you can download here. Essentially it looks at your database configuration and matches this against a set of rules to inform you you of what improvements to make, based on experience gained by Microsoft Consultants and Premier Field Engineers.
However this tool is not going to exist as such in SQL Server 2008. Instead there will be a set of policies that you can import and use in Policy Management (btw this feature was previously known as the Declarative Management Framework -DMF).
Policies comprise three things:
You can export and import policies as xml files and then apply them to all of your servers to maintain consistency among them, but that is a needlessly manual exercise and there is a better way, by using a configuration server to run commands against a group of SQL Server instances (which I will cover in a later post).
If you have installed CTP6 of SQL Server 2008 you will have access to a whole bunch of policies which you can import and use. They’re in:
Program Files\Microsoft SQL Server\100\Tools\Policies
It is these polices which replace the Best Practices Analyzer. Policies also partly replace the Surface Area Configuration introduced in SQL Server 2005, the ability to turn the CLR off and on being a good example of this.
This policy based approach is very similar to how systems administrators work on the windows servers that SQL Server runs on and thus provides a consistent way of working wether you have to do both roles in a smaller organisation or collaborate with a systems administrator in a larger one.
Finally to answer one FAQ, You can only use Policy Management on SQL Server 2008 i.e. NOT SQL Server 2000 or 2005
Following on from my last post, when DBA’s have to manage multiple SQL Server 2000 instances you would normally see all of them registered on their local desktop in enterprise manager under a variety of groups such as dev, prod, test etc.
With the advent of SQL Server 2005 this practice seems to have died off, perhaps because no one knows that this option exists in Management Studio, or because there’s no advantage in doing so. But that changes in SQL Server 2008, with the introduction of Configuration Servers.
So in this screen shot I have opened up the registered servers by selecting View –> Registered Servers (CTRL+ALT+G):
As you can see there is a node for configuration servers and the first step is to add a server (in the same way as you register a new server). Then create a group underneath it and finally register the servers this configuration server will apply to ( again in the same as normal registered servers are added):
Having done that, you can now apply policies to all of the servers in the group or a T-SQL command by running them from the configuration server.
So in this exercise if I run this
SELECT * FROM sysdatabases;
on the configuration server (by creating a new query from the configuration server) I would get a message:
An error occurred while executing batch.
Error message is: The server is not connected.
DBSERVER(DBSERVER\Administrator): (11 row(s) affected)
DBSERVER\prod(DBSERVER\Administrator): (8 row(s) affected)
and all of the databases in each server will be in a grid in the results tab. Note the error above is because dbserver/inst03 is not running (as you can see from the management console screen shot above).
In the same way you can right click on the configuration server and run a policy over all of the servers in the group.
So like lord of the rings you can have one server to control them all.
I was at SQL Bits (the SQL) at Aston University on Saturday and as usual if I can't answer a question on the day you will find it here:
The details of how to do all of this are in Books on Line (local help reference)
The details of how to do all of this are in Books on Line (local help reference)
If you didn't make my session there is a new clinic coming out in the next few days on Reporting Services 2008 here. And if you didn't make the event at all it was another resounding success with 325 attendees despite freight on the line between London and Birmingham, so well done to Tony Rogerson, Simon Sabin, Martin Bell, Alan Mitchell and the rest of the team.
Anyway keep an eye out on the UK SQL community site for the next one, and be aware they also run stuff in the evenings, in case you love of SQL Server collides with other lesser interests such as DIY, extreme sports, retail etc.
I drew the short straw at the 2008 Launch event last week in Birmingham; speak for one hour on upgrading SQL Server. I found this very difficult because on the one hand you can usually just do the upgrade, once you have done all the pre-requisite impact analysis and planning and also there isn't a huge amount to show, before during or after the upgrade.
One point I did want to stress is to get read of all the deadwood and redundant code as part of the exercise. If you are moving from 2000 to 2005 or have done so already you can leave the newly upgrade database 2000 compatibility mode, and there is even a 2000 compatibility mode in SQL Server 2008 (as well as one for 2005). Great! just back up that old 2000 database and restore to 2005/8 and relax. I wouldn't do this myself unless there was a third party application that isn't yet 2005 compliant. Why?
The reason I do like compatibility mode is that while it is on you can then set up profiler to watch for any events that use this feature. This can then be used to track down the stuff you need to change..
And why the picture of 25 tons of rubble? Well I am upgrading my drive so I got rid of all the redundant drives (2 of them each 100mm thick!) before installing drive v3 as the new one would crack if laid on top of previously cracked layers. Like upgrading SQL Server it's boring, but worth it in the end.
Excel already has always had excellent support for analysis services, but to get the best form it you need to use a version of office later than than the version of SQL Server you are connecting to. So for analysis services 2000 excel 2003 is OK but if you connect to analysis services 2005 then you are a lot better of with excel 2007 as this version understands the newer features.
One thing you can't do in any version of Excel is write your own analysis services (mdx) formulae. There's no technical reason for this as all excel is doing in getting the data is sending an mdx query in XML/A form. That has been fixed with an OLAP add-in for Excel project that has just been posted on Codeplex. The tool also allows you to see the mdx that's passed to analysis services, and my top tip for this would be to used it to design queries based on cubes for reporting services reports.
One word of caution about keeping analysis services calculations away from the cube is that they can only be seen by going through excel and so I would see this tool being used as a prototyping tool for new calculations, and when these are ready to go in to production they are stored in the cube and available for all to use. Why? One version of the truth - hard to achieve and very easy to break.
One of the biggest headaches in migrating away from SQL Server 2000 is Data Transformation Services (DTS). The tool is very widely used and its initial simplicity no doubt contributed to that. A good example is of several Oracle sites where SQL Server was sat in a corner simply to move data between Oracle databases using DTS.
But once you wanted to do anything sophisticated in DTS then its initial appeal rapidly waned, for example loops and proper error handling where often impossible to implement together.
So in SQL Server 2005 the whole thing was replaced by Integration Services (SSIS) and although there was a migration tool the resultant packages were hard to understand and reminiscent of using the wizard to design packages in DTS.
So my advice is to keep things running in native DTS mode, which will also be available in SQL Server 2008, until such time as you need to re-engineer a package because it needs to change anyway, perhaps because of a schema change in the source or target. This approach will mean that in three years time when the next version of SQL Server is released you won't need to worry that DTS is no longer supported in that version, because you will have done all the migration already.
I have noticed another approach and that is to use a third party tool, DTS xChange, which is very comprehensive solution to the problem, and as far as I'm aware is the only tool in this space. I was impressed that it allows for packages to be converted but also for them to be logged and to be transaction aware as required. There's a good on-line demo on the site:
So if you have more than about 20 migrations to do this tool would probably be worth investing as it will save you more time than it costs.
Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence components of SQL Server, so here’s a simple grid that I have adapted from Scalability Experts for use in the upgrade workshops and presentations I give:
Along the top is the upgrade path e.g. SQL Server 2000 - 2005, with the various components down the left hand side. Green is good red means significant re-work while amber is somewhere in between.
Analysis Services had a complete redesign in SQL Server 2005, and although you can convert a cube during upgrade the result will not be an optimal design or make best use of the powerful new features in SQL Server 2005/8. SQL Server 2008 just has extra features on top of 2005, such as performance improvements and better design tools. So in my opinion you need to completely redesign your cubes, and then adapt the calculated members to account for the subtle changes in MDX in SQL Server 2005.
Reporting Services was developed for SQL Server 2005, but then bought out for SQL 2000 before 2005 was released. The basic story here is that reports can be upgrade all the way along form 2000/5/8 but if you have been using custom security or making calls to the 2000 SOAP API then there this work will need to be redone as the architecture of SQL Server 2008 is completely different e.g. there is no dependency on IIS.
Data Transformation Services (DTS) was replaced by Integration services (SSIS) in SQL Server 2005. You can still run DTS packages in SQL Server 2005/8 as they were but there were a number of reasons for the replacement:
Of all of these it is DTS that is the most widely used and is the biggest barrier to upgrading. The interface and approach to design is very different and the huge amount of work that has gone into to DTS are the other contributors to this. DTS packages can be converted to SSIS during a SQL Server upgrade and this will work for about 70% of the time. Of course it is the most complex packages that won't upgrade; those with custom activeX transformations for example.
So as I posted a few days ago you could leverage the power of DTS xChange or continue to run the packages as is until they need to be changed anyway. There are intermediate options like calling a DTS package from SSIS, but if you are going to start to do any work like this wouldn't it make sense to start with a clean sheet of paper and leverage the power of SSIS form the outset?
I was at an evening event to kick of specialist Finance based chapter of PASS (Professional Association for SQL Server) at the NYSE offices in London and I was asked about the resource kits Microsoft used to put out with SQL Server up until 2000. The DBA asking the question was annoyed that they had gone and wanted Microsoft to reinstate them for SQL Server 2008.
In my opinion a lot of the sort of stuff that used to be in resource kits for whatever platform is now appearing on Codeplex i.e. useful stuff written by Microsoft Consultants or MVP's that is free to use but comes with no support or warranty. The other way tools become available is from third party partners such as Quest, Red Gate, Idera to name a few. Many of these have free evaluation periods or free cut down versions to get you to buy-in to them.
This will continue with the release of SQL Server 2008 and I can see user friendly add-ins for policy management, the performance data warehouse and so on.
Occasionally in the dark corners of e-bay, and amazon you'll find this odd mp3 player for sale called a Zune, Originally it looked a bit like a brick as it came with a lot of in built armour, but in its latest guise it looks more like any other well known player albeit in some odd colours. Like a lot of hi-tech wizardry they aren't supposed to be on sale in backwater Britain, for a number of reasons including the vagaries of media licensing and the fact that they fall foul of our strange rules on radio (they transmit and receive content out of the box and have an fm radio on board).
So to promote globalisation I bought mine in Seattle and so far I don't think I have upset any ambulances, aircraft or the fight against terrorism as a result of using it. I am loving the clean interface and the big screen, but I guess I won't be sharing my music like in this video
anytime soon as they are so rare in the UK.
Incidentally the video has been around internally in Microsoft for a while, but I had not seen it before outside the blue firewall. The official Microsoft guide to your lifestyle in the UK (sans Zune) is here.
I want to mention another tool for obtaining a more thorough analysis of upgrade issues developed in conjunction with Scalability Experts and Microsoft, The SQL Server Upgrade Assistant (SUA). This tool is free but expensive. By that I mean that although it is free to use, it requires a lot of effort, even though it’s as automated as possible, and that’s what makes it expensive. It’s probably best suited for application developers to thoroughly test every feature against the back-end database.
So how is this done?
Basically by following a simple wizard in the tool, the hardest step of which is to create a playback to be used for testing. This consists of a backup of the database to be upgraded and a trace file capturing the spread of work performed against that database. The tool helps you with the backup and sets up the trace profile, but you need to have the workload to run while the trace is on. Of course you could just watch a production system but this is not recommended for two reasons:
Ideally you would have some sort of standard test harness that is used for any regression testing as you develop the application.
Below is the SUA in action
The rest of the steps for using the SUA are as per the left hand side of the dialogue above. The key step in the process is the final analysis where you compare the traces for the playback against the database before and after upgrade:
Note that I have shown the the 2008 version here but there is also one for 2005, although the user guide for this seems to have vanished form the scalability experts site.
So if you are in an environment organisation where you do need this kind of thorough test of an upgrade then download the tool and user guides here.
I am a huge fan of Windows Server 2008. I am lucky enough to have a laptop that supports Hyper-V and has 4Gb of RAM so I have a dual boot setup for Vista and Windows Server 2008. I could just have Windows Server 2008 installed and then virtualise Vista on top, but there there aren't the integration components (like virtual machines additions for virtual PC, but for Hyper-V) for Vista yet so although it would run it wouldn't be that fast. Also James tells me that their will be some upgrade issues moving a hyper-V virtual machine created in the beta version included in the Windows server 2008 rtm to the Hyper-V rtm again around these integration components.
Anyway back to SQL Server. SQL Server 2008 ctp4 wouldn't work properly on the Windows Server 2008 beta around at the time; you had to remotely manage it if you wanted to use it. That was fixed in CTP5 and with the release of Windows Server 2008 I now run my demo's and tests on that.
Of course it's easy to setup a demo environment, you run everything as administrator and everything just works. In the real world you are going to need to secure and manage your data, and with many IT Pro's doing more than one job (part time dba, exchange and network managers to name a few) I thought it be worth having a look at this white paper on running SQL Server 2008 over Windows Server 2008. It's a high level document showing you what to use in each server to make your lives easier, if you are not an expert in the other server (like me!).
In a bizarre postscript I have a just built a virtual machine with 3 instances of SQL Server running on 32 bit windows server 2008, not that amazing except that the instances are called shiloh, yukon and katmai in honour of the version of SQL Server they relate to; so I have SQL Server 2000 sp4 running over Windows Server 2008. It gives me one place to show all the upgrade tools you can use to move from SQL Server 2000 to 2005 and or 2008 at next week's big launch event at the Birmingham ICC.
I was doing a demo to the final year business students Aston University today , and I noticed that you can now render reports to Word from Reporting Services (it's in CTP 6 of SQL Server 2008). About time some of you would say but this really needed Word 2007 to make it work. The down side for now is that the cunning plan to design reports in Excel & Word didn't make the cut for SQL Server 2008, so we just have the report designer to let the users make their own rpeorts.
However the students I showed this to today were impressed with the report designer and the BI stuff in excel (connecting to cubes and the data mining add-ins), which is a good litmus test for their usability as they have all been exposed to different tools in their year out placements and they will be tomorrows information workers.
Bizarrely, my offer of swag for the best answer to a question was refused as it turned out that the two students concerned were ex-Microsoft interns and owned up because they were already fully kitted out. But fair play to them as the question I asked was to explain what the balanced scorecard was and not anything to do with Microsofty stuff.
After the tax man, probably the most hated profession in the IT industry is the recruitment business; everyone seems to have a horror story to tell from both the employer and candidate perspective.
At SQL Bits a couple of weeks ago there was a session by a SQL Server recruitment specialist, James Proctor from Huntress Technology, so I thought I would have a rest from T-SQL and see what this brave chap had to say, especially as he was going to be between the DBA's and their beer and pizza.
I have to say I found the whole presentation very interesting, even though I have the best job in the world and don't need another one. One particular set of metrics also showed how SQL Server has taken off over the last few years:
For everyone else in the room, James then proceeded to give a simple but effective guide to getting a slice of the action, either in a permanent position or as a contractor. It was all blindingly obvious , but we all have a blind spot about ourselves and I have seen all of James's advice on good practice ignored in the past, the most obvious one being not telling the person interviewing you that you actually want the job!
The same sort of thing applies to employers, James spent a good five minutes explaining to one unhappy lady that you will get a applicants with the skills you ask for, so if you are vague you will get a wide range of potentially unsuitable candidates. Again all very obvious, but strangely this practice also continues and recruitment agents don't get paid by the number of people they put forward so they don't win out of this either.
So don't shoot the messenger, (or James the recruitment agent).
A quick scan of the web will pick up meanings for SSDS:
Yesterday it acquired another meaning SQL Server Data Services
Now in addition to pay as you go phones, hotels (in Japan), Motorway tolls, SSDS is a pay as you go database hosted by Microsoft and as the catchy title suggests is built on SQL Server.
It's essentially there to support the development of flexible web 2.0 applications and so it understands all of the latest in connectivity such as SOAP, REST and LINQ. Both VB and C# programmers and can work with it and it's in its beta form to try now.
Many of the production details are still being worked out, such as how much will it cost, what's the SLA and so on, so join the beta and post tons of feedback on Connect.
If you can't make the launch, and weren't at SQL Bits a couple of weeks ago then why not convince your manager, wife, accountant that you need to go Germany to get SQL fix. The Professional Association PASS for SQL Server and BI are holding their European Conference at Neuss, 14-16 April. So all of the fun of PASS without all that jet lag, and all but one of the tracks is in English so the only languages you will need are SQL, MDX and possibly some LINQ.
I notice that some of the UK MVP's (Alan Mitchell & Chris Webb) will be flying the flag. I would also recommend the sessions by Steffen Krause (Microsoft Germany), Reed Jacobsen (Hitachi) and Marco Russo(SQL BI) who has written the MS Press book on LINQ, but came to my attention for his excellent articles on many to many dimension in Analysis Services.