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:
- PowerShell on Linux
- Pyodbc and psmssql drivers for Linux and Windows
All that is to say, you can perform the same actions or achieve the same level of automation, using either language: PowerShell or Python.
Python is better as a general-purpose programming/scripting language. If you also have Linux or other non-Windows boxes to administer, you may also probably prefer Python, even though Microsoft is pushing and assisting third parties in developing PowerShell modules to cover wide areas of technologies. If you are a Windows admin, PowerShell is something you’d probably like more, simply because of its deep integration with Microsoft products.
In this article, we’ll describe and discuss both technologies and you can see for yourself, which you may choose.
PowerShell
In the last decade, since its birth, PowerShell has propelled automation extensively due to several of its qualities such as flexibility, dynamism, remoting capabilities, modularity, etc., not to mention being baked into the operating system.
The available packages lead to easier administration and automation of several repetitive tasks on Windows systems, along with almost all other Microsoft products. Needless to say, many third-party providers such as Citrix and VMware have also created PowerShell modules and snap-ins that administrators can leverage in order to achieve automation. SQL Server is one of the greatest benefactors of PowerShell, having several database management and automation capabilities built in. Ever since PowerShell, we’ve found it a lot easier to load the related modules and invoke the respective cmdlets to take care of several day-to-day activities, starting from simple querying to complex maintenance.
PowerShell is the prime candidate when it comes to scripting and automation in the world for Windows systems based on one of the main benefits of PowerShell, which is the deep integration with Windows APIs, and it general extensibility. The flexibility of PowerShell addresses most of the challenges we’ve had for several years in managing Windows systems. Today, PowerShell is available for Linux and Bash for Windows (with Linux Subsystem for Windows). According to me, it helps both Windows as well as Linux administrators go cross-platform. Perhaps, someday, we may be able to use both the systems as if they were one.
Today, DevOps is something sought after. DevOps is nothing but the combination of application development and operations. DevOps aims at eliminating the disconnect between the process of development, and system administration by means of automation of certain aspects of application development and automation. Python and PowerShell are two obvious choices in that area, among other common scripting/programming languages such as Bash, Ruby and Java.
Python
Python has been around for a long time, and is known for its simplicity among several other fortes, which makes it one of the go-to languages for deep learning and machine learning. Despite the current popularity of PowerShell, Python, for SQL Server, is still a matter of preference for some professionals, as it does offer some compelling features and advantages.
I would recommend reading my first two articles on Data analytics using Python and Python for SQL Administrators, to a get primer on Python for SQL. These articles may even answer a few of your questions.
Why not both?
Exactly, why not both!? If you know both the languages, you’re spoiled with choices, where you may prefer one tool over the other for the job at hand. Knowing both the languages would only benefit you. It’s not like a future employer is going to put your resume in the circular filing cabinet because you know a lot of really useful and powerful scripting languages!
In a way, you may feel that Python is a better choice when handling some Linux-specific tasks, or find PowerShell to be more efficient handling a Windows task. While Python is an excellent scripting language, it doesn’t have the flexibility of interactive use that PowerShell has. A lack of a friendly interactive interface was one of the reasons Microsoft dropped VBScript after a point and decided to go all out developing PowerShell. Each has its own advantages.
In the rest of the article, we’ll dig a bit deeper into these technologies from setting them up to using them for some simple tasks, so you can learn about them in more detail.
Installing PowerShell on Linux
All right, Linux… First-Class Citizen and all. How do you go about installing PowerShell on Linux? Turns out, it is a pretty straightforward process. Let’s get PowerShell on Linux, shall we? One step at a time.
Step 1: Install wget.
|
1 2 3 |
#sudo yum install wget |
Step 2: Download the PowerShell package for CentOS using the wget utility
|
1 2 3 4 |
#wget --directory-prefix=/etc/yum.repos.d/powershell.repo https://github.com/PowerShell/PowerShell/releases/download/v6.0.0-alpha.18/powershell-6.0.0_alpha.18-1.el7.centos.x86_64.rpm |
Step 3: Install PowerShell using yum
|
1 2 3 |
# sudo yum install -y powershell |
The summary shows that PowerShell libraries have been registered in the Linux system.
Let’s now invoke the PowerShell console.
|
1 2 3 |
#powershell |
Executing a Python script using PowerShell
This section talks about how the Python sample code first.py is executed using PowerShell
|
1 2 3 4 5 6 7 8 9 |
# This program adds two numbers num1 = 1.5 num2 = 6.3 # Add two numbers sum = float(num1) + float(num2) # Display the sum print('The sum of {0} and {1} is {2}'.format(num1, num2, sum)) |
The below output shows the integration of PowerShell and Python using the concept of piping. It’s a technique of communication between two modules by passing the information from the first module to the next, just like how we pass the output of one cmdlet in PowerShell to the next.
Loading the SQL Server modules into Linux
Of course, we’re database administrators. Our primary focus is SQL Server. So let’s dig further and see how the SQL Server modules are loaded onto Linux. Let’s try an Import-Module.
|
1 2 3 |
Import-Module SqlServer |
The error tells us that the SQL Server related modules are not loaded.
Register the DLLs o the PowerShell Core components
The section details how to register the DLL’s to the PowerShell core components. The prerequisites for .NET Core have been assumed to be met before proceeding further. The next steps would be to Identify and download the correct files. In the case of CentOS, we download Microsoft.SqlTools.ServiceLayer-centos-x64-netcoreapp2.0.tar.gz.
Download the tar file here
After the download completes, extract the files using the tar command. In the output we can see that the file SQL Server related DLLs are registered to PowerShell core components.
|
1 2 3 |
#tar –xvf Microsoft.SqlTools.ServiceLayer-centos-x64-netcoreapp2.0.tar.gz |
So far, the DLLs are registered. To run the SQL module, we must add the .NET Framework type to the PowerShell session.
|
1 2 3 4 |
>Add-type --AssemblyName “Microsoft.SqlServer.Smo” >Add-type --AssemblyName “Microsoft.SqlServer.ConnectionInfo” |
Done. Let us now build the connection string.
|
1 2 3 4 5 |
>$SQLSrvConn=new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo(‘.’,’sa’,’thanVitha@2015’) >SQLSrvConn |
Next, we use the above connection string to instantiate the PowerShell SQL Server object.
|
1 2 3 4 |
>SQLSrvObj=new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn) >SQLSrvObj |
That went well.
Next, let’s list the database on the local instance.
|
1 2 3 4 |
>$dbs=$SQLSrvObj.Databases >$dbs|Select Name,Collation, CompatibilityLevel,Size |
There we go!
Wrapping Up
PowerShell is not only a scripting language, but a complete management solution, using the shell access-it directly taps into the OS. Almost all of Microsoft Enterprise solutions, be it SQL Server, or System Center, or Exchange, have their respective PowerShell modules/snap-ins. Some of the management consoles are built with PowerShell as their core.
Therefore, a few years ago, it would’ve seemed obvious that PowerShell was the best solution for automation in Windows-based environments. Python, however, has the wide variety of APIs that support many of modern programming techniques and new fields like machine learning.
Although, things have changed now; Microsoft has open-sourced the PowerShell core. This caused a paradigm shift. PowerShell core, based on the .NET Core is cross-platform, today. We now have PowerShell Desired State Configuration, which is a step into the Infrastructure as a Code arena. To make it more effective as a Configuration Management solution, today, PowerShell can run well on Linux distributions as well. Today, PowerShell and Python are languages that can truly complement each other. In my opinion, PowerShell or Python should now become PowerShell and Python.
What’s your opinion?
What programming language do you recommend to sysadmins and new IT pros? What resources do you think would be most helpful when learning a new coding language? Let’s have a discussion in the comments below!
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021