Calsoft Labs is a leading technology partner for companies, helping them develop new products and modernize existing ones using emerging technologies. more

MSDE - The product and related issues

Introduction

MSDE is a solution from Microsoft that enables developers to build freely distributable desktop and shared database solutions, which can easily migrate to SQL Server 7.0. This white paper discusses the MSDE product and issues related to the installation, database and migration of data; as well as an evaluation of its merits and demerits.

In preparing this document, we have referenced MSDN Library materials. We would also like to acknowledge the respective trademarks of the companies mentioned in this paper.

MSDE - The products and its attributes in a nutshell

According to Microsoft, "MSDE is a fully SQL Server compatible database engine that can be used for (developing) desktop and shared database solutions built with either Visual studio 6.0 or Access 2000". Once the applications are developed, developers can distribute their applications freely without bothering about issues of scalability. It supports upto 2GB of data for any individual database. The advantage of MSDE is that it runs as a service in the background, without the user having to get into the issues regarding its functioning.

In addition, MSDE complies with the Transact-SQL language guideline. Transact-SQL is a superset of ANSI-SQL, which has the functionality of enabling a server to be programmed with custom business logic. This custom business logic can be invoked from the client and executed on the server. On this account, MSDE objects are fully compatible with SQL SERVER. MSDE is also fully compatible with all the database connection technologies that SQL server 7.0 supports. This includes ADO, DAO, ODBC and OLEDB. It runs under Windows NT 4.0 or later and Windows 95 or later.

Programs built with MSDE have the advantage of built-in scalability without involving any upgrade or conversion, if the client wishes to "upsize" or scale the application for the enterprise.

MSDE Installation Issues

There are 2 kinds of installations issues that need to be tackled.

MSDE for Developer

For a developer, the installation process is simple - it can be done from either an MS OFFICE CD - 2000 PREMIUM or 2000 Developer. If installed from the Office 2000 Premium CD, the machine on which installation takes place, becomes the local server. Projects created with this version will have an .ADP ( Access Database Project) extension with the database on MSDE, even though the user interface is that of Access. However, you cannot distribute software created with this version of MSDE. The 2000 Developer version is a self-extracting file, and software created with it can be distributed. In addition, MSDE can be installed using the MSDE for Visual Studio 6.0 CD.

MSDE for the end user

When deploying custom solutions, developers can include a command line in MSDE for Visual Studio 6.0 setup with the installation program used to deploy the solution. The MSDE for Visual Studio 6.0 setup uses a response file to allow it to run silently with a single command line statement.

While distributing the solutions, the following have to be done programmatically :

Installing MSDE on the user's machine

The VBA Package and Deployment Wizard, has an option to run an executable or batch file on completion of Setup. The following example uses the feature to launch a batch file. This batch file contains a command line that will extract and install MSDE on the user's machine.

The batch file will contain

 Start/wait msdex86.exe -s -a -f1 "sql70ins.iss"
 

This uses a default .iss file command within msdex 86.exe along with all of the product defaults.

Microsoft's Demo version for installing MSDE on user's machine has the following command line

(comments)
 < start /wait D:\MSDE\setup\setupsql.exe 
 -f1 setup.iss -SMS -s" >

Command :

SQLCmd = lsCDDriveNoLabel &
 "\MSDE\msdeX86.exe -a"
SQLCmd = SQLCmd & " -f1 """ & Mypath &
 "\setup.iss"""
SQLCmd = SQLCmd & " -SMS"
SQLCmd = SQLCmd & " -s"

"-a" Append any following arguments to the internal command line

"-f1" Following this argument, the name of response file needs to be given along with the full path.

"-s" Run in Silent Mode. Can be used when MSDE is to be installed without any user interaction.

Silent Installation

Silent installations are implementations of software programs and upgrade procedures from a network server, that run without interrupting the work flow or requiring user inputs. Since these installations do not require user input, the input must come from a different source. That source is the InstallShield Silent Response file, named setup.iss.This file contains the information that the user needs to provide during a normal installation.

The installation of MSDE can be automated by using an .iss file that records the responses and selections normally provided by the user during an interactive install. To determine the success of MSDE silent install, you can look up the Sqlstp.log and Setup.log files that are created automatically during the setup.

If you want to determine programmatically as to whether the installation was successful, you need to take the following steps

The ResultCode entry is present in the [ResponseResult] section and the the Completed Entry is present in the [Status] section of the setup.log file

ResultCode == 0

Completed == 1

Reboot Required == 0/1. If it is 0, then no rebooting is required and if it is 1, then rebooting is required.

It can be done in 2 ways. On Win NT it can be done via a command line after installation or through SQL DMO code.

The command line for Win NT is : net start mssqlserver

Through SQL DMO code - which works for Win95/98 other than Win NT - requires a reference to SQLDMO Library and the following code is to be put :

Table:ResultCode

 Sub TurnOnMSDE()
 Dim oSvr As SQLDMO.SQLServer

 Set oSvr = CreateObject("SQLDMO.SQLServer")
 On Error GoTo StartError
 oSvr.LoginTimeout = 60 
 'this needs to be high to avoid time-out errors.
  oSvr.Start True, "(local)", "sa", ""

 ExitSub:
 Exit Sub

 StartError:
 If Err.Number = -2147023840 Then 
'This error is thrown when the server is 
  already running,
 and Server.Start is executed on NT
 oSvr.Connect "(local)", "sa", ""
 Resume Next
 End If
    
 End Sub

Database related issues

We will now look at the database related issues in detail.

Creating a Database

MSDE is a data engine and does not have its own user interface for database design. So you need to adopt any of the following methods to create a database that can be utilized with MSDE.

Connecting to Database

According to Microsoft, connection to a database can be achieved through either using Access/Access 2000 or through Visual Basic. To quote from their white paper,

"Connecting to an existing MSDE database can be accomplished in two ways through Access 2000. Tables can be linked to an Access database (.mdb), or tables can be connected to through native OLE DB from an Access 2000 Project. Access 2000 Projects (.adp files, based on MSDE) can take advantage of some of the features of MSDE that not supported through Access databases (.mdb files, based on Jet). These features include stored procedures, triggers, and declarative referential integrity (DRI). This additional feature makes using Access project files a much better method for connecting to MSDE than linked tables. Project files can also connect to SQL Server databases, so scaling up to SQL Server requires no changes to an Access 2000 Project."

If you want to connect to MSDE using Visual Basic, then ADO will be preferred method to do so. When you define the connection programmatically, you will have to ensure that the DSN is created and that the ODBC connectivity has been provided. If not, you can do this programmatically before creating the connection to MSDE database through ADO.

Database Objects

As explained earlier in this document, MSDE, like Jet, is a database Engine, which gives the databases a Sql Server feel and functionality even though the database may have been created in or through MS ACCESS.

You could create an MSDE database either through Access 2000 or with SQL Server Tools.

Creating Database through Access 2000

MSDE databases are referred to as "Access Projects" ( files with .adp extension). To create a New project, start ACCESS 2000, and from the menu choose File -> New. Then from the dialog box that comes up, choose Project(New). These Access project files maintain an OLE DB Connection to the MSDE server.

To create MSDE database objects using Access 2000

To create new tables in Access

To create a new trigger on an MSDE server table using Microsoft Access:

Note: It is only possible to manage triggers through Access for SQL Server compatible database tables. The option does not appear for local Access tables.

Defining referential integrity in Access 2000

The simplest way to enforce table relationships in an MSDE database is through the use of Declarative Referential Integrity (DRI). You can define DRI relations for your MSDE tables using an Access database diagram.

To define DRI relations:

Creating Database Objects through Visual Basic

The following code excerpts are taken from the Visual Basic MSDE installation sample project. The code below demonstrates how to create a new database directly from a custom Visual Basic application. The complete Visual Basic MSDE installation sample project is available for download.

 Public Function CreateDatabase(DBName As String, 
 DevPath As String) As Boolean

'This function will create a database and log file

First step is the connection:

 Dim oSvr As Object

 Set oSvr = CreateObject("SQLDMO.SQLServer")

 'Connect to SQL server
 oSvr.Connect "(local)", "sa", ""

 Define the database objects required:

 'Dim oDatabase As New SQLDMO.Database
 Dim oDatabase As Object
 Set oDatabase = CreateObject("SQLDMO.Database")

 'Dim oDBFileData As New SQLDMO.DBFile
 Dim oDBFileData As Object
 Set oDBFileData = CreateObject("SQLDMO.DBFile")

 'Dim oLogFile As New SQLDMO.LogFile
 Dim oLogFile As Object
 Set oLogFile = CreateObject("SQLDMO.LogFile")
	.

Name the data file:

 ' Define the PRIMARY data file.

 oDBFileData.Name = "XYZData"

 oDBFileData.PhysicalName = DevPath & "\XYZ.mdf"

 oDBFileData.PrimaryFile = True

 Set the database growth options:

 ' Specify file growth in chunks 
 of fixed size for all data files.

 oDBFileData.FileGrowthType = SQLDMOGrowth_MB

 oDBFileData.FileGrowth = 1

 oDatabase.FileGroups("PRIMARY").DBFiles.Add 
 oDBFileData

 Set up the parameters for the transaction log:

 ' Define the database transaction log.

 oLogFile.Name = "XYZLog"

 oLogFile.PhysicalName = DevPath & "\XYZ.ldf"

 oDatabase.TransactionLog.LogFiles.Add oLogFile


 This is where the database is created:

 ' Create the database as defined.

 oSvr.Databases.Add oDatabase
 CreateDatabase = True
  .	
  

Having seen creating of database, let us look at creating of tables using Visual Basic. For our purpose, we again look at the demo sample.

Public Function CreateTables7() As Boolean

'Following is an illustration of how to use SQL-DMO to create tables.'An alternative is to use the Execute method to run a CREATE TABLE script

 .
 First, connect to MSDE:

 'Connect to SQL server
 oSvr.Connect "(local)", "sa", ""

 Define the database objects required:

 'Dim oDatabase As SQLDMO.Database
 Dim oDatabase As Object
 Set oDatabase = CreateObject("SQLDMO.Database")

'Dim tableCategories As New SQLDMO.Table
 Dim tableCategories As Object
 Set tableCategories = CreateObject("SQLDMO.Table")

'Dim colCategoryID As New SQLDMO.Column
 Dim colCategoryID As Object
 Set colCategoryID = CreateObject("SQLDMO.Column")

'Dim colCategoryName As New SQLDMO.Column
 Dim colCategoryName As Object
 Set colCategoryName=CreateObject("SQLDMO.Column")

'Dim colDescription As New SQLDMO.Column
 Dim colDescription As Object
 Set colDescription=CreateObject("SQLDMO.Column")

'Dim colPicture As New SQLDMO.Column
 Dim colPicture As Object
 Set colPicture = CreateObject("SQLDMO.Column")

 Select the database that tables 
 will be added to:

'Get the XYZ database
 Set oDatabase = oSvr.Databases("xyzdb")

Define the columns of the new table:

'Populate the Column objects to define
 the table columns.
colCategoryID.Name = "CategoryID"
colCategoryID.Datatype = "int"
colCategoryID.Identity = True
colCategoryID.IdentityIncrement = 1
colCategoryID.IdentitySeed = 1
colCategoryID.AllowNulls = False
colCategoryName.Name = "CategoryName"
colCategoryName.Datatype = "varchar"
colCategoryName.Length = 15
colCategoryName.AllowNulls = False
colDescription.Name = "Description"
colDescription.Datatype = "text"
colDescription.AllowNulls = True
colPicture.Name = "Picture"
colPicture.Datatype = "image"
colPicture.AllowNulls = True

Set the final table properties:

'Name the table, then set desired properties to 
control eventual table
'construction.
tableCategories.Name = "Categories"
tableCategories.FileGroup = "PRIMARY"

This is where the columns are added to the table:

' Add populated Column objects to
 the Columns collection of the
' Table object.
tableCategories.Columns.Add colCategoryID
tableCategories.Columns.Add colCategoryName
tableCategories.Columns.Add colDescription
tableCategories.Columns.Add colPicture

This is where the table is created:

' Create the table by adding the Table object 
  to its containing
' collection.
oDatabase.Tables.Add tableCategories
.

Solution Deployment

MSDE for Visual Studio comes packaged in InstallShield command line installation program. This program does a silent installation of MSDE on the user's machine though some installation settings can be changed to suit the user's machine.

Using Visual Studio 6.0 there are 2 methods by which you can install MSDE. The 1st method uses the Visual Studio 6.0 Package and Deployment method and the 2nd is done through a custom Visual Basic application.

Since SQL SERVER 7.0 and MSDE use the same database file formats, the database can be detached from one database server and attached to another db server. This is an easy method to distribute MSDE solutions. Again the sample Microsoft VB 6 MSDE installation project is used as an example :

Database files must be detached before they can be attached to another server:

Public Function DetachDatabase(DBName As String) 
As Boolean

'This function detaches a SQL Server or 
MSDE database.
This sample uses late binding. Early
 binding will work as well:
Dim oSvroot As Object
Set oSvroot = CreateObject("SQLDMO.SQLServer")
.

First, make a connection to the database server:

Connect.
oSvroot.Connect "(local)", "sa", ""

The actual database detach is fairly simple when using SQL-DMO. "Dbname" is the name of the database that will be detached and "True" tells the server to skip the update of statistics before detaching (updating statistics provides the database server information about the size and contents of a database):

' Detach
Call oSvroot.DetachDB(DBName, True)
.
Once database files are detached, 
they must be attached:
Public Function AttachDatabase(DBName As String, _
FileName1 As String, Filename2 As String) 
As Boolean
'This function attaches a database file to an SQL
'Server or MSDE server. 
.
Again, the first step is making 
a connection to the database server:
'Connect.
oSvroot.Connect "(local)", "sa", ""
.

There are two database files to attach. One file contains the data (.mdf) and one contains a transaction log (.ldf). Here we pass in the path to each of these files separately:

' Attach.

Call oSvroot.AttachDB(DBName, FileName1 & ", 
" & Filename2)

Data Access Options in MSDE

There are a number of options for accessing data in an MSDE solution. This section looks at the three of these options.

Access

Access client-server applications are created using Access Projects. These Projects use OLE DB to connect to MSDE databases. Microsoft Access front-ends can be converted into database projects using the Access 2000 Upsizing Wizard.

ADO

ActiveX Data Objects (ADO) is an ActiveX programming interface that wraps the OLE DB API. ADO provides data connectivity to Visual Basic applications as well as any application capable of controlling ActiveX in-process servers. ADO can be used to connect a custom Visual Basic application to an MSDE database, access data, and perform administrative functions in the database.

OSQL

OSQL is the newest query tool for accessing SQL server from the command line. It is something which is very similar to ISQL but the difference is that OSQL uses ODBC database application programming interface to connect to the database rather than DB-LIB which is used by ISQL. The DB-Library remains at a SQL Server 6.5 level and hence applications that depend on DB-LIBRARY do not support some SQL Server 7.0 features.

This utility allows you to enter Transact-SQL statements, system procedures and script files.

-U login_id 

Is the user login ID. Login IDs are case-sensitive.

-e

Echoes input.

-E 

Uses a trusted connection instead of requesting a password.

-p 

Prints performance statistics.

-n

Removes numbering and the prompt symbol(>) from input lines.

-d db_name 

Issues a USE db_name statement when osql is started.

-Q "query" 

Executes a query and immediately exits osql. Use double quotation marks around the query and single quotation marks around anything embedded in the query.

-q "query"

Executes a query when osql starts, but does not exit osql when the query completes. (Note that the query statement should not include GO). If you issue a query from a batch file, use %variables, or environment %variables%. For example:

 SET table = sysobjects
 osql /q "Select * from %table%" 

Use double quotation marks around the query and single quotation marks around anything embedded in the query.

-c cmd_end 

Specifies the command terminator. By default, commands are terminated and sent to Microsoft® SQL ServerT by entering GO on a line by itself. When you reset the command terminator, do not use SQL reserved words or characters that have special meaning to the operating system, whether preceded by a backslash or not.

-h headers 

Specifies the number of rows to print between column headings. The default is to print headings one time for each set of query results. Use -1 to specify that no headers will be printed. If using -1, there must be no space between the parameter and the setting (-h-1, not -h -1).

-w column_width 

Allows the user to set the screen width for output. The default is 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.

-s col_separator

Specifies the column-separator character, which is a blank space by default. To use characters that have special meaning to the operating system (for example, | ; & < >), enclose the character in double quotation marks (").

-t time_out 

Specifies the number of seconds before a command times out. If no time_out value is specified, a command runs indefinitely; the default time-out for logging in to osql is eight seconds.

-m error_level 

Customizes the display of error messages. The message number, state, and error level are displayed for errors of the specified severity level or higher. Nothing is displayed for errors of levels lower than the specified level. Use -1 to specify that all headers are returned with messages, even informational messages. If using -1, there must be no space between the parameter and the setting (-m-1, not -m -1).

-I 

Sets the QUOTED_IDENTIFIER connection option on.

-L 

Lists the locally configured servers and the names of the servers broadcasting on the network.

-? 

Displays the syntax summary of osql switches.

-r {0 | 1} 

Redirects message output to the screen (stderr). If you don't specify a parameter, or if you specify 0, only error messages with a severity level 17 or higher are redirected. If you specify 1, all message output (including "print") is redirected.

-H wksta_name 

Is a workstation name. The workstation name is stored in sysprocesses.hostname and is displayed by sp_who. If not specified, the current computer name is assumed.

-P password 

Is a user-specified password. If the -P option is not used, osql prompts for a password. If the -P option is used at the end of the command prompt without any password, osql uses the default password (NULL). Passwords are case-sensitive.

The OSQLPASSWORD environment variable allows you to set a default password for the current session. Therefore, you do not have to hard code a password into batch files.

Migration of Data

From Jet Database to MSDE

We have seen earlier the creation of a new database on MSDE by various methods. We can also move data from the Jet Database to the MSDE Client / Server database. The following will be the steps involved in the migration process:

Duplicate column names are given an alias

Data delimiters are converted

Boolean constants are converted to integers

String concatenation is converted from an ampersand to a plus sign

Wild card characters are converted to their Transact-SQL equivalents

With Ties is added to all TOP queries that contain an ORDER BY clause

Drop Index

Distinct row

Owner access

Table in Union

Order by in Unions

Transform

Parameters

Migration of data from MSDE to SQL Server

Where the applications grow beyond 2 GB data limit, the data would have to be moved to SQL Server. SQL server is fully compatible with the MSDE solution and hence, MSDE can be upgraded directly to any SQL Server by installing SQL Server. Else, the data can be transferred from the MSDE installation to SQL Server using SQL Server 7.0 Enterprise Manager.

Data Base Replication

Database replication is a process where the main data is kept at a different place and a copy of this data is kept in a local machine for the user to access. There are three modes of data replication:

MSDE - A Comparison

Limitations

Though MSDE is a promising database engine, it also has some limitations:

As can be seen, both Access and MSDE have their places in the scheme of things for a developer. If the developer needs some kind of intermediate product which has some of the features like Access with the SQL Server functionality, then MSDE is the choice.

Conclusion

As can be seen, MSDE provides an ideal platform. When developers are faced with the prospect of the database-needs expanding beyond levels that Access can support, MSDE ideally fills in the gap without being too expensive an option. In addition, MSDE provides SQL Server kind of functionality and security, and SQL server support for the databases.