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>
- Can that work? Can we do a tsql command across discrete db servers?
- What other method we should consider?