Do you have PowerShell code that you reuse in your scripts over and over? Do you have server names hard coded in variables? Are you using a text file or CSV file to import server names? Do you find yourself only utilizing one server out of a cluster of servers to make your PowerShell commands? These are the questions I asked myself and the answer used to be YES. In this post, I will go over how you can store your infrastructure server information in a SQL database and call that data from a custom PowerShell module. By utilizing this method, you can expect the below benefits:

• Centralized code means less places to modify if you want to make a change
• Randomized server selection to prevent over usage of one server
• Centralized location to store server information
• Easily add or remove server infrastructure as your environment changes
• Flexibility to pull server data from multiple sites and locations
• Standardized scripts make for easier readability and debugging

Disclaimer: I am not a SQL DBA, so I may not be utilizing SQL to its fullest potential or doing it the “SQL” way. I am much more comfortable with PowerShell, so I tend to utilize it to do my heavy lifting as opposed to having more robust SQL queries, stored procedures or views.

SQL

Let’s start out by briefly looking at the SQL Table. For this demo, I stood up a SQL Server 2016 instance on a Windows Server 2019. The details for that will not be covered in this post. I utilized SQL Server Management Studio to create the database and table. Here are the steps:

1. Launch SQL Server Management Studio and connect to your SQL Server
2. Right click Databases, and then click New Database
3. Give the database a name
4. Click OK to create the database with all defaults (you may want to customize depending on your environment)
5. Once the database is created, expand the new database
6. Right click on Tables, and then click on New, then Table
7. Fill out all the Column Names and specify the Column Data Type
8. Click the floppy disk icon to save the table, it will prompt you to name the table
9. Give it a name and click OK

Below is the end result of the table and the data I inputted.

Code

GitHub

Now we can finally get to the good stuff! Let’s start off by putting together the PowerShell module. To create a PowerShell module, all we need to do is save our file with the .psm1 extension. Now that we have a PowerShell module file created, we need to add the functions. The first function we are going to add is a function to randomly grab one infrastructure server based on the given site, environment, type and active status from our SQL table. This function will execute a SQL query against the table that we setup earlier. Once that data is returned, it will be filtered based on the parameters that are passed in. The final output of this function is one randomly generated server (example output at bottom of post).

Function Infra_One_Random ($Site, $Environment, $Type, $Active) { 
    ### SQL query used for invoke-sqlcmd
    $Infra_query="
    SELECT [site]
          ,[location]
          ,[server]
          ,[type]
          ,[active]
          ,[environment]
    FROM [demo_db].[dbo].[demo_table]
    GO
    "
    #####################################################
    try{
        ### Execution of the SQL Query and then it pipes the results to the where-object command to filter out the results based on the given parameters
        $Servers_Selected = Invoke-Sqlcmd -ServerInstance SQL_01 -Query "$Infra_query" -ErrorAction SilentlyContinue | Where-Object {$_.site -eq $Site -and $_.environment -eq $Environment -and $_.type -eq $Type -and $_.active -eq $Active}
        ### Randomly selecting one server from the returned servers from above query
        Get-Random -InputObject $Servers_Selected.server -Count 1
    }
    Catch [Exception]{ ### If the command inside the try statement fails the error will be outputted
        $Error_Message = "FAILED - "  ### Adding FAILED - before the actual error message to keep all the error messages in a standard format
        Return $Error_Message + $($_.Exception.Message)
    }
    #####################################################
}

The next function we are going to add is a function to pull all infrastructure servers based on the given site, environment, type and active status from our SQL table. This function will execute a SQL query against the table that we setup earlier. Once that data is returned, it will be filtered based on the parameters that are passed in. The final output of this function is all servers for a given site (example output at bottom of post).

Function Infra_All_Servers ($Site, $Environment, $Type, $Active) {
    ### SQL query used for invoke-sqlcmd
    $Infra_query="
    SELECT [site]
          ,[location]
          ,[server]
          ,[type]
          ,[active]
          ,[environment]
    FROM [demo_db].[dbo].[demo_table]
    GO
    "
    #####################################################
    try{
        ### Execution of the SQL Query and then it pipes the results to the where-object command to filter out the results based on the given parameters, then it pipes that to foreach-object to only return the name of the servers
        Invoke-Sqlcmd -ServerInstance SQL_01 -Query "$Infra_query" -ErrorAction SilentlyContinue | Where-Object {$_.type -eq $Type -and $_.site -eq $Site -and $_.active -eq $Active -and $_.environment -eq $Environment} | ForEach-Object {$_.server}
    }
    Catch [Exception]{ ### If the command inside the try statement fails the error will be outputted
        $Error_Message = "FAILED - " ### Adding FAILED - before the actual error message to keep all the error messages in a standard format
        Return $Error_Message + $($_.Exception.Message)
    }
    #####################################################
}

The final function we are going to add is a function to grab one random infrastructure server from each site based on the given environment, type and active status from our SQL table. This function will execute a SQL query against the table that we setup earlier. Once that data is returned, it will be filtered based on the parameters that are passed in. The final output of this function is one randomly generated server for each unique site (example output at bottom of post).

Function Infra_One_Random_Each_Site ($Environment, $Type, $Active) {
    $Unique_Active_Servers = @() ### Creating empty array to be used later
    ### SQL query used for invoke-sqlcmd
    $Infra_query="
    SELECT [site]
          ,[location]
          ,[server]
          ,[type]
          ,[active]
          ,[environment]
    FROM [demo_db].[dbo].[demo_table]
    GO
    "
    #####################################################
    try{
        ### Execution of the SQL Query and then it pipes the results to the where-object command to filter out the results based on the given parameters
        $Servers_Selected = Invoke-Sqlcmd -ServerInstance SQL_01 -Query "$Infra_query" -ErrorAction SilentlyContinue | Where-Object {$_.active -eq $Active -and $_.type -eq $Type -and $_.environment -eq $Environment}
    }
    Catch [Exception]{ ### If the command inside the try statement fails the error will be outputted
        $Error_Message = "FAILED - " ### Adding FAILED - before the actual error message to keep all the error messages in a standard format
        Return $Error_Message + $($_.Exception.Message)
    }
    #####################################################
    ### Sorting the SQL data for just all of the unique sites so we can identify which server to use for each site
    $Unique_Sites = $Servers_Selected | Sort-Object -Unique -Property site | ForEach-Object{$_.site}
    ### Going through each unique site to identify one random server to use
    Foreach ($Site in $Unique_Sites){
        $Unique_Active_Servers += $Servers_Selected | Where-Object {$_.site -eq "$Site"} | Get-Random -Count 1 | ForEach-Object {$_.server}
    }
    Return $Unique_Active_Servers
}

Now that we have our PowerShell module created and the functions created, we can move into how to utilize it. For this example, I placed the PowerShell module on a network share, but, you could also place it within the directories specified in the PSModulePath variable. To import this module into memory so we can use the functions within it, we use the Import-Module command. Here I use the Force parameter to ensure that I am always using the latest and greatest version of the module. I also use the Verbose parameter which is for demonstration purposes only.

Once the module is loaded into memory, I can execute the functions with the appropriate parameters. Here are some examples of running the functions and expected output:

Infra_One_Random

Infra_All_Servers

Infra_One_Random_Each_Site

Alternatively, the more common way would be to store the results in a variable or array to be utilized throughout a script.

$Random_Server = Infra_One_Random -Site a -Environment prod -Type vcenter -Active true
$All_Servers = Infra_All_Servers -Site a -Environment prod -Type vcenter -Active true
$Server_Per_Site = Infra_One_Random_Each_Site -Environment prod -Type citrix_dc -Active true

That’s it for now, thanks for reading!

• powershell