3 Tips for Running SQL Server Databases in the Amazon Cloud

3 Tips for Running SQL Server Databases in the Amazon Cloud

 In Software Development
The key to long-term success with any server technology is to plan for failure. Amazon’s cloud infrastructure (the official name is Amazon Web Services or AWS) has a lot of advantages when compared to running servers on-premises or using other vendors. However, no cloud vendor (including Amazon) has a 100% fail-safe hosting platform.
This blog post will highlight a few tips we learned as we have migrated our clients’ applications to AWS. The diagram shown here represents a typical “mirrored” server architecture.

1. Leverage “Zones”

Amazon maintains a number of data-centers around the world. These data-centers are called Regions. Regions in the US include East (“Northern Virginia”) and two West (“Northern California” and Oregon) Regions. Each Region is divided into “Availability Zones”. Zones are physically isolated data-centers within a Region. The key point to remember is that issues within a Zone typically do not impact other Zones (or Regions). We usually recommend to our clients that they use any of the following to guard against a Zone failure or performance problems:“Mirrored Servers” – Duplicate web servers are placed in different Zones. Amazon’s routing service (“Elastic Load Balancer”) will distribute web traffic to available servers in a round-robin order.

“Database Mirroring” – Microsoft SQL Server databases should be mirrored across Zones. When a SQL “Witness Server” is used, automatic fail-over to the mirrored database occurs when the primary database is not available. My colleague Patrick Jasinski has written a detailed guide to setting up SQL Mirroring using machine certificates; you can read it here.

2. Use Dynamic Server Names

When an Amazon Windows server is restarted by default it may have use different machine name. It is tempting to set a permanent server name (e.g., “SQL-A”). However, we found that setting a fixed name interfered with establishing the initial SQL mirror. We installed the latest SQL 2008 R2 service packs on the primary, secondary, and SQL Witness servers and tried the SQL Mirror Wizard a half dozen times. No matter what we tried, the mirror would not establish until we reverted the servers to dynamic server names.

3. Applications should connect to the server using the “Public DNS” address

When an Amazon server is started it will have an internal IP address and a “Public DNS” name which maps to the internal IP address. Each time the machine starts (or is rebooted) it may have a different internal IP address/Public DNS name. To provide a consistent identifier across machine restarts we typically assign the same Elastic IP (“EIP”) address to the server. In addition, all applications (whether running in Amazon or outside of Amazon) connect to the servers using the Public DNS name, instead of the EIP or internal IP address. In addition to providing a consistent address, the Public DNS name will avoid unnecessary routing and data charges.
If you have further questions on how you can use Amazon Web Services or other cloud infrastructure platforms, feel free to contact us for a free consultation.
Photo Credit: https://www.flickr.com/photos/8058853@N06/5715738184
TellUsWhatYouThink
, , , , ,
Recent Posts