Wednesday, 28 January 2009

SQL Server Express installation

How things have changed, that what I was thinking when I finished the install of SQL Server Express edition.

But, as almost always, I've started with the end of the story.

A close relative is soon going to change roles at work. She's moving to a system administration position, where, among other tasks, she's going to have to deal with a lot of MS SQL server tasks.

I was therefore asked about the best way to learn "SQL" After some more questions it became clear to me that "SQL" was in fact Microsoft SQL Server. I've always been amazed at how well Microsoft has done in the marketing side, since for quite a lot of people, even technical ones, the word "SQL" means "Microsoft SQL Server", apparently ignoring all the rest of the relational databases out there.

Since I know too well that the SQL language is not that standard, my advice was to get some sort of limited free SQL Server edition and use that as a learning platform. You can probably learn any other RDBMS and translate most of your knowledge to MS SQL, but there will be always parts of the language, storage and indexing concepts that are specific to each product.

How wrong I was, not anything sort of pared down RDBMS. MS makes available for download the full SQL server software suite, including the multidimensional database engine. There are restrictions on its use, of course, and I must confess that I read them, understood them, confirmed that it was Ok to use for learning and then and forgot the details. Remember, you should read them to make sure you're not accidentally infringing. If you want to learn the ins and outs of the MS product this is the way to go, as nothing is missing from the commercial package.

I've always been very supportive of these kind of offerings. People get a chance to get marketable skills and vendors get a pool of people that at least is familiar with the software that can help paying customers get the most out of their purchased product. Before the big players made those kind of packages available, I've seen too many sales reps declaring "piracy" on a junior developer taking software home just to be able to learn more in his/her spare time.

Back to the story. I proceeded to download the package. At this point, I faced the reality I have been able to ignore for the last few years. Windows server product installations have become complex. Way more than they used to be. I had first to read the prerequisites and install and update the required OS components. After a couple of reboots, I was ready to execute the installation program.

But I (wrongly) assumed that installation was going to be more or less the same as it was before. In the past, to get a basic installation working it was just a matter of accepting the defaults and letting the installer do its thing. Oh well, and perhaps a reboot or two. The end result was probably not efficient or secure, but at least got you to point of having something that worked. However, this path of least resistance has been taken to the extreme by Microsoft, to the point of being the root of much of the problems Windows is famous for.

First, there are the security problems. In terms of security, Microsoft always in the past compromised safety in the name of usability. That philosophy bounced back to them in many dire ways. Users got used to the idea that they could change anything in their machines without restrictions and started to complain when Vista was released, as it was for them an anomaly to respond to UAC prompts. The lives of malware creators were much easier as in this default environment any user level action could trigger a system change without further permission.

Application installations also dumped by default into the hard disks tens of megabytes of code, templates, galleries and whatever else that were not really going to be used in 90% of the cases. This also created a competitive disadvantage since latest netbooks have limited disk capacity and thus cannot easily accommodate the standard Office installations.

Seems that Microsoft is trying to fix those problems. In MS SQL Server case, I was confronted with a lot of choices. The installation package wants you to specify exactly what you want to install, where and which user accounts should run the services. Which made me trying to get on with the most obvious answers without checking the documentation first. The installation program then went ahead, only to be stopped at the last stage with an error stating the user account I specified was not valid to run the service. Complaining to myself, I then read the documentation and realized that I had not given the correct access rights to the user account.

Ok, I thought, let's tweak the service startup parameters and change the account for the SQL Service. Ooops, since the initialization scripts of the database were not executed this instance cannot be started, regardless of the user you choose. Ok, let's run again the installation. No way, you've already an installation of SQL server in the machine and the install program rejects overwriting it. Ok, let's remove the instance that cannot start. No way, the instance cannot be removed without the service being started at least once first, which cannot happen until you the installation executes the installation scripts. Which cannot happen until the instance can be started, which cannot happen until....

I found a way (and perhaps it's not the best, I'm no expert on database installation procedures) of this chicken and egg problem by using the uninstall program icon. This program does not need your database instances to be in an usable state and just wipes all the installation from the machine. After doing that, I ran again the install program taking care this time to specify the correct user account with the correct access rights and everything went fine.

Moral of the story? Microsoft listened to criticisms. They tried to make their products more secure by default and less bloated by default. By doing that, they've sacrificed the ease and convenience that were once sales arguments for their products. I guess that this is no longer relevant for them, giving that they have a market dominand position in a lot of markets, databases however not being one of them.

This also gives good arguments in favor of a standard for application packaging. In the last few years, the only software installs that I've actually done myself have been Debian packages. They are self-contained, install automatically the dependent packages (no need for you to track and install separate updates or service packs) and offer secure reasonable defaults that you can change later. And best of all, no reboots. Unless, of course, you're upgrading your kernel.

If Microsoft could dictate such standard, lives would be much simpler for everybody. Unfortunately, Microsoft has different ways of updating across its own range of products, so this is probably never going to happen.