Ideas please

Question

Ideas please

Ford Fairlane

SSCertifiable

Points: 7664
More actions
February 15, 2015 at 5:42 pm

#315279

Hi all,
I am looking for solutions to a tricky situation.
I have a database in say Datacenter A. Probably about 300GB in size. It is running on SQL Server 2012 ENT ED. I also have running on a mobile device some 2000 km away another database. This is using SQL SERVER 2012 Express. Now the mobile devices are not always available during the day as they are taken out on site and are not connected to any network.
What I need to come up with is how to replicate any changes to either of the systems... I have ruled out any form of replication due to the face that the mobile devices are offline for most of the day. CDC maybe....
Any ideas / thoughts would be greatly appreciated.
Hope this helps...
Ford Fairlane
Rock and Roll Detective

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Ford Fairlane (2/15/2015)
Hi all,
I am looking for solutions to a tricky situation.
I have a database in say Datacenter A. Probably about 300GB in size. It is running on SQL Server 2012 ENT ED. I also have running on a mobile device some 2000 km away another database. This is using SQL SERVER 2012 Express. Now the mobile devices are not always available during the day as they are taken out on site and are not connected to any network.
What I need to come up with is how to replicate any changes to either of the systems... I have ruled out any form of replication due to the face that the mobile devices are offline for most of the day. CDC maybe....
Any ideas / thoughts would be greatly appreciated.

Just so you know, this is THE SINGLE HARDEST thing to do in any form of data manipulation - bi-directional modification synchronization.

You cannot use CDC since that isn't available on express edition.

Microsoft has a Sync Framework for just such a scenario. I would take a look at it. I have never used it personally.

What about Merge Replication? It is also set up for this scenario, but I don't know if it is available via express edition either.

You will REALLY need to work hard to conquer the various conflict scenarios - where the same piece(s) of data has been modified on each end.

You can build your own using triggers and external code, but this is very hard and complex.

You may want to search around for a third party product that claims to handle this stuff for you. OOOHH!! Come to think of it a very good friend of mine built such a framework for a client we shared. It is VERY slick stuff! Drop me a PM and I will get you in touch with him. He has his own consulting company and could probably port that work over to suit your needs.

Good luck - you are going to need it!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

I wrote a system that did this type of thing several years ago. It was VERY difficult to get working. As Kevin mentioned the conflict resolution was by far the hardest part. The trickiest part here was dealing with data that had been updated on multiple mobile devices since they were last synched. Even more tricky when two mobile devices and the main database were all three updated since the last synch. Much of the decisions for these complicated conflicts were decided as business rules for how the synch process worked. I don't have access to that code anymore but remember most of the big pieces. This is not going to be an easy project.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

Thanks for the responses, I was not expecting it to be easy but to make it worse, the connection between the two systems is over a microwave link... :w00t:

Hope this helps...

Ford Fairlane
Rock and Roll Detective

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Ford Fairlane (2/16/2015)
Thanks for the responses, I was not expecting it to be easy but to make it worse, the connection between the two systems is over a microwave link... :w00t:

The poor link only matters in that a) it takes longer to transfer a given data size and b) you need to be really robust in your connection/disconnection/transaction handling.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service