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…
Year: 2015
Highly Scalable Fault Tolerant Scalable Systems Considertions
Highly Scalable Fault Tolerant Scalable Systems Considertions: Dr Tanenbaum on Fault Tolerant Operating Systems. https://en.wikipedia.org/wiki/Andrew_S._Tanenbaum Murphys Law: https://en.wikipedia.org/wiki/Edward_A._Murphy,_Jr. https://en.wikipedia.org/wiki/Murphy%27s_law 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. ( http://emetric.net/ ). 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…
Apache Drill Setup and Usuage
Apache Drill Setup and Usuage: Install and Configure Apache Drill http://drill.apache.org/docs/installing-drill-on-windows/ 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 : http://en.wikipedia.org/wiki/Snapshot_isolation Issues with SnapShot Isolation: http://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level Kimberly Tripp Video on Isolation Levels : http://download.microsoft.com/download/6/7/9/679B8E59-A014-4D88-9449-701493F2F9FD/HDI-ITPro-TechNet-mp4video-MCM_11_SnapshotIsolationLecture(4).m4v Kendra Little on SnapShot Isolatioin : http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Microsoft Link: https://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/bb522682.aspx SQL Team Link : http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level Idera Short article…
SQL Server Locking
Microsoft Link : https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx Locks held by Current Processes: SQL Server 2008 R2 https://technet.microsoft.com/en-us/library/ms189497(v=sql.105).aspx SQL Team Primer on Locks : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server Kalen Delany Crisp explanation on Update Locks: http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx 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…
(No Ratings Yet)
Loading...
Top