Shawn Melton

Author
Recent Posts

Shawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada.

After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.

He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).

View all posts by Shawn Melton

Learning PowerShell and SQL Server – Introduction

April 23, 2018 by Shawn Melton

Introduction

This article is the first step among many that I hope will help give you a foundation of knowledge to get started utilizing PowerShell. The focus in these steps will be specific to using PowerShell with SQL Server, but I have to cover some of the basic things. Which once you grasp the basics of PowerShell and using it, in a general sense, you set yourself up for easily learning how to use it with other products.

In this article I’m going to touch on the following items:

History Lesson (short reference for a timeline on releases)
Windows PowerShell vs PowerShell Core
SQL Server and PowerShell (as it is today)
Terminology (some terms that help to understand)
PowerShell Editors

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 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.

$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.

SSIS and PowerShell – Execute process task

April 11, 2016 by Shawn Melton

Introduction

SQL Server Integration Services (SSIS) and PowerShell (PS) together offer a plethora of opportunities, and some shortcuts when having to import, export, or at times moving data. I have come across packages that contain a Script Task with lines and lines of C# code that, done with PowerShell, could make maintaining that package much easier. Overall, the most common thing I see Script Task doing is accessing the file system or doing some manipulation on a file. One thing I hope that picks up speed in the BI world of SSIS is utilizing PowerShell for these type of actions. This is not to say one is better than the other as you should pick what is best in your eyes, but when I can do operations against the file system with a one-liner in PowerShell it is just easier to maintain that in the package. In this article I will go over how you can use the most common task utilized for executing PowerShell code in an SSIS package: Execute Process Task.

Perform volume maintenance tasks security policy

April 4, 2016 by Shawn Melton

Introduction

You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.

Analyzing SQL Server backups

March 8, 2016 by Shawn Melton

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

Connecting PowerShell to SQL Server

January 13, 2016 by Shawn Melton

Introduction

PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.

There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.