SQL Server Upsizing Wizard Preparation

Article
10/23/2006

Before you run the SQL Server Upsizing Wizard, you must prepare both the client and server sides.

Preparing the SQL Server Side

Before upsizing, you must ensure that you have necessary permissions on the server, estimate the size of your database, and check that the server has sufficient disk space. There are also special preparations for upsizing to multiple disks or devices.

Checking Free Disk Space

Make sure you have enough disk space on the server.

Caution If the SQL Server Upsizing Wizard runs out of disk space on the server, it halts, leaving a partial database and any devices it created on the server. You can remove devices, databases, and tables with the SQL Server Administration tool.

Setting Permissions on SQL Server Databases

In order to run the SQL Server Upsizing Wizard, you must have certain permissions on the SQL server to which you will be upsizing. The permissions you need depend on the tasks you want to accomplish.

To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.
To build a new database, you need CREATE DATABASE and SELECT permissions on the system tables in the master database.
To create new devices, you must be a system administrator.

For more information on granting server permissions, see your SQL Server documentation.

Estimating SQL Server Database and Device Size

When you create a new database, the SQL Server Upsizing Wizard asks you to select devices for your database and log. It also asks you to set the size of the database and your devices.

Estimating SQL Server Database Size

When SQL Server creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all this space is necessarily used by the database — the database size simply limits how large a database can grow before it runs out of space.

Note You can increase the size of a SQL Server database after it's created. For more information, see the ALTER DATABASE command in your SQL Server documentation.

To estimate the space needed for your database, calculate the total size of your Visual FoxPro .dbf files for the tables you want to upsize plus the rate at which your new SQL Server database will grow. In general, every megabyte of Visual FoxPro data requires at least 1.3 to 1.5 MB in the SQL Server.

If you have ample disk space on your server, multiply the size of your Visual FoxPro tables by two. This ensures that the SQL Server Upsizing Wizard has enough space to upsize your database and also leaves some room to grow. If you expect to add a lot of data to the database, increase the multiple.

Estimating SQL Server Device Sizes

All SQL Server databases and logs are placed on devices. A device is both a logical location at which to put databases and logs, and a physical file. When a device is created, SQL Server creates a file, thus reserving a set amount of disk space for its own use.

The SQL Server Upsizing Wizard displays the amount of free space available on the existing SQL Server devices. Select a device that has at least enough free space for your estimated database size.

If no existing devices have enough free space, you can use the SQL Server Upsizing Wizard to create a new device. New devices should be at least as big as your estimated database size. If possible, make the device larger than your database size, so you can expand your database later, or place other databases or logs on the same device.

Note Device size cannot be changed. Make sure you create sufficiently large devices.

Using Multiple SQL Server Disks or Devices

In most cases, the SQL Server Upsizing Wizard provides more than enough control over SQL Server devices. However, if your server has multiple disks or if you want to place a database or log on multiple devices, you might want to create devices before running the SQL Server Upsizing Wizard.

Servers with Multiple Physical Disks

If your server has more than one physical hard disk, you might want to place your database on one disk and the database log on a different disk. In the event of a disk failure, you'll be more likely to recover your database if the log and the database are stored on different physical disks.

The SQL Server Upsizing Wizard allows you to create new devices but only on one physical disk — the same disk as the Master database device.

To place a database and log on separate disks, make sure you have devices that are big enough on both disks, creating new devices if necessary. Then run the SQL Server Upsizing Wizard.

Placing Databases or Logs on Multiple Devices

SQL Server allows databases and logs to span several devices. However, the SQL Server Upsizing Wizard allows you to specify only one device for your database and one device for the log.

If you want to specify multiple devices for a database or log, make those devices (and no other devices) the default devices. Then run the SQL Server Upsizing Wizard and choose Default for the database or log device.

Note If the new SQL Server database or log sizes don't require using all the default devices, SQL Server uses only the devices necessary to accommodate the database or log.

Preparing the Client

Before upsizing, you must have access to a SQL Server through an ODBC data source or named connection. You must also have a Visual FoxPro database, which you should back up before running the SQL Server Upsizing Wizard.

Creating an ODBC Data Source or Named Connection

When you create a new remote database, you select an ODBC data source or named connection in your Visual FoxPro database that accesses the SQL Server to which you want to upsize. Because you can't proceed through the Upsizing Wizard until you select a named connection or data source, you should create the appropriate named connection or data source before you start the upsizing process.

For information on creating a named connection, see Creating Views. If you want to create an ODBC data source, run the ODBC Administrator. For information on setting up ODBC data sources, see Installing Visual FoxPro.

Backing Up Your Database

It's a good idea to create a backup copy of your database (.dbc, .dct, and .dcx files) before upsizing. While the SQL Server Upsizing Wizard doesn't modify .dbf files, it does operate on the .dbc directly by opening the .dbc as a table at times and indirectly by renaming tables and views when creating new remote views. If you back up your database, you can revert your database to its original pre-upsizing state by overwriting the upsized .dbc, .dct, and .dcx files with the original backup copies, which reverses the renaming and creation of new views.

Closing Tables

The SQL Server Upsizing Wizard attempts to exclusively open all the tables in the database to be upsized; if any tables are already open and shared, the wizard closes them and reopens them exclusively. Opening tables exclusively before upsizing protects against other users attempting to modify records in the tables you're exporting during data export. If any tables can't be opened exclusively, the SQL Server Upsizing Wizard displays a message; those tables are not available for upsizing.