Updating 2 databases at the exact same time
-
I have 2 databases that contain some duplicate data.
One is a ODS and the other is a DW.
Both are located on the same server.
I have a dispatch table in each database that tells me wether the data is the official version or not (by dates).
Both databases can be accessed at any time by a number of users.
My concern is the following : I have to make sure that both dispatch tables are updated at the exact same time.
If my ODS dispatch table is updated first, the users would miss some data.
If my DW dispatch table is updated first, the users would obtain duplicates.
As this is sensible accounting data, these two scenarios are unthinkable.
I'm using SQL Server 2008R2 and SSIS/T-SQL.
Any idea ?
Would a simple transaction do the trick ? Do I have to worry about read uncommitted ?
Thanks,
Vincent
-
Vincent_Poirier (1/6/2014)
I have 2 databases that contain some duplicate data.One is a ODS and the other is a DW.
Both are located on the same server.
I have a dispatch table in each database that tells me wether the data is the official version or not (by dates).
Both databases can be accessed at any time by a number of users.
My concern is the following : I have to make sure that both dispatch tables are updated at the exact same time.
If my ODS dispatch table is updated first, the users would miss some data.
If my DW dispatch table is updated first, the users would obtain duplicates.
As this is sensible accounting data, these two scenarios are unthinkable.
I'm using SQL Server 2008R2 and SSIS/T-SQL.
Any idea ?
Would a simple transaction do the trick ? Do I have to worry about read uncommitted ?
Thanks,
Vincent
Yes a transaction would solve this...with the exception of anybody using read uncommitted. Of course since you say this is sensitive accounting information I would think that is forbidden anyway. Anybody using read uncommitted or nolock in this type of environment should be lined up in front of pork chop firing squad.
_______________________________________________________________
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/ -
Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it with the "current" data. When everything checks out, repoint the synonyms to point to the newly updated tables. The next update, simply reverse the process.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And here I was looking forward to the high-velocity pork chop firing squad. 😀
-
Ed Wagner (1/6/2014)
And here I was looking forward to the high-velocity pork chop firing squad. 😀Well Jeff is the captain of the guard so all we need is a few more guys in black hoods carrying sacks of chops ready to load. 😛
_______________________________________________________________
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/ -
Jeff Moden (1/6/2014)
Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it with the "current" data. When everything checks out, repoint the synonyms to point to the newly updated tables. The next update, simply reverse the process.Do you think I can rely on both synonyms being rebuilt at the same time?
Like the pork shop idea by the way... They count everything but the number of accountants... maybe I should try that 😉
-
Vincent_Poirier (1/6/2014)
Jeff Moden (1/6/2014)
Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it with the "current" data. When everything checks out, repoint the synonyms to point to the newly updated tables. The next update, simply reverse the process.Do you think I can rely on both synonyms being rebuilt at the same time?
Like the pork shop idea by the way... They count everything but the number of accountants... maybe I should try that 😉
Absolutely not. They can't be rebuilt at precisely the same time unless you have a timed window for each synonym that well all execute at precisely the same time. That can be complex so I recommend that you suffer with the 3 milliseconds that each repoint will take. :-):-D:-P;-)
In all seriousness, I suppose that something could get in the way of a synonym repoint but I've never had it happen, yet.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
_______________________________________________________________
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/ -
Sean Lange (1/6/2014)
Oooooo.... the classic MK I. I haven't seen one of those in a long time. It was one of my favorites even though the velocity and accuracy were a bit low. It would launch high enough to come in almost vertically and gave one enough time to hide the launcher so the subject never knew who hit him. It was almost silent, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/7/2014)
Oooooo.... the classic MK I. I haven't seen one of those in a long time. It was one of my favorites even though the velocity and accuracy were a bit low. It would launch high enough to come in almost vertically and gave one enough time to hide the launcher so the subject never knew who hit him. It was almost silent, as well.Of course I added chop to suite.
These are perfect around the office. Just enough velocity to launch over the cube wall. Appropriately timed you can rain down porkchops from multiple sides all at once if you can find some assistants. 😉
_______________________________________________________________
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/ -
Jeff Moden (1/6/2014)
Vincent_Poirier (1/6/2014)
Jeff Moden (1/6/2014)
Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it with the "current" data. When everything checks out, repoint the synonyms to point to the newly updated tables. The next update, simply reverse the process.Do you think I can rely on both synonyms being rebuilt at the same time?
Absolutely not. They can't be rebuilt at precisely the same time unless you have a timed window for each synonym that well all execute at precisely the same time. That can be complex so I recommend that you suffer with the 3 milliseconds that each repoint will take. :-):-D:-P;-)
In all seriousness, I suppose that something could get in the way of a synonym repoint but I've never had it happen, yet.
But how do you repoint a synonym without actually dropping it ??
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply