Sql-server – Refreshing a table using CREATE TABLE, DROP and Rename

Looks like your clustered index page splits are going to be painful because the clustered index holds the actual data and this will need new pages to be allocated and the data moved to these. This is likely to cause page locking and thus blocking.

Remember also that your clustered index key is 21 bytes and this will need to be stored in all of your secondary indexes as a bookmark.

Have you considered making your primary key identity column your clustered index, not only will this reduce the size of your other indexes, it will also mean that you will reduce the number of page splits in your clustered index. It's worth a try if you can stomach rebuilding your indexes.

Modifying their structure is generally frowned upon

Replication is more than likely out and I'd throw Sync out before that. (from real life high transacitonal tests on Sync Framework)

If 3-4 hours is your data latency exceptance, log shipping will probably be your bests bet here on a read-only copy. But how much change is happening in the log? find that out be monitoring it to see how quickly and how much you need to push across.

If you can't go to Mirroring or upgrade to 2012 enterprise, that is out although that would be a sound strategy if you can go Enterprise if not on it.

SSIS isn't meant to just dump data over but it can do it. You're looking at far too much in the terms of lookup transformations though and the task would be expensive in time and resources. Although, like I said, it can do it.

Really, there will be a distinct narrowing of choices based on answering a few questions

• Data latency acceptance
• Amount of data change in a given minute,hour and day Connectivity to the secondary
• Read requirements on the secondary instance
• Up-time of the secondary instance
• Alterations to existing schema and all objects
• Security