Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 ETL Batch Integrity Puzzle

Author  Topic 

jarthda
Starting Member

10 Posts

Posted - 2013-02-05 : 14:14:50
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 - 2013-02-05 : 23:22:27
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

52326 Posts

Posted - 2013-02-06 : 00:13:06
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
   

- Advertisement -