Jason McKittrick's Blog

SQL RDBMS

Understanding Fragmentation and its effects on SQL

Understanding Fragmentation and how it affects the performance of your database is critical.  Performing regular maintenance tasks that defragment and compact clustered and non-clustered indexes on tables and views. This will improve index-scanning performance. This should be run on weekly or fortnightly basis. Fragmentation can happen at several levels and if fragmentation is allowed to occur, overall…

SQL Backup Types and Backup Strategies

There are several types of backups to consider when planning a backup strategy. In this post I am going to talk about different backup types and talk through the strategies. The following list summarizes the database backup types: Full Database Backups – A full database backup backs up the whole database. This includes part of…

SQL Server Recovery Models

There are several means by which SQL Server databases can be recovered. The recovery model set for the individual database determines the types of recoveries that can be performed and the amount of work and space required to protect the data. To understand how database backup works, there must first be an understanding of the…

Table Column Widths

Just sharing a simple block of code to find the width of a row in a given table based on DDL used to create the table.   This is especially helpful if you are looking to take a table to In-Memory with a row size limitation of 8,000. Just used this for one of my customers…

Storage Subsystems Disks Types

This is the first Blog I am doing in a series on Storage and Disks.  It is important to understand the “Black Box”  so that the proper Architecture can be designed to maximize the value of the technology investment.   Disks Types The type of hard drive utilized in the host server or the storage array…

SSIS Package Configurations with SQL Encryption

This blog post outlines the implementation approach to use SSIS package configurations as a development and deployment best practice. I have written it as a step by step approach as requested by some of my customers for purposes of setting up and configuring Package Configurations for SSIS. Most companies are using Package configuration, however, are not encrypting…

Data Links

Over the years I have collected free Data Source links and assembled my favorites.  I have collected these from stumbling on them and some from colleagues. Enjoy! Updated 1/20/2016. World The World Bank http://www.worldbank.org/ Gapminder http://www.gapminder.org/data/ United Nations Datasets http://data.un.org/ International Monetary Fund http://www.imf.org/external/data.htm Open Spending https://openspending.org/ CIA World Factbook https://www.cia.gov/library/publications/the-world-factbook/ NOAA/NCEI weather and climate data http://www.ncdc.noaa.gov/ Government Data.gov…

SQL Server Row and Cell Level Security

With current regulations such as SOX, HIPAA, etc., protecting sensitive data is a must in the enterprise.  There is a published toolkit to implement Row Level Security (RLS) and Cell Level Security (CLS) using SQL Server Label Security Toolkit which is a free download from CodePlex http://sqlserverlst.codeplex.com/.  It makes use of a Security label in SQL…