The Sedna Upsizing Wizard is leaps and bounds better than the version previously shipped by Microsoft in any version of Visual FoxPro.

The Fox Team listened to the complaints from the Fox Community over the years about the wizard being deficient, with some fatal flaws, and recognized how it became outdated with the advent of SQL Server 2005. Sedna attempts to correct all of this and more.

Fox developers have long mocked the Upsizing Wizard as a weak attempt to assist VFP developers migrating VFP database containers to the SQL Server database platform. The Sedna release completely changes the perception and gives you a great tool to migrate data easily to SQL Server 2000 and SQL Server 2005.

I have observed the huge performance improvements the bulk XML insert brings to the process.

The first thing you should know about the Sedna Upsizing Wizard is it is more than a standard wizard. It has a standard wizard user interface to step you through the process of selecting the appropriate settings to migrate the database structures and optionally the data as one would expect. Sedna’s new architecture separates the wizard user interface from the upsizing engine so developers can programmatically control a migration. This means you can skip the user interface and take control of the process so you do not need to re-select your choices again as you step through the user interface each time you want to test a migration. In addition, the entire process is extendible in true VFP tradition.

This article will address each of the steps in the updated wizard, discuss the changes and improvements the Fox Team made to the process, and provide you an example of programmatically controlling the engine along with properties you set and methods you call to perform a database upsizing. I’ve based the changes discussed in this article on the Sedna October 2006 Community Tech Preview (CTP). Please note that at the time I’m writing this article, Microsoft has not finalized the features that they’ll include in the final release.

Upsizing Wizard UI

The Fox Team has revamped and modernized the Sedna Upsizing Wizard user interface (UI). The UI uses a standard wizard to step through choices and make appropriate selections for the migration. Previously, Visual FoxPro’s Upsizing Wizard included nine steps but the Sedna Upsizing Wizard reduces the number of steps to six steps, thereby streamlining the process and placing the steps in a more logical order. For example, in Sedna, Microsoft moved step five (selecting the target database) of Visual FoxPro’s Upsizing Wizard to step three because that seems more logical. Microsoft also consolidated steps three (choose tables) and four (map field data types) into a single step.

You can start the Upsizing Wizard in one of three ways. If you are running the CTP inside of VFP 9.0 (with or without any of the service packs) and try to run it from the Tools > Wizards menu you will still get the old wizard. Since the CTP is pre-beta, it does not overwrite the existing wizard with the CTP version. The final version of the Sedna components will have the new wizard connected to the menu.

You can also run the Upsizing Wizard in the Command window:

DO UpsizingWizard.app

The wizard accepts three optional parameters if you want to control how it runs: name and path of the source database container, the name of the target database in SQL Server, and a logical parameter indicating whether the target database is new or not. Using these parameters you can call the wizard programmatically as part of a migration process or from a custom developer menu.

You can also use the Data Explorer (Figure 1) to call the Upsizing Wizard from the shortcut menu for VFP Database connections. Starting the Upsizing Wizard this way selects the local database and creates the target database for you.

Figure 1: You can run the Upsizing Wizard directly from the Sedna version of the Data Explorer.

The Fox Team didn’t make significant changes to step one (select local database). You only upsize one database at a time. The listbox shows all open database containers. You can use the Open… button to open up another database and add it to the list. Select the VFP database you want to upsize and move on to step two.

Next you’ll select the destination database (Figure 2) to define the connection to SQL Server. In previous versions of the Upsizing Wizard you could use a predefined ODBC Data Source Name (DSN) or a VFP connection in the database container you are upsizing. The Sedna version still retains the VFP database connection option, but now allows you to select any VFP database, not just the one you are upsizing. You do not upsize two different databases. Rather, you use the connection in the second database container to connect to SQL Server. If you select the ODBC route you get the option of using a predefined DSN, a helpful connection string generation tool, or you can just code the connection string directly. These changes give you more options and you get to predefine the way you want the security handled for logins on one dialog. The flexibility set up for the destination database is very useful when testing out the upsizing process or you need to convert multiple databases residing on different servers.

Figure 2: There are many new choices when you select the destination database including the ability to pick different SQL Servers available on the network when you are building a connection string.

In step three you name the target database. You also indicate if the database already exists on the SQL Server or not. If it does, the Upsizing Wizard presents you with a drop down list of databases for the server selected in step two. If the database doesn’t exist, you enter in the name you want for the database. If you enter a name for a new database that already exists you will not be able to move to the next step until this is corrected.

One significant complaint addressed is the way character fields in VFP are upsized into SQL Server Varchar fields.

Step four (Figure 3) is where you indicate what tables and views to upsize and how you want to map the column data types and sizes. I really like having the selection of tables/views and the column details on one page. This consolidation saves time moving back and forth when you determine which tables you want to migrate. If you want to work with columns from a specific table, first select the table and the Upsizing Wizard will refresh the column grid with column details.

Figure 3: In step four you’ll make all the decisions with respect to what tables and views are upsized and how you want the columns to be on the SQL Server side.

Like in the old Upsizing Wizard, you still get the same settings for the column details with the option to change the data type for the server side from the default mapping to one that meets your needs. If you change the data type and the column is part of a relation, you get a warning to change the related columns in the other tables just like the older version. The Sedna Upsizing Wizard also lets you determine if the column can accept null values or not on the SQL Server side.

Step five (Figure 4) lets you set some upsizing options. Notable differences in the Senda Upsizing Wizard include the ability to select the location of the upsizing report output, the ability to determine if you want to use bulk inserts if you are uploading data (highly recommended), and if you want blank FoxPro dates to be upsized as a NULL or if you want it to work like the old upsizing wizard and have it change blank dates to January 1, 1900. (I’ll discuss more details on this feature in the Upsizing Improvements section later in this article).

Figure 4: Step five is where you determine the attributes to upsize, any changes to make locally, control the speed of the data load, how blank dates are handled, and if the report output is created and where it gets stored.

I have observed the huge performance improvements the bulk XML insert brings to the process. It is really impressive. Doug Hennig (http://doughennig.blogspot.com) blogged about this on July 7, 2006 in an entry titled SQLXMLBulkLoad Rocks! His testing in one case demonstrated the load taking 10% of the time it took to load with direct table inserts saving him over 100 minutes.

Step five of the Sedna Upsizing Wizard offers another new feature I recommend-changing the location of the output folder. Prior to the Sedna Upsizing Wizard, Microsoft had hard-coded the output folder as your current default folder plus a folder named “Upsize”. If you are like me, you are not always sure what your current folder is set to, or you’d like to know where the Upsizing Wizard put the output it created, so make sure to pick the folder. Otherwise, you may find out the wizard overwrote some output you wanted to retain.

Step six provides the final three choices before you perform the database upsizing. You can choose to upsize the database, just create scripts and documentation for the upsizing process, or do both. There is no change in the last step of the wizard.

Click [Finish] to begin the process. You’ll see a progress bar showing how far along you are and a message box when the wizard is finished. I am still amazed when I look into SQL Server and see the database with all the data migrated. I have written numerous data conversion programs over the years and know all the problems you can run into when running conversions. This generic wizard has not failed me in converting every VFP database I have thrown at it. The most impressive part is the fact I am testing it with a pre-beta version and it appears to be very stable. Naturally, your mileage might vary so I highly recommend that you test the Sedna Upsizing Wizard so the team at Microsoft can iron out as many of the hidden problems as possible before the final release.

Upsizing Improvements

Microsoft has introduced a few subtle changes to the way the Upsizing Wizard upsizes the data under the hood. I already mentioned the significant performance improvement of the upsizing based on how it uses bulk XML loading of the data into SQL Server.

Another significant improvement in Sedna addresses a complaint developers had about the way the previous Upsizing Wizard upsized character fields in VFP into SQL Server Varchar fields. In VFP, a character field is always the same length no matter how much data the user or application entered into the field. The field is always right filled with spaces. When the old version of the Upsizing Wizard upsized this data, it passed the extra spaces along to a Varchar field. This completely defeated the purpose of the Varchar field in SQL Server. The Sedna version of the wizard trims the spaces for all Character fields upsized to a Varchar field. This will save space in the SQL Server database and save developers from creating post conversion routines to clean out the extra spaces.

Another data issue with the old wizard is related to VFP Date and DateTime fields with respect to empty dates ({}, {//} or {// ::}). The concept of an empty date does not exist in SQL Server. DateTime fields are either filled in with a date or must be NULL. The previous version of the Upsizing Wizard upsized empty dates to January 1, 1900. You can control the way the Sedna Upsizing Wizard handles empty dates in step five of the wizard user interface, or by setting the upsizing engine BlankDateValue property if you are handling upsizing programmatically.

The last two changes to the upsizing process are related to the way SQL Server deals with table names and column names. VFP developers occasionally run across tables designed with columns using a reserved SQL keyword although it is not recommended and definitely violates best practices. The old Upsizing Wizard did not handle this well. The Sedna version of the wizard automatically delimits the columns with brackets when it comes across keyword named columns during the upsizing process.

Microsoft will also fix a bug in the older Upsizing Wizard that occurs when you upsize tables with a space in the name. The original Upsizing Wizard replaced the spaces with an underscore (_). For instance, it would upsize “Customer History” as “Customer_History” which could break things like views and your application code. The new wizard upsizes the table name with the spaces.

Upsizing Engine

The Visual FoxPro team separated the Upsizing Engine from the user interface to allow developers to programmatically control the upsizing process without user interaction.

I already discussed how you can pass three parameters to the UpsizingWizard.APP file when you run it. You can use two other ways to control and extend the Sedna Upsizing Wizard: programmatic control of the UpsizingEngine object, and creating an UpsizeExtension object.

I think you might be getting the idea that the upsizing engine is extremely powerful and extremely extensible in true VFP tradition.

Look at the two programs included in the article downloads (not in the October 2006 CTP) and in future releases of the Sedna Upsizing wizard. For more details on an update, see the sidebar, Post-CTP Update. The programs show you the way you programmatically control the engine and extend it with the UpsizeExtension object. Both programs have a lot of comments included to guide you through the process of customizing it for your needs. In fact, you’ll find lots of comments that start with “*// Customization” with notes on why you might want to make changes and what value you want to set.

TestEngine.PRG demonstrates the properties you can set in the UpsizeEngine and the methods you need to call if you want to execute that behavior. TestEngine.PRG demonstrates upsizing the VFP Northwind database to a SQL database called “YYY” using no UI whatsoever. Note: you'll have to change the assignment to lcConnString and the SET PATH statement to match the proper settings on your system. You can review the code to see how this all works, but I want to highlight just a few sections of the code to give you a taste of how simple it is to work with the UpsizeEngine object.

After the program successfully connects to the SQL Server, it instantiates the UpsizeEngine object:

loEngine = NEWOBJECT('UpsizeEngine','WizUsz.prg')

At this point, you can start setting some properties to define the behavior of the upsizing process and then call a couple of key methods. The TestEngine code shows how few properties you have to set to upsize a database. I have detailed some of these properties in Table 1 and some of the methods used to set properties in Table 2.

Once you’ve set all the properties you populate the various lists with the items you want converted (tables, views, relationships, indexes, and fields). You accomplish this using a couple of key methods (Table 2) and then you call one method to kick off the upsizing process:

loEngine.ProcessOutput()

The example code also shows how you can use the VFP BINDEVENTS() function to hook in your own behavior to the initialization process, the update process, and the completed process:

BINDEVENT(loEngine, 'InitProcess', ;
          SomeObject, 'InitProcess')
BINDEVENT(loEngine, 'UpdateProcess', ;
          SomeObject, 'UpdateProcess')
BINDEVENT(loEngine, 'CompleteProcess', ;
          SomeObject, 'CompleteProcess')

You create the SomeObject reference and bind it to the different methods of the UpsizeEngine object. The UpsizeEngine object raises the three events using RAISEEVENT(), which in turn delegates to your code.

The TestExtension program is almost identical to the TestEngine program, with one caveat; it creates a second object known as an UpsizeExtension object (Listing 1) and assigns the reference to this object to the oExtension property of the UpsizeEngine object. The object follows the hook design pattern. Inside the UpsizeEngine object are methods with the same name as the UpsizeExtension object. The methods in the UpsizeEngine object look at the UpsizeExtension object if it is set and check for the method on the UpsizeExtension object. If it exists, the method in the UpsizeExtension object is run. This is very similar to the event handling with COM objects and implementing the programming interface of the COM object. Note: you do not have to define every function in the UpsizeExtension object that resides in the UpsizeEngine object. You only have to define the methods you want to extend.

The improvements in performance, exposing the engine separately from the user interface, more granular control for upsizing data, and the better data handling strategies makes the Sedna Upsizing Wizard a pleasure to use.

I think you might be getting the idea that the upsizing engine is extremely powerful and extremely extensible in true VFP tradition.

Microsoft released the source code for the Sedna Upsizing Wizard with the October CTP and will release the final version when Sedna ships. This means you can review it, extend it, and enhance it. I anticipate that if there is enough interest in the Fox Community, this could become a project in the open source project VFPX (http://www.codeplex.com/Wiki/View.aspx?ProjectName=VFPX).

Conclusion

The Sedna Upsizing Wizard will become a viable choice for VFP developers when it comes to creating a strategy to migrate Visual FoxPro database containers to SQL Server 2000 and SQL Server 2005. In the best case you have designed your VFP database just as you want it in SQL Server and all you have to do is run the wizard. Those databases that need a bit of reengineering will not be as simple to upsize, but the scripts generated by the wizard might be useful as the last step of your migration.

define class UpsizeExtension as Custom
   function CreateTargetDB(toUpsizeEngine)
      messagebox('In CreateTargetDB method')
   endfunc

   function AnalyzeFields(tlAllTables, ;
                          toUpsizeEngine)
      messagebox('In AnalyzeFields method')
   endfunc

   function SendData(toUpsizeEngine)
      messagebox('In SendData method')
   endfunc

   function AnalyzeIndexes(toUpsizeEngine)
      messagebox('In AnalyzeIndexes method')
   endfunc

   function CreateIndexes(toUpsizeEngine)
      messagebox('In CreateIndexes method')
   endfunc

   function CreateTriggers(toUpsizeEngine)
      messagebox('In CreateTriggers method')
   endfunc

   function CreateScript(toUpsizeEngine)
      messagebox('In CreateScript method')
   endfunc

   function BuildReport(toUpsizeEngine)
      messagebox('In BuildReport method')
   endfunc

   function UpsizeComplete(toUpsizeEngine)
      messagebox('In UpsizeComplete method')
   endfunc
enddefine