Is SQL Server Express Multiuser-Capable?

Is SQL Server Express Multiuser-Capable?
      by Michael Otey, [email protected]

One common misperception about SQL Server 2005 Express Edition is that it’s designed as a single-user database and it isn’t suitable for multiuser applications. I’m not sure where this idea comes from. Maybe it stems from the Express moniker, or perhaps it’s because some people equate "free" with less functionality or because SQL Server Express’s network connectivity settings are turned off by default. (Learn more by visiting "Express Essentials: Get Ready for SQL Server 2008 Express Edition").

Whatever the reasoning behind the misperception, the truth is that SQL Server Express can be used as a multiuser database. It’s a great product for small businesses and can compete with other products in the SQL Server family and with other open source databases. SQL Server Express uses the same core relational database engine that other SQL Server products use and shares with them the capability to support and secure multiple logins for each database. The only limitations SQL Server Express has are its support for only one CPU, its maximum RAM capacity of 1GB, and its maximum database size of 4GB. Even with these limitations, using SQL Server Express is a great way to build a fully functioning multiuser database.

SQL Server Express’s relationship to other members of the Visual Studio Express family is in name only. The Visual Studio Express products are intended for the same user base (i.e., home users, hobbyists, and student developers), but after November 2006 the Visual Studio Express products will no longer be free. SQL Server Express is designed with a broader goal. SQL Server Express is a robust product that can be bundled with commercial products, custom ISV applications, and, yes, even small-business databases.

Out of the box, SQL Server Express's network connectivity settings aren't enabled. The reasoning behind this configuration is simple: If users were to use SQL Server Express in a standalone installation with the network settings enabled, malicious outside users would be able to access the PC--this is how the SQL Slammer worm made such a big impact on Microsoft Data Engine (MSDE) installations. Many MSDE systems that were servicing standalone applications didn’t download the latest Microsoft bug fixes, which left them open to network connections and allowed the worm to gain access. SQL Server Express prevents this type of attack by leaving the network connectivity settings off by default. Enabling the network settings is easy; you select Microsoft SQL Server 2005, then Configuration Tools from the Start menu. Select SQL Server Surface Area Configuration to launch the SQL Server Surface Area Configuration tool. Select Surface Area Configuration for Services and Connections, use the Remote Connections option, and click Local and remote connections. This process makes SQL Server Express network-enabled and capable of servicing multiple network users.

Make no mistake; you can use SQL Server Express to create a multiuser database. And because the product is free, it provides tremendous value for small businesses looking for an affordable product to build a database.

Creating Tables, Part 2

In my previous columns, I’ve provided some basic information about using SQL Server 2005 Express to build databases. To recap, you need to download SQL Server Express from Microsoft's download site, get it running, build a database, then build tables within the database. Tables are the core of any relational database; they group related data in the database. For example, you might create a customer table to track your company’s customers and a product table that contains all of the products you sell. Tables consist of columns, and each column has an associated data type that governs the type of data the column can store. In the March 6, 2006 Jump Start, I listed the different data types SQL Server Express provides out of the box. (You can also create your own user-defined data types, and I'll cover this topic in a future column.)

Let's take a look back at a sample table we created in the February 6, 2006 Jump Start. The following SQL Server Express statement creates a table named Media:

CREATE TABLE [dbo].[Media](
     [MediaID] [int] NOT NULL,
     [MediaType] [varchar](10) NOT NULL,
     [MediaTitle] [varchar](50) NOT NULL,
     [LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]

The purpose of this table is to store information about a media collection. The table has four columns (i.e., ID, type, title, and last updated). Let's take a look at the data types to use in each column. I'll use the MediaID column to assign a unique ID to each item in the media collection. MediaID uses the int data type, which means that this column stores only numbers; for example, you might want to assign a consecutive number for each item in this column. The NOT NULL keyword means that this column must always contain data.

The MediaType and MediaTitle columns will use the varchar data type, which means I'll enter alphanumeric data in these columns. Each MediaType entry can have up to 10 characters, and MediaTitle entries can have as many as 50 characters. Under MediaType, you might enter "DVD," and under MediaTitle you might enter "Mr. and Mrs. Smith."

The LastUpdated column uses the datetime data type and will contain date and time values to record when the data was last updated. The ON [PRIMARY] keyword is optional and indicates which file group the table is created on. Complex databases can be split into multiple files (each of which is called a "file group"), and the ON [PRIMARY] keyword lets you choose which file will store a table. Simple databases use only a single file named PRIMARY.

March SQL Server 2005 Express Edition CTP

If you’ve been using SQL Server 2005 Express Edition, you’ll definitely want to check out the latest SQL Server 2005 Community Technology Preview (CTP) announcement that Microsoft posted on March 20, 2006. The latest CTPs include: