Jason McKittrick's Blog

Author Archive for Jason McKittrick

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 02/25/2017. Data Collectors National Weather Service Data Collector  https://github.com/thesqlgeek/WeatherData 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/…

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…

AlwaysOn Availability Group Capacity Planning

There are a number of factors to consider when undertaking the capacity-planning phase for your AG environment. One such factor is the amount of resources each replica will require to provide appropriate performance for the system. To work this out, you need to take into account not only the normal activity that your environment will…

AlwaysOn 2014 Enhancements from AlwaysOn 2012

Recently one of my customer was looking for detailed information on SQL Server AlwaysOn 2014 Enhancements.  Below is a good list of product enhancements that I put together listing enhancements in SQL 2014 AlwaysOn from what was available in SQL 2012. If you are looking to install a new version of SQL to get AlwaysOn setup from…

SQL Server 2014 CTP 1 – Hekaton

Hekaton is the new SQL Server 2014 In-Memory database technology.  It is going to change the way we architect databases going forward. Decisions about which data or tables go into memory and which ones will function in the SQL Inter-Op mode.   Until we get a ton of detail here is a simple Hekaton ST-SQL Script…

SSIS Logging Framework

The need for a Logging Framework for SSIS packages to track and log activity within ETL (Extract, Transform, Load) processes. This blog outlines the database schema, stored procedures and implementation practices within SSIS packages to be utilized for the SSIS packages used for the data applications.  I will build and use a sample package load of Regions…