This post is a “living” document – it will be added to over time to provide everything you need to know about administering the Microsoft SQL server to host Btrieve data.
What Objects are created into MSSQL?
-
For tables with at least one index which was not unique before and the table does not have a Primary Key defined in the DDFs:
- MDS_RECNUM column is a primary key. It is added as the last segment to any index which is not already unique. Thus, all indexes are created as Unique. This guarantees proper record traversal forward and backwards through otherwise duplicate values.
- If the table has a primary key and has indexes which are not unique, the segments of the primary key are appended to the non-unique key to make it unique.
- “Application Locks”
- Btrieve allows an application to lock records outside of a transaction. This is counter-intuitive to a SQL database and conflicts with normal transaction processing. Therefore, the standard server record locks cannot be used. Instead, the driver utilizes App Locks and manages its own record locks.
- Locks are acquired through getapplock and individual locks are released with releaseapplock. When a table is closed or a transaction is ended, a large number of locks may be needed to be released. This falls to the mds_release_locks_v1 stored procedure.
Database Configuration
Recovery Model
It is suggested to use the Bulk Logged model for the best performance versus safety tradeoff.
Isolation Level
Certain features in the driver are reliant on the Snapshot isolation level. If another mode is used, it is possible for an application to hang – one client id is waiting on a locked record from another client id (from the same process).
To check the current mode of the database, run the SQL command DBCC USEROPTIONS.
The migration tool runs these commands on the database. If the migration tool is not utilized, these commands should be run manually:
ALTER DATABASE <db> SET READ_COMMITTED_SNAPSHOT ON
Data files used?
- For each Btrieve data file, a corresponding interface file is created. ‘filename.ext’ becomes ‘filename_ext.INT’. The INT file contains metadata used at runtime and will reside in the location where the original data file would have been located. The original Btrieve data file is no longer needed.
- The Pervasive.SQL/Btrieve engine is not needed for Mertech database drivers to work in the application (only by the migration tool). The Pervasive.SQL engine can be shut down after the data migration is performed.
User access needed to MSSQL database
- To be done: define EXACT roles/rights needed for migration
- To be done: define EXACT roles/rights needed for runtime
- If application will be creating tables on the fly (temp tables for instance), the user will need object creation rights.
Application logic can be altered to login as a different user for these temp tables.
Determine licenses used
You can use the following query to see how many licenses are in use by the Mertech driver. If this number exceeds the count provided by the license file, the driver will fail to run.
SELECT count(DISTINCT(net_address))
FROM master.dbo.sysprocesses
WHERE program_name = 'SQL_BTR - SN:<your serial number>'
Things to look for
-
B_CREATE and / or file deletion through OS call.
Many apps will create “temp” files for sorting and other short-lived purposes. Or, create new files each quarter/year.
B_Create is supported; however the table will not be fully defined in SQL. This can be overcome with application modification to utilize either the MdsAddTable function or an INT file Template (discuss elsewhere).
Instead of removing the temp file using an OS call, utilize MdsDropTable (or B_DROP_FILE).
There is also a B_TRUNCATE_FILE API if the application simply wants to delete all the records. This is far faster than the usual Btrieve method of read/delete each record and avoids dropping the table which may have adverse affects on the defined tablespaces.
Can app control login?
B_SQL_LOGIN and B_SQL_LOGOUT allow the application to avoid the login dialog and fully hide the user credentials used for accessing the database. Documented in the SDK portion of the User Guide.
Can app execute queries?
B_SQL_* functions allow execution of SQL queries and results retrieval on the same connection as the driver. Documented in the SDK portion of the User Guide.
Sensitive data
Most applications will have a few tables of data which is more “sensitive” than others. This may include user and password information for logging in to the application, registration codes, medical information, etc. Since Btrieve files must be accessible to the user running the application, the data is also accessible. This is a great concern for which Btrieve provides an encrypted file format.
The issue also exists when accessing the data through Pervasive.SQL (ODBC, Ole-DB, etc) which requires tables to be defined in the DDFs. Some applications will choose to not define the sensitive tables and leave them strictly to Btrieve access with an owner name. Files that are not defined in the ddfs will not be migrated to the new SQL server – this is a problem…
How do these two approaches migrate to MSSQL?
First, keep in mind; SQL tables do not require direct access by users. In other words, your application can be given a login which only it knows so users cannot access the data directly – only through the application. So, if the server, database, tables are locked down under security, auditing, etc, the data should be even safer in the SQL tables than it was under Btrieve. So, most administrators would not encourage the use of table encryption for instance.
For the data files which are not currently defined in the DDFs, there are several approaches.
- Define them in the ddfs. You don’t have to define each field – leave most of the record as a binary. Only the indexed fields must be properly defined.
But perhaps you intend to continue shipping the same ddfs to Btrieve customers and still don’t want the tables to appear here.
- Backup the current ddfs and define the secure tables. You will then be maintaining two sets of ddfs.
- Create a blank set of ddfs in another folder (that does not ship to the customers). In here, define just the secure tables. You will then have to migrate both sets of tables to the SQL server.
Another thought to further ensure security on the sensitive tables: separate the data into another database. Whether the tables are all defined in one set of ddf, or in two, you can migrate them to different database on the SQL server. This really doesn’t change the security of the data as logins can control access to the tables anyway. But it does clearly partition the data and make it easier to know which tables must have tighter security control.
Working with the new data
Now that the Pervasive Control Center is gone, how do I look at the data on the server?
The usual method is the Microsoft SQL Server Management Studio that ships with SQL Server.
There are also many tools available for purchase online. One such is Aqua Data Studio – http://www.aquafold.com/
Most of these tools let you execute queries, edit data, modify table structures, import/export, etc.