Amazon RDS – Oracle DB Data Import

Amazon RDS – Oracle DB Data Import ”; Previous Next To import data into the RDS oracle database or export data from it, we need to consider the size of the data involved and use a appropriate technique. The Sql Developer tool which we use to connect to AWS RDS oracle instance, can be used for both export and import small volume of data like 20MB or so. But to import data of size in terabytes, we need to use oracle data pump. Exporting Data Using SQL Developer Step-1 After connecting to the AWS RDS Oracle DB, choose tools and Database Export. Step-2 The next step asks for the type of export where we decide the export format. Step-3 Next we decide on the DB objects to be exported. Step-4 We can further decide on the name of the objects to be exported. Step-5 We can further decide on the objects attributes to be exported. Step-6 Finally we get the summary screen where we can revisit the objects we have choosen. Clicking finish on the above step will export the DB into a file in choosen format. Importing Data Using SQL Developer Similar to the export steps above we can choose to import the db by using Database Copy command from the Tools menu option. Print Page Previous Next Advertisements ”;

Amazon RDS – Multi-AZ Deployments

Amazon RDS – Multi-AZ Deployments ”; Previous Next In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide data redundancy, eliminate I/O freezes, and minimize latency spikes during system backups. Running a DB instance with high availability can enhance availability during planned system maintenance and help protect your databases against DB instance failure and Availability Zone disruption. Creating Multi-AZ deployment You can choose the option of selecting a multi-AZ deployment when creating a DB instance or you can also choose the option of modifying an existing db instance to become a multi-AZ DB instance. The following diagram shows the option where we are modifying an existing option. But the same option is also available during the creation of the DB instance. How Multi-AZ works In the event of a planned or unplanned outage of your DB instance, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have enabled Multi-AZ. The time it takes for the failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable. Failover times are typically 60-120 seconds. However, large transactions or a lengthy recovery process can increase failover time. Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon”s failover technology. SQL Server DB instances use SQL Server Mirroring. Print Page Previous Next Advertisements ”;

Amazon RDS – MS SQL DBA Tasks

Amazon RDS – MS SQL DBA Tasks ”; Previous Next As a RDS service the MSSQL DB has many DBA tasks available as managed service. You do not have the shell access to the DB, but through the console or through the commands in the client software you can execute various DBA activities. Below are the most common and frequently used DBA tasks performed in Amazon RDS Ms SQL server. Change Data Capture CDC captures changes that are made to the data in the tables. The changes that were made to user tables are captured in corresponding change tables. These change tables provide an historical view of the changes over time. The change data capture functions that SQL Server provides enable the change data to be consumed easily and systematically. Use the below commands in SSMS connected to RDS MSSQL server to enable and disable CDC. #Enable CDC for RDS DB Instance exec msdb.dbo.rds_cdc_enable_db ”” #Disable CDC for RDS DB Instance exec msdb.dbo.rds_cdc_disable_db ”” Next to track the changes in a specific table we use the stored procedure sp_cdc_enable_table with the below command. #Begin tracking a table exec sys.sp_cdc_enable_table @source_schema = N”” , @source_name = N”” , @role_name = N”” , @captured_column_list = ”” ; Modifying tempdb Database Options The tempdb system database is a global resource which is available to all users connected to the instance of SQL Server and is used to hold the following Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors. Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions. Following are examples of How you modify the RDS MSSQL tempdb for various DBA activities. # setting the size to 100 GB and file growth to 10 percent. alter database[tempdb] modify file (NAME = N”templog”, SIZE=100GB, FILEGROWTH = 10%) # set the MAXSIZE property to prevent tempdb database from using all available disk space. alter database [tempdb] modify file (NAME = N”templog”, MAXSIZE = 2048MB) # Shrinking the tempdb Database file size and requests a new size exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N”test_file”, @target_size = 10; OFFLINE to ONLINE Transition You can transition your Microsoft SQL Server database on an Amazon RDS DB instance from OFFLINE to ONLINE using the following command. EXEC rdsadmin.dbo.rds_set_database_online name Non-English Character Set During the creation of RDS MSSQL instance, the default collation marked for the DB is English. But it can be changed to another non-English language by applying the COLLATE clause along with the name of the collation. The below example illustrates that. CREATE TABLE [dbo].[Account] ( [AccountID] [nvarchar](10) NOT NULL, [AccountName] [nvarchar](100) COLLATE Japanese_CI_AS NOT NULL ) ON [PRIMARY]; Print Page Previous Next Advertisements ”;

Amazon RDS – PostgreSQL Connecting to DB

Amazon RDS – PostgreSQL Connecting to DB ”; Previous Next To connect to Amazon RDS PostgreSQL DB we need a client software. In this case we use pgAdmin. Install it using the link pgAdmin . After it is successfully installed we follow the steps below to connect it to the Amazon RDS. Step-1 From the DB instance details get the end point. Step-2 As it is a browser based interface, choose the add server option as shown in the below diagram. Step-3 Use the end point and the master user credentials as the connection details. Step-4 Once connected, we get the following window. Print Page Previous Next Advertisements ”;

Amazon RDS – Oracle DBA Tasks

Amazon RDS – Oracle DBA Tasks ”; Previous Next As an industry leading database technology, oracle has many in-built features which makes it easy to manage the DBA activities, even in the cloud. The Amazon RDS oracle DB provides access to many stored procedures and functions which can be accessed using the SQL developer client tool. This procedure can be executed using the user ID and password created during the Amazon RDS instance creation. Below are the examples of some of the most frequently used DBA activities. Killing a Session Sometimes a long running query or any other DB activity needs to be killed by killing the session. We use the Amazon RDS procedure rdsadmin.rdsadmin_util.kill to kill a session. The following code does that. # First get the session identifier and the session serial number, select SID, SERIAL#, STATUS from V$SESSION where USERNAME = ”AWSUSER”; # Next use the procedure begin rdsadmin.rdsadmin_util.kill( sid => sid, serial => serial_number); end; / Setting the Default Tablespace The Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_tablespace can be used to set to the default tablespace for a DB using the following command. exec rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => ”AWSuser”); Setting the Database Time Zone We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_db_time_zone to changes the time zone for the DB. # Change the time zone of the DB to UTC + 5.30 exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ”+5:30”); # Change the time zone to a specific region exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => ”Asia/Kolkata”); Adding Online Redo Logs We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile to add additional redo logs. The following command adds a log file of size 128MB. exec rdsadmin.rdsadmin_util.add_logfile(p_size => ”128M”); Print Page Previous Next Advertisements ”;

Amazon RDS – MySQL DBA Tasks

Amazon RDS – MySQL DBA Tasks ”; Previous Next As with every other database, Amazon RDS MYSQL also needs DBA tasks to fine tune the database and do periodic health checks etc. But as the AWS platform does not allow the shell access to the DB, there are a limited number of DBA tasks that can be performed as compared to the on-premise installation of MySQL. Below is a list of common DBA tasks that can be performed in AWS RDS MySQL database and their descriptions. Accessing Error Logs The MySQL error log ( mysql-error.log) file can be viewed by using the Amazon RDS console or by retrieving the log using the Amazon RDS CLI. mysql-error.log is flushed every 5 minutes, and its contents are appended to mysql-error-running.log. The mysql-error-running.log file is then rotated every hour and the hourly files generated during the last 24 hours are retained. Using RDS Console Below there are links to two log files described above. Using CLI Using CLI the log files are published to CloudWatch Logs as a JSON Object. aws rds modify-db-instance –db-instance-identifier mydbinstance –cloudwatch-logs-export-configuration ”{“EnableLogTypes”:[“audit”,”error”,”general”,”slowquery”]}” –apply-immediately Killing a Long Running Session or Query Sometimes the DBA needs to kill a long running session or query which is not giving the result quick enough. This DBA task is done by first finding the process ID of the query and then using a RDS function to kill the query. The below commands are the examples. # get the ID Select * from INFORMATION_SCHEMA.PROCESSLIST #Apply the Kill Function CALL mysql.rds_kill(processID); Improve Crash recovery Time We can improve the recovery time from a crash by setting a DB parameter called innodb_file_per_table. We can find this parameter in the RDS console as shown below. Next we can Search for the parameter name as shown below. Amazon RDS sets the default value for innodb_file_per_table parameter to 1, which allows you to drop individual InnoDB tables and reclaim storage used by those tables for the DB instance. This speeds up the recovery time from the crash. Stop and Reboot DB Stopping a DB, Rebooting it or creating snapshots etc can be done easily through RDS console as shown in the below diagram. Print Page Previous Next Advertisements ”;

Amazon RDS – MySQL Features

Amazon RDS – MySQL Features ”; Previous Next MySQL is a popular Relational DB which is available in the amazon RDS services with its community edition features. Almost every feature of MYSQL can be leveraged in the RDS platform with only some restrictions based on regions and availability zones. Below is a brief description on MYSQLs major features in the RDS platform.Supported Versions The versions 5.5, 5.6 and 5.7 are the major versions supported in the RDS platform. Except for 5.6.27 all versions are supported in all AWS regions. If no version is mentioned during the DB creation, it defaults to the most recent version at that point in time. Below is an example of how to get all supported DB Engine versions using AWS API in a python SDK program. import boto3 client = boto3.client(”rds”) response = client.describe_db_engine_versions( DBParameterGroupFamily=”mysql5.6”, DefaultOnly=True, Engine=”mysql”, EngineVersion=”5.6”, ListSupportedCharacterSets=True, ) print(response) When the above code is run we get an output as below − { “ResponseMetadata”: {}, “DBEngineVersions””: [ { “Engine””: “mysql”, “DBParameterGroupFamily””: “mysql5.6”, “SupportsLogExportsToCloudwatchLogs””: true, “SupportedCharacterSets””: [], “SupportsReadReplica””: true, “DBEngineDescription””: “MySQL Community Edition”, “EngineVersion””: “5.6.40”, “DBEngineVersionDescription””: “MySQL 5.6.40”, “ExportableLogTypes””: [ “audit”, “error”, “general”, “slowquery” ], “ValidUpgradeTarget””: [ { “Engine””: “mysql”, “IsMajorVersionUpgrade””: true, “AutoUpgrade””: false, “Description””: “MySQL 5.7.22”, “EngineVersion””: “5.7.22” } ] } ] } Version Upgrade There MySQL version number is maintained as MYSQL A.B.C. In this notation, A.B indicates the major version and C indicates the minor version. The upgrade approach is different between minor and major version upgrades. Minor Version Upgrade The DB instances are automatically upgraded to new minor versions when ever they are supported by Amazon RDS. This patching occurs during a schedules maintenance window which you can control. You can also manually upgrade to new versions if you prefer to turn off the automatic update. Major Version upgrade Major version upgrades are not available as automatic upgrade. It must be done by the account user manually by modifying the DB instance. Below flowchart indicated the steps in achieving the major version upgrade. This approach ensures that the upgrade process is thoroughly tested before it is applied on the live production database. Database Security The security for RDS MYSQL DB is managed at three layers. Using IAM In this approach the IAM user should have appropriate policies and permissions. Granting of such permissions is decided by the account holder or the super user who grants these permissions. Using VPC You either use a VPC security group or DB security group to decide which EC2 instances can open connections to the endpoint and port of a DB instance. These connections can also be made using SSL. Using IAM Database Authentication In this approach you use a IAM role and an authentication token. The authentication token generates a unique value which is relevant to the IAM role that is used in the access process. Here the same set of credentials are used for database as well as other aws resources, like EC2 and S3 etc. Print Page Previous Next Advertisements ”;

Amazon RDS – MS SQL DB with SSL

Amazon RDS – MS SQL DB with SSL ”; Previous Next To protect data from being viewed by unintended parties, we can use connection encryption between the client application and the RDS DB instance. Encryption is available in all AWS regions and for all the DB types supported by AWS RDS. In this chapter we will see how encryption is enabled for MSSQL Server. There are two ways to enable encryption. Force SSL for all connections — this happens transparently to the client, and the client doesn”t have to do any work to use SSL. Encrypt specific connections — this sets up an SSL connection from a specific client computer, and you must do work on the client to encrypt connections. Force SSL In this approach we force all the connections form the DB client to use SSL. This is done by using the rds.force_ssl parameter. Set the rds.force_ssl parameter to true to force connections to use SSL. As it is a static parameter, we must reboot your DB instance for the change to take effect. The below diagram shows how to reset the value by visiting the DB parameters settings page to set the value for rds.force_ssl parameter. Encrypting Specific Connections We can encrypt connections from specific client computers only to the RDS DB Instance. In order to do this, we need to install certificate on the client computer. Below are the steps to install the certificate. Step-1 Download the certificate to the client computer from here . Step-2 Follow the path Windows -> Run -> type MMC and enter. It opens the following window. Step-3 In the Add or Remove Snap-ins dialog box, for Available snap-ins, select Certificates, and then choose Add. Step-4 Follow the Path Computer Account -> Local Computer -> Finish. Step-5 In the MMC console, expand Certificates, open the context (right-click) menu for Trusted Root Certification Authorities, choose All Tasks, and then choose Import. Step-6 Select the .pem file downloaded in the previous step and finish the import wizard by choosing the default values and clicking next. Step-7 We can see the certificate installed as below. Step-8 When connecting to AWS RDS MSSQL Db instance using SSMS, expand the options tab and choose Encrypt connection. Now the client connection to RDS from this computer will be encrypted. Print Page Previous Next Advertisements ”;

Amazon RDS – DB Snapshots

Amazon RDS – DB Snapshots ”; Previous Next Amazon RDS creates automated backups of your DB instance during the backup window of your DB instance and stores them as volume snapshots. Automated Backup For Automated backup to work properly, followings are the criteria. Your DB instance must be in the ACTIVE state for automated backups to occur. Automated backups don”t occur while your DB instance is in a state other than ACTIVE, for example STORAGE_FULL. Automated backups and automated snapshots don”t occur while a copy is executing in the same region for the same DB instance. The first snapshot of a DB instance contains the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental, which means that only the data that has changed after your most recent snapshot is saved. The following diagram shows how we can configure the automated backup window for a DB instance using the AWS console. To disable the automated backup, set the number of days to zero. Manual Backup We can also take backups manually by using snapshots. To take a snapshot manually we use the instance action option after selecting the instance as shown below. We can aslo take manual backup using the CLI with the below command. aws rds create-db-snapshot / –db-instance-identifier sourcedbinstance / –db-snapshot-identifier dbsnapshotinstance Print Page Previous Next Advertisements ”;

Amazon RDS – PostgreSQL creating DB

Amazon RDS – PostgresSQL creating DB ”; Previous Next As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a PostgreSQL can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one. Using AWS management Console AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a PostgreSQL instance. Step-1 Select the PostgreSQL Engine form the console. Step-2 Specify the required DB details. Step-3 In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details. Stpe—4 This is the final step when you mention the vpc and security settings, encryption, backup options and log export etc. For brevity the screen shot has been shortened showing only the final options. Stpe—5 In the final step we choose the create Data base option. Using CLI To create a PostgreSQL DB instance by using the AWS CLI, call the create-db-instance command with the parameters below. aws rds create-db-instance –db-instance-identifier pgdbinstance –allocated-storage 20 –db-instance-class db.t2.small –engine postgres –master-username masterawsuser –master-user-password masteruserpassword Using API To create a PostgreSQL instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below. https://rds.amazonaws.com/ ?Action=CreateDBInstance &AllocatedStorage=20 &BackupRetentionPeriod=3 &DBInstanceClass=db.t2.small &DBInstanceIdentifier=pgdbinstance &DBName=mydatabase &DBSecurityGroups.member.1=mysecuritygroup &DBSubnetGroup=mydbsubnetgroup &Engine=postgres &MasterUserPassword= &MasterUsername= &SignatureMethod=HmacSHA256 &SignatureVersion=4 &Version=2013-09-09 &X-Amz-Algorithm=AWS4-HMAC-SHA256 &X-Amz-Credential=AKIADQKE4SARGYLE/20140212/us-west-2/rds/aws4_request &X-Amz-Date=20140212T190137Z &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date &X-Amz-Signature=60d520ca0576c191b9eac8dbfe5617ebb6a6a9f3994d96437a102c0c2c80f88d Print Page Previous Next Advertisements ”;