This was a real move, moving a production site to AWS. Once the database structure was in place it was a matter of moving the data. I initialized the database using the EXPORT feature of SSMS. This was fine except where there were very large tables. The initialization process took over 30+ hours. The primary database remained on line during the entire process.

Since there was known drift in the data I looked for a variety of ways to sync up the two instances. The Idera SQL Data Compare worked great! I was able to get 90% of all tables in sync. The remaining 10% which were the larger tables I chose to BCP into a staging table in the target database. With simple MERGE logic I was able to sync the remaining tables.

End to end the data transfer process took from Friday night, midnight to Sunday morning about 10:30am.

The killer in the entire process was dealing with TEXT & NTEXT fields in a number of the very large tables. Just a simple BCP on those tables took hours to export/import.

I'm so glad I went through the effort. I learned a lot with what you can and cannot do. I will say this, I have some really neat procedures that managed the MERGE process that I chose to abandon during the final push but certainly could be used for small/medium sized databases.

Kurt