Blog Posts - KillaDBA

SQL Server on Linux!

1/30/2018

I was signed up to present at my local PASS chapter meeting (http://atlantamdf.com/) on January 8th 2018. The only problem with this is that my alma mater, The University of Georgia, was playing in the College Football National Championship game…. And I had tickets! Needless to say, I didn’t present… but I did create a video that was played at the meeting. Here is the video that was played that is a walk-through demo of me installing SQL Server 2017 on Red Hat Linux 7.4 on a virtual box VM:

PASS Summit 2017

1/16/2018

Double wow! I attended PASS Summit in Seattle, Washington this past fall and I had a great experience! I would suggest that anyone passionate about data should definitely attend this event. PASS Summit is the world’s largest conference in the world for SQL Server professionals. Find out more about SQL Server here:
http://www.pass.org/

I met so many cool people with the same interests in data and I also got to perform in front of my fellow PASS attendees!

You can check out my PASS summit recap in this video below! I only had 5 seconds to explain each picture but I believe I still did a good job in describing my experience:

Big Data Lyrics and sound cloud link

9/27/2017

Big Data

{Verse 1}

Never in the history of humans have we ever seen
a day like this when data is abundant to infinity
We’ve always backed up data like on walls with hieroglyphics
But now we have a thousand ways to reclaim what goes missing
Computers –man- Technology, servers, in the cloud lies
Millions I mean Billions perhaps trillions of these hard drives.
We live in times where we can finally process,
All this data is a mess - Without tools so we can use it
Mass data - that can tell us what is what –
Insight is what we need - and Big Data is what’s up
The who, the what, the when -when we gotta know the why.
Big data in the answer -because Data Doesn’t Lie, <ha ha!>
We have abilities to structure, make it clean
then decide how-to extract - Now I’m talking ‘bout the V’s
Volume-Variety-Velocity - that’s 3
But we can’t forget Veracity or Variability – Big Data.

{Verse 2}

So now were living in a time – where he have a lot of data
But it doesn’t mean a hoot – if we don’t know how to leverage.
Our society will benefit – analysis - efficiency
I am a data scientist – it’s my way of philanthropy.
Money we could save, give away or just make
Is but one way that Big Data could assist – so many ways
Shout out Apache software – Yes I’m talking ‘bout Hadoop
I’m using Polybase from Microsoft to get the scoop
The news, the clues, from any storage system
So never ever purge – we have clever algorithms
that make it so that different platforms talk to each other
The different data sets - and complex architecture.
So analyze the trends - we are all made of atoms
On a sub-atomic level - just like atoms we have patterns
Big data is the future where the data tells the future
Mass data into structure making decisions to deliver – Big Data

Killa DBA song medley

6/12/2017

Here is a video that shows snippets of Killa DBA in Redmond, WA performing at a SQL Saturday event (Microsoft building 92)

My Performance at Microsoft

5/26/2017

Wow! SQL Saturday in Redmond, WA on 4/15/2017 was a success! My wife and I really enjoyed Seattle and our tour of the Microsoft campus in Redmond. the people were great, the presenters were very informative, the food was awesome and Killa DBA had a "killer" performance!

Here is a snippet of the performance:

Here are a few pictures from my trip to Microsoft (Bldg 92)

IT GIRL

4/9/2017

I really enjoy writing songs. Especially when they come to me easily. Which isn't always the case. This song was fun to write and record because it all flowed with ease. Although the song is fictional, there are many snap shots of my experiences as an employee and DBA in an IT department. The lyrics below are preserved exactly how I write them in my word or notes document. Please see song/video below.

IT Girl

{Chorus}
I’m in love with an IT girl (Love - Love)
I’m in love with an IT girl (Love - Love)
I’m in love with an IT girl (Love - Love)
I’m in love with an IT – IT Girl

{Verse 1}
So I’m a DBA and databases are my world
Until I met a girl - She is an app developer.
See I knew from the start - this programmer stole my heart
Used to favor VB – now I’m digging C – sharp.
I love her source code, so efficient – good code.
She even flipped the script – converted to Java script.
My head is in the cloud, like her applications are
Instead of Diamonds and Pearls, she likes Python and Perl.

{Pre-Hook}
In the studio – I’m talking BIDs -that is
With my SSIS and her IP address - we fell in love.
From QA to Test all-the-way-to-Prod.
Yeah - I’m Ok with her – she’s my de-ve-lop-er.

{Chorus}

{Verse 2}
So as her DBA – I did suggest an upgrade
SQL Server 16 – not that other database
She agreed – got approved -from the upper management
Databases mi-grated – it was money well spent.
On deployment night – RFC looked tight
But to add to this story – there was not enough storage.
I’m Killa DBA – and that’s good for us both
‘cause before we got started - I enabled auto growth!

{Pre-Hook}
In the studio – I’m talking BIDs -that is
With my SSIS and her IP address - we fell in love.
From QA to Test all-the-way-to-Prod.
Yeah - I’m Ok with her – she’s my de-ve-lop-er.

{Chorus}

{Rap}
<pause> Her .NET Framework –yeah- is all that
From mobile –to- desktops -all the way to Web apps.
I like the way she moves – in and out of code-it’s
like her name was -Trinity Straight out of Matrix Reloaded.
Huh – Better yet – she’s an architect.
You can tell by her Data-flow analysis
She bang slash user slash local bin Perl
(#!/usr/local/bin/perl)
I love with her brain – my IT Girl.

Backup and Recovery

3/27/2017

Instead of listing interesting facts about the importance of the backup and recovery process...
I have decided to list the lyrics of my song instead.
Please see link below for song.

Backup and Recovery (Bits & Bytes)

{Chorus}
I’m putting your bits where your bytes should be…

{Verse 1}
Ooooo back it up – easy back it up – Killa DBA-let me explain to you what’s up.
The backup and restore are really crucial to survival
Minimize the data loss. Protection is so vital.

Corruption, disasters, disgruntled employees.
I’m gonna cover some Backup and restore strategies.
Never put the data file and backup on the same device.
Just incase the drive fails also I/O will prevail.

So let’s consider- will the database get bigger?
What’s the nature of the data? Backup now? Backup later?
How much space will it take? Should I schedule for tomorrow?
Is this a simple, bulk-logged or a full recovery model?

Make sure you test it. The data is invested
So recovery tests regularly - is suggested.
Maintenance plans help you really understand.
Scheduled jobs in place – to reclaim more space – so Lets go!

{Chorus}
I’m putting your bits where your bytes should be…

{Verse 2}

A full backup gets the data, t-logs, everything
A differential only gets extents that have changed.
The file backup will allow you to pick out the data file.
Filegroups are the same Backup the groups by name.

Partial backups – with the groups and files you pick and choose.
With Copy-only backups there are no logs to include.
Mirror backups will ensure multiple copies.
Transaction logs for the point in time recovery.

Restore it with the GUI or with T-SQL.
Restore it like a boss by cracking open PowerShell.
From which media type? From the file or Device?
From a tape? URL? Azure storage container?

The WITH option: REPLACE, KEEP_REPLICATION,
RESTRICTED USER to keep out other thread connections
RECOVERY, NO-RECOVERY or STANDBY
Please master this -Or you can kiss your data bye bye!

{Chorus}
I’m putting your bits where your bytes should be…

References:

https://technet.microsoft.com/en-us/library/ms175477(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

Types of Indexes (or Indices)

1/31/2017

Types of indexes (Types of Indices) see Video below!

DBAs are constantly facing challenges with making queries run faster. One of the top tuning procedures is to add an index to the table or tables in order to increase the optimizer's accessibility to the requested data.
*Note - The first three definitions are excerpts from lyrics in my index song. The remaining definitions come from the referenced link below. Again, my intentions are to inspire listeners to learn through my songs. For example - The different types of indexes in SQL Server 2016 are now ingrained in my memory!

Unique – “A unique index will contain – no duplicates – no row is the same – according to the key that is… Every row in the table or view – it’s gotta be new!
Uniqueness can be a property of clustered and nonclustered indices.”
Clustered – “..and speaking of Clustered – it is implemented as a B-tree index structure. There can only be one per table – sorted in order… and I don’t mean sort of. So without it all you have is a heap – with data pages that aren’t even linked…
Non-Clustered – Not we got a locator (woo!) – pointing to the data row’s key value. It can be in any order – have as many as you wanna - On a table or a view - or a clustered index too.”
Hash - With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.
Filtered - An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Memory-optimized non-clustered indexes - For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns
Included columns - A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Computed columns - An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
Spatial - A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
XML - A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Full-text - A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
Columnstore - An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

ONLINE REFERENCE: https://msdn.microsoft.com/en-us/library/ms175049.aspx

Data Protection

12/16/2016

Security in the world of data has become very important. Computer viruses, Identity theft, hacked websites and more have put “data security” at the forefront of this new era of "having lots of data and storage." This is the first time in known human history that we have been able to store as much data as we have! Even at this very moment that you are reading this, data storage technology is improving at radical speeds. Not only are we able to store more data than we could yesterday, but we are now able to retain, save and archive this data with better safety precautions. Increased Disaster recovery plans over time have given us the abilities to have more insight so that data will tell us what we have done wrong in the past as well as how we can improve our future. Companies are now realizing that data is what acquires new customers, retains customers, generates money, etc… Bottom line - data doesn’t lie! I can go on and on about the importance of protecting data. Here are a few security items mentioned in my new song:

Physical protection – Where does your data live? Is the server in a secure location? Who has access to it?
Disaster Recovery – Can your infrastructure handle a disaster? Having a cloned server at a different site that can assist with keeping the database going just in case.
Firewall – Network security that can monitor traffic in and out of servers.
Update fixes – It is important to install updates to address bugs and security issued from time to time (my song says always… but … it depends)
Disable unused data protocol – for example… if you are using TCP/IP – disable shared memory and named pipes.
Monitoring – alerts against the database can help out tremendously. Long queries, failed jobs, storage issues, etc…
Transparent Data Encryption – Encrypts data at rest: Data files, Logs and backups.
Xp_cmdshell – Allows admionitrators to use command shell through SSMS or t-sql
Disable SA – This is a default account. Knowing that there is a login called SA gives a hacker 50% closer to his goal!
Passwords – change your SQL login or Windows authenticated password from time to time.
SQL Browser – This service can really come in handy where there are multiple instances of SQL Server running on a server. It should be disabled on single instance servers.
SQL Injection – Within application code, nasty sql statements can be scripted within the entry fields to do unimaginable things within a database. In my song, I make a funny about the developer handling this issue, but in fact, having the right database permissions can also help to eliminate SQL injection.
Always encrypted – Encrypts sensitive data and only those with key can see data.
TLS – or Transport Layer Security is a protocol that provides security over a network between two applications.
Row Level Security – This feature allows the admin to restrict a user or role’s access to the row level only.
DDM – or Dynamic Data Masking limits the amount of sensitive data seen by user. For example: having the last 4 digits of a social security number or bank account card shown as opposed to having the complete number exposed.
Data – My last statement or lyric in this song is a theory that puts in perspective the importance of data. It may come across as crude, but I mention that the data is more important than a company’s hardware, real estate and/or even people

A FEW REFERENCE Sites:
https://www.mssqltips.com/sqlservertip/3159/sql-server-security-checklist/
http://searchsqlserver.techtarget.com/tip/Microsoft-SQL-Server-security-best-practices-checklist/

Wow! Positive responses!

12/15/2016

Wow! I really do appreciate all of the positive responses I have received regarding my SQL Server 2016 song! This only confirms my belief that I should continue with my journey. One of my goals is to complete enough songs so that I can create an album. My hope - is that these songs help others as much as they have helped me with my studies and retaining knowledge about SQL Server.

If you have any suggestions, comments or criticisms ... please don't hesitate to let me know!

Thanks again everyone! I'll keep it going... next song... "Data Protection"

Peace,

KillaDBA