June 14, 2012 at 9:57 am
We have a stored procedure that is used to update email addresses in our HRMS database. Unfortunately, the HRMS system is splitting into two separate systems (i.e., separate databases) and the same table will be utilized in both systems.
What is the best way to update both databases when updating an email address? Our Business rules dictate that the change must be made right away. The databases are linked together.
Can the update be accomplished by a trigger on the first table being updated or is there a way to add the insert/update of the second table/database to the current SP so that both are rolled back if an error is detected?
The following snippet is taken from the current Stored Procedure that does the insert/update:
--- Determine if any addresses of this type already exist (any college) ---
SELECT @InputAddrTypeCount = COUNT(*)
FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType
IF @InputAddrTypeCount = 0
BEGIN
BEGIN TRANSACTION
SET @Preferred = (CASE
WHEN EXISTS(SELECT * FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND PREF_EMAIL_FLAG = 'Y')
THEN 'N'
ELSE 'Y'
END)
INSERT INTO EMAIL_ADDRESSES (EMPLID, E_ADDR_TYPE, EMAIL_ADDR, PREF_EMAIL_FLAG)
VALUES (@InputEmplId, @InputAddrType, @InputEmailAddr, @Preferred)
-- Check for SQL Server errors --
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN(4)
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN(0)
END
END
ELSE IF @InputAddrTypeCount = 1
BEGIN
--- Determine if the Domain portion of the input email address matches the existing email address ---
--- Don't update the address if the original address is from another domain (i.e., college)
SELECT @CollegeAddrCount = COUNT(*)
FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType
AND LOWER(EMAIL_ADDR) LIKE ('%' + @InputEAddrDomain)
IF @CollegeAddrCount <> 1
BEGIN
RETURN(3)
END
BEGIN TRANSACTION
UPDATE EMAIL_ADDRESSES
SET EMAIL_ADDR = @InputEmailAddr
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType
-- Check for SQL Server errors --
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN(5)
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN(0)
END
END
ELSE --@InputAddrTypeCount > 1
BEGIN
-- Error and exit if more than one address of this type is found ---
RETURN(2)
END
Thanks in advance for your assistance.
Steve
June 14, 2012 at 10:15 am
Sounds like transactional replication may be a good solution for you.
http://msdn.microsoft.com/en-us/library/ms151176%28v=sql.90%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 10:23 am
Just out of interest, how are you going to keep the EmplIds in sync? Seems like there might be more to this than just syncing e-mail addresses.
Can both systems update the e-mail addresses?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 14, 2012 at 10:29 am
Looking over the Transactional Replication Overview triggered a couple of areas that I need to provide clarification.
Updates may be made in either database
If db1.tableA is updated then db2.tableA should be updated as well
If db2.tableA is updated then db1.tableA should be updated as well
Normally there is a very low volume of updates 2-10/day 98% of the time with peak periods (two days per year) at 2000/day.
Both databases are SQL Server 2005 but we are slowly migrating to SQL Server 2008
Thanks again.
June 14, 2012 at 10:33 am
EmplIds are kept in sync by other HRSA processes. This SP only works with existing EmplIds which are synced between systems when they are created.
June 14, 2012 at 10:43 am
Transactional Replication seems like a lot of overhead to keep one table synchronized. Are there any other suggestions for keeping the tables in sync?
Thank you,
Steve
June 14, 2012 at 10:43 am
Are the databases on the same server?
If so, transactions will stretch across them.
If not... DTS can handle your needs.
June 14, 2012 at 10:52 am
I had to check on that question. They are in our test environment but when we go to production with the split they will not be.
I also asked our dba about the transactional replication but she did not seem too enthusiastic given there is only one or two tables that would use this.
June 14, 2012 at 11:09 am
Since updates could occur on either table, I'd almost say that peer to peer replication between the two tables would be a good solution.
Having the stored procedure on each database handle updating both tables is also a solution using linked servers, but what happens if one of the servers is unavailable during the update? Do you still update in the database that is available or wait until both are available.
Triggers could be problematic and would also require using linked servers and has the same issue as the stored procedures.
Of these, the peer to peer replication would allow updating one database while the other is unavailable and when it becomes available replication would bring it back in sync with the other.
June 14, 2012 at 11:22 am
When you say peer to peer replication is this the same as transactional replication mentioned above in the post? I appreciate the list of alternatives and the reasons they may not work.
Steve
June 14, 2012 at 11:48 am
Peer to peer replication is a form of transactional replication. It allows updates on either side.
Also, if you aren't using Enterprise Editon of SQL Server 2005, it isn't available as an option unfortunately.
Here is a link to some information on it: http://msdn.microsoft.com/en-us/library/ms151196(v=sql.90).aspx
June 14, 2012 at 1:59 pm
I'm with Lynn. If the databases are on separate servers, Peer-to-Peer Transactional replication seems the easiest way to do it. You can just publish the necessary trables and should be good to go.
If not, I guess you could also cobble together a Service Broker solution, but at that point you're just rolling your own Transactional Replication without all of the cool bits that Microsoft's comes with. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply