Programming Snapshot or Transactional Replication from Heterogeneous Data Sources

SQL-DMO and the Replication Distributor Interface sample explains how to support a third-party publication. Because this sample uses transactional replication, this sample will work only with Microsoft® SQL Server™ 2000 Standard Edition and SQL Server 2000 Enterprise Edition.

SQL-DMO configures a third-party publication and Distributor using SQL Server. The Replication Distributor Interface logs any transaction that it marks for replication to this Distributor. The third-party publication uses the Distribution Agent to distribute the transactions as if they were coming from an instance of SQL Server.

If the samples were installed to the default location, the files used in this sample will be located in the C:\Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\samppub directory.

To run the sample programs

1. On the computer that will be the Publisher, verify that SQL Server Agent is running. If it is not running, start it.
   
   
2. In SQL Query Analyzer, open \Samples\sqlrepl\samppub\samppub.sql, and then execute it. This script creates two databases, SampleSubscriberDB1 and SampleSubscriberDB2, and sets up a subscription to one of the databases. The script also configures the computer for publishing and distribution, adds a distribution database named distribution, and enables the computer as a Subscriber.
   
   
3. Execute the BAT file iniwkdir.bat, in the in \Microsoft SQL Server\80\Tools\Devtools\Samples\Sqlrepl\Samppub directory, by either double-clicking it in Microsoft Windows Explorer or running it at a command line. The result of the execution creates the working directory at C:\mssql8\repldata\UNC\samppub that contains the files Samptab.sch and Samptab.bcp. These schema and .bcp data files will be used by the Visual C++ application.
   
   
4. On the Publisher, open Visual Basic, open the SQL-DMO application workspace, samppub.vbp, in the samppub\sqldmo directory. Build the application by selecting Make samppub.exe on the File menu. The resulting executable will be named samppub.exe, (the same name as the Visual C++ executable); therefore, it is recommended that this one be kept in the SQLDMO directory or in a directory of your choosing where the two executables can be distinguished from each other.
   
   
5. Run the Visual Basic version of the samppub.exe from inside Visual Basic on the Run menu by clicking Start, or by starting the executable from where it was saved. The application has one form with four buttons.
   
   
6. Click the Create Sample Publication button to create a publication on a third-party vendor. You can view the results in SQL Server Enterprise Manager. Expand Replication, and there will be a new folder called Heterogeneous Publications. There will be a folder called Sample Vendor, with a publication called SamplePublication. You can right-click SamplePublication:SampleDatabase and select Properties to view information about the publication. You will see that there is no subscription to this publication. If the Replication folder was already expanded, click Refresh to show the new objects or close and open SQL Server Enterprise Manager again.
   
   
7. Click the Add Subscription button. This creates a push subscription, with the data going to the SampleSubscriberDB1 database. If you have not clicked the Create Sample Publication button, clicking this button will give an error because there will be no publication on which to add a subscription. After clicking this button, you can view the results in SQL Server Enterprise Manager. Expand Replication, expand Heterogeneous Publications, expand Sample Vendor Right-click SamplePublication:SampleDatabase and select Properties. The Subscriptions tab shows the new subscription.
   
   
8. Open Visual C++, and then open the workspace samppub.dsw in the samppub\repldist directory. On the Tools menu, point to Options, and then click the Directories tab. In the Show directories for box, select Include files and point to the \Microsoft SQL Server\80\Tools\Devtools\Include directory. For the Library setting, navigate to \Microsoft SQL Server\80\Tools\Devtools\Lib. On the Build menu, select Build samppub.exe. The application will be compiled and saved to the samppub\repldist directory.
   
   
9. Run the Visual C++ version of samppub.exe . On the Build menu, click Execute samppub.exe. It can also be executed directly running it from the directory where it was saved. This will deliver transactions to the Distributor. The application will perform Snapshot Agent and Log Reader Agent operations for the publication. When run, messages will appear in the Command window, with the messages:

   Sample Publisher Agent Startup
   Added 1 Transaction(s) consisting of 3 Command(s)
   Sample Publisher Snapshot Agent Succeeded.
   Added 1 Transaction(s) consisting of 30 Command(s)
   Sample Publisher Logreader Agent Succeeded.
   

   After a successful execution, you should see a table SampleTable1 in the SampleSubscriberDB1 database. Opening the table shows 20 rows of data.

10. Optionally, create a push subscription to the SampleSubscriberDB2 from the publication database using SQL Server Enterprise Manager. When creating this subscription, do not create an initial snapshot. Running the Visual C++ program results in transactions being propagated to both subscriptions.
    
    
11. Execute the Visual Basic samppub.exe file from where it was saved, and then click Drop Subscription to drop the push subscription. After clicking this button, you can view the results in SQL Server Enterprise Manager. Expand the SQL Server Group, the Replication folder, Heterogeneous Publications folder, and Sample Vendor. On SamplePublication:SampleDatabase, right-click and select Properties. On the Subscriptions tab is a Properties button that shows the Subscription Properties dialog box. There are no subscriptions showing. If you have not clicked the Create Sample Publication button, clicking this button will give an error, as there will be no subscription to drop because one has not been created. If you have not clicked the Add Subscription button, clicking this button will give an error, as there is no subscription to drop.
    
    
12. Click the Drop Sample Publication button to removes the publication. In SQL Server Enterprise Manager, in the Replication folder, the Heterogeneous Publications folder is removed if this was the only heterogeneous publication on the computer. If you have not clicked the Create Sample Publication button, clicking this button will give an error, as there will be no Publication to drop. The subscription and the publication have been removed.

    Data and schema files were installed on the local computer to a directory created by the iniwkdir.bat. The directory is C:\mssql8\repldata\uncsamppub. They were used by the Visual C++ application as the data copied into the SampleTable1.