Geolocation information for IP addresses

MaxMind (https://www.maxmind.com/en/geolocation_landing) can be used to provide  you the ability to identify the location, organization, connection speed, and user type of your Internet visitors. 

Redshift SQL query tools

      Here are two tools that can be used with Amazon Redshift to issue query commands:      

1    1. SQL Workbench :

http://www.sql-workbench.net/downloads.html

The information required regarding your Redshift cluster are:

A. JDBC or ODBC connection string: example JBDC connection string looks like this: jdbc:postgresql://redshiftexamplecluster.cvhv40lk8tel.us-east-1.redshift.amazonaws.com:5439/redexampletl?tcpKeepAlive=true

B. User ID : master

C. password : password

More details here: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-using-workbench.html

2. Aginity : http://www.aginity.com                       A. Server (endpoint): example looks like this: lab.cfmvmxhkrtel.us-west-        2.redshift.amazonaws.com

B. User ID : master

C. password : password

D. Database: databasename

E. Port: 5439

The nice thing about Aginity is that there is no JDBC driver to install. With SQL Workbench, you will need to download and install 

More on where to find the  JDBC driver and configure with SQL Workbench

1. Location of driver: http://jdbc.postgresql.org/download/postgresql-8.4-703.jdbc4.jar

2.     Add the downloaded driver JAR to the driver to the PostGres Driver in SQL Workbench

Storage tiering on AWS

Here is the replay of the session I just presented with an AWS partner (App Associates) and customer (Riso):http://cloudconclave.blogspot.com/2013/11/aws-storage-tiering-for-enterprise.html

I opened the session by 'testing' the audiences understanding of AWS storage tiers:
1. Assuming a 16K block size, what storage option provides average throughput of 1 to 2 MBPS ?
2. What storage option has single threaded through put of around 17 MBPS ?
3. Assuming a 16K block size, what EBS volume provides an average of 16-20 MBPS through put ?
4. Once again assuming 16K block size and also assuming 4 1K PIOPS volumes, for what EC2 instances will you start to see network saturation ?
5. What storage option produces approximately 100-145 MBPS read and write through put?
6. For which storage option is it possible to transfer approximately 3 TB a day a day over a WAN ?




















Answers:
1. Standard IOPS
2. S3
3. 1K PIOPS
4. Any instance with .5 Gpbs network connection. For example, m2.2xlarge
5. Hi1.4xlarge (high IO) ephemeral storage
6. AWS Storage gateway

AWS VPN connection as a Direct Connect fail over connection

Another question that comes up often:

• Can a VPN connection be used as a fall back for lost Direct Connect connection?
• Yes, more details here: http://aws.amazon.com/directconnect/faqs/#T73.

VPN costs for connections and data

VPC has no cost associated. However, if you want to extend your data center or provide a secure IPSec tunnel through a VPN client, you need to add in costs for the VPN connection and data transfer costs over the VPN tunnel.   This is the first place to look at to decipher VPN connection cost: http://aws.amazon.com/vpc/pricing/.  The cost is $0.05 per VPN Connection-hour + standard AWS data transfer charges for all data transferred via the VPN Connection. For example, if you have 2 VPN connections the cost for those connections would be:  .05x24x365.25x2(2 connections) / 12 = ~73.05 + data transfer.

Now for the data transfer out piece:  On data transfer out for VPN connection, the cost is .12 per GB for less then 10 TB a month per connection..  So, two connection each at 1 TB a month would be $245.76 a month.  

Oracle RDS timezone

The default time zone for your RDS instance is UTC and cannot be changed on the DB. You can set the desired timezone (EST) per database connection. The time zone will only be valid for the connection therefore it needs to be done for each connection (and each time you connect).

The following page outlines  how to do this http://aws-musings.com/amazon-relational-database-service-rds-the-timezone-problem

Mindterm SSH on Mac

I moved from a Windows laptop to a Mac and just experienced this issue:

https://forums.aws.amazon.com/thread.jspa?threadID=117962

I did as suggested and used the following for my private key path:

../Downloads/EssentialsLab.pem

AWS import export : When to use import export

This web page has some guidelines on when to use AWS import export and when to use a network connection:
http://aws.amazon.com/importexport/

Go down to this section: "When to Use Import/Export"

I often get asked this question when someone wants to move large Oracle database to the cloud.

Oracle client connection through VPN

If you are attempting to connect to your Oracle RDS or EC2-based Oracle DB through SQL Developer and SQL Plus from a client machine, you will get a ORA-12541: TNS:no listener if connecting through VPN ( or maybe a corporate firewall ). If you can connect to your instance through SQL Plus on an EC2 instance, it could be because of VPN or a firewall.

AWS Glacier UI

Amazon AWS Glacier was just introduced but there are already a number of free Windows UI tools to use.  I just used this one to backup my laptop files to Glacier. http://fastglacier.com/

Oracle RDS on AWS - Connecting with DB tools

After taking some time to make sure security groups for my Oracle RDS instances were set properly, I was able to connect to two Oracle RDS instances I have spun up.  A few comments on the various tools:
1. SQL Plus - You can use the instant client latest 11g release.  You do need to make sure the connect string is set properly.  How to do this can be found here:
http://docs.amazonwebservices.com/AmazonRDS/2012-01-15/UserGuide/USER_ConnectToOracleInstance.html
2. SQL Developer - I used the 64 bit version that does not have the JDK installed so you will need to download the JDK separately,
3. TOAD - You need to point TOAD to the Oracle client software. This is done by adding Oracle client to the system path.  I have another blog entry on this.

Connecting to an EC2 based Oracle instance works the same way.

AWS EC2 and RDS Oracle access using Toad

Toad is a great tool for accessing the Oracle database for development.  However, there is one 'trick' regarding the configuration of Toad and the Oracle client.  The Oracle client software (in my case the Oracle 11g instant client) needs to be in the Windows path.  There are great instructions on how to do this here:
https://support.quest.com/SolutionDetail.aspx?id=SOL7712

If the class path is not set, you will get an error when you open up Toad that the Oracle client is not found.