The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.
One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.
In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.
To back up your database to Azure BLOB storage, first you need to set up the BLOB storage in your Azure account. You will also need to create a container first.
You can back up the database to Azure by using either PowerShell or T-SQL. Continue reading →
Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.
The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.
This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.
A couple of the most noticeable additions are OneGet and PowerShellGet. If you have experience in *nix, these two new features are very similar to apt-get or yum or RPM. It allows you to connect to web-based package repositories, and install them.
Microsoft Second Shot is back for 2015! If you’re planning to upgrade your Microsoft certifications (MCSE, MCSA, MCSD), you might want to take advantage of this insurance. You will be eligible for a free retake (if you don’t pass the first time) if you take your exam between January 5, 2015 and May 31, 2015.
Here is a quote of the description of the book from the Manning website:
PowerShell Deep Dives is a trove of essential techniques and practical guidance. It is rich with insights from experts who won them through years of experience. The book’s 28 chapters, grouped in four parts (Administration, Scripting, Development, and Platforms), were hand-picked by four section editors: Jeffery Hicks, Richard Siddaway, OisÃn Grehan, and Aleksandar Nikolić.
Whether you’re just getting started with PowerShell or you already use it daily, you’ll find yourself returning to this book over and over.
So 2012 has been a really hectic year for me so far. But… my book is out!
About This Book
Provides over a hundred practical recipes that utilize PowerShell to automate, integrate and simplify SQL Server tasks
Offers easy to follow, step-by-step guide to getting the most out of SQL Server and PowerShell
Covers numerous guidelines, tips, and explanations on how and when to use PowerShell cmdlets, WMI, SMO, .NET classes or other components
Builds a strong foundation that gets you comfortable using PowerShell with SQL Server–empowering you to create more complex scripts that you need in your day-to-day job
Who This Book Is For
This book is written for the SQL Server database professional (DBA, developer, BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks. A little bit of scripting background is helpful, but not necessary.
I’m trying to get some data I can play around with Tableau, fortunately information for Canadian climate data is available in CSV and XML from http://www.climate.weatheroffice.gc.ca/. I thought I’d go fetch the Vancouver daily weather data since 1953 using PowerShell.
Here’s a few PowerShell snippets for anyone interested. I haven’t cleaned these up yet, but should be pretty functional.