What do I need to execute a SQL Server PowerShell

问题:

I am creating an Octopus deploy that installs a windows service on a remote server. I am re-using a template from a web deploy project that sets up the web services and web application (on other servers). It has an extra step that deploys database scripts to update the schema and stored procedures etc. This step is executed on the web server using SQL Server PowerShell tools (apparently).

I need to incorporate that as well into my project. The folks who set up the original project are long gone! I have managed to get it working up to the last step, however the SQL Server PowerShell tools needed on the new server are not available.

The database script step is throwing the following error.

Import-Module : The specified module 'C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS' was not loaded because no valid module file was found in any module directory.
At C:\Octopus\Work\20160930164655-24\.ps1:1 char:14

The necessary folders and files exist on the web server. It looks like SQL Server Client is not installed though (judging by the start menu; I don't see SQL Server Management Studio for example).

In Programs and Features on the web server I see a bunch of SQL Server things:

• Microsoft SQL Server 2012 Command Line Utilities
• Microsoft SQL Server 2012 Data tier App Framework
• Microsoft SQL Server 2012 Express LocalDB
• Microsoft SQL Server 2012 Management Objects
• Microsoft SQL Server 2012 Management Objects (x64)
• Microsoft SQL Server 2012 Native Client
• Microsoft SQL Server 2012 Transact-SQL Compiler Service
• Microsoft SQL Server 2012 Transact-SQL ScriptDom
• Microsoft SQL Server 2012 Transact-SQL Language service
• Microsoft SQL Server 2012 TSQL Language service
• Microsoft SQL Server Comnpact 4.0 SP1 x64 ENU
• Microsoft SQL Server Data Tools - enu(11.1.20627.00)
• Microsoft SQL Server Data Tools - Build Utilities - enu (11.1.20627.00)
• Microsoft SQL Server System CLR Types
• Microsoft SQL Server System CLR Types (x64)
• Microsoft SQL Server System CLR Types for SQL Server 2012
• Microsoft SQL Server System CLR Types for SQL Server 2012 (x64)
• Windows Powershell Extensions for SQL Server 2012

I need to raise an IT ticket to get this set up on my new server but don't know what to ask for! What installation(s) would give me the above features? Or what install would allow me to execute that script?

Would the SQL Server Client 2012 suffice?

Note: the server I need to install on is 32 bit.

回答1:

SQLPS is auto installed in sql server 2012. The old versions need to install it from download Featurpack.

It's installed normally for sql server 2012 in the folder:

  c:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS

If it's not available in that path require installing:

   Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012

It's availabl at url:
32 bit:
64 bit:

in Poweshell 3 you can run the command (without path):

      Import-Module Sqlps -DisableNameChecking;

and it's valid if you use the path

Invoke-Sqlcmd is similar to the command line tool sqlcmd

You can run your script file using the command:

 sqlcmd -S <server> -U <user> -P <password>  -i <qry.sql>

回答2:

What you seek is the Microsoft SQL Server 2012 Feature Pack, available here:

https://www.microsoft.com/en-us/download/details.aspx?id=29065

If you expand the "Install Instructions" section, and then scroll about halfway down the page, you will see that one of the components listed there is the PowerShell Extensions, which contains the SQLPS module. Note that this component depends on the Shared Management Objects component (also on the same page), which in turn depends on CLR Types (also on the page, but at least that one is already installed on your server).

There are different versions available for other versions of SQL Server:

SQL Server 2014: https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2016: https://www.microsoft.com/en-us/download/details.aspx?id=52676