Readme_Sales Orders Merge Replication Sample

11/05/2008 21:36:06


This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
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. 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. The publication also supports SQL Server Compact 3.5 SP1 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 3.5 SP1 and SQL Server Express 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

This sample is supported only on default installations of SQL Server. Named instances are not supported. Before running this sample, make sure the following software is installed:
  • SQL Server 2005 or later version, including the following optionally installed components:
  • SQL Server Database Engine Services (including replication)
  • Client Tools
  • AdventureWorks Sample OLTP Database. This sample database is available on the Microsoft SQL Server Samples and Community Projects page on CodePlex.
  • The SQL Server samples. These samples are available on the Microsoft SQL Server Samples and Community Projects page on CodePlex. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.
  • .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 Documentation.

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\100\COM directory. When using Web synchronization, you must also place a copy of BusinessLogic.dll in the C:\Program Files\Microsoft SQL Server\100\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. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.
  2. Ensure that the SQL Server Database Engine samples have been installed. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.
  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\100\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:
  7. 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.
  8. Drops and re-creates the stored procedures and user-defined functions used for data access at the client.
  9. Drops and re-creates the AdvWorksSalesOrders Publication.
  10. Adds articles to the publication.
  11. Registers the business logic handler at the Distributor.
  12. 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.
  13. (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.
  14. 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 samples installation directory. The default is C:\Program Files\Microsoft SQL Server\100\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\100\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:
  7. Replace the value of the Subscriber key with the name of the local server.
  8. Replace the value of the Publisher key with the name of the server configured previously as the Publisher.
  9. (Optional) Replace the value of the WebSynchronizationUrl key when connecting to the Publisher using Web synchronization. This can also be done at run time.
  10. (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.
  11. Place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\100\COM directory.
  12. (Optional) If using Web synchronization, place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\100\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:
  6. Edit sales order information.
  7. Add a new item to a sales order.
  8. Delete an item from a sales order.
  9. Change the ordered quantity for an item in a sales order.
  10. Select a synchronization method from the Synchronize menu to synchronize the subscription.
  11. (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:
  2. (Optional) Select a reinitialize option to reinitialize the subscription.
  3. (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.*
  4. (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 *
    1. 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\100\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

      Other Resources
      Replication Programming Concepts Developer InfoCenter (Replication) Samples (Replication)
      Help and Information
      • Getting SQL Server 2008 Assistance* © 2008 Microsoft Corporation. All rights reserved.

Last edited Nov 6, 2008 at 11:59 PM by bonniefe, version 9

Comments

jaypabs Aug 11, 2010 at 8:16 AM 
Thank you very much for this sample. I run it without error on my own computer.

However can anybody please tell me on how to run this in other computer? The sample is using adventure-works\garrett1 database. I want to use other salesperson. I am new to AdventureWorks so please bear with me. Thank you