sql server 2008 - Determining the most effective and safest table locks during bulk data copy -

sql server 2008 - Determining the most effective and safest table locks during bulk data copy -

this going 1 of worst-of-the-worst situations you're going want suggest exclusively different. don't worry, know, , unfortunately there's nil can it, please seek limit answers ones explain smallest alter possible create biggest impact.

with said, here's situation: there process replicates info sybase server (15.5) sql server (2008 r2) every 30 minutes during business hours. on sql server, there linked server sybase database copying tables. here's scary part: copying done deleting of existing rows , inserting new ones sybase, in fashion (within stored procedure on sql server, triggered enterprise scheduling software):

-- table 1 delete abc1; insert abc1 (col1, col2, col3) select col1, col2, col3 linked.server.dbo.abc1; go -- table 2 delete abc2; insert abc2 (col1, col2, col3) select col1, col2, col3 linked.server.dbo.abc2; go -- ... , on hundreds of tables

there no indices, primary keys, foreign keys, or info integrity whatsoever on sql server copy, can command table schemas; cannot, however, touch sybase server. mssql database read-only environment reporting , lookups. performance isn't critical. it's been way years , lives on...

if haven't vomited yet, might have thought of problem: every 1 time in while, couple of seconds, our applications , reports cannot find (and cannot access) info in tables while beingness deleted , inserted to.

i'm no dba, things table locks, hold locks, exclusive locks, etc. foreign me, gather perhaps of help in situation. after reading through msdn's article on table hints, brain telling me should transactions , table locks. have no thought what, say, next do, because unsure how test it:

set transaction isolation level serializable go -- table 1 begin transaction updateabc1 delete abc1 (tablock, holdlock); insert abc1 (tablock, holdlock) (col1, col2, col3) select col1, col2, col3 linked.server.dbo.abc1; commit transaction updateabc1 -- table 2 begin transaction updateabc2 delete abc2 (tablock, holdlock); insert abc2 (tablock, holdlock) (col1, col2, col3) select col1, col2, col3 linked.server.dbo.abc2; commit transaction updateabc2

so questions are:

would modifying stored procedure incorporate transactions , locking allow requests tables modified carry on without coming empty and/or causing deadlocks? if not, , if alter (on sql server), be?

sybase , microsoft sql server @ point normal database replication possible, , has been considered, task unwieldy our grouping , our budget.

an idea... shuffle tables 1 beingness queried isn't 1 beingness loaded.

you can utilize alter table .. switch. switch can move info between identical tables in filegroup. note, target table must empty may not suit setup.

in case, consider hiding tables synonyms instead.

sql-server-2008 sybase database-replication high-availability