Running SQL Server workloads in the cloud can yield tremendous price/performance benefits, but dialing in the optimal cloud consumption model can be a challenge.
I recently moderated an MSSQL Tips webinar, entitled Rightsizing Your Cloud Consumption on AWS.
In the webinar, SentryOne Solutions Engineering Manager, Denis McDowell, and AWS Partner Solutions Architect, Tom Staab, shared guidance to help attendees optimize their cloud resource consumption on the AWS platform.
We covered how to:
- Accurately assess your cloud resource needs
- Evaluate licensing models
- Systematically track cloud spending
- Optimize the performance of databases on AWS to avoid over-spending on consumption
If you missed the live presentation, click here to view it on-demand for free.
There were so many great things to discuss during the webinar that we ran out of time and weren’t able to chat through the great questions submitted by attendees. We compiled all the questions and shared the answers below.
Webinar Q&A
Q: Are compatibility modes fully supported on Amazon RDS? For example, could I run SQL Server 2014 on compatibility mode 100?
Staab: Yes. The compatibility level is set at the database level and is configurable for RDS instances.
Q: How many databases can an Amazon RDS instance support?
Staab: This depends on the instance size and configuration but varies from 30 to 100. Please review this Amazon RDS resource for more details.
Q: Will Amazon RDS support linked servers in the future? How soon?
Staab: Amazon RDS SQL Server does support linked servers. For additional information, please read this updated AWS blog on the subject.
Q: What support is there for SQL Server Reporting Services (SSRS) on AWS? Is my only choice to run it on an EC2 instance?
Staab: SSRS, SQL Server Integration Services (SSIS), and SQL Server Analysis Services (SSAS) are only available on Amazon EC2 at this time.
Q: How does troubleshooting SQL Server performance differ in RDS than on-prem SQL Server?
Kline: The main difference is that you cannot perform the same sort of adjustments to the Windows OS-level settings in RDS than compared to on-prem since RDS manages all of that for you. Because of that, it’s important to focus your attention on the things that you can troubleshoot, such as database indexing choices and SQL code. That’s where the SentryOne products excel—DB Sentry for instances of RDS and Plan Explorer for tuning queries.
Q: Are there any published stats for the latency a user should expect when using an Amazon service (like always 10ms to get the connection established)?
Staab: I’m not aware of anything published on this topic.
Q: What sort of migration helper tools are available?
Staab: AWS has tools and services, and various third-party tools are available as well. Please review the information here for guidance on tools, services, partners, and more.
Kline: I also like the AWS data platform migration tools, for example, to move between Oracle and Aurora DB. More details about this feature are available here.
Q: For capturing perfmon stats, what frequency do you recommend? For example, would you leave perfmon capturing counters constantly?
Kline: First, I would be remiss if I didn’t point out the Amazon has a number of products for monitoring RDS-all described here. For example, Tom mentioned both Amazon CloudWatch and Amazon Enhanced Monitoring as possible ways to see performance data. Plus, I work for a database performance monitoring company and would strongly encourage you to look at our products, such as DB Sentry, SQL Sentry Essentials, or SQL Sentry. And finally, for completely free query tuning help, don’t forget Plan Explorer.
Having said that, in those rare cases when you have to resort to PerfMon, you must carefully balance the overhead of frequent PerfMon polling versus the value of the data it provides—not to mention the cost of storing and manipulating the performance data. PerfMon is a lot higher overhead than SentryOne polling, about 3-5% versus less than 1% for SQL Sentry. Also, note that PerfMon is cumulative. So, you add 3-5% more overhead for each additional person running PerfMon. (For what it’s worth, you can save yourself some of the headaches of using PerfMon by sticking to the SQL Server DMV called sys.dm_os_performance_counters and writing the performance data directly to a table within your RDS database. You then query the table where you store that performance info, rather than directly invoking PerfMon).
Having gotten past all the caveats and provisos, I would recommend capturing performance data continuously while users are active (if no users are active, see if you can entirely pause the instance to save money). Depending on the kind of workload and criticality of the database/instances, I tracked between 25-75 PerfMon counters with a default polling frequency of every three minutes for business-critical workloads but adjusted up or down (for both the number of PerfMon counters and the frequency of polling) according to the criticality of the database/instance.
For an even shorter list of important PerfMon counters, check out my former colleague Allen White’s blog post, “15 SQL Server Performance Counters to Monitor.” He recommends 17 PerfMon counters, several of which you’d have to exclude because they’re Windows counters not available on RDS.
Additional Resources
If you haven’t already, I hope you’ll view our Rightsizing Your Cloud Consumption on AWS if you haven’t already. As an added resource, check out Denis McDowell’s blog “Containing Costs in the Cloud by Rightsizing Your Data Platform” for an overview of the importance of using baselines and capacity planning to size your cloud implementation properly.
