Having got past the pre-installation issues in my last post I am now ready to install SQL Server 2008 Express with Advanced Services..
I ran the System Configuration Checker and got a set of green lights, except for PowerShell...
and the install won’t get past that so I am going to have to find that because it’s not part of the install (which it is with Developer, Standard, Enterprise etc.) If you are still with me you can get PowerShell here. A small but good feature is that at least I can rerun the test from here especially as this installation doesn’t require a reboot. So at last we are good to go …
If I go back to the main screen I now have an installation option so I select this and agree the license and now I get to run some (more) setup rules from which I get a warning that the firewall is on :
Now I can select which bits I want in the usual way ..
and then the instance, which is how you can have multiple or similar independent versions, of SQL Server on the same machine…
I am going with the default named instance of SQL Express. Having checked the disk space, I now have to choose the accounts and in this case I am going for NT AUTHORITY\ LOCAL SERVICE..
The next screen is important as you need to get into SQL once it has installed to I am going for mixed mode, the sa password should be as strong as your administrator and don’t forget to add yourself into the SQL Server administrators as I have done here..
I have installed advanced services which comes with reporting services so there is an extra configuration screen for this..
Finally you get the inevitable screen to participate in the program to report errors and usage, followed by the rules being rerun, followed by a confirmation of your settings.
The only good thing about this tortuous process is that it is very thorough and so if you have got this far it should all work and now I can show you what it look like.
I wanted to get Express installed on my machine and so I downloaded the RC0 (of Express with Advanced Services) and ran it.
BTW there is is this Akamai download manager behind all of the RC0 downloads which you have to allow to run to get the download to start. If all is well you should have this download client running (and let me know if this is giving you grief):
The first hiccup you might hit is that the install requires the Windows Installer 4.5 which you can get here.
When I eventually ran the install it told me I needed the .net framework 3.5, however if you follow the link from the dialog it points you at the wrong download. So having installed that I got the same message again.
It turns out that you need actually need .Net framework 3.5 sp1 beta. Hopefully this will all get sorted out in the final version, as is the case with the other editions of SQL Server 2008.
I have been seeing a fair bit of confusion about the new end user reporting tools in SQL Server 2008, so I thought it would be good to try and clear it up.
In SQL Server 2005 there is a Report Builder tool that relies on a Report Model. The report Model is created by an IT guy and what the end user can report on. There are two ways to create a model:
- In the BI dev studio suing the Report model project add-in. Here a model defines the relationships between a set of relational tables, as well as the ability to add derived columns and give existing ones friendly names.
- In Report Manager (the portal used to control reporting services) a model can be created directly on top of an analysis services cube, from the properties of an analysis services data source.
The end user can then go to report manager and download a click once application which is now called Report Builder v1. This has quite limited functionality compared to the tools available in BI Dev Studio, but does create a valid report file which the user can publish to reporting services.
In SQL Server 2008 Report Builder V1 will still be there as will the tools to create models. However there is also Report Builder v2, the tool formerly known as the Report Designer Preview and Blue (because of it’s blue office toolbar). Currently in RC0 the tool is not in the SQL Server 2008 install, it is in the separate feature pack here. Unlike v1 Report Builder v2 has the full feature set of the designer inside BI Dev Studio so users are no longer limited to just using a model.
The confusion is that both versions will be supplied with SQL Sever 2008, but v1 won’t be changed at all so you can’t use the new shiny charts or any of the other new stuff in SQL Server 2008 reporting services.
I do think Report Builder v2 is a big step in the right direction as it will allow proper collaboration on reports between the techies and the users. For example I could:
- Create the model as before but now the users can us all of the power of reporting services
- Setup a blank report with a data source in it and let the user do their own thing
- Give them more help by providing a blank report with a data source and a result set.
- Help them get a report working because I can fix it and give back to them for further design work without breaking it as would happen in V1.
Therefore I can concentrate on the data elements of the report and leave the user to the look and feel.
For those of us who have been tracking the evolution of SQL Server 2008 through it’s long gestation it can be a bit annoying when things get moved around. In RC0 odds and ends seem to have disappeared and the more sceptical DBA might assume the features have been pulled prior to release, however what has usually happened is that they have been moved to somewhere more logical.
Here’s a few casualties of the redesign work in later CTPs and RC0, that have tripped me up:
Where did SQLCMD mode go in the management console?
it’s no longer on the toolbar, but it is there and you can get it back by customising the toolbar …
BTW it’s also the last option in the query menu option
What happened to all the best practices policies that were in the declarative management framework in earlier CTP’s ?
The new term is policy management , but if you expand this in tree in RC)0 those policies aren’t there..
You need to import them from here…
\program files(x86)\Microsoft SQL Server\100\Tools\Policies\Database Engine\1033
Where has the Activity Monitor gone?
It has moved and you can either get to it form the main toolbar…
Or by right clicking on the server node.
Of course if you have only just started to evaluate SQL Server 2008 then, it’s a bit like going on holiday while the decorators have been in, it’s all vaguely familiar but there’s a lot of new stuff to get used to and some of your old favourites have been thrown out or given a makeover.
Despite the Business Intelligence being the top priority for for CIO’s for the last three years, and it’s long history their are many people in business who have no idea what it is. So I was delighted to be asked to present to the executive MBA class at Cranfield School of Management yesterday as part of a series on the key technologies business should invest in.
It was interesting the my co speaker Rob Todd of Adventus came out with very similar advice around enterprise systems: one version of the truth, the need for really good cooperation between the business, the software vendor and the system implementation team for example.
I also got a lot of good questions & comment such as
”If the algorithm for a KPI is only held in one place and the whole business relies on it and it turns out to have an error in it it will affect the whole business”
Absolutely right, but what’s the alternative to have that KPI embedded in reports and spreadsheets throughout the business , which would lead to multiple versions only some of which might be correct. If the rules change all of these would have to be changed so far better to have the one source and make very sure it is correct through thorough testing and change control.
“You say that BI evolved form Executive Information Systems and Decision Support Systems, what will be the new name for BI”?
I think that BI is a good term for what it does, and it hasn’t had the bad press overall that some of the component parts have such as data warehouses, so I don’t see it changing anytime soon. There is a lot of talk about Business and Enterprise Performance Management, but this is one use of BI, namely as part of a structured process to monitor analyse and plan change to a business. There is also personal BI , so I keep track of how I am doing at work and at home, and there is team BI for the group or division you’re in.
I also think business likes stability and not changing names or coming out with new terms for the same old stuff, could win the IT industry a few friends and remember these are the people who are signing the cheques!
I also found it interesting that thee were only a couple of students who had come across the balanced scorecard before, given that so many vendors, Microsoft included extensive support for this in their products. IMO this is a simple but effective technique for aligning everyone’s efforts to the corporate strategy.
Ever since I have been using SQL Server you have had the ability to manage SQL Server from another machine, so back in the day I used enterprise manager and query analyzer on my PC to do stuff on a server hidden in a basement somewhere.
At some sites an enterprising software compliance guy would quiz me on whether or not it was OK to install the client tools on my machine. I would point out that what matters is the license to connect to the server SO provided that the server I was connected to had a CAL assigned to me or it was CPU licensed then all is well, as the client tools aren’t licensed themselves. That usually sent him away, and I had a smug look on my face until the server team applied a service pack to SQL and then I needed to service pack my PC/laptop to get working properly again.
But is this correct? In a word yes, the EULA you agree to when you download SQL Server has this clause in it:
a. Running Instances of the Additional Software. You may run or otherwise use any number of instances of additional software listed below on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software.
- Analysis Services Shared Tools
- Business Intelligence Development Studio
- SQL Server 2005 Books Online
- Connectivity Components
- Legacy Components
- Management Tools
- Notification Services Client Components
- Reporting Services Report Manager
- Reporting Services Shared Tools
- SQL Server 2005 Shared Tools
- Software Development Kit
- SQLXML Client Features
- SQL Server Mobile Server Tools
Consolidation of server is a very hot topic as server rooms fill up and cooling becomes ever more expensive. Virtualization is the current darling in this space and indeed it is fantastic for many application workloads. However it is not the only solution to the problem and this is particularly true for SQL Server. There are several tools available form Microsoft as well as para virtualization solutions like HP’s Polyserve. Looking at the Microsoft solutions in a little bit more detail:
- Consolidate databases using SQL Server instances. This has been a popular approach because you can assign CPU and memory to an instance to guarantee the resource each database will get. The down side is that if you split your machine into instances like this and one instance is not working that hard the other instances can’t get at the unused resources even if they are under pressure. You also have to patch both instances here and so the maintenance can be a bit tricky.
- Consolidate databases into one instance of SQL Server. This is not so popular as up until now you have no control over resource contention when the server is under pressure. However with SQL Server 2008 there is resource governor in the enterprise edition which allows for fine grain control of memory and CPU, (but not IO yet).
- And then of course there is virtualization, which is being widely used, but here again you are carving up a physical machine in terms of cores, RAM etc. so a virtual machine can only ever use what is allocated to it no matter how busy the other parts of it are.
One thing to note on all of these approaches this is network performance. If you had 2 servers before and you only have 1 now then unless you have multiple network card in the new box the two instances will have contend for the network card.
There is a white paper on SQL Consolidation here
Finally the licensing of all of these options can affect your decision. Enterprise edition makes this quite simple as you license the physical machine per CPU, so you can have as many SQL Server instances, SQL Server virtual machines (any vendor not just Hyper-V) as you can cram on there.
There is some buzz around about SQL Server 2008 being out in August e.g. on SQL Server Central, however that’s more than I know. I do know that it is listed in the August price list and that pricing is no different form SQL Server 2005, which is more than I can say for some other database products or the price of fuel.
As far as release dates I know it will be out by the end of Q3 this year but that could be 11:59 pacific time on 30 September, although I am hoping it will be out before SQL Bits so I can demo against the release rather than RC0.
However I am always curious about who really cares about precise dates apart from journalists who will flame Microsoft if 'it’s late, but what about the DBA who wants to use it? IMO all I think you/they need is a plan date and to know what’s in it that justifies an upgrade or migration. For example McLaren Electronic Systems are on the SQL Server TAP Program because they want to use Filestream, ITV.com are very interested in resource governor, and there is a lot of early adoption activity around spatial data.
Bottom line - It will be out sometime in Q3 when it’s ready
Planning for SQL Bits cubed (13th September in Hatfield) is moving along but the organisers need your help to make the day successful. The simplest thing you can do is to register on the sort stuff you want to here about. For the extrovert and dedicated among you register as a speaker and submit your own session(s). I have submitted a couple of sessions myself but this is not a Microsoft event it is your event and I will only present because I have been asked.
Please register and submit your sessions here and I look forward to meeting you all there.
I got one of my neighbour’s sons onto our one week work experience program this week, so I thought I would drop in to watch the presentations they had to do. Everyone was genuinely impressed with the quality of the work, the imagination and professionalism they showed, and their presentation skills would put many of us to shame as they showed no fear and no preconceptions. One team from the FunTech program went for the north face and chose to compare XP vs Vista. Their presentation started off with a simple scientific approach by simply opening calculator in XP and Vista:
- It takes 12 seconds on XP to hit start –>programs –> accessories –> calculator, assuming you know where to go.
- Same hardware now running Vista search calc… click on calculator 7 seconds and no a priori knowledge needed.
This is such a simple message, but Microsoft has simply not articulated the argument as simply as this. They wanted to bring back Bob as well and showed us a few sketches..
However the best bit was when one of the team Emily (You know who you are!) concluded the presentation by simply saying “Vista is a chick magnet”. What she meant was that girls like her think it is cool to use, but I am sure most people seeing the title of this post are going to assume that Vista has the same kind of pulling power as a Ferrari.
Thanks to the new vista squad for letting me use their stuff…
- Angus Rigby
- Stuart Shepherd
- Emily White
..and to the rest of the team’s for all their hard work.
Somebody out there is reading this, and thinks that my stuff is worth voting for and so I have been short listed for a Computer Weekly blog award. Thanks very much whoever you are!
If anyone out there is of the same enlightened opinion please vote for me in the programming and technical blogs category. It only takes a couple of seconds to do and there is no registration involved.
I was in a customer meeting today with David Portas of Conchango who is hard at work doing battle with the new FileStream data type in SQL Server 2008. He is developing a solution where large filestream objects are being pushed around a site from central servers to roving laptops. Curiously, he’s using SQL Server 2008 Express on the laptops for a number of reasons:
- There is the same FileStream support in express as there is standard, enterprise etc.
- Although there is a 4gb database limit, this does not apply to the FileStream objects so these can be as large as required.
- SQL Express 2008 can run on a 64 bit edition of windows as a 64 bit application albeit only using one physical CPU and 1Gb RAM.
- SQL Server Express can take part in messaging from other editions of SQL Server, so David has his master database in enterprise edition and hopes to use service broker to push the FileStream objects down to the laptops.
- SQL Server Express is free and can be distributed for free as part of an ISV solution to customers
I could also add spatial support to this list but while there is also support for all the new 2008 data types in Express, in this solution would spatial analysis not be that much useful.
I cannot be more specific about the actual application at the moment but it does give you an indication of the sort of use to which SQL Server Express can be put in an enterprise solution.
Fuel injection is generally considered to be a good thing for cars, while IT professionals consider SQL injection to be a bad thing. The technique does not exploit weaknesses in the the database , but how it and the relevant web server are configured. In the Microsoft world a lot of work has been done about this based on the following principle included in its Trustworthy Computing Initiative:
- Secure by Design. All the Microsoft product teams go to a lot of trouble to make each new version secure and SQL Server and Windows Server are no exceptions. By the same token the bespoke applications you develop should follow these principles and part of this is ensure that there are no vulnerabilities in your code that a SQL injection attack could exploit. Help is at hand here in the shape of a special tool available here that does that analysis for you.
- Secure by Default. All new Microsoft products are shipped with what James T Kirk and James O’Neill refer to as shields up; all the security features are turned on and all of the connectivity features are turned off. You then have to explicitly turn on features open ports etc. to get started and you should only turn on what you need. To get advice on what to configure check this blog post on SQL Injection attacks from the Microsoft Security Vulnerability & Defense Team.
- Secure by Deployment. The process of keeping all of the installed products patched and up to date to meet new threats as they arise.
Stating the obvious here, but Microsoft applies this guidance internally and applies it to all the Microsoft.com sites including MSDN & TechNet which all use IIS and SQL Server. If these sites were compromised then this story would be all over the cloud in seconds, so the advice works, so please use it as well.
Now that hyper-v is released I though it would be good to go through a little glitch I found moving a Virtual PC vm (sometimes known as a VPC) to a hyper-v vm.
As I have mentioned before my trusty heavyweight laptop dual boots to Vista for day to day work, and Windows Server 2008 for demonstrations and so on. I don’t have SQL Server 208 installed natively on either OS and won’t until SQL Server 2008 is released to the public. Because of this all my stuff is in virtual machines (vm). I have Virtual PC installed in Vista while Windows Server 2008 has the Hyper-V role installed as I have a 64-bit laptop which specifically supports virtualization (set in the BIOS).
I needed to build a new vm with SQL Server 2008 rc0 and Visual Studio 2008 running on Windows Server 2008 and I did all this in Virtual PC for two reasons:
- I didn’t care that it would only be a 32bit environment (only hyper-V support 64-bit guest vm’s).
- I wanted to do admin stuff while it was building which I don’t have setup on the Windows Server partition for reasons I won’t bore you with.
The problem came when I wanted to migrate it to being a hyper-V machine, in that it was running like a snail and so I have now written these steps to overcome the issue:
Step 1. Like any good DBA make a backup by copying the vm so you have a fallback if things go wrong.
Step 2. Uninstall the Virtual PC virtual machine additions in the vm while running it in Virtual PC. Virtual machine additions enable the mouse to move smoothly from the host to the guest vm and allows you to share folders and improves performance by using some of the Hyper_V technology (if your hardware can support this). Hyper-V has similar integration components whihc are entirely different and use different key strokes to Virtual PC e.g. login in virtual PC is <Right- ALT> <Delete> while in Hyper-V it is <CTRL><Alt><End>.
Step 3. Fire up Windows Server 2008 and go into the hyper-V role. If you haven’t got Hyper-V setup then please go here for a step by step guide.
Step 4 Create a new VM and point to the .vhd (virtual hard disk) of the machine to be migrated.
step 5. fire it up in Hyper-V and check all is well in device manager as per this screen grab…
If you have a warning triangle here, then the performance will be atrocious i.e. similar to virtual PC without its virtual machine additions installed. To fix the problem launch msconfig and select advanced options from the boot tab and turn on detect HAL (Hardware Abstraction Layer)…
Once that is fixed, the performance is very nearly as good as a physical machine, but bear in mind that you should still do the same sort of disk layouts as you had with virtual PC i.e. put the vhd on a different physical disk than the physical OS if possible, even if this is a slower usb drive.
For those that don’t know him Michael Rys is the principal program manager lead for SQL Server’s Beyond Relational Data team and represents Microsoft on the W3C xml query working group. While he’s over in the UK next week he’s volunteered to speak at a special SQL Server community event on 30th June, which is very short notice so my apologies!
The details of the event are on the UK SQL Server Community site (on the events tab) and you’ll need to register as places will be limited:
Agenda
6:30pm - 6.45pm - Introduction, news and gossip.
Whats going on in the world,
6:45 - 9pm An Introduction to the New Spatial Data Support in SQL Server 2008 by Michael Rys
Spatial data is fundamental, modeling the locations and shapes of objects in the world to enable visual analysis of data. This session will be an introduction to working with spatial data in SQL Server. We will describe the different types of spatial data, give business examples of the use of spatial data, and provide an overview and demonstration of the new spatial features in SQL Server 2008 such as the spatial types, their methods, how to visualize and how to index it.
Location
Conchango Offices
36 Southwark Bridge Road
London
I look forward to seeing you there.
