February 13, 2009 at 10:40 am
Project:
Setting up transactional replication for a large hospital system. Replication pulls changes from primary server to x-number of facility servers. Replication is setup and working correctly.
Issue:
Each replicated table has a facility ID field. This ID is unique to each facility. Replication pulls the ID from the primary server. The ID needs to be changed to match the facility's unique ID. A stored procedure has been created select the facility's ID and update the table with the correct ID. The procedure will be run automatically via the SS replication agent.
Question:
How do you create the stored procedure so that it will update ONLY the new/updated rows that were pulled during replication?
Example:
Backup server pulls 3 transactions from primary server with ID 1234.
Backup server has ID of 9876
Stored procedure will run and change the 3 transactions ONLY - updating 1234 to 9876.
February 13, 2009 at 10:47 am
littlebeeper110 (2/13/2009)
Project:Setting up transactional replication for a large hospital system. Replication pulls changes from primary server to x-number of facility servers. Replication is setup and working correctly.
Issue:
Each replicated table has a facility ID field. This ID is unique to each facility. Replication pulls the ID from the primary server. The ID needs to be changed to match the facility's unique ID. A stored procedure has been created select the facility's ID and update the table with the correct ID. The procedure will be run automatically via the SS replication agent.
Question:
How do you create the stored procedure so that it will update ONLY the new/updated rows that were pulled during replication?
Example:
Backup server pulls 3 transactions from primary server with ID 1234.
Backup server has ID of 9876
Stored procedure will run and change the 3 transactions ONLY - updating 1234 to 9876.
So, the ID on the replicated server is 9876, correct? The newly replicated records, however, have the ID 1234, correct?
update MyTable set
FacilityID = 9876
where
FacilityID = 1234;
This will only update the new records, as those are the only ones with the FacilityID = 1234.
Okay?
February 13, 2009 at 10:54 am
Because the replicated ID could vary, we wouldn't be able to hardcode a value to look for and replace. The stored procedure would need to have a little more AI, as to be able to identify that there are new or updated rows, and that only those rows need to be updated with the correct ID, without looking for a specific ID to replace.
February 13, 2009 at 11:03 am
How does the backup server know what its ID is?
February 13, 2009 at 11:22 am
The stored procedure uses a SELECT statement to query the Facility table on the backup server. It then stores the query'd FacilityID into a variable. It will then update the FacilityID on the other tables with this value. The problem right now is that the stored procedure runs and updates all FacilityID values to the query'd value. Since a server could have mulitiple facilites, we would only want to update the new/changed row's FacilityID, and not every FacilityID.
Here's an example of the tables:
tblFacility - has the FacilityID for all facilities. (I.E. Facility1 = 1234, Facility2 - 5678)
tblDocuments - has a FacilityID field (I.E. Document1, used by Facility1 = 1234, Document2, used by Facility2 = 5678)
tblTasks - has a FacilityID field (I.E. Task1, used by Facility1 = 1234, Task2, used by Facility2 = 5678)
If we update Document1 on the Primary server (which has a FacilityID of 8888), the change is pushed to the backup servers. Now the backup servers have a document in their DB with a FacilityID of 8888. The FacilityID needs to be updated to the appropriate ID (say, 1234). Because the backup server tblDocuments has documents for multiple facilities, we can't just do a mass update. We need to be able to only change the new (updated) document from 8888 to 1234. But since we don't actually "know" that the FacilityID is 8888, we can't hardcode to replace '8888' with '1234'. We have to be able to see that a document has been added/updated, and then change only that document's FacilityID to 1234.
Dang...I think I confused myself now!
February 13, 2009 at 11:23 am
Basically, we need to update a row only if a row has changed or been added. No update to untouched rows.
February 13, 2009 at 11:39 am
All updates occur on the main server (for sake of this example FacilityID = 8888), and all records on this server have the FacilityID coded as 8888, correct?
All updates and inserts are then replicated to the other servers. I assume that means on an update, the FaciliteID on the target server gets changed (from, say 1234) to 8888, or on an insert has the FacilityID of 8888.
Am I correct so far?
If so, why not:
declare @FacID int;
select @FacID = ServerFacID from MyFacIDTable; -- need to have something here.
update MyTable set
FacilityID = @FacID
where
FacilityID <> @FacID;
FYI, There is a better way, but this is just a way to see if I am thinking right.
February 13, 2009 at 1:40 pm
Correct. The only snag I'm running into is -
where
FacilityID <> @FacID;
There is a possiblity (even if small) that there could be multiple FacilityID's in the document table. In that case, they would not want to change the existing FacilityID's, only the ID on the new row. Other than that small peice, that is exactly what I was looking for.
February 13, 2009 at 9:14 pm
This will do it...
http://www.sqlservercentral.com/articles/Video/65070/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply