Robert.stellinga

Microsoft SQL Server management in SLM : best practices

Blog Post created by Robert.stellinga Employee on Mar 29, 2018

 

Dear reader,

 

Based on the previous questions asked about how to efficiently manage the lifecycle of Microsoft SQL Servers, I’ve decided to write a blog post about this specific matter. In this blog post, I’ll try to explain and show ways to determine how to draw up an overview of the SQL Servers currently being inventoried in your environment, how to assign specific SQL Server licenses with different metrics to these SQL Servers and which reports you should apply to maintain and manage the lifecycle of your Microsoft SQL Server estate – quantity, version, edition, location and of course compliance –

 

So…after reading this blog post you should be able to:

  • determine and create an overview of all SQL Servers running in your estate
  • collect information about the SQL Server version, edition, resources used, allocation, if it’s running in a virtual server or not, and more interesting information.
  • add a SQL Server license and assign the license accordingly based on the correct metric
  • create and save easy to use reports specifically for your SQL Server estate, including compliance information

 

 

MICROSOFT SQL SERVER APPLICATIONS

Looking at the list of all applications in the Snow License Manager, you might get overwhelmed with the amount of SQL Server applications you see and wonder where you should start. Luckily, the Snow Recognition Services will provide useful filters to easily switch between metrics and bundles. From a license compliance perspective I assume that you end up with the following editions you wish to manage; Enterprise, Standard, Web Edition and the somewhat older Datacenter. For this blog post – and all other examples used - I will focus on the Enterprise and the Standard editions. SQL Server Express and Developer are free of charge, Datacenter is obsolete and the Web Edition is only available via SPLA providers.

 

Lets have a closer look at one single SQL Server application, in this case “Microsoft SQL Server 2012 Standard”. I have selected the compliance tab, to highlight some interesting findings (picture 1).

Picture 1

 

In this particular example every SQL Server 2012 Standard installation is calculated on the number of cores. The compliance tab provides information about the actual cores (247) in use on every server in your IT estate, and at the same time also adjusting the required number of cores (302) needed to be compliant. Although, the calculations are made based on the number of cores, this doesn’t mean that you are unable to apply a different metric; like the “Server + Call” model. I’ll get back to this matter in the last subtopic.

 

Microsoft SQL Server family

In the screenshot above (picture 1) of the Microsoft SQL Server 2012 Standard application, you’ll see a red rectangle. By clicking on “Microsoft SQL Server” you’ll end up in the Application Family of the SQL Server. This section in the Snow License Manager contains lots of interesting information about all the Microsoft SQL Servers in your IT estate.

Picture 2

 

The top section (picture 2) highlights different SQL Server editions and shows a total compliance percentages. The lower section (picture 3) shows more details about your SQL Server IT estate, which provide some very interesting details you may apply for making important (business) decisions. The first tab contains information about your SQL Server licenses purchases, including the amount of license purchases, but more importantly if you still have active Software Assurance on top of those license or not.

Picture 3

 

The last tab (picture 4) contains details about the actual installation of each specific SQL Server, including information about the server type, edition, version and processor/core resources used for each installment. This is important information for assigning the right license based on the metric that might apply.

Picture 4

 

Although we have seen lots of vital information by looking at one single SQL Server application and analyzing the details provided in the Application Family for SQL Server, the best way to collect and analyze the data needed to assign all your SQL Server licenses accordingly, is to use the reports available in the report category.

 

 

MICROSOFT SQL SERVER REPORTS

In this section of my blog post I will zoom in and use two out-of-the-box available reports, that are very helpful for getting a better understanding of your total SQL Server estate and the capability of keeping a keen eye on the compliance per SQL Server. Our goal is to determine which SQL Servers needs which specific SQL license, so that you can assign them accordingly.

 

The two default reports I’ll use are the following:

1) Microsoft SQL-Server hardware comparison

2) License tracking per computer

 

Starting with the first report “Microsoft SQL-Server hardware comparison” I have removed and added the following columns for a better overview of the SQL Server IT estate.

 

Removed columns:

  • Organisation (which you could consider to keep in the report, if you need to know the business unit the SQL Server belongs to. For this example I’ve decide to remove it)
  • Applications licensed per processor
  • Licensed processors
  • Applications licensed per core
  • Licensed cores

 

Added columns:

  • Is Virtual
  • Processor type (in case you want to know the physical processor of each physical server for the core factor calculation. Please note, that Snow automatically takes the core factor into account!)

 

The screenshots (picture 5) below shows the final report you could end up with.

Picture 5

 

We now have a list of all SQL Servers grouped per version and edition. We can see which SQL Server is running on a physical stand-alone machines and which SQL Server is running in a virtual machine. The information about the number of processors and cores, including the processor type, is important for compliance reasons. In case you wish to have more detailed information about the physical layer that is hosting all of the virtual machines – to apply Microsoft SQL Licenses to the physical layer and use virtualization rights – you can use the information provided in the Application Family of Microsoft SQL Server (picture 4) or use the second report, which I will explain now.

 

The second report “License tracking per computer” first needs some search criteria to show the right output. I have applied the following two filters (picture 6):

Picture 6


I have also removed and added the following columns for a better overview of the SQL Server IT estate in this report.

 

Removed columns:

  • Organisation (which again you could consider to keep in the report, if you need to know the business unit the SQL Server belongs to. For this example I’ve decide to remove it)
  • Manufacturer
  • Assignment type

 

Added columns:

  • Compliance
  • Datacenter name
  • Host computer name
  • Requirement adjustment reasons

 

The result of my tailored report can be viewed below (picture 7). I have currently not added any Microsoft SQL Server licenses in the Snow License Manager, which means that the Compliance columns indicates a shortage for every SQL Server installation. This top section of the report shows a list of physical stand-alone servers.

Picture 7

 

If I select or filter on a particular datacenter, I end up with the following overview (picture 8) of the datacenter and each host that is a part of the datacenter, which I might use to either decide to license the physical layer or each individual virtual server – based on the number of virtual servers running SQL Servers -

Picture 8

 

I will save this tailored report in my personal report group called “Microsoft Reports” with the name “Microsoft SQL Server installation on physical servers and virtual servers, including compliance position”.

 

 

MICROSOFT SQL SERVER LICENSES

Now that I have been able to investigate the different Microsoft SQL Server applications running in my estate with the use of the applications list and the application family overview, I’ve also created two helpful reports that provide the information I now need to start assigning my Microsoft SQL Server license entitlements.

 

I have gather all my Microsoft agreements and licenses, and found out that I own the following Microsoft SQL Server Licenses:

  • 9 Microsoft SQL Server 2005 Standard (server + call)
  • 2 Microsoft SQL Server 2008 Enterprise licenses (server + call)
  • 18 Microsoft SQL Server 2012 Enterprise Core licenses (2-pack = 36 cores in SLM)
  • 27 Microsoft SQL Server 2016 Standard Core licenses (2-pack = 54 cores in SLM)
  • 2 Microsoft SQL Server 2016 Standard licenses (server + call)

 

When I look at my current compliance summary for Microsoft SQL Server, I get the following (picture 9) result:

Picture 9 – taking from the report: Compliance summary with criteria set on Microsoft as manufacturer and SQL on application family -

 

I now need to add all my Microsoft SQL Server licenses 1-by-1 and regardless of the license metric, assign each license to the correct machine.

 

To determine and know for sure which machine needs which Microsoft SQL Server license, you might need to address this within your company to find out more about the SQL Database and specific components and what it is used for. Please also be aware that I strongly advise you to contact your Microsoft trusted advisor or reseller (LSP) to assist and advice you with the Microsoft SQL license rules and product terms that might apply to your personal situation.

 

I will demonstrate the adding of licenses for a couple of my available licenses, starting with the nine Microsoft SQL Server 2005 Standard licenses. I will assign five of them to the SQL Server 2000 Standard installations to cover them based on downgrade rights. To make sure I can assign the licenses I need to adjust the “Assignment type” in the license. The screenshot (picture 10) below shows where to find this.

Picture 10

 

I can then start assign the license to each specific SQL Server running a SQL Server 2000 Standard installation, as shown in the screenshot (picture 11) below.

Picture 11

 

I have assign the two Microsoft SQL Server 2008 Enterprise accordingly and will now continue with the 18 Microsoft SQL Server 2012 Enterprise Core licenses. The screenshot (picture 12) below shows that I have added the license as one purchase record, containing a total of 36 cores (18 x 2)!

Picture 12

 

With the use of my saved license tracking report;“Microsoft SQL Server installation on physical servers and virtual servers, including compliance position” I can easily assign the available Microsoft SQL Server 2012 Enterprise Core licenses, as shown in the screenshot (picture 13) below.

Picture 13

 

Continuing and finishing the assignment of all other available licenses accordingly (27 Microsoft SQL Server 2016 Standard Core licenses and 2 Microsoft SQL Server 2016 Standard licenses), I can immediately see the results in the compliance report (picture 14) and in my saved license tracking report (picture 15).

Picture 14

 

Picture 15

 

This way, I can easily look at my overall compliance situation for Microsoft SQL and spot the compliance situation for each individual server that has a SQL component installed that requires a license of some kind. If for some reason, new installations of SQL components should take place in my IT estate, I’ll be able to see on which server(s) and in which location this took place and can proactively pick this up and determine a license need or not.

 

I hope this blog post helps you out with getting a better grip on your total Microsoft SQL Server landscape and the possible views you can have to analyze this, and at the same time add your Microsoft SQL Server licenses the right way.

 

Don’t hesitate to comment on this blog post or reach out to me or any of your local Snow contact for more assistance.

Outcomes