Mertech will be releasing the beta of its Flex2SQL v10.1 drivers during the week of  June 24th. Flex2SQL v10.1 is a minor upgrade from the v10.0x drivers released earlier last year. In v10.1, our overarching objective is to make it easier to work with  DataFlex development tools and SQL administration tools. This effort will continue in future releases.

The foundation for tighter integration between the DataFlex and SQL worlds was laid in the initial release of v10 drivers with the elimination of the table definition files. The .TD files were created to make OPEN calls faster when working with SQL but at a price; any change to the table structure on the database server required deleting the .TD files to get the updated structure. This led to unnecessary deployment headaches. We improved the efficiency of our OPEN calls so that we now get updated information from the server when the file is opened without affecting performance.

For a DataFlex program to co-exist amicably with changes made to table structures on the database server, we need to store (and retrieve) as much information on the servers as possible, without breaking DataFlex functionality.

The following are the highlights of the new version:

Store relationship information in the database
Currently, the relationship information between files is stored in the INT file. With V10.1 drivers, users will be able to recreate all DataFlex relationships directly on the server, and you will also be able to read relationship information on the server back into DataFlex.

Mixed-case table and field names
Currently we allow all object names to be either all upper case or lower case and the field names follow the convention selected for the table name. In v10.1, users will be able to define their table names and field anyway they prefer.

Inverse Key data integrity check
We are introducing a new data checker for diagnosing problems with inverse key field values

Convert tables types from recnum to RowID tables
Users will be able to create ROWID tables using VDF Studio as well as convert existing RECNUM tables to ROWID tables using our migration utility.

Save multiple connection strings in login dialog
The current login dialog box only allows users to connect to one server. With this enhancement, users will be able to switch between multiple server back ends and save the login information. This is useful in a development environment where developers have to switch between development or training server to and production server.

New SQL script for database maintenance
In addition to scripts for creating or dropping objects, the new scripts will allow analyzing and adjust database after loader executions, adjusting sequences and handling line feed characters in in text files and also scripts for turning on triggers.

Increase in ESQL performance
We have improved performance of ESQL code in retrieving data, especially when using variables to bind values.

New low-level ESQL API
Our current ESQL engine is implemented through the DataFlex ICODE engine using FMAC commands. We have built a completely new driver level API to allow building object-oriented ESQL classes for working with multiple result sets, simplifying stored procedure and function.

Tablespace control (PostgreSQL only)
Allows PostgreSQL users to select the table space where they want to create tables.

Introduction

One of the most significant benefits of running MS SQL is the database recovery model. Your company’s information is its life-blood, and there are myriad methods to backup and preserve it. We will be discussing some basic techniques to preserve your data in a simple and efficient manner. We will not delve into Mirroring, Transaction Log Shipping or Replication; merely the actual backup of individual databases. The backup operations are dependent on the type of backup recovery model used.

 The MS SQL default is Full recovery model, which is the model we will be discussing in this article as this is the most commonly used model. We will be using the MS AdventureWorks2008 database as our test database in this article. It is available for download from a Microsoft partner website AdventureWorks2008. Other backup strategies such as backing up full-text search catalogs and very large databases are not discussed in this article.

Types of Recovery Models

                Simple Recovery Model

                Full Recovery Model

                Bulk-Logged Recovery Model

Types of backups

                Full

                Differential

                Transaction Log

Creating a Full Backup

Creating a Full backup using T-SQL scripts

                 Using sqlcmd or SQL Query Analyzer in the SQL Query Management Studio tool, enter the following code to create a Full backup device and backup file:

USE MASTER
EXEC sp_addumpdevice ‘disk’, ‘AdventureWorks2008 Full Test’, ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorks2008Full.dat’
USE MASTER
BACKUP DATABASE AdventureWorks2008 TO ‘AdventureWorks2008 Full Test’
WITH DESCRIPTION=‘Full Adventureworks 2008 Backup’ 

                Creating a Full Backup using SQL management Studio

1. Highlight the database you wish to backup
2. Right-click the database and select “Tasks”, “Back Up…”
3. If you would like the backup to expire, enter an integer value.
   1. For general usage enter an integer between 7 and 30 days inclusive
4. The default device is a disk file with a “bak” file extension.
   1. If you have an off-line or on-line backup repository specify this now.
5. Click the <OK> button to create the backup.

Restoring a Full Recovery Model Backup Step-by-Step

Below we will give an example of a specific type of restore operation involving restoring the AdventureWorks2008 database using the Full recovery model from a Full backup, Differential backup and a transaction log backup.

Step one – Perform a tail-log backup

As mentioned above, immediately prior to our Restore operation we need to backup any uncommitted transactions that may still be in the log. From the SQL Query window in the Management Studio logged into the correct SQL Server instance or from a SQLCMD command prompt logged into the correct SQL Server instance type (Note the filename and pathname are user defined; just make sure the directory path exists):

BACKUP LOG AdventureWorks2008 TO
DISK=’c:\temp\AdventureWorks2008EmergencyLog.bak’ WITH NORECOVERY

Step two – Perform Full Database Restore

Next we need to restore the last good full backup. Make sure you use the NORECOVERY option. We cannot fully recover our database and allow users back in until we have applied all the files necessary to complete the Restore operation. Note also that since we have two files included in our Full backup we need to restore the second file (unless we are just initiated our backup job less than two weeks prior). In our example enter the following (referring to the files we created in our Backup Strategies session):

RESTORE DATABASE AdventureWorks2008 FROM “AdventureWorks2008 Full Test” WITH File = 1, NORECOVERY

Step three – Perform Differential Backup Restore

The next step is to restore the last good differential backup. For our example we can assume we have a single Differential backup file to restore. Our command should be as follows:

RESTORE DATABASE AdventureWorks2008 FROM “AdventureWorks2008 Diff Test” WITH NORECOVERY

Step four – Perform Log File Restore

 To restore the log file, run the following command:

RESTORE LOG AdventureWorks2008 FROM “AdventureWorks2008 Log1 Test” WITH NORECOVERY

Step five – Perform the Final Database Restore

The last SQL command verifies and activates the database for use:

RESTORE DATABASE AdventureWorks2008 WITH RECOVERY

Creating and scheduling automatic Backups

Using sqlcmd and the Task Scheduler for SQL Backups

We will need to schedule each of our backup jobs using the task scheduler. We will be running a single command, sqlcmd with the appropriate parameters. A typical command would look like the following:

Sqlcmd /S <server name\instance>/E /Q “EXEC sp_BackupAdventureWorksTest @backupType=’F'”

Caveat running sqlcmd: sqlcmd is particular about the syntax and text case. Make sure you use single-quotes around T-SQL string parameters and double-quotes around the T-SQL query command we are running. The stored procedure parameter text case must match the variable declaration exactly. This is a pain in the neck, but much worse if you make an error and have to track it down! Although you can use a hyphen (“-“) in front of the sqlcmd parameters, I have had better success using a “/” character. Watch out, because this may change in the future.

Scheduling our Task

Use the “Task Scheduler” Windows utility to create a backup schedule for your database. If you do not know how to configure, please refer to this MSDN knowledgebase articles Windows XP Tasks and Windows Vista Tasks.

Database drivers are all about performance. They have a simple job to do and that is, to send and receive data from the application to the database server in the fastest possible way. Period. This is a fundamental requirement of database drivers, especially the ISAM to SQL kind where the product has to work with a new database without source level changes.

While the driver is the most important component in improving performance, there are some other elements to improving performance, rated (in my opinion)  in order of importance:

1. The database driver
2. The server hardware
3. The database server setup
4. The network

Database Drivers
All drivers are not created equal, (just like a Fiat and Ferrari are not equal.) A lot depends on how the product is engineered, how it handles data and how it interacts with the relational back end.

Over the years, when working on a new driver version, we have always put performace as our number one priority. Our development team’s goal is to squeeze out every last bit of performance possible. If we feel that re-engineering our code will give us that extra 10-15 percent, we will do it! For our v10 drivers for DataFlex, we pretty much re-engineered the entire source code to give us twice the performance of our v9 drivers.

We are already seeing the results of these re-engineering efforts.  A recent customer prospect doing a performance comparison between our Flex2SQL for SQL Server and the DAW CK for DataFlex reports Mertech’s driver outperforms DAW CK  by 3 to 5 times. In some cases, with larger tables, the performance difference was even higher. Clearly the driver plays a big part in performance.

Another area where the driver matters is in scalability. We recently deployed our Oracle driver at a customer site with 2500+ concurrent users! This customer had tried unsuccessfully to go live  with DB2 for over a year and finally gave up. It was not the DB2 server that couldn’t handle the load. It was the DB2 driver.

Server Hardware
When choosing a server hardware,  you need to make sure that you have adequate RAM and you have the fastest possible disk set up. The more RAM you have, the more data will be cached and served from memory. The biggest hardware bottleneck in client server computing  is disk access. Knowing what type of disk configuration will give you better result becomes very important. For example, knowing that RAID 1+0 will be 30-40%  faster than RAID 5 when writing to disk.  There is a huge repository of information on how to optimize a given database server. Include that in your planning process, especially if you are going to have  100+ users doing a lot of disk access.

Database Server Configuration
Most database servers perform quite well out of the  box with minimal tuning requirements. However, if you will have a large number of users, say 100+ users with large amount of data, you will need to make sure that the server is set up to reduce disk contention and disk writes. One way of doing this to by using a smart SAN or by putting your transaction log, index tables and data tables on separate disk spindles. Allocating a large RAM for server process is always good and if you are in Windows, make sure your server has a higher process priority.

Network
The network used to be an issue when we had 10MB networks which was fairly common just a few years ago. This is largely not an issue any longer with faster 100MB and Gigabit switches. However, when running the application over WAN, VPN and even wi-fi network can become a big factor. There you deal with some other issues like network connection glitches and data traffic over a smaller pipeline. The new intelligent data fetching mechanism we implemented in our v10 driver for DataFlex significantly reduces network traffic and also handles network connection issues by automatically retrying when connection to database fails. This means the application doesn’t crash when connection to server is lost for any reason.