Migrate to SQL Server 2005 Express Edition
Take advantage of new SQL Server 2005 features, GUI administration, and XCopy or ClickOnce deployment with SQL Express and Express Manager.
Technology Toolbox: VB.NET, ASP.NET, SQL Server Express beta 2 (June CTP or later), Visual Studio 2005 or Visual Basic Express (February CTP or later)
Microsoft's no-charge SQL Server Desktop Edition (MSDE) 1.0 and 2000 made it economical for small- to medium-sized businesses to replace file-system (ISAM) databasestypically dBASE, FoxPro, and Jetwith a more robust client/server architecture.
MSDE has been an effective weapon in Microsoft's combat with fewer-featured, "open-source" databases, such as MySQL, PostgreSQL, and, more recently, CA-Ingres. Microsoft intends to build upon its success in this vein by installing SQL Server 2005 Express Edition (SQLX) with most VS 2005 editions. In this article, I'll describe new SQLX features and how SQLX differs from SQL Server 2005 and MSDE 2000, both of which I've covered in previous VSM articles.
SQLX is a lightweight version of SQL Server 2005 that minimizes the installation footprint and thus reduces download time and resource consumption. A silent installation is the default for VS 2005 editions. Alternatively, you can download and install the latest SQLX beta or Community Technical Preview (CTP) version for VS 2005 and its three Express Editions: Visual Basic Express, Visual C# Express, and Visual Web Developer Express. VS 2005 Professional and Team System editions include SQL Server 2005 and Windows Server 2003 Developer editions.
Running the independent SQLX installer lets you configure the server during the installation process. Installation options include changing the instance name from the default SQLEXPRESS (not recommended), specifying mixed-mode (Windows and SQL Server) authentication, and selecting the default database collation. SQLX installs side-by-side with SQL Server 2000 and 2005, and MSDE 2000. You'll be able to download a redistributable SQLX version in Windows Installer (MSI) format when SQL Server 2005 releases to manufacturing. You can also expect usage and licensing policies for redistributable and embedded SQLX to be similar to those for MSDE 2000.
The most important new SQLX feature is the removal of MSDE's query governor, which restricts the server to simultaneous execution of five queries, at most. When more than five queries attempt to run simultaneously, the governor places query six or later in a pool for subsequent execution. SQLX's maximum database size is 4 GB, compared with MSDE's 2 GB limit, but there's no limit to the number of databases per instance. You can run up to 50 named SQLX instances on a single server; MSDE's limit is 16. Counterbalancing these advantages, SQLX limits you to a single processor and 1 GB of RAM for the buffer pool; MSDE 2000 handles two processors and up to 2 GB of RAM. SQLX ignores a second processor, if it's present. These specs make SQLX more than competitive with Sybase's "free" Express version of its Adaptive Server Enterprise database for Linux, which supports 2 GB of RAM and 5 GB total storage for databases.
In the real world, multiple users are likely to hit MSDE 2000's governor limit before SQLX attempts to consume more than 1 GB of server RAM, assuming that it's available. Another minor SQLX downside is that you must install the .NET Framework 2.0's 22 MB redistributable Dotnetfx.exe file before installing SQLX from a 37 MB self-extracting Windows installer file. This issue is mitigated by users' need to install Dotnetfx.exe version 2.0 before installing their first VS 2005 Windows Form client. MSDE installs SQL Server Agent, but SQLX doesn't. This means you must use Windows' Scheduled Task Wizard to automate database backups and other repetitive tasks. SQLX supports merge replication as a subscriber only; MSDE 2000 can act as merge-replication publisher. Finally, MSDE supports merge modules for customized installation, but SQLX doesn't.
SQLX Supports Most Yukon Features
SQLX supports most new SQL Server 2005 features, including CLR integration, the native XML data type and XQuery engine, auto-tuning for performance optimization, public key data encryption and decryption, the OLE DBbased sqlcmd.exe replacement for the osql.exe command-line tool, SQL Management Objects (SMO) and Replication Management Objects (RMO), dynamic management views, and user-schema separation. Microsoft's Paul Flessner announced at TechEd 2005 that SQLX will include SQL Server Reporting Services. SQLX doesn't install or support some enterprise features, such as full-text search; Integration, Notification or Analysis Services; data mining; clustering; mirroring; or HTTP endpoints for native Web services. Attempting to open an SQL Profiler connection to an SQLX instance fails with a "SQL Profiler does not support SQL Express" message. SQLX provides support for Service Broker as a client only; an SQL Server 2005 instance is required to pass messages to or between SQLX instances (download code that demonstrates the new SQL Server 2005 features).
SQLX installs an SQL Server Configuration Manager (SSCM) MMC snap-in to manage default SQL Server 2005 instances and named instances of SQLX. SSCM integrates with My Computer's Computer Management snap-in or opens in its own window from the Programs | Microsoft SQL Server 2005 | SQL Server Configuration Manager menu (see Figure 1). The SQL Server 2005 Services node has items for the SQL Server Browser service and each SQL Server 2000/2005 or SQLX instance. SQLX installs the SQL Browser service in manual startup mode and enables only the Shared Memory protocol, which restricts connections to clients running on the local machine. Start the SQL Browser service manually with the Services tool opened from Control Panel or SQL Server Configuration Manager to make the local SQLX instance visible to remote computers. If you want to enable clients on other machines to access a remote SQLX instance, enable the TCP/IP, Named Pipes, or both protocols under the SQL Server 2005 Network Configuration/Protocols for SQLEXPRESS node. You must start and stop the instance for new network settings to take effect.
The February CTP version of SQL Server 2005 and SQLX adds the SQL Server Surface Area Configuration (SAC) tool to minimize potential security risks presented by unneeded services. Choose Programs | SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration to open the SAC tool's main dialog. Click on the Surface Area Configuration for Services and Protocols link to open the page of the same name. This page presents an alternative method for specifying the instance's enabled protocols and enabling automatic startup of the SQL Browser service. Click on OK or Cancel to return the main dialog, and then click on the Surface Area Configuration for Features link. Select the CLR Integration link in the treeview, mark the Enable CLR Integration checkbox, enable and disable other features as necessary, and click on OK (see Figure 2). Marking a checkbox is easier than enabling CLR integration or other features with Sqlcmd.exe and T-SQL commands.
Unlike MSDE 1.0 and 2000, which have no Microsoft GUI management apps, you can download and install the SQL Express 2005 Manager (XM) front end from Microsoft's main SQLX page (see Additional Resources). Installing XM requires presence of a local SQLX or SQL Server 2005 instance, but you can use XM to manage SQL Server 2000 and MSDE 2000 instances also. Connecting XM to a remote SQLX instance running under Windows XP SP-2 or Windows Server 2003 SP-1 with the firewall enabled requires adding port 1433 and 1434 exceptions to allow SQL Server and SQL Server Browser traffic on the network.
Microsoft describes XM as a "lightweight database management tool built on top of the ? .NET Framework 2.0." The February CTP XM version has an opening connection splash screen to specify the instance nameusually .\SQLEXPRESS, where the period (.) prefix is a symbolic constant for (local). Typing (local)\SQLEXPRESS or, if you've enabled TCP/IP, localhost\SQLEXPRESS in the Server Instance textbox works as well. XM has two more dialogsOpen Query and Save File As QueryName. XM displays databases from the selected instance only; unlike SQL Server Management Studio and Enterprise Manager, you can't save and open multiple connections. The Tools menu has only one choice: Launch SQL Computer Manager. What you can do with XM is create or delete (drop) a database, write T-SQL statements in a query editor window, and execute the queries against the current instance's database. Rumors in Microsoft-authored newsgroup and blog messages indicate that a few additional tool-like featuressuch as automating database backups and restoresmight appear in a later CTP or the release version. In the meantime, "flyweight" is a better classification for XM's February CTP version.
The impetus for assigning a standard default instance name to new SQLX installations is to minimize duplication of SQLX instances on client machines, which in turn reduces resource consumption, management effort, and potential security lapses. Many applications that require MSDE 2000 install either a default instance or a named instance with a vendor-specific moniker and a starter database or two. Moving to a standardized client installation means that installers can detect the presence of the SQLEXPRESS instance and avoid installing another instance if .\SQLEXPRESS exists. The Windows Management Instrumentation (WMI) Provider for Computer Management is one option for determining if a specified instance is installed and running. This snippet tests the State property value of the MSSQL$SQLEXPRESS instance to determine whether it's installed and registered with WMI, as well as whether it has started:
Dim strWMIPath As String = _
'Dim strSvcName As String = _
"SqlService.ServiceName= " + _
Dim moSQLX As New _
Dim objState As Object = moSQLX("State")
If objState = Nothing Then
'Instance not installed or registered
'Install a new instance
ElseIf CUint(objState) < 4 then
'Instance installed, but stopped
'Start with a ServiceController
ElseIf CUint(objState) = 4 then
'Instance installed and started
SQL Server Books Online for the February CTP offers minimal WMI documentation, so the preceding code is based on trial-and-error tests with the WBEMtest.exe utility. (See the "How to: Access WMI Provider for Computer Management using WQL" help topic for instructions on using WBEMtext.exe.) For example, the objState (State property) values are based on those of the System.ServiceProcess.ServiceControllerStatus enumeration: Stopped = 1, StartPending = 2, StopPending = 3, Running = 4, and so on. The State property is a member of the SqlService class. You probably can use WMI to enable TCP/IP and Named Pipes protocols, and CLR integration for existing instances, but Microsoft must supply more SQL Server 2005 WMI documentation to reduce syntax guesswork.
SQLX installs by default with Windows-only authentication. To enable mixed-mode authentication, you must change the value of the \HKEY_LOCALMACHINE\ SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\LoginMode key from 1 to 2. Log on with your Windows sa account and execute these T-SQL instructions from sqlcmd.exe or XM to set the sa password for SQL Server authentication:
ALTER LOGIN sa WITH PASSWORD='whidbey'
ALTER LOGIN sa ENABLE;
Attach Files for XCopy and ClickOnce Deployment
One of Microsoft's goals for the VS Express editions and SQLX is to ease new users' migration from Jet ISAM files to client/server databases. Adding a SQL Server connection to a project by attaching a pair of MDF and LDF files is a feature that all VS.NET versions share. The most common location for permanently attached files is the default \Program Files\SQL Server\SQL.1\MSSQL\Data folder for the first SQL Server instance. VS 2005, VS Express, and SQLX simplify the process for Windows Forms projects by autogenerating connection strings for MDF database files that you add to the project folder or a subfolder.
To test the autogeneration process, which requires a running SQLX instance, open a new Windows form project, and add a DataFiles subfolder. Right-click on the subfolder node and choose Add Existing Item. Then navigate to a folder with an MDF file that isn't permanently attached to a SQL Server or SQLX instance. If you don't have an unattached MDF file, temporarily stop the instance to release the lock on the file. Then select Data Files from the Files of Type list, and double-click on the MDF file to copy it to the ?\DataFiles folder. Copying the file adds a connection string to the app.config file, generates a logical database name, and opens the Data Source Configuration Wizard dialog's Choose Your Database Objects page. The default DataSet name is MdfFilenameDataSet, which you can edit. Expand the Tables node, and select the table(s) to add to the DataSet (see Figure 3). Click on Finish to add the tables to the Data Sources window, create a new MdfFilename_log.ldf file, and add DataSet files to the subfolder.
This autogenerated connection string for the Northwnd.mdf sample database attaches it to the default SQLX instance while the client app is open:
Integrated Security=True;User Instance=True
The |DataDirectory| element is a placeholder for the project's absolute path, which enables XCopy deployment. The database files automatically detach from the server instance when all connections to the database close. Alternatively, you can use these T-SQL statements to attach and detach MDF files:
CREATE DATABASE [CreditCards] ON
( FILENAME = N'C:\Nwind\DataFiles\northwnd.mdf' ),
( FILENAME = N'C:\Nwind\DataFiles\northwnd_log.ldf' )
ALTER DATABASE [CreditCards] SET
SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname =
N'northwnd', @skipchecks = 'false',
The Data Sources window contains a selectable node for each table you add. The wizard adds nodes for related tables automatically. If you use the sample Northwnd.mdf database, adding the Customers, Orders, and Order Details tables creates a hierarchy of Customers, Orders, and Order Details objects in the Data Sources window (see Figure 4). Building the project in Debug mode copies the DataFiles subfolder and its database files to the ?\bin\debug\DataFiles folder, which Solution Explorer hides. Building the release version copies the MDF and LDF files to the ?\bin\release\DataFiles folder for XCopy deployment (see Figure 5).
ClickOnce deployment installs .NET Framework 2.0 and SQLX prerequisites, if necessary, and copies the ProjectName.exe, config, and manifest files from the deployment virtual directory's default \ProjectName_126.96.36.199 folder, as well as the MDF and LDF files from the ?\ProjectName_188.8.131.52\DataFiles folder. According to the "Visual Studio 2005 Product Line Overview" page, you need VS 2005 Standard Edition or higher to enable ClickOnce deployment (see Additional Resources). Unfortunately, as then-Microsoft Community Lead Program Manager for Visual Basic Robert Green stated in a November 23, 2004 message to the microsoft.private.whidbey.windowsforms.deployment newsgroup, "ClickOnce is in the October Community Technology Preview of VB Express.... And of course, it will work even better in Beta 2." The ProjectName Properties Windows' Publish page is alive and well in the VB Express February CTP release, so it remains to be seen if the same is true in later versions.
VS 2005 or Visual Web Developer Express Web sites add an App_Data (formerly Data) subfolder for attached files automatically when you create the project. However, these Web sites don't add a default Jet (Access) aspnetdb.mdb file to the folder, as the previous beta and CTP versions of Visual Studio did. (IIS doesn't serve the content of folders with the App_* prefix.) As of the February CTP, SQLX is the default data provider, and ADO.NET 2.0 Membership, Roles, Profile, and Personalization features use an aspnetdb.sql script to generate the default aspnetdb.mdf file in the ?\App_Data folder. ADO.NET 2.0 doesn't autogenerate the connection string for automatic attachment and detachment of other MDF files. You must specify the file to attach by adding a SqlDataSource, and then browse to or type the MDF filename in the Add Connection dialog's Attach a Database File textbox. The file becomes permanently attached if you specify an existing attached file for a Windows Form as the database name.
Download the Sample Databases and Projects
Installing SQL Server 2005 or SQLX doesn't add the Northwind, Pubs, or AdventureWorks sample databases; you must download and run their installation scripts separately (links at end of article). The Northwind and Pubs downloads have NORTHWND.mdf and PUBS.mdf files that you can copy from the C:\SQL Server 2000 Sample Databases folder. Running the instnwind.sql, instpubs.sql, and instawdb.sql scripts creates permanently attached Northwind, Pubs, and AdventureWorks databases. To use the auto-attach/detach feature, stop the SQLX instance with SCM to remove the locks on the northwnd.mdf, pubs.mdf, and AdventureWorks_Data.mdf files. Next, copy the MDF files from the \Program Files\SQL Server\MSSQL.1\MSSQL\Data folder to a new temporary location, such as a \SampleDatabases folder. After you restart the SQLX instance, drop the three sample databases to prevent conflicts when auto-attaching the MDF files. Copy the MDF files from \SampleDatabases to your Windows Form test project's \DataFiles or Web Form project's \App_Data folder.
This article's sample SQLExpress.sln project demonstrates a master-details-subdetails form whose data source is an auto-attached copy of Northwnd.mdf. Autogenerated code fills the NorthwindDataSet's three DataTables and saves changes to the attached database. You don't need to write a single line of code to create an operable client/server front end that can update multiple tables.
Microsoft "strongly urges" authors to migrate from Northwind to the AdventureWorks (AW) OLTP sample database in book and magazine article examples, but that's easier said than done. AW is an unnecessarily complex and over-normalized database whose Visio-generated database diagram requires printing on at least a two-foot by three-foot sheet to be readable (see Additional Resources). AW contains HumanResources, Person, Production, Purchasing, and Sales schemas, which encompass a total of 68 tables. Unlike Northwind, creating an updateable version of a simple AW master/detail table from the SalesOrderHeader and SalesOrderDetail tables requires substantial modification to the autogenerated DataSet designer code to handle update and insert errors for computed columns and inserts to rowguid columns. The sample AWOrdersAndDetails.sln project's connection string expects AdventureWorks_Data.mdf to be attached permanently to the SQLEXPRESS instance, so you must execute the instawdb.sql script from XM, SQL Server Management Studio, or the sqlcmd.exe command line before you run the project.
If your data-intensive products or projects don't require MSDE features that SQLX lacks, start planning a migration from MSDE 2000 to SQLX immediately. Even if you don't upgrade your front ends to VS 2005 and ADO.NET 2.0, SQLX promises improved performance and the opportunity to take advantage of new SQL Server 2005 features in your presentation, data-access, or both tiers.