CODEWITHSUNDEEP
powershellazure-analysis-services
Cataster
Cataster

Reputation: 3481

How to change connection string for each database in each server

Say i have 2 servers server 1 and server 2.

In each server there are x databases, each having a connection to a global database.

server1 has database1, datatabse2, database3.

server2 has database4, database5

We would have 2 files, one file containing the servers, and another containing the "databases"

then the loop would be:

Import-Module SqlServer 

foreach ($server in $servers_file)
{
    $Analysis_Server = New-Object Microsoft.AnalysisServices.Server  
    $Analysis_Server.connect("$server") 

    foreach ($database in $databases_file)
    {
        $database = $Analysis_Server.Databases.FindByName($database)

        ####### Setting connection property for $database #######

        $database.DataSources[0].ConnectionString = "UserId=…;Password=…."
    }

}

the problem here is this loop does not consider where a database belongs for the server.

for example, if i have the database file as:

database1

database2

database3

database4

database5

how can i make this loop know that it has to exit the inner loop when database1-3 has finished from server1, and now you have to move on to the outerloop to connect to server2 for databases4 and 5.

ConnectionInfo class: https://learn.microsoft.com/it-it/dotnet/api/microsoft.analysisservices.connectioninfo?view=sqlserver-2016

Upvotes: 0

Views: 192

Answers (2)

Theo
Theo

Reputation: 61013

Seeing your last comment on wanting to use a csv file to populate the Hashtable, you can do this:

Suppose your CSV looks like this

"Server","Database"
"server1","database1"
"server2","database4"
"server1","database2"
"server1","database3"
"server2","database5"

You can then read it in using Import-Csv and create a hash from it like this

$h = @{}
Import-Csv '<PATH_TO_THE_CSV_FILE>' | Group-Object Server | ForEach-Object {
    $db = @()
    foreach ($item in $_.Group) {
        $db += $item.Database 
    }
    $h += @{$($_.Name) = $db}
}

Depending on your version of PowerShell (I think you need at least 5.1 for this), you can simplify the previous by doing:

$h = @{}
Import-Csv 'D:\Code\PowerShell\StackOverflow\Databases.csv' | Group-Object Server | ForEach-Object {
    $h += @{$($_.Name) = $_.Group.Database}
}

Next you can use the loop as described by thom schumacher:

foreach($server in $h.Keys){ 
   $Analysis_Server = New-Object Microsoft.AnalysisServices.Server  
   $Analysis_Server.connect("$server") 
   foreach($db in $h[$server]) {
      write-output "$server - has db $db"
      $database = $Analysis_Server.Databases.FindByName($db)

      ####### Setting connection property for $database #######

      $database.DataSources[0].ConnectionString = "UserId=…;Password=…."

    }
}

Edit

From your comment I gather your csv file looks like this:

"Server","Database"
"server1", "database1, database2, database3"
"server2","database4, database5"

In that case, you can read it into a Hashtable like this:

$h = @{}
Import-Csv '<PATH_TO_THE_CSV_FILE>' | ForEach-Object {
    $h += @{$($_.Server) = ($_.Database -split '\s*,\s*') }
}

If your CSV file does not have headers like above, user the -Header switch on the Import-Csv cmdlet to give the columns a name we can work with like this:

    Import-Csv '<PATH_TO_THE_CSV_FILE>' -Header 'Server','Database'

Upvotes: 1

Thom Schumacher
Thom Schumacher

Reputation: 1583

I suggest you make a hashtable where you assign the Database values to each server.

$h=@{'server1'=( 'database1','database2','database3');'server2'=( 'database4','database5')}

Then your loop becomes:

foreach($key in $h.keys){ 
   $Analysis_Server = New-Object Microsoft.AnalysisServices.Server  
   $Analysis_Server.connect("$key") 
   foreach($value in $h[$key])
    {
      write-output "$key - has db $value"
      $database = $Analysis_Server.Databases.FindByName($database)

      ####### Setting connection property for $database #######

      $database.DataSources[0].ConnectionString = "UserId=…;Password=…."

    }
}

Upvotes: 1

Related Questions