Year: 2015

SQL Server Mirroring

SQL Server Mirroring Related Queries: How do I swap Primary and Secondary on Mirroring Databases ? When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks. To swap back the Primary/Secondary , connect to the

Highly Scalable Fault Tolerant Scalable Systems Considertions

Highly Scalable Fault Tolerant Scalable Systems Considertions: Dr Tanenbaum on Fault Tolerant Operating Systems. Murphys Law:,_Jr. If there’s more than one possible outcome of a job or tasks and one of those outcome will result in disaster

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

Random Errors

Putty Fatal Error: Network Error: Software caused connection to abort. PuTTY Fatal Error I worked with CentOS servers from Windows PCs, and I had the same problem with PuTTY. A session didn’t last more than 1-5 minutes. I tried to

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

SQL Server Hardware Selection

SQL Server Hardware Selection , licensing cost and BenchMark Tools.

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`

TempDB Contention

TempDB contention: TempDB plays very crucial in well performing SQL Server engine. These are few simple design decisions , which helps to avoid the performance bottlenecks in the long run. As a general rule, if the number of logical processors is

Concurrency and Storage Needs

Concurrency and Storage Needs : Requirements: We operate on K-12 Online Summative Market. Our Needs are: When Kids log-in to take the test, they should be able to login without any issues. Should be able to store their responses and

How to handle Perennial statement of “Our Database is SLOW”?

How to handle Perennial statement of  “Our Database is SLOW”? I have been involved with Database and Product Development for a decade in different Verticals. I have heard this statement more than I could count “Database is Slow”,”Database is the

Snapshot Isolation in SQL Server

Snapshot Isolation in SQL Server Wiki Article on SnapShot Isolation : Issues with SnapShot Isolation: Kimberly Tripp Video on Isolation Levels : Kendra Little on SnapShot Isolatioin : Microsoft Link: SQL Team Link : Idera Short article

SQL Server Locking

Microsoft Link : Locks held by Current Processes: SQL Server 2008 R2 SQL Team Primer on Locks : Kalen Delany Crisp explanation on Update Locks: UPDATE locks are not really a separate kind of lock, but

Random GUID or Sequential GUID

Random GUID or Sequential GUID SQL Server has two in-built functions for creating uniqueidentifier. NEWID() NEWSEQUENTIALID() New NEWSEQUENTIALID would help to reduce page splits and fragmentation because of sequential nature of the values returned from this function. NEWID() is truly

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP): Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster. Hold on for a sec..Its SQL Server. Common

SQL Handle and Plan Handle to Text – Quick Reference

SQL Handle to Text – Quick Reference DECLARE @pStartOffSet INT DECLARE @pEndOffSet INT DECLARE @pSQLHandle VARBINARY(64) SELECT @pSQLHandle = 0x030008001374fc0232620901c7a300000100000000000000 ,@pStartOffSet = 1788,@pEndOffSet = 3042 SELECT SUBSTRING(st.text, (@pStartOffSet/2)+1, ((CASE @pEndOffSet WHEN -1 THEN DATALENGTH(st.text) ELSE @pEndOffSet END – @pStartOffSet)/2) +

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