Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is a
redistributable version of the SQL Server 2000 relational database engine. By
using MSDE 2000, application developers have a means by which to distribute
their application and a database in which to store the data. Additionally, MSDE
2000 supports replication. This is a very useful feature that application
developers can use particularly if users are disconnected and want to merge
their local copy of the data with a central server. However, MSDE 2000
replication includes certain limitations and caveats. This article describes
those caveats for MSDE 2000 replication and also provides information about how
to manage replication with MSDE 2000.
When you are using replication
with regular editions of SQL Server, all SQL Server and replication features
are available. MSDE 2000 can replicate between all versions of SQL Server;
however, when you are using MSDE 2000 as part of the replication topology,
there are certain limitations. Some of these limitations occur because of
inherent limitations of MSDE 2000 and certain others are design limitations
with respect to MSDE 2000 and replication.
limitations and caveats
- MSDE 2000 can act as both a Publisher or Distributor, and a
Subscriber, and it can replicate between MSDE versions and regular versions of
SQL Server. Additionally, you cannot use it for transactional publication, but
you can use it as a Publisher of snapshot replication, and merge replication,
and as a Subscriber to all three types of publications.
- You cannot use remote Distributors with MSDE 2000. An MSDE
2000 Publisher must use the same server as the Distributor.
- The Repldata folder is not created during MSDE 2000 Setup,
which generates an error when you are trying to set up a publication by using
the default snapshot folder. To use the default snapshot location, create a
Repldata folder under the MSSQL or MSSQL$InstanceName folder, and then create
limitations and caveats
- MSDE 2000 limits the size of the database to 2 GB. If MSDE
2000 is part of the replication topology, the size of the replicated database
is limited to 2 GB. If MSDE 2000 is the Subscriber, replication agents fail
after the database exceeds 2 GB. At that point, consider upgrading the
Subscriber to a regular SQL Server edition.
For additional information about how to upgrade
from MSDE 2000 to a regular SQL Server edition, click the following article number to view the article in the Microsoft Knowledge Base:
Upsize SQL Server 2000 Desktop Engine to SQL Server
- The number of connections to MSDE 2000 is optimized for
five connections. This may affect the performance of replication if MSDE 2000
is the Publisher and there are a enough Subscribers to the publication or if it
is a Subscriber to multiple publications.
Using MSDE 2000 as a Publisher or Distributor
When MSDE 2000 is part of the replication topology, it can act as
either the Publisher or Distributor, or as the Subscriber. When you are using
MSDE 2000 as the Publisher or Distributor, consider the following limitations:
- The Distributor and the Publisher are always be the same
computer. If the publication is a merge publication multiple threads are
spawned on the Publisher or Distributor side to do merge processing. And if
there are multiple Subscribers to the publication the number of threads spawned
on the Publisher increases many fold. This may lead to performance issues
because of the inherent limitations in MSDE 2000.
- In active replication environment where there are a large
number of Subscribers, Microsoft recommends that you use the regular SQL Server
edition as the Publisher or Distributor instead of MSDE 2000.
Using MSDE 2000 as a Subscriber
MSDE 2000 is primarily used in disconnected scenarios. Because of
this, MSDE 2000 typically acts a Subscriber to a merge publication in a
replication topology. Typical problems that may occur when you use MSDE 2000 as
a Subscriber are:
- The subscribing database exceeds the 2GB limit.
- Using multiple Subscribers may result in the overloading of
MSDE 2000 and throttling of MSDE 2000.
MSDE 2000 does not require any licensing when you use it in a
stand-alone mode, but when you use it as part of a replication topology that
involves regular SQL Server editions, if the server is in Per Seat mode as
opposed to a Per Processor mode, each MSDE 2000 Subscriber must have a
client-access license (CAL).
For more information about SQL Server
licensing, visit the following Microsoft Web site:
with MSDE 2000
In regular SQL Server editions, you manage replication by using
SQL Server Enterprise Manager. The task of creating the publication and
subscriptions are typically performed by using the replication user interface
in SQL Server Enterprise Manager. Because MSDE 2000 does not include SQL Server
Enterprise Manager, you must administer and manage replication by using custom
programs. If SQL Server Enterprise Manager is available on any computer, and
you can register MSDE 2000 in SQL Server Enterprise Manager, you can manage
replication by using SQL Server Enterprise Manager.
earlier, MSDE 2000 can act as a Publisher or a Subscriber, and in most
scenarios, it is used as a Subscriber. The following section describes how to
create and to manage MSDE 2000 subscriptions.
How to create a subscription to MSDE 2000
There are a several ways to create a subscription to an MSDE 2000
Subscriber. The following methods are based on the assumption that MSDE 2000 is
not registered in any SQL Server Enterprise Manager:
- Windows Synchronization Manager
- Replication ActiveX Controls
- Managing replication by using SQL-DMO replication
- Using the OSQL command-line utility to add replication jobs
Windows Synchronization Manager
Windows Synchronization Manager is a utility that is available
with Microsoft Windows 2000 and on any computer that is running Microsoft
Internet Explorer 5.0. You can use it to synchronize or to distribute data
between instances of Microsoft SQL Server 2000 when you are using snapshot
replication, transactional replication, or merge replication. For more
information about Windows Synchronization Manager, refer the "Windows
Synchronization Manager" topic in SQL Server 2000 Books Online.NOTE
: When you use Windows Synchronization Manager, you can only
create an anonymous pull subscription.
To create a subscription:
- Open Windows Synchronization Manager: Click Start, click Programs, click Accessories, and then click Synchronize.
- In the Create New Subscription window, you have the
following three options: (To manually add a subscription, use the third
- Browse the active directory.
- Attach a SQL Server subscription database.
- Manually specify the publication and subscription
- In the Create Anonymous Subscription window, type the
subscription and the publication information.
- Click OK, and the subscription appears under Microsoft SQL Server 2000 the
next time you open Windows Synchronization Manager.
- To synchronize the subscription, click the subscription
that you want to synchronize, and then click Synchronize.
: If you have the publication listed in Active Directory, or you
can create attachable subscriptions for the publication, use one of the first
two options. For more information about Active Directory publication and
attachable subscriptions, see the "Active Directory Services" and "Attach
Subscription" topics in SQL Server 2000 Books Online.
create the subscription, you can manage it from Windows Synchronization Manager
by clicking Properties
. By doing so, you can re-initialize the subscription, drop the
subscription, and perform other changes.
For additional information about Windows
Synchronization Manager, click the following article number to view the article in the Microsoft Knowledge Base:
How to use the Windows Synchronize Manager with Pull subscribers
Replication ActiveX controls
In most cases, MSDE 2000 is used as a server for applications
that are deployed on user computers. In such cases where replication is
required, you can use Replication ActiveX controls in the application to manage
replication to that MSDE 2000 Subscriber.
The application that you
are distributing can use the replication ActiveX objects to create
subscriptions to a merge, transactional, or snapshot publication. Additionally,
you can use the methods and properties of these objects to manage these
subscriptions. For example, if you are deploying a Microsoft Visual Basic
application, and you must replicate data to the main server that is running SQL
Server, you can include a piece of code in the application that creates the
subscription, and then performs the synchronization.
To view sample
applications that demonstrate how to use the Replication ActiveX controls to
create and to manage subscriptions to merge transactional and snapshot
replication, see the "Developing Replication Applications Using ActiveX
Controls" topic in SQL Server 2000 Books online.
Managing replication by using SQL-DMO replication objects
SQL Distributed Management Objects (SQL-DMO) is a collection of
objects that encapsulate Microsoft SQL Server database and replication
management. You can create an application by using Microsoft Visual C++ or
Microsoft Visual Basic, and then use SQL-DMO objects to set up and to manage
For more information about SQL-DMO and developing
applications by using SQL-DMO, see the "Developing SQL-DMO Applications" topic
in SQL Server 2000 Books Online.
Using the OSQL
command-line utility to add replication jobs
OSQL is a command-line utility that the MSDE 2000 installation
includes. You can use this tool to connect to SQL Server and to run queries and
For additional information
about OSQL, click the following article number to view the article in the Microsoft Knowledge Base:
How to manage the SQL Server Desktop Engine (MSDE 2000) by using the Osql utility
Additionally, you can create pull subscriptions to
SQL Server Publications directly by using Transact-SQL statements. When you are
creating the subscription by using stored procedures, a job is created on the
Subscriber. Because MSDE 2000 does not include client tools, you must use
stored procedures to stop and to start the job. NOTE
: This example is based on the assumption that the subscription is
anonymous, and that it is for a merge publication.
- From the MSDE 2000 Subscriber, use OSQL to connect to the
- Add the anonymous subscription by using the sp_addmergepullsubscription stored procedure.
- Add the merge agent job by using the sp_addmergepullsubscription_agent stored procedure.
- Start the job by using the sp_start_job stored procedure.
sp_addmergepullsubscription @publication = 'pubs',@publisher = 'fastnfurious' ,@publisher_db = 'pubs'
,@subscriber_type = 'anonymous'
sp_addmergepullsubscription_agent @name = 'MSDE Sub' ,@publisher = 'fastnfurious' ,@publisher_db = 'pubs'
,@publication = 'pubs' ,@publisher_security_mode = 0,@publisher_login = login,@publisher_password = 'strongpassword'
,@subscriber = 'gash2ksrv' ,@subscriber_db = 'sub' ,@subscriber_security_mode = 0 ,@subscriber_login = 'login'
,@subscriber_password = 'strongpassword' ,@distributor = 'fastnfurious'
,@distributor_security_mode = 0,@distributor_login = 'login'
sp_start_job @job_name ='MSDE Sub'
: This code does not include any parameters for controlling the
job schedule. Additionally, there is no procedure setup to determine the status
of the job. If you want to have the job status written to the event log, modify
the job by using the sp_update_job
stored procedure, and then set the @notify_level_eventlog
For more information about these stored
procedures, see the following topics in SQL Server 2000 Books Online:
The methods that are described earlier in this article describe
various ways to manage an MSDE 2000 subscription. Apart from these methods, if
the MSDE 2000 Subscriber is registered in SQL Enterprise Manager, you can
administer the MSDE 2000 Subscriber (Publisher) like any other instance of SQL
Managing Distributor and Publisher
MSDE 2000 can act as a Publisher and as a Distributor.
The steps to create a Distributor and a Publisher in MSDE 2000 are similar to
the steps in the regular editions of SQL Server:
- If the server is registered in SQL Enterprise Manager, you
can configure the Distributor and Publisher by using the Replication Wizard.
For more information, see the "Replication Wizards" topic in SQL Server 2000
- If client tools are not available, you can use the OSQL command-line utility and stored procedures, or SQL-DMO to
configure the Distributor and the Publisher.
To download an updated version of SQL Server 2000 Books
Online, visit the following Microsoft Web site:
For additional information about how to use replication ActiveX controls with Microsoft Visual
Basic .NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:
How to program the SQL Snapshot and SQL Distribution control by using Visual Basic .NET
How to program the SQL Merge control by using Visual Basic .NET
For additional information about MSDE 2000, click the following article numbers to view the articles in the Microsoft Knowledge Base:
How to connect to Microsoft Desktop Engine
How to back up a Microsoft Data Engine database with Transact-SQL