HoB | Create a Listing of your SQL Server Deployments (2022)

Jamie Swanson
  • December 20, 2021
  • Cloud, SQL Server

HoB | Create a Listing of your SQL Server Deployments (2)

(Video) Learn How to Deploy SQL Server 2019 in Containers in Just Minutes | Data Exposed: MVP Edition

If you currently have Microsoft SQL Server in production use within your organization, then you may often wonder if you have an accurate picture of all those SQL Server databases. Have any of them been upgraded recently? Do you have a mix of Enterprise and Standard editions? Do you have any versions that are now out of support? In this article we will present a way to gather the Version and Edition information from each database in your environment. You can compare that result to your Microsoft license entitlements to see if you have any issues that need to be addressed.

Supported version

As of this writing (2021), SQL Server 2008 and earlier is out of support. Extended Support for SQL Server 2012 will end in July 2022. After that date, no bug fixes or patches for security vulnerabilities will be available. If you still use SQL Server 2012 in production, you should already be planning your migration to newer versions.

The latest release is SQL Server 2019. SQL Server 2022 was announced in Nov 2021 and is expected to ship sometime in mid-2022.

Which Edition?

Microsoft offers a variety of paid and unpaid versions of SQL Server. Here we are only focusing on the Enterprise and Standard editions. When licensing per core, the Enterprise edition is roughly four times the cost of the Standard edition. For smaller workloads, you may find that the Standard Edition is adequate for your needs. While there is no practical limit to the size of your database with Standard Edition, it does limit the size of memory for buffer pools and other features that may affect performance.

One by one

How can you determine which Editions you have installed in your environment? For smaller environments, you can use SQL Server Management Studio to inspect the version or to run a query against each server.

SQL Server Management Studio

Use the SQL Server Management Studio to find your version and edition.HoB | Create a Listing of your SQL Server Deployments (3)

Use the Object Explorer to connect to a server. Once connected, the version information will appear in parentheses. Right-click this object and select Properties. The Server Properties will show you a ‘Product’ string (such as “Microsoft SQL Server Standard (64-bit)”) that includes the Edition (Standard in this case).

You can execute this simple query to display an extended version string with both version and edition information.

SELECT @@VERSION;

PowerShell

For larger deployments, you can use PowerShell to get updated version information for all databases at once. You will need a valid user credential for each database. The following assumes that you have a basic knowledge of PowerShell and have already configured it for your use. The only prerequisite for this method is the “SQLServer” PowerShell module from Microsoft.

By the way, if you are not running PowerShell 5.1 or higher, then stop and upgrade PowerShell first! There is really no good reason to run any earlier version. PowerShell 5.1 came out in 2017 and is backward compatible.

Run $PSVersionTable at a PowerShell prompt to verify your PowerShell version.


There is a great summary here of versions and how to upgrade.

PS C:\> $PSVersionTableName Value---- ----- PSVersion 5.1.19041.1320

SQLServer module

Writing a script to get updated version information for all databases at once is straightforward with PowerShell. Here we make several assumptions. First, you have a common user/password that is valid in all databases and has been granted sufficient rights to run the Get-SqlInstance command. Next, you have a listing of each computer with SQL Server in a text file. Each computer name should be on a line by itself.

Install the SqlServer module from the PowerShell Gallery. This only needs to be done once.

install-Module SqlServer -force

If you have implemented a database user with Windows Authentication, and you are currently logged in to Windows as that user, your credentials will automatically be used to connect. Something like this command should work for example.

(Video) Deploying SQL Server in Kubernetes - Anthony Nocentino

Get-SqlInstance -ServerInstance localhost

If you need to pass in user credentials, store them first like this (replacing MyUser with a valid username):

$Cred = Get-Credential -Message "SQL User" -UserName MyUser

Enter the password in the popup window that appears when running the above command.

Get-SqlInstance

Now you are ready to call the Get-SqlInstance command.

Get-SqlInstance -ServerInstance localhost -Credential $Cred

You should see a result like this:

Instance Name Version ProductLevel UpdateLevel HostPlatform HostDistribution------------- ------- ------------ ----------- ------------ ----------------OCDB01 15.0.2080 RTM n/a Windows Windows 10

Version 15 corresponds to the product SQL Server 2019. You can find an easy-to-read listing of SQL Server version numbers here: https://buildnumbers.wordpress.com/sqlserver/

But what about the edition? The default output of Get-SqlInstance only shows a small selection of properties. If we store the result of the command to an object, it is easier to inspect all the properties that are available.

$Instance = Get-SqlInstance -ServerInstance localhost -Credential $Cred
$Instance.Edition
Standard Edition (64-bit)

Running the script

Now we know enough to build our script! The script will read the list of computers in the file that you created. The Get-SqlInstance command gets a SQL Instance object for each instance of SQL Server that is present on the target computer. For the default instance, we only need to specify the computer name. Your SQLServers.txt might look like this:

SQLProduction01SQLProduction02SQLDevelopment01
# Prompt for a valid password$Cred = Get-Credential -Message "SQL User" -UserName MyUser# Read your prepared list of computers that are running SQL Server$ServerNames= Get-Content SQLServers.txtforeach ($Srv in $ServerNames) {$Instance = Get-SqlInstance -ServerInstance $Srv -Credential $Cred$Instance | Select-Object InstanceName,DomainInstanceName,Version,Edition}

If your user has sufficient rights to connect to all databases, you can leave out the credentials.

# Read your prepared list of computers that are running SQL Server
$ServerNames= Get-Content SQLServers.txtforeach ($Srv in $ServerNames) {$Instance = Get-SqlInstance -ServerInstance $Srv$Instance | Select-Object InstanceName,DomainInstanceName,Version,Edition}

The instance name may be in either the InstanceName or the DomainInstanceName property, so both are included in the output. Once you have this script running successfully, you can run it any time you want to check on any version changes in your SQL Server deployments. Remember to first add any new computers running SQL Server to the SQLServers.txt file. You may also want to add a timestamp to the output if you have a need to track your usage and versions over time.

Hopefully this script gives you some confidence in using PowerShell and exploiting the many features of the free SQL Server PowerShell module.

Additional documentation regarding the SQL Server PowerShell module can be found on Microsoft’s website at: https://docs.microsoft.com/en-us/powershell/module/sqlserver/?view=sqlserver-ps

Understanding Microsoft SQL Server licensing can be complex and go beyond just counting cores and users. House of Brick can help you understand your SQL Server licensing footprint and provides services for maintaining the license compliance on an ongoing basis. Visit the following link for more information about House of Brick’s SQL Server licensing services: https://houseofbrick.com/expert-services/compliance-governance/

  • configure SQL Server, License Consulting, MySQL, SQL Server

HoB | Create a Listing of your SQL Server Deployments (4)

(Video) Working with SQL Server Containers to Build Database Solutions - Carlos Robles

Jamie Swanson

Jamie Swanson is an IT Business Analyst at House of Brick. He has a broad background in programming, application design, databases and IT management. His go-to tools are Excel and PowerShell. Jamie enjoys doing data analysis as well as finding opportunities to automate manual processes.

All Posts

Related Posts

(Video) 45 Implement Logging to SQL Server in SSIS

Oracle

Oracle Log Maintenance Introduction

Log Maintenance is the task that always seems to be overlooked after you set up a new database, get the data migrated, and live through

Read More »

September 29, 2022

Oracle

Is My Oracle Database Supported?

One thing our team hears frequently from customers is a variation on “I have to upgrade so that my database will be supported.” This statement

Read More »

September 27, 2022

AWS

Oracle RAC to Single Instance for AWS Migrations

Oracle Real Application Clusters (RAC) is Oracle’s database level clustering technology that allegedly provides high availability. The intent is to make the RAC databases always

Read More »

(Video) How to Configure Always-On High Availability in MS SQL Server 2019 - Step by Step

September 20, 2022

Videos

1. SQL Server Live Mount for Development and Testing Purposes
(Rubrik)
2. Quick Tutorial - Users and Permissions in SQL Server
(Keil Jones)
3. SCCM - How to Find What Software is Installed on a Device
(Digital Infrastructure)
4. PI SQL - Deploy an SSIS package to a SQL server
(OSIsoft Learning)
5. Install and Configure SCCM and SQL on Different Servers
(the Sysadmin Channel)
6. GCP | How to connect to SQL Server or any TCP port on your VMs using IAP tunnels
(The Cloud Nerd)

Top Articles

Latest Posts

Article information

Author: Msgr. Refugio Daniel

Last Updated: 10/20/2022

Views: 5530

Rating: 4.3 / 5 (54 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Msgr. Refugio Daniel

Birthday: 1999-09-15

Address: 8416 Beatty Center, Derekfort, VA 72092-0500

Phone: +6838967160603

Job: Mining Executive

Hobby: Woodworking, Knitting, Fishing, Coffee roasting, Kayaking, Horseback riding, Kite flying

Introduction: My name is Msgr. Refugio Daniel, I am a fine, precious, encouraging, calm, glamorous, vivacious, friendly person who loves writing and wants to share my knowledge and understanding with you.