Alternatives to SQL Server Merge Replication

Hello,

I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.

Microsoft supplied alternatives: RDA (Remote data access), ADO.NET Synchronization Services (currentluy in beta), ASP.NET Web Services (rolling your own).|||Is ADO.NET Synchronization Services as powerful as Merge Replication?|||

Just a hunch on why you might be looking for alternatives:

I know many developers are implementing alternatives to RDA and replications in effort to reduce number of CALs required for SQL Server. They believe what if N devices are connected to this “alternative” and “alternative” connects to SQL Server you only need one CAL as there’s only one physical connection to SQL Server. That is, however, not the case and SQL Server EULA has a clause about “through proxy” connection to prevent that scenario so you would need N CALs regardless. If that’s the reason you’re looking for alternatives you might just use RDA/Replication and save time and money on development.

Another reason for alternatives is security –developers don’t want IIS installed as they believe it’s unsecure. If that’s the case I would like to point out IIS has been around for years and runs on millions of hosts around the globe. Thinking self made solution would me more secure is quite perky.

Alternatives to Merge Replication

Hi,

Can anyone help me with third party tools that perform the same way as SQL Servers Merge replication.

What is wrong with Merge Replication?|||

Following is my problem

I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.

|||

In SQL 2000 you can choose not to replicate foreign key constraints or replicate them and mark them as NOT FOR REPLICATION. See BOL topic http://msdn2.microsoft.com/en-us/library/ms152529.aspx.

In SQL 2005, we have better retry logic as well as article ordering which greatly reduces the need to resolve conflicts if the above doesn't work for you.

Alternative to Merge Replication?

Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.
|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.
|||Sorry, meant to post this earlier...

Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx
|||

there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?

as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.

|||

My scenerio is:

Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005

1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.

Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.

Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.

I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.

|||

According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"

And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.

|||

Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.

If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.

Alternative to Merge Replication?

Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.|||Sorry, meant to post this earlier...

Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx|||

there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?

as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.

|||

My scenerio is:

Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005

1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.

Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.

Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.

I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.

|||

According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"

And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.

|||

Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.

If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.

Alternative to Merge Replication

I am involved in a development project of an enterprise level hosted application. The main product is a smart client application, and will soon have an embedded sql express option. At the moment I am building the WM 5.0 mobile application and have been advised against using merge replication to syncronize the databases.

The reasons have been that our main SQL 2005 database is on a hosted server that would have to open ports directly to the internet for the merge replication to do its thing. So far I have built some realy pain in the *** manual synchronization that loops through every table it needs to sync looking for a last modified time... pushing and pulling this way. This way it can pass DataSets through a web service just like the smart client app is working today.

Is Merge Replication acceptable or even safe in a situation like this? Any advice or opinions would be appreciated.

Thanks.

You do not need SQL Server to be on the internet for replication. Replication goes through IIS, so you only need IIS on the internet. You can use all security measures IIS offers including SSL and authentication to protect your data.

Keep in mind what whatever method you would choose, you have to have some open port for device to connect to. It's doubtful your home grown solution would be more secure than IIS which was around for years, tested by hackers, fixed and tested again. So, yes, replication should be appropriate for that.

|||

Ok, I understand what you are saying. Since it is hosted through IIS I guess we could avoid setting up IIS on our data boxes and create a virtual directory to the snapshot folder or whatever it exposed through IIS.

Another concern is filtering data. We have 6000 or so customers sharing one SQL server database and the data exposed to them is filtered through organization ID's and logins..., Is merge replication accessible through code (also the filters) or would each device have to be setup in SQL manually with a static view of data?

I'm sorry for the questions that might be ignorant, but I can't seem to find any good documentation on what situations merge replication is optimal for.

Thanks again.

|||

Excuse us for catching this up very lately.

You can use SqlCeReplication.HostName as your filter on SQL Server. You can use HOST_NAME() function on the SQL Server filter which would then replaced byt the SqlCeReplication.HostName to know which records this device/client is interested in.

I am sure there will be many more question that would arise and I sincerely request you to go through the resources available and get prepared for better questions!

Resources are listed out @. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295034&SiteID=1

Thanks,

Laxmi

Alternative to horizontal partitioning?

Hi,
In my replication environment I am using merge replication to get access to
our vendors to our orders.
They should not be able to see the price information of the orders. So, I
want to do something like horizontal partitioning.
I realize I could just leave the columns out of the article; but doing that
would be hard for two reasons:
1. I want to keep using the same publication for vendors as for our own
locations. If we leave out some columns we would need to create the exact
same publication with the columns for our own locations that use
replication.
2. Our program would be impacted. Our views depend on the fields. Our UDFs
depend on the fields, etc.
So, my ideal solution would be telling replication to include the column for
'schema' porpouses, but to not synchronize any data contained in it (so
effectively leaving the field NULL in the vendors databases).
While keeping the same database structure (and making life easier) we would
not be giving our vendors sensitive information.
I think there is no way to accomplish that in MSSQL2000 Merge replication.
Any ideas? workaround? (I am starting to consider messing with the
replication triggers myself - but that doesn't seem like a really good idea,
unless I modify the templates SQL uses to generate them).
Thanks for the comments, Jos Araujo.
Can you try to modify the stored procedures that is used to insert or update
the record in your vendors database?
Just a thought.
Perayu
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OtWgqqU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi,
> In my replication environment I am using merge replication to get access
> to
> our vendors to our orders.
> They should not be able to see the price information of the orders. So, I
> want to do something like horizontal partitioning.
> I realize I could just leave the columns out of the article; but doing
> that
> would be hard for two reasons:
> 1. I want to keep using the same publication for vendors as for our own
> locations. If we leave out some columns we would need to create the exact
> same publication with the columns for our own locations that use
> replication.
> 2. Our program would be impacted. Our views depend on the fields. Our UDFs
> depend on the fields, etc.
> So, my ideal solution would be telling replication to include the column
> for
> 'schema' porpouses, but to not synchronize any data contained in it (so
> effectively leaving the field NULL in the vendors databases).
> While keeping the same database structure (and making life easier) we
> would
> not be giving our vendors sensitive information.
> I think there is no way to accomplish that in MSSQL2000 Merge replication.
> Any ideas? workaround? (I am starting to consider messing with the
> replication triggers myself - but that doesn't seem like a really good
> idea,
> unless I modify the templates SQL uses to generate them).
> Thanks for the comments, Jos Araujo.
>