Synchronizing 2 tables in sql server

Question

I have a table on a server A and the same table on another server B. I want to update the table on server A with the data from server B once a day. The table on server A has over 100 million records. How can I do this so that while I am updating the data on the table on server A it is still available for reading with the previous info.

Intended behavior:

Server A:

create table tbl_transaction_test (
    tabid int identity,
    first_name nvarchar(255),
    last_name nvarchar(255),
    [address] nvarchar(255),
    update_dt datetime
)

Server B:

create table tbl_transaction_test (
    tabid int identity,
    first_name nvarchar(255),
    last_name nvarchar(255),
    [address] nvarchar(255),
    update_dt datetime
)

begin transaction transaction1
truncate table A
Insert into A.tbl_transaction_test 
     select * from B.tbl_transaction_test 
commit transaction transaction1

And at the same time I want to select from the table on server A.

How can I obtain the behavior that the data in the table on server A is changed only when the transaction is commited and this is done instantly(almost instantly).

Answer 1

Andomar already suggested how to make the entire new data set visible 'at once', by using a staging table and the good ole' sp_rename trick. There are variations on that theme, like for example using a partitioned table and switching out the existing data, then switching in the staging table, see Transferring Data Efficiently by Using Partition Switching.

Another approach is to use snapshot isolation. With snapshot isolation you can delete all rows in the table and insert the new ones in a transaction and it will not block any readers, as all the readers will continue to use the old row versions. See Understanding Row Versioning-Based Isolation Levels.

But one has to question the sanity of a scheme that involves transferring 100M daily. Is just unfeasible. So is the idea to manipulate 100M rows in a single transaction, the most likely result will be that your transaction log file will grow until they fill the entire drive and then the server will roll over and die.

What you most likely need is one of the solution suggested by Filip: replication, mirroring or log shipping. A good comparison of these solutions is the High Availability with SQL Server 2008 white paper.