PowerShell

Prashanth Jayaram

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

May 22, 2018 by

This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

  • Introduction
  • Technical specifications
  • How to load SQL Server modules on Windows machine
  • Security – Credential Management
  • The objectives of Backup and Restore
  • Library Linkage
  • How SQL Server 2017 backup feature is used on Linux
  • And more …
Read more »
Timothy Smith

Reading file data with PowerShell

May 10, 2018 by

We have some custom files that we receive from different providers and for these situations we are unable to use standard ETL programs without any customization. Since we’re expanding our ability to read these custom files with .NET, we’re looking for efficient ways to read files with PowerShell that we can use in SQL Server Job Agents, Windows Task Schedulers, or with our custom program, which can execute PowerShell scripts. We have many tools for parsing data and wanted to know efficient ways of reading the data for parsing, along with getting specific lines of data from files by number, or by the first or last line of the file. For reading files efficiently, what are some functions or libraries we can use?

Read more »
Shawn Melton

Learning PowerShell and SQL Server – Introduction

April 23, 2018 by

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

Read more »
Jefferson Elias

How to create charts from SQL Server data using PowerShell

February 8, 2018 by

Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Context

In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell.

Now, it’s time to use what we’ve learned so far.

Read more »
Craig Porteous

Migrating SSRS content with PowerShell

January 29, 2018 by

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

Read more »
Prashanth Jayaram

6 methods to write PowerShell output to a SQL Server table

December 25, 2017 by

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

Read more »
Timothy Smith

How to return data use, index compression, and row information with PowerShell

December 6, 2017 by

Background

We recently inherited a database environment where we’re facing significant data growth with limits on the sizes we can allow our databases to grow. Since we maintain multiple development, QA and production environments and these environments must be sized appropriately. We set a few standards about tables that exceed certain sizes – rows, data or both, or have certain growth patterns and our standards force compression at thresholds we set for our different environments (including using page, row, or clustered columnstore index compression) We’re looking for how we can get information about data and compression in tables and options we have so that we can quickly determine candidates that don’t currently match our best practices design we’ve set for our environments.

Read more »
Prashanth Jayaram

An overview of Python vs PowerShell for SQL Server Database Administration

November 2, 2017 by

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

Read more »
Prashanth Jayaram

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

Read more »
Craig Porteous

How to connect to (and query) Power BI and Azure using PowerShell

September 13, 2017 by
Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Read more »
Prashanth Jayaram

How to Capture Database(s) usage stats using sp_spaceused and PowerShell

September 11, 2017 by

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

Read more »
Prashanth Jayaram

Multi-server Script to Find Orphaned Data Files using PowerShell

August 23, 2017 by

Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.

Read more »
Sifiso W. Ndlovu

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

January 23, 2017 by

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.

Read more »

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

January 12, 2017 by

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.

Read more »
Aamir Syed

PowerShell: Get a daily database status email

November 22, 2016 by

The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.

Read more »