VDF-GUIdance - Multi-homed sustem in MS SQL Sever

Shared knowledge leads to accumulated knowledge

Multi-homed system in MS SQL Server

by Frank G. Vandervelpen

Summary This white paper describes a way to tackle the problem that arises when you want to convert a multi-homed DataFlex database to MS SQL Server. By multi-homed we mean a database with shared datafiles in one directory and specific company-related datafiles in their own seperate directory	No Files Available
	Date Created:	17/11/1999

	Date Updated:	17/11/1999
	Author:	Frank G. Vandervelpen
	Company:	Velpe Development bvba

How to make programs dynamic for several MS SQL Databases with the same database structure

Installation of SQL Server 6.5

The installation of SQL Server 6.5 was a standard installation. We have loaded support pack 5a on it, and we have increased the master database device to 1.25 GB. (i.e. the master.dat)

Installation of the SQL_DRV

We have installed Mertech's Flex2SQL version 2.0 for DataFlex 3.1d Console mode and the Visual DataFlex Flex2SQL version 2.0 for Visual DataFlex version 6.

We have seen some conflicts with msvcrt.dll with vdf6. It should not give any problems with VDF5.1.

The conversion

We have migrated the DataFlex files from the filelist towards SQL Server through the standard migration utility. Therefore, we have used the account sa and the appropriate password. We have selected a filelist and an environment file (cfg) to determine the source path.

Ways of converting a multi-homed system

Suppose following directory structure when migrating from DataFlex:

M:\forks\data : holds the shared databases

M:\forks\Brownies : holds the specific tables for company 1 and the databases are the same as for the other 2 companies

M:\forks\whities : holds the specific tables for company 2 and the databases are the same as for the other 2 companies

M:\forks\yellowies : holds the specific tables for company 3 and the databases are the same as for the other 2 companies

When we migrate these towards SQL Server 6.5 we can't place these things into databases. So the tables will be placed in separate tables. We have tested relationships over several SQL Databases and this works because we have defined them in the .int file. However, the DATABASE_SPACE_NAME is not defining which SQL-database to use. This is defined in the .td file. However, these are necessary to open the convenient SQL Database.

Therefore, making it dynamic was not the easiest part.

What we have done is first open all mutual (shared tables). Once that we have done this, we are defining the new SQL-Database to use for the company-dependent tables.

This leads to the situation where we have the following code:

SET_DATABASE_NAME to sValue
open kids

where sValue is the name of the SQL-Database. All files opened after the Set_database_name will try to open the table in the SQL-Database indicated, rejecting the parameter in the .td file. Don't forget to close the file before reopening it to get the new SQL-Database.

On the left hand site is the layout from a point of view of the SQL Server

Multi-homed system in MS SQL Server

Summary