Category Archives: Sql Server

Some notes about SSMS.

Here’s where the problem lies. If you are installing SQL 2012-2016 your Scripting Options may not work.

For SQL Server 2014 SSMS is included in the SQL install and SP.

The fix:

Even though you might only be installing SQL Server 2014 because SMO Options didn’t always work between versions 2012-2016 we need to install SQL Server 2017 SSMS 17.9.1.

e.g. What was broken was the SMO Scripting Option for
$Scripter.Options.NoFileGroup = $false
which is the default but even explicitly forcing it had no effect.

Simply adding SSMS 17.9.1 allowed correct scripting from the server.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

SSMS 17.9.1 is the current General Availability (GA) version of SSMS
1) download Download SQL Server Management Studio 17.9.1

2) download Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)

Version Information
Release number: 17.9.1
Build number: 14.0.17289.0
Release date: November 21, 2018

No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.

Import-Module SQLPS -DisableNameChecking 
$SQLInstanceProd = @()
            
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\" 
        
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
    $MyPSObject = New-Object PSObject -Property @{ 
            SQLInstance  = $_ 
            ServerGroup  = $ServerGroup;
        }
    $MyPSObject
    }

$SQLInstanceProd

Putting that logic into Get-RegisteredServers I populate the following list in my Profile.

$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{  $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance