Amazon

Amazon – RDS – Parameters – Trace Flags

Daniel Adeniji 1204, 1222, 3226, 4199, 7806, 8017, Amazon, Amazon Web Services ( AWS ), Microsoft, MS SQL Server, Parameter Groups, Parameters, Relational Data Services ( RDS ), Trace Flags

Background

As intimated in our last post, one is able to effect MS SQL Server DBCC Trace Flags through RDS Parameter groups.

Currently, there are about 74 options that one can access through parameter groups.

In this post, we will focus on the dozen DBCC trace flags that are exposed.

 

Trace Flags

 

Trace Flag Description Default Recommendation Reason
 1204 Returns the resources and types of locks participating in a deadlock and also the current command affected  Off  On The information is very useful for troubleshooting deadlocks
 1211 Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks  Off  Off  It is better to allow the SQL Engine to control lock escalation
 1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.  Off  On Same as Trace Flag 1204, helpful for deadlock troubleshooting
 1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.  Off  Off Same as Trace Flag 1211, best to allow SQL Engine control
 2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE  Off  Off Again, allow the SQL Engine to throttle whether to enable parallel checking
 3205 Disable hardware compression for tape drivers  Off  Off MSFT doc states – “If a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression“.
 3226 Suppress log entries for backup operations  On  On Suppress successful backup logging.  This reduces errorlog cluttering especially useful for the more frequent log backup
 3625 Limits the amount of information returned in error messages  On  On In our own environment, the default security put in place ( for errorlog) by SQL Server is sufficient
 4199 Controls multiple query optimizer changes previously made under multiple trace flags   Off  On You want to check your SQL Server Version and Patch level and see if it supports this Trace Flag.

If it does, I will say test the Trace Flag out, and keep it enabled, if it does cause harm.

Please read more here.
 4616 Makes server-level metadata visible to application roles  Off  Off No need to expose this class of information
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration  Off  Off Memory dump is useful for diagnostic and so no need to disable memory dumps
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express  On  On Dedicated Administrator connection is useful for accessing stalled SQL instances

 

 

Code

Enable Trace Flag – 1204

aws rds Modify-db-parameter-group

Sample code for enabling a trace flag through AWS/RDS CLI.

Syntax


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name [parameter-group-name]  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

Sample


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

 

dbcc traceoff

Sample code for disabling a trace flag through Transact SQL.

Syntax

DBCC TRACEOFF ([trace-number], -1)
GO

Sample


DBCC TRACEOFF (1204, -1)
GO

Output – Image

DBCCTraceoff

Output – Text


Msg 2571, Level 14, State 3, Line 1
User 'guest' does not have permission to run DBCC TRACEOFF.

Explanation

  1. As we do not have sysadmin privileges, we are not able to issue DBCC TraceOn/DBCC TraceOff
    • This command “requires membership in the sysadmin fixed server role” ( Link )

 

 

Review Enabled Trace Flags

List all enabled trace flags

Code


DBCC TRACESTATUS(-1);
GO

Output

DBCCTraceStatus-Enabled

Explanation

  1. I am familiar with all the other Trace flags ( listed above ), outside of Trace Flags 4199 and 8017
    • 4199
      • Need to clear cached plan
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          https://support.microsoft.com/en-us/kb/974006
          If DBCC TRACEON\TRACEOFF is used this does not regenerate a new cached plan for stored procedures. Plans could be in cache that were created without the trace flag
    •  8017
      • Aaron Morelli
        Twitter – @sqlcrossjoin
        Topic – A Topical Collection of SQL Server Trace Flags
        Link – https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
        Ken Henderson 2005, page 387 (paraphrased): basically means “no offline schedulers”.
        Normally, when using affinity to restrict the CPUs that SQL can use, SQLOS starts up schedulers for every CPU on the box, but then keeps schedulers that it is not allowed to use in “offline” state. However, those schedulers are using resources, so you can prevent SQL from ever creating those schedulers by turning on this flag. You can combine this with 8002 to achieve the “move among CPUs” effect for your schedulers
        This flag appears to have been turned on by default in SQL 2005 Express Edition, as evidenced by all of the upgrade warnings people were experiencing when trying to upgrade to SQL 2008 Express.
      • Makes sense as SQL Server Express Edition does not support SQL Server Agent

 

References

Trace Flags – MSFT

  1. Trace Flags (Transact-SQL)
    https://technet.microsoft.com/en-us/library/ms188396.aspx


Trace Flags – Generic

  1. Warner Chaves – The Most Important Trace Flags for SQL Server
    http://sqlturbo.com/the-most-important-trace-flags-for-sql-server/
  2. Aaron Morelli – Trace Flag Respository
    https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
  3. Derik – sqlHammer – Derik’s Favorite Trace Flags
    http://www.sqlhammer.com/deriks-favorite-trace-flags/

 

Trace Flags – 4199

  1. SQL Server query optimizer hotfix trace flag 4199 servicing model
    https://support.microsoft.com/en-us/kb/974006
  2. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/
  3. Benjamin Pierce – SQL 2008 – 2012 Query Optimizer Trace Flag 4199 – Increase performance
    http://www.symantec.com/connect/blogs/sql-2008-2012-query-optimizer-trace-flag-4199-increase-performance
  4. Joe P – Developer Gems – SQL Server Trace Flag 4199
    http://developergems.blogspot.com/2012/06/you-know-sometimes-as-software.html
  5. David K. Lee – Convergence of Data and Infrastructure – SQL Server Trace Flag 4199
    http://www.davidklee.net/2012/08/23/sql-server-trace-flag-4199/
  6. Paul White – Optimization Phases and Missed Opportunities
    http://sqlperformance.com/2013/06/sql-indexes/recognizing-missed-optimizations
  7. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/

 

Trace Flags – 4616

  1. Gerard Conroy – SQL Server Trace Flag 4616 no longer required for Dynamics NAV 5.0 SP1 or Dynamics NAV 2009 SP1
    https://blogs.msdn.microsoft.com/nav/2010/02/11/sql-server-trace-flag-4616-no-longer-required-for-dynamics-nav-5-0-sp1-or-dynamics-nav-2009-sp1/

 

SQL Server Builds

  1. Microsoft SQL Server Version List
    http://sqlserverbuilds.blogspot.in/

 

Amazon – AWS – RDS – DB Parameter

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), CLI, describe-db-parameter-groups, describe-db-parameters, Relational Data Services ( RDS )

Background

Amazon RDS Offers an ability to access some of the more system level configurations of the hosted database.

In MS SQL Server, some of those options are available via sp_configure.

Here is how to view exposed configuration via AWS\RDS CLI Commands.

Parameter Groups

List DB Parameter Groups

Code


aws rds describe-db-parameter-groups

Output

ListDBParameterGroups

 

List Specific DB Parameter Group

Code


aws rds describe-db-parameter-groups --db-parameter-group-name default.sqlserver-ex-12.0

Output

ListSpecificDBParameterGroup

 

Parameter Values

List all DB Parameter Values

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table --query "Parameters[*].[ParameterName, ParameterValue]" | more

Output

ListDBParameterValues

 

List DB Parameter – Scan for startup procs

Code


aws rds describe-db-parameters  --db-parameter-group-name default.sqlserver-ex-12.0 --output table  --query "Parameters[?ParameterName==`scan for startup procs`]"

Output

ListDBParameterValue-ScanForStartupProcs

 

List DB Parameter – Optimize for adhoc workloads

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`optimize for ad hoc workloads`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-OptimizeForAdhoc

 

List DB Parameter – Max Server Memory

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`max server memory (mb)`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-MaxServerMemory

Explanation

  1. We are using free AWS and so our max memory is 1GB // SQL Server Express

 

List DB Parameter – Trace Flags

Code


@rem Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema
aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`1222`].[ParameterName, ParameterValue, ApplyMethod]"

Output

— Empty —

 

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?DataType==`boolean`].[ParameterName, ParameterValue, ApplyMethod]"

Output

DescribeDBParameters-Boolean

 

Explanation

  1. One workaround for accessing Trace Flags figures is to look at records whose datatype is Boolean

 

Change DB Parameter – Trace Flags

Code


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group --db-parameter-group-name default.sqlserver-ex-12.0 --parameters "ParameterName=1222,ParameterValue=1, ApplyType=immediate"

Output – Textual

A client error (InvalidParameterValue) occurred when calling the ModifyDBParameterGroup operation: Cannot modify a default parameter group.

Output – Image

CannotModify-DefaultParameterGroup

 

Copy DB Parameter

Code


aws rds copy-db-parameter-group ^
 --source-db-parameter-group-identifier "default.sqlserver-ex-12.0" ^
 --target-db-parameter-group-identifier "corp.sqlserver-ex-12.0" ^
 --target-db-parameter-group-description "corp.sqlserver-ex-12.0"

Output-Text


A client error (InvalidParameterValue) occurred when calling the CopyDBParameterGroup operation: The parameter DBParamet
erGroupName is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, an
d hyphens; and must not end with a hyphen or contain two consecutive hyphens.

 

Output-Image

NotAValididentifier

Bug Reports

  1. aws_db_parameter_group name argument error #6044
    https://github.com/hashicorp/terraform/issues/6044

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


aws rds create-db-parameter-group ^
 --db-parameter-group-name "corp.vMSSQL12" ^
 --db-parameter-group-family "sqlserver-ex-12.0" ^
 --description "corp.sqlserver-ex-12.0"

Output-Text


A client error (InvalidParameterValue) occurred when calling the CreateDBParameterGroup operation: The parameter DBParameterGroupName is not a valid identifier. 
Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Output-Image

CreateDBParameterGroup-WithPeriod

Explanation

  1. DB Parameter group name, corp.vMSSQL12, has a period in it
  2. And, unfortunately the code fails when the parameter has a period

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


     aws rds create-db-parameter-group ^
        --db-parameter-group-name "corp" ^
        --db-parameter-group-family "sqlserver-ex-12.0" ^
        --description "corp.sqlserver-ex-12.0"

Output-Text

{
 "DBParameterGroup": {
 "DBParameterGroupName": "corp",
 "DBParameterGroupFamily": "sqlserver-ex-12.0",
 "Description": "corp.sqlserver-ex-12.0"
 }
}

Output-Image

CreateDBParameterGroup-Successful

Explanation

  1. When we supplied  a simpler name, corp rather than corp.vMSSQL12, we are good.

 

Change DB Parameter

 

Code


aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1222,ParameterValue=1, ApplyMethod=immediate"

Output-Text

{
 "DBParameterGroupName": "corp"
}

Output-Image

ChangeDBParameterGroup-Successful

 

Modify DB Instance

Code


aws rds modify-db-instance  --db-instance-identifier adriel --db-parameter-group-name=corp

Output

modifyDBInstanceGroup

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- applying

Restart DB Instance

Code


aws rds reboot-db-instance  --db-instance-identifier adriel

Output

restartDBInstance

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- pending-reboot

Conclusion

Couple of noteworthy points …

  1. It is best to create and craft DB Parameter groups before hand
    • If you do not assign a DB Instance to a user created Parameter group, you will be assigned a default parameter group
    • Unfortunately, default parameter groups, are not modifiable, and as such one has little flexibility for configuring instances assigned to them
    • Assigning DB instances to them during creation will reduce the number of modifications and reboot thereafter
  2. Since different versions of your database will support and expose different configurable parameters, it is likely best to create DBMS Version parameter groups

 

References

Amazon AW Documentation

  1. AWS Documentation » AWS Command Line Interface » AWS Command Line Interface » Using the AWS Command Line Interface » Controlling Command Output from the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/controlling-output.html
  2. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Working with DB Parameter Groups
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Viewing
  3. AWS Documentation » Amazon Relational Database Service (RDS) Documentation » API Reference » Actions » DescribeDBParameters
    http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBParameters.html

 

Implementation

  1. Why You Should Always Use a Custom DB Parameter Group When Creating an RDS Instance
    http://www.stratalux.com/blog/always-use-custom-db-parameter-group-creating-rds-instance/
  2. VMware Continuent Documentation Center – Changing Amazon RDS Instance Configurations
    https://pubs.vmware.com/continuent/tungsten-replicator-2.2/deployment-amazonrds-rdsconfig.html

 

Amazon – AWS – RDS – SQL Server – DB Server – IP Address

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Relational Data Services ( RDS ), Technical, Virtual Private Cloud (VPC)

Background

Wanted to see what it will take to access our RDS Server via its IP Address as different from accessing through the FQDN.

 

Console

RDS Home Page

Access our region specific RDS Home page ( https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2#dbinstances: ).

DBInstance

 

Get Matching IP Address

Code:


ping FQDN

Output:

ping

 

Validated

Validated that we are able to access the SQL Server Instance using the FQDN’s IP Address

What is the Server’s actual IP Address?

Dynamic Management View

SYS.DM_EXEC_CONNECTIONS

Code


SELECT 
		  [Machine Name] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
		, [ServerName] = SERVERPROPERTY('ServerName')
		, [IP Address Of SQL Server] = LOCAL_NET_ADDRESS
		, [IP Address Of Client] = CLIENT_NET_ADDRESS

FROM SYS.DM_EXEC_CONNECTIONS 

WHERE SESSION_ID = @@SPID

 

Output

SYS

The IP Address of SQL Server is 172.30.0.95

CONNECTIONPROPERTY

Code


SELECT  
	     net_transport       = CONNECTIONPROPERTY('net_transport')
	   , protocol_type       = CONNECTIONPROPERTY('protocol_type')
	   , auth_scheme         = CONNECTIONPROPERTY('auth_scheme')
	   , local_net_address   = CONNECTIONPROPERTY('local_net_address')
	   , local_tcp_port      = CONNECTIONPROPERTY('local_tcp_port') 
	   , client_net_address  = CONNECTIONPROPERTY('client_net_address')

Output

CONNECTIONPROPERTY

The IP Address of SQL Server is again noted as 172.30.0.95.

Attempt DB Server Access using Server’s IP Address

If you try to access the DB Server using the IP Address 172.30.0.95, you will not be able to.

 

Is your DB Server IP Address private?

MxToolbox

Go to http://mxtoolbox.com/ReverseLookup.aspx and determine if our address is private

Enter Address

ReverseLookup

Response

IsAPrivateIPAddress

Is a private IP address…

 

Commentary

It is important to either mark the DB Server as publicly available or to properly configure the VPC to properly route traffic behind the scene to the DB Server’s private IP Address.

Amazon – AWS – Free Tier – RDS – Create Instance

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Relational Data Services ( RDS )

RDS

Create Instance

Launch a DB Instance

We access our region specific RDS Dashboard ( https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2 )

DB Instances Exists

If DB Instances exists, the count of DB Instances will be listed besides the “DB Instances” item.

CreateInstance-LaunchADBInstance

 

DB Instances Do Not Exist

If DB Instances do not exist.

URLs

  1. Region Specific

 

EmptyDBInstances

 

 

Select an engine

Initial

Here is the initial screen for choosing the DB Engine.

We can see that the default is Amazon Aurora.

SelectEngine-Original

 

SQL Server

Once we select SQL Server, we can see the editions of SQL Servers available – Express, Web, Standard, and Enterprise.

SelectEngineSQLServer (Express-Web--StandardEdition--EnterpriseEdition)

 

SQL Server Express

Again, we go the free route.

And, so we will choose “Microsoft SQL Server Express Edition”.

Specify DB Details

Initial

SpecifyDBDetails-Initial

 

Constrain to Free Tier – Off

When we do not have free tier checked here is our screen.

SpecifyDBDetails-OnlyShowOptionsThatAreEligibleForRDSFreeTrier-Off

 

Explanation
  1. db.t2.micro – 1 vpc, 1 Gib RAM
  2. db.t2.micro – 1 vpc, 0.613 Gib RAM

 

Constrain to Free Tier – On

When we have free tier checked here is our screen.

SpecifyDBDetails-OnlyShowOptionsThatAreEligibleForRDSFreeTrier-On

Availed
  1. A single db.t2.micro instance
  2. 20 GB of storage
Purpose
  1. Allows new AWS customers to gain hands-on experience with Amazon RDS
DB Instance Class
  1. db.t2.micro – 1 vpc, 1 Gib RAM
  2. db.t2.micro – 1 vpc, 0.613 Gib RAM

 

Choices

SpecifyDBDetails-Completed

 

Explanation

  1. DB Engine :- sqlserver-ex
  2. License Model :- license-included
  3. DB Engine Version :- 12.00.4422.0.v1
  4. DB Instance Class :- db.t1.micro — 1 vCPU, 1 GiB RAM
  5. Storage Type :- Magnetic
  6. Allocated Storage :- 20 GB
  7. Storage Type :- Magnetic
  8. Allocated Storage :- 20 GB
  9. DB Instance Identifier :- adriel
  10. Master username :- sa
  11. Master Password :- xxxx
  12. Confirm Password :- xxxx

Btw, the name adriel means “flock of God“; as seen here

 

Configure Advanced Settings

ConfigureAdvancedSettings-Original

 

Configure Advanced Settings – Network & Security

ConfigureAdvancedSettings - Network & Security

 

Configure Advanced Settings – Microsoft SQL Server Windows Authentication

ConfigureAdvancedSettings - Microsoft SQL Server Windows Authentication

 

Configure Advanced Settings – Database Options

ConfigureAdvancedSettings - Database Options

Configure Advanced Settings – Backup

ConfigureAdvancedSettings - Backup

 

Configure Advanced Settings – Monitoring

ConfigureAdvancedSettings - Monitoring

Configure Advanced Settings – Maintenance

ConfigureAdvancedSettings - Maintenance

 

 

Your DB Instance Is Being Created

YourInstanceIsBeingCreated

We are told that our “Database Instance is being created“….

And, assigned a couple of follow up items.  And, those are:

  1. Configure Security group
  2. Consider Amazon Elasticache
    • Memcached
    • Redis-compatible in-memory cache

 

Review Database Instance Creation Progress

URL

  1. Region Specific URL

 

Status – Creating

ReviewDBInstanceCreationProgress

Columns

  1. Engine :- SQL Server Express
  2. DB Instance :- adriel
  3. Status
    • Creating
    • backing-up
  4. Class :- db.t2.micro
  5. VPC :- vpc-75d97a11
  6. Multi-AZ :- N/A
  7. Replication Role
  8. Encrypted :- No

Status – Backing-up

ReviewDBInstanceCreationProgress-Status-backingup

 

Status – available

ReviewDBInstanceCreationProgress-Status-available

 

VPC

VPC Dashboard

VPC Resources

Here are our currently assigned VPC Resources

VPCDashboard-Initial

 

VPC Resources

Here is a current list of VPC Resources

VPCResources

Which one is our SQL Server Instance using?

Here is one way to determine our DB Instance’s VPC:

  1. Access RDS Dashboard

 

DB Instance

ListDBInstances

 

VPC Resource – VPC Selected

VPCSelected

 

  1. VPC ID :- vpc-75d97a11
  2. State :- available
  3. VPC CIDR :- 172.30.0.0/16
  4. Route Table :- rtb-8ba921ef
  5. Network ACL :- acl-2b06b44f

 

Security

There are a couple of choices for guiding our DB Instance availability.

Those choices are Network ACLs and Network Groups.

Security Groups

URL

  1. Region Specific

 

Here are the Network Groups that are currently assigned to us:

Security-SecurityGroups-List

 

 

Which Security Groups?

Which security groups are relevant to our VPC

  1. VPC
    • We know that our VPC is vpc-75d97a11
      • And, so we will ignore Group ID sg-a95d78ce, at this time
      • And, focus on sg-32fbc955 ( default ) and sg-07fbc960 ( rds-launch-wizard )

Took to the Net and found

What are the default security groups created when I set up AWS EB for the first time?
http://stackoverflow.com/questions/27829620/what-are-the-default-security-groups-created-when-i-set-up-aws-eb-for-the-first

 

Here is Scuba Dev’s response

  1. rds-launch-web
    • When you manually launch an EC2 VM from the web console, AWS will provide you with the option of reusing an existing security group or creating a new one.
    • When you create a new one, the default rule is SSH (port 22) and a default security group name of “launch-wizard-#“.
  2. default

It looks like either will do.

Out of curiosity let us dig deeper, by clicking on each security group and reviewing its present construct.

VPC Security Group – default

DefaultVPCSecurityGroup-Initial

 

VPC Security Group – RDS Launch Wizard

InboundRules

Tabulated

Group Name Group ID Type Product Port Range Source
Default sg-32fbc955
 All Traffic  ALL  ALL  sg-32fbc955
rds-launch-wizard  sg-07fbc960
 MS SQL (1433)  TCP (6)  1433  207.140.111.60/32

 

 

Explanation

  1. Default
    • The default group is wide open
  2. RDS Launch Wizard
    • Type = MS SQL Server (1433)
    • Product = TCP (6)
    • Port Range = 1433
    • Source = 207.140.111.60 / 32
      • Because the subnet is 32, the range is the lone host ( 207.140.111.60 )

Specificity is good here and so we will choose the “RDC Launch Wizard”

Security Groups – RDC Launch Wizard

Expand to Self

Get Public IP Address

Authorizing Access to an instance
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html
Decide who requires access to your instance; for example, a single host or a specific network that you trust. In this case, we use your local system’s public IP address. You can get the public IP address of your local computer using a service. For example, we provide the following service: http://checkip.amazonaws.com. To locate another service that provides your IP address, use the search phrase “what is my IP address”. If you are connecting through an ISP or from behind your firewall without a static IP address, you need to find out the range of IP addresses used by client computers.

When we access http://checkip.amazonaws.com/, we received http://checkip.amazonaws.com/.

checkip

As suggested, you can simply google same, what is my ip address ( https://www.google.com/#q=what+is+my+ip+address ).

 

Review & Add Public IP Address

Let us expand our IP Addresses by adding our public IP Address

Here are the currently listed IP Addresses

ManageExistingInboundRules

 

Acknowledgement.

Only now did I notice that our public listed IP Address is the one auto-added, in the first place.

RDS

Console

Review DB Instance

ConnectionInformation-20160428-0222PM-Cropped

 

Explanation

  1. Endpoint: adriel.[xxxxx].us-west-2.rds.amazonaws.com:1433
  2. DB Instance: adriel
  3. Status : available
  4. Connection Information
    • Publicly Accessible : No
    • Master Username: sa
    • Security Group Rules
      • Security Group
        • rds-launch-wizard
          • Type :- CIDR-IP – bound
          • Rule  :- 207.140.111.60/32

 

Client

SQL Server Management Studio

Connect to DB Instance

ConnectToServer

 

Error Messages

Error=25AndError=87

 

Image

AdvancedInformation

Textual


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)

Error Number: 87
Severity: 20
State: 0

VPC

VPC Security

Allow all hosts

Add All Source

AllIPAddresses - Edit

Review Sources

AllIPAddresses - Completed

 

RDC

Console

ConnectionInformation-20160428-0301PM

Connection Information

Rule

  1. 207.140.111.60/32
  2. 0.0.0.0/32

 

Make Publicly Available

CLI

Let us make publicly available via CLI

Code

Syntax

aws rds modify-db-instance --db-instance-identifier [instance-identifier] --publicly-accessible --apply-immediately
Sample

aws rds modify-db-instance --db-instance-identifier adriel --publicly-accessible --apply-immediately

Output

makePubliclyAvailable

 

Console

Access DB Instance Modify Panel

We can modify the DB instance by doing the following

  1. Access RDS Dashboard
  2. Select the DB Instance
  3. Click on the Instance Actions button
  4. From the drop-down menu, select the Modify option

 

Modify

 

Modify DB Instance

ModifyDBInstance

 

 

Review RDS Dashboard – Instance – Connection Information

Same confirmed via RDS Dashboard – Connection Information …

ConnectionInformation-20160428-0403PM

Client

SQL Server Management Studio

Connected….

Connected

 

Summary

We were successfully able to create a new DB Instance.

We accessed the Virtual Private Cloud (VPC) panels to expand the IP Addresses that are allowed access to our DB.

But, unfortunately none of our attempts succeeded.

We thus reverted to making the DB Instance itself publicly available.

 

References

AWS – Official

  1. Creating a SQL Server DB Instance and Connecting to a Database on a SQL Server DB Instance
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html
  2. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Virtual Private Clouds (VPCs) and Amazon RDS » Scenarios for Accessing a DB Instance in a VPC
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.Scenarios.html#USER_VPC.Scenario4
  3. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » MySQL on Amazon RDS » Modifying a DB Instance Running the MySQL Database Engine
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.MySQL.html
  4. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Modifying a DB Instance and Using the Apply Immediately Parameter
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html
  5. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Virtual Private Clouds (VPCs) and Amazon RDS » Working with an Amazon RDS DB Instance in a VPC
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html
  6. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Renaming a DB Instance
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RenameInstance.html
  7. AWS Documentation » Amazon Virtual Private Cloud » Getting Started Guide » Getting Started with Amazon VPC » Step 2: Create a Security Group
    http://docs.aws.amazon.com/AmazonVPC/latest/GettingStartedGuide/getting-started-create-security-group.html
  8. Authorizing Inbound Traffic for Your Linux Instances
    http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html

 

CLI

  1. Modify DB Instance
    http://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html

 

Network CIDR

  1. Setting CIDR/IP so anyone can access it from any IP?
    http://stackoverflow.com/questions/6365773/setting-cidr-ip-so-anyone-can-access-it-from-any-ip

 

Sample Implementation

  1. MySQL
  2. Apache
  3. MS SQL Server


StackOverflow

  1. What are the default security groups created when I set up AWS EB for the first time?
    http://stackoverflow.com/questions/27829620/what-are-the-default-security-groups-created-when-i-set-up-aws-eb-for-the-first

 

SlideShare.Net

  1. AWS Cloud – Network Security and Access Control in AWS
    http://www.slideshare.net/AmazonWebServices/network-security-and-access-control-in-aws

SQL Server on Amazon RDS – Create Database – File Groups

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Microsoft, MS SQL Server, Relational Data Services ( RDS ), Technical

Background

Quick commentary on creating a database that sits on multiple filegroups on Amazon RDS.

 

Guide

Launch SQL Server Management Studio (SSMS) and connect to your Amazon RDS MS SQL Server.

New Database

General

Initial

Here we specify the database name has DBLAB.

And, the system autofills the Logical names of DBLAB for the File Type of ROW.  And, DBLAB_log for the LOG.

NewDatabase - General

 

Filegroups

Initial

Here is the initial screen for filegroups.

NewDatabase-FileGroups

 

Post

Here is what things look like once we added Indexes and Indexes2 as file groups.

NewDatabase-FileGroups-AddedAdditionalFilegroups

General

Post

We return to the general Tab and added new files and positioning them to the corresponding Filegroup

  1. Logical name of DBLAB_Indexes attached to Indexes Filegroup
  2. Logical name of DBLAB_Indexes2 attached to Indexes2 Filegroup

 

NewDatabase - General-AddedAdditionalFiles

 

Best Practice

Best practice suggest that we should target a separate physical disk, LUN, for each file group.

If we try to seek out an alternate File path, we will get the error stated below.

Image

xp_fixeddrives-error-229

Textual


The execute permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'

Implication

We do not have permission to access xp_fixedrives.  xp_fixedrives lists available storage on attached physical drives.

SQL Script

Btw, here is the SQL Script.


CREATE DATABASE [DBLAB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBLAB', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%), 
 FILEGROUP [Indexes] 
( NAME = N'DBLAB_Indexes', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB ), 
 FILEGROUP [Indexes2] 
( NAME = N'DBLAB_Indexes2', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes2.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB )
 LOG ON 
( NAME = N'DBLAB_log', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Summary

One has to rely on the underlying storage configuration when using a provisioned system such as RDS.

Typical engineering thoughts such as separating data and logs are not available.

Same with attempts to disambiguate data and index I/O.

Amazon – RDS – Command Line Interface ( CLI ) – Returning empty resultset

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), CLI, describe-db-instances, describe-db-parameter-groups, Relational Data Services ( RDS ), Technical

Background

Just blew a couple of hours trying to determine why a couple of RDS/CLI Commands are coming back empty.

 

Commands

Here are the commands I tried out

describe-db-instances

Command


aws rds describe-db-instances

Output


{
"DBInstances": []
}

 

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups

 

Output


{
 "DBParameterGroups": []
}

Remediation

Traced the error back to the fact that I had set a wrong region.

Workarounds are to override the default per each command utterance, or go back and reset the default.

Specify Region at Command Level

describe-db-instance

Command


aws rds describe-db-instances  --region us-west-2

Output

describe-db-instances-20160426

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups --region us-west-2

Output

describe-db-parameter-groups-20160426

 

Reset default region

Review Configuration

Command


aws configure

Output

Wrong default – us-west-1

aws configure - 20160426 - 0658PM

Reset Configuration

  1. Connect to Console and get default

 

Connect to Console and get default

dbinstances-RDS-ScreenShot

 

Reset default via “aws configure”

Here we change the default region from us-west-1 to us-west-2


aws configure

   default region name [us-west-1] : us-west-2

aws configure - 20160426 - 0708PM

 

Confirm

Issue query without specifying default

Code


aws rds describe-db-instances

Output

DescribeDBInstancePostSettingDefault

Amazon – Web Services – RDS – Command Line Interface – Day 1

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), MySQL, Relational Data Services ( RDS )

Background

This is will be first of likely many posts on Amazon Web Services, specifically RDS.

RDS stands for Relational Data Services. And, it encompasses Oracle, Microsoft SQL Server, PostgreSQL, and MySQL and its variants ( Aurora, MariaDB )

 

Introduction

As a Database Administrator needs will arise to perform administrative tasks such as reviewing existing instances, creating new ones, taking a snapshot ( so that we can a fallback in case we mess things up), create and review security groups.

Yes, these tasks can be completed via the Web Console. But, seriously, in some cases nothing beats scripting.

And, so let us see how we can access scripting functions.

 

Web Portal

Took to the web portal https://console.aws.amazon.com/console/home.

Signin

AWS-AMAZON-COM-SIGNIN

Console

Homepage

No CLI Interface Menu

There is no interface to the CLI Console.

So how to get a CLI Console ?

 

Download

Found a nice lead-in “Installing the AWS Command Line Interface” here.

MS Windows

MSI

As my current machine is MS Windows, chose the MSI Path.

MSIInstaller

 

Installed

Installed Downloaded application.

Custom Setup

Chose to target D:\Program Files\Amazon\AWSCLI.

CustomSetup

 

Usage

Launch Command Session

Launch command session (cmd.exe)

Change Directory

Syntax

CD /D [installed-folder]

 

Sample

CD /D D:\Program Files\Amazon\AWSCLI\awscli

 

AWS Configure

We need to provide our Access ID and Key.

And, optionally a default region.


D:\Program Files\Amazon\AWSCLI\awscli\> aws configure
AWS Access Key ID [None]:
AWS Secret Access Key [None]:
Default region name [None]:
Default output format [None]:

Skipped AWS Configure

If you try to issue commands, before first setting your credentials, you will get the error pasted below.

Output


Unable to locate credentials. You can configure credentials by running "aws configure".

Sample

Describe DB Instance

Syntax


D:\Program Files\Amazon\AWSCLI\awscli>aws rds describe-db-instances

Sample


D:\Program Files\Amazon\AWSCLI\awscli>aws rds describe-db-instances --region us-east-1

Output


A client error (AccessDenied) occurred when calling the DescribeDBInstances operation: User: arn:aws:iam:1i118717:user/daniel is not authorized to perform:
 rds:DescribeDBInstances

Explanation

  1. In our case, our account has yet to be privileged to query for DB Instances

 

Command Reference

Here is a list of RDS Commands – RDS – References

Argument Explanation
describe-certificates Lists the set of CA certificates provided by Amazon RDS for this AWS account.
describe-db-engine-versions Returns a list of the available DB engines.
describe-db-instances Returns information about provisioned RDS instances.
describe-db-log-files Returns a list of log files
describe-db-parameter-groups Returns list of DB Parameter groups
describe-db-snapshots Returns list of DB Snapshots

 

References

AWS Command Line Interface

  1. Getting Help with the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/getting-help.html
  2. Command Structure in the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/command-structure.html

 

Microsoft SQL Server on Amazon RDS

  1. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html