Greetings all;

Well this past weekend I was able to successfully move a VLD from a VM environment to the Amazon RDS.

To highlight the tool set, I tried to keep things real simple. I used BCP along with Red-Gate's SQL Data Compare. I stuck with BCP with tables > 1 million rows and SQL Data Compare with the remaining tables. I also wrote custom merge scripts when dealing with tables with RI and were cumbersome to strip them down to load the data.

The effort wasn't 100% flawless requiring some quick thinking during the wee hours of the morning to move things along.

Looking back I would seem to think that Amazon should seriously consider opening up the file system for their SQL Server platform so VLD backups can be moved to the server and restored locally. That would save a lot of aggravation. Even if temporary access was given to initialize the database would make this process much easier.

The DBA is left with finding creative ways to push the data to RDS. This process was met with an initial failure. Our first attempt we ran out of time. I had developed a special sync process that would push the data through a Linked Server. Although this worked, the process was very very slow.

The other critical aspect to this was to make sure I didn't run out of room, which happened to me twice on one of the largest tables within the database.

Things I didn't think about until 2-3:00 am was to change the database recovery model to Bulk Load instead of FULL.

All in all I faced the challenge and accomplished the goal. It took about 2 days for me to catch up on sleep, but it was well worth the experience.

All the best.

Kurt Zimmerman