SQL Server Upsizing Process Completion

Article
10/23/2006

You can now take additional steps, both on your server and in your Visual FoxPro application, to ensure your application and data are secure and functioning properly.

You can also use the information in this section when you build an application from remote views rather than by upsizing. Regardless of how you created remote tables, you take certain steps to ensure the server and client are prepared to work together in your client/server application.

SQL Server Steps

You can complete the upsizing process on your server by:

Making sure the tables you want to edit from Visual FoxPro are updatable.
Setting permissions on the database so that users can access the objects they need.
Protecting your work by making your new database recoverable, in case it's damaged or lost.

Adding Unique Indexes for Updatability

A remote table should have a unique index to be updatable in Visual FoxPro. The SQL Server Upsizing Wizard can export an existing unique index, but it doesn't create one where none exists. Make sure that tables you want to edit from Visual FoxPro are updatable.

Setting Permissions

The new SQL Server database and its objects receive a set of default permissions from the SQL Server. Set permissions on the remote database so that your users have access to the objects they need.

Database Logon Permissions

The default permissions of a new database make it accessible only to system administrators and the database owner.

You can add new users and groups by using the SQL Server Security Manager or the system procedures sp_adduser and sp_addgroup.

For more information on adding users and groups, see SQL Server Security Manager Help and the documentation of the system procedures sp_adduser and sp_addgroup in the Microsoft SQL Server Transact-SQL Reference.

Object Permissions

All objects created by the Visual FoxPro-to-SQL Server Upsizing Wizard, including tables, triggers, and defaults, are accessible initially only to the database owner and system administrators. This is true whether you upsized to a new or existing database. If you overwrite existing objects, you also overwrite all object permissions.

Ensuring Recoverability

Protect your work by making your new database recoverable in case it's damaged or lost.

Dumping the Master Database

When a database is created on a SQL Server, new records are added to the system tables in the Master database. Dumping the Master database also provides you with a backup copy, including all the latest changes.

Scheduling Backups

Schedule regular backups of your database, so you can restore your database from this backup copy in the event of a serious problem.

Device Mirroring

Mirroring a device continuously duplicates the information from one SQL Server device to another. In the event that one device fails, the other contains an up-to-date copy of all transactions.

If you anticipate that many changes will be made to a database between backups and you can't afford to lose those changes, consider device mirroring. Device mirroring is most effective when the devices are located on separate disks, as both devices might be lost if they're on the same disk and the disk fails.

Visual FoxPro Client Steps

Once you've transferred objects from Visual FoxPro to a SQL Server, you probably need to modify code in the original Visual FoxPro database so that it functions properly with the new SQL Server database.

Optimizing Views

Views created by the SQL Server Upsizing Wizard aren't parameterized and therefore are not optimized. For most efficient processing, add parameters to views created by the SQL Server Upsizing Wizard to download just the data you need. For information on adding a parameter to a view, see Creating Views.

SQL Server doesn't support some Visual FoxPro functions. If the remote view created by the SQL Server Upsizing Wizard uses functions that couldn't be mapped to Transact-SQL functions, the view will not work. For more information on mapping Visual FoxPro expressions to Transact-SQL expressions, see Expression Mapping.

Creating Stored Procedures and Triggers

The SQL Server Upsizing Wizard doesn't upsize Visual FoxPro stored procedures and triggers. If you want to create SQL Server stored procedures or triggers, you can use Transact-SQL on the server or use SQL pass-through in Visual FoxPro. For more information on using Transact-SQL, see your SQL Server documentation. For information on using SQL pass-through, see Implementing A Client/Server Application.

Comparing Event Order

In Visual FoxPro, some events occur in a different order, depending on whether your application is using SQL Server data or Visual FoxPro data. These differences might require changes to your code.

Default Values

Visual FoxPro default field values appear when you begin editing a new record. Default values generated by SQL Server defaults appear only after a record has been inserted. You need to change any code that depends on having values before the record is committed, such as the code for lookups.

Validation Rules

In Visual FoxPro, field validation occurs when the focus leaves a field. When you edit SQL Server data in attached tables, triggers and rules aren't fired until you leave the record. You might need to modify any record validation rules that rely on field validation occurring when a field is exited.

Handling Unconverted Expressions

The upsizing report indicates whether each Visual FoxPro table validation rule, field validation rule, and default expression was successfully converted. If a default expression or validation rule was not translated, you should rewrite it in Transact-SQL.

You can also perform validation at the form level in Visual FoxPro. However, if server data is then modified without using a particular form, the validation will not be applied and invalid data might be entered.

For more information about expression conversion, see Expression Mapping. For more information about Transact-SQL functions, see your SQL Server documentation.

Record Locking

Visual FoxPro uses optimistic locking internally when accessing tables on a SQL server. Optimistic locking means that the row is locked only when the edited value is committed and the update process occurs — usually a very brief interval.

Optimistic locking is used rather than pessimistic locking on SQL Server because pessimistic locking on SQL Server is provided by page locking, potentially locking many records at a time. While page locking prevents other users from making changes to the same record you're editing, it can also prevent users from accessing many other records in the same (locked) page. Optimistic locking provides the best multi-user access for a Visual FoxPro client/server application.

You can optimize updates and control how update conflicts are handled with the SQL WhereType property. For more information on controlling update conflicts, see Creating Views.