SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query local SSMS server group with Powershell?


Query local SSMS server group with Powershell?

Author
Message
dtibz01
dtibz01
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 272
This may be a totally off the wall question, but it seems like it should be possible to do. Just can't find any specifics as to how I might accomplish it.

I have a situation in that I have the same database (structurally) across multiple SQL Server instances, with different database names. Through SSMS I have a local server group defined that references each unique instance and individual database. This allows me query each database at once with a single SQL statement, regardless of database name.

When queries are run through my SSMS environment, I have the option enabled to return the SQL Server instance in the grid output so the instance name is returned with each row. I would like to automate running my query and outputting the results to Excel via Powershell script.

What I'm hung up on is that I need to be able (with Powershell) to reference my SSMS local server group as the the data source that the query runs against, and run the query. Has anyone done this?

I appreciate any feedback..
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7780 Visits: 6045
Well, if you can get the information out of the file, registered servers are stored in
C:\Users\username\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

But I'd be much more tempted to set up my own text file or db table to store the server/database information.

Easy enough once you've got the list to run your query in a foreach loop.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40062 Visits: 14413
Have you heard of Central Management Server (CMS)? It supports the "interact with each server in a server group from PowerShell" functionality that I think you are after.

If setting up a CMS is not something you want to begin doing then I would also look into storing the list of servers in a text file and using something like

foreach ($server in (Get-Content C:\myservers.txt)){Write-Host $server;}

or something similar with the servers stored in a utility database somewhere instead of a text file.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dtibz01
dtibz01
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 272
Thank you both (Gazareth & opc.three) for taking the time to reply. I will likely end up going the route of a servers.txt file, I was just trying to find a way around it and in typical fashion was probably over thinking it a bit I guess. In any case, I do appreciate the feedback.


DT
MG-148046
MG-148046
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3743 Visits: 2828
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name> then set a variable equal to get-childitem and do a foreach loop over each server in the group.

A simple example:

Set-Location SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>
$servers = get-childitem
foreach ($server in $server) {<do something herer>}

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40062 Visits: 14413
MG-148046 (12/27/2012)
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name> then set a variable equal to get-childitem and do a foreach loop over each server in the group.

A simple example:

Set-Location SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>
$servers = get-childitem
foreach ($server in $server) {<do something herer>}

I like the text file or database table only because it's portable. Here is more info on it. It requires the installation of some add-ons to make sqlps importable into your PoSh sessions.

Programming SQLRegistration in the SQL Server PowerShell Provider

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7780 Visits: 6045
MG-148046 (12/27/2012)
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>


Nice, didn't know about that location! Certainly easier than parsing the XML file!
Ness
Ness
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 974
This also works
get-childitem 'SQLSERVER:\SQLRegistration\Central Management Server Group\

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search