November 24, 2008 at 6:32 am
Hi Guys,
We have two databases A and B. A holds some data which has to be copied to database B. At any point of time the data between A and B has to be the same. As an example A holds the IDs of all students and B holds information something like their addresses with the IDs coming from A. Whenever a new student is added to A the ID should get added into database B and likewise when a student is deleted from A, then the corresponding data should be deleted from B. What this means is that we need to synchronize the data all the times.
Have you guys worked on this sort of scenario and if yes what was the solution you have adopted?
Several things that are coming to our mind are:
1. Use triggers on database A to invoke .Net code and this .Net code would update database B
2. Use service broker to asynchronously execute the trigger
3. Use notification services
4. Use Microsoft Sync framework
Any inputs that you can provide would be of immense help to us,
Thanks
bala
November 24, 2008 at 6:58 am
Don't use notification services - the feature has been removed from SQL Server.
There are a lot of ways to deal with this. It will depend on your network structure, your need for data consistency, and the latency that is acceptable.
In simplest form, you could use linked servers and triggers to update between the two servers. This would mean a failure to update the second server would fail the original transaction (this may be good or bad). It would also slow down the original transaction.
If you need the transaction on server B divorced from the one on server A, Service Broker is a pretty good choice. The nice thing about Service Broker is that if your second server is down, the transactions will automatically queue up until the server is available and run then. So, you get the benefit of not having to stop use of server A if there is a problem with server B.
Another option may be to use replication. If you replicate the table in Server A to the table in Server B, you could easily modify the replication procedures to update or delete other tables at the same time. Again, you get many of the benefits of using service broker, but with replication you get some good monitoring tools.
November 24, 2008 at 7:25 am
Sure sounds like a possible case for transactional replication.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 24, 2008 at 8:51 am
My first thought was replication, but you have to meet the requirements (PKs, GUID, non changing subscriber, etc.) for that. You can get around some of them, but it gets complicated quickly.
I might look to Service Broker if you can tolerate delays in the changes. The big thing is how important is it for things to be in sync. If there can't be any chance for a lack of sync, think banking scenarios, then you probably want linked servers and two phase commit here (both changes in a trigger or transaction).
If not, replication might be the simplest to set up.
November 24, 2008 at 11:37 pm
Thanks to all for the replies.
We cannot go for transactional replication (I am assuming that for transactional replication the databases have to have the same schema). The reason being that database A and B are completely different. Database B needs information from some of the columns of several tables of database A. So what we were contemplating was that through triggers we would have some business logic written on A that would fetch the required information and then call up the server (mostly a WCF server) on B to update or write new information to database B. Since triggers are by default synchronous, any operation done by users on A would then have to wait until the trigger execution is done and this could, perhaps, insert lot of latency due to synchronization which might hurt the users of database A. So in this scenario we thought of going for service broker to execute the trigger asynchrounously there by getting rid of the latency issues due to synchrounous execution of triggers.
To sum up - we have a server A with database and few business objects. Whenever any updates are done to the database on A, some information of these updates would have to go to the database on server B.
Regards
Bala
November 24, 2008 at 11:46 pm
balakrishna.gubba (11/24/2008)
Thanks to all for the replies.We cannot go for transactional replication (I am assuming that for transactional replication the databases have to have the same schema). The reason being that database A and B are completely different. Database B needs information from some of the columns of several tables of database A. So what we were contemplating was that through triggers we would have some business logic written on A that would fetch the required information and then call up the server (mostly a WCF server) on B to update or write new information to database B. Since triggers are by default synchronous, any operation done by users on A would then have to wait until the trigger execution is done and this could, perhaps, insert lot of latency due to synchronization which might hurt the users of database A. So in this scenario we thought of going for service broker to execute the trigger asynchrounously there by getting rid of the latency issues due to synchrounous execution of triggers.
To sum up - we have a server A with database and few business objects. Whenever any updates are done to the database on A, some information of these updates would have to go to the database on server B.
Regards
Bala
you can use RPC's and made it as schedule. this will less impact on your OLTP server.
November 24, 2008 at 11:46 pm
Since you are using SQL Server 2005 (this is posted on a SQL Server 2005 forum), instead of triggers executing stored procedures with the business logic to move data, you should look at using Service Broker. The triggers you setup can simply send a message to a Service Broker queue, and return (completing the transaction that fired the trigger), and Service Broker can then complete the business process of moving the data between databases asynchronously.
November 24, 2008 at 11:54 pm
This is precisely what we are doing now...
"Since you are using SQL Server 2005 (this is posted on a SQL Server 2005 forum), instead of triggers executing stored procedures with the business logic to move data, you should look at using Service Broker. The triggers you setup can simply send a message to a Service Broker queue, and return (completing the transaction that fired the trigger), and Service Broker can then complete the business process of moving the data between databases asynchronously."
Just wanted to learn from the forum if there are any other methods that can achieve the required functionality.
Thanks again for the replies.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply