February 16, 2012 at 9:23 am
I have a sql 2008 database that contains a list of users.
When any attributes change son the users I need to either insert or update the attributes on users attributes on two separate linked databases.
What is the best method for doing this. On the control database I set a flag when the record is inserted or updated. Then I was thinking I would run a job that would check and just do an insert or update on the linked servers. I need to convert some of the values to be combatable on one of the linked server so they are supported in the legacy app.
The number of record that will be inserted or updated is small, probably no more that 200 or 300 in a day. But I need the updates and inserts to be pushed probably every half hour.
I looking for any help or best practice suggestions.
Thanks
February 16, 2012 at 9:28 am
Are the linked servers read-only? Replication may be an option, or a DML trigger.
Jared
CE - Microsoft
February 16, 2012 at 10:02 am
Service Broker sounds like a good alternative.. Fire and forget..
I try VERY hard to limit the use of linked server, I used to use them often.. But I got burned too often..
CEWII
February 16, 2012 at 10:04 am
jflynn-735049 (2/16/2012)
I have a sql 2008 database that contains a list of users.When any attributes change son the users I need to either insert or update the attributes on users attributes on two separate linked databases.
What is the best method for doing this. On the control database I set a flag when the record is inserted or updated. Then I was thinking I would run a job that would check and just do an insert or update on the linked servers. I need to convert some of the values to be combatable on one of the linked server so they are supported in the legacy app.
The number of record that will be inserted or updated is small, probably no more that 200 or 300 in a day. But I need the updates and inserts to be pushed probably every half hour.
I looking for any help or best practice suggestions.
Thanks
There are no such things in SQLServer as "linked tables" or "linked databases". There are only linked servers.
There are many ways of moving data from one database to another. It can be anything: replication, DML triggers, scheduled jobs (sql and not-sql), SSIS, windows service etc...
Choosing the right solution should is based on specific requirements.
From details you've specified, it does look like it will be of low volumes and it doesn't need to be done close to real-time.
As one of simple solutions, you can do the following:
Create a table which will contain id's of effected users and action (Insert/Update/Delete). This table will be populated by an AFTER UPDATE,INSERT,DELET light-weight trigger on "user" table.
Create a stored proc which will extract the effected records, one by one or in batch and format the data appropriately.
Create windows service which can pool the table by executing stored proc, this service then will connect and sync the data to required destinations and flush processed records (or flag them processed).
As I said, it's one of many other ways of doing it...
February 16, 2012 at 10:32 am
Elliott Whitlow (2/16/2012)
Service Broker sounds like a good alternative.. Fire and forget..
+1
This is how I would do it (and have done in the past):
1) On the source server, create a table with the same definition as the target table on the linked server
2) Populate data on the source table with your normal application work
3) Capture changes with a trigger
4) The trigger writes to the "local copy" table and invokes Service Broker
5) Service Broker moves the data to the linked server
Would that work?
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply