Integration Services Performance Best Practices – Data Flow Optimization

January 26, 2017 by Koen Verbeeck

In this article, we’ll present a couple of common best practices regarding the performance of Integration Services (SSIS) packages. These are general guidelines for package design and development which guarantee a good performance in most use cases. However, there are always exceptional cases and outliers. The mantra of “measure twice, cut once” also applies here. Thoroughly test any changes to your packages to conclude that a change made a positive effect. This means not only running the in the designer, but also on the server. Also, this article is not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best practices that will guide you through the most common development patterns.

Are SQL Server database triggers evil?

January 25, 2017 by Daniel Calbimonte

Introduction

There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers to determine advantages vs disadvantages of each approach.

Using Memory-Optimized Tables to Replace SQL Temp Tables and Table Variables

January 24, 2017 by Ahmad Yaseen

TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.

Connecting PowerShell to SQL Server – Using a Different Account

January 24, 2017 by Shawn Melton

Introduction

In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication.

How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell

January 23, 2017 by Sifiso Ndlovu

In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.

Best Practices for Configuring Newly Installed SQL Server Instances

January 20, 2017 by Musab Umair

Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects in mind many of us have to go through the configuration of the newly installed or pre-installed but un-configured instances. Configuring SQL Server newly instances are not an easy task and a DBA might want to configure an Instance in many ways.

SQL Server Job Performance – Reporting

January 19, 2017 by Ed Pollack

Description

Once collected, job performance metrics can be used for a variety of reporting needs, from locating jobs that are not performing well to finding optimal release windows, scheduling maintenance, or trending over time. These techniques allow us to maintain insight into parts of SQL Server that are often not monitored enough and prevent job-related emergencies before they become emergencies.

Using sp_server_diagnostics

January 19, 2017 by David Alcock

Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.

SQL Server Job Performance – Tracking

January 19, 2017 by Ed Pollack

Description

Keeping track of our SQL Server Agent jobs is a very effective way to control scheduling, failures, and understand when undesired conditions may be manifesting themselves. This is a brief journey towards a solution that provides us far greater insight into our jobs, schedules, and executions than we can glean from SQL Server by default.

Scaling out Reporting Services & changes in SQL Server 2016

January 18, 2017 by Craig Porteous

Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL 2016 with next to no guidance from Microsoft on the changes.

How to secure your passwords with PowerShell

January 18, 2017 by Shawn Melton

Introduction

Do you have processes or scripts that require you to provide a password? Against the desires of your security officer, do you have to save those passwords in plain text, in your scripts? PowerShell offers a way that you can store a password or prompt the user for the information. You can then utilize that information to build what is known as a PSCredential. The majority of commands for PowerShell that support remote connections to servers (WMI, CIM, Invoke-Command, etc.), offer the ability to pass in a credential. While some only need the password, some need the full object to authenticate a user. This object in PowerShell can be made a few different ways based on your needs. I will go over a few options that are commonly used, but first lets discuss what makes up a PSCredential.

How to install the SQL Server Utility Control Point and configure multiple instances for monitoring

January 16, 2017 by Musab Umair

As we progress in our career as a SQL Server Database Administrators we encounter situations where managing and monitoring a large number of SQL Servers is the daily job requirement. Monitoring SQL Server is a very vast and detailed area of study. Out of all the monitoring the very basis and critical monitoring in the Resource Utilization Monitoring for SQL Server.

Deploying Packages to SQL Server Integration Services Catalog (SSISDB)

January 16, 2017 by Thomas LeBlanc

Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a previous deployment. On top of these features, internal reports with a dashboard help when debugging errors or examining performance over time.

Understanding SQL Server database static data and how it fits into Database lifecycle management

January 13, 2017 by Marko Radakovic

What is static data

Static data (aka Code, Lookup, List or Reference data), in the context of a relational database management system, is generally data that represents fixed data, that generally doesn’t change, or if it does, changes infrequently, such as abbreviations for US states for example e.g. ME, MA, NC. This data is typically referenced, extensively, by transactional type data. For example, an customer table would have references to static table for City name, State or province, Country, Payment terms e.g. NET 30 etc.

Automate Delivery of SQL Server Production Data Environments Using Containers

January 12, 2017 by Paul Stanton

There has been a lot of buzz about Docker and containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source in March of 2016. Full disclosure, I am a co-founder at WinDocks.

In this article we’ll take a look at the basics of containers, and the most popular use by SQL Server DBAs.

How to download and install the SQL Server Database Experimentation Assistant (DEA)

January 12, 2017 by Vitor Montalvão

Introduction

The Database Experimentation Assistant (DEA) is a new Experimental solution provided by Microsoft used to help upgrading databases from older SQL Server versions (MSSQL 2005 or higher versions) to the more recent ones (MSSQL 2012 or higher versions). It accomplished this based in a given workload that is previously captured from the source database and later replayed on the target SQL Server instances for comparison.

SQL Server 2016 Always On Availability Group with Direct Seeding

January 12, 2017 by Ahmad Yaseen

SQL Server Always On Availability Groups are an enterprise-level high-availability and disaster-recovery feature introduced the first time in SQL Server 2012as an alternative to database mirroring. A set of user databases that fail over together forms the availability group. These availability databases are hosted by the availability replicas and can be readable- writable at the primary replica and up to eight sets of secondary replica databases that can be configured to be read-only databases. The availability groups fail over due to the availability replica’s level issues and not the ones caused due to database level issues such as data loss or database corruption.

Parameterizing Database Connection in SQL Server Integration Services

January 12, 2017 by Thomas LeBlanc

SQL Server Integration Services (SSIS) has been growing into an enterprise solution over the last three releases. Variables were a step in that direction, but with Project deployments to the SSIS Catalog in SQL Server 2012 Microsoft has stepped it up, even more. Project parameters have become the go to solution for changing variable values in packages at run time. This article is going to dive into an example that parameterizes the Server Name and Initial Catalog (database name) in a connection string for packages in a project.

Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell

January 12, 2017 by Musab Umair

As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.

10 SSMS Tips and Tricks to boost your Productivity

January 6, 2017 by Samir Behara

I have been using SSMS for a long time now, and over the period of time I have realized that it’s a very powerful IDE – however, there are lots of hidden features which are less known to lots of developers. In this article, I will be sharing some beneficial Tips and Tricks which will give you an extra edge in your day to day work, and in the process take your productivity to the next level.

Native JSON Support in SQL Server 2016

January 6, 2017 by Manjuke Fernando

With the introduction of SQL Server 2016, Microsoft introduced many new features which had taken SQL Server another step forward and they made sure that it stands in front among many major Relational Database Systems.

One such feature which has been lacking but desperately required was the native support towards JSON.

Report Subscription Changes in SQL Server Reporting Services 2016

December 30, 2016 by Sifiso Ndlovu

What makes SQL Server 2016 one of my favorite SQL Server release since the release of SQL Server 2012 is the increased number of exciting new features that have been introduced. Whilst my article Top 5 New SQL Server 2016 DMVs for DBAs reviewed some of these new features albeit from a DBA point of view, in this article I continue to review SQL Server 2016 but from the perspective of an SSRS administrator in relation to the changes in report subscriptions.

Different Ways to set the Max Degree of Parallelism in SQL Server

December 29, 2016 by Ahmad Yaseen

In this article, we will go through the different ways to set the Max Degree of Parallelism option. Before doing that, it is helpful to understand the concept of parallelism, how it works and what the MSXDOP is.

SQL Server Index vs Statistics a consultants woes…or rants

December 29, 2016 by Kimberly Killian

As a DBA, I am often asked why is something performing slow, what and why statistics need to be updated or what will cause them to be “off”. My initial question to clients when they pose these questions to me is what changed on your end? Did the data change significantly and did the rebuild or reorganize index job run? Before I get into the answers to these questions from my clients, let me give you some background. So, just to clarify, for most of my clients, I work as a remote part-time DBA, that being said, I manage their database from every aspect including setting up servers, backups/restore, troubleshooting, managing their index’s, etc. and again remotely. So normally, I have setup jobs that will manage their index’s ranging from a weekly rebuild or even sometimes I use one that I’ve designed that makes a choice to either rebuild or reorganize an index based on fragmentation level. The “general rule of thumb” is reorganizing the index for fragmentation from 5% to 29% and rebuild when 30% plus. Those are pretty standard numbers I did not make them up.

$C:\Users\wshaw\AppData\Local\Temp\SNAGHTML519cfb8.PNG$

Introduction of Visual Studio Code for DBAs

December 29, 2016 by Shawn Melton

Introduction

Visual Studio Code (Code), have you heard of this product yet? This is an open-source, cross-platform and extremely light weight code editor from Microsoft. You may see some folks explain this editor as the little brother to Visual Studio Community Edition (VS Community), but it is more compared to editors like Atom, Sublime Text or even Notepad++. It is not something you can use to compile program code, so it is for the less complex coding projects. I utilize Code as my editor of choice now with PowerShell, and even T-SQL at times. In this article, I wanted to walk you through using Code and note some specific extensions I use for PowerShell and SQL Server.