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

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
FilesMicrosoft SQL Server90SDKAssemblies 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 = “OMEGASQLEXPRESS”

        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