Upsizing to SQL Server

Since release 2.0 of MS Access, an add-on tool has been available from Microsoft that can greatly simplify moving an Access database into the SQL Server environment. This tool is called the Upsizing Wizard. It is available from a variety of sources, including Microsoft Office 97 Professional and the Microsoft Developer Network. In this example, we'll use the version supplied with Office 97.

Upsizing Wizard consists of two tools. The first is the wizard itself, which takes a Microsoft Access database and creates an equivalent database on SQL Server—with the same table structure, data and most (but not all) of the attributes of the original Microsoft Access database.

The second tool supplied is the SQL Server Browser. This allows the developer to view, create, and edit SQL Server objects, including tables, views, defaults, rules, stored procedures and triggers. SQL Server Browser can be used to manage both a SQL Server database created by the Upsizing Wizard, and any existing SQL Server objects.

Upsizing Design Issues

Before we can upsize an Access database to SQL Server, there are several important design issues we need to consider. Ideally, we would design our database from the very beginning with Web deployment in mind. As we'll see, the design of a database optimized for access from the Internet, or on an intranet, is very different to that of a typical single-user database. If you have an existing database that was not built with Web access in mind, you're likely to have to redesign certain aspects of it to take advantage of the upsizing process. We'll look at these aspects first.

Design Tips from the Client Side

Throughout this book, we're looking at how to build Active Server Pages. Let's take a moment to recap on some of the things we should take into account when designing a Web page that accesses data using ADO. These can have an enormous impact on the performance of our site.

Use Recordset
objects of the static cursor type if the result set contains relatively few columns, doesn’t contain OLE
Object
or large Memo
fields and when you don’t need to update the server tables. If you are simply presenting data, and multi-directional strolling is not required, use the adOpenForwardOnly
cursor type. This makes a single pass through the request table to present the results, incurring a minimum overhead.
Minimize the number of items in server-populated combo boxes, list boxes and other selection controls. Use static cursor type Recordset
objects to populate these controls wherever possible. Don't let the selection lists get too big—keep in mind that you have to pump all this data across to the user's Web browser. Anyway, from a design perspective, a selection list with too many items quickly becomes unwieldy.
Adhere to server-based naming restrictions from the beginning. Upsizing Wizard can correct many common errors in this regard, but not all of them. It's best to adhere to SQL Server restrictions from the beginning, in order to assure a painless migration. SQL Server field names must be 30
characters or less. The first character must be a letter or the symbol @
. The remaining characters may be numbers, letters, or the symbols, $
, #
and _
. No spaces are allowed.
Make sure you have an ODBC data source defined before you start the upsizing process. Upsizing Wizard requires you to log into an SQL Server database. If you are creating a new database, you should make sure you have an ODBC data source for the Master database for the SQL Server to which you want to upsize. If you are upsizing to an existing database, make sure you have an ODBC data source name for it set up first. You can create an ODBC data source name by running the ODBC Administrator—see Appendix G for details.

Design Tips from the Server Side

On SQL Server, we also need to take into account some basic design issues. Addressing these before we start the process can save us the difficulties associated with having to start again.

To make upsizing go as smoothly as possible, you should make sure that you have sufficient access permissions on the SQL Server you want to upsize to. The permissions you need will vary according to what you want to accomplish. At minimum, you must have CREATE
TABLE
permission. If you want to build a new database from scratch, you must have CREATE
DATABASE
permissions. Finally, if you want to create new devices, you must be a member of the Admin group.
Calculate how much disk space upsizing will require, by multiplying the size of your Microsoft Access database by two, and make sure you have enough free. This will ensure that Upsizing Wizard has enough space to upsize your database and leave it some room to grow as well. If you expect a lot of data to be added to the database, you should allow more space.
If your server has more than one physical hard disk, you may want to place your database on one disk, and the log for the database on a different disk. In the event of a disk failure, the likelihood of recovering will be much greater.