Tag Archives: Microsoft

Use SQL Server Central Management Server remotley through powershell

Ever wanted to run a scheduled powershell script that uses the CentralManagementServer to connect to all your SQL servers ?

After trying out a bunch of diffrerent solutions I found this piece of code on a Microsoft blog.

I have modified it and combined it with several other code pieces to get the result I wanted.
This code will run on towards the Central Management Server you havce setup, it will work from any other server
which has the following installed on it:

* Shared Management Objects
* SQLSysClrTypes
* PowershellTools

I had some initial problems to create the output file beacuse the SQLPS module dosen’t allow use of UNC paths as output,
there fore I had to use the New-PSDriveĀ  command to map a driveletter.

To use the script chnage the pathc and file names to your liking,
change the CMS servername and change the name of you group in CMS .ServerGroups[ “All SQL servers” ] .
Here it is possible to add more levelsin the folder structure if you have organized your servers in prod, test and dev or similar.
Like this .ServerGroups[ “All SQL servers” ].ServerGroups[ “Test” ]

In this code I have just include code to get me the size of all databases on all servers in my CMS server.
But you could of course change that code to execute things or get any other informationĀ  out from your servers.
I have added some powershell formatting to get the size in GB and with no decimals.

$date=Get-Date -format "yyMMd"
#Need to map a drive letter since UNC paths are not supported with SQLPS
New-PSDrive -Name "G" -PSProvider FileSystem -Root "\\server\path" -Persist $file = "G:\SQL_dev_stats_$date.csv"

function GetSQLStats {
#Load SMO assemblies
$CentralManagementServer = "CMS-server\instancename"
$MS='Microsoft.SQLServer' @('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$My="$ms.Management.Smo" #
$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "All SQL servers" ].GetDescendantRegisteredServers() |
    Foreach-object {new-object ("$My.Server") $_.ServerName } | # create an SMO server object
       Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
          Foreach-object {$_.Databases } | #logins for every server successfully reached

          # Put your Powershell SQL statements here
              Select-object @{Name="Server"; Expression={$_.parent}}, Name, @{Label="Size (MB)";Expression={"{0:N0}" -f $_.Size | foreach {$_.replace(",","")}} } |
          # End of your code here
########## Main Program ###########
GetSQLStats  | out-file $file #-notypeinformation -Delimiter ";"

MasterDataServices setup in a AlwaysOn environment

I was told to install MDS (Master Data Services) in AlwaysOn environment with HA (high availability) and DR(disaster recovery) protection for a customer. This sounded like an easy task with some withe papers and guidelines available on the net.

I started on with this one from Microsoft which looked very promising:


But I soon realized this was maybe not the best solution since it required me to install IIS on each node in the AlwaysON cluster and it also required that all IIS installations where kept in sync.

The AlwaysOn environment at this customer site is setup with 5 nodes, three on primary site and two on DR site. It includes 3 Availability Groups which can failover between at least 3 of the different hosts. It uses NonShared Storage.

Installing IIS and all MDS services in this environment didn’t sound very appealing so I decided to use a different approach.

I created a new virtual server in Vmware and added it to the SRM (SiteRecoveryManager) setup, which means that it is replicated to the DR site and can be started up there in case of DR situation. On the primary site HA is accomplished through multiple hosts in the Vmware cluster.

After Installing MDS on the new virtual server, I run the Master Data Services Configuration Manager and pointed to the AlwaysOn AG listener that I had previously created on the new AG for the MDS database.

I now have HA and DR solution for MDS without having to mess up the SQL servers with IIS and MDS services, that feels like a much cleaner solution to me.

Staffan Olofsson
MCSE: Data Platform