Home | More About Me 

...MSDE - Microsoft Data Engine...

Table of Contents




Introduction
This is an attempt to prepare a detailed knowledge base on Microsoft’s Data engine, which is abbreviated as MSDE.

Top

What is MSDE ?

MSDE i.e., Microsoft Data engine, is known as an alternative to Jet for building desktop and shared solutions. MSDE is a fully SQL server 7.0 compatible data engine for building distributed solutions that consists of the SQL server features, reliability and security. The code base written for MSDE need not be changed when migrated to SQL server 7.0.

The MSDE operates as a service. The MSDE services are made on or off by the operating system environment. Hence it doesn’t have a user interface; instead the access 2000 or SQL server’s enterprise manager can talk to this service for its database operations.

With MSDE for VB 6.0, the developers can:

1. MSDE is an attractive option for upgrading jet based database application to improve scalability or for creating new desktop or shared applications that can be easily migrated into SQL server without any change in code base.
2. Develop solutions that are free for distribution.
3. Be tuned to provide optimal performance at up to 5 concurrent users and supports 2 GB of data.
4. Allow merge replications, point in time recovery and dynamic backup and restore.

Note: The MSDE package is not shipped with VB 6 package. It is shipped for free along with the MS Office 2000 professional edition.

The MSDE database is referred to as “Access projects” with .ADP when created in MS Access 2000. The actual location of the database file is into Drive:\MSSQL7\DATA\*.MDF file. The drive could be any one of local hard drives like, C, D etc.

Top


Why do we have to use MSDE?


This is an obvious question to anyone that why do we have to use MSDE database when we have other Microsoft’s options available with us like MS Access 2000 or SQL server 7.0. The reason is,

Though the Access database is relatively inexpensive, they place limits on the database size and performance for concurrent users.

SQL server 7.0 has much higher support for data size and concurrency, but it can be very expensive.

The most important is if you write programs into Access, it is not easy to upgrade it to SQL server.

So the advantage of MSDE is,

Though it is the restricted version of SQL server, the data model is exactly the same as the SQL server. It does have a database size limitation, but it can be upgraded to SQL server without any change in the code.
Top


Features of MSDE

1. Though the MSDE is a data engine, it does not have its own interface for database creation or administration. But this can be achieved by the comprehensive interface of enterprise manager of SQL server 7.0. This is required only to test the MSDE solutions that are created using VB 6.0. The SQL 7.0 server (Developer edition) offers the Enterprise Manager for administrating, creating and managing the database objects, but its developer edition license doesn’t permit its distribution with custom solutions.

However, the MSDE databases can also be created using MS access 2000 user interface or visual studio’s development environment.

2. MSDE is free for distribution.

3. MSDE databases are fully compliant with SQL server technology, i.e., the tables, stored procedures, triggers or any other database objects created in MSDE will operate without modification in the SQL server database. So it offers full features of SQL server.

4. It runs on windows 9X, NT and windows 2000 platform.
Windows NT SP4 (Service pack 4): This is required in case, when you are attempting to install the MSDE for Windows NT in which the service pack version is below 4.

5. It has the client-server architecture. The actual data operations happen on the server and not the client.

6. It runs as a service.

7. It does not include a user interface of its own.

8. The normal data operations can be done through ADO (ActiveX data object).

Top


System requirements for MSDE

1. For developing and using MSDE solutions, client workstation must have office 2000 professional editions (or higher) installed.

2. Must have a connection to an MSDE server either on the respective workstation or over the LAN (Local area network).

3. The MSDE doesn’t have its own database-managing interface. Using interfaces of MS access 2000 or SQL server’s Enterprise Manager can achieve the MSDE database creation.
(So MSDE can create its own database and maintain them but by help of other tools. Please visit, http://msdn.microsoft.com/vstudio/msde/tables.asp for more information)

4. MSDE runs on windows 9X as it, but for Windows NT, it requires Service Pack 4 (SP4) or above.

5. 55 MB free disk space / 64 MB RAM recommended.

6. Intel / alpha platforms.

Top


Working with MSDE databases

As MS access uses Jet engine to create its databases, the MSDE databases uses MSDE as a data engine.

Note: Make sure that the data engine is on to work with MSDE server.

Creating MSDE databases

Since MSDE doesn’t have its own database design interface, user is required to have Enterprise manager of SQL server 7.0 (developer edition) or MS Access 2000 or Visual studio’s development environment to do so.

Creating MSDE database using MS access 2000:

1. The MSDE databases created in MS access 2K are stored as “MS access Projects” (as *.ADP files). The actual MSDE database files are with extension MDF and its transaction log file are with extension LDF.

2. Open MS access 2K.

3. Click on File -> New.

4. Click on the ‘General’ tab, and select ‘Project (New database)’. Once clicked you need to specify the location where you want to store the database. Select the appropriate location.

5. It will open a wizard for you to select the server (SQL server) on which you want to create the database along with the name of the database. Choose appropriate server and name and click on ‘Next’. Once its attempt to create the database is over, click on ‘Finish’.

Creating MSDE database using MS SQL server 7.0:

This is the fastest way to create and maintain the MSDE database through enterprise manager / Transact-SQL statements / SQL – DMO of SQL server 7.0.

For creating and maintaining databases using SQL server, please visit the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_01_116b.asp?frame=true

Note: To work with MSDE programmatically, one can make use of Microsoft SQL-DMO (SQL Distributed Management Objects) library (in VB, it is found in the references).

Top

Communicating with Database:

The database related operations add/delete/edit (DML operations) data from the available tables can be performed using Microsoft’s rich ADO library can be used to execute the SQL commands.

For DDL statements, refer to this example below,
http://msdn.microsoft.com/vstudio/msde/deploying.asp#database, in this DCL statement that creates the database is used.

Top

Deploying MSDE solutions:

Since MSDE is free for distribution, it can be deployed along the Package and deployment wizard of VB 6.0.

Visit below link to know more about it:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q231923
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q234626

Any SQL server database can also be used as an MSDE database by performing the ‘Attach’ and ‘Detach’ SQL-DMO operations. To know more about these operations, please visit the following link:
http://msdn.microsoft.com/vstudio/msde/deploying.asp#database

Top

Back up and Restore of database:

Physical restore and backup of the database:

When the MSDE server is on, the current data modifications or operations are done in the Driver:\MSSQL7\Data\ within the files YourDBName.MDF and YourDBName_log.LDF files. In order to take up the backup, you need stop MSDE server by clicking on ‘Mssqlserver – Stop’ option which one can find by right clicking the MSDE service manager icon in system tray. Once it shows red dot in the service manager, which means it has stopped its services, and closed the data related files. However, it may actually take some more time to do this operation (generally 10 secs). After that one can copy the YourDBName.MDF and YourDBName_log.LDF files physically in their respective backup folders. The change in this file can be noted by the change in the date and time of those files.

Please visit below link for more information:
http://www.vbpj.com/upload/free/features/vbpj/2000/14fal00/ss0014/ss0014.asp

Top

Programmatic backup of the database:

The ‘BackUP’ object within the SQL-DMO object provides a source database name and target device to take the backup of the entire database. It will allow the user to make ‘complete’ backup or ‘differential’ (only that data is taken for backup which has changed after the last backup) backup.

Please visit the following for more information:
http://www.microsoft.com/office/ork/2000/five/75t2_6.htm
(It’s a command line example)
http://www.15seconds.com/howto/pg000945.htm

Top

Compress/Decompress Database:

The database can be compressed/ decompressed by the T-SQL statements, The SQL statement could be like following,

DBCC SHRINKDATABASE
( database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}]
)

Arguments:
It shrinks the size of the data files in the specified database.
database_name:
Is the name of the database to be shrunk. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.
target_percent:
Is the desired percentage of free space left in the database file after the database has been shrunk.
NOTRUNCATE:
Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.
TRUNCATEONLY:
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target percent is ignored when TRUNCATEONLY is used.

For e.g., DBCC SHRINKDATABASE (NameOfDB, 5,TRUNCATEONLY)

Useful Link:
http://www.sourcecode.co.nz/shrinkdbs/index.htm

Top

Linking Database

Linking database provides you a way to connect to a database of different source within your own data source environment. For e.g., the data operations can be done between an Access database (*.MDB file) and MS SQL Server 7.0 database and vice-versa. Since MSDE database has 100% compatibility with SQL server 7.0, it is also capable of generating linked servers.

Transact-SQL statements are helpful in creating distributed queries, which can retrieve data from any OLE DB data source.

The OLE db data source could be any of the following:
· MS SQL Server 7.0.
· MS Access.
· Oracle.
· OLAP.
· ODBC drivers.
· Or any Text file or MS excel sheet.

The T-SQL procedures used are,
· sp_addlinkedserver
· sp_addlinkedsrvlogin
· OPENQUERY
· OPENROWSET

*************
Note:
1. The MDB file (or for that matter any database file) which you want to add into MSDE database, that file must be residing on the same MSDE server where you want to add it as a linked server.
2. If step 1 is not followed, the OLE DB provider gives error that ‘The specified database file could not be found.
*************

Top

Procedure to create linked servers

1. sp_addlinkedserver:

Before you communicate to a database on different data source, you need to create its linked server.

The sp_addlinkedserver has following parameter syntax:

sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]

Linked server for Access DB:

Assume that MSDE server is running on machine ‘ABC’, then the syntax would be,
EXEC sp_addlinkedserver
   @server='myacc',
   @srvproduct='',
   @provider='Microsoft.Jet.OLEDB.4.0',
   @datasrc='F:\Priya\mydb.mdb',
   @provstr=NULL,
   @catalog=NULL

@server is the name of the linked database,
@datasrc is the location of the MDB file (Make sure that the MDB file resides on the same server as ‘ABC’ in this case),
@Provider is the name of the OLE DB data source etc.

Linked server for SQL Server 7 DB:

EXEC sp_addlinkedserver @server='MySQL’,
   @srvproduct='',
   @provider='SQLOLEDB',
   @datasrc='comp5',
   @catalog='TP'
go

For more information on its argument listing, please visit the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
The above link also tells you how to connect to other OLE DB data sources.

Run this SQL statement and then go to Step 2. If the above named linked server already exists, then one can directly go to step 3.

Top

2. sp_addlinkedsrvlogin:

This stored procedure is used to create login mappings from local logins to MS Access (or any other database) logins.

The sp_addlinkedsrvlogin has the following syntax:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
   [ , [ @useself = ] 'useself' ]
   [ , [ @locallogin = ] 'locallogin' ]
   [ , [ @rmtuser = ] 'rmtuser' ]
   [ , [ @rmtpassword = ] 'rmtpassword' ]

For more information on its argument meaning, please visit the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp

For e.g.,

In case of MS ACCESS DB,

sp_addlinkedsrvlogin 'myacc', 'FALSE', NULL, 'Admin', Null.

In case of SQL server,

sp_addlinkedsrvlogin 'mysql', 'FALSE', NULL, 'sa', ‘’.

Top

3. Executing the query:

Once the linked servers are created, the query can be created in 3 using following:

a. OPENQUERY:

This executes the specific query using linked server. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

The syntax of the OPENQUERY is as follows:

OPENQUERY (linked_server name, 'query')

For e.g.,

To retrieve data from MS ACCESS database will be,

Select * from OPENQUERY(myacc, 'Select * from myTab')

Where ‘myacc’ is the name of the linked server and we are retrieving records from ‘mytab’ table.

For more information on OpenQuery, please visit the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp

Top

b. OPENROWSET:

OPENROWSET is another method by which one can retrieve data from another data source. It does not require any linked server as such.

The syntax is as follows:

OPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } )

For e.g.,

SELECT * From OPENROWSET ('Microsoft.Jet.OLEDB.4.0','F:\Priya\mydb.mdb';'admin';'', MYTAB)

For more information on Openrowset, please visit following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp

Top

c. Using 4 part name syntax:

This requires the linked server.

The syntax is as follows,

Select * from (linked server name)…(table name)

For e.g.,

Select * from MYACC...MYTAB.

This query works for only SQL server 7.0 version.

Top

Procedure to delete DB link

The created linked server can be deleted through ‘Enterprise Manager’ by performing the step-by-step procedure as given below:

· Expand a server group, and then expand a server.

· Expand Security, and then expand Linked Servers.

· Right-click the linked server to delete, and then click Delete.

· Confirm the deletion.

For more information, please visit following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_46nt.asp

Delete using stored procedure:

Sp_dropserver will drop the specified linked server.

The syntax is as following:
sp_dropserver [ @server = ] 'server' [ , [ @droplogins = ] { 'droplogins' | NULL} ]

For eg,
exec sp_dropserver 'myacc'

For more information, please visit the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_drop_9c1e.asp

Other useful links on linked servers:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246255

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q200797

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q218592

Top

Download Example in VB

Download VB Code
VB code + Docs

The above MSDE example has been created to get familiar with its coding and data operation.

The example is created in VB and it will show you the following operation:

1. The database file TestProjSQL.mdf is created using MS Access 2000.
2. Attach database to MSDE engine.
3. Transactions – Add/delete.
4. Detaching the database.
5. Knowing the databases and tables within the given MSDE server.
6. Taking backup/restore of the database. (Note is added for DB restore/backup in readme.txt file.)

Note: Please make sure that you read Readme.txt file before executing the project.

Top

Migration of SQL Server database to MSDE database

1. MSDE is fully compatible with SQL server 7.0, i.e., all the tables, stored procedures, views used within SQL server can be used for MSDE without any change in the code written to access them.

2. If the SQL server 7.0 needs to be installed on the same machine of MSDE, then one needs to take care of some of the registry entries, which may hinder the installation of SQL server. These 2 registry entries need to be cleared BEFORE installation of SQL server (If it is planned to install SQL server on MSDE machine.)
These entries are located at,
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Setup\SQLdataroot
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Setup\SQLdatapath.

3. Service pack of SQL server is both designed for SQL server and MSDE and while installing the service pack; it will recognize the MSDE installation and will install only those files pertaining to MSDE.

4. The OLE DB and ODBC drivers that work for SQL server will work for MSDE as well.

5. Programmatic access to both these servers is through SQL-DMO object model. Hence the same lines of code can be used for both of these.

6. For more information on the comparison between MSDE and SQL server, please look at the following table:
http://www.sqlmag.com/Files/09/7840/Table_01.html

Features that are not fully supported in MSDE:

1. Replication with MSDE:
With the enterprise edition of SQL server, all types of replication is possible. However, with MSDE, replication is only possible if SQL server client access licenses (CAL'S) are purchased. However, a desktop edition of SQL server is recommended over MSDE for replication (CAL is also required for SQL server desktop.). Also MSDE doesn’t act as publisher for transactional replication.

2. The maximum database size of MSDE is 2GB and for SQL server is in terabytes, hence for larger data solution, MSDE is not suitable.

3. Multi-user performance of SQL server is better than MSDE, i.e., the MSDE performance comes down if there is more than 5 concurrent users work on it. Hence if the server load becomes more it is good to migrate to SQL server database.

4. MSDE doesn’t have its own Interface to work with its database components neither it has Query analyzer to work with queries, hence one need to have Access 2000 installed. Where SQL server has SQL enterprise manager to work with database.

5. MSDE doesn’t act as an online analytical processing (OLAP) server.

Top

References on MSDE

Complete guide on MSDE

http://msdn.microsoft.com/vstudio/msde/default.asp
The information is also available in MSDE library 2000 CD.

External references

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7840

http://www.vb-helper.com/tip_msde.html

http://www.computer-consulting.com/MSDE.htm

http://www.advisor.com/Articles.nsf/aid/GROHM105-2

http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=383

Top


 Home | More About Me 

©All rights reserved.