You all know how important is to have the tool that can make our life easy do our system administration, and become a hero in our organization. Here’s a startup helper guide to get you going with some PowerShell and SQL Server tools.
What is available for automation!
For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)
Here are some important PowerShell Modules to use for SQL Server management scripting:
- *SQLServer – This module currently can be use on SQL Server 2017 and greater.
- *DBATools – This a community supported module that will work with SQL Server 2000 and greater.
- DBAReports – Supports for Windows SQL Server.
- DBCheck – Support for Windows SQL Server.
*Note: This module is coming popular in cross-platform systems (non-Windows)
All of the above module can be downloaded from the PowerShell Gallery from the PowerShell console using the Install-Module cmdlet.
Install-Module -Name SQLServer -Force -AllowClobber;
Now, when working with older versions of SQL Server (2008->2017), you will find the SQLPS module is loaded during the SQL Server installation.
Just remember, since SQL Server 2017, Microsoft has change the PowerShell SQLPS module to SQLServer module downloadable from the PowerShell Gallery. This module is not available in PowerShell Gallery, only available during the SQL Server installation.
When PowerShell SQL Server Module can’t provide a script?
It won’t hurt to install the SQL Server Management Objects (SMO) library in case you want to be creative and start building your own SQL PowerShell scripts. This library is already available cross-platform, meaning that it will work in Windows, Linux and MacOS environments.
In this case, you can install the SQL Server SMO library “Microsoft.SqlServer.SqlManagementObjects” from the PowerShell Console using the Install-Package cmdlet.
Install-Package -Name Microsoft.SqlServer.SqlManagementObjects -AllowPrereleaseVersions;
Wait! There is more
As you already know, to manage SQL Server in Windows environment, we use the SQL Server Management Studio. But, this
application won’t work cross-platform.
So, the cross-platform option available is Azure Data Studio (February edition):
Don’t forget to include for following extensions:
- whoisactive
- Admin Pack for SQL Server
- Server Reports
- SQL Server 2019 (Preview)
What about Python?
By now you should already know that Python has been around for many year as cross-platform interpreted object-oriented high-level language. And, its popularity keeps increasing.
I would recommend to take a look at the Anaconda Distribution, and specifically the one with the latest version of Python (v3.7).
Download Anaconda for data science platform:
This installation will include *All* Python packages available to build an application.
And, Python can interact with PowerShell too!
Ah finally Containers!
Yes! Containers has become popular and can’t be ignored. It can be use in both Windows, Linux and any cloud environments. Go ahead to learn how to work and manage Docker containers.
Docker site to Download the Docker CE.
Don’t forget to check Docker Hub to find the latest Docker Container images available for download. And, you will need to create an account before downloading images. The image below shows how-to search for the SQL Server image.
In Summary
As technology will keep improving, make sure stay up-to-date. This give us the opportunity to improve our job position and be of value for the organization that hire us.
Don’t forget to look for the nearest technology event in your areas, as this is the opportunity to learn for free and gain invaluable knowledge.