SQL Server: migrate one table to discrete DB server

Question

SQL Server 2012 Standard

We have one table that is highly transactional (tons of inserts, no updates), that contains a BLOB object of 5kb-300kb per row. The whole table is about 31GB in size (table and indexes), and has 450K rows. There is a primary key (it is a clustered table), and there is a record creation timestamp col.

We want to move table to its own db server. We have already updated the application to reference this table via its own ADO connection string. Yes, we can take the app offline during a maint window to do this.

What is the best strategy to migrate a single table to another DB server?

Options we have considered: - Log shipping , but this replicates whole db... if we could log ship one table, we would do it for sure, we like log shipping - Transactional replication... we do NOT like transactional replication because it is such a monster.

One thought is to bring the app down briefly, and do a tsql command to

insert into <new db server.db.table> select from <source db server and table>

1. Can that work? Can we do a tsql command across discrete db servers?
2. What other method we should consider?

Answer 1

If it is one time, then you should use bcp out and bulk insert .. much better and faster than the linked server approach that you are thinking, since in the linked server approach if you run the query on the source server then you are pushing the data from source server to destination server which is an expensive operation. Alternatively, you can use SSIS as well.

If you need to keep the data in the new server sync with the old one, then your only option is to use transactional replication.

Also, refer to my answer on : Which one is more efficient: select from linked server or insert into linked server?

Answer 2

1. Yes it could. however...
2. I would use SSIS.

Depending upon the size of the table you could create an SSIS (.dtsx) package and execute in on the target server. This would also avoid the use of the linked server.

You could create this with the import wizard in SSMS and save the package.

If the table is massive, there are a few options and features within SSIS that will help you break the job into more manageable chunks. SSIS can also handle bulk insert.

If this table is the only object in the target database you could also consider putting the database into simple recovery mode or bulk logged. That may help minimize logging while you are copying data.

Provided you are correct that the source table is only inserted to, and not updated this should be relatively straight forward. After the copy you could use a linked server query to compare. (outer join on primary key)

BCP may have better performance, but db->db has the advantage of 1 less step and the storage requirements that go with it. The biggest advantage is that the ssis approach can be interrupted and you could easily resume where you left off.