August 5, 2009 at 1:27 am
Hello,
I work with SQL Server 2008 on several databases having the same table descriptions (nearly 70 databases).
For a few tables, one database is the master (modifications are only made here).
Each time a data is modified through admin screens in one of these tables, the data modification need to be transmitted in the other databases.
I don't want to use triggers for two reasons :
- The user will have to wait a too long time for all databases to be modified before getting a new screen
- If at least one database is down, the change is lost for it and when the database is up again, data is out of sync
I don't want to do this programmatically in the code associated with the admin screens for the same reasons.
And I don't want to write a sort of transaction file keeping a trace of what is done and what failed and should be repeated later until completion.
The sync doesn't need to be immediate (some minutes later is allowable).
So, what would be the best way to achieve my goal (maybe SSIS ? But I have never used it until now) ?
Thanks for the help.
Gedeon
August 5, 2009 at 8:41 am
As I see it you have 2 options
Replication
or you could look at the new SS2008 feature CDC
/mSc
August 5, 2009 at 10:48 am
OK ! Thanks Grasshopper !
I'll look these two technos.
August 5, 2009 at 12:47 pm
Hello Gedeon,
I see in Replication the Best Solution to accomplish your request.
You can use Transactional / Snapshot Replication.
It replication model allow you define the set of table to be replicated. Also you can define which column will be replicated and also filters horizontal / vertical over the table can be defined. Replicatoin is sync by SQL Server itself(agents) , so, you can monitor all you sync activities on the Replication Monitor.
You can configure is the sync is going to happen immediatly after the record change or scheduled for later.
Replication allows you have a publisher (source information) and a lot of suscribers (target information).
Regards,
Victor Alvarez
http://sqlpost.blogspot.com
August 5, 2009 at 1:13 pm
Hello Victor,
As described, replication seems to feet my needs.
But is there also a way to insert a few check/transformation code between the publisher and the subscribers ?
Having begun to read the documentation about replication before your answer, I believe I have read a sentence telling it is possible, but I have not read further info about this until now (but I'm beginning to try to understand how it works and I have a lot to read).
Besides, I get an error with SQL Server Management Console when I try to create a new publisher, the very first operation I have tried. This error tells me I must connect to the server using its true name, not an IP address or an alias. And that's what I do. So, I don't understand.
Regards.
Gedeon
August 5, 2009 at 1:47 pm
I've seen lots of people use replication to do just this.
When you talk about code, are you looking for mistakes, like Spelling? The easy way to introduce custom code is to use custom code to read the table and check for changes, correct things, and then move the data to a new table. Replicate that new, clean table to the subscribers.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 6, 2009 at 10:18 am
Yes ! It is mainly code for checking data.
Thanks for the advice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply