Upsizing to SQL Server

Microsoft seem to be pushing both Access and FoxPro users in the direction of using SQL Server as their back end data store. Access users are finding that the development tools are being downplayed in the newest versions 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 SQL Express.

Introduction

The job of upsizing a FoxPro or Access database to SQL Server is made easier if the database exists before you start the process. Creating a database is an easy job in the SQL Server Management console if you accept the default suggestions. These examples are taken from Microsoft SQL Server Management Studio Express. This provides a graphical interface to the management console which is very much easier to use 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 Registered Servers from the View menu.

If you see the Stopped icon against the name of the server then right-click on the server and select Start from the shortcut menu.	Stopped
You'll be asked whether you are sure that you want to start the service and then you'll see a dialog "Attempting to start the service..." with a progress bar which may run for about a minute. Once the service is running you'll see the running icon against its name and you can close the Registered Services window.	Running

You can 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 and connect the Object Explorer to the server you started in the previous step.

Connect

A new installation of SQL Server holds nothing but a set of system databases for its own internal use. We have to create a new database to hold our upsized 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 might not be the most appropriate or effective values for your particular database but the database will run with these defaults and you can tune them later. The only property that does need to be entered 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.