SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 ETL Batch Integrity Puzzle
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jarthda
Starting Member

10 Posts

Posted - 02/05/2013 :  14:14:50  Show Profile  Reply with Quote
We don't have a EDW or data staging environment yet but have data bridges in place to get three tables amounting to 1.5GB of data(8 million rows) and need to move them to a co location according to our SLA. We have 2 hours and 24 minutes on Saturday or Sunday.

Assuming I move the backups across the network and restore, now I have the source data on the production target. What is the best strategy to update the target tables whith batch integrity in mind?
Start a transaction, delete the target tables,then insert.... commit.
If this take 60 minutes what will be the user experience for the case where someone starte browsing data before I started my deletes?

this is a very basic RDBMS question I feel.
User of a web app connects to SQL and browses data from three tables. My ETL transaction deletes the tables and Inserts new data. does the user have access to the 'pre deleted' snapshot of the tables or do any new reads need to wait until the ETL load is done?

I hope this makes sense and appreciate any insight.


Jarthda
Embarcadero/SQL Server/and now... Informatica

srimami
Posting Yak Master

160 Posts

Posted - 02/05/2013 :  23:22:27  Show Profile  Reply with Quote
If a user starts browsing data before your ETL load, he will have the data in the session id he opened. For e.g. let me say, user fired select * from xyz table and got the result set as 250 rows. Your ETL load finished downloading data (Delete and Insert) which has 350 rows now. If he fires the same select statement it would show the updated records with 350.

If user is firing queries at the time of your ETL load, he will get the result with most updated records.

Hope this helps.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/06/2013 :  00:13:06  Show Profile  Reply with Quote
you may be better off giving the user a read only snapshot whilst you do the delete insert on background. This will not have any downtime on user accessibility of the data. at the end of process your updated data would be available to users as well.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000