Converting to SQL – A recipe for DataFlex developers

For years now, Data Access Worldwide has been advocating for developers still using the embedded DataFlex database to convert to an SQL database.

Why? Simply, to take advantage of the enormous benefits that these state-of-the-art systems provide.
So, if you’re still using the embedded database, here’s a simple-to-follow recipe to get you started with converting to Microsoft SQL Server today!

This blog will get you to the point where your application is running with SQL Server on your development machine to begin your familiarization with DataFlex and SQL. Let’s get started...

• Download Microsoft SQL Server Express or Developer (both are free)
  While the Developer edition can’t be deployed, it has none of the database limitations Express does, which may or may not matter for testing - this will depend on your application. See the Microsoft SQL Server edition information.
   
• Install Microsoft SQL Server
  Install SQL Server on your development machine. For more information, refer to the Microsoft SQL Server installation documentation.
  
  
• Migrate your application to DataFlex 19.1
  If you don’t yet use SQL Server with DataFlex, we recommend using DataFlex 19 or higher, which includes Managed Connections, a feature that simplifies creating and managing connections to databases in the Studio and other tools, as well as your application.
  
  Migrating to DataFlex 19.1 will also allow you to take advantage of new tools, including compiler warnings, to assist you in code cleanup. Visit the DataFlex Help for more about Language and Code Cleanup.
  
  • Download DataFlex 19.1.

• Convert the Order Entry sample to SQL Server
  • Open the Order Entry sample workspace in DataFlex 19.1 Studio
  • Create a Connection ID
    • Select Database menu > SQL Connection Manager > Add to Create a Connection Id
    • Use a unique and meaningful Connection Id, such as Order191.
    • If you installed SQL Server Express or Developer on your local development PC, “(local)” should work to access that SQL Server instance. If not, open SQL Server Management Studio (SSMS) to connect to the SQL Server instance you wish to use and copy the instance name from there.
    • Check ‘Use Windows Authentication’. This is typically used while developing.
    • Click the ‘Create Database…’ button and enter a database name. I suggest using the same name as your Connection Id, it’s an easy way to remember what this database is for.
    • Click the ‘Test Connection’ button to ensure everything works. If so, click ok to close the dialog.
    • Once back in the ‘SQL Connection Manager’, click Save to save the Connection Id.
  • Convert tables to SQL Server
    • Select Database menu > SQL Conversion Wizard > convert all the tables to SQL Server
      This wizard will guide you through the steps of converting the embedded database tables to your newly created SQL Server database. When in doubt, use the default wizard settings.
      The following statements are to clarify common questions from developers who are new to SQL Server and our conversion wizard:
    • Convert recnum tables to standard tables unless your application has legacy code that depends on Recnum support (e.g. Recnum relationships).
    • Checking ‘Delete original table after conversion of table successfully completed’ deletes the DataFlex table files (*.dat, etc.). If you leave this unchecked (which we recommend until you are certain the conversion is successful), these files will remain, and you can switch back to using these simply by removing the “MSSQLDRV:” prefix from the tables’ filelist entries – you can remove the prefix using Database > Remove database identification option in Database Builder.
    • Leave the owner field blank. When using SQL Server, this will default to the “dbo.” prefix, the SQL Server default.
  • Once the wizard completes, the tables in this workspace have been converted to SQL Server. You can use the DataFlex tools or even SSMS to view and edit the data.
  • Next you can use the same steps to migrate your own application/workspace to SQL Server. Be sure that you have a complete back up of your workspace and data before starting any migration or data conversion.

Additional Tools

• SQL Server Management Studio (SSMS) isn’t a requirement, but we highly recommend installing it, at minimum on your development PC. SSMS is a powerful tool that allows visual management of SQL Server instances and databases. If you’re going to use SQL Server in any capacity, you should familiarize yourself with the basic functionality of SSMS.
  
  Download Download SQL Server Management Studio (SSMS) - this page also has the SSMS documentation.

• There are numerous SQL Server sample databases available online; no samples are installed by default with SQL Server. Chinook is a nice sample database many developers are familiar with. Using sample databases is a great way to learn and test features of SQL Server without having to worry about damaging your own data.
  
  Download the Chinook sample SQL Server database - this is an SQL script that you can execute in SSMS to create the database on your SQL Server.
• Check out Harm Wibier’s Working With Databases course in the DataFlex Learning Center.
• For a “real world” example about upgrading to SQL, read Michael Mullan’s blog, If you still have clients on the DataFlex embedded DB, WHY?, that recounts his experience converting a client from the DataFlex embedded database to Microsoft SQL Server.