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

 

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

 

Tools to Move Database O-Premise to Amazon RDS or SQL Azure

SQLServerF1

With growing popularity of cloud technologies like Amazon RDS and SQLAzure, there has been lot of interest about understanding these technologies. These cloud solutions are cost effective but are limited on many aspects, one of which is moving data or databases between on-premise SQL Server instance to Amazon RDS and SQLAzure.

Mostly DBAs use backup/restore method to move or migrate databases/data from one server to another server or between SQL instances, but when it comes to Amazon RDS and SQLAzure, Restore is not supported as they do not provide file system level access, so this method is ruled out. Then what other methods are available for us to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.

Below are some of the options which are available to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.

Generate Scripts – Script create database, and its objects like table schemas, views, Stored Procedures, triggers, users, database roles and permissions, etc. Note that SQL Server Logins need to be created before hand to be able to map the database users to logins. This method can also be used to transfer data, but that will create huge file and can be difficult in writing to and reading from the file and makes things complicated, so better to use this method to create empty database with schemas only.

Import/Export Wizard – SQL Server Management Studion has buit-in tool which is Import/Export wizard which can be used to transfer schema and data. This creates a SSIS package to transfer the data and mostly is easy to create and implement. However there can be some issues with Identity columns, etc, so need to use it carefully and choose proper options.

BCP – For users who are comfortable with command line can opt for BCP which can be used to export data to dat file and then import that data into Amazon RDS or SQLAzure.

Sample commands:

For moving data from on-premise to dat file
BCP.exe “[HR].[dbo].[Emp]” out “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S ServrName\SQLInstName -T

For moving data from dat file to Amazon RDS
BCP.exe “[HR].[dbo].[Emp]” in “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S AmazonRDSConn -U AmazonRDSAdminUser

SQL Database Migration Wizard – Users who are comfortable using GUI tool to take care of data transfer can use SQL Database Migration Wizard which is available in CODEPLEX, which is simple to use. This can be used for both AmazonRDS and SQLAzure.

If you are confused on which tool or which of these methods to choose from, then you may ask yourself questions like do we need to move the data once and will never going to move again and if you prefer GUI method in which case you can using GUI option using SQL Database Migration Wizard or Import/Export in SSMS. If you may require to transfer data periodically, then you may choose scripts/BCP command and use SQL Agent jobs to run it when ever required.

Hopefully this answers all your questions on how to move data between on-premise SQL Server database and AmazonRDS or SQLAzure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
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

 

Limitations of Amazon RDS SQL Server 2012

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. Some of the important restricted features are mentioned below.

– Only SQL Authentication Logins are allowed. Windows authentication is not supported, so applications are to be changed to only use SQL Server authentication.
– Amazon RDS only provides support for core database engine of SQL Server, but does not include SSRS, SSAS, SSIS, etc. If application requires these services, then they can be implemented on on premise server and can access data from Amazon RDS.
– Maintenance plans are not supported, so we would need to use SQL Scripts to implement the maintenance tasks like backups, Index and statistics maintenance, Integrity checks, etc. There are already many of these scripts available like Ola Hellengren scripts. Using scripts also provides flexibility of dealing with these maintenance tasks.

– Linked Server access is limited and only can be configure and used from Amazon RDS to outside SQL Server instances over internet or other Amazon RDS instances.
– SQL Agent role is limited, which causes issues where one user with SQL Agent role cannot see jobs created by another user with SQL Agent role. So workaround is to use only one account for job management.
– In Amazon RDS, during the initial creation of instance, we choose the storage and this storage cannot be changed later. There are no workarounds and will need to create new instance with required storage design and then have to migrate data from old instance to new instance.
– Replication can be setup, but is is limited. We can only configure Amazon RDS instance as a subscriber. We cannot use Amazon RDS instance as publisher/distributor. We cannot use pull subscription too.

Some of the other features which are not supported by Amazon RDS SQL Server 2012 or lower include below.

Database Mail
Service Broker
Database Log Shipping
Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
Additional T-SQL endpoints
Performance Data Collector
Distribution Transaction Coordinator (MSDTC)
WCF Data Services
FILESTREAM support
Policy-Based Management
SQL Server Audit
BULK INSERT and OPENROWSET(BULK…) features. These must be run from client-based server storage.
Data Quality Services
Instant file initialization
Always On (2012 Enterprise Edition)
File tables
Server level triggers

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
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