Best Practices to run SQL Server on Amazon RDS

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although running SQL Server on Amazon reduces cost to great affect, but has its own limitations too and need to review them carefully before moving data to Amazon RDS.

– Always Better to choose Multi-Availability zone deployment to have high availability, as we never know when the instance may go down, so to avoid or reduce the amount of down time, having high availability using Multi-Availability zone deployment will be helpful.
– Plan thoroughly in advance about the CPU, Memory and Storage requirements, changing these later may be complex. Storage cannot be changed once instance is created.
– Choose the proper server level settings,as they can be modified using Parameter Group and can be some what difficult to change and may be required instance to be restarted for few settings changes to apply.
– Patch levels are often controlled by Amazon RDS and may have some known issues due to older version of patches available and can impact your application due to known issues.
– Always choose the database files with proper initial size and auto-growth at higher sizes to avoid growing of files during application usage, this reducing the time and improving performance.
– If you are using Multi-Availability zone deployment, note the amount of time taken for the synchronization, as this is synchronous and thus the time for sync can have significant impact on you data access from application.

There may be other best practices, and following them will help running your databases on Amazon RDS seemless and provide cost effective solution replacing on-premise SQL Server databases. Not considering these limitations can significantly cause timeouts or performance issues and can make you feel frustrated for moving to Amazon RDS. Proper planning and understanding if your application works well on Amazon RDS will decide how the application will behave in future. Also, as time the amount of data can increase and can have significant impact on performance, so it is important to consider server configuration for future data growth and user usage.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

DBA Tasks Offloaded After Moving to AmazonRDS or SQLAzure

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

For On-Premise SQL Server instances, DBAs and System Admins are responsible for many tasks which may be day to day or some scheduled tasks like patching, maintenance etc. Amazon RDS or SQL Azure can offload many such tasks which SQL Server DBA or System Admin has to take care and thus can reduce the load on the DBAs on large environments with many servers and applications. Instead of spending time on repeated things, they can focus more on other important DBA activities like performance turning, health checks, etc.

Some of the important activities or tasks offloaded from DBAs or System Admins are listed below. These are not the only ones, but there are others too.
Patching – Patching of SQL Server instances or Operating System, drivers, etc have been one of the core responsibilities of the DBAs or System Administrators and often are repeatable and does not require smart work instead requires some hard work on working during off hours to reduce the impact on the users using the application. With Amazon RDS, these patching is taken care by Amazon team and we only have to specify the window on when the patching needs to take place. However this limits the control on what patches can be applied, as every patch we want will not be applied, as they need to certify which patch needs to be applied and tested to work smoothly.

Upgrade to Higher Version – Upgrading on-premise SQL Server is considered one of the major project and requires lot of planning and time to implement and test. With Amazon RDS, the implementation of upgrade and any issues are taken care by Amazon and we can just focus on testing our application and fixing the appliation compatibility issues.

Backups – Backups are taken care by Amazon and we just need to specify the time when backups can be performed and how many days backups are required to be retained. Point to note is that the number of backups to be retained is directly proportional to the increase in storage price to keep the backups. By default recovery model used in SIMPLE and cannot be changed to simple, instead we need to choose retention period as 0, meaning we do not need any backups. Thus, for databases which need backups will have full and transaction log backups and mostly we can recover our data to as early as 5 minutes before the crash.

High Availability – Amazon provides support for High Availability and it is important to choose the high availability feature, as this is third party vendor and on data centre which we have no control or details about, so we never know if there will be any issues or outages. For high availability, RDS can automatically setup a database mirroring secondary on another Availability Zone often in another Amazon data centre in the same region.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

AmazonRDS SQL Server DB Instance Settings Which Can be Modified

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

AmazonRDS SQL Server DB Instance has various settings which can be modified to suit our requirements. Below are some of the important setting which can be modified after the AmazonRDS SQL Server DB Instance has been created. It is important to understand that modifying these settings can have adverse effect too, so just be careful and fully understand the change which you are making. You can make all these changes listed below from AmazonRDS web console.

DB Engine Version – Used to choose the version of the SQL Server database engine that we want to use.
Multi-AZ Deployment – Allows us to create a standby mirror of our DB instance in another Availability Zone, click Yes, otherwise, click No.
DB Instance Identifier – We can rename our DB instance by providing a new name. When we change the DB instance identifier, instance reboot will occur immediately if we set Apply Immediately to true, or will occur during the next maintenance window if we set Apply Immediately to false.
New Master Password – We can change the master password. By resetting the master password, we also reset permissions for the DB instance.
Security Group – We can choose the security group we want to associate with the Database Instance.

Certificate Authority – We can choose a certificate we want to use.
Publicly Accessible – Selecting Yes allows the DB instance a public IP address, meaning that it will be accessible outside the VPC (the DB instance also needs to be in a public subnet in the VPC), Id we choose No, the the DB instance will only be accessible from inside the VPC.
Parameter Group – Allows us to choose the parameter group we want to associate with the database instance. Changing this setting does not result in an outage. The parameter group name itself is changed immediately, but the actual parameter changes are not applied until you reboot the instance without failover.
Option Group – We can choose the option group which we want to associate with out database instance.
Database Port – We can specify a particular port to use for our Database instance.
Backup Retention Period – We can adjust or modify the database backup retention period. To disable automatic backups, we can set this value to 0(useful for test environments to reduce cost).
Backup Window – We can choose the backup windows, on when the backup of the database needs to be performed. This time is in Universal Coordinated Time (UTC) and a duration in hours.
Auto Minor Version Upgrade – If you want your DB instance to receive minor engine version upgrades automatically when they become available, click Yes. Upgrades are installed only during your scheduled maintenance window.
Maintenance Window – We can set the time range during which system maintenance, including patchs, upgrades, will occur. This is start time in UTC and a duration in hours.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

How to Connect to AmazonRDS from SQL Server Management Studio?

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although application connect to the database and works fine, it is important for DBAs or Developers to be able to query for data and to manage Databases and other objects places on AmazonRDS. There may be different ways to connect to Amazon RDS and manage or query data, but one of the easiest method and most popular and convenient way is to do it through well known tool which is SQL Server Management Studio(SSMS). We only need few basic details and then we should be good to connect to our database on AmazonRDS.

Firstly we need to create a security group from AmazonRDS web console, and then we must modify the DB instance to associate it with the security group. Once we are done with it, we can follow below steps to be able to connect to our database from SSMS

– On the Instances page of the AWS Management Console, we need to choose the arrow next to the DB instance to show the instance details. Make a note of the server name and port of the DB instance, which are displayed in the Endpoint field at the top of the panel, and the master user name, which is displayed in the Username field in the Configuration Details section.

– Launch Microsoft SQL Server Management Studio(SSMS). Then Connect to Server dialog box appears in the Server section “select Database Engine”. Now provide the server name of the DB instance then a comma(,) and port number. Example: EndPoint,Port

– Now choose SQL Server as authentication type as this is the only authentication method supported in AmazonDRS. Provide the user name and password and then click connect and you should be connected successfully, unless there are any port or firewall issues. Open a new query window and run few sample queries to make sure you can run queries.

Some of the known issues which you may face while trying to connect to AmazonRDS from SQL Server Management Studio are related to to your local firewall and the IP addresses you authorized to access your DB instance in the instance’s security group are not in sync. If you cannot send out or receive communications over the port you specified when you created the DB instance, you will not be able to connect to the DB instance. Check with your network administrator to determine if the port you specified for your DB instance is allowed to be used for inbound and outbound communication.or newly created DB instances, you must wait for the DB instance status to be “Available” before you can connect to the instance. Depending on the size of your DB instance, it can take up to 20 minutes before the instance is available.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings