Moving Large Number of Records between Databases Involved in AlwaysOn.

Moving data from Transaction database to Archive/Reporting database. After the records has been moved, delete the records to avoid duplicates.

Note:

• If your database is involved in AlwaysOn or in Full Recovery mode, make sure you backup the database and log file to clear out the log files on the database before preceding.
• After you have finish with the record move, you should clear out the log files on the database to avoid running out of log space.

Duration

Action	Records	Duration	Log Growth
INSERT	20 million records	27:15 minutes
DELETE	20 million records	1:33 minute
INSERT	50 million records	1:00:24 hour	112GB after 70 million record inserts
DELETE	50 million records	7:00 minutes	55GB after 70 million record deletes

Example

SET IDENTITY_INSERT [Server01].[MyDb01].[dbo].[tbMyTable] ON

INSERT INTO [Server01].[MyDb01]..[dbo].[tbMyTable]  
(
	[iColId]
	,[vcCol]
)
SELECT      
	[iColId]
	,[vcCol] 
FROM 
	[Server02].[MyDb02].[dbo].[tbMyTableARCH] WHERE iColId > 1000000

SET IDENTITY_INSERT [Server01].[MyDb01]..[dbo].[tbMyTable] OFF