Installing MSSQL 2008 Express

For an overview of the issues to do with installing and updating MSSQL in general, see Installing MSSQL Express Versions. This post covers downloading and installing MSSSL 2008 Express.

MSSQL 2008 is currently at R2 Service Pack 2 (SP2).
Just to keep things nice and confusing, R2 is a new release of SQL Server, not quite a new version, but a major upgrade (MSSQL 2005 is version 9.0, MSSQL 2008 is version 10.0, MSSQL 2008 R2 is version 10.5), and the service packs start over for it as a release.

The release history is:

• MSSQL 2008
• MSSQL 2008 SP1
• MSSQL 2008 SP2
• MSSQL 2008 R2
• MSSQL 2008 R2 SP1
• MSSQL 2008 R2 SP2

Use the R2 SP2 files here.

Note that if you are installing extra features like ReportBuilder (there are a whole list), you'll need to go the R2 SP2 Feature Pack downloads page here rather than download from the product specific R2 pages (again, there is no indication on these product pages that they are outdated).

Use the x64 files if you have a 64 bit Operating System, or the x86 files otherwise.
The top two 'SQLEXPR' files are the bare bones server. The last two, starting in 'SQLEXPRWT', are 'with tools' meaning the server plus Management Studio and a few other development tools.  The two starting with 'SQLEXPRADV' are the whole kit and kaboodle - the server, management studio, business intelligence and reporting services tools.
The SQLEXPR32 file is the 32-bit bare bones server only.

We will follow the installation of the client tools and server version.

(1) Prerequisites - required before commencing installation
The installer will provide you with a download link to the appropriate update.
If you won't be on the internet, then you need to download the files and take them with you.

Installer 4.5 here
  There are Windows 6 (Vista), Windows XP and Server 2003 Versions, x86/x64

PowerShell 2.0 here
This download is not clear - the links are about halfway down the page: 'Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0)'
There are Server 2003, Server 2008, Vista , XP, x86/x64 versions

(2) Task Selection
Choose 'New Installation ...' unless you want to upgrade an existing server

(3) Setup files are installed, the welcome screen is shown, acceptance of Licensing Terms

(4) Feature Selection
Unless you are doing development or specifically using replication, you can leave out these options as shown.

For the core server only install, the page looks like this:

(5) Instance Configuration
I usually install as a named instance.
Usually when I upgrade major versions (2005, 2008, 2008 R2) I install the new server as a separate instance, to allow concurrent running of the systems for a time - hence I name the server to identify the version as shown here.
The old server instance can be removed when no longer needed.
However, you may want to keep the server name consistent and generic (eg. 'SQLExpress') so you don't need to reconfigure the server name on any client machines as it is upgraded through various versions.

(6) Server Configuration
The selection of local service or network service is not crucial.

(7) Windows Authentication Mode is regarded as more secure, but it requires a domain controller and proper network administration.  For legacy Applications which use SQL Authentication (username and password) or for small sites which don't have a dedicated server, Mixed Mode is appropriate.  The password being entered is automatically for the 'sa' (System Administrator) SQL user - you don't have to input the 'sa' username anywhere.

The current windows user should automatically be added to the Administrative users list.

(8) Installation Confirmation and Completion

SQL Server Express 2008 is now installed, but by default it can't be accessed from anywhere but the local computer. To enable remote access:

(1) Go to Start > Program Files > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager

(2) Expand 'SQL Server Network Configuration' and click on 'Protocols for ...' your server instance.  Check 'Shared Memory', 'Named Pipes' and 'TCP/IP'.

(3) You can go to 'SQL Server Services' and restart the SQL Server service, but I'd recommend rebooting the machine at this stage instead.

(4) Any other problems connecting from remote machines can be attributed to firewalls.  Check for firewalls on both the client and server (the Server is critical), and if need be the port to open is port 1433.
Note that the slammer worm uses this port, so what we want to do is enable access to the server machine via the port from the local network only; NOT from the internet.
Firewalls can cause some very strange intermittent behaviour - not just outright blocking - so disabling them completely is the first step in troubleshooting, then they can be progressively re-enabled with appropriate settings, checking for correct operation at each step.

Installing MSSQL 2005 Express

For an overview of the issues to do with installing and updating MSSQL in general, see Installing MSSQL Express Versions. This post covers downloading and installing MSSSL 2005 Express.

The MSSQL 2005 release history is:

• MSSQL 2005
• MSSQL 2005 SP1
• MSSQL 2005 SP2
• MSSQL 2005 SP3
• MSSQL 2005 SP4

It is currently at Service Pack 4 (SP4), downloadable here.

The top file is the core server only, the second file is server + client tools + BI/reporting, and the third is server + client tools.
MSSQL 2005 does not have a native 64 bit installation; but it can be run on 64 bit (it uses the Windows-on-Windows-64 emulation subsystem). The fourth file can be used for native 32-bit-only installs (it leaves out 64 bit compatibility and won't run on 64 bit systems at all).

We will follow the installation of the client tools and server version.

(1) Installing Setup files and welcome screen

 (2) System Configuration Check and Registration Information

(3) Selection of Features

Unless you are doing development or specifically using replication, you can leave out these options as shown.

(4) Windows Authentication Mode is regarded as more secure, but it requires a domain controller and proper network administration.  For legacy Applications which use SQL Authentication (username and password) or for small sites which don't have a dedicated server, Mixed Mode is appropriate.  The password being entered is automatically for the 'sa' (System Administrator) SQL user - you don't have to input the 'sa' username anywhere.

(5) Error Reporting - tick these if you feel inclined.

(6) Installation and Finished Screens

SQL Server Express 2005 is now installed, but by default it can't be accessed from anywhere but the local computer. We need to use the Surface Area Configuration Tool.

(1) Start the tool

(2) Choose 'Surface Area Configuration for Services and Connections'

(3) Make the shown changes and click 'Apply'

(4) You will then need to go to 'Database Engine' > 'Service', and click Restart, then go to 'SQL Server Browser' > 'Service' and click Restart.
This should be all that is needed, but experience shows that it is sometimes necessary to restart the machine, so to save time it's probably a good idea to do that rather than bother restarting the services as just mentioned.

(5) Any other problems connecting from remote machines can be attributed to firewalls.  Check for firewalls on both the client and server (the Server is critical), and if need be the port to open is port 1433.
Note that the slammer worm uses this port, so what we want to do is enable access to the server machine via the port from the local network only; NOT from the internet.

Installing MSSQL Express Versions

You would expect installing the Express version of MS SQL Server to be a straightforward task, but in that you'd be wrong.  Firstly, these are the different released versions of SQL Server Express (as of Oct 2011):

• MSSQL 2005
• MSSQL 2005 SP1
• MSSQL 2005 SP2
• MSSQL 2005 SP3
• MSSQL 2005 SP4

• MSSQL 2008
• MSSQL 2008 SP1
• MSSQL 2008 SP2
• MSSQL 2008 R2
• MSSQL 2008 R2 SP1
• MSSQL 2008 R2 SP2

These are all still available for download, with no warnings on the download page that versions are obsolete.

On top of this, there are three or four different bundles you can download, depending on the features you want - in both 32 (x86) and 64 bit (x64) versions.
The names of features change regularly between versions of MSSQL and sometime even between service packs.

What are your chances of getting the correct latest download ? Not good !

This post will provide links to the combinations you want and guide you through the installation process.

The Ins and Outs of MSSQL Updates

MSSQL service packs have the following behaviour

• updating from an older to newer service pack level essentially reinstalls MSSQL from scratch, so you really don't want to install an old version and update it. However, the advantage of this way of doing things is that you don't have to apply the service packs in order - you can skip straight to the latest
• as soon as you attach your database (mdf) files to the new server, they will be updated to the new server's version format and from that point on the files will not work with an earlier service pack or version of SQL
• for this reason you MUST get the correct server release (2000, 2005, 2008) AND SP level (0-4).  In addition, ALWAYS make a copy of important databases (backups or copies of properly detached mdf files) before installing a Service Pack.

MSSQL Features

There are essentially three features of MSSQL.

• the core server
• client/developer tools (MSSQL Management Studio or MSSMS and a command line query tool) -  these are used during development and/or troubleshooting
• Business Intelligentce (BI)/Anaysis Services and Reporting Services

There will be downloads for (a) core only (b) core + developer tools, (c) core + dev tools + BI/reporting

Checklist

So in summary, you need to decide these points before proceeding:

1. which version of SQL Server Express do you want (2005 or 2008) ?
2. are you installing on a 64 bit or 32 bit Operating System ? (note: this is not necessarily the same as the hardware, as you can run a 32 bit OS on a 64 bit processor)
3. what features do you require from the above list ?

The proceed to the post specific to your version

Macbook Pro as the Ultimate Dev/Media PC

I’ve had a Macbook Pro for about three years now, and I’m a definite convert. I run Windows for all my .NET/COM development work (MSSQL, Visual Studio, etc), but for personal media the Mac can’t be beat: rock solid on video capture and editing (iMovie), photo organisation (iPhoto), and GarageBand is a free multitrack recorder/sequencer with professional quality instrument samples. (And that’s just the start …)

Unfortunately, my old (and first ever) Macbook suddenly died (motherboard), and this experience highlighted the shortcomings of my backup strategy. I was prepared for a hard drive failure – had images and data backups of everything – but when you are forced to get a new machine, you often want to start fresh rather than restoring an old and cluttered OS from a different hardware set.

After spending many hours over the course of a week installing software and settings to my desktop PC to cover me while the old laptop was at the shop (7 day wait!), I knew there had to be a better way.

This is the recipe:

(1) Macbook Pro 15″ (bottom of the range): AU$2070

(2) Solid State Drive

I researched SSDs enough to know that cheap ones are worse than mechanical drives.  Then I ran across Jeff Atwood’s first post on SSDs (here  is the latest post, with links to the previous two, and a warning). All up, the Intel X25 series (approx AU$350) was the most reliable and appropriate (Crucial make a faster but less reliable model, but it really requires SATA 3 to do its thing).

AU$160 to get the Mac shop to install it to the new MBP without voiding the warranty. Holy cow, these things fly ! I’ll second all the things Jeff said. My ‘fast’ desktop just went to being slow as treacle by comparison.  I’m running every dev tool known to humans on the Windows VM and it starts up in about 20 seconds.

(3) Oracle VirtualBox

I was previously running Windows using Parallels for Mac using the Boot Camp partition (the physical disk). I had heard that running off a virtual disk was actually faster.  But the initial problem was that I needed to use my Desktop for a week between laptops, and it’s a PC.  I could shell out another $80 for Parallels for Windows, but a friend recommended VirtualBox, which runs under OSX, Windows AND Linux.  And is free. What’s not to love ?

So I installed Virtualbox on the Desktop (under Windows XP), fired up an XP image and started installing software. After upgrading the RAM to 6Gb, I realised that XP wasn’t going to recognise most of that (the 3Gb 32 bit limit … doh!).  But since the action was happening on VMs now, I grabbed a copy of 64 bit Ubuntu, and in about 40 minutes was installed and running windows on VirtualBox happily under Ubuntu with my full 6Gb quota of RAM.

So VirtualBox does not have all the creature comforts of Parallels, but frankly I had found most of that to be window dressing anyway, and had been thru upgrade hell each time I tried to update to the latest Parallels.  VirtualBox is stable, fast, and I can use the exact same VM image on Mac, PC or Linux in the time it takes me to copy the 35Gb VM image.  Machine dies ?  Hard drive dies ? No problem.

(4) Dropbox

Having been using Mozy for a while, and having a free Dropbox account (2Gb) I realised that Dropbox provides effectively the same backup service as Mozy/Carbonite without all the drawbacks. $99 a year will get you 100Gb of space – more than enough for non-media storage (with a few shared photo folders for the extended family).

So I now install Dropbox with the Dropbox folder on the root of the drive, and just store everything in there. Real-time, secure, offsite, web-available, remote-synced backup.

As an aside, when setting up the VMs it pays to use separate virtual disks for data and OS – just create and attach two disk images. That way you can back up the VM once a month or so to capture your latest program installations (by simply copying the image file), but have all your data on a separate drive backed up live with Dropbox or the backup solution of your choice.

There you have it.  The ultimate work/media machine, fast as anything, development PC fully virtual, fully backed up.  If the machine or drive dies, you can be up and running on a clone of your dev PC VM on an alternate machine within about half an hour, depending on how much data you need to pull down from Dropbox to get the ball rolling.

Clonezilla Live: Relocating a Partition

When my old Dev desktop machine slowly died, mostly I had reinstalled the software I needed to my laptop, but there were a few legacy products with complex and touchy configurations that I really would rather not have to deal with again. The easiest way around this was to restore the image I had of its OS partition to a VM, for occasional use in maintaining these legacy apps.
I had used Clonezilla Live to create the images, and duly fired up a VM, created a virtual disk, booted off the CDROM and attempted to recover the image to the new virtual disk.
It turns out that Clonezilla is a little fussy about where it restores its images to: it won't by default restore them to a partition other the one it backed up from.
For example, it backed up from IDE drive C: (/dev/hda1), but all new SATA drives and virtual disks have the first partition at /dev/sda1.
No problem; when clonezilla detects this it automatically tries to rewrite the image to a new temporary image to suit the new location. Unfortunately, this attempt fails when it runs out of memory and the restore is aborted (33Gb image).

Not being a *nix guy, I emailed Spiros Georgaras, the creator of the Clonezilla-SysRescCD, who seemed to be one of the only people out there writing helpful HOWTOs on the topic of Clonezilla.
I'm posting his reply here to help other folks who got to this particular dead end.

> [ME]
> You say in the docs:
>
> Recent versions of *Clonezilla Live* partly support restoring to a different location, so the *reloc-img*
> script is obsolete, and has been removed. *Clonezilla Live* now supports:
>     * Relocation of a disk image (restoring a whole disk)
>     * Relocation of a partition image (restoring a partition)
>
> I'm trying to do just this (relocate a partition image), and Clonezilla Live keeps trying to make a temp copy and failing.
> It appears to be trying to copy to /tmp, but I'm not sure where that's mounted to.

[SPIROS]

/tmp is mounted as tmpfs which means it's like a RAM-disk. If there's not enough memory, this could be a reason for the failures you're facing. If this is the case you could stop Clonezilla program (exit to command prompt), and
sudo su -
umount /tmp
mount /dev/sdxY /tmp
where /dev/sdxY is an existing linux partition on any of your hard disks (not the one containing your image file) Then try to run CLonezilla again

Thanks Spiros !