Let's step back a little: you're basically switching from a SQL Server that someone else manages, to managing your own server.
Q: What are the security challenges involved with running your own server?
You can get hacked, or you can incorrectly set permissions to let strangers access your data and publish it. Here are some resources to get you started:
Q: What are the high availability challenges involved?
Amazon RDS for SQL Server uses database mirroring for high availability. In your own EC2 instances, you can choose whether you'd like a similar level of protection with mirroring or Always On Availability Groups, or if you'd like to save money and get less protection.
You didn't ask about patching, but I'd bundle that in here: the burdens of patching will now be on you rather than Amazon.
Q: Can I make my migration restores go faster?
Right now, Amazon RDS only gives you access to full backups. Your backup/restore migration timeline is:
- Shut down your app
- Back up RDS to S3
- Restore the S3 file backup to EC2 SQL Server
- Start your app again
That's going to result in long downtimes for a 200GB database. To shorten it, you can use tools like Red Gate's Data Compare or build your own comparison tool to detect what changes have been made to your database. For example, on one project, the developers added LastUpdated timestamp fields to all the tables, and maintained those with triggers. Then, after our restore, we could shut down the apps, copy across the specific records that had been updated, and go live with only a few minutes of downtime.
Q: What time zone should I use for my EC2 servers?
Just as RDS was configured with UTC, so should your SQL Servers. Just set your EC2 boxes to be in UTC time zone regardless of their location, and then you won't have problems when you fail over from one region to another.