February 11, 2009 at 9:04 am
I can't do that.
I did for a program of mine, i designed the DB, and added to any table a timestamp field and a UID field: and I can keep in synch MSAccess, MSSql, and MYSql.
But the software managing the DB for this dentist studio is proprietary SW, I have no control over it, and the company who owns the right is not very supportive.
Also, they owns the commercial rights for sure, but i doubt they ever had access to the source code. Unless i could open the transaction files somehow (the ldf files), and read from that. But i do not know how.
February 11, 2009 at 9:09 am
You can always write a trigger on the tables that will fire After Update, Insert or delete and write to another table with a Primary key, Table name and operation... 😀
There are more ways to skin a cat... :hehe:
-Roy
February 11, 2009 at 9:17 am
This sounds cool.
I will have to have to study WCF.
So you are telling me to write an agent, that connects to any table of the DB, and, when it is fired for the operation you described, will write into a new table (possibly on a different DB, why not?), what happened.
And another thask, or thread, using this information to perform synchronization.
Did i understand well?
February 11, 2009 at 9:24 am
Roy, while i agree that the solution you're talking about would work, why spend the time designing a replication-like technology from scratch when SQL server includes replication at no extra cost? It sounds like it would be a ton of time spent on a solution that will likely be less performant than any of the replication technologies already available.
Regards, Jim C
February 11, 2009 at 9:25 am
First you write triggers that fires when your App (or even updates made by you on the fly) makes any changes to the data in the tables. This trigger will write to a table with the Table name, The primary key, and the operation.
Your service will read from this table (where the trigger writes), get the data from the table that was updated and then notifies to a service on the other DB that an operation occurred. here is the operation occurred and here are the values. The service on the other side will then synch the data.
-Roy
February 11, 2009 at 9:30 am
Jim C (2/11/2009)
Roy, while i agree that the solution you're talking about would work, why spend the time designing a replication-like technology from scratch when SQL server includes replication at no extra cost? It sounds like it would be a ton of time spent on a solution that will likely be less performant than any of the replication technologies already available.
Yes, I agree the fact that SQL can replicate much better than the app you will write. But here is the reason why I suggested this outrageous idea.
If you have a very bad latency and intermittent internet connection between the two servers then merge might have issues. I have seen this happen.
The solution I provided can handle those problems if you code it correctly.
The solution I am providing should be used only if you have very bad latency and intermittent internet connection. Or else you are doubling the work for yourself. SQL Servers Merge can and will do the same Job for you.
-Roy
February 11, 2009 at 9:46 am
Roy, i wouldn't say your idea is outrageous. There is always a place for custom solutions. However, I still feel that even with the connection issues that Aldo's client is experiencing, SQL replication is the best choice. He will likely set his polling interval to 60 seconds. Each time the merge agent wakes up, it will attempt to merge data. If there is no connection, it will error for that attempt, and try again in another 60 seconds.
You said you have seen problems like this before. What kinds of issues did you run into with merge replicating over an unreliable connection?
Regards, Jim C
February 11, 2009 at 9:50 am
We tried it between two continents. The latency was about 360ms. Even synching the DB initially was a problem. And then sometimes the Internet connection dropped.
-Roy
February 11, 2009 at 10:04 am
There is one more thing to keep in consideration: total cost.
If i can implement Roy's solution, my customer is going to save about 5200 in SQL Server Licensing (that is, $6.700), as he will be able to stick to his current Express free version.
If Roy's solution could be completed in one month or less, full time, my customer is going to save some (and i will have one month of work :)). Even not considering the work I will have to do to configurate and check and assist the merge configuration. And the fact i will have to write some code to resync, as i stated in my very first post. Code that I could reuse in the final job.
I will think about it, and of course, tha last word will be to the Doctor. 😉
Thanks to everybody, you have been of great help.
February 11, 2009 at 10:30 am
aldo_marchioni (2/11/2009)
There is one more thing to keep in consideration: total cost.If i can implement Roy's solution, my customer is going to save about 5200 in SQL Server Licensing (that is, $6.700), as he will be able to stick to his current Express free version.
If Roy's solution could be completed in one month or less, full time, my customer is going to save some (and i will have one month of work :)). Even not considering the work I will have to do to configurate and check and assist the merge configuration. And the fact i will have to write some code to resync, as i stated in my very first post. Code that I could reuse in the final job.
I will think about it, and of course, tha last word will be to the Doctor. 😉
Thanks to everybody, you have been of great help.
If you go with SQL 2008 Change Tracking is available on all Editions and it can "simplify" A LOT your work.
By the way if you follow that route you may want to look into Change Tracking and Sync Services for ADO.NET
There is a lot of work already made for U!
Personally, I would go with merge replication but it will COST more to your client (depending on your hourly rate of course :D)
* Noel
February 11, 2009 at 12:18 pm
Aldo,
You do not have to reinvent the wheel, as SQL Server is the perfect companiion to help resolve your challenge. You first need to understand your entire challenge in terms of data distribution. SQL Server replication has all the core componentsto help with managing distributed data and dealing with any data conflicts that may or may not pop-up (e.g. same data being updated at both sites). Dealing with communication link latencies and availability is where merge replication will provide a good solution to your problem.
Hope this helps!
Phillip Cox
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply