Hands on: SQL Server Express on the Lan

As networks become all-pervasive, more and more applications are being released that share data via an SQL database. The fact that you can get free versions of Microsoft’s SQL Server product (SQL Server Express), and other database engines is further fuelling this trend. However, getting SQL Server Express to work over a home or small-business network isn’t always plain sailing.

Express by name
For those unfamiliar with it, SQL Server Express is based on the same code as the full Microsoft database engine, except that it can only use one processor, 1GB of memory and handle databases up to 4GB in size.

It can be both downloaded and distributed free of charge and was originally known as the Microsoft SQL Server Desktop Engine (MSDE) before being renamed SQL Server Express with the release of SQL Server 2005 –­ unsurprisingly, in 2005. A new SQL Server 2008 version is now also available.

The software is designed to be used in two ways. First, by developers who need to write and test database applications and second, because it can be distributed freely, by vendors of third-party applications who can use it to bundle a modest database server with their code at no extra cost.

Note, however, that I’m not going to tell you how to write, build or distribute database applications, or fine tune the way the software works. I’ll leave that to Mark Whitehorn in his Databases column. My aim is to identify and address some of the common pitfalls encountered when using it over a home or small-business Lan.

The examples I’ll be using are for SQL Server Express 2005, as it’s the release I get asked to help fix networking problems with most often.

Installing SQL Express
A lot of the problems I have to sort out can be avoided if more care is taken during setup. The SP2 version can be obtained by going to the Microsoft website where you’ll find links to both this and the later 2008 implementations. The 2005 download is a manageable 53.5MB in size with a separate download also available for the SQL Server Express Toolkit, which includes a graphical database management tool (SQL Server 2005 Management Studio Express).

Although referred to as a server SQL Express can be installed and run on Windows desktops as well as servers. All you need is Windows 2000 or later and at least 512MB of Ram. However, if using a PC remember the network connection limits associated with desktop versions of Windows. With XP Pro it’s 10 concurrent sessions; on XP Home it’s just five.