Managing Replication with SQL Server 2000 Desktop Engine (MSDE 2000)
In other editions of Microsoft® SQL Server™, replication is typically managed with SQL Server Enterprise Manager. You can only use SQL Server Enterprise Manager with MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition, Standard Edition, or Enterprise Edition), and if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000. If you do not have or cannot use SQL Server Enterprise Manager, you must configure and maintain replication using other methods. This topic describes how to use these methods to create a subscription; you can use similar methods to create a publication.
How to create a subscription with MSDE 2000
There are a several ways to create a subscription for an MSDE 2000 Subscriber.
- Using the osql command-line utility to add replication jobs
- Managing replication by using SQL-DMO replication objects
- Replication ActiveX® controls
- Windows Synchronization Manager
The osql Utility
osql is a command-line utility included with MSDE 2000. osql allows you to connect to SQL Server to run queries and scripts. You can create pull subscriptions to SQL Server publications by executing stored procedures through osql. The following example demonstrates creating an anonymous subscription to a merge publication:
- From the MSDE 2000 Subscriber, use osql to connect to the subscribing database.
- 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.
The code for steps 2-4 will look similar to the following:
sp_addmergepullsubscription @publication = 'pubs' ,@publisher = 'MyDistPub' ,@publisher_db = 'Northwind' ,@subscriber_type = 'anonymous' sp_addmergepullsubscription_agent @name = 'MySubAgent' ,@publisher = 'MyDistPub' ,@publisher_db = 'pubs' ,@publication = 'MyPublication' ,@publisher_security_mode = 1 ,@subscriber = 'MySub' ,@subscriber_db = 'sub' ,@subscriber_security_mode = 1 ,@distributor = 'MyDistPub' ,@distributor_security_mode = 1 sp_start_job @job_name ='MySubAgent'
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.
SQL-DMO Replication Objects
SQL Distributed Management Objects (SQL-DMO) is a collection of objects that encapsulate SQL Server database and replication management. You can create an application by using Microsoft Visual C++® or 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 Developing SQL-DMO Applications.
Replication ActiveX Controls
In most cases, MSDE 2000 is used 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 want to replicate data from the database on the user's computer 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, see Developing Replication Applications Using ActiveX Controls.
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 SQL Server 2000 when you are using snapshot replication, transactional replication, or merge replication. For more information, see the Windows Synchronization Manager.
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, select Programs, select Accessories, and then click Synchronize.
- In the Items to Synchronize dialog box, select the node in the tree labeled To create a subscription: select this, then click Properties.
- In the Create New Subscription dialog box, you have the following options:
- Browse the active directory.
- Attach a SQL Server subscription database.
- Manually specify the publication and subscription information.
To manually add a subscription, select the third option.
- Browse the active directory.
- In the Create Anonymous Subscription window, enter the subscription and 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.
Note If the publication is 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 topics Active Directory Services and Attachable Subscription Databases.
After creating 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.