SQL Server 2005 Express Edition - Part 21 - Using Replication Management Objects

April 24, 2008

In the most recent installments of our series covering new and improved features incorporated into SQL Server 2005 Express Edition, we have been discussing its replication characteristics. As we have pointed out, while there are some significant limitations in this area (when compared with its predecessor - SQL Server 2000 MSDE - as well as its full-fledged contemporaries), there are several workarounds that can be used to emulate missing functionality. So far, we have demonstrated this approach by taking advantage of replication-specific executables (such as distrib.exe or replmerg.exe) and T-SQL code combined with Windows Synchronization Manager and Web Synchronization technologies. In this article, we will explore another method of reaching the same goal, which involves Replication Management Objects (RMO).

In order to improve the ability to automate administration of its database management products, with the release of SQL Server 2000, Microsoft has introduced SQL Distributed Management Objects (SQL-DMO) - a COM-based object model, easily accessible through programming or scripting. With advent of SQL Server 2005, features available via SQL-DMO technology have been implemented as a set of .NET-based object libraries, with the bulk of administrative tasks packaged in the form of SQL Server Management Objects (SMO) and those specific to replication separated as Replication Management Objects (RMO). The latter has been encapsulated into Microsoft.SqlServer.Rmo.dll assembly, residing by default (alongside Microsoft.SqlServer.Smo.dll and other supporting libraries) in the Program Files\Microsoft SQL Server\90\SDK\Assemblies folder, created during the setup of SQL Server 2005 Express Edition (providing that you specified that Replication component should be included in the installation).

To leverage capabilities exposed through the RMO, you can use Visual Basic 2005 Express Edition (for more information regarding location of its source files, its licensing model, or setup, refer to an earlier article of this series). Once you have it installed and running, launch a new project (by using the appropriate entry from the top level File menu) based on the Console Application template. To access classes built into Replication Management Objects, you need to make your project aware of their respective libraries. The simplest way to accomplish this involves the use of the Add Reference dialog box (invoked via the context sensitive menu of the References node in the Solution Explorer window of Visual Basic 2005 Express Edition interface). From its .NET tab, choose Microsoft.SqlServer.Replication .NET Programming Interface, Microsoft.SqlServer.ConnectionInfo, and Replication Agent Library items and click on the OK command button to confirm your selection. At that point, you should be able to add relevant code to the default Module1 that is automatically generated as part of your project. In our sample demonstration, we will leverage code listed in the How to: Synchronize a Pull Subscription (RMO Programming) article of the SQL Server 2005 Books Online, but will simplify it for the sake of clarity, by retaining only those sections that provide core configuration settings or actions necessary to perform synchronization and removing its error checking provisions. (Obviously, you should add missing code before deploying such a solution in your production environment). The first portion of the code (which allows us to reference all members of relevant namespaces directly by their name - rather than being forced to specify it in the fully qualified format) needs to be part of the (General) (Declarations) section of the Module1.vb (we will rely on VB.NET programming language throughout our example):

Imports Microsoft.SqlServer.Replication
Imports Microsoft.SqlServer.Management.Common

while the second portion will constitute the content of its Main() subroutine. Note that we follow the same naming convention and assumptions that were used in our earlier examples, utilizing replmerg.exe command line utility, i.e. our publication based on the SalesTaxRate(Sales) table in AdventureWorks database exists on the distributor/publisher located on the default SQL Server 2005 Enterprise Edition instance hosted on the computer ALPHA, and our SQL Server 2005 Express Edition-based subscription database called AdventureWorksRepl resides on computer OMEGA, with properly configured subscription. The code below is also based on the premise that the subscription has been assigned correct Security and Web Synchronization settings, including working Windows integrated authentication-based connections to its distributor/publisher and the Web Server, as well as a URL pointing to the WebSync virtual directory. (Reference the SQL Server 2005 Books Online article we mentioned above if any of these assumptions do not apply in your case and you need to set some of configuration parameters through code). With these simplifications in place, the resulting module takes the following form:

Module Module1
 
    Sub Main()
 
        Dim subscriberName As String = "OMEGA\SQLEXPRESS"
        Dim publisherName As String = "ALPHA"
        Dim publicationName As String = "SalesTaxRate(Sales)"
        Dim subscriptionDbName As String = "AdventureWorksRepl"
        Dim publicationDbName As String = "AdventureWorks"
 
        Dim conn As ServerConnection = New ServerConnection(subscriberName)
 
        Dim subscription As MergePullSubscription
        Dim agent As MergeSynchronizationAgent
 
        conn.Connect()
 
        subscription = New MergePullSubscription()
        subscription.ConnectionContext = conn
        subscription.DatabaseName = subscriptionDbName
        subscription.PublisherName = publisherName
        subscription.PublicationDBName = publicationDbName
        subscription.PublicationName = publicationName
 
        subscription.LoadProperties()
 
        agent = subscription.SynchronizationAgent
 
        agent.OutputVerboseLevel = 1
        agent.Output = ""
 
        agent.Synchronize()
 
        conn.Disconnect()
 
    End Sub
 
End Module

By establishing an instance of ServerConnection class and invoking its Connect() method, we obtain access to the subscriber. LoadProperties() method of the MergePullSubscription class retrieves properties of our preconfigured subscription, which are necessary to initiate synchronization. Thanks to having the appropriate values assigned to Output and OutputVerboseLevel properties of the instance of SynchronizationAgent class, we are able to view status messages displayed during Web synchronization (in the same manner, in which they are displayed when running replmerge.exe from the Command Prompt). You can track execution of the module using options available via the top level Debug menu or by running an executable generated via Build menu from the Command Prompt. Note that with RMO programming, you can manage a variety of replication activities (beyond pull merge Web Synchronization covered in our example). For more information, refer to the Programming with Replication Management Objects article in the SQL Server 2005 Books Online.

This concludes our discussion regarding replication functionality in SQL Server 2005 Express Edition. In our next article, we will start exploring several of its most common upgrade scenarios.

» See All Articles by Columnist Marcin Policht