Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » SQL Server High Avilabilty Solutions
Add to briefcase
14 posts, Page 1 of 2
12»»

SQL Server High Avilabilty Solutions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Nick123-481228
Posted Wednesday, December 22, 2010 2:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:25 PM
Points: 50, Visits: 529
Hi,

We have financial database size of around 400Gb and we do daily batch updates which takes nearly 30mint to 45mints.During this time one of very important Price table is locked half of this upload process becuase it has to populate calculated columns.

Our management asking me that we need our database which should be used on 24/7 and 365 days but aslo we have to get into daily updates? How can be possible?Users should not be blocked any point in time and aslo they should not getting dirty reads.

Can some one please suggest me best way of achieving this problem.

Thanks
Nick
Post #1038133
grahamc
Posted Wednesday, December 22, 2010 5:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 563, Visits: 943
What is happening that the Price table is locked for so long? Price updates? Where do the changes come from?

I would consider a 2 table approach... 1 online table (current) and 1 offline table (can have updates and changes made). You will need some sort of control flag (additional table, perhaps) that tells which pricing table to use.

IF flag = 1
EXEC proc1
ELSE IF flag = 2
EXEC proc2

Something like that....
Post #1038184
Nick123-481228
Posted Wednesday, December 22, 2010 5:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:25 PM
Points: 50, Visits: 529
Thanks for your advise.

On price table it does :

if Price Date exists with different value then moves data from Main table to archieve table
then Updates with new values on Main table
then insert complete new values on Main table.

I source of data comes from differnt SQL database.

In your solution when do you sync both tables becuase our updates are happens on daily basis and which required lot of changes in application level.

Thanks
Nick

Thanks
Post #1038191
grahamc
Posted Wednesday, December 22, 2010 5:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 563, Visits: 943
It depends on the process, but, if the update is done once a day, then on day 1 you update table 1 while table 2 is still in use. You then switch over to use table 1 and can then update table 2, that way both tables should always be the same.

There are other options with regards to "trickle feed" of the data from the other SQL DB, etc.
Post #1038194
Nick123-481228
Posted Wednesday, December 22, 2010 8:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:25 PM
Points: 50, Visits: 529
Can you please let me know what are other solutions?

What is the trickle feed?

Thanks
Nick
Post #1038309
drawlings
Posted Wednesday, December 29, 2010 2:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 4:19 PM
Points: 44, Visits: 374
Nick,

Delete unchanged records from the staging table prior to the mass update. This removes the "has price changed" logic from the Product table update.

Dan
Post #1040594
GilaMonster
Posted Wednesday, December 29, 2010 2:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 37,689, Visits: 29,948
Have you considered one of the snapshot isolation levels?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1040598
Oliiii
Posted Monday, January 03, 2011 3:09 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 194, Visits: 685
The usual easy way to fix this problem is to upsert your data in small batches so the lock time are tiny and users won't notice anything (if you have a clustered index and manages to use only the smallest lock possible).

The total run time of your job might be longer but you'll not lock down everything while you do it.

You could also have a look at partitioned tables (load the new data in a table and then switch it to the main table once it's ready).
Post #1041750
Jeff Moden
Posted Monday, January 03, 2011 6:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790
Nick123-481228 (12/22/2010)
Thanks for your advise.

On price table it does :

if Price Date exists with different value then moves data from Main table to archieve table
then Updates with new values on Main table
then insert complete new values on Main table.

I source of data comes from differnt SQL database.

In your solution when do you sync both tables becuase our updates are happens on daily basis and which required lot of changes in application level.

Thanks
Nick

Thanks

Gosh... I feel your pain but there's still not much anyone knows about your data or meta data. I recommend you start by taking a look at the article at the last link in my signature line below. You'll probably get better help that way. Otherwise, one guess is as good as another. Please be sure to tell us which version (Enterprise, Standard, ???) you're using, 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1041790
ALZDBA
Posted Monday, January 03, 2011 6:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 6,861, Visits: 8,048
how about using a staging table to prepare all your new data and only at the very last merge the data into your target table ?

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1041793
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.