What is the easiest way to install SQLPS module? We plan to use invoke-sqlcmd on more than 2000+ servers. (Windows server 2003 standard edition/
SQL Server 2008 EXPRESS SP2).
I know SQLPS comes with SQL 2012. But I don’t think we’ll migrate to 2012 anytime soon.
SQLPS first shipped with SQL2008R2, in fact. But I’m not sure the module will work with older versions of SQL Server - there was server-side programming that had to be done in order to make the module work.
But you’re welcome to try! You’d need to install the SQL 2012 admin tools - e.g., Management Studio - on your computer. That’s the only way I’m aware of to get the module installed locally.
But you don’t need it. If all you want to do is sent T-SQL queries to remote computers, you can do that by using the .NET Framework from within PowerShell. We covered the technique in “PowerShell In Depth,” and it simply involves instantiating an instance of System.Data.Sql.SqlClient.SqlConnection to connect to a computer, and then .SqlCommand to create and execute the command. Everything you need is built into the Framework and is accessible from PowerShell, and will work with any version of SQL Server way back to, I dunno, 7.0 or something.
Thank you Don.
I found this post on installing SQLPS without installing SQL Server.
http://blog.smu.edu/wis/2012/11/26/sql-server-powershell-module-sqlps/
But as you said, I’m not sure if I can use Invoke-SqlCmd against SQL 2008 instances. Remote invoking is not an option in our case.
The SQL that needs to be executed on these servers is little complex, Invoke-SqlCmd would have been really handy. I’ll give it a try to run the script using .net approach. Thank you.