June 29, 2017 at 8:43 am
It all comes down to the rules and boundaries of your project world.
Are there only five tables to be synchronized?
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent?
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures?
Is this a two way sync where data changes come down and go up?
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?
Knowing the answers to these will help us decide if ServiceBroker or Sync or simpler solution is the right answer.
June 29, 2017 at 8:44 am
alladiz - Thursday, June 29, 2017 8:27 AMPhil Parkin - Thursday, June 29, 2017 7:41 AMI'm assuming that your intention is to run an SSIS job on a schedule, say every hour, and to somehow detect which of the laptops are connected and which are not?
You'll have some challenges with this approach, because it needs to be 'connection tolerant'. Eg, what happens if the laptop user disconnects midway through the transfer of their updates?
How many tables (approximately) are we talking about?
Do any of the tables have IDENTITY primary keys? If yes, you are going to have an interesting (I'm sure there's a better word!) time managing clashes (same identity, different data, from multiple local users).Thanks Phil for taking time to look at this. I am assuming I can use execute process task to check for machine availability. I can create metadata table to keep track of time of successful completion and pick up the updated data based on it. I think the number of tables will be closely about 5. There wont be data clashes as data will be different per local user.
Sounds like you have a solid case for SSIS, then. Wish I had databases with only 5 tables 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2017 at 8:45 am
Bill Talada - Thursday, June 29, 2017 8:43 AMIt all comes down to the rules and boundaries of your project world.Are there only five tables to be synchronized?
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent?
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures?
Is this a two way sync where data changes come down and go up?
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?Knowing the answers to these will help us decide if ServiceBroker or Sync or simpler solution is the right answer.
Great questions.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2017 at 8:46 am
Phil Parkin - Thursday, June 29, 2017 8:44 AMalladiz - Thursday, June 29, 2017 8:27 AMPhil Parkin - Thursday, June 29, 2017 7:41 AMI'm assuming that your intention is to run an SSIS job on a schedule, say every hour, and to somehow detect which of the laptops are connected and which are not?
You'll have some challenges with this approach, because it needs to be 'connection tolerant'. Eg, what happens if the laptop user disconnects midway through the transfer of their updates?
How many tables (approximately) are we talking about?
Do any of the tables have IDENTITY primary keys? If yes, you are going to have an interesting (I'm sure there's a better word!) time managing clashes (same identity, different data, from multiple local users).Thanks Phil for taking time to look at this. I am assuming I can use execute process task to check for machine availability. I can create metadata table to keep track of time of successful completion and pick up the updated data based on it. I think the number of tables will be closely about 5. There wont be data clashes as data will be different per local user.
Sounds like you have a solid case for SSIS, then. Wish I had databases with only 5 tables 🙂
There are many tables but sync is required only for 5 tables as of now 🙂
June 29, 2017 at 8:55 am
Bill Talada - Thursday, June 29, 2017 8:43 AMIt all comes down to the rules and boundaries of your project world.Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent?
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures?
Is this a two way sync where data changes come down and go up?
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?Knowing the answers to these will help us decide if ServiceBroker or Sync or simpler solution is the right answer.
Thanks Bill for these questions (making me think more 🙂 ). I have provided the answers to the best of my knowledge. Thanks again!!
Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent? I am not sure about this as of now.
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures? possibility of other users changing the data
Is this a two way sync where data changes come down and go up? It will always be from local to central
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?I don't think so.
June 29, 2017 at 8:58 am
Ken McKelvey - Thursday, June 29, 2017 8:27 AMUsing Service broker, with triggers, would solve the 'connection tolerant' problem. Service broker will work with SQL Express as long as it is connecting to a non-Express edition:
https://msdn.microsoft.com/en-us/library/ms345154.aspx#sseover_topic10Of course, the plumbing for Service Broker is fiddly and you would have problems with monitoring but, as Bill mentioned, there are no easy solutions.
I think you should at least read the sync documentation as it will give you an idea of potential man traps.
Hi Ken, I have never explored this option before. I will give it a reading and see.
Thanks!!
June 29, 2017 at 8:58 am
alladiz - Thursday, June 29, 2017 8:55 AMThanks Bill for these questions (making me think more 🙂 ). I have provided the answers to the best of my knowledge. Thanks again!!
Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent? I am not sure about this as of now.
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures? possibility of other users changing the data
Is this a two way sync where data changes come down and go up? It will always be from local to central
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?I don't think so.
This answer
"It will always be from local to central"
conflicts with this
"possibility of other users changing the data"
How would a user ever change another user's data if data is only ever sync'd inwards?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2017 at 9:59 am
Phil Parkin - Thursday, June 29, 2017 8:58 AMalladiz - Thursday, June 29, 2017 8:55 AMThanks Bill for these questions (making me think more 🙂 ). I have provided the answers to the best of my knowledge. Thanks again!!
Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent? I am not sure about this as of now.
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures? possibility of other users changing the data
Is this a two way sync where data changes come down and go up? It will always be from local to central
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?I don't think so.This answer
"It will always be from local to central"
conflicts with this
"possibility of other users changing the data"How would a user ever change another user's data if data is only ever sync'd inwards?
Sorry for the incorrect info. The data movement will be bi-directional.
June 29, 2017 at 10:17 am
alladiz - Thursday, June 29, 2017 9:59 AMPhil Parkin - Thursday, June 29, 2017 8:58 AMalladiz - Thursday, June 29, 2017 8:55 AMThanks Bill for these questions (making me think more 🙂 ). I have provided the answers to the best of my knowledge. Thanks again!!
Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent? I am not sure about this as of now.
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures? possibility of other users changing the data
Is this a two way sync where data changes come down and go up? It will always be from local to central
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?I don't think so.This answer
"It will always be from local to central"
conflicts with this
"possibility of other users changing the data"How would a user ever change another user's data if data is only ever sync'd inwards?
Sorry for the incorrect info. The data movement will be bi-directional.
OK. Can you give us an insight into the PKs of the tables involved? As they are not IDENTITY columns (which is good!), what are you using? GUIDs? SEQUENCEs? This will be very important when it comes to bidirectional syncing. Do all of the tables have date created and date modified columns?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2017 at 10:25 am
Phil Parkin - Thursday, June 29, 2017 10:17 AMalladiz - Thursday, June 29, 2017 9:59 AMPhil Parkin - Thursday, June 29, 2017 8:58 AMalladiz - Thursday, June 29, 2017 8:55 AMThanks Bill for these questions (making me think more 🙂 ). I have provided the answers to the best of my knowledge. Thanks again!!
Are there only five tables to be synchronized? Yes, as of now.
Are the five tables constrained by foreign keys...so inserts must be done parent to child and deletes done from child to parent? I am not sure about this as of now.
Are each of the ten users owners of their data...so other users cannot change it and cause merge failures? possibility of other users changing the data
Is this a two way sync where data changes come down and go up? It will always be from local to central
Can all data for a user be deleted and refreshed from the laptop up to the server in a brute force way?I don't think so.This answer
"It will always be from local to central"
conflicts with this
"possibility of other users changing the data"How would a user ever change another user's data if data is only ever sync'd inwards?
Sorry for the incorrect info. The data movement will be bi-directional.
OK. Can you give us an insight into the PKs of the tables involved? As they are not IDENTITY columns (which is good!), what are you using? GUIDs? SEQUENCEs? This will be very important when it comes to bidirectional syncing. Do all of the tables have date created and date modified columns?
So far only couple of tables are available and they have PK's but no IDENTITY. There are no GUIDs or sequences. Please suggest what would be better. I can create audit columns like Datecreated and datemodified or add any required flags to track syncing.
June 29, 2017 at 11:40 am
alladiz - Thursday, June 29, 2017 10:25 AMPhil Parkin - Thursday, June 29, 2017 10:17 AMalladiz - Thursday, June 29, 2017 9:59 AMSorry for the incorrect info. The data movement will be bi-directional.OK. Can you give us an insight into the PKs of the tables involved? As they are not IDENTITY columns (which is good!), what are you using? GUIDs? SEQUENCEs? This will be very important when it comes to bidirectional syncing. Do all of the tables have date created and date modified columns?
So far only couple of tables are available and they have PK's but no IDENTITY. There are no GUIDs or sequences. Please suggest what would be better. I can create audit columns like Datecreated and datemodified or add any required flags to track syncing.
I think I'd be tempted to set your PK to be a nonclustered GUID for each of the tables, and put a unique index, possibly clustered (depending on query requirements) on whatever the PK is currently.
The GUID assures you of uniqueness between the remotes.
The unique index needs to be on one or more columns which cannot possibly collide during the sync process (include username, perhaps?)
Date Created and Date Modified will be almost essential. You may choose to add CreatedBy and ModifiedBy columns too, for additional clarity.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply