Deploying .Net / SQL Server 2005 replication

Hi,

I am experiencing a problem with deployment of a VB .Net application which carries out merge replication, and would greatly appreciate any assistance. I am currently struggling to find stuff about my problem on the net, which probably means either (a) I'm missing something really simple or (b) I shouldn't be trying to do this in the first place. :)

The story so far:
1. I added the SQLMergXLib.dll COM to my Visual Studio project, allowing me access to SQL Server merge replication functionality from my code, and wrote a procedure for synchronising a given pull subscription. This worked beautifully and with minimal fuss.
2. I tried to deploy the project to a different machine. I received an error to the effect of, "Couldn't create object, it's a COM object and it's not registered correctly." I have encountered this problem a few times before when trying to use Interop, so I called myself a few names, went back to the deployment project, and specified that the object should be registered. Still no joy. I tried this a couple of different ways round but then...
3. ...found an article on MSDN suggesting that I should consider using the managed Microsoft.SqlServer.Replication, Microsoft.SqlServer.Management, Microsoft.SqlServer.RMO interfaces instead. I reasoned that this would eliminate the registry issue altogether as everything would become native to the .Net framework, and this seemed infinitely preferable anyway, so I promptly substituted the SQLMergX DLL for these, rehashing my code to match. Again, this built and ran OK on my development machine.
4. I tried to deploy the project to a different machine. I received the following error: "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. This application has failed to start because the application configuration is incorrect."

Now it's worth pointing out that I think I know what the problem might be here: No SQL Server of any kind is installed on the target machine. However, SQL Server is not required to deploy standard database solutions in .Net, and I am hoping this is also true with the Microsoft.SqlServer... namespaces, as it is a requirement that the subscription database can be located anywhere (not necessarily the target machine), and therefore that SQL Server need not be present on the target machine. However, if this is simply impossible, please let me know so that I can look at other alternatives.

Other than that, I don't mind what method I use to get the synchronisation working as long as it works (I've been at this a good few days now...), so if anyone has done this before and can offer any assistance (any registering/files I need to include etc, I am hoping it is something that simple), it would be appreciated.

Thanks,

Steve.

Hi, Steve,

You may find following SQL Server 2005 Books online topic helpful to your case:

Requirements for Deploying Replication ActiveX Controls

When deploying the Microsoft ActiveX replication controls independently of Microsoft SQL Server 2005, you must include additional files in the installation kit that you use to distribute your application. If you deploy your application using these ActiveX replication controls on a computer where Microsoft SQL Server 2005 Express Edition or SQL Server 2005 client software is also installed, these files will already be present.

This topic describes how to deploy native code applications that use the replication ActiveX controls to access SQL Server 2005 functionalities. To obtain all of the benefits provided by the Microsoft .NET Framework, you should consider updating native code applications to managed code using Microsoft Visual Studio 2005. From managed code applications, you can access the same replication ActiveX functionality using classes in the Microsoft.SqlServer.Replication namespace.

If you use Microsoft Visual Studio and the Deployment Wizard to prepare your installation kit, the wizard will recognize that the files from the Referenced Libraries column in the following table are needed, and will include them in the installation kit. The wizard will also indicate that dependency information for these files is not available. Include the files from the Dependent Libraries column of the table in your installation kit (click the Add button on the Included Files page of the wizard). The paths shown in this table assume SQL Server 2005 was installed to the default location.

Replication Control Referenced Libraries Dependent Libraries

Microsoft SQL Distribution Control 9.0

Sqldistx.dll

Replerrx.dll

C:\Program Files\Microsoft SQL Server\90\COM\Rdistcom.dll

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Binn\Sqlrepss.dll

$(WinSysPath)\Sqlwoa.dll

|||

Thank-you for your response.

I had followed this article with regard to the ActiveX deployment and experienced the problem discussed above. Does anyone know if there are any articles describing the requirements of this further?

The article also implies that moving over to managed code provides the same functionality but with less fuss, so I would rather do it this way if at all possible. The files Visual Studio included when deploying my implementation (which required access to the SynchronizationAgent, MergePullSubscription and ConnectionInfo classes, I think) were:

Microsoft.SqlServer.BatchParser.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.RegSvrEnum.dll
Microsoft.SqlServer.Replication.dll
Microsoft.SqlServer.Rmo.dll
Microsoft.SqlServer.ServiceBrokerEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.WmiEnum.dll

Are there other files I need to include with any of these for their classes to work correctly?

Thanks in advance for any help, still struggling with this a bit. :)

Steve.

|||

Hi, Steve,

I got more information from our developer where it seems the best way to deploy RMO based application is to install a SQL Server instance on the client machine (like SQL Server Express which is freely available).

Here is the quote from our developer:

We don’t really have a good story for developers who want to deploy an application based on RMO as the synchronization agents in RMO are just wrappers of the existing controls which are in turn wrappers of the agent components. As a result, anyone who wants to deploy an RMO application would need to include most of everything in 90\COM + resources\1033\replres.rll. To make matters worse, all of our components depend on sqlncli.dll and the 80 CRT which is deployed as a WinSxS component. The snapshot agent itself also depends on the SMO assemblies which are deployed in the GAC. Given the external dependencies, the simplest way to deploy an RMO application is probably to install some version of SQL Server (Express should be sufficient for most types of applications).

Hope that help,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Yeah, I spent a lot of yesterday introducing myself to the delights of DLL Hell (having begun my career as a .Net programmer, it's my first time... :)), with regards to both scenarios I described, and ultimately plumped for doing exactly what you suggest: Deploying SQL Server Express on the target machine.

I thought that it seemed excessive but it seems to be the best way forward.

Thanks for all your help,

Steve.