Using PowerShell to manage SQL Server under Linux

Using PowerShell to manage SQL Server under Linux

Our previous article described installing and configuring SQL Server under CentOS 7.3, and today we'll focus on how to use PowerShell under Windows to manage SQL Server under Linux. In fact, when it comes to PowerShell everyone is already familiar with, PowerShell is not only the command set is also a batch program, can be very good to assist the administrator to improve the daily operation and maintenance work, But one of the problems with PowerShell at Microsoft is that different services need to have different PowerShell installations, and there's a big difference between a shell, an Exchange shell, and a system's own PowerShell in Lync. And also today we're going to talk about using PowerShell to manage SQL Server, you need to install a separate ssms to use PowerShell to export the SQL module. If you import the SQL Server module using the system's PowerShell, you will be prompted with an error, hoping that Microsoft will solve the problem completely. Well, not much to say, to go to the beginning of our introduction today;

We first learned that SQL Powershell is included in SQL Server Manager Studio, so we only need to download the corresponding ssms; download link

Https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8ySizRD90AAGK2HsUlM4108.png" height= "292"/>

Installation steps we skipped.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4D/wKiom1i8ySmD8heTAACvTLxbRBw174.png" height= "/>"

After downloading the installation we can use the system's own PowerShell to manage (if we have installed the SSMs tool just downloaded to use the system's own PowerShell)

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4D/wKiom1i8ySqCXz2MAAIN3NDPeGg685.png" height= "484"/>

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4B/wKioL1i8ySvyaj5wAACtXfj_L-c111.png" height= "436"/>

We first need to import the SQL module

Import-module SQL Server

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8ySyQ1EMDAACCoM2z-Sk865.png" height= "417"/>

Verifying SQL Server module status

Get-module-name SQL Server

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4D/wKiom1i8yS3ilLQ0AACDBNRVaOE341.png" height= "282"/>

Next we use PowerShell to link SQL Server and then get the server information

# Prompt for credentials to login into SQL server$serverinstance = "<your_server_instance>" $credential = get-credent ial# Load the SMO assembly and create a Server object[system.reflection.assembly]::loadwithpartialname (' Microsoft.SqlServer.SMO ') | Out-null$server = New-object (' Microsoft.SqlServer.Management.Smo.Server ') $serverInstance # Set Credentials$server. Connectioncontext.loginsecure= $false $server. Connectioncontext.set_login ($credential. UserName) $server. Connectioncontext.set_securepassword ($credential. Password) # Connect to the Server and get a few properties$server.information | Select-object Edition, Hostplatform, Hostdistribution | format-list# Done

We execute scripts to facilitate our use of PowerShell Ise

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8yS7wuj8BAAJ2ps1aFR4115.png" height= "484"/>

Then we started writing PowerShell scripts;

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4D/wKiom1i8yTCTQPUsAAKVi28_r5c514.png" height= "437"/>

Prompt to enter SQL Server's account and password

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4D/wKiom1i8yTSTle_kAAOH3PBgTQ4257.png" height= "484"/>

My execution result information is not complete, should show the system version: such as Linux and so on.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4B/wKioL1i8yTXRcNdjAAK9VZRb0rA831.png" height= "458"/>

Finally, let's talk about how to manage SQL Server on Linux using PowerShell.

The first is through the module that imports SQL under PowerShell

Import-module SQL Server

Then, using PowerShell to connect to SQL Server, you can use the following methods

# Prompt for credentials to login into SQL server$serverinstance = "<your_server_instance>" $credential = get-credent ial# Load the SMO assembly and create a Server object[system.reflection.assembly]::loadwithpartialname (' Microsoft.SqlServer.SMO ') | Out-null$server = New-object (' Microsoft.SqlServer.Management.Smo.Server ') $serverInstance # Set Credentials$server. Connectioncontext.loginsecure= $false $server. Connectioncontext.set_login ($credential. UserName) $server. Connectioncontext.set_securepassword ($credential. Password) # Connect to the Server and get a few properties$server.information# done

Enter your SQL Server account and password verification

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4D/wKiom1i8yTfj66YiAAHe4kwDTRc980.png" height= "282"/>

After execution, we can see the results of the operation.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4D/wKiom1i8yTiRY74kAAI2GE1Z5Aw946.png" height= "484"/>

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4D/wKiom1i8yTvCfor5AAM5BdQKaAY243.png" height= "484"/>

Finally, we can use PowerShell to operate SQL Server.

The operation command is the same as our local command;sqlcmd

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8yTvg6wZ7AAB_z-hXokk523.png" height= "476"/>

In fact, there is another way to connect to SQL Server through PowerShell--this is the most common, but also the most convenient way to use, in fact, the use of MySQL under Linux is the same method, but the parameters are different

First, import the SQL Server module

Import-module sqlserversqlcmd-s 192.168.5.20-u Sa-p Way is also possible

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8yTyQzQACAACN-NklhO0301.png" height= "321"/>

In fact, all of the next steps are the same as our use of PowerShell on Windows.

Also use the help command to operate

Get-help SQL Server

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4D/wKiom1i8yT3huuQvAAFV8evyv9M463.png" height= "452"/>

Because I need to download and install the help command, so it's slow, I can't wait here.

Next we use PowerShell to manage our SQL data, for example, we look at what databases the current system has

Select name from Sys.databases;go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4B/wKioL1i8yT7j_CtKAACiWjBxkMk586.png" height= "484"/>

Next we create a database and then insert the data

Create DATABASE Xll;go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4D/wKiom1i8yT-gr_SeAACn-HgtjUg055.png" height= "392"/>

Then we select the database

Use Xll;go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4D/wKiom1i8yUDw9ZT-AAC4cOvnHAw922.png" height= "339"/>

Next we create a table, under the database you just selected

CREATE TABLE xllinfo (ID int, name varchar (), quantity int); go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4D/wKiom1i8yUCj-ixlAABxMEJXzNw186.png" height= "305"/>

We view the current table information

SELECT * FROM Xllinfo;go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/4B/wKioL1i8yUHh4SGNAABKPv2tqCY700.png" height= "151"/>

Then we insert the data into the table

INSERT into TESTTB values (1, ' ZS ', +); insert into TESTTB values (1, ' ls '); go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8yUGRCDWyAACTvvLDMiY616.png" height= "208"/>

We continue to query

SELECT * FROM Xllinfo;go

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/4B/wKioL1i8yUKCSGLeAABKmeZtjTc004.png" height= "/>"

Next we'll confirm it under SSMS.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/4B/wKioL1i8yUSSAsM2AAFn3xn2tTc262.png" height= "484"/>

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://s3.51cto.com/wyfs02/M02/8E/4B/wKioL1i8yUWyD5WbAAE9420W74s495.png "height=" 359 "/>

This article from "Gao Wenrong" blog, declined reprint!

Using PowerShell to manage SQL Server under Linux