Category: LAMP

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

LAMP Stack Setup on El Capitan

Very Good article on Setting up LAMP Stack on El Capitan: On OS X Yosemite/El Capitan to start/stop/restart MySQL post 5.7 from the command line: sudo launchctl load -F /Library/LaunchDaemons/ sudo launchctl unload -F /Library/LaunchDaemons/

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

Apache Drill Setup and Usuage

Apache Drill Setup and Usuage: Install and Configure Apache Drill sqlline> !connect “jdbc:drill:zk=local” Enter username for jdbc:drill:zk=local: admin Enter password for jdbc:drill:zk=local: ***** http://localhost:8047/ SELECT derivedtable.ItemID ,AVG( CAST (derivedtable.TimeSpent AS INTEGER)) AvgTimeSpent FROM ( SELECT rawresponsetime.Time10.`id` ItemID ,rawresponsetime.Time10.`value` `TimeSpent`

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 or MySQL for our Business Needs?

Business Need: Currently we are in the cross-roads of growing from small-mid sized company to large scale K-12 online Test Delivery and reporting Solution provider. We primarily operate on K-12 Summative Market and its absolutely critical for us  to provide 100 % accuracy

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

NGINX Protect Domain through Password

NGINX Protect Domain through Password It’s very easy to place a simple htpasswd-based authentication system on a domain served by nginx. To do this, you’ll want your server block to look like this: server { listen 80; server_name; root

NGINX and APACHE URL Redirects

NGINX APACHE URL Redirects Redirect all the requests to https server { listen 80; server_name; rewrite ^ https://$server_name$request_uri? permanent; } Redirect Specific Pages rewrite /humor /humor/humor.php; rewrite /events/search /Events/events_search.php; rewrite /events/summary /Events/events_summary.php; rewrite /photos /photos/index.php; rewrite /help /Technology/blog.php?ID=344; rewrite

Change HostName in Linux Machines

Change HostName in Linux Machines Edit  /etc/hostname , make the name change, save the file. You should also make the same changes in /etc/hosts  file Run sudo /etc/init.d/hostname restart or sudo service hostname restart /etc/hostname /etc/hosts sudo /etc/init.d/hostname restart sudo