CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Corovei Andrei
Corovei Andrei

Reputation: 1668

Synchronizing 2 tables in sql server

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).

Upvotes: 3

Views: 4307

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294267

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.

Upvotes: 2

Filip Popović
Filip Popović

Reputation: 2655

Maybe You should just start from High Availability Solutions Overview article if You want built in functionality. Note that for using these functionalities You should have appropriate privileges on both servers. In short:

  • Replication - keeps source database and target databases synchronized by
  • Log shipping - sends transaction log backups from one database/server to one or multiple databases/servers and applies them on destination databases
  • Mirroring - basically, it sends stream of active transaction log records to destination server and apply it to destination database

Don't forget to check whether Your SQL Server Editions and licenses support Replication/Mirroring/Log Shipping (or Cluster-Failover mentioned in article if You decide to use it).

Or You can make a custom solution (replication-like) with trigger on source table:

  1. Trigger will keep changed records in log table with timestamp column and status (updated, inserted, deleted).
  2. Copy this separate table to destination server where timestamp > last_timestamp_you_transfered on predefined interval (use job)
  3. Apply changes to destination server (changes where timestamp > last_timestamp_applied)

Notes:

  • You can always keep log table small deleting records where timestamp is less than the last processed
  • You can avoid job by writing application that will do the transfer on each x seconds/minutes

Every proposed solution requires some permissions on those servers. Either You need permissions to set replication, mirroring, log shipping or create triggers+job.

Upvotes: 2

Andomar
Andomar

Reputation: 238086

One way would be to use a staging table. You copy the entire source table into a new table, and when the copying is done, you drop the old table and rename the staging table.

select  * 
into    A.tbl_transaction_test_staging
from    B.tbl_transaction_test

begin transaction
drop A.tbl_transaction_test
exec sp_rename 'A.tbl_transaction_test_staging', 'A.tbl_transaction_test'
commit transaction

The rename operation is fairly fast.

Upvotes: 1

Related Questions