How to use replication with SQL Server 2000 Desktop Engine (MSDE 2000)

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.

Replication-specific 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 the publication.

MSDE 2000-specific 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:
  325023  (http://support.microsoft.com/kb/325023/ ) 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.

Licensing issues

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:

Managing replication 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.

As mentioned 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 objects
• 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:

1. Open Windows Synchronization Manager: Click Start, click Programs, click Accessories, and then click Synchronize.
2. In the Create New Subscription window, you have the following three options: (To manually add a subscription, use the third option.)
   • Browse the active directory.
   • Attach a SQL Server subscription database.
   • Manually specify the publication and subscription information.
3. In the Create Anonymous Subscription window, type the subscription and the publication information.
4. Click OK, and the subscription appears under Microsoft SQL Server 2000 the next time you open Windows Synchronization Manager.
5. To synchronize the subscription, click the subscription that you want to synchronize, and then click Synchronize.

NOTE: 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.

After you 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:

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 replication.

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 scripts. For additional information about OSQL, click the following article number to view the article in the Microsoft Knowledge Base: 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.

1. From the MSDE 2000 Subscriber, use OSQL to connect to the subscribing database.
2. Add the anonymous subscription by using the sp_addmergepullsubscription stored procedure.
3. Add the merge agent job by using the sp_addmergepullsubscription_agent stored procedure.
4. Start the job by using the sp_start_job stored procedure.

NOTE: 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 parameter.

For more information about these stored procedures, see the following topics in SQL Server 2000 Books Online:

• "sp_addmergepullsubscription"
• "sp_addmergepullsubscription_agent"
• "sp_start_job"
• "sp_update_job"

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 Server.

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:

1. 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 Books Online.
2. 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: For additional information about MSDE 2000, click the following article numbers to view the articles in the Microsoft Knowledge Base: