SQL Server Upsizing Wizard

The SQL Server Upsizing wizard creates a SQL Server database that duplicates, as much as possible, the functionality of Visual FoxPro database.

Note

For SQL Server versions 6.5 and higher, the wizard sets the compatibility level to 6.5. You may need to reset this level and make additional changes after upsizing.

You can create a local working model of your application using Visual FoxPro tables, views, and databases to represent data that eventually will be accessed on a remote server. When you are ready to move this prototype to its working location, you will upsize it. You can use the SQL Server Upsizing wizard to:

• Move local data to a remote server.

• Transform local base tables and local views into remote base tables and remote views.

• Migrate a local application to a client/server application.

Note

Upsizing can take a long time, depending on the size of your data, the amount of network traffic, and the number of concurrent demands being handled by your server. Large tables may require several hours to upsize.

Before you run the SQL Server Upsizing wizard, you must prepare both the client and server sides. You must have certain CREATE and SELECT permissions on the server and named connections on the client. For more information, see SQL Server Upsizing Wizard Preparation.

To access the SQL Server Upsizing wizard from the Tools menu, choose Wizards, and then click Upsizing.

Step 1 - Select Local Database

In the first step, you choose which local database you want to upsize.

• Database to upsize
  Lists the currently open databases. Select the database you want to upsize. If you do not have a database open, choose the Open button to select and open a database.

Step 2 - Select Data Source

In this step, you specify options to select the ODBC SQL Server data source or named connection you want to use.

• ODBC data sources
  Choose this option button to display SQL Server ODBC data sources. When selected, the adjoining list is titled Available data sources.

• Connections
  Choose this option button to display connections stored in the open database that connect to SQL Server data sources. When selected, the adjoining list is titled Existing connections.

• Available data sources
  Lists the ODBC data sources that connect to the SQL Server. This list is displayed when the ODBC data sources option button is selected.

• Existing connections
  This list is displayed when the Connections option button is selected. It lists the connections stored in the open database that connect to SQL Server data sources.

• All
  Use this check box to display all data sources that are not identified explicitly with the "SQL Server" keywords in the title. The words "SQL Server" must appear in the data source name for Visual FoxPro to identify the data source as a SQL Server data source. If your data source uses a third-party driver to connect to SQL Server, it might not be identified as a SQL Server data source.

  The list displayed depends upon whether you have the ODBC data sources or the Connections option button selected.

When you click Next, the wizard connects you to SQL Server and displays the SQL Server Login dialog box. If you used a named connection with a saved password, the wizard logs you on to the SQL Server you've selected without prompting you for ODBC login information.

Using a Named Connection

If you use a named connection to access your data source, the SQL Server Upsizing wizard associates the named connection with any remote views it creates during upsizing. If the connection definition includes a password, the Save Password With View option (later in Step 8) is selected automatically for you and disabled; this prevents the password from being removed from the connection definition.

Using a Data Source

If you use a data source name rather than a connection name to log on to the data source, and you choose to have the wizard create remote views, the SQL Server Upsizing wizard creates a named connection called "Upsize" (or "Upsize2," "Upsize3," and so on, if a connection definition exists with the suggested name).

Step 3 – Choose Tables

In this step, you can select the Visual FoxPro tables you want to export to SQL Server.

• Available tables
  Lists all the tables stored in the database you have chosen to upsize.

• Selected tables
  Lists the tables you've chosen to upsize into the database on the server.

  You must select at least one table, and you can choose the >> button to export all tables.

When you choose Next, the SQL Server Upsizing wizard reads the list of tables you've selected to upsize and tries to open those tables for exclusive use in the database you choose to upsize. Any tables that can't be opened exclusively won't be available to upsize. The tables are opened exclusively to prevent other users from changing the data in the tables during the upsizing process, which helps to ensure the accuracy of exported data. If any tables are already open and shared, the wizard closes them and then reopens them exclusively; this might cause you to lose any temporary relations established with the SET RELATION or SET SKIP commands.

Choosing Tables to Export

It's very important to choose the tables you export carefully, as this choice has a significant impact on the performance of your application. Exporting all tables might result in excessive (and slow) server queries. A well-designed client/server database usually consists of a mix of local and remote tables.

In general, keep tables, which change rarely or infrequently, in your local database, such as a table of state names and abbreviations. Export tables that change frequently and are accessed by many users, such as a table of orders.

The SQL Server Upsizing wizard exports the table's field names, data types, and data. For more information, see Planning Client/Server Applications and Optimizing Client/Server Performance.

Step 4 - Map Field Data Types

In this step, Visual FoxPro displays the default data type mappings used to convert local data to remote data, as tables are upsized to the server. You can choose to change these default mappings.

You cannot change the default mapping for a key field to a data type that prevents the field from being indexed. You can change the default mapping of a field that is part of an index key if you choose a data type that is indexable; however, you might want to change the data types for the other fields in the key to match. Visual FoxPro warns you if you change the default data type mapping for a field that is not a key field but is used in a Visual FoxPro index. If you make a mistake, click Default to reset the data types.

• Table
  Select the table whose fields you want to map from the list of all the tables you've chosen to upsize.

• Timestamp column
  Select this check box to add a timestamp column to the server version of the table. If you want to apply this to all tables, you can save time by selecting the All Tables check box.

• Identity column
  Select this check box to add an identity column to the server version of the table. If you want to apply this to all tables, you can save time by checking the All Tables check box.

• Default
  Choose this button to reset all table field settings for all fields in the selected table back to their defaults. This button in effect undoes any changes you have entered for a specific table earlier in Step 4.

• Field Name
  Lists the fields contained in the table you selected in the Table list box.

• FoxPro Type
  Lists the Visual FoxPro data type for the field.

• Server Type
  Lists the server data type to which Visual FoxPro data in this field will be mapped when the table is copied to the server. Click this field to display a drop-down list from which you can select a different server data type.

• Width
  Specifies the width of the field.

• Precision
  Specifies the decimal precision of the field, when applicable.

Creating Timestamp Columns

A SQL Server timestamp column contains a unique value, generated by SQL Server that is updated whenever the server record is updated. Using a timestamp field on an updatable remote table can increase performance and reliability.

If you update fields using the Visual FoxPro SQL WhereType values DB_KEYANDMODIFIED or DB_KEYANDUPDATABLE, Visual FoxPro must check all modified or all updatable fields to determine whether they were changed by another user. Because text or image fields can be many megabytes in size, comparing these fields for changes can be network-intensive and time-consuming, thereby reducing performance. Reliability also can be affected, because converting the value of a floating-point field between client and server might cause the value to appear to have changed when it has not.

When you add a timestamp field to a remote table and update using the SQL WhereType value DB_KEYANDTIMESTAMP, Visual FoxPro uses only the value in the timestamp field to determine whether a record has been changed before updating it. Because Visual FoxPro can compare the value in the timestamp field more quickly than it can evaluate the contexts of large text or image fields, you can increase performance on remote data. However, if you use the SQL WhereType value DB_KEYANDTIMESTAMP, any change in the remote record is recognized as an update conflict, whether or not the changed remote field is in the list of fields you've set as modifiable in your remote view.

If you prefer to update fields using the Visual FoxPro SQL WhereType values DB_KEYANDMODIFIED, you might improve performance by setting the remote view's Compare Memo property to false (.F.). When CompareMemo is set to false, Memo fields are removed from the list of remote view fields compared against the data in the remote server row.

Step 5 - Select Target Database

In this step, you can select the database on the data source server to which you want to copy tables.

After you have connected to a server and have chosen a data source, the tables you want to upsize, and the field data type mapping, you can use an existing database or create a new database as the destination for your upsized local database. If you chose a named connection earlier in Step 2, you can use the database named in the connection or create a new database as the destination.

• Available databases on ' database name '
  This list is displayed when you select the Existing option button to add Visual FoxPro tables to an existing SQL Server database. Select the database into which you want to copy Visual FoxPro tables when you upsize.

• New database name
  This text box is displayed when the New option button is selected. Enter a name for the new database you want to create on the remote server to which you are upsizing. The name can be up to 30 characters in length and can include letters, digits, and the symbols #, $, and _. Spaces are not allowed.

If you upsize to an existing database, the wizard jumps to Step 8.

If you use a version of Microsoft SQL Server later than 6.x, the wizard jumps to Step 8 whether you use an existing database or create a new one.

Step 6 - Set Database Properties

If you chose to create a new SQL Server database earlier in Step 5, then the wizard displays this step to help you to select the database device and size for your database. You can choose to create your new SQL Server database on an existing device, or create a new device.

Selecting an Existing Device

• Database device
  Displays all the devices on the SQL Server you logged on to, including default devices. Select a device with sufficient free space for your database.

• Size
  Displays the size in megabytes of the database device selected in the adjoining Database Device list box.

• Free Space
  Displays the amount of free space remaining on the database device selected in the Database Device list box.

• Database size
  Enter the amount of space, in megabytes, you want to allocate for the new database. The size of a new database must be at least two megabytes, because that is the minimum allowed by SQL Server.

Default Devices

If one or more devices have been set as default devices on your SQL Server, the SQL Server Upsizing wizard includes a Default entry in the list of available devices. The Default entry can represent more than one device. Choosing the default device does not ensure you'll have enough room to upsize your database. The SQL Server Upsizing wizard checks the devices specified as default to make sure there's enough room for the database.

Tip

To place your database on multiple devices, make those devices (and no others) default devices. When you run the SQL Server Upsizing wizard, select Default for your database.

For more information on setting the default status of devices, see your SQL Server documentation for a description of the sp_diskdefault system procedure.

Creating a New Device

If existing devices are too full, you might want to create a new device. You must be a system administrator to create new SQL Server devices.

To create a new SQL Server device

1. Select Create new device from the list of devices.

   If you are a system administrator, a dialog box appears. Otherwise, an error message appears.

2. Type a name for the new device.

   The device name must be 30 characters or less and consist of letters, digits, and the symbols #, $, or _. Spaces are not allowed.

3. Click OK.

   The new device name is added to the devices list box.

4. Type a device size.

   The device size must be at least two megabytes. The combined size of new devices cannot exceed available disk space.

If you are a system administrator, the SQLServerUpsizing wizard displays the amount of space on the server drive storing the Master SQL Server database.

Note

The size of a device cannot be changed after it has been set. Make sure you create sufficiently large devices.

The selected new device is created after you choose the Finish button. The SQL Server Upsizing wizard creates the new device in the same directory as the Master database device. If you add a new device to the list but don't select the new device as the database device, then the new device is not created.

The SQL Server Upsizing wizard can generate a report that includes the logical name, physical name, and other information about new devices you create.

Device Number Limitations

Every SQL Server device is given a number when it is created. However, the available pool of device numbers is limited. The default value is 10, although the number might be different on your server. The SQL Server Upsizing wizard searches for an available device number. If all device numbers are taken, you won't be able to create a new device.

Note

To increase the number of SQL Server device numbers available, see your SQL Server documentation for a description of the sp_configure system procedure.

Step 7 - Specify Log Properties

In this step, you can specify the size and device to store a transaction log. A transaction log is created for your database by SQL Server, and can be used to reconstruct the database in the event of a serious system problem.

• Log Device
  Displays a list of log devices on the remote server. Select the device you want to use for the transaction log.

  Ideally, a database and its corresponding log should be placed on devices that are on separate physical disks. These devices must be created before you start the SQL Server Upsizing wizard, as the wizard creates all new devices on the same physical disk — the disk where the master database device resides.

  If you have only one physical disk, you should place the database and its log on separate devices, so that you can use the SQL server DUMP TRANSACTION command.

• Size
  Displays the size in megabytes of the database device selected in the adjoining Log Device list box.

• Free Space
  Displays the amount of free space remaining on the log device selected in the Log Device list box.

• Log Size
  Enter the amount of space, in megabytes, you want to allocate for the transaction log. For more information on how to determine the log size you need, see Specifying Log Size later in this topic.

• Database Size
  Displays the amount of space allocated for the new database in Step 6.

Specifying Log Size

As a general guideline, you allocate 10 to 20 percent of the size of the new SQL Server database; this guideline is dependent upon a number of considerations that are discussed in your SQL Server documentation.

You can enter a value of 0 for the log size, which causes the wizard to place the log (a table called syslogs) on the same device as the database. When the log size is set to 0, the log consumes the minimum amount of space on the server but also fills up very quickly.

When you upsize to a new database, the SQL Server Upsizing wizard dumps the transaction log every time it fills up. The dump makes a backup copy of a database and its transaction log in a form that can be read in with LOAD DATABASE. For more information on the SQL Server DUMP TRANSACTION command and on estimating the amount of space to allocate for a transaction log, see your SQL Server documentation.

Note

You can increase the size of a log or move it to a new device. For more information, see your SQL Server documentation for a description of the ALTER DATABASE command and the sp_logdevice system procedure.

Step 8 - Set Upsizing Options

In this step, you can control how the SQL Server Upsizing wizard exports your tables. You also can specify the changes you want the SQL Server Upsizing wizard to make to the local database. You can create upsizing reports, redirect views to use remote data, create new remote views on tables that are upsized, and save passwords with views.

Specifying Table Attributes to Upsize

By default, the SQL Server Upsizing wizard exports a table's structure and its data. Along with field names and data types, you can also export:

• Indexes

• Defaults

• Relationships (referential integrity constraints)

• Validation rules

The SQL Server Upsizing wizard can export additional table properties and create timestamp columns on certain SQL Server tables. The SQL Server Upsizing wizard also can modify your Visual FoxPro database so your queries, forms, and reports use the data in your new SQL Server tables, rather than the data in the local Visual FoxPro database.

Visual FoxPro indexes and defaults become SQL Server indexes and defaults. If you choose to export validation rules, the SQLServer Upsizing wizard attempts to export field- and row-level validation rules to SQL Server, where they become stored procedures called from SQL Server triggers. Exported table relationships also become part of the triggers.

Tip

If you export table relationships, be sure to export indexes as well or you might experience mediocre performance.

Table Attributes to Upsize

• Indexes
  Select to upsize Visual FoxPro .cdx indexes.

• Defaults
  Select to upsize default values for table fields.

• Relationships
  Select to upsize relationships stored in the database you are upsizing.

• Validation rules
  Select to upsize field and table validation rules.

• Structure only, no data
  Select to upsize the empty table structure, without copying table data to the SQL Server data source.

• Use declarative referential integrity
  Enabled when upsizing to a SQL Server version 6.x database. Select to create SQL constraints to enforce referential integrity.

• Null Mapping
  Select the fields that can accept .NULL.

  This option helps to ensure that insert and update commands against remote data succeed. This option allows you to override the existing null setting fields in the Visual FoxPro tables being upsized. If you choose the General and Memo fields option, then all general and memo fields in your selected Visual FoxPro tables will be upsized with these fields set to allow nulls.

  There are differences in how Visual FoxPro SQL commands such as INSERT and UPDATE affect local and remote data depending on the null settings for fields. For example, if you have an INSERT command that does not include all the fields in a table, Visual FoxPro inserts blanks into the unspecified fields for local data. If the INSERT command is issued for a remote view (SQL Server data), then nulls are inserted into the fields not specified in the command. The command will fail if any one of these fields does not support nulls. It's fairly easy to write an INSERT command, which includes all the fields and values to be inserted. Note, however, that inserting content into a general field is a little more cumbersome.

Changes to Make Locally

• Create upsizing report
  Creates a series of reports documenting the results of the upsizing process.

• Redirect views to remote data
  Changes the definition of local views in the database you are upsizing to remote views that use the upsized server tables as base tables. Your queries, forms, and reports then use views on the data in the new SQL Server data source rather than the original Visual FoxPro data.

• Create remote views on tables
  When you upsize a local table, you need a remote view to access the upsized table on the remote server. The SQL Server Upsizing wizard can create new, remote views as you upsize. As the wizard creates new remote views, it renames all local versions of tables by adding the suffix "_local" to the table name.

  Note

  Creating remote views on tables as you upsize is not recommended, because these unoptimized views select all the data in the table rather than selecting only the information your application needs. The preferred method is to create parameterized views. You can either create a local parameterized view, which you then upsize by choosing Redirect views to remote data, or create a new parameterized remote view on the table after you've completed the upsizing process. For more information, see How to: Create Parameterized Views.

• Save password with views
  By default, you must enter your password and login ID when opening a remote view in a new session with Visual FoxPro. Select this option to store your password locally with the remote view definition in your database.

  Note

  This check box is checked automatically for you and disabled if you used a named connection earlier in Step 2 to access your data source and that named connection includes a password. This prevents the password from being removed from the connection definition.

Creating Upsizing Reports

When you select Create Upsizing Report, Visual FoxPro generates upsizing reports that document the tables, views, fields, indexes, and referential integrity constraints the SQL Server Upsizing wizard creates on SQL Server. The wizard places the reports in a new project, using the following report names:

• RptErrors

• RptField

• RptIndex

• RptRels

• RptTable

• RptViews

The upsizing reports include information about any devices and databases that are created, information about any errors encountered during the upsizing process, and a complete explanation of the way in which each Visual FoxPro object is mapped to a SQL Server object. You can view or print these reports after upsizing is complete.

Upsizing All Tables Used By a Local View

If you upsize all the tables used by a local view, the local view is renamed by adding the suffix "_local" to the existing local view name. The SQL Server Upsizing wizard creates a new view based on the SQL statement of the local view, substituting the remote table names for the local table names. The KeyField and other update properties of the view are preserved.

Upsizing Some of the Tables Used By a Local View

If you upsize only some of the tables used by a local view, the SQL Server Upsizing wizard does not rename the local views. Instead, it renames the Visual FoxPro tables you export with the suffix "_local." For example, if you export a table named "Employees," the table is renamed "Employees_local" in your database. The wizard then creates a remote view for each of the tables you exported. Each view selects all fields and all records from the remote table.

Note

Visual FoxPro handles local and remote data in different ways. The data access design of an application using local data may become very inefficient when applied as is to remote data.

Advanced Dialog Box

• Make Primary Key a Clustered Index
  By default, tables that have a primary key automatically get a clustered index created on SQL Server by the Upsizing wizard. This is because it is a SQL Server default if the [CLUSTERED | NONCLUSTERED] clause is not specified in the ALTER TABLE statement, which the wizard uses. Being that primary keys are often already in sequential order, a clustered index may not be needed. This option allows you to control whether a clustered index is created.

• Drop Local Tables
  If you choose to create remote views for your tables, the wizard will create a new set of remote views using the names of their corresponding tables (and rename the original tables). It is possible that you no longer need to use the local tables. This option allows you to drop the local tables from the database.

• Default Remote View Name
  If you choose to create remote views, you may want to control the naming of these tables. By default, the name used is that of the original table (and the original table is renamed). The following options allow you to control the naming of remote views.

  • Prefix   Specifies a character string to prepend to each of the newly created remote view names.

  • Suffix   Specifies a character string to add to the end each of the newly created remote view names.

  • None   (Same as local table name.) Defaults to using table name, and renames original table.

Step 9 -Finish

In this step, you can choose to:

• Upsize without generating SQL code.

• Only generate SQL code for upsizing.

• Upsize and generate SQL code.

• Upsize
  Select to begin creating databases and tables, as applicable, on the remote server.

• Save generated SQL
  Select to have the upsizing wizard generate the SQL script necessary to upsize your database and then stop without beginning to create databases and tables on your remote server.

  You can use this option to provide a SQL upsizing script that you then customize to meet the needs of your application. Once you've modified and saved the SQL script, you then run the script to complete the upsizing process. This two-step process provides tremendous flexibility, allowing you to use Visual FoxPro to generate the large majority of the code you'll need, yet providing a method for fine-tuning the upsized installation.

• Upsize and save generate SQL
  Select to upsize your database and save the SQL script generated by the wizard.

The first and third upsizing options are available only if you have CREATE TABLE permission on the server. If you choose either of the options that save generated SQL code, all the SQL code generated by the SQL Server Upsizing wizard is stored on your hard disk.

Note

Upsizing can take a long time, depending on the size of your data, the amount of network traffic, and the number of concurrent demands being handled by your server. Large tables can require several hours to export.

To upsize your database, select the upsizing option you want and choose Finish.

The SQL Server Upsizing wizard creates devices and databases if necessary, and begins exporting Visual FoxPro objects to SQL Server.

See Also

Tasks

How to: Resolve SQL Server Upsizing Wizard Errors

Concepts

How the SQL Server Upsizing Wizard Works

Other Resources

Wizards (Visual FoxPro)

Upsizing Visual FoxPro Databases