Using MSDE to Build Scalable Solutions That Migrate to SQL Server
Summary: Discusses how to use the Microsoft® Data Engine (MSDE) to build scalable client/server database applications that easily migrate to Microsoft SQL ServerTM. (7 printed pages) Includes:
SQL Server Reliability
Setup and Installation
Using SQL Server or Visual Studio to Work with MSDE
Distribution and Deployment of MSDE Solutions
Upsizing to SQL Server
Scaling Back to Create Trial Versions
Microsoft Data Engine (MSDE) databases will operate under SQL Server without modification, making MSDE an ideal choice for helping you create applications that will scale from desktop to enterprise on a single code base. This article looks at MSDE Setup and installation, working with MSDE using the Microsoft SQL Server or Microsoft Visual Studio® development environments, and scaling your MSDE solution to the enterprise using SQL Server. You'll also learn about using MSDE to create easily downloadable trial or entry-level versions of applications that run on SQL Server.
Does this sound familiar: You've built a very cool application in Microsoft Visual Basic® or Microsoft Access and deployed it to a few people in the organization. It's one of those things that, once introduced to a small group, just catches fire. Everyone wants to use it. Soon, your boss or the president sees or hears about it, thinks it's great, and wants you to deploy it across the entire organization within just a few days. The new MSDE technology can help you make that situation a reality. The same code base that you run against MSDE can be immediately migrated over to SQL Server. Instant enterprise edition, no code changes required.
Using MSDE for building mobile and shared solutions provides the easiest migration path to SQL Server 7.0, so MSDE is the optimal data engine to use when you need to control your development costs. MSDE is fully compatible with all of the database connection technologies that SQL Server 7.0 supports, including Microsoft ActiveX® Data Objects (ADO), Data Access Objects (DAO), Open Database Connectivity (ODBC), and OLEDB. In fact, MSDE technology is based entirely on the SQL Server 7.0 code base. Like Microsoft Jet, MSDE is a technology, not a product. With MSDE, you can code for any feature the core SQL Server engine supports. Other components outside the core SQL Server engine—things like online analytical processing (OLAP) services and full text search—are not supported.
While SQL Server runs under Microsoft Windows NT® Server, MSDE runs under Windows NT 4.0 or later and Windows 95 or later and is available with Microsoft Office 2000 Premium and Developer editions as well as to licensed users of Visual Studio 6.0 Professional and Enterprise edition tools. Desktop and shared solutions built with either MSDE for Visual Studio 6.0 or Office 2000 Developer can be distributed royalty free to end users. That means that while you build to MSDE according to the SQL model, you can distribute what you build in the same way you would an application using Microsoft Jet (.mdb).
MSDE was tuned to deliver the same performance you'd get using the SQL Server enterprise engine at up to five concurrent users, and supports up to 2 gigabytes (GB) of data for any individual database. The storage limit is per database, not per server. A single MSDE server can support multiple MSDE databases, each containing up to the 2-GB limit. For more users, or when you need more data storage, you should use SQL Server or SQL Server Enterprise Edition for optimal performance at this higher level of scalability.
Databases created with MSDE are 100 percent compatible with—and support many of the features of—SQL Server 7.0, including most Transact-SQL commands. So, building solutions with MSDE for Visual Studio 6.0 allows you to offer the enterprise-class reliability of SQL Server.
With MSDE, you can take advantage of features like:
- Dynamic locking, which automatically chooses the optimal level of lock, such as row, key range page, or table, for all database operations. This delivers optimal performance without tuning requirements.
- Dynamic self-management, which allows the server to monitor and manage itself, freeing administrators from common daily operations.
- Merge replication, which lets users modify distributed copies of a database at different times online or offline. Later, this can be combined into a single uniform result.
MSDE also logs transactions, so if anything goes wrong during a write to an MSDE database—things like disk errors and network or power failures—MSDE will recover from its transaction log and revert to its last consistent state. This gives MSDE databases greater reliability than Jet databases, which don't log transactions.
Unlike SQL Server 7.0, MSDE does not support symmetrical multiprocessing (SMP) on Windows 95 or later, and can't be a replication publisher (although it can act as a replication subscriber) in a transactional replication environment. Also, note that replication of MSDE databases is only possible if SQL Server Client Access Licenses (CALs) are purchased. If you require replication publishing, you'll want to use SQL Server Desktop rather than MSDE. (A CAL is also required for SQL Server Desktop.)
MSDE runs as a service on the machine where it is installed. It only needs to be installed once as the MSDE service can host multiple databases from different applications. If any editions of SQL Server are installed on a machine, MSDE is not required, because all editions of SQL Server are capable of hosting any databases that rely on MSDE.
Requirements for Installation
The first step in installing MSDE is to review its operating requirements. A typical MSDE installation requires 40 megabytes (MB) of local hard drive space, and 64 MB of system memory is recommended. MSDE is for use with Windows 98, Windows 95, Windows NT 4.0 with Service Pack 4 or later installed, and Windows 2000. MSDE runs on both the Intel and Alpha versions of Windows.
Certain MSDE features are not available under Windows 95 or Windows 98. These include integrated security options, advanced performance features, and support for certain network platforms. Check the MSDE for Visual Studio 6.0 Technical FAQ at http://msdn.microsoft.com/vstudio/msde/techfaq.asp for further details on limitations when installing on these platforms.
MSDE runs on both Workstation and Server editions of Windows NT 4.0. Service Pack 4 is required to run MSDE on any Windows NT 4.0 platform. MSDE runs on all editions of Windows 2000.
Obtaining the MSDE for Visual Studio 6.0 Setup Program
MSDE is distributed in all versions of Office that include Access, such as Office 2000 Professional, Office 2000 Developer, and Office 2000 Premium, and it is also available to registered users of any Visual Studio 6.0 Professional or Enterprise edition tool (such as Visual Basic 6.0 Enterprise). MSDE for Visual Studio 6.0 is available for download and ordering at http://msdn.microsoft.com/vstudio/msde/techfaq.asp. Installation procedures vary depending on which distribution you receive.
MSDE for Visual Studio 6.0 is intended for inclusion with custom applications. Visual Studio developers are encouraged to:
- Develop SQL Server-compatible applications using the SQL Server Developer edition that is included as part of the MSDE for Visual Studio 6.0 package.
- Deploy the application with the MSDE redistributable engine that is available with MSDE for Visual Studio 6.0.
SQL Server Developer edition is available to Visual Studio 6.0 developers who receive MSDE for Visual Studio 6.0, and is a full copy of the SQL Server 7.0 product licensed for the sole use of developing and testing MSDE solutions (see the end user-license agreement for more details).
Installing MSDE from Office 2000
Although MSDE is not part of Microsoft Office 2000 or Microsoft Access 2000 stand-alone setup, you can install MSDE from the Office 2000 CD-ROM by running Setupsql.exe, located in the \Sql\x86\Setup folder. The Office 2000 version of MSDE also contains additional Help files that are located in the \Windows\Help folder, the SQL Server Enterprise Manager Help (entmgr.chm), and the Replication Wizard Help (replwiz.chm), which you can use with the SQL Server database utilities available from the Access Tools menu.
Once you install MSDE on your computer, on Microsoft Windows 95 or later, you may need to start the SQL Server Service Manager (double-click the MSSQLServer icon on the task bar and click Start/Continue). MSDE starts automatically on Windows NT 4.0 or later; you may want to automatically start MSDE on Windows 95 or later by selecting the Auto-Start service when OS starts check box on the SQL Server Service Manager window.
Access 2000 supports MSDE through new Access Project files. Projects in Access 2000 are different from linked databases. Project files allow for control of SQL Server and MSDE features that are not supported by linked tables in Jet databases. For SQL Server users, Office 2000 solutions using MSDE databases can be identified in SQL Server's Enterprise Manager as the product "SQL Server Office" in the SQL Server Properties window.
Installing MSDE for Visual Studio 6.0
Visual Studio 6.0 developers are encouraged to develop SQL Server-compatible applications using SQL Server Developer edition, and then use MSDE as part of the custom application distribution. MSDE for Visual Studio is installed through a command-line InstallShield installation program. This installation program is designed to allow deployment of MSDE solutions to customers and end-users. For example, consider a new, custom desktop or shared database solution created with Visual Basic 6.0 that uses MSDE. In order for the application to function, MSDE must be installed. In creating the installation program for the Visual Basic application, the developer includes an argument to execute the command-line MSDE setup. Then, during installation of the custom application, MSDE is installed silently, without requiring any additional steps.
For detailed information on MSDE for Visual Studio installations, see "How do I install the Microsoft Data Engine (MSDE) from the Command Line?" in the MSDE for Visual Studio 6.0 Technical FAQ at http://msdn.microsoft.com/vstudio/msde/techfaq.asp.
Chances are you already have what you need to begin working successfully with MSDE. Because it shares the same development environment as SQL Server and other Windows applications, there is no learning curve involved to begin working with MSDE.
Like Microsoft Jet, MSDE does not have its own user interface. Visual Studio 6.0 developers have the option of using SQL Server 7.0 Developer Edition, which is included on the MSDE for Visual Studio 6.0 CD-ROM, to create MSDE databases and database objects. For more information on using these tools, refer to the overview on Creating and Maintaining Databases in the SQL Server Programmer's Toolkit.
In addition to using the SQL Server 7.0 development environments, you can use both Access 2000 and the Enterprise Data Tools introduced in the Visual Studio 6.0 development system as the MSDE development environment. A complete Visual Basic MSDE installation sample project is available for download to help you create a new MSDE database using Visual Basic.
The quickest way for Visual Studio 6.0 developers to create a new database when distributing an MSDE solution is to first create the database using SQL Server Developer edition, detach the database, and then attach it to an MSDE server using SQL-DMO. For more information on moving database files between servers, see "How do I distribute database files with MSDE solutions?" in the MSDE Technical FAQ at http://msdn.microsoft.com/vstudio/msde/deploying.asp.
When deploying your apps, the quickest way to create a new database is to first create the database using SQL Server Developer edition, back up the database, and then restore it to an MSDE server using Transact-SQL. For more information on using backup and restoration to copy databases see the section Creating and Modifying a Table in the SQL Server Programmer's Toolkit.
When the time comes to migrate your MSDE application to SQL Server, you have several options. One is to simply install SQL Server on top of MSDE. You can also remotely administer MSDE by pointing your SQL Server box to the MSDE box and uploading the MSDE data to the SQL Server machine. Finally, you can detach the MSDE data file and reattach it on a SQL Server machine.
Here's another twist: You're a software vendor with a product that runs on SQL Server, and you want to offer an evaluation edition of the product that can be easily downloaded from the Web. Reducing your download to a manageable size can be a major battle and tie up valuable developer resources. However, when you use MSDE to create an introductory or trial or edition of a full product that ordinarily ships with SQL Server 7.0, you can quickly replace SQL Server with MSDE and only have to develop and support one version of your application.
For example, Telemate.Net Software recently introduced MSDE-based versions of its main product, Telemate.Net, an enterprise-level product that runs on SQL Server. They simply took the same code, scaled it back, and run it on MSDE.
How easy was it? "We had a very easy time with it," says John O'Reilly, Director of Development for Telemate.Net Software. "If you do it once, you're an expert," he says. "Telemate.Net has a fairly involved installation process, and I had it up and going on MSDE in a day," says Lance Smith, a Telemate.Net Software developer who worked on the project. "We didn't expect it to be that simple, but it really was."
If you're thinking about using MSDE to create a trial version of an existing SQL Server application, Smith recommends you keep in mind the need to develop an upgrade installation that will handle the upgrade path from MSDE to SQL Server. "The first time you install SQL Server over MSDE," Smith explains, "it simply converts MSDE to SQL Server, but none of the tools have been loaded. You need to run it a second time to install all the programs that are part of SQL Server."
Working with MSDE can be an excellent option when you want to build desktop or shared database solutions for which you foresee increases in system requirements, or when you want to offer a trial or entry-level version of a product that currently runs on SQL Server. Either way, MSDE is an inexpensive method for building client/server database applications with the highest level of scalability, and provides you with the easiest migration path to full SQL Server on the same code base.
- The MSDE Technical FAQ (http://msdn.microsoft.com/vstudio/msde/techfaq.asp) provides detailed technical information on installing, developing, and deploying applications with MSDE for Visual Studio 6.0. You'll also find information on creating MSDE databases and tables, and connecting to MSDE databases.
- Service Pack 1 for Microsoft SQL Server 7.0 and MSDE 1.0 (http://msdn.microsoft.com/vstudio/downloads/addins/msde/) includes several fixes along with support for Windows NT 4.0, Terminal Server Edition (recommended for all installations).
- Code Sample: Connecting to an MSDE database (http://msdn.microsoft.com/vstudio/downloads/addins/MSDE/condwnld.aspx). A Visual Basic 6.0 example application containing sample code that demonstrates how to connect and execute statements against an MSDE database.
- Code Sample: Installation Sample Project (http://msdn.microsoft.com/vstudio/downloads/addins/MSDE/instdwnld.aspx). This code sample is a Visual Basic 6.0 project that demonstrates how to programmatically control the MSDE for Visual Studio 6 command line setup and perform many operations required to install and deploy an MSDE application.