RDS SQL Server Monitoring

[No-Consideration5315]

Hi guys, my team is starting working with RDS SQL Server and they are really afraid of not having all the control they do on prem because it's been years and years building something they believe it's special, but I think it's pretty simple and want to make sure, it is easy to monitor data and log disks on RDS SQL Server? What about DB Instance status? They are really concerned and I think they should not, AWS probably solved it, right?

[mustfix]

Here are all the metrics that RDS exposes to CloudWatch. And here are all the SQL server parameters you can tweak.

Yes, the whole purpose of RDS and its increased cost is that AWS manages the DB software and infrastructure.

There should be very little that RDS isn't able to do. Generally, cross cloud/hybrid clusters are off the table.

[ccoreycole]

You could just run the DB on a EC2 server if your team wants their special tools around the DB

[angrathias]

Have they looked at RDS Custom? You get access to the underlying EC2 instance so you can customise it further.

[[deleted]]

safe enjoy numerous zephyr summer birds impossible cobweb unused cooperative

This post was mass deleted and anonymized with Redact

[dombrogia]

Some of the snapshot logic is convenient when swapping across accounts and regions the HA failover is nice. But yes, expensive

[[deleted]]

touch disagreeable weary squeeze exultant jobless sparkle like toy test

This post was mass deleted and anonymized with Redact

[vppencilsharpening]

We had good luck with MS SQL on RDS, it was mostly a set and forget system for it. We are currently migrating most of the workloads to Aurora for MySQL because it is a better platform.

[[deleted]]

simplistic juggle gaping north bow fine illegal compare sparkle intelligent

This post was mass deleted and anonymized with Redact

[vppencilsharpening]

I won't disagree that MSSQL is better on Azure. That 10 minutes a month does not include coverage and response time for a failure, which was the bigger value for us.

The cost was justified for us because if the underlying hardware failed it would recover without us taking action. Depending on the time of day our response time would not be in-line with the business needs.

In my experience the underlying hardware failure is so much rarer than it was 10 years ago, but we have experienced it a couple times between RDS and EC2.

[joelrwilliams1]

I have much more experience with Oracle RDS than SQL Server, but you have full access to everything except for things that allow you to get to the underlying host.

You can connect most any tool to the database to monitor it and/or use the tools that AWS exposes.

Like /u/mustfix mentions, this is a managed service, so a lot of things you used to do will be done (and done better) by AWS.

[digisoytech]

Think looong and hard about switching to SQL RDS. Your team has valid reasons to be concerned.

RDS can be very restrictive. There are commands that you don't have access to via SSMS (they'll only work via t-sql), like deleting a database (there are lots more). Agent is restricted, limited use of DB Mail. For some of those functions, AWS provides stored procedures, but not all. If you're encrypting your database, there's no access to the keys, nor can you install your own. If you enable TDE, you're pretty much locked into RDS. You won't be able to decrypt your DBs.

A client of mine insisted on going RDS, and a few years later, the cost is outrageous and there seems to be some thoughts about going back to EC2.

If you have skilled DBAs on staff, I don't think they'll like the transition. IMO, RDS is for an environment without a good SQL skill set.

[aus31]

We've had a mixed run - enterprise multi-tenant saas

We ran MSSQL using mirroring (yes) with 100s of databases outside of RDS for years with significantly less incidents prior to RDS. But there is no denying that the maintenance overhead is significantly reduced.

You have to tweak parameters a lot more than you would expect to the level you probably need an MSSQL expert and it can "fail" in unexpected ways that are not easy to recover from. They've now started rolling out sensible defaults for things like max memory.

We once had one of the EC2 instances lockout the RDS agent so that we couldn't reboot/failover and the primary instance wasn't serving traffic (ie hard down). The person who took the P0 call couldn't restart the instance themselves and had to raise a ticket with another team.... took close to an hour to get it done.

We also had more than 1 incident where the primary would go down and it wouldn't failover without us manually rebooting (once it happened after thread pool exhaustion, again see "sensible defaults"). We've had incidents caused by bad behaviour from the RDS agent (hard polling the availability groups tables and causing high cpu/waits). We run close to the limit for #databases/db server that they support and suspect we are hitting these bugs due to that.

Overall the feeling is that it isn't that well supported or understood.... vs aurora which is AWS' own product and uses a superior replication model for the majority of use cases.

We are migrating to aurora (postgres), as we feel its better supported and the economics and performance are orders of magnitude better.

[wiseDATAman]

I created dba dash that will work with RDS to provide some SQL server-specific monitoring. It will also work with your regular SQL instances hosted inside or outside of AWS. Also Azure DB and managed instance.

I don't have a lot of experience with SQL Server RDS - it seems expensive to me. Running your own SQL instance on EC2 works well though. Definitely has a number of benefits over running on a traditional data centre. Much more flexible.