Skip to content
Home » News » 2017 News » Moving from the DataFlex database to Microsoft SQL Server

Moving from the DataFlex database to Microsoft SQL Server

  • by
DataFlex database

Are you still using the DataFlex database for your DataFlex applications, but considering moving to another database? We believe you could benefit by moving from the DataFlex database to an industry standard database, such as Microsoft SQL Server.

Why move from the DataFlex database?

Although the DataFlex embedded database is sufficient, current databases, e.g., MS SQL Server, will give you increased flexibility, efficiency and performance, which will allow you to:

  • integrate with data from non-DataFlex applications, nearly all of which use SQL databases
  • simplify administration, maintainance and support of applications, with the built-in tools and 3rd party products for SQL DBMS engines
  • benefit from the robustness offered by modern SQL DBMS servers, especially over LANs, WANs and Wi-Fi connections

Data Access Worldwide added DataFlex support for Microsoft SQL Server 2016 in October 2016 (see DataFlex adds support for Microsoft SQL Server 2016) providing new enhancements in the areas of scalability, performance, security and more for your DataFlex applications.

Find out more about the many other benefits of choosing to use SQL with your DataFlex applications.

Many DataFlex developers have already made the move to SQL databases, read what they have to say.

Migrating from the DataFlex database – using the Conversion Wizard

The DataFlex Studio comes with a free SQL Conversion Wizard, which walks you through the process of converting a DataFlex database to your target database, e.g., to MySQL databases. The Conversion Wizard is also available from the Database Builder utility.

The suggested route is to create a “Managed Connection ID” which just holds your connection info, i.e.:

  • which driver – so what type of DB you are connecting to
  • the actual Server to connect to
  • user ID
  • password
  • database

The wizard then lets you select which tables you want to convert, including whether:

  • to create them as “Recnum tables”, which you will want to do if you make use of Recnum in your programs, or “Standard tables” (so without Recnum). It’s probably safest to use recnum tables unless you are sure you don’t use it
  • to convert the actual data or just create an empty skeleton
  • to delete the original tables afterwards (hint: don’t – unless you really trust your backups!)

So long as you don’t delete the original tables, you can just take a back-up of Filelist.cfg first and then if the whole show goes south, you can just restore that and functionally the DataFlex side will be back where you started. 

In the past, before the advent of the Conversion Wizard, doing this process manually, writing your own scripts and programs to do it, was a monumental task, but now with the Wizard it is just a matter of a few clicks and it is done.  How long it will take depends on the size of your data of course; I don’t have any actual metrics, but it is pretty darn quick.

Anyone can access the Wizard for free by downloading a copy of the DataFlex Personal Edition (a free and fully featured development licence, but not licensed for commercial use) and give it a try.  You will probably have to create a Data Access ID before it will let you do that, but that is only a sign-up process – they understandably want to know who you are for marketing purposes.  From the DataFlex Studio (once installed and told where your data files and filelist are), the SQL Conversion Wizard is on the menu under “Database”.

Watch the ‘Migrate to SQL’ video

If you are thinking of migrating your DataFlex embedded database, but would like more information, try watching the Migrate to SQL YouTube video (produced by FrontIoT). This video goes through the process of using the Conversion Wizard ad how to make changes to the search list.