SQL Server 2005 Express Edition Overview

 

Rajesh George

November 2005

Applies to:
   Microsoft SQL Server 2005 Express Edition
   Microsoft Visual Studio 2005

Summary: SQL Server Express Edition, a free product based on SQL Server 2005 technology, includes the unique Application XCopy feature, and networking and security that differ from other SQL Server 2005 editions. This article discusses these topics and the integration of SQL Server Express with Visual Studio 2005. We also compare this product with the existing Microsoft free databases like MSDE and Jet. (19 printed pages)

Contents

Introduction
   Target Scenarios
Key Features in SQL Server Express 2005 Express Edition
   Engine Specifications
   Tools Support
   Networking Support
   Data Access Support
   Security
   Replication Support
   SQL Service Broker
   User Instances
   Setup and Deployment
   Language Support
   Support Considerations
   Hardware/Software Requirements
   64-bit Support
   Features Not Present in SQL Server Express
Visual Studio Integration
Comparison with Other Free Microsoft Databases
   Comparison with MSDE
   Comparison with Jet
Conclusion

Introduction

SQL Server Express is a free and easy-to-use database product that is based on SQL Server 2005 technology. It is designed to provide a database platform that offers superior ease of use, enabling fast deployments for its target scenarios. The ease of use starts with a simple and robust graphical user interface (GUI) setup that guides the user through the installation process. The GUI tools that come for free with SQL Server Express include SQL Server Management Studio Express Edition (Technical Preview version available at launch), Surface Area Configuration Tool, and SQL Server Configuration Manager. These tools simplify the basic database operations. The design and development of database applications are made easier by the integration with Visual Studio projects. In addition, I introduce the ability to deploy database applications by moving them like typical Windows files. The servicing and patching are also simplified and automated.

SQL Server Express uses the same reliable and high-performance database engine as the other versions of SQL Server 2005. It also uses the same data access APIs such as ADO.NET, SQL Native Client, and T-SQL. In fact, it is differentiated from the rest of the SQL Server 2005 editions only by the following:

• Lack of enterprise features support
• Limited to one CPU
• One GB memory limit for the buffer pool
• Databases have a 4 GB maximum size

Features like Auto-Close and the ability to copy databases as files are enabled by default in SQL Server Express, while the high availability and business intelligence features are absent. It is very easy to "scale up" if that becomes necessary, as Express applications will work seamlessly with SQL Server 2005 Workgroup, Standard, or Enterprise editions. The Web download enables a free, fast, and convenient deployment.

This white paper covers the components and features unique to SQL Server Express, such as Application XCopy (User Instances), networking, and security. It also provides guidelines for common usage scenarios. In addition, I cover the ease of data application development using Microsoft Visual Studio 2005. This discussion is targeted at:

• Hobbyists and other nonprofessional developers
• Database developers, administrators, and operations specialists
• Business analysts
• Technical decision makers

Target Scenarios

SQL Server Express was developed with two distinct uses in mind. The first is as a server product, especially as a Web server or a database server. The second is as a local client data store where the application data access does not depend on the network. Ease of use and simplicity are key design goals.

The three main usage scenarios for SQL Server Express are:

• Nonprofessional developers building Web applications
• ISVs redistributing SQL Server Express as a low-end server or client data store
• Hobbyists building basic client/server applications

SQL Server Express provides an easy-to-use and reliable database platform that is "feature-rich" to cover these scenarios. Special consideration is given to the ease and reliability of setup and deployment to make it easy for ISVs to use and redistribute.

Key Features in SQL Server 2005 Express Edition

SQL Server Express uses the same database engine as the rest of SQL Server 2005, and all the programmatic features are the same. For additional information in these areas, see SQL Server 2005 Books Online. The features that are unique to SQL Server Express and/or have higher customer impact are described in detail below.

Engine Specifications

The SQL engine supports 1 CPU, 1 GB RAM, and a 4 GB database size. This mechanism permits easy differentiation from other SQL Server 2005 editions by having well defined cut-off points. Otherwise, there is no workload throttle and the engine performs as in other editions. There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database.

SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. Internally, the engine limits the number of user scheduler threads to 1 so that only 1 CPU is used at a time. Features such as parallel query execution are not supported because of the single CPU limit.

The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, and so on is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1 GB for the buffer pool. Address Windowing Extensions (AWE) or 3 GB data access is not supported or needed.

The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and the distributed Transaction Coordinator is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Programmers intending to use SQL Server Express are recommended to keep these changes in mind when designing their applications.

Multiple SQL Server 2005 Express installations can coexist on the same machine along with other installations of SQL Server 2000, SQL Server 2005, or Microsoft Desktop Engine (MSDE). In general, it is best that SQL Server 2000 instances be upgraded to Service Pack 4 (SP4). The maximum limit to the number of SQL Server Express instances is 16 on the same machine. These instances must be uniquely named for the purpose of identifying them.

SQL Server Express by default installs as a named instance called SQLEXPRESS. This particular instance may be shared among multiple applications and application vendors. We recommend that you use this instance unless your application has special configuration needs.

The APIs available to program against SQL Server Express are the same as those for SQL Server 2005, so that users have a seamless experience if they choose to move to other editions of SQL Server 2005. All of the new features in SQL Server 2005, such as common language runtime (CLR) integration, new data types such as VARCHAR(MAX) and XML, user-defined types, and user-defined aggregates are supported. Also, SQL Server Express databases can attach to SQL Server 2005, and applications programmed with a SQL Server Express instance will work equally well with a SQL Server 2005 instance. Replication and SQL Service Broker functionality is also available, and will be described in detail later.

Tools Support

SQL Server Express is designed to be easy to use, and the graphical user interface (GUI) tools make it easy even for database novices to use the basic database functionalities in SQL Server Express. A new GUI tool called SQL Server Management Studio Express Edition will be freely available as a separate Web download. SSMS-EE will allow easy database management and query analysis capabilities and will be freely redistributable.

SSMS-EE will support connections to SQL Server Express and other SQL Server 2005 editions, SQL Server 2000, and MSDE 2000. A simple connection dialog will guide the user through the selection of the instance and the authentication methods to be used. Both local and remote connections will be possible. Object Explorer will enumerate and display the common objects used, such as the instance, tables, stored process, and so on, in a hierarchical manner and will help the user visualize access to the database.

All the database management functionalities will be available by invoking the right-click context menu from Object Explorer. SSMS-EE features like creating and modifying databases, tables, views, logins, and users are identical to the full SQL Server Management Studio available in other editions. This allows you to immediately leverage the skills you learn with SSMS-EE once you upgrade to the full version of SSMS.

Many database users prefer to manage their servers using T-SQL, since this approach offers finer-grained control than using the graphical user interface. The Query Editor in SSMS-EE will allow users to develop and execute T-SQL statements and scripts. The Query Editor will have rich features such as keyword color-coding and a results pane that returns results in a data grid. The error messages, if any, will also be shown in the results pane. SSMS-EE supports all the query editor functionality of SSMS including graphical query plans.

Figure 1. Query Editor (click image to enlarge)

SSMS-EE will be distributed through a separate Web download. Users will simply click the executable to launch the setup. If SSMS is already installed, SSMS-EE will fail to install because it is unnecessary. Similarly, users will be able to uninstall SSMS-EE by re-launching the setup or using the Add or Remove Programs menu in Control Panel.

Some of the tools that ship with SQL Server Express include the SQL Computer Manager, SQL Command, and BCP. SQL Computer Manager is used for starting and stopping the SQL Server service, and for enabling and disabling network protocols. SQL Command is used for connecting and querying using the command line, while BCP is used for bulk copying data.

SQL Computer Manager allows administrators to configure basic service and network protocol configurations. This is the SQL Server 2005 equivalent of older tools like Server Network Utility, Client Network Utility, and Service Manager. It is not intended to adjust performance characteristics or operations of SQL Server.

In Computer Manager, there is a node for "Microsoft SQL Servers" under which all services, server network protocols, and client network protocols will exist. The services node lists all the available Express services and gives details like the name of the server instance, the service status, and the startup type. You can select a particular service and perform operations like start, stop, pause, or restart of the service. The Server Network Protocols node enumerates the list of protocols for each instance on the machine. Right-clicking the protocol (e.g., TCP) will allow you to enable or disable the protocol, or change its properties. Similar options are available in the client network protocols node. Please note that the Server Network Protocols node works directly on the protocol settings on the SQL Server instance, while the Client Network Protocols node deals with the protocol settings of clients like MDAC or SQL Native Client provider. The Client Network Protocols node also allows you to create an alias that is an alternate name for SQL Server, and can contain information such as the server name, protocol used, connection string, and encryption information.

SQL Command is an OLE DB version of the existing osql tool. It not only attempts to maintain functional compatibility with osql, but also includes support for new SQL Server 2005 data types. All command line options output information to standard output, except error messages that may appear when an operation failed. The SqlCmd -? command displays the syntax summary of sqlcmd switches.

SQL Computer Manager and SQL Command are the same for SQL Server Express as for the other editions of SQL Server 2005.

Networking Support

Only the shared memory connection type on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, there are the following options:

• Use the Surface Area Configuration tool to enable networking and enable and start the SQLBROWSER service.
• Use SQL Server Configuration Manager to enable relevant protocols and start SQL Browser. Figure 2 shows the usage of this tool to enable the networking protocols.
• Use DISABLENETWORKPROTOCOLS=0 in the setup command line, if you know in advance that networking support is needed.
• Use SMO-based scripting to enable the protocols.

SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. Since shared memory does not use it, this service is turned off in SQL Server Express by default. This means that the user will have to start this service so that network access can work.

Note   One interesting fact is that SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.

Figure 2. Using SQL Sever Configuration Manager to enable the protocols (click image to enlarge)

Data Access Support

SQL Server 2005 Express supports the same native and managed providers as the rest of SQL Server 2005. This has the huge advantage that an application written for SQL Server Express will work seamlessly with other SQL Server editions.

SQL Server 2005 Express supports ADO.NET for managed access. We recommend using the SqlClient data provider for developing new applications, since most of the application XCopy features are available only with SqlClient. The ADO.NET 2.0 data providers (available in Visual Studio 2005) support the new SQL Server data types like varchar(MAX) and XML, as well user-defined types.

Starting with SQL Server 2005, the logical sessions in the server are detached from the physical connections. Both the client and server transport layers are updated to provide multiplexing capabilities so that multiple logical sessions can go over a single physical connection. This enables clients to have multiple active result-sets (MARS) against the same connection. Note that MARS is not targeted at removing the need for multiple connections in general. MARS is off by default in SQL Server 2005, and enables you to interleave SQL operations. For example, you can operate on a result-set and also be able to execute statements against the database while processing it, without opening a new connection. MARS can effectively replace server cursors in many of these scenarios and is particularly useful when the data retrieval and update operations all happen within the same transaction.

SQL Server 2005 clients also support asynchronous Input Output (I/O) so that the application threads will no longer be blocked by data transfer operations, and the client application is as responsive as possible. In this model, the I/O calls will return immediately and the completion will be asynchronously notified to the application. This enables the application to continue with other processing and check for the I/O completion events at a later time.

The data access components in the SQL Server 2005 time frame will be divided into two parts: the MDAC stack that is part of the operating system, and the SQL Native Client provider giving SQL Server specific data access library for native data access. The SQL Native Client is targeted at SQL OLEDB, SQL ODBC, and ADO customers who are writing new applications or enhancing existing applications to take advantage of new SQL Server 2005 functionality.

SQL Native Client contains the SQL Server 2005 additions to the SQL OLE DB, SQL ODBC, SQL BCP, and SQL Networking Interface. The SQL Server 2005 features such as MARS, User Defined Types, XML data type, etc. are available via SQL Native Client only for C or C++ programmers. SQL Native Client is comprised of new components (new class IDs) that are separate and distinct from the existing components in MDAC. For example, the OLE DB Progid used is SQLNCLI; the ODBC Driver Name is SQL Native Client; and the Header file used is SQLNCLI.h.

SQL Native Client comes as a single dll (SQLNCLI.dll). Not being an operating system component means simplified servicing, and easier redistribution and deployment of applications. There will be an updated SQL Native Client release with each new release of SQL Server and the applications can explicitly change their configuration manifest to use a later version of this provider.

There are some interesting dynamics based on the interactions between MDAC providers and the SQL Native Client providers. For instance, MDAC 2.5, 2.7, or 2.8 cannot connect to SQL Server 2005 using Shared Memory. This affects any native application using SQL OLE DB or SQL ODBC and includes not only existing native applications, but also the managed OLE DB or ODBC applications since they use MDAC internally. Typically for SQL Server, if the shared memory connection fails, networking protocols such as TCP/IP are used. However, for SQL Server Express, since the networking is turned off by default, these applications would simply fail to connect. The workaround is to change the apps to use the SQL Native Client provider or enable networking Transmission Control Protocol (TCP) and start SQL Browser.

Security

For SQL Server Express, one of our goals is to provide secure defaults for the different components. For instance, the networking protocols such as TCP/IP and Named Pipes are turned off. SQL Browser service is not started unless the user explicitly asks for this in the setup command line. The SA or System Admin account is disabled by default if Windows Authentication is used. Normal users on the machine have almost no privileges on the SQL Server Express instance. A Local administrator on the server must explicitly grant relevant permissions for normal users so that they can use SQL functionality.

The SA login is a special login in SQL Server, and is a member of the system administrator (sysadmin) role. It is used predominantly in configurations that use the SQL authentication mode, and is not used in Windows authentication mode. SQL requires a strong SA password for security reasons, and during GUI installs and silent SQL authentication mode installs, the user must provide a strong SA password. However, for silent Windows authentication installs, the SA password is not a requirement. The reason is that when using Windows authentication mode, the silent SQL Server Express setup provides a random strong SA password if the password is not specified by the user. The setup also disables the SA account in this case, so that you must explicitly enable SA at a later stage using T-SQL if you wish to use it. This is done so that the ISVs do not have to provide the password when using Windows authentication, so that the mass deployment scenarios are not blocked. In future releases, this functionality may be extended to GUI Windows-based installs also.

Replication Support

Replication allows the user to maintain copies of data at multiple sites using a publisher-subscriber model with synchronization of the copies at user-defined intervals. SQL Server Express supports subscriptions to merge, snapshot, and transactional publications, but does not permit publications itself. Replication subscriptions in SQL Server Express are fully functional. However, since SQL Server Express does not ship SQL Agent, scheduling the subscriptions is more challenging. You can sync SQL Server Express subscriptions by the following methods:

• Programmatically sync using Replication Management Objects (RMO).
• Use Windows Sync Manager for scheduled sync.

SQL Service Broker

SQL Service Broker (SSB) is the new reliable messaging infrastructure in SQL Server 2005. The service programs can choose to communicate through peer-to-peer message exchange contracts called dialogs. This feature is accessible via extensions to the T-SQL language.

SQL Server Express can use Service Broker only in combination with other SQL Server 2005 editions. If SQL Server Express receives a Broker message from another Express instance, and if another SQL Server 2005 edition has not processed the message, then the message is dropped. So the message can originate from an Express instance and end up at one, but it must be routed through a non-Express instance if that is the case. You can check a Message Drop trace event that is accessible from the Profiler or use tracing stored procedures to track this type of occurrence. The error message associated with the dropped message includes verbiage to this effect: "This message has been dropped due to licensing restrictions."

Consider a scale-out sample scenario. The application has 100 SQL Server Express instances deployed on cash registers. They are connecting to a non-SQL Server Express server in the back end. This scenario works as long as the back-end server is involved in all the dialogs. The SQL Server Express instances cannot engage in successful dialogues with each other without going through the back end.

User Instances

User Instances are a new feature in SQL Server Express that provides the ability to treat databases like files. The local database can now be moved, copied, or e-mailed along with the application. At the new location, no additional configuration is needed to make it work. There are three main features that enable the Application User Instance support in SQL Server Express: the AttachDBFilename option in the connection string, the lack of a requirement to specify the logical database name, and the User Instance option.

There are some assumptions implicit in User Instance support. We assume that SQL Server Express with instance name SQLEXPRESS is already installed on the machine. Also this is a managed-stack-only solution, and you must use the .NET SQL Server Data Provider to develop XCopy-deployable applications. In other words, you cannot use SQL Native Client or MDAC to develop applications that can be used with User Instances.

Typically, the application developer would copy just the user database and the log file along with the application. However, in SQL Server there are some configuration entries present in a special system database called master. The features that rely on entries in master include SQL authentication (we advise using Windows authentication whenever possible). If your application relies on any entries in master, the application developer will have to make sure these configuration entries are replicated in the target system. One way to ensure this is to include an SMO, DMO, or T-SQL configuration script along with the application that runs when the application is installed on the target machine. For most applications running with Windows authentication, replicating information from the master database should not be an issue.

AttachDBFileName

You can specify a relative or absolute file path for the AttachDBFileName connection string entry. The specified database file is attached when the connection is opened and this database is used as the default database for that connection. If the database is already attached when AttachDBFileName is invoked, then attach does nothing. This key word supports a special string called |DataDirectory| that points at runtime to the data directory of the application where database files are stored. This special string should be at the beginning of the file path, works only against a local file system, and checks are done for \..\ syntax so that the file path is not higher than the directory pointed to by the substitution string.

There are certain variations in the usage of log files when using AttachDBFileName. The log file name must be of the format: <database-File-Name>_log.ldf, and there is no option to specify the log file name when using AttachDBFileName. For instance, if the database file name is myDb.mdf and is at location c:\myApp, then the log file name should be myDb_log.ldf. If SQL cannot find this file in the same directory as the database file, then a new log file will be created during attach. This means that we do not support user-defined log file names when using AttachDBFileName.

SQL Server supports using multiple data and log files for a database and these files can be distributed in multiple file groups. This is not supported in the AttachDBFileName syntax. Also the network share, HTTP paths, or Universal Naming Conventional (UNC) remote databases are not supported.

Logical database name

When the logical database name is not specified in the connection string, an automatic name is generated for the database that is being attached. The name generated is based on the relative file path of the .mdf file. For instance, if the file is at c:\myDocuments\Myapp\myDB.mdf, the logical database name will be based on the full path. If the file path is longer than 128 characters, then this function will use the existing path and a hash to generate the logical database name. This is new in SQL Server Express, since not specifying the database name in SQL Server 2000 would result in an error. The supported syntax includes database=; or initial catalog=; or the user can also omit them totally in the connection string.

This feature is useful when moving or copying the database on the same machine, since the file-path–based logical name is unique. If this feature was not present, there will be naming conflicts within SQL Server if the same logical name is used to open databases in two different directories. Application XCopy is also supported across machines.

Note that it is still possible to explicitly specify the logical database name using the keywords database or Initial Catalog. Users may want to explicitly specify the logical database name when using replication, SQL Service Broker, multiple-part names in T-SQL queries, or cross-database scenarios.

Auto-Close

Auto-Close existed in SQL 2000 and is enabled by default in SQL Server Express. This feature releases the file locks on the user databases when there are no active connections to it. Thus, the database is ready to be moved or copied after the application that uses it is closed.

However, from the user's perspective, Auto-Close does not always work, especially during unexpected client termination or when using a connection pool. For unexpected client terminations, the connections are cleaned up eventually by the time-out mechanism. When connection pooling is enabled, connections are not actually closed when the user invokes the connection close; they are returned to the pool instead. In order to close all connections, typically the application domain has to be shut down or all the open connections have to be closed. ADO.NET also contains functions like ClearPool for manual resets.

User Instances

User Instances allow a non-administrator to attach and administer SQL Server Express database. They do this by creating a private copy of SQL Server that runs in the security context of the user that opens the connection to a database. The user opening the connection becomes the service account for the User Instance and thus has full database system administrator permissions. These permissions allow a non-administrator Windows user to have database administrator permissions permitting the user to attach databases with AttachDBFilename. These permissions also allow the user to create any necessary database objects; making this mode ideal for developing database applications.

Setup and Deployment

SQL Server Express setup is a simplified version of the SQL Server 2005 process. It is robust, reliable, and supports both GUI and the silent install modes. Only Windows Installer-based (.msi) technology is supported.

The SQL Server Express Web download is packaged as a single executable using Microsoft installation technology called SFXCab. You can download SQL Server Express from the Microsoft Download Center. Double-clicking the .exe automatically starts the install process.

The Visual Studio 2005 version of the .NET Framework is a prerequisite for the installation of SQL Server Express, and the setup produces an error if the correct version is not found on the machine. The setup has a component called System Configuration Check (SCC) that is run before the actual setup begins. This component verifies that the system meets the minimum criteria for installation; the criteria include minimum hardware requirements, minimum operating system requirements, pending reboot requirements, etc. Many users could run into the pending reboot requirement failure, which means that the user has to reboot the machine before the SQL Server Express installation can proceed. The main advantage of the SCC is that common installation errors due to bad machine configuration are identified early and appropriate warnings and error messages are given to the user. For instance, the SCC will give a warning but proceed with the install if only 256 MB memory is present on the machine. A sample screen shot of SCC is given in Figure 3 below.

Figure 3. System Configuration Check

Once the setup starts, you can see that the screens are simplified to be accessible to developers of all levels of experience. For instance, some complicated dialogs such as those handling collations and service accounts are hidden by default, using the Hide Advanced Configuration check box. If the user clears this option, then the advanced configuration dialogs are shown. Setup logging is turned on by default so that the user can easily figure out any installation failures by using the log files created in the \Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG directory.

SQL Server Express installs as a named instance called SQLEXPRESS by default and we recommend that applications use the shared SQLEXPRESS instance. Also by default, several features are turned off in SQL, so you must explicitly choose the components you want to install. The feature component tree screen is shown below, where you will manually check the items to be installed. The setup command line ADDLOCAL=All will select all the components for installation. The feature component tree GUI entries, as well as the corresponding command line options, are shown in Table 1 below.

Table 1. SQL Server Express Feature Components tree

Feature Components GUI	Feature Components Command Line Parameters: Provide a comma-delimited list with no spaces of the features to install after ADDLOCAL.
SQL Server Database Services	SQL_Engine
Data Files	SQLEngine_Data_Files
Replication	SQL_Replication
Client Components	Client_Components
SQL Command Line Tools	Server_tools
Connectivity Components	Connectivity
Software Development Kit	SDK

The GUI setup mode is recommended when you are installing the product yourself. Typically, you'll get SQL Server Express as a Web download from Microsoft site, or from the application CD.

We recommend using the silent setup mode when you are installing SQL Server Express as a part of an application install. In this mode, you can verify if the instance name SQLEXPRESS is present on the machine, and if not, install it silently. There are two easy ways of identifying SQL Server Express instances on the machine:

• ServerInstance in SQL Windows Management Instrumentation (WMI) Provider contains the SQL edition and version information.
• The Select SERVERPROPERTY("ENGINEEDITION") T-SQL command returns a value of 4 for SQL Server Express.

You can use a Setup bootstrap or Visual Studio "Click Once" technology to include the SQL Server Express Microsoft Windows Installer (MSI) or Web download exe, which is especially helpful for ISVs. You should not proceed with installing SQL Server Express if the SQLEXPRESS instance is already present. One important thing to keep in mind is that .msi chaining is not supported by Microsoft, and should not be used. Also, merge modules are not supported with SQL Server Express. If you have an existing installation of MSDE that does not appear in the Add or Remove Programs menu in Control Panel, you must perform a clean installation of SQL Server Express.

Some of the commonly used command line parameters are given below.

• SAPWD is used to provide the password for the SA account. This is predominantly used if SECURITYMODE=SQL is also used. SECURITYMODE=SQL enables Mixed mode authentication and it is important to provide a strong SA password. For Windows authentication, this parameter is not required.
• ADDLOCAL=ALL selects all the SQL Server Express components for installation.
• DISABLENETWORKPROTOCOLS is used to enable/disable networking protocols. By default the value is 1, which means that the networking is disabled by default. Use DISABLENETWORKPROTOCOLS=0 to enable networking in the instance
• /qn is used to install silently
• The INSTANCENAME parameter is used to specify the name of the named instance. The default value recommended is SQLEXPRESS.

Language Support

The SQL Server Express database engine supports all of the 12 languages that MSDE supports. The targeted languages are English, Brazilian Portuguese, Dutch, Swedish, Simplified Chinese, Traditional Chinese, French, German, Italian, Japanese, Korean, and Spanish.

Support Considerations

SQL Server Express is supported via newsgroups, as well as Web-based documentation and resources such as SQL Books Online and white papers.

Hardware/Software Requirements

For the hardware and software requirements for SQL Server Express, please refer to SQL Server Books Online.

64-bit Support

SQL Server Express will support Windows on Windows (WOW) on x64 platforms. WOW essentially means running a 32-bit Express on 64-bit machines. SQL Server Express will not install on IA64 machines.

Features Not Present in SQL Server Express

Some of the features available in other SQL Editions, but not in SQL Server Express, include the following:

• Availability features such as data mirroring, clustering, etc.
• Full-text search
• SQL Agent
• Reporting Services
• Business Intelligence Platform, such as Notification and Analysis Services
• SQL Management Studio, which is the new GUI tool replacing SQL 2000 Enterprise Manager

Note that Full Text Search and Reporting Services will be added to SQL Server Express Edition later in 2006.

Visual Studio Integration

SQL Server Express is installed with all editions of Visual Studio. Visual Studio installs SQL Server Express using the instance name SQLEXPRESS. In SQL Server Express, the applications rely on the SQLEXPRESS instance name. The goal of Visual Studio and SQL Server Express integration is to make database access with SQL Server Express as simple and easy as working with Jet. This is true not only for client applications but also for ASP.NET Web server scenarios.

For instance, a database object, which is a template SQL Server database, is introduced in the Visual Studio projects. This object is displayed when the user chooses the Add New Object option in a Visual Studio project. Once a database is added to the project, the connection management to the database happens automatically so that the user can click the database to see all the objects inside the database such as tables, stored procedures, etc. We offer the ability to drag and drop relevant tables directly to a form. In the case of Visual Web Developer, the GridView and SqlDataSource are created automatically without the user typing in a line of code. You can also automatically bind a control such as a text box to a database value by just dragging and dropping. All the connection string settings are stored in a central Web.config, so that the user has to go to a single location to modify.

Visual Studio uses the User Instance in SQL Server Express so SQL Server database files can be treated just like Windows files within the Visual Studio project. For instance, you can rename, delete, copy, or move the database (.mdf) file in the Data Directory just like Jet files. The associated log file (.ldf) goes through the same operation as the .mdf because Visual Studio maintains an association between these files. Visual Studio manages these operations and they succeed even while an ASP.NET page or process is running against the database. This means that the typical Visual Studio application developer need not worry about log files in a design and development scenario. However, during deployment, the log files must be transported along with database files.

Comparison with Other Free Microsoft Databases

Comparison with MSDE

SQL Server Express replaces Microsoft SQL Server Desktop Engine (MSDE) in SQL Server 2005 and provides numerous ease-of-use features that enable it to be used by a nonprofessional developer or hobbyist. MSDE is based on SQL 2000 technology and is recommended for use with Windows 9x platforms, while SQL Server Express is based on SQL Server 2005 technology. SQL Server Express has features such as Application XCopy, Robust Setup UI, CLR support, GUI tools, and Visual Studio Integration that are not present in MSDE. However, some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.

The use of merge modules for deployment has been a servicing problem for MSDE, and this functionality is not available in SQL Server Express. The workload throttle in MSDE was sometimes difficult to understand and use. In SQL Server Express, the throttle is removed and instead the engine uses CPU, RAM, and database size limitations to differentiate it from the other editions. The table below shows the comparison of these products.

Table 2. Comparison of MSDE with SQL Server Express

SQL Server Express 2005	MSDE 2000
User Instance support	Feature not present
DTS in separate download	DTS runtime present
Easy deployment because of no MDAC	MDAC is part of install
MSI only, good servicing story	MSI and MSM, servicing of MSM hard
Subscriber Replication for Transactional, Merge, and snapshot	Merge/snapshot publication supported in addition to replication subscription
Robust setup UI	Basic setup UI
No agent	Agent present
Supports Windows 2000 SP4, Windows XP SP1, and Windows 2003	Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003
CLR support	No CLR support
GUI tools available	No GUI tools
Database size limit: 4 GB	Database size limit: 2 GB
1 CPU, 1 GB RAM	2 CPU, 2 GB RAM
No throttle	Workload throttle enforced for 5 concurrent workloads
Deep integration with Visual Studio	Basic integration with Visual Studio

Comparison with Jet

SQL Server Express is based on the latest SQL Server 2005 technology, while Jet has been in service pack and maintenance stage for quite some time. All the latest and greatest features, such as CLR integration and XML support, are available only in SQL Server Express. The reliability and scalability story of SQL Server Express is also significantly better than Jet. Applications written to SQL Server Express can easily move to SQL Server Workgroup, Standard, or Enterprise editions, while Jet is more difficult to scale up. SQL Server Express also provides finer-grained security control over its database objects. With the Visual Studio 2005 integration and the User Instance feature, we are bringing the ease of use of Jet to SQL Server.

Conclusion

SQL Server Express is a significant Microsoft product release, since this product is designed specifically for nonprofessional developers, ISVs, and hobbyists. It is free, easy to use, packed with powerful features, and provides a seamless upgrade path to other editions of SQL Server. There are features such as User Instances that are unique to this SQL Server edition, and the installation and deployment is secure by default. It has big advantages over the existing free Microsoft databases, Jet and MSDE. The integration of SQL Server Express with Visual Studio 2005 also provides a simple database design and development experience.

© Microsoft Corporation. All rights reserved.