Questions About RDS SQL Server You Were Too Shy to Ask

There are a number of different ways that you can host SQL Server. RDS SQL Server, for example, uses SQL Server within AWS as a simple database service, much like a more versatile alternative to MySQL. Obviously, it is a compromise, in that you lose many of the extras beyond the database. Laerte Junior answers those questions about RDS that people seem to assume you know the answers to, but which you may be too shy to ask.

  1. What is RDS SQL Server?
  2. What versions of SQL Server are supported on RDS?
  3. How do I copy an existing SQL Server database into RDS?
  4. Do I have SA rights in RDS SQL Server?
  5. If lose the password of the master user what would I need to do to get access back?
  6. I got an error saying I was missing the rights to create a new database, but I am using Master Account – how can I fix it
  7. Can I set up alerts and email notifications in RDS SQL Server?
  8. My Database has a lot of scheduled jobs, are these OK on RDS?
  9. How are ETL processes such as SSIS affected? Can I still use BCP to transfer data?
  10. I need to be able to be able to say, for compliance, in which legislative area the data is held. Can I do this with RDS?
  11. Are there any limitations of the way you can resize your database?
  12. Can I take snapshots of my RDS databases?
  13. Why should I use RDS rather than SQL Azure?
  14. How do I run SQL Server instances with SSAS or SSRS in RDS?
  15. Does RDS replace the host automatically?
  16. How do the automatic backups work?
  17. How does RDS SQL Server provide High Availability?
  18. Can I use native backup and restore from SQL Server?
  19. Can I use SQL profiler in RDS SQL Server?
  20. Can I use extended events?
  21. Do I have access to the OS to run PowerShell scripts?
  22. Can I use Encryption?
  23. I need to use Always Encrypted, is this possible?
  24. Is Instant file initialization allowed?
  25. Can I backup more than one database at same time?
  26. As it is a managed server, do I have a service to re-index, rebuild, update statistics of my indexes; in other word a maintenance plan as part of the service
  27. Do I need a DBA to use RDS SQL Server?
  28. So having read the pros and cons of Managed Service, why I would use RDS SQL Server instead of EC2 with SQL Server?
  1. “What is RDS SQL Server?”

    RDS SQL Server is SQL Server in the Cloud, but some of the services are managed by AWS. That makes your life as an application developer rather easier, but it is inevitably a compromise. To get that level of management, you sacrifice host-level access, and therefore full control of both the Operating System and your SQL Server Instance.

    RDS SQL Server is very much a managed service: DaaS – Database as Service – which means that AWS will take care of the Infrastructure, High Availability, Backups, Reliability, Security and Software patching.

    ‘Wow Laerte, that’s awesome!’

    Well; as with everything in SQL Server, it depends. It could suit your requirements very well, but remember that you will surrender control of the more important features of SQL Server and Operating System in exchange of this managed service.

    ‘Is it worthwhile?’

    The advantages of a managed cloud-based service are obvious, but the answer depends on the extent to which your business can allow you to lose full control of these features I’ve just mentioned. In many cases, it can be a good solution. Think of it more as a grown-up alternative to managed MySQL in the cloud.

  2. “What versions of SQL Server are supported on RDS?”

    All the useful ones. RDS SQL Server supports SQL Server version s: 2008 R2, 2012, 2014 and 2016 and a lot of license models including (Express, Web, Standard and enterprise Edition). For a more accurate information on license models, check here on the Amazon site

  3. “How do I copy an existing SQL Server database into RDS?”

    Actually, to migrate to RDS SQL Server you have three different possibilities. You can …

    • Use a native backup on-premise, copy to an S3 Bucket and restore from there.
    • Move the data with BCP. To do this, you will need to lunch an Ec2 instance and from there use BCP to connect to RDS. You can’t do it directly from RDS because you don’t have access to the operating system in RDS.
    • Use DMS (Database Migration Service) with CDC. From On-premise to RDS CDC is allowed, but from RDS to RDS DMS with CDC in SQL Server is not allowed mostly because it needs to use some stored procedures that are enabled with SA permissions and you don’t have SA rights in RDS
  4. “Do I have SA rights in RDS SQL Server?”

    No. The highest level of permissions is the ‘Master User’ who created the instance. Despite its rather grand name, this user is owner of the databases and has a few other privileges. This approach drastically restricts the supervision and maintenance you can do it in SQL Server and obviously limits the usefulness of RDS SQL Server for more complex uses.

    The system privileges of the master user are ALTER ANY CONNECTION, ALTER ANY LINKED SERVER, ALTER ANY LOGIN, ALTER SERVER STATE, ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW SERVER STATE, ALTER ANY SERVER ROLE, ALTER ANY USER

    The roles that are assigned to the ‘Master User’ are: DB_OWNER (Database Level Role), PROCESSADMIN (Server Level Role), SETUPADMIN (Server Level Role), and SQLAgentUserRole (Server Level Role)

  5. “If lose the password of the master user what would I need to do to get access back?”

    Just go to the AWS Console and reset the master user account and password.

  6. “I got an error saying I was missing the rights to create a new database, but I am using Master Account – how can I fix it?”

    This is interesting. Even through you are in a managed environment, you sometimes need to just crash the rights and privileges from the master user account. Don’t ask me how it happens, but it does: I got a lot of cases like this one. To fix the problem, you can’t reset the Master account as the solution that
    I suggested in the previous question won’t work for this. You will need to open a case and the engineer will escalate to fix the issue

  7. “Can I set up alerts and email notifications in RDS SQL Server?”

    No. You cannot use alerts or db mail. AWS forces you to use their services for that, such as CloudWatch alerts and SES emails.

  8. “My Database has a lot of scheduled jobs, are these OK on RDS?”

    Yes and no. It depends of the jobs and what they do. You will find that you will not have access or rights to run some types of scheduled jobs. A stored procedure that needs SA rights, or a PowerShell script from a job will not work. Carefully check your jobs and what they do before you choose RDS SQL Server

  9. “How are ETL processes such as SSIS affected? Can I still use BCP to transfer data?”

    BCP can be used but, as I said before in reply to a previous question, it has to be run from a EC2 Instance. Forget about SSIS. Sorry, but it isn’t supported. Yes, I know; SQL Server RDS is a simple managed SQL Server database for simple applications: it doesn’t pretend to be anything else.

  10. “I need to be able to be able to say, for compliance, in which legislative area the data is held. Can I do this with RDS?”

    You will know in which availability zone your SQL Server is stored. When you create the instance, you choose the availability zone you prefer. You will never know more precisely than this, but it should be sufficient for most legislative frameworks.

    See http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html

  11. “Are there any limitations of the way you can resize your database?”

    Yes, and it’s a pain. If you need to increase storage you will need to launch another instance and move your data, using backup and restore, to the new instance with the new storage. You cannot change the storage capacity nor the type of storage for a SQL Server DB instance. This is evidently due to limitations of striped storage attached to a Windows Server environment.

    ‘So can I take a snapshot and restore in the new instance?”
    No. Amazon RDS does not support changing the storage configuration for a Microsoft SQL Server DB instance when restoring from a DB snapshot.

  12. “Can I take snapshots of my RDS databases?”

    No: You can’t.

  13. “Why should I use RDS rather than SQL Azure?”

    This question really needs its own article because both have their limitations, but here I’ll be brief. If you are concerned about the limitations of RDS, then that is a sign that it probably isn’t the best solution for you. In these circumstances, my answer would be togo for SQL Azure.

  14. “How do I run SQL Server instances with SSAS or SSRS in RDS?”

    You can’t. They don’t run. It’s as simple as that.

  15. “Does RDS replace the host automatically?”

    Yes. This service works like a charm for most of the time. Each action that is performed by the host service is following by a very well-designed workflow that will replace the entire instance (Hardware, SO and SQL)

    Although it is comforting to know that it works, it is what I would expect from any move to the cloud from an on-premise database hosting.

  16. “How do the automatic backups work?”

    The retention period can be up to 35 days. The Recovery-Point Objective (RPO) is 5 minutes and you cannot change the recovery model. If you attempt to change it, AWS will revert it to FULL and a workflow of backups is automatically fired.

    You cannot overwrite a database in an instance with the restore. Instead, you need to restore the database to another instance and then move the data to the original instance. You can imagine how hard is to restore just one database.

  17. “How does RDS SQL Server provide High Availability?”

    RDS uses SQL Server Database mirroring. AWS will automatically set up a secondary in another Availability zone (AZ) and will synchronize logins and permissions, but the task of transferring the SQL Agent jobs are the responsibility of the user and have to be performed manually. You can check my article here to automate the process:

    https://www.red-gate.com/simple-talk/sql/database-administration/automating-synchronization-rds-sql-server-agent-jobs-multi-az-environment/

  18. “Can I use native backup and restore from SQL Server?”

    Yes, you can do a native backup and restore, but with some restrictions. You cannot replace an existing database. You need to drop it first if you want to replace a database from your own backup. Also there are a few steps to do it, because the backup file will be done in a S3 bucket. For more information see: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.BackupRestore.html

  19. “Can I use SQL profiler in RDS SQL Server?”

    Yes, you can.

  20. “Can I use extended events?”

    Sorry, no, you must forget about using Extended Events, as it is not yet supported.

  21. “Do I have access to the OS to run PowerShell scripts?”

    No, sadly you don’t have access to either the OS or PowerShell scripts. However, if you need to use some PowerShell scripts you will need to launch a EC2 Instance that will connect to the RDS SQL Server, and then run the PowerShell scripts in your EC2 Instance.

  22. “Can I use Encryption?”

    Yes, Both SSL connection encryption and Transparent Data Encryption (TDE) are supported on RDS but you will need Enterprise licensing for TDE.

  23. “I need to use Always Encrypted, is this possible?”

    Yes you can, Always encrypted (Supported for all editions on 13.0.4422.0 SP1 CU2 and later.)

  24. “Is Instant file initialization allowed?”

    No, it’s not allowed. It means that auto growth events will be a pain unless you disable auto-growth.

  25. “Can I backup more than one database at same time?”

    No you can’t. AWS uses a special stored procedure called rds_backup_database that creates a task and until this task is finished, you cannot re-run it.

  26. “ As it is a managed server, do I have a service to re-index, rebuild, update statistics of my indexes; in other word a maintenance plan as part of the service?”

    No, you don’t have this. You will need to implement one yourself. The only managed service out of the box is the one that automatically runs backups, because I guess that RDS subscribers don’t like to take backups.

  27. “Do I no longer need a DBA to use RDS SQL Server?”

    Sorry, but if you assume that, then you’ve been hypnotized by the marketing person. You will need at least a Junior DBA or someone that has some good, or at least an Intermediate-level knowledge of SQL Server, mostly because of the restrictions and the missing maintenance services. You would need at least some routine maintenance of your indexes, and a good way of monitoring the databases.

  28. “So having read the pros and cons of Managed Service, why I would use RDS SQL Server instead of EC2 with SQL Server?”

    It depends. As I’m an experienced DBA, I am still asking that and it’s a <black hole> in my mind – <paraphrasing Route 53>Your mileage might vary, though. RDS takes away a lot of the hassle, and that could be worth a lot to you and the organization you work for. So much depends on your requirements and the in-house expertise that you have available. Vehicles come in all shapes and sizes as well, and that smart pickup may be last thing you really need. SQL Server RDS is the cute urban runabout of managed SQL Server.

    If you still are considering using RDS SQL Server, you can find out more information here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html