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.
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
For more information on adding users and groups, see SQL Server Security Manager Help and the documentation of the system procedures
sp_addgroup in the Microsoft SQL Server Transact-SQL Reference.
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.
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.
Schedule regular backups of your database, so you can restore your database from this backup copy in the event of a serious problem.
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.
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 How to: Create Parameterized 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 Mapping Visual FoxPro Databases to SQL Server Databases.
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 Enhancing Applications Using SQL Pass-Through Technology.
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.
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.
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 . For more information about Transact-SQL functions, see your SQL Server documentation.
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 multiuser 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 Managing Conflicts When Updating Data.