LAMP | Kaizen - Continuous Improvement

Category: LAMP

Stored Procedure Error Logging

Posted on October 28, 2017 by

Raju Venkataraman Posted in AWS RDS, Interview Questions, SQL Server — No Comments ↓

Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block

USE TEMPDB;
GO
IF OBJECT_ID('APM.ErrorLog') IS NOT NULL
    DROP TABLE APM.ErrorLog;
GO


CREATE TABLE APM.ErrorLog
(
    ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY,
    DatabaseID INT,
    DatabaseName sysname,
    SPID INT,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure VARCHAR(400),
    ErrorMessage NVARCHAR(MAX),
    AdditionalInfo NVARCHAR(MAX),
    CreatedBY VARCHAR(50)
        DEFAULT SYSTEM_USER,
    CreatedDateTime DATETIME
        DEFAULT GETUTCDATE(),
    Status INT
        DEFAULT 1
);
GO

CREATE OR ALTER PROC APM.LogErrorInfo
(
    @pAdditionalIndfo NVARCHAR(550) = NULL,
    @pCreatedBy VARCHAR(50) = NULL
)
AS
BEGIN
    INSERT INTO APM.ErrorLog
    (
        DatabaseID,
        DatabaseName,
        SPID,
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorMessage,
        AdditionalInfo,
        CreatedBY
    )
    SELECT DB_ID(),                       -- DatabaseID - int
           DB_NAME(),                     -- DatabaseName - sysname
           @@SPID,                        -- SPID - int
           ERROR_LINE(),                  -- ErrorNumber - int
           ERROR_SEVERITY(),              -- ErrorSeverity - int
           ERROR_STATE(),                 -- ErrorState - int
           ERROR_PROCEDURE(),             -- ErrorProcedure - varchar(400)
           ERROR_MESSAGE(),               -- ErrorMessage - nvarchar(max)
           ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
           ISNULL(@pCreatedBy, SYSTEM_USER);

    SELECT DB_ID(),                       -- DatabaseID - int
           DB_NAME(),                     -- DatabaseName - sysname
           @@SPID,                        -- SPID - int
           ERROR_LINE(),                  -- ErrorNumber - int
           ERROR_SEVERITY(),              -- ErrorSeverity - int
           ERROR_STATE(),                 -- ErrorState - int
           ERROR_PROCEDURE(),             -- ErrorProcedure - varchar(400)
           ERROR_MESSAGE(),               -- ErrorMessage - nvarchar(max)
           ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
           ISNULL(@pCreatedBy, SYSTEM_USER);

/*
 -- UNIT TEST
 BEGIN TRY
SELECT 1 /0
END TRY
BEGIN CATCH
EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test'
END CATCH

SELECT * FROM APM.ErrorLog
*/

END;

USE TEMPDB;

IF OBJECT_ID('APM.ErrorLog') IS NOT NULL

DROP TABLE APM.ErrorLog;

CREATE TABLE APM.ErrorLog

(

ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY,

DatabaseID INT,

DatabaseName sysname,

SPID INT,

ErrorNumber INT,

ErrorSeverity INT,

ErrorState INT,

ErrorProcedure VARCHAR(400),

ErrorMessage NVARCHAR(MAX),

AdditionalInfo NVARCHAR(MAX),

CreatedBY VARCHAR(50)

DEFAULT SYSTEM_USER,

CreatedDateTime DATETIME

DEFAULT GETUTCDATE(),

Status INT

DEFAULT 1

);

CREATE OR ALTER PROC APM.LogErrorInfo

(

@pAdditionalIndfo NVARCHAR(550) = NULL,

@pCreatedBy VARCHAR(50) = NULL

)

BEGIN

INSERT INTO APM.ErrorLog

(

DatabaseID,

DatabaseName,

SPID,

ErrorNumber,

ErrorSeverity,

ErrorState,

ErrorProcedure,

ErrorMessage,

AdditionalInfo,

CreatedBY

)

SELECT DB_ID(), -- DatabaseID - int

DB_NAME(), -- DatabaseName - sysname

@@SPID, -- SPID - int

ERROR_LINE(), -- ErrorNumber - int

ERROR_SEVERITY(), -- ErrorSeverity - int

ERROR_STATE(), -- ErrorState - int

ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)

ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)

ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)

ISNULL(@pCreatedBy, SYSTEM_USER);

SELECT DB_ID(), -- DatabaseID - int

DB_NAME(), -- DatabaseName - sysname

@@SPID, -- SPID - int

ERROR_LINE(), -- ErrorNumber - int

ERROR_SEVERITY(), -- ErrorSeverity - int

ERROR_STATE(), -- ErrorState - int

ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)

ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)

ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)

ISNULL(@pCreatedBy, SYSTEM_USER);

-- UNIT TEST

BEGIN TRY

SELECT 1 /0

END TRY

BEGIN CATCH

EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test'

END CATCH

SELECT * FROM APM.ErrorLog

END;

Install and Run SQL Server Docker Container on Mac

Posted on October 8, 2017 by

Raju Venkataraman Posted in AWS, EC2, Git — No Comments ↓

Install and Run SQL Server Docker Container on Mac Like most people, I use Mac , Windows as well Linux OS for development and testing purposes. Primarily I use Mac for Development purpose. I have few projects which uses SQL Server as…

Bulk Load Data Files in S3 Bucket into Aurora RDS

Posted on July 15, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Technology — 1 Comment ↓

Bulk Load Data Files in S3 Bucket into Aurora RDS We typically get data feeds from our clients ( usually about ~ 5 – 20 GB) worth of data. We download these data files to our lab environment and use shell…

MYSQL / AURORA Database Troubleshooting

Posted on June 28, 2017 by

Raju Venkataraman Posted in AWS RDS, MySQL, Technology — No Comments ↓

Number of Connections by Host

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count
FROM information_schema.processlist 
group by SUBSTRING(HOST, 0, 10) , DB,USER 
ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count

FROM information_schema.processlist

group by SUBSTRING(HOST, 0, 10) , DB,USER

ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

Aurora Max Connections

select AURORA_VERSION();

select * from mysql.slow_log 
where sql_text not like '%LOAD DATA%'
order by query_time desc
limit 1000 ;

select count(*) from mysql.general_log 
where user_host not like 'rdsadmin%'
and user_host not like '[rdsadmin]%'
and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log  ;

select @@MAX_CONNECTIONS
-- '4000'

select *  from mysql.general_log 
where command_type like '%Connect%';

select *  from mysql.general_log_backup 
where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

select AURORA_VERSION();

select * from mysql.slow_log

where sql_text not like '%LOAD DATA%'

order by query_time desc

limit 1000 ;

select count(*) from mysql.general_log

where user_host not like 'rdsadmin%'

and user_host not like '[rdsadmin]%'

and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log ;

select @@MAX_CONNECTIONS

-- '4000'

select * from mysql.general_log

where command_type like '%Connect%';

select * from mysql.general_log_backup

where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Memory-Optimized Tables

Posted on June 23, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Interview Questions, Scaling SQL Server, SQL Server — No Comments ↓

Monitor Memory Optimized Table Space Usage :

;
WITH    system_allocated_memory ( system_allocated_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              + SUM(TMS.memory_allocated_for_indexes_kb) )
                                        / 1024.00)
                                 FROM   [sys].[dm_db_xtp_table_memory_stats] TMS
                                 WHERE  TMS.object_id <= 0
                               ), 0.00)
             ),
        table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS table_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              - SUM(TMS.memory_used_by_table_kb) )
                                        / 1024.00)
                               ), 0.00) AS table_unused_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS index_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)
                                                              - SUM(TMS.memory_used_by_indexes_kb) )
                                        / 1024.00)
                               ), 0.00) AS index_unused_memory_in_mb
               FROM     [sys].[dm_db_xtp_table_memory_stats] TMS
               WHERE    TMS.object_id > 0
             )
    SELECT  s.system_allocated_memory_in_mb ,
            t.table_used_memory_in_mb ,
            t.table_unused_memory_in_mb ,
            t.index_used_memory_in_mb ,
            t.index_unused_memory_in_mb ,
            ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),
                                               'IsXTPSupported')
                   ), 0) AS has_memory_optimized_filegroup
    FROM    system_allocated_memory s ,
            table_index_memory t

SELECT  t.object_id ,
        t.name ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb
                                                  - TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_unused_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb
                                                  - TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_unused_memory_in_mb
FROM    sys.tables t
        JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

;

WITH system_allocated_memory ( system_allocated_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

+ SUM(TMS.memory_allocated_for_indexes_kb) )

/ 1024.00)

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id <= 0

), 0.00)

table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)

/ 1024.00 ))

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

- SUM(TMS.memory_used_by_table_kb) )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)

/ 1024.00 ))

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)

- SUM(TMS.memory_used_by_indexes_kb) )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id > 0

)

SELECT s.system_allocated_memory_in_mb ,

t.table_used_memory_in_mb ,

t.table_unused_memory_in_mb ,

t.index_used_memory_in_mb ,

t.index_unused_memory_in_mb ,

ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),

'IsXTPSupported')

), 0) AS has_memory_optimized_filegroup

FROM system_allocated_memory s ,

table_index_memory t

SELECT t.object_id ,

t.name ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb

- TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb

- TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM sys.tables t

JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

All Memory Used by Memory Optimized Table across Database Engine

-- this DMV accounts for all memory used by the hek_2 engine 
SELECT type ,
name ,
memory_node_id ,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%'

-- this DMV accounts for all memory used by the hek_2 engine

SELECT type ,

name ,

memory_node_id ,

pages_kb / 1024 AS pages_MB

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%'

Enable Natively Compiled Stored Procedure Stats Collection

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1  
DECLARE @c BIT  
EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT  
SELECT  @c AS 'collection status'

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1

DECLARE @c BIT

EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT

SELECT @c AS 'collection status'

DBCC FREEPROCCACHE does not remove natively compiled stored procedures from Plan Cache

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT  @sql += N'EXECUTE sp_recompile N'''
        + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''
'
FROM    sys.sql_modules sm
        JOIN sys.objects o ON sm.object_id = o.object_id
WHERE   uses_native_compilation = 1

EXECUTE sp_executesql @sql

	
-- Reset wait and latch statistics.
DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT @sql += N'EXECUTE sp_recompile N'''

+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''

FROM sys.sql_modules sm

JOIN sys.objects o ON sm.object_id = o.object_id

WHERE uses_native_compilation = 1

EXECUTE sp_executesql @sql

-- Reset wait and latch statistics.

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

…

Brute force attack on SQL Server

Posted on June 22, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Interview Questions, SQL Server — No Comments ↓

Brute force attack on SQL Server If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks. Following query will help you identify the failed login attempts and you…

SQL Injection attack on Website hosted on EC2 Machine

Posted on June 10, 2017 by

Raju Venkataraman Posted in AWS, EC2, SQL Server, Technology — No Comments ↓

SQL Injection attack on Website hosted on EC2 Machine: I setup a website http://h1bsalary.online with publicly available dataset. As soon as I launched website, numerous trolls and automated bots sending traffic to identify the vulnerabilities. Safe-Guards I have taken so far :…

Install SQL Server Docker Container on Linux

Posted on May 25, 2017 by

Raju Venkataraman Posted in AWS, Scaling SQL Server, SQL Server — No Comments ↓

Install SQL Server Docker Container on Linux and Connect through Client Tools I was surprised by ability to run setup and run SQL Server Docker container. It only takes about 2 commands and 2 Minutes.

$docker pull microsoft/mssql-server-linux
$docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
$docker exec -it 40dd "bash"
$/opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:

$docker pull microsoft/mssql-server-linux

$docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

$docker exec -it 40dd "bash"

$/opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux
[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"
root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:

Command History
[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux
Using default tag: latest
latest: Pulling from microsoft/mssql-server-linux
4c0c60131530: Pull complete
Digest: sha256:604d27fe5d3d9b4434fb1657e9bf4f2c2bf55ea9bd29dc0cb3660d84bc6f56a8
Status: Downloaded newer image for microsoft/mssql-server-linux:latest
[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
40dde973f4a0cc2af469f9d1c2182403d1e22e28c2a8821e29ce832529965513
[root@ip-10-0-0-120 ec2-user]# docker -it 40dd "bash"
flag provided but not defined: -it
See 'docker --help'.
[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"
root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1> select @@servername;
2> go

--------------------------------------------------------------------------------------------------------------------------------
40dde973f4a0

(1 rows affected)
1> select db_name();
2> go

--------------------------------------------------------------------------------------------------------------------------------
master

(1 rows affected)

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux

[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"

root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

Command History

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux

Using default tag: latest

latest: Pulling from microsoft/mssql-server-linux

4c0c60131530: Pull complete

Digest: sha256:604d27fe5d3d9b4434fb1657e9bf4f2c2bf55ea9bd29dc0cb3660d84bc6f56a8

Status: Downloaded newer image for microsoft/mssql-server-linux:latest

[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

40dde973f4a0cc2af469f9d1c2182403d1e22e28c2a8821e29ce832529965513

[root@ip-10-0-0-120 ec2-user]# docker -it 40dd "bash"

flag provided but not defined: -it

See 'docker --help'.

[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"

root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

1> select @@servername;

2> go

--------------------------------------------------------------------------------------------------------------------------------

40dde973f4a0

(1 rows affected)

1> select db_name();

2> go

--------------------------------------------------------------------------------------------------------------------------------

master

(1 rows affected)

Version Info:

select @@version

/*
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64) 
May 10 2017 12:21:23 
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)
*/

select @@version

Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)

May 10 2017 12:21:23

Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)

…

AWS Data Pipeline & Load S3 File Into MySQL

Posted on May 5, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, EC2, Technology — No Comments ↓

Getting started with AWS Data Pipeline AWS Data Pipeline is a web service that you can use to automate the movement and transformation of data. With AWS Data Pipeline, you can define data-driven workflows, so that tasks can be dependent on…

AWS Regions and Availability Zones

Posted on May 5, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Technology — No Comments ↓

AWS Regions and Availability Zones

C:\Users\Raju>AWS ec2 describe-regions
C:\Users\Raju> aws ec2 describe-availability-zones

1 2	C:\Users\Raju>AWS ec2 describe-regions C:\Users\Raju> aws ec2 describe-availability-zones

EC2 Regions and US-WEST-2 Availability Zones References: http://docs.aws.amazon.com/general/latest/gr/rande.html http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html#using-regions-availability-zones-describe

Tagged with: AWS

Restrict S3 bucket Access to VPC

Posted on October 16, 2016 by

Raju Venkataraman Posted in AWS, Technology — No Comments ↓

Allow access to S3 bucket only from VPC Currently I am evaluating options to lockdown permissions to my S3 Buckets as part of Security Enhancements. These are the steps I followed to lock down S3 Bucket access only to my…

How to become DBA favorite Developer

Posted on September 20, 2016 by

Raju Venkataraman Posted in AWS RDS, SQL Server — No Comments ↓

Application Name : When a connection is made to SQL Server there are by default no way for SQL Server to know what software is making the connection. If several apps are using a shared SQL Server and there are…

AWS SLA Summary

Posted on August 6, 2016 by

Raju Venkataraman Posted in AWS, AWS RDS, EC2, Technology — No Comments ↓

AWS SLA Summary SLA Percentages Useful Links Cloud Provider Service Availability https://cloudharmony.com/status

AWS RDS First Touch Penalty

Posted on August 3, 2016 by

Raju Venkataraman Posted in AWS RDS, SQL Server, Technology — No Comments ↓

AWS RDS First Touch Penalty According to AWS Documentation The first time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk…

Useful Free Tools and Books

Posted on August 2, 2016 by

Raju Venkataraman Posted in AWS RDS, Downloads, SQL Server — No Comments ↓

Useful Free Tools Download Links Wonderful Microsoft utilities PerfMon , Profiler , Dynamic Management Views and System Tables should provide lot of insights and help in diagnosing and troubleshooting issues. These free tools are nice add-ons and improves the productivity. …

AWS RDS API Reference

Posted on August 2, 2016 by

Raju Venkataraman Posted in AWS, AWS RDS — No Comments ↓

AWS RDS API Reference Some of the RDS API command usuage . Editable parameters list for SQL Server. AWS RDS Options Group

aws rds  describe-option-group-options --engine-name sqlserver-se

1	aws rds describe-option-group-options --engine-name sqlserver-se

AWS RDS SQLServer SE 12.0 Parameter Groups

aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-se-12.0

1	aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-se-12.0

AWS RDS Instance Details

aws rds describe-db-instances
aws rds describe-db-instances --db-instance-identifier ramblingsofraju-aws-prod

1 2	aws rds describe-db-instances aws rds describe-db-instances --db-instance-identifier ramblingsofraju-aws-prod

AWS RDS Options Group Options…

Remote Host Identification SSH Error

Posted on August 1, 2016 by

Raju Venkataraman Posted in AWS, EC2, Technology — No Comments ↓

Remote Host Identification SSH Error

Rajus-MBP:.ssh Raju$ ssh -i Key.pem ec2-user@54.214.1.26
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
SHA256:oFgx2U4Q0KUxtFnouxHYJdLyH6qDbX/rKtsg
Please contact your system administrator.
Add correct host key in /Users/Raju/.ssh/known_hosts to get rid of this message.
Offending RSA key in /Users/Raju/.ssh/known_hosts:1
RSA host key for 54.214.1.26 has changed and you have requested strict checking.
Host key verification failed.

Rajus-MBP:.ssh Raju$ ssh -i Key.pem ec2-user@54.214.1.26

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!

Someone could be eavesdropping on you right now (man-in-the-middle attack)!

It is also possible that a host key has just been changed.

The fingerprint for the RSA key sent by the remote host is

SHA256:oFgx2U4Q0KUxtFnouxHYJdLyH6qDbX/rKtsg

Please contact your system administrator.

Add correct host key in /Users/Raju/.ssh/known_hosts to get rid of this message.

Offending RSA key in /Users/Raju/.ssh/known_hosts:1

RSA host key for 54.214.1.26 has changed and you have requested strict checking.

Host key verification failed.

Edit known_hosts and remove the offending host entry and SSH into Host Again

Rajus-MBP:.ssh Raju$ vi known_hosts

Rajus-MBP:.ssh Raju$ ssh -i Key.pem ec2-user@54.214.1.26
The authenticity of host '54.214.1.26 (54.214.1.26)' can't be established.
ECDSA key fingerprint is SHA256:2VcR+DiKNRwyQwZ2LqtZ6EHxQYv5MWMAsrrI.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '54.214.1.26' (ECDSA) to the list of known hosts.
Last login: Fri Jul 29 23:08:35 2016 from

__| __|_ )
_| ( / Amazon Linux AMI
___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2016.03-release-notes/
4 package(s) needed for security, out of 4 available
Run "sudo yum update" to apply all updates.

Rajus-MBP:.ssh Raju$ vi known_hosts

Rajus-MBP:.ssh Raju$ ssh -i Key.pem ec2-user@54.214.1.26

The authenticity of host '54.214.1.26 (54.214.1.26)' can't be established.

ECDSA key fingerprint is SHA256:2VcR+DiKNRwyQwZ2LqtZ6EHxQYv5MWMAsrrI.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '54.214.1.26' (ECDSA) to the list of known hosts.

Last login: Fri Jul 29 23:08:35 2016 from

__| __|_ )

_| ( / Amazon Linux AMI

___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2016.03-release-notes/

4 package(s) needed for security, out of 4 available

Run "sudo yum update" to apply all updates.

AWS SQL Server RDS Native Backup Restore

Posted on July 30, 2016 by

Raju Venkataraman Posted in AWS, AWS RDS, SQL Server, Technology — 47 Comments ↓

Steps to Restore .bak SQL Server backup file to Amazon SQL Server RDS
Step by Step Guide to backup and restore SQL Server backups on AWS RDS through S3 Buckets.

Launching AWS SQL Server RDS

Posted on July 16, 2016 by

Raju Venkataraman Posted in AWS, AWS RDS, Scaling SQL Server, SQL Server, Technology — 1 Comment ↓

Learning from Launching AWS SQL Server RDS
We are evaluating options to migrate our read/write heavy production SQL Server database to amazon SQL Server RDS. We have pretty high throughput needs for few hours a day for few months in a year ,which is mission critical for our business success. We are evaluating pros and cons of moving to amazon RDS with provisioned IOPS. Current hosted provider doesn’t offer on-demand scaling solution .

SQL Server Recursion / CTE

Posted on June 30, 2016 by

Raju Venkataraman Posted in AWS RDS, SQL Server — No Comments ↓

Simple Employee data hierarchy to illustrate recursion through traditional while loop as well as Common Table Expressions. ( CTE )

Category: LAMP

Stored Procedure Error Logging

Install and Run SQL Server Docker Container on Mac

Bulk Load Data Files in S3 Bucket into Aurora RDS

MYSQL / AURORA Database Troubleshooting

Memory-Optimized Tables

Brute force attack on SQL Server

SQL Injection attack on Website hosted on EC2 Machine

Install SQL Server Docker Container on Linux

AWS Data Pipeline & Load S3 File Into MySQL

AWS Regions and Availability Zones

Restrict S3 bucket Access to VPC

How to become DBA favorite Developer

AWS SLA Summary

AWS RDS First Touch Penalty

Useful Free Tools and Books

AWS RDS API Reference

Remote Host Identification SSH Error

AWS SQL Server RDS Native Backup Restore

Launching AWS SQL Server RDS

SQL Server Recursion / CTE

Recent Posts

Categories

Recent Posts

Archives