Can SSIS read PowerShell output??
-
I am the SQL DBA on a SharePoint team. I will have 20 SharePoint farms using one SQL 2008 R2 cluster as their backend (these are Development teams). An installation of SharePoint creates around 25 databases... that adds up to about 500 databases! And to make things worse, for each farm the ~25 database names are identical except for a GUID on the end. To see how messy this gets, see the screenshot here: http://imperfectit.blogspot.com/2010/05/list-all-sharepoint-2010-databases.html
This could end up being a DBA managment nightmare! I know at some point a developer will tell me they messed up and need me to delete their databases, which will be REALLY hard since they all look nearly the same, and if I delete the wrong ones I will be flippin' burgers the next day. :hehe:
So again looking at that link I can get a list of all the SQL databases used by each farm by running a PowerShell script on each server. I would like to have SSIS do this and save the results into a table, and then in SSIS I can also put the farm name in another column, then it will be really easy to see what database belongs to what farm, if there are any orphaned databases, etc.
Is there a way to have SSIS run a PowerShell script on each server, and save the PowerShell output into a table?
-
Why don't you just use powershell to get the instance + db names and load them into a database table?
__________________________
Allzu viel ist ungesund... -
>>and then in SSIS I can also put the farm name in another column
Can you use the extended properties of a database.. Right-click on the database and go to extended properties. You then can categorize the db. You would then be able to query the system databases and get the info you need. Not sure why you would need ssis or powershell.
-
Chrissy321 (3/8/2012)
>>and then in SSIS I can also put the farm name in another columnCan you use the extended properties of a database.. Right-click on the database and go to extended properties. You then can categorize the db. You would then be able to query the system databases and get the info you need. Not sure why you would need ssis or powershell.
Personally I wouldn't do that on 20 or so instances.. 😉
__________________________
Allzu viel ist ungesund... -
Chrissy321 (3/8/2012)
>>and then in SSIS I can also put the farm name in another columnCan you use the extended properties of a database.. Right-click on the database and go to extended properties. You then can categorize the db. You would then be able to query the system databases and get the info you need. Not sure why you would need ssis or powershell.
The idea is to be able to query all 20 SharePoint Farm servers as needed so I can see if any changes have been made. If a SharePoint Farm Administrator adds or removes a Service Application to/from their farm, it will add/remove databases on my SQL cluster. If I only look at SQL and see the number of DBs has changed, I won't have any idea which Farm changed its databases!
And if I don't have an automated process and aggregate the data from 20 different SharePoint farms using something like SSIS, then I will have to frequently log in to 20 different servers, run a script, collect the data, merge all the results from 20 different servers into one table or spreadsheet or something, and then begin analyzing. Or SSIS could collect it all once every night and I could just look at the results in the morning and see if there is any difference. Much easier, and a perfect use for SSIS, if only I could figure out how to get SSIS to import the results of a PowerShell script!
-
jpSQLDude (3/8/2012)
Chrissy321 (3/8/2012)
>>and then in SSIS I can also put the farm name in another columnCan you use the extended properties of a database.. Right-click on the database and go to extended properties. You then can categorize the db. You would then be able to query the system databases and get the info you need. Not sure why you would need ssis or powershell.
The idea is to be able to query all 20 SharePoint Farm servers as needed so I can see if any changes have been made. If a SharePoint Farm Administrator adds or removes a Service Application to/from their farm, it will add/remove databases on my SQL cluster. If I only look at SQL and see the number of DBs has changed, I won't have any idea which Farm changed its databases!
And if I don't have an automated process and aggregate the data from 20 different SharePoint farms using something like SSIS, then I will have to frequently log in to 20 different servers, run a script, collect the data, merge all the results from 20 different servers into one table or spreadsheet or something, and then begin analyzing. Or SSIS could collect it all once every night and I could just look at the results in the morning and see if there is any difference. Much easier, and a perfect use for SSIS, if only I could figure out how to get SSIS to import the results of a PowerShell script!
It does not make any sense to use SSIS in order to get this thing work. I would make a list of servers and save them into a text file then do something like this..
CREATE TABLE [dbo].[SPDatabases](
[DatabaseName] [nvarchar](50) NULL,
[ServerName] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SPDatabases] ADD CONSTRAINT [DF_SPDatabases_Date] DEFAULT (getdate()) FOR [Date]
GO
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = "localhost"
$dbName = "DBA"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServer
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
foreach($sql in get-content c:\sql.txt) {
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sql
foreach ($db in $srv.Databases){
$cmd.CommandText = "INSERT INTO SPDatabases(DatabaseName, ServerName) VALUES ('$db','$srv')"
$cmd.ExecuteNonQuery() | Out-Null
}
}
$conn.Close()
Only if you want to make your life easier..otherwise go for SSIS.
__________________________
Allzu viel ist ungesund...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply