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

 

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

 
1 2 3