Category: AWS

SQL Server Recursion / CTE

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

AWS RDS vs Azure vs Managed Services for SQL Server

We are evaluating pros and cons of different hosting solutions for SQL Server which best suits our business needs.
Our plan is to evaluate AWS SQL Server RDS, Azure RDS , Managed solutions from hosting provider.
Evaluate each option in these categories.
1. Performance and Reliability
2. Ability to scale up during peak loads
3. Cost ( Based on Network , Storage, Memory and CPU )
4. Operations Efficiency
5. Compliance

SQL Server Internal Versions

SQL Server Internal Versions SQL Server Versions Database Internal Versions and Compatibility Levels Useful Query SELECT SERVERPROPERTY(‘ServerName’) ‘ServerName’ , @@VERSION ‘@@version’ , SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) ‘ComputerNamePhysicalNetBIOS’ , SERVERPROPERTY(‘MachineName’) ‘MachineName’ , SERVERPROPERTY(‘InstanceName’) ‘InstanceName’ , SERVERPROPERTY(‘IsClustered’) ‘IsClustered’ , SERVERPROPERTY(‘BuildClrVersion’) ‘BuildClrVersion’ , SERVERPROPERTY(‘Collation’) ‘Collation’ ,

Remove Procedure Cache and Reset Wait Stats

Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance DBCC FREEPROCCACHE; Flush the cache and suppress the regular completion message DBCC FREEPROCCACHE WITH NO_INFOMSGS; Remove all elements from the

AWS Solution Architect Preparation

AWS Solution Architect  Reference Materials Load Balancing : Elastic Load Balancing automatically distributes incoming application traffic across multiple Amazon EC2 instances. It enables you to achieve greater levels of fault tolerance in your applications, seamlessly providing the required amount of

AWS VPC Primer

AWS VPC : VPC are tied a specific Region. You can’t have your VPC span across multiple Regions. Security Groups: Instant Level Security NACL : Subnet Level Access Control VPC-VPC: Throgh VPC Peering CIDR : /16 – First 16 Bits

AWS AURORA RDS – Loading Billion Rows

We do online assessment and reporting for K-12 Education Industry. ( ). We had a need to load 3 through K-12 students historical test reports for entire state for past 10 + years , which ended up being close to billion rows for historical data.

Amazon reInvent 2015 Las Vegas

Highlights: Data Migration Tool – We use aurora and data loading is little more than  walk in the park. Hope this solves our problem. QuickSight – Explore this product for our needs RedShift – See how it can cutdown our Data

Importing and Exporting Data From a MySQL DB Instance

Importing and Exporting Data into Amazon RDS We had a business need to load ~ Billion rows of Data into MySQL or Aurora Database. These are the information I gathered on optimizing load process. Load Data Infile Link to MySQL

Amazon RDS Information

Aurora RDS: AWS Released encryption for Aurora Instances. Encryption is not available as of now.  Amazon RDS Encryption Links Encrypting Amazon RDS Resources You can encrypt your Amazon RDS instances and snapshots at rest by enabling the encryption option for

MySQL Ramp Up

MYSQL: Add Column to Existing Table and Make it Auto Increment and Add Primary Key Constraint Link : Alter Table and Add Column ALTER TABLE StudentPortalData ADD StudentPortalDataID INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (StudentPortalDataID); Query Plan and

MERGE Statement and Deadlocks

MERGE Statement and Deadlocks: We have MERGE SQL Statement to satisfy our functional needs. Instead of writing separate Insert / Update (UPSERT) Statement we decided to use MERGE Statement because of its simplicity in syntax. MERGE test.Target AS t USING(SELECT

Attaching Volume to EC2 Instance

Attaching Volume to EC2 Instance  To list the partitions  $ cat /proc/partitions major minor  #blocks  name  202        1   52428800 xvda1 Create a Volume in EC2 Instance and attach it to the instance. $ cat /proc/partitions major

SQL Server – Read Errorlog

Enable Audit log to capture successful and failed Logins — To Enable Failed and Successful Logins — Need to Restart the Server for this to effect EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’AuditLevel’, REG_DWORD, 3; GO — Query for Successful Logins EXEC

MySQL and phpMyAdmin Setup

MySQL and phpMyAdmin Setup sudo yum install mysql sudo yum install mysql-server sudo yum install mysql-devel sudo chgrp -R mysql /var/lib/mysql sudo chmod -R 770 /var/lib/mysql sudo service mysqld start sudo yum install phpmyadmin Errors: # tail -f error.log 2014/03/06

Install S3CMD tools on EC2 Instance.

Install S3CMD tools on EC2 Instance. SSH Into your EC2 Box and you run the following command to install S3CMD. $ sudo yum install s3cmd Loaded plugins: priorities, update-motd, upgrade-helper Bad id for repo: datastax , byte = 8 amzn-main/latest