LocalDB – Stormrage

I went to the trouble of downloading the localdb versions of SQL so i could build a lab fast.

The PowerShell Example Below Dynamically creates multiple versions of SQL Server, all with the sa password of MyRealPasswordForTheDemo#312

###################################################################################################
## Create semi-random Localdb instances and also add them to the CMS
###################################################################################################
#LDB-rand-DQUPE
Clear-Host
###################################################################################################
## BEGIN Configuration
###################################################################################################
## [string[]] $CMSEnvironments = “Prod”,”Dev”,”QA”,”STG”, “UAT”,”Prod” ## repeating Prod twice means twice as many prod servers as other layers
[string[]] $CMSEnvironments = “Prod”,”Dev”,”QA” ## repeating Prod twice means twice as many prod servers as other layers
[string] $SQLServerName = “LocalHost”
[int] $ServersPerLayer = 1
[string] $ScriptsDirectory = “”
[Hashtable] $AvailableSQLVersions =  @{ “SQL2014″ =” 12.0″;”SQL2016″ = “13.0”;”SQL2017″ = “14.0”;”SQL2019″ = “15.0”} #$AvailableSQLVersions.GetType()

$Password = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String(‘TXlSZWFsUGFzc3dvcmRGb3JUaGVEZW1vIzMxMg==’)) 
$UserName = “sa” 
$UseSQLLogin = $false;
###################################################################################################
## END Configuration
###################################################################################################
Function Get-RandomCharacters {										# https://powersnippets.com/create-password/
  [CmdletBinding()]Param (									# Version 01.01.00, by iRon
    [Int]$Size = 8, [Char[]]$Complexity = “ULNS”, [Char[]]$Exclude
  )
  $AllTokens = @(); $Chars = @(); $TokenSets = @{
    UpperCase = [Char[]]’ABCDEFGHIJKLMNOPQRSTUVWXYZ’
    LowerCase = [Char[]]’abcdefghijklmnopqrstuvwxyz’
    Numbers   = [Char[]]’0123456789′
    Symbols   = [Char[]]’!”#$%&”()*+,-./:;<=>?@[\]^_`{|}~’
  }
  $TokenSets.Keys | Where-Object {$Complexity -Contains $_[0]} | ForEach-Object {
    $TokenSet = $TokenSets.$_ | Where-Object {$Exclude -cNotContains $_} | ForEach-Object {$_}
    If ($_[0] -cle “Z”) {$Chars += $TokenSet | Get-Random}					#Character sets defined in uppercase are mandatory
    $AllTokens += $TokenSet
  }
  While ($Chars.Count -lt $Size) {$Chars += $AllTokens | Get-Random}
  ($Chars | Sort-Object {Get-Random}) -Join “”								#Mix the (mandatory) characters and output string
}

###################################################################################################
##check for all the possible LocalDB versions for variety
###################################################################################################
$PathToLocalDB = ‘C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SQLLocalDB.exe’  ##SQL2019

if($env:COMPUTERNAME -eq ‘STORMBASE’) {$BasePath = ‘D:\Data\GitHub\Workspace\AutomatedInstall\DBA_sps\’} else{$BasePath = ‘C:\Data\GitHub\Workspace\AutomatedInstall\DBA_sps\’}
$LogPath = [System.IO.Path]::Combine($BasePath,”LocalDBSQLScriptLogs_” + (Get-Date).ToString(“yyyy-MM-dd”) + “.txt”)
## $PathToLocalDB defaults to the “latest” version of SQL with the above logic.

###################################################################################################
##Remove any LocalDB instances  that already exist, except for the default
###################################################################################################
$CurrentLocalDBs = &$PathToLocalDB ‘info’
foreach($localDB in $CurrentLocalDBs)
{
  if ($localDB -ne ‘MSSQLLocalDB’ -and $localDB -ne “v11.0″)
  {
    $SQLQuery =”DELETE FROM msdb.dbo.sysmanagement_shared_registered_servers WHERE name = ‘” + $localDB.ToString() + “‘;”
    Invoke-SQLCMD -Query $SQLQuery -Database “msdb” -ServerInstance $SQLServerName -TrustServerCertificate
    &$PathToLocalDB ‘stop’   $localDB.ToString()
    &$PathToLocalDB ‘delete’ $localDB.ToString()
  }
}

###################################################################################################
## now add multiple servers to each environment
###################################################################################################
foreach($LocalDBVersion in $AvailableSQLVersions.GetEnumerator() ) #ForEach-Object($LocalDBVersion in $AvailableSQLVersions.GetEnumerator()){Write-Host $LocalDBVersion.Name + “–>” + $LocalDBVersion.Value -ForegroundColor Green}
#$AvailableSQLVersions.GetEnumerator() | ForEach-Object {“$($_.Key) – $($_.Value)”}
{ 
  [int] $i=0 

  foreach($val in $CMSEnvironments)
  {
    $SQLQuery = “IF NOT EXISTS(SELECT 1 FROM msdb.dbo.[sysmanagement_shared_server_groups] WHERE [name] = ‘_” + $val.ToString() + “_Apps’ INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) VALUES(‘_” + $val.ToString() + “_Apps’,'” + $val.ToString() + “‘,0,1)”
    
    #ETL This block is creating ONE ETL server per environment, to mimic how it is done in one of my environments
    $results = Get-RandomCharacters 8 UN
    $results =’WIN-‘ + $results + ‘-‘ + ‘ETL’
    Write-Host $results -ForegroundColor Green

    &$PathToLocalDB ‘create’ ($results.ToString()) ($LocalDBVersion.Value.ToString())
    &$PathToLocalDB ‘start’ $results.ToString()
    $SQLQuery = @”
      DECLARE @P1 varchar(128) = ‘{0}’;
       IF NOT EXISTS(SELECT * FROM  msdb.dbo.[sysmanagement_shared_server_groups] WHERE name = ‘_ETL_Apps’ AND parent_id = 1)
       INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) SELECT ‘_ETL_Apps’,’_ETL_Apps’,0,1;
      INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers(server_group_id,name,server_name,description,server_type)
          SELECT grpz.[server_group_id],@P1,'(LocalDB)\’ + @P1,@P1 + ‘;Version {2};{3}’,0
          FROM msdb.dbo.[sysmanagement_shared_server_groups] grpz
        WHERE  grpz.[name] = ‘_ETL_Apps’
“@ -f $results,$val,$LocalDBVersion.Key.ToString(),$LocalDBVersion.Value.ToString()
      #$SQLQuery
      Invoke-SQLCMD -Query $SQLQuery -Database “msdb” -ServerInstance $SQLServerName -TrustServerCertificate
      for ($i=0;$i -le $ServersPerLayer;$i++)
      {
        $results = Get-RandomCharacters 8 UN
        $results =’WIN-‘ + $results + ‘-‘ + $val
        #$results

        &$PathToLocalDB ‘create’ $results $LocalDBVersion.Value
        &$PathToLocalDB ‘start’ $results.ToString()
        $SQLQuery = @”
          DECLARE @P1 varchar(128) = ‘{0}’;
          IF NOT EXISTS(SELECT * FROM  msdb.dbo.[sysmanagement_shared_server_groups] WHERE name = ‘_{1}_Apps’ AND parent_id = 1)
          INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) SELECT ‘_{1}_Apps’,’_{1}_Apps’,0,1;
          INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers(server_group_id,name,server_name,description,server_type)
              SELECT grpz.[server_group_id],@P1,'(LocalDB)\’ + @P1,@P1 + ‘;Version {2};{3}’,0
              FROM msdb.dbo.[sysmanagement_shared_server_groups] grpz
            WHERE  grpz.[name] = ‘_{1}_Apps’
“@ -f $results,$val,$LocalDBVersion.Key.ToString(),$LocalDBVersion.Value.ToString()

          Invoke-SQLCMD -Query $SQLQuery -Database “msdb” -ServerInstance $SQLServerName -TrustServerCertificate
          
          ##now we need some base tools in our new instance!
          #”SQL_005_sp_createdb.sql” –allows ceach instance to create databases with the same name (placing in subfolders)
          $sqlfile = [System.IO.Path]::Combine($BasePath,’SQL_005_sp_createdb.sql’)
          Invoke-SQLCMD -InputFile $sqlfile -Database “master” -ServerInstance (“(LocalDB)\$results”) -TrustServerCertificate
          
          $SQLQuery = “IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = ‘SandBox’) EXECUTE sp_createdb ‘SandBox’ ”         
          Invoke-SQLCMD -Query $SQLQuery -Database “master” -ServerInstance (“(LocalDB)\$results”) -TrustServerCertificate
          if($Server -ne ” -and [System.IO.Directory]::Exists($BasePath))
          {
            $CurrentServerName = (“(LocalDB)\$results”)
            $SQLDatabase = ‘master’
            ###################################################################################################
            ## any files in current folder?
            ###################################################################################################
            $AllSQLFiles =  [System.IO.Directory]::GetFiles($BasePath,’*.sql’) | Sort-Object 
            foreach($sqlfile in $AllSQLFiles)
            {
              try
              {
                $StagingFileName = “–Begin ” + [System.IO.Path]::GetFileName($sqlfile)
                Write-Host $StagingFileName -ForegroundColor Green
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
         
                if($UseSQLLogin)
                {
                  Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -UserName $UserName -Password $Password -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                }
                else
                {
                  Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                }

                $StagingFileName = “–End   ” + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999

              }
              catch
              {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                $StagingFileName = “–Error ” + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
              }

            }
            ###################################################################################################
            ##foreach folder single level only.
            ###################################################################################################
            $AllDirectories = [System.IO.Directory]::GetDirectories($BasePath) | Sort-Object
            foreach($CurrentDirectory in $AllDirectories)
            {
              $AllSQLFiles =  [System.IO.Directory]::GetFiles($CurrentDirectory,’*.sql’) | Sort-Object
              foreach($sqlfile in $AllSQLFiles)
              {
                try
                {
                  $StagingFileName = “–Begin ” + $CurrentDirectory + ” | ” + [System.IO.Path]::GetFileName($sqlfile)
                  Write-Host $StagingFileName -ForegroundColor Green
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
             
                  if($UseSQLLogin)
                  {
                    Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -UserName $UserName -Password $Password -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                  }
                  else
                  {
                    Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                  }

                  $StagingFileName = “–End   ”  + $CurrentDirectory + ” | ” + [System.IO.Path]::GetFileName($sqlfile)
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
                catch
                {
                  $ErrorMessage = $_.Exception.Message
                  $FailedItem = $_.Exception.ItemName
                  $StagingFileName = “–Error ”  + $CurrentDirectory + ” | ” + [System.IO.Path]::GetFileName($sqlfile)
                  Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                  Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
              } #$AllSQLFiles
            } #$AllDirectories
          } #if($Server -ne ”
        ##Create Some Random Databases With Random Tables
        Write-Host ‘–Begin EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];’ -ForegroundColor Green
        $SQLQuery = “EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];”         
        Invoke-SQLCMD -Query $SQLQuery -Database “master” -ServerInstance $CurrentServerName -TrustServerCertificate
         Write-Host ‘–End EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];’ -ForegroundColor Green
      } # for $ServersPerLayer
   } # $CMSEnvironments
} # $AvailableSQLVersions
###################################################################################################
## Now randomly Organize the newly added Servers into The Groups
###################################################################################################
$SQLQuery = @”
DECLARE @serverGroup int
SELECT @serverGroup = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = ‘All Applications’ AND parent_id = 1
SELECT @serverGroup
IF @serverGroup > 1
EXECUTE msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id = @serverGroup — int
–###################################################################################################
–##top 20 business apps for my group names
–###################################################################################################
DECLARE @GroupNames TABLE(RW int identity(1,1) NOT NULL PRIMARY KEY,GroupName VARCHAR(50) )
INSERT INTO @GroupNames(GroupName)
VALUES(‘Evernote’),(‘Wunderlist’),( ‘Pocket’),(‘RescueTime’),( ‘Toggl’),( ‘Eternity Time Log’),(‘Basecamp’),( ‘Trello’),( ‘Asana’),(‘Slack’),( ‘Stride’),( ‘Skype for Business’),(‘Streak’),( ‘Insightly’),( ‘Nimble’),(‘Square’),( ‘PayPal Here’),( ‘Dwolla’),(‘QuickBooks Online’),( ‘Freshbooks’),( ‘Wave’)

DECLARE @AllApplicationsID  INT
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = ‘All Applications’ AND parent_id = 1)
  BEGIN
    INSERT INTO msdb.dbo.sysmanagement_shared_server_groups([name],[description],[server_type],[parent_id])
      SELECT ‘All Applications’,’All Applications’,0,1
  END
  SELECT @AllApplicationsID = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = ‘All Applications’ AND parent_id = 1

–create some Application Groups under ‘All Applications’
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups([name],[description],[server_type],[parent_id])
SELECT GroupName,GroupName,0,@AllApplicationsID
FROM @GroupNames MySource
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups MyLookup
ON MySource.GroupName = MyLookup.name AND MyLookup.parent_id = @AllApplicationsID
WHERE MyLookup.server_group_id IS NULL

–Now delete anything under those groups
DELETE srvz
FROM msdb.dbo.sysmanagement_shared_registered_servers srvz
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups grpz ON srvz.server_group_id = grpz.server_group_id
INNER JOIN @GroupNames MySource ON MySource.GroupName = grpz.name AND grpz.parent_id = @AllApplicationsID

–now add anything that is under [_]%[_]Apps randomly to one of the app groups
;WITH grps
AS
(
SELECT row_number() OVER(ORDER BY (SELECT NULL)) % 20 AS RW,grpz.* FROM msdb.dbo.sysmanagement_shared_server_groups grpz
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups parentz
ON grpz.parent_id = parentz.server_group_id
WHERE parentz.name = ‘All Applications’
)
,srv
AS
(
SELECT row_number() OVER(ORDER BY (SELECT NULL)) % 20 AS RW,srvz.* FROM msdb.dbo.sysmanagement_shared_registered_servers srvz 
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups grpz ON srvz.server_group_id = grpz.server_group_id
where grpz.name LIKE ‘[_]%[_]Apps’
)

INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers([server_group_id],[name],[server_name],[description],[server_type])
  SELECT grps.[server_group_id],srv.[name],srv.[server_name],srv.[description], 0 AS [server_type] –,grps.name
  FROM grps INNER JOIN srv ON grps.RW = srv.RW
“@
Invoke-SQLCMD -Query $SQLQuery -Database “msdb” -ServerInstance $SQLServerName -TrustServerCertificate

###################################################################################################
## Create semi-random Localdb instances and also add them to the CMS
###################################################################################################
#LDB-rand-DQUPE
Clear-Host
###################################################################################################
## BEGIN Configuration
###################################################################################################
## [string[]] $CMSEnvironments = "Prod","Dev","QA","STG", "UAT","Prod" ## repeating Prod twice means twice as many prod servers as other layers
[string[]] $CMSEnvironments = "Prod","Dev","QA" ## repeating Prod twice means twice as many prod servers as other layers
[string] $SQLServerName = "LocalHost"
[int] $ServersPerLayer = 1
[string] $ScriptsDirectory = ""
[Hashtable] $AvailableSQLVersions =  @{ "SQL2014" =" 12.0";"SQL2016" = "13.0";"SQL2017" = "14.0";"SQL2019" = "15.0"} #$AvailableSQLVersions.GetType()

$Password = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String('TXlSZWFsUGFzc3dvcmRGb3JUaGVEZW1vIzMxMg==')) 
$UserName = "sa" 
$UseSQLLogin = $false;
###################################################################################################
## END Configuration
###################################################################################################
Function Get-RandomCharacters {										# https://powersnippets.com/create-password/
  [CmdletBinding()]Param (									# Version 01.01.00, by iRon
    [Int]$Size = 8, [Char[]]$Complexity = "ULNS", [Char[]]$Exclude
  )
  $AllTokens = @(); $Chars = @(); $TokenSets = @{
    UpperCase = [Char[]]'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    LowerCase = [Char[]]'abcdefghijklmnopqrstuvwxyz'
    Numbers   = [Char[]]'0123456789'
    Symbols   = [Char[]]'!"#$%&''()*+,-./:;<=>?@[\]^_`{|}~'
  }
  $TokenSets.Keys | Where-Object {$Complexity -Contains $_[0]} | ForEach-Object {
    $TokenSet = $TokenSets.$_ | Where-Object {$Exclude -cNotContains $_} | ForEach-Object {$_}
    If ($_[0] -cle "Z") {$Chars += $TokenSet | Get-Random}					#Character sets defined in uppercase are mandatory
    $AllTokens += $TokenSet
  }
  While ($Chars.Count -lt $Size) {$Chars += $AllTokens | Get-Random}
  ($Chars | Sort-Object {Get-Random}) -Join ""								#Mix the (mandatory) characters and output string
}

###################################################################################################
##check for all the possible LocalDB versions for variety
###################################################################################################
$PathToLocalDB = 'C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SQLLocalDB.exe'  ##SQL2019

if($env:COMPUTERNAME -eq 'STORMBASE') {$BasePath = 'D:\Data\GitHub\Workspace\AutomatedInstall\DBA_sps\'} else{$BasePath = 'C:\Data\GitHub\Workspace\AutomatedInstall\DBA_sps\'}
$LogPath = [System.IO.Path]::Combine($BasePath,"LocalDBSQLScriptLogs_" + (Get-Date).ToString("yyyy-MM-dd") + ".txt")
## $PathToLocalDB defaults to the "latest" version of SQL with the above logic.

###################################################################################################
##Remove any LocalDB instances  that already exist, except for the default
###################################################################################################
$CurrentLocalDBs = &$PathToLocalDB 'info'
foreach($localDB in $CurrentLocalDBs)
{
  if ($localDB -ne 'MSSQLLocalDB' -and $localDB -ne "v11.0")
  {
    $SQLQuery ="DELETE FROM msdb.dbo.sysmanagement_shared_registered_servers WHERE name = '" + $localDB.ToString() + "';"
    Invoke-SQLCMD -Query $SQLQuery -Database "msdb" -ServerInstance $SQLServerName -TrustServerCertificate
    &$PathToLocalDB 'stop'   $localDB.ToString()
    &$PathToLocalDB 'delete' $localDB.ToString()
  }
}

###################################################################################################
## now add multiple servers to each environment
###################################################################################################
foreach($LocalDBVersion in $AvailableSQLVersions.GetEnumerator() ) #ForEach-Object($LocalDBVersion in $AvailableSQLVersions.GetEnumerator()){Write-Host $LocalDBVersion.Name + "-->" + $LocalDBVersion.Value -ForegroundColor Green}
#$AvailableSQLVersions.GetEnumerator() | ForEach-Object {"$($_.Key) - $($_.Value)"}
{ 
  [int] $i=0 

  foreach($val in $CMSEnvironments)
  {
    $SQLQuery = "IF NOT EXISTS(SELECT 1 FROM msdb.dbo.[sysmanagement_shared_server_groups] WHERE [name] = '_" + $val.ToString() + "_Apps' INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) VALUES('_" + $val.ToString() + "_Apps','" + $val.ToString() + "',0,1)"
    
    #ETL This block is creating ONE ETL server per environment, to mimic how it is done in one of my environments
    $results = Get-RandomCharacters 8 UN
    $results ='WIN-' + $results + '-' + 'ETL'
    Write-Host $results -ForegroundColor Green

    &$PathToLocalDB 'create' ($results.ToString()) ($LocalDBVersion.Value.ToString())
    &$PathToLocalDB 'start' $results.ToString()
    $SQLQuery = @"
      DECLARE @P1 varchar(128) = '{0}';
       IF NOT EXISTS(SELECT * FROM  msdb.dbo.[sysmanagement_shared_server_groups] WHERE name = '_ETL_Apps' AND parent_id = 1)
       INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) SELECT '_ETL_Apps','_ETL_Apps',0,1;
      INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers(server_group_id,name,server_name,description,server_type)
          SELECT grpz.[server_group_id],@P1,'(LocalDB)\' + @P1,@P1 + ';Version {2};{3}',0
          FROM msdb.dbo.[sysmanagement_shared_server_groups] grpz
        WHERE  grpz.[name] = '_ETL_Apps'
"@ -f $results,$val,$LocalDBVersion.Key.ToString(),$LocalDBVersion.Value.ToString()
      #$SQLQuery
      Invoke-SQLCMD -Query $SQLQuery -Database "msdb" -ServerInstance $SQLServerName -TrustServerCertificate
      for ($i=0;$i -le $ServersPerLayer;$i++)
      {
        $results = Get-RandomCharacters 8 UN
        $results ='WIN-' + $results + '-' + $val
        #$results

        &$PathToLocalDB 'create' $results $LocalDBVersion.Value
        &$PathToLocalDB 'start' $results.ToString()
        $SQLQuery = @"
          DECLARE @P1 varchar(128) = '{0}';
          IF NOT EXISTS(SELECT * FROM  msdb.dbo.[sysmanagement_shared_server_groups] WHERE name = '_{1}_Apps' AND parent_id = 1)
          INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups](name,description,server_type,parent_id) SELECT '_{1}_Apps','_{1}_Apps',0,1;
          INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers(server_group_id,name,server_name,description,server_type)
              SELECT grpz.[server_group_id],@P1,'(LocalDB)\' + @P1,@P1 + ';Version {2};{3}',0
              FROM msdb.dbo.[sysmanagement_shared_server_groups] grpz
            WHERE  grpz.[name] = '_{1}_Apps'
"@ -f $results,$val,$LocalDBVersion.Key.ToString(),$LocalDBVersion.Value.ToString()

          Invoke-SQLCMD -Query $SQLQuery -Database "msdb" -ServerInstance $SQLServerName -TrustServerCertificate
          
          ##now we need some base tools in our new instance!
          #"SQL_005_sp_createdb.sql" --allows ceach instance to create databases with the same name (placing in subfolders)
          $sqlfile = [System.IO.Path]::Combine($BasePath,'SQL_005_sp_createdb.sql')
          Invoke-SQLCMD -InputFile $sqlfile -Database "master" -ServerInstance ("(LocalDB)\$results") -TrustServerCertificate
          
          $SQLQuery = "IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'SandBox') EXECUTE sp_createdb 'SandBox' "         
          Invoke-SQLCMD -Query $SQLQuery -Database "master" -ServerInstance ("(LocalDB)\$results") -TrustServerCertificate
          if($Server -ne '' -and [System.IO.Directory]::Exists($BasePath))
          {
            $CurrentServerName = ("(LocalDB)\$results")
            $SQLDatabase = 'master'
            ###################################################################################################
            ## any files in current folder?
            ###################################################################################################
            $AllSQLFiles =  [System.IO.Directory]::GetFiles($BasePath,'*.sql') | Sort-Object 
            foreach($sqlfile in $AllSQLFiles)
            {
              try
              {
                $StagingFileName = "--Begin " + [System.IO.Path]::GetFileName($sqlfile)
                Write-Host $StagingFileName -ForegroundColor Green
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
         
                if($UseSQLLogin)
                {
                  Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -UserName $UserName -Password $Password -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                }
                else
                {
                  Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                }

                $StagingFileName = "--End   " + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999

              }
              catch
              {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                $StagingFileName = "--Error " + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
              }

            }
            ###################################################################################################
            ##foreach folder single level only.
            ###################################################################################################
            $AllDirectories = [System.IO.Directory]::GetDirectories($BasePath) | Sort-Object
            foreach($CurrentDirectory in $AllDirectories)
            {
              $AllSQLFiles =  [System.IO.Directory]::GetFiles($CurrentDirectory,'*.sql') | Sort-Object
              foreach($sqlfile in $AllSQLFiles)
              {
                try
                {
                  $StagingFileName = "--Begin " + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                  Write-Host $StagingFileName -ForegroundColor Green
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
             
                  if($UseSQLLogin)
                  {
                    Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -UserName $UserName -Password $Password -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                  }
                  else
                  {
                    Invoke-SQLCMD -InputFile $sqlfile -ServerInstance $CurrentServerName -Database $SQLDatabase -Querytimeout 0 -Verbose 4>&1 -TrustServerCertificate| Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                  }

                  $StagingFileName = "--End   "  + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
                catch
                {
                  $ErrorMessage = $_.Exception.Message
                  $FailedItem = $_.Exception.ItemName
                  $StagingFileName = "--Error "  + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                  Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                  Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                  Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
              } #$AllSQLFiles
            } #$AllDirectories
          } #if($Server -ne ''
        ##Create Some Random Databases With Random Tables
        Write-Host '--Begin EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];' -ForegroundColor Green
        $SQLQuery = "EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];"         
        Invoke-SQLCMD -Query $SQLQuery -Database "master" -ServerInstance $CurrentServerName -TrustServerCertificate
         Write-Host '--End EXECUTE [dbo].[sp_CreateRandomBuzzWordDatabases];' -ForegroundColor Green
      } # for $ServersPerLayer
   } # $CMSEnvironments
} # $AvailableSQLVersions
###################################################################################################
## Now randomly Organize the newly added Servers into The Groups
###################################################################################################
$SQLQuery = @"
DECLARE @serverGroup int
SELECT @serverGroup = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = 'All Applications' AND parent_id = 1
SELECT @serverGroup
IF @serverGroup > 1
EXECUTE msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id = @serverGroup -- int
--###################################################################################################
--##top 20 business apps for my group names
--###################################################################################################
DECLARE @GroupNames TABLE(RW int identity(1,1) NOT NULL PRIMARY KEY,GroupName VARCHAR(50) )
INSERT INTO @GroupNames(GroupName)
VALUES('Evernote'),('Wunderlist'),( 'Pocket'),('RescueTime'),( 'Toggl'),( 'Eternity Time Log'),('Basecamp'),( 'Trello'),( 'Asana'),('Slack'),( 'Stride'),( 'Skype for Business'),('Streak'),( 'Insightly'),( 'Nimble'),('Square'),( 'PayPal Here'),( 'Dwolla'),('QuickBooks Online'),( 'Freshbooks'),( 'Wave')

DECLARE @AllApplicationsID  INT
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = 'All Applications' AND parent_id = 1)
  BEGIN
    INSERT INTO msdb.dbo.sysmanagement_shared_server_groups([name],[description],[server_type],[parent_id])
      SELECT 'All Applications','All Applications',0,1
  END
  SELECT @AllApplicationsID = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE name = 'All Applications' AND parent_id = 1

--create some Application Groups under 'All Applications'
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups([name],[description],[server_type],[parent_id])
SELECT GroupName,GroupName,0,@AllApplicationsID
FROM @GroupNames MySource
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups MyLookup
ON MySource.GroupName = MyLookup.name AND MyLookup.parent_id = @AllApplicationsID
WHERE MyLookup.server_group_id IS NULL

--Now delete anything under those groups
DELETE srvz
FROM msdb.dbo.sysmanagement_shared_registered_servers srvz
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups grpz ON srvz.server_group_id = grpz.server_group_id
INNER JOIN @GroupNames MySource ON MySource.GroupName = grpz.name AND grpz.parent_id = @AllApplicationsID

--now add anything that is under [_]%[_]Apps randomly to one of the app groups
;WITH grps
AS
(
SELECT row_number() OVER(ORDER BY (SELECT NULL)) % 20 AS RW,grpz.* FROM msdb.dbo.sysmanagement_shared_server_groups grpz
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups parentz
ON grpz.parent_id = parentz.server_group_id
WHERE parentz.name = 'All Applications'
)
,srv
AS
(
SELECT row_number() OVER(ORDER BY (SELECT NULL)) % 20 AS RW,srvz.* FROM msdb.dbo.sysmanagement_shared_registered_servers srvz 
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups grpz ON srvz.server_group_id = grpz.server_group_id
where grpz.name LIKE '[_]%[_]Apps'
)

INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers([server_group_id],[name],[server_name],[description],[server_type])
  SELECT grps.[server_group_id],srv.[name],srv.[server_name],srv.[description], 0 AS [server_type] --,grps.name
  FROM grps INNER JOIN srv ON grps.RW = srv.RW
"@
Invoke-SQLCMD -Query $SQLQuery -Database "msdb" -ServerInstance $SQLServerName -TrustServerCertificate