If you're using ColdFusion with Access today, then it's likely you'll be using it with SQL Server tomorrow. Luckily, Microsoft has provided some tools that make upgrading to SQL Server a relatively easy process. You'll be surprised to learn how simple it is to upgrade and how easy it is to work with.

This article covers the basics of using these tools and helps you plan an easy database upgrade. You'll learn when and how to upgrade, and how to work with the Microsoft Access 2000 "front end" to SQL Server to make your transition as painless as possible.

Knowing When to Upgrade
There are lots of reasons to upgrade from Microsoft Access to SQL Server. The most important is a simple one: many of us are discovering that MS Access really isn't a stable platform when working with Cold-Fusion. I've found that sites become more stable and easier to work with as a result of the upgrade to SQL Server. And they can run faster too! Access isn't really meant to be a client/server database, which is, in effect, the way you use it when you connect it to the web with Cold-Fusion. Furthermore, Allaire and Microsoft point out that the database should not be used this way.

This may come as a surprise to ColdFusion sites that have used Access successfully for years. But those sites probably have limited amounts of traffic or don't do many online updates.

So how do you know when to upgrade? First and most important, if you find that your web site/web server is unstable, very often the trouble has to do with using Microsoft Access. Before pointing your finger at Access, however, be sure to check a few things. For instance, be sure you're using CF-LOCKs wherever necessary...especially around reading/writing session/application variables and any unthreaded CFX/COM objects.

Next, check that you've got the latest Microsoft Data Access Components (MDAC) from Microsoft's web site: www.microsoft.com/data. Follow Microsoft's directions if you do upgrade, as installing the MDAC can be tricky. Make sure you stop every service in your service's control panel or you'll end up with a zombie installation of the MDAC (e.g., from the land of the living dead)! If you're already using the latest MDAC and still experiencing inexplicable server crashes, check your ColdFusion server logs. See if you have any Windows NT 232 errors, which are often the result of problems caused by using MS Access. If you have lots of these, you'll know it's time to move to a better database.

What SQL Server Improves over Access
There are also "positive" reasons to upgrade. First, if you plan to have multiple servers sharing the same database, you should really upgrade. Although it's possible to share an MS Access database using UNCs, it's strongly discouraged for performance and security reasons. Further- more, with proper configuration, multiple servers in different physical locations can share the same SQL Server database. For instance, if your company has a ColdFusion server in Los Angeles and another one in Tokyo, both of them could use the same SQL Server as a data source.

If you're a developer working remotely on a site, you'll definitely find SQL Server the platform of choice. Instead of having to download the database every time you want to change or add a new field, you can access the table structures remotely and change whatever you like...and not even disrupt the web site while you're doing it. In other words, no more errors because the table is "exclusively locked" by a developer making a change to a table or even just looking at it in design view. Furthermore, SQL server uses multiple processors and makes better use of your server's RAM to improve performance of your database queries. Let's not forget about all the great features SQL Server has that MS Access doesn't even begin to address. For instance, SQL Server has the ability to use stored procedures and full text searching - both of which can dramatically improve the performance and functionality of your web site. There are all sorts of other improvements and enhancements, too.

SQL Server is a more complete implementation of the SQL standard. From an application development point of view, you can now handle multiple schemas and complex security requirements. You have additional data integrity features, including triggers. You can choose among various concurrency modes to enhance performance. And there are additional tools that work with SQL Server. If you cut your programming teeth on C, there's an Embedded C interface where you'll feel comfortable programming reports.

The Tools of the Upgrade
Of course, before you can begin, you'll have to set up SQL Server somewhere. Setting it up is beyond the scope of this article. Installing the software isn't all that difficult, but setting it up properly and securely is quite intricate. If you're using an ISP or web-hosting provider, they'll set it up for you and give you an IP address and a username and password to get to it. If it will be set up on a machine in your office, hopefully you already have a network administrator or someone experienced with SQL Server to help you get it going. Be sure and keep up with all of the service packs that come out for SQL Server. (Microsoft is currently shipping Service Pack 2.)

Assuming you already have SQL Server set up someplace, you'll need some sort of "client" software to administer it. On the most basic level, you could use CFQUERY through ColdFusion to issue commands to SQL Server. This is definitely a viable - if not the easiest - way to communicate with your database.

Enterprise Manager and Other SQL Server Tools
The second way to administer with the database is to install the SQL Server client software on the machine you'll be using to develop your web sites. This includes the SQL Server Enterprise Manager, Import and Export Data Wizard, and Query Analyzer. To install these, just take the SQL Server CD and put it in. The auto-menu will pop up and offer to install SQL Server for you (see Figure 1). When you choose to install it by clicking on "Install SQL Server 7.0 Components," you'll be given the option to install only the client software. Be sure and choose "local install" when prompted for that information. Also note that you need to reboot the computer after the install is complete, even though you won't be prompted to do so.

The tools will come in handy whether or not you choose to adopt this software as your main method of setting up your databases. For instance, the Query Analyzer is a real help in figuring out how to optimize your queries and where to add indexes to tune the performance of the database.

A common variant of this second method is to have your ISP install these tools remotely and use PC-anywhere to run them on a colocated machine.

Using MS Access Projects to Manage SQL Server
I find the third method the easiest and most convenient. You can use the Microsoft Access 2000 interface to access and manipulate SQL Server databases. Although the interface isn't exactly the same as when you're using it with an actual Microsoft Access database, it's similar enough that you'll be able to get your bearings quickly and get to work.

To get started, you need to create a new "project" in Access. To do so, choose FILE > NEW. You'll most likely want to choose Project (Existing Database) to gain access to a SQL Server database that's already in service (see Figure 2). It might surprise you the first time you do this that you'll be immediately prompted to save an .ADP file. This file is the "project" file. It will be a small file that simply contains pointers and preferences for accessing the SQL Server database. Just point it to a convenient directory and continue from there.

Next you'll need to fill out the "Data Link Properties" (see Figure 3). For #1, you'll typically enter either an IP address or a machine name to get to your SQL Server. This information will be provided by whoever set up your database; #2 will also depend on the way the database was set up. Once #1 and #2 are set up correctly, you'll be able to pull down the dialog box in #3 and select the database you want to work with. To verify that you've typed the correct information, you can click on the TEST CONNECTION button to see if it fails and to help you ascertain why. Reasons for the failure could be incorrect settings, bad passwords, or the way the security on SQL Server is set up. Another possibility is that Access is trying to use the wrong protocol to communicate with the database. To determine this, open up the "Client Network Utility," which may be found in your SQL Server or MSDE program group. Be sure the correct protocol is selected as the default. For instance, if you're supposed to be using TCP/IP, don't set the default network library to Named Pipes or some other protocol.

After you press the OK button, something similar to the old, familiar MS Access 2000 interface will open and present you with tables from your SQL Server database. A few prominent differences should be mentioned. First, you'll find that your QUERIES tab is replaced with "VIEWS." SQL Server's Views function along the same lines as Queries in MS Access. Next you'll notice that the Database diagrams option is built right into the menu. You'll find the diagram builder is a definite improvement over the one that you'd normally use with MS Access, but it's still lacking. Finally, you'll see a new item "Stored Procedures." If the idea of Stored Procedures is new to you, you'll be glad to know something about them. They can speed up your queries tremendously by keeping precompiled versions of them waiting to run on SQL Server. This way, you need only supply the values for a variable or two and POW! there's your data.

Using the New MSDE
("SQL Server Lite")
As a developer using SQL Server, you'll want to be aware of the "free" Microsoft Data Engine (MSDE). It's a new product that came out as a free add-on to Office 2000. You can think of the MSDE as "SQL Server 7.0 Lite," because it works like SQL Server but is licensed for less demanding use. Installing it will put a mini-SQL Server on your workstation.

Once you have it installed, you'll have the option of creating your databases using MSDE instead of the native Jet Engine that MS Access would normally default to. From a ColdFusion developer's point of view, choosing MSDE instead of the Jet Engine will provide you with an easy upgrade path to SQL Server. In other words, when you create a database using the MSDE, it's actually already in the native SQL Server format. Moving your database design to a full version of SQL Server is a relatively simple matter, and there's no danger of any compatibility problems in either the data or the SQL statements in your ColdFusion project.

MSDE is managed via MS Access through an Access project (ADP file). It does not come with its own version of SQL Server Enterprise manager, or the other SQL tools, although if you happen to have a copy of Enterprise Manager on your machine, you can use it with MSDE.

The place to find a copy of the MSDE installer is on your Microsoft Office 2000 or Microsoft Access 2000 CD. It may be installed on Windows 95 or 98 or Windows NT. To get started, run SQLSERVERSTP.EXE in the \SQL\ INSTALL\ SETUP directory. An install wizard will guide you through the process.

Once you've successfully installed MSDE, you'll find an extra icon in your system tray. It's a computer with a green arrow (if you see a red square instead, it means that for some reason the service hasn't started. You may need to reboot or simply right click on the icon and choose "MSSQLserver - Start"). To use the MSDE you'll need to set up an Access Project as described above.

Summary of Part I
We've covered reasons to upsize your Access database to SQL Server, the tools you'll use to do it, and the "free" MSDE "SQL Server Lite." In Part 2 we'll explain in detail how to copy data to SQL Server and how to troubleshoot your upsizing project.

AUTHORS' NOTE The Microsoft Data Engine (MSDE) basically is SQL Server. It contains all the underpinnings of SQL Server and uses an identical format. It's a great way to start out a database you'd like to eventually run on SQL Server. It comes free with MS Office/Access 2000.

• Figure 1
• Figure 2
• Figure 3