Share via

Sales Orders Sample for Merge Replication

  • Article

This readme file details how to install and run the Sales Orders sample. This file also details any known issues with the sample.

Scenario

This sample highlights a merge replication topology for delivering data to mobile users, and it also demonstrates the programmability features of merge replication in SQL Server 2005. The sample is a Windows Forms-based application that uses standard Microsoft data access technologies and merge replication to enable a salesperson to maintain her own local data while synchronizing periodically with the home office. This sample is supported for Subscribers running all editions of SQL Server 2005. The publication also supports SQL Server Compact Edition Subscribers. For more information about this sample scenario, see Sales Orders Sample Scenario.

Languages

  • C#
  • Transact-SQL

Features

The Sales Orders sample demonstrates the following features of merge replication:

  • Replication Management Objects (RMO).
  • Parameterized row filters.
  • Precomputed partitions.
  • Client-requested partitioned snapshot.
  • Article type-based performance optimizations.
  • SQL Server Compact Edition and Express Edition Subscribers.
  • Automatic identity range management.
  • Business logic handlers.
  • Web synchronization (optional).

For more information about how these replication features are demonstrated in the sample, see Sales Orders Sample Implementation Details.

Prerequisites

Before running this sample, make sure the following software is installed:

  • SQL Server 2005 Service Pack 1 (SP1), including the following optionally installed components:

    • SQL Server Database Services (including replication)
    • Client Connectivity Components
    • Programming Models
    • Development Tools (including SDK)
    • AdventureWorks Sample OLTP Database. This sample database is included with SQL Server 2005 and is also available at the SQL Server Developer Center.
    • The SQL Server 2005 samples. These samples are included with SQL Server 2005 and are also available at the SQL Server Developer Center. For more information, see Installing Samples.

  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. For more information, see Installing the .NET Framework SDK.

    Note

    To generate the initial snapshot, you must have SQL Server 2005 SP1 applied at the Publisher. To use this sample on a SQL Server 2005 Publisher without SP1 applied, you must first drop and re-create the FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID FOREIGN KEY constraint on the SalesOrderHeader table without the ON DELETE CASCADE clause.

Required Permissions

The permissions needed to configure and run the Sales Orders sample are based on replication security requirements, and are as follows:

  • To run InstallSalesOrdersSample.bat, which configures replication, creates the AdvWorksSalesOrders publication, and generates the initial snapshot, your Windows account must be a member of the sysadmin fixed server role at the Publisher.

  • The Sales Orders sample does various Database Engine and replication tasks, including creating the AdventureWorksLocal database, creating the local Subscription, and running the Merge Agent to initialize, reinitialize or synchronize the subscription. In order to successfully run the sample executable (SalesOrders.exe) at the Subscriber, your Windows account must have at least CREATE DATABASE permissions at the Subscriber.

    Note

    When the local database and subscription exists, your Windows account must be at least a member of the db_owner fixed database role on the AdventureWorksLocal database at the Subscriber to run the application.

  • To initialize the subscription and synchronize with the Publisher, the Windows account used to connect to the Publisher (or with the Web server when using Web synchronization) must meet the following requirements:

    • Have a valid login at the Publisher associated with a user in the publication database.
    • Have a valid login at the Distributor associated with a user in the distribution database (of the guest user).
    • Be a member of the publication access list (PAL) for the AdvWorksSalesOrders publication.

Building and Configuring the Sample

To build the sample project and configure the replication topology, you will need to do the following:

  • At the Publisher, run the replication installation script.
  • (Optional) Configure the Internet Information Services (IIS) server to support Web synchronization for merge replication. If this is not done, you cannot use the Web synchronization feature of the sample.
  • At the Subscriber, modify the configuration settings in the app.config file of the sample.
  • At the Subscriber, build the sample using Visual Studio 2005 or the .NET Framework 2.0 SDK.
  • At the Subscriber, place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\90\COM directory. When using Web synchronization, you must also place a copy of BusinessLogic.dll in the C:\Program Files\Microsoft SQL Server\90\COM directory on the Web server.

To configure the Sales Orders sample at the Publisher

  1. Ensure that the AdventureWorks sample database has been installed and the database is attached. If the database is not attached, see Installing AdventureWorks Sample Databases and Samples in SQL Server Books Online.

  2. Ensure that the SQL Server Database Engine samples have been installed.

    For more information, see Installing Samples.

  3. Ensure that SQL Server Agent is running. You can view the status of and start this service from Object Explorer in SQL Server Management Studio.

  4. Navigate to the installation location for the Sales Order sample scripts. The default is C:\Program Files\Microsoft SQL Server\90\Samples\Replication\Merge\SalesOrders\Scripts.

  5. (Optional) Configure Secure Sockets Layer (SSL) at the Publisher or at the server used for Web synchronization. SSL is required for communication between the IIS server and all Subscribers. To configure SSL, specify a server certificate for the IIS server to use.

  6. Execute InstallSalesOrdersSample.bat and when prompted enter the login (in the case-sensitive format "DOMAIN\login") and password for the Windows account under which the Snapshot Agent job will run. This calls sqlcmd.exe to execute CreateAdvWorksSalesOrdersPublication.sql, which does the following:

    • Checks that replication has been configured on the local server, and if not configures it as a Publisher with local Distributor using the distribution database.
    • Drops and re-creates the stored procedures and user-defined functions used for data access at the client.
    • Drops and re-creates the AdvWorksSalesOrders Publication.
    • Adds articles to the publication.
    • Registers the business logic handler at the Distributor.
    • Starts the Snapshot Agent job to generate the initial snapshot.

    The batch file can also automatically configure a virtual directory at the Publisher to support Web synchronization. If you choose not configure the virtual directory at the Publisher during installation, or if you plan to use a separate Web server, you must run the Configure Web Synchronization Wizard prior to using the Web synchronization features of the sample.

  7. (Optional) Run the Configure Web Synchronization Wizard to configure an IIS server to support Web synchronization. Skip this step if InstallSalesOrdersSample.bat successfully created the virtual directory or if you will not use Web synchronization. For complete instructions about using this wizard, see How to: Configure IIS for Web Synchronization.

  8. Before continuing, ensure that the Snapshot Agent job for the AdvWorksSalesOrders publication has successfully completed. The status of this job can be viewed using Replication Monitor. For information about using Replication Monitor to view the status of a Snapshot Agent job, see the topic How to: View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor) in SQL Server Books Online.

To build and configure the Sales Orders sample at the Subscriber

  1. At the command prompt, navigate to the SQL Server 2005 samples installation directory. The default is C:\Program Files\Microsoft SQL Server\90\Samples.

  2. Execute the following at a .NET Framework or Microsoft Visual Studio 2005 command prompt:

    sn -k SampleKey.snk

    Note

    You can skip this step if this key was previously created for use with another sample.

  3. Navigate to the installation location for the Sales Orders sample solution in Microsoft Visual C#. The default is C:\Program Files\Microsoft SQL Server\90\Samples\Replication\Merge\SalesOrders\CS.

  4. Open the SalesOrders.sln solution file in Visual Studio 2005.

  5. Build the project.

  6. In the project output folder, edit the SalesOrders.exe.config file and change the following:

    • Replace the value of the Subscriber key with the name of the local server.
    • Replace the value of the Publisher key with the name of the server configured previously as the Publisher.
    • (Optional) Replace the value of the WebSynchronizationUrl key when connecting to the Publisher using Web synchronization. This can also be done at run time.
    • (Optional) Use the SyncWhenConnectedInterval key to set the interval between synchronizations, in seconds, when using the synchronize-when-connected functionality.

    Note

    Replication does not support localhost for server names.

  7. Place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\90\COM directory.

  8. (Optional) If using Web synchronization, place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\90\COM directory on the Web server.

Running the Sample

To run the Sales Orders sample and synchronize data

  1. Ensure that remote connections are enabled at the Publisher and at the Subscriber (TCP/IP or Named Pipes) and that the SQL Server Agent service is running.

  2. (Optional) At the Subscriber, test the Web synchronization connection by connecting to the Web server in diagnostic mode following the instructions on "To configure client certificates at the Subscriber" in How to: Configure Web Synchronization for Merge Replication (Replication Transact-SQL Programming).

    Note

    If an HTTP error occurs when using Web synchronization, open Internet Explorer, and from the Tools menu select Internet Options, Connections, and LAN Settings. Ensure that Automatically detect settings is disabled.

  3. Start SalesOrders.exe from Visual Studio 2005 or from the command prompt. When the application runs for the first time, the subscription database and subscription are created and the Merge Agent is started to initialize the subscription.

  4. After the subscription data has been initialized, the Sales Orders form will load, displaying a list of existing orders that belong to salesperson "adventure-works\garrett1".

  5. Using the Sales Orders form, you can make the following changes to sales data:

    • Edit sales order information.
    • Add a new item to a sales order.
    • Delete an item from a sales order.
    • Change the ordered quantity for an item in a sales order.

  6. Select a synchronization method from the Synchronize menu to synchronize the subscription.

  7. (Optional) Check Synchronize When Connected to automatically synchronize the subscription. Set the synchronization interval for background synchronization using the SyncWhenConnectedInterval key in the app.config file.

    Note

    The Synchronize When Connected feature is not available when running on Microsoft Windows 2000 platforms. For more information, see Sales Orders Sample Implementation Details.

To use advanced synchronization options of the Sales Orders sample

  1. From Advanced in the Synchronize menu, choose one of the following options:

    • (Optional) Select a reinitialize option to reinitialize the subscription.

    • (Optional) Select Web Synchronization Options and supply the Internet URL and the Windows Authentication credentials used to access the Web server over a secure HTTPS connection. To use this option, you must have already configured an IIS server for Web synchronization.

      Note

      Upload-only or download-only synchronization is not supported with Web synchronization. When Web synchronization is enabled, the Only Upload Data menu item is disabled.

    • (Optional) Select Get Subscription Status to view the results the most recent Merge Agent sessions.

Removing the Sample

Use the following procedure to remove the Sales Orders sample.

To delete the subscription only

  1. At the Subscriber, execute the following Transact-SQL script to delete the pull subscription.

    -- This batch is executed at the Subscriber to remove 
-- the Sales Orders sample subscription.
DECLARE @publisher AS sysname;

-- change this value to the name of the Publisher server.
SET @publisher = N'PUBSERVER'; 

USE [AdventureWorksLocal]
EXEC sp_dropmergepullsubscription 
  @publisher = @publisher, 
  @publisher_db = N'AdventureWorks',
  @publication = N'AdvWorksSalesOrders';
GO

  2. At the Publisher, execute the following Transact-SQL script to unregister the pull subscription.

    -- This batch is executed at the Publisher to remove 
-- the Sales Orders sample subscription.
DECLARE @subscriber AS sysname;

-- change this value to the name of the Subscriber server.
SET @subscriber = N'SUBSERVER'; 

USE [AdventureWorks]
EXEC sp_dropmergesubscription 
  @publication = N'AdvWorksSalesOrders', 
  @subscriber = @subscriber, 
  @subscriber_db = N'AdventureWorksLocal';
GO

To remove the Sales Orders sample

  1. At the Publisher, navigate to the installation location for the Sales Order sample scripts. The default is C:\Program Files\Microsoft SQL Server\90\Samples\Replication\Merge\SalesOrders\Scripts.

  2. Execute the batch file RemoveSalesOrdersSample.bat.

  3. When prompted, type "Y" and press Enter to remove all replication objects at the Publisher, remove all replication objects in the subscription database, and uninstall publishing and distribution at the Publisher.

    Important

    When using this script, all replication infrastructure will be removed. If you have created other publications that use this Publisher or Distributor, do not execute DropAdvWorksSalesOrdersPublication.sql. Instead, you should remove the AdvWorksSalesOrders publication manually. For more information, see How to: Delete a Publication (SQL Server Management Studio) or How to: Delete a Publication (Replication Transact-SQL Programming) in SQL Server Books Online.

  4. At the Subscriber on the AdventureWorksLocal database, execute sp_removedbreplication (Transact-SQL).

  5. Change the database context to the master database and execute DROP DATABASE AdventureWorksLocal.

Comments

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications and assemblies should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

See Also

Concepts

Replication Samples

Other Resources

Planning for Replication Programming
Replication Developer InfoCenter

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Changed content:
  • Updated the requirements to SQL Server 2005 SP1.
  • Added a note with information on how to run the sample on a SQL Server 2005 Publisher.

5 December 2005
Changed content:
  • Updated deployment steps.
  • Updated key creation and deployment information to reflect sample updates.