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.
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.JarthdaEmbarcadero/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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|