MS SQL Server Tab

How To Access

  • Main Menu | Tools | Generate Scripts | SQL Script for Creating Table MS SQL Server tab.

This tab allows you to select the database and set migration options that are unique to MS SQL Server databases.

Note

If not connected to an MSSQL server, all the controls on this tab page will be disabled.

GenerateScripts_MSSQLTab.png

Use Database

Select the database where the converted data will be saved on the back-end table.

Filegroups

An SQL Server database can be partitioned using files and/or filegroups. A filegroup is simply a named collection of individual files grouped together for administration purposes. A file cannot be a member of more than one filegroup. Tables, indexes, text, ntext, and image data can all be associated with a specific filegroup. This means that all their pages are allocated from the files in the filegroup. Log files are never a part of a filegroup.

There are three types of filegroups:

  • Primary: The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.

  • User-defined filegroup: User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

  • Default filegroup: One filegroup in each database operates as the default filegroup. When SQL Server allocates a page to a table or index for which no filegroup was specified when they were created, the pages are allocated from the default filegroup. Only one filegroup at a time can be the default filegroup. Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another.

If no default filegroup is specified, the primary filegroup is the default filegroup.

With this new feature in Flex2SQL, you can now select the filegroup where you want to place your tables and index files.

Note

  • Microsoft Windows Azure SQL Database does not support filegroups.

  • SQL Server 2000 can work quite effectively without filegroups; so many systems do not need to specify user-defined filegroups. In this case, all files are included in the primary filegroup and SQL Server 2000 can allocate data anywhere in the database. Filegroups are not the only method that can be used to distribute I/O across multiple drives.

  • Members of the db_owner fixed database role can back up and restore individual files or filegroups instead of backing up or restoring an entire database.

Legacy Index Support

This option allows the user to use existing field name formats for inverse keys.

Use Older Name Format for Inverse Keys: If this option is selected legacy name formats are used when defining inverse keys.

Use Regular Save During Migration

This option overrides the default migration mode (bulk insert) that is designed for high-speed migration of data from a DataFlex database SQL Server database. Due to a bug in MS SQL Server, there are some cases where data is not correctly loaded into the table for tables with large number of columns. The bug has been reported to Microsoft.

Select Use Regular Save During Migration to use regular inserts to copy data. This is significantly slower than the default option.

This option is useful if you have problems during the migration. Records are inserted one by one instead of in bulk. This lets you migrate all records that do not have any errors and skip problematic ones. If you are migrating in bulk and one row in a bulk group has problems, the entire bulk will fail to migrate.

Bulk Copy Options

To allow the user more flexibility during the data conversion Flex2SQL now includes the capability of customizing the bulk copy settings for the data migration. The user can specify the number of rows per batch or kilobytes per batch. Note: Only one value can be specified; either rows or kilobytes.

  • Rows per Batch: This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.

  • Kbytes per Batch: This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.

  • Lock Table: This option locks the table during the conversion. If selected, no DataFlex operations can take place during the conversion.

Bottom Controls

The controls at the bottom of the dialog remain the same regardless of the tab selected.

What to do radio buttons

There are 3 options:

  • Create Table and index: This will generate a script that can be used to create the tables and the indexes from the selected .Dat file(s) onto the selected backend.

  • Create Table Only: This will generate a script that can be used to generate the create the table(s) from the selected .Dat file(s) onto the selected backend.

  • Create Indexes Only: This will generate a script that can be used to create the indexes from the selected .Dat file(s) on the seleted backend.

The Buttons

  • Preferences: Shows the Preferences dialog.

  • SaveSet: Shows the Save the files to save as a set dialog.

  • Generate All: Generates the scripts for all the tables selected in Filelist dialog based on the radio button selected in What to do radio buttons.

  • Convert: Generates script for the only the table in the Table Name form based on the radio button selected in What to do radio buttons.

  • Help: Shows the help page of the selected tab.

  • Cancel: Closes the dialog.