Sales Orders Sample Implementation Details

This topic describes some of the implementation details and design decisions for the Adventure Works Sales Orders sample application. This sample, which is included with Microsoft SQL Server 2005, demonstrates the benefits of programming merge replication server to client scenarios. For more information, see Sales Orders Sample for Merge Replication.

Architectural Overview

The application itself is a Microsoft Windows Forms-based managed client developed using Microsoft Visual Studio 2005. This client application accesses sales order data in a local instance of SQL Server 2005 (all editions) using the data access facilities of the Microsoft .NET Framework 2.0. A merge replication topology is implemented to maintain a consistent set of data between the salesperson's computer and the main AdventureWorks database back at headquarters, and this data is partitioned so that each salesperson only receives the data needed to support his or her customers. The merge publication is configured programmatically using replication stored procedures. When the Sales Orders application is started for the first time, it programmatically creates the local database using SQL Server Management Objects (SMO) and defines the merge pull subscription using Replication Management Objects (RMO). The subscription is maintained and monitored programmatically using RMO.

Replication Features

By using merge replication, salespeople are able to work with their sales data locally when on a client call or while traveling without having a connection to the AdventureWorks database. Periodically, a salesperson will connect to the Internet to upload sales order changes to the Publisher and download sales order changes and updated product information to the Subscriber. The merge replication topology for this sample has been designed to support multiple Subscribers and is based on features introduced in SQL Server 2005. This includes SQL Server Express and SQL Server 2005 Compact Edition. The replication topology is discussed in greater detail in Exchanging Data with Mobile Users.

Parameterized Row Filters

In practice, the sales data belonging to each salesperson should be horizontally filtered using the SUSER_SNAME function based on the login information in the Employee.LoginID column. Filtering can improve performance, reduce the size of the initial snapshot, reduce the potential for conflicting data changes between Subscribers and simplify application logic. However, in a sample application it is unreasonable to filter using the SUSER_SNAME function. Instead, the sample partitions the data using the HOST_NAME function. Replication also enables you to overload the HOST_NAME function to specify any filtering criterion for partitioning data, and this sample uses the clause WHERE HumanResources.Employee.LoginID = HOST_NAME to define the horizontal filter for the Employee Table article. Join filters are used to extend this parameterized row filter to other sales-related tables.

Important

There are security considerations if you use a parameterized filter that includes HOST_NAME(). For more information, see the section "Filtering with HOST_NAME()" in Parameterized Row Filters.

Because of the article filtering options used in this sample, you must supply a different value of Hostname for each subscription to the publication. The following Employee.EmployeeID values can be used in the Hostname key in the sample:

• adventure-works\michael9
• adventure-works\pamela0
• adventure-works\tsvi0
• adventure-works\shu0
• adventure-works\rachel0
• adventure-works\lynn0

Before you change the Hostname value for an existing subscription, you must execute the following Transact-SQL script at the Subscriber and Publisher, respectively, and then rerun the sample to re-create the 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

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

Preparing the Partitioned Snapshots

The publication is configured to allow Subscribers to request the generation of their partitioned snapshot when the subscription is initialized. However, you can also generate these snapshots prior to initialization, thereby incurring this processing cost predictably at a predetermined time. For more information, see How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming).

Schema Options

Article schema options determine how a database object is replicated to the Subscriber. For more information, see Publishing Data and Database Objects. The schema options used in the sample depend on the type of object and functionality required at the Subscriber.

Programmability Objects

• For data access stored procedures and user-defined functions, only the object creation commands are scripted, such as CREATE PROCEDURE and CREATE FUNCTION.

This equates to a @schema_option value of 0x00001.

Table Objects

• Table objects are created at the Subscriber.
• Clustered and nonclustered indexes on tables are replicated.
• Check and FOREIGN KEY constraints are replicated.
• Unique keys are replicated.
• User-defined data types are converted to base types at the Subscriber.
• User-defined triggers on tables are not replicated.
• Schemas that are not already present on the Subscriber are created.

This equates to a general @schema_option value of 0x8004EF1.

Subscription Status

This sample invokes the SubscriberMonitor class to display subscription status information. This class is implemented as part of this sample and also as a stand-alone monitor in the SubscriberMonitorUtility project. For more information, see Subscriber Monitor Sample.

Web Synchronization

Web synchronization is enabled for the publication when it is created. However, to use Web synchronization, you must run the Configure Web Synchronization Wizard and set up certificates for SSL at the Web server and at the Subscriber. The sample uses HTTP Basic Authentication over a secured SSL connection, which is the recommended practice for Web synchronization. For more information, see Configuring Web Synchronization. When Web synchronization is enabled in the Web Synchronization Options form and you specify a login and password for HTTP Basic Authentication, the application sets the UseWebSynchronization property for the subscription to true and the InternetLogin and InternetPassword to synchronize using the HTTPS protocol. When using the sample, you must specify a Windows account and password to use Web synchronization. These credentials are used by the Web server when connecting to the Publisher. If the Merge Agent is unable to connect to the Web server using HTTPS at the location specified by the InternetUrl property, an error is returned.

Custom Business Logic

Because sales orders are usually placed when the salesperson is offline, it is likely that an order will be placed for products that are back ordered. Because of corporate policy, orders are not shipped until all items in the order can be filled. When an order cannot be filled immediately, the Status column of the SalesOrderHeader Table is set to a value of 3 to indicate that the entire order will be back ordered. In these situations, the application must notify the salesperson as soon as possible of a back-order situation so that they can notify the customer.

The Sales Orders sample implements the BusinessLogicModule class of the Microsoft.SqlServer.Replication.BusinessLogicSupport namespace, inserting custom business logic into the synchronization process. For every row change of the SalesOrderDetail Table that is sent to the Publisher, the custom logic connects to the Publisher and checks the inventory for that item against the ProductInventory Table. If the application determines that the ordered quantity exceeds the total available inventory, it sets the Status column of the SalesOrderHeader Table to 3. The application then writes a message to the synchronization log that describes the back ordered situation. Because the business logic framework provided by SQL Server 2005 can be used to implement any functionality supported by the .NET Framework, the custom business logic can just as easily be used to raise a dialog box or send an automated e-mail message to the customer.

Note

When synchronizing the subscription using Web synchronization, messages written to the log by the business logic handler are not displayed at the Subscriber.

The custom business logic implements the InsertHandler and UpdateHandler methods to handle row inserts and updates. The Initialize method is also implemented. When the custom business logic is initialized, replication passes Publisher information that is used to programmatically generate the connection string for the SqlConnection object using the SqlConnectionStringBuilder class.

Synchronization Status

Replication agents can be started from an agent job (asynchronously) or directly from your code (synchronously). One of the major benefits of running replication agents synchronously is the ability to obtain and display agent status messages using the agent's built-in callback functionality. In the Sales Orders sample, the Status event (callback) is handled by using the StatusEventHandler to get and display status messages from returned StatusEventArgs objects. An integer is also returned that indicates the estimated percentage complete for the synchronization, which is displayed to users using the progress bar. Because background synchronization can cause the information in the Subscription Status form to become outdated, a salesperson can click the Refresh button to load the latest sessions.

Synchronizing When Connected

The Sales Orders sample demonstrates the strengths of merge replication for synchronizing data in a disconnected environment. However, there could be situations, such as during meetings at corporate headquarters, when Internet connectivity is readily available. This sample can also be used to synchronize continuously in a connected environment. With the Synchronize when connected option enabled in the Advanced Options, a Timer control is enabled. When the timer reaches the interval, which is preset in the application configuration file, the timer event is raised and the application queries the Windows Management Instrumentation (WMI) provider (using the System.Management namespace) to check whether the computer has connectivity. If there is connectivity, the subscription is synchronized in the background. To avoid data entry problems, the sample does not allow background synchronization if a form other than Sales Orders has focus. Because the sample uses a WMI property that is not available in Microsoft Windows 2000, the Synchronize when connected menu option is not enabled on computers that are running on Windows 2000.

Note

In this sample, synchronizing when connected is implemented in a single thread. Ideally, this functionality is implemented in a separate thread. When you run replication agents in a separate thread, consider using the BackgroundWorker class.

Deployment

All the programming objects that are used to access data in the subscription database, such as stored procedures, user-defined types and user-defined functions, are created only at the Publisher in the publication database and are published to the Subscriber as articles. The first time that the Sales Orders sample is run on a salesperson's computer, the application connects to the Publisher, downloads the initial snapshot, and applies the snapshot, which includes the program's data access programming objects. After the snapshot is successfully applied, the application loads the local data in the Sales Orders form, and the application is ready to be used for data entry.

Using the new Visual Studio 2005 ClickOnce deployment feature, you can publish an application installation package to a Web site. Subscribers can download and install the application from this ClickOnce deployment site and initialize their subscriptions remotely over a virtual private network (VPN) from an offsite location, provided that .NET Framework 2.0 is already installed. ClickOnce can also deliver service updates from the Web site. For more information about ClickOnce deployment, see the topic "ClickOnce Deployment Overview" in the Visual Studio 2005 documentation.

For step-by-step instructions about how to install and run this sample, see Sales Orders Sample for Merge Replication.

User Interface Elements

The Sales Order sample implements the following user interface elements.

Element Name	Description
Sales Orders form	This is the main user interface element and the entry point of the application. On this form, a salesperson can work with orders by selecting a customer from the Customer combo box and an order from the Order combo box. Clicking the Edit button displays the selected order for editing in the Edit Order form. Clicking the New button displays an empty Edit Order form.
Edit Order form	This form allows the salesperson to edit an order. Line items can be edited in the Order Items grid, and new items can be appended to the grid and saved by clicking the Add button. Items can be selected and removed from the grid by clicking the Delete button. When an order is complete, the Save button commits the changes to the database.
Synchronize Sales Data form	This form is invoked to initialize, synchronize, or reinitialize the subscription. Status messages from the Merge Agent are displayed in the Synchronization Status field, and a progress bar is shown to indicate percent complete.
Merge Subscriber Monitor form	This form is implemented in a separate assembly. It is invoked from the Advanced Options menu and displays subscription information using the MergeSubscriberMonitor. For more information, see Subscriber Monitor Sample.
Web Synchronization Options form	This form is invoked by selecting Web Synchronization Options in the Advanced Options menu. It is used to manage Web synchronization settings for the subscription. Instead of managing these settings in the application, a MergePullSubscription object is used to store Web synchronization options in and retrieve Web synchronization options from the subscription metadata. In this sample, you must supply a Windows login and password that are passed to the Web server using HTTP Basic Authentication over an SSL connection. For more information, see "Web Synchronization" in the "Sample Considerations" section later in this topic.
Logon User form	This form is invoked when the subscription is created. A valid windows login and password must be supplied to the Mergepullsubscription object when the subscription is created with CreateSyncAgentByDefault set to true. This is required because the sample must have the agent-related metadata created to store web synchronization settings. When CreateSyncAgentByDefault is false, the agent job is not created and Web synchronization and other subscription metadata must be maintained by the application. For more information, see "Web Synchronization" in the "Sample Considerations" section later in this topic. For a SQL Server 2005 Express Edition Subscriber, the metadata is still created even though this edition does not support SQL Server Agent and the agent job itself is not created.
Synchronize menu	Items in this menu start a synchronization session and enable synchronization when connected.
Advanced Options menu	Items in the menu reinitialize the subscription, enable Web synchronization, and show the Merge Subscriber Monitor form.

Sample Considerations

This section describes design considerations, compromises, and potential issues that exist in this sample.

Support for SQL Server Compact Edition Subscribers

A primary design consideration for the replication topology is that the publication support SQL Server 2005 Compact Edition Subscribers. Because SQL Server 2005 Compact Edition only supports character-mode data snapshots, the publication has some additional limitations that would not have existed when using native-mode snapshots, which can be used when all Subscribers are running other editions of SQL Server 2005. These restrictions include the following:

• Running Transact-SQL scripts before and after the snapshot is applied. This would have enabled the creation of the local database and any user-defined types or schema on the Subscriber using Transact-SQL instead of having to load SQL Server Management Objects (SMO) assemblies at runtime.
• Computed columns. A few key columns in the AdventureWorks sales orders schema cannot be replicated. These include LineTotal in the SalesOrderDetail Table and TotalDue and SalesOrderNumber in the SalesOrderHeader Table. The application must do its own computations to display this information to users.
• Logical records could have been used to ensure that sales items in the SalesOrderDetail Table were not sent to the Publisher without the row in the SalesOrderHeader Table to which they belong. However, when supporting SQL Server 2005 Compact Edition Subscribers, logical records cannot be used.

Merge Replication Performance

Because the AdventureWorks sample database was designed to emulate many of the production databases implemented using SQL Server, the schema is fairly complex and its data is highly normalized. This degree of normalization can improve index-dependent operations but may have a negative effect on merge replication performance. In this sample, five join filters are required to extend the parameterized row filter from the Employee Table down to the SalesOrderDetail Table. SQL Server 2005 includes new features that optimize the performance of parameterized row filters, such as precomputed partitions. However, in situations in which merge replication performance becomes an issue, you should denormalize the published tables to reduce the number of join filters to less than three. For more information, see Enhancing Merge Replication Performance.

Web Synchronization

Enabling users to seamlessly switch between Web synchronization and regular synchronization using a direct connection to the Publisher influenced the architecture of the sample. The sample uses the MergePullSubscription class to help securely store and retrieve properties required for Web synchronization, which also simplifies our application logic. To obtain this functionality from the MergePullSubscription class, the subscription must be created with the CreateSyncAgentByDefault property set to true. Along with the row in the MSsubscription_properties table that enables you to set these properties on a MergePullSubscription object, this also creates an agent job for the subscription. Because the application always starts the synchronization directly and does not use SQL Server Agent, this job is never used. The agent job will not be created for a SQL Server 2005 Express Edition Subscriber because SQL Server Agent is not supported on this edition, but the required metadata is still created. When the Create method is call and CreateSyncAgentByDefault is true, SynchronizationAgentProcessSecurity information must be provided. This is why the Logon User form is displayed when the subscription is created. These credentials are stored by SQL Server but are never used. Instead the Merge Agent is started under the context of the user running the sample.

The sample is designed to use integrated authentication on all server connections, except for the Subscriber connection to the Web server during Web synchronization which uses HTTP Basic Authentication. We do not recommend using Integrated Authentication for Web synchronization. In a real-world scenario, the IIS server used for Web synchronization is deployed on a computer other than the Publisher or Distributor. Because of the limitations of Windows impersonation, this requires that the Basic Authentication credentials supplied to the Web server are equivalent to a login at the Publisher.

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.

User-Defined Types

AdventureWorks includes a number of user-defined types, which are replicated as base types at the Subscriber. If these types are common language runtime (CLR) types, they are replicated as CLR types.

Published AdventureWorks Objects

The following AdventureWorks tables and data access stored procedures and user-defined functions are published as articles in the merge replication topology.

Object name	Object type	Remarks
Customer	Table	For more information, see Customer Table (AdventureWorks).
CustomerAddress	Table	For more information, see CustomerAddress Table (AdventureWorks).
Employee	Table	Download-only article. A parameterized query based on LoginID is used to partition the data for the each sales person's subscription. For more information, see Employee Table (AdventureWorks).
Product	Table	Download-only article. For more information, see Product Table (AdventureWorks).
ProductCategory	Table	Download-only article. For more information, see ProductCategory Table (AdventureWorks).
ProductDescription	Table	Download-only article. For more information, see ProductDescription Table (AdventureWorks).
ProductInventory	Table	For more information, see ProductInventory Table (AdventureWorks).
ProductListPriceHistory	Table	Download-only article. For more information, see ProductListPriceHistory Table (AdventureWorks).
ProductModel	Table	Download-only article. For more information, see ProductModel Table (AdventureWorks).
ProductModelProductDescriptionCulture	Table	Download-only article. For more information, see ProductModelProductDescriptionCulture Table (AdventureWorks).
ProductSubcategory	Table	Download-only article. For more information, see ProductSubcategory Table (AdventureWorks).
SalesOrderDetail	Table	For more information, see SalesOrderDetail Table (AdventureWorks).
SalesOrderHeader	Table	For more information, see SalesOrderHeader Table (AdventureWorks).
ShipMethod	Table	Download-only article. For more information, see ShipMethod Table (AdventureWorks).
SpecialOffer	Table	Download-only article. For more information, see SpecialOffer Table (AdventureWorks).
Store	Table	For more information, see Store Table (AdventureWorks).
StoreContact	Table	For more information, see StoreContact Table (AdventureWorks).
ufnGetAccountingStartDate	Scalar-valued User-defined Function	AdventureWorks function.
ufnGetAccountingEndDate	Table-valued User-defined Function	AdventureWorks function.
udfGetStatusTextTable	User-defined function	Table valued function that returns text descriptions for the status values.
uspSalesOrderDetailDeleteCommand	Stored procedure	Used by the application when deleting rows from the SalesOrderDetail Table.
uspSalesOrderDetailInsertCommand	Stored procedure	Used by the application when inserting rows from the SalesOrderDetail Table.
uspSalesOrderDetailSelectCommand	Stored procedure	Used by the application when querying the SalesOrderDetail Table (AdventureWorks).
uspSalesOrderDetailUpdateCommand	Stored procedure	Used by the application when updating the SalesOrderDetail Table.
uspSalesOrderHeaderDeleteCommand	Stored procedure	Used by the application when deleting rows from the SalesOrderHeader Table (AdventureWorks).
uspSalesOrderHeaderInsertCommand	Stored procedure	Used by the application when inserting rows from the SalesOrderHeader Table.
uspSalesOrderHeaderSelectCommand	Stored procedure	Used by the application when querying the SalesOrderHeader Table (AdventureWorks).
uspSalesOrderHeaderUpdateCommand	Stored procedure	Used by the application when updating the SalesOrderHeader Table.
uspStoreSelectCommand	Stored procedure	Used by the application to query customers for a given store.
uspCheckProductInventory	Stored procedure	Used by the custom business logic to change the order status to back ordered and return the message that is logged.

Change History

Release	History
17 July 2006	New content: Added a note to clarify that the synchronizing when connected feature is implemented in a single thread.

See Also

Concepts

Sales Orders Sample Scenario

Help and Information

Getting SQL Server 2005 Assistance