Upsizing to SQL Server

In recent years Microsoft seem to have been pushing both Access and FoxPro users in the direction of using SQL Server as their back end data store. Access develops are finding that the development tools are being downplayed in Access 2007 and 2010 as Microsoft make Access easier to use for the end user on the desktop. Visual FoxPro users know that we've got to migrate our databases to a new platform sometime in the next decade. Typical users of both databases are being tempted by Microsoft's free offer of SQL Server Express and these pages give a brief overview of what can be done.

Our own internal office administration tools are a mixture of FoxPro and Access databases which have grown over the years. We'll have a background project running here all year to see how easy it is to upsize our accounts, enquiry, marketing and bug-tracking databases to an SQL Express backend with a variety of front ends.

Introduction

The task of upsizing a FoxPro or Access database to SQL Server is made easier if the skeleton of the database exists in SQL Server before you start the process. We will create the data tables as we upsize the data but a SQL Server database is more than just a collection of tables holding our data. It has an administrative structure as well.

Creating a new database is an easy job in the SQL Server Management console if you accept the default suggestions.

The screen shots in these examples have been taken from Microsoft SQL Server Management Studio Express 2005. The Express range is a free tool which provides a graphical interface to the management console. This is very much easier as a starting point than the command line interface in the earlier versions of SQL Server.

Creating a new database

Start by making sure that your SQL Server software is running. Open the SQL Server Management Console and select the Registered Servers option from the View menu.

If you see the red dot of the "Stopped" icon against the name of the server then right-click on the server and select Start from the shortcut menu.

You'll then be asked whether you are sure that you want to start the service. If you confirm this operation then you'll see a dialog "Attempting to start the service...". This displays a progress bar which may run for about a minute. Once the service is running you'll see the triangle in the green "Running" icon and you can close the Registered Services window.

Close the Registered Servers window when you have the server running. Select Object Explorer from the View menu to see details of the databases available on this server.

Click the Connect icon in order to connect the Object Explorer to the server that was started in the previous step.

A new installation of SQL Server holds nothing but a set of system databases for its own internal use. We can ignore these. The next step in the upsizing is to create a new database to hold our data.

Right click on the Databases entry and select New Database... from the menu. This will open a dialog asking for properties of the new database. You can leave most of these at their default settings for now. These settings might not be the most appropriate or effective values for your particular database but we can be sure that the database will run with these defaults and you can always tune them later. The only property that does need to be changed from the default value is the database name.

Press the OK button and the wizard will create a new database as two files. I chose "upsize_sample" as the name for the new database so the data will be held in upsize_sample.mdf and the log information in upsize_sample_log.ldf.

By default, these will both be in a folder such as C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA. This option is typically not visible in the New Database dialog but you can scroll sideways on the Database Files grid and alter these paths if necessary.

Now that the database exists, the next stage in the preparation is to create an ODBC connection to the database.