With some interesting experiences under our collective belts when it comes to working with SQL Server on AWS, it seems like it might be interesting to share some of the things we’ve learned and seen in the field.
AWS’ RDS with SQL Server is a great SaaS deployment of SQL Server that gives you the benefit of the fully-managed environment for your SQL Server, while still giving you great access for administration of that SQL Server. There are some interesting reporting bits as well, and they can lead to some good optimization options.
– More visibility. You get a lot more granular visibility moving to SQL Server as a service. This comes from the fact that it’s a (nearly) dedicated environment running your database systems, so any traffic or activity you have full control over. You also won’t be tempted to load “just one more little application” on that SQL Server out of laziness or need for resources. I mean, if you did that sort of thing, of course. The thing that has been a great help here is using this better reporting and visibility to really understand the workload and performance loading of the instance. Using that information you can set up any batch type of operations to run at times when you know for a fact systems are at their lower utilization points. We’ve used this on a few different operations where we were able to group those operations to stack into those slower times and not be worried about other influencing operations on the server overall.
– Be aware of the differences in cutover processes. Yes, you can restore from S3, but there are rules around this, specifically no incrementals, things like that. So make sure you fully understand the types of things to expect and how that operation works. If you have the usual suspects like encryption, username and password accounts, other things that need to come across, make sure you go through the conversion steps ahead of the big day several times. Anytime you don’t receive a predicted response (that user account doesn’t have permissions correctly assigned for some reason!), go back and understand what happened. It’s worth the time before you’re doing it for real. There are subtle differences and, sometimes, you’re using the opportunity to also upgrade to the latest engine. If that’s the case, you’ll need use-case testing on the new instance to make sure all is well. For example, in one case, we saw where the SQL Statements were referencing a table alias. The alias wasn’t defined, but there was only one table in the query, so old SQL Server didn’t trip on it. New SQL Server did. It was a subtle thing, but it was a thing.
– As with any upgrade, have a fall-back plan. Don’t backup your database and kill the SQL Server VM that was running it. Keep that system alive until you know all is well. Most importantly, the most helpful things is to have go, no-go points in the process. If you get to a certain spot and don’t see a specific result, it’s time to go back to square one and figure out what’s up. If you define these go, no-go situations ahead of time, you won’t be left thinking “just a few minutes more, I can figure this out…”
– We’ve seen far better performance (I wrote about this earlier as well) and have been able to downsize some instances once they settled in and we checked out how they were really working with the workload. Don’t be afraid to re-evaluate after your systems have been up and going for a couple of weeks. You may be able to downsize those instances or may need to upsize them depending on the workload. Don’t make assumptions though about how things will play out as the systems start to absorb those production workloads.
– Check and re-check your firewalls and security settings as you go. Many times during setup, you’ll open ports or assign permissions to logins that you don’t want in place after the migration. Makes notes about those as you go. We’ve seen many cases where IP ranges and such were left allowed to get in that had no business doing so. With notes about your changes, you’ll be able to quickly go back and clean those up and better secure the system after the migration.
What are your tips for AWS SQL Server?
