PowerShell to run same SQL to multiple databases in multi tenant system

Background

Multi-tenant systems are complex by nature and not everybody works on multi-tenant systems even in the Enterprise. In those systems, the boundary has to be clearly defined between tenants. It is mainly due to the noisy neighbour/tenant. No one wants to get disturbed by others in the same system. Some systems use all the tiers separated from the web server itself. Some separate only the database by sharing web and processing tiers. There is no one rule how to separate the tenants.

If we are maintaining a multi-tenant system with every tenant has their own database with identical structure, it is a question most of the time how to apply structural / schema changes quickly. Below is one method which is easy to apply a hotfix to all.

PowerShell script

This script iterate through all the databases in the SQL Instance then executes the SQL in a file specified. That file may contain an alter table, SP changes, etc...

---

# NOTE* It is advised to test on dev server before higher environments
# ----------------------------------------------

$Inst = "<Your sql instance with port number if applicable>"

$filepath = "<file path to sqlfile>"


# $databases grabs list of databases. Adjust the where condition to filter.

$databases = invoke-sqlcmd -ServerInstance $Inst -Database "master" -Query "select name from sys.databases where database_id > 4 and name <> 'TEST'"


foreach ($database in $databases)
{
    $DBname = $database.name
    # Outputs one file per database
    $outfile = $filepath + "."+$database.name+".log"

    #Execute scripts
    Invoke-Sqlcmd -ServerInstance ${Inst} -Database $database.name -InputFIle $filepath | out-file -filepath $outfile

    $database.name
}

---

Note 

This assumes there is only one SQL instance in the system and takes care of the databases inside. But in the real situation, huge multi-tenant systems will have multiple SQL instances.