Points of interest
We use SQL Server 2014, Power Shell, SQL Server Reporting Services (SSRS), Excel, and the Microsoft Assessment and Planning Toolkit (MAP) to gather and report information on SQL Servers in your environment as it relates to their respective editions and service packs. This reporting is desirable for consultants who wish to provide services to benefit their clients and IT professionals who'd like to measure the health of the SQL Server installations in their organization. Surely, other products report on the information in this article--what we provide is simply one way (and a good way) to do it.
Introduction
If you have or manage SQL Servers within your organization, you may not be aware that many of your SQL Servers may be out of support from Microsoft either from a product or service pack perspective. Additionally, if you have multiple DBAs and managers your support structure could be segmented where you're not really sure what SQL servers you may have in your organization--even though you may think you have a good idea. In our organization, as a DBA among many, I have not been comfortable with the SQL Server sprawl that I'd been sensing. Using the SSRS report in this article I was able to find 66 percent more SQL Servers that I previously did not know about and over 80 percent that were either out of date, either through service packs or SQL versions or not supported by Microsoft. As shown in Figure A and B, I was impressed by the report's findings but not with our organizational acumen to maintain and document SQL servers.
In this article, we developed a way to determine all the SQL Servers in our environment at will and filter this information with Microsoft sunset data for Microsoft SQL Server and present all this information in an SSRS report. As a side benefit, we also store the number of processors and cores for your servers, and lots of other valuable data, so you can determine if your organization is licensing your SQL Servers correctly (we will probably write a SSRS report that reports on SQL Server cores in the future as an addition/Part II to this article). As you can see, we have 189 SQL Servers and 14 Editions of SQL Server. Alternately, manually checking the sunset data on Microsoft's Support Lifecycle page (https://support.microsoft.com/en-us/lifecycle?C2=1044) for each of our servers would be a time consuming process. But even more time consuming would be manually finding all the SQL Servers on our network. Stick with us and we'll show you a much easier way to gather all this information in minutes using SQL Server 2014, Power Shell, SQL Server Reporting Services (SSRS), Excel, and the Microsoft Assessment and Planning Toolkit (MAP).
Note:
In this article we do give you lots of information. That being said, we try to give you the "how-tos" early on-- so you can read the "extra" stuff later. Additionally, while our report may look neat to some, SSRS really isn't the main focus, but rather how to easily get the data that SSRS report is portraying.
Figure A: SQL Server Sunset Report Summary
Figure B: SQL Server Sunset Report Detail
What the Report Says
The report is composed of a main executive summary followed by report details, or sub report, about all the SQL Servers. The Executive Summary identifies the total number of SQL Servers, the total number of SQL Server editions, and the number of unsupported editions. It shows up to date SQL Servers, the expired SQL Servers, and the unsupported servers both in number and percentage of the SQL Server total. The bar chart shows the count of SQL Servers by SQL edition. Following that the sub report shows the SQL Server name, instance name, SQL Server Edition, the last date of SQL Server support and the last date of SQL Server service pack support. The last two columns show the maximum service pack level is available for the SQL Server Edition and service pack that SQL Server is currently on. In the server details in Figure B, the first column displays a SQL gauge that rates the current server by either red, yellow, or green. Red means that the SQL Server edition is out of date and the service packs are not up to date. Yellow means that the SQL edition is still supported by Microsoft but the service pack level is not. If the SQL Server is rated green, then both the SQL edition and the service pack level is good. The text in the executive summary in Figure A provides even more details you can use to interpret the report.
Good News/Bad News
For starters we've got some good news and some bad news. The good news is you can easily create our SQL Server SunSet database and SSRS reports to get much of the code without sorting through a good deal of its complexity. From a how-to perspective that's fantastic--that's about 80 percent of this article. For the bad news, which isn't really bad news, we want to give you so much more than that. We want to show you how to collect data on SQL Servers you didn't even know about, how to use Power Shell and Microsoft Excel to scrub that data, and finally, use SQL Server and SSRS to process the information. As you'll see this "bad news" involves using Power Shell and addition SQL features for Power Shell, and Microsoft SQL Server with extra Microsoft Office features as well. This "sounds" great until you realize that these features have been the subject of many articles and blogs on how to just get it to work in the first place--and we only have time and resources to present this article with only a brief introduction to these steps. However, when appropriate we'll provide links to resources we've found most effective in resolving issues. Hopefully, my presentation will be spot on and you won't have to go to those links!
Background
In order to duplicate our steps to generate this report, you'll need the following:
- An Active Directory domain
- An Active Directory domain account
- Microsoft Assessment and Planning Kit 9.3
- Microsoft Excel
- Microsoft SQL Server 2014 Express with your domain account having sysadmin rights
- Report Builder for Microsoft SQL Server 2014 version 3.0
- Windows Server 2008 R2 with Power Shell 2.0 with local admin rights for your domain account
Note: you "may" be able to perform the steps in this article with any recent flavor of Windows and SQL Server but we've not tested those combinations. I personally don't see any issues up front, but mileage may vary.
Preparation for Gathering Your SQL Servers and Reporting the Information
To get our SQL Server SunSet Report, we carefully installed and configured a variety of components help us collect data and generate the report we showed you in Figure A. Just a word of caution. As we mentioned, people wrote whole blogs and articles showing how to get things like Excel and Power Shell to work correctly with SQL Server in the fashion we're showing you. We've tried to supply what we found worked best but in an extremely condensed format. However, we've also supplied links to some of the most useful articles and blogs to help you should you have issues with the technical information we've provided.
Briefly, we used a Windows 2008 R2 Server with Power Shell 2.0 installed. Then we installed SQL Express 2014 and elements of the SQL 2014 Feature Pack that you'll need for Power Shell. Additionally, we installed the Microsoft Access Database Engine 2010. Then, we installed the Microsoft Assessment and Planning toolkit (MAP). Finally, we installed Microsoft Excel to help us scrub the data from the MAP output--which presents its data in Excel format. We also installed our SQLSunSet database that converts the raw MAP data into our SSRS report using tables, views, functions, and stored procs. Of course, we did all this with local admin rights to the Windows server and SA rights within SQL Server. Below, you'll find the brief steps we took to implement and configure these software features and products. Now, you might be thinking that these steps are a lot to go through just to generate a report. However, most of these steps are a one-time implementation. As you'll see later, when everything is in place, pulling new MAP data and generating the SSRS report is rather easy and quick.
To begin, you'll need to install Windows Server 2008 R2 Standard Edition x64 with Power Shell 2.0 or above:
- Installing and configuring is beyond the scope of this article.
- Other flavors of Windows may, and most likely will work, but we've not tested these eventualities.
Install SQL Server Express 2014:
You'll need SQL Server Advanced Edition x64. Follow these basic instructions to download, install, and configure SQL Server.
- Download and install it from : http://www.microsoft.com/en-us/download/details.aspx?id=42299.
- From the Feature Selection window select all Features and click Next.
- From the Instance Configuration window type MAPS as the named instance (MAPS with an S--this is not a misspelling).
- Note: MAP will install another copy of SQL Server Express if it doesn't find your MAPS instance.
- Click Next.
- Next, in the Server Configuration Window in the Service Accounts tab, enter your login into the SQL Server Database Engine Account Name and Password and click Next. As you'll see, the SQL Server services needs an AD account to avoid an OPENROWSET error that we use in this article.
- Now, in the Database Engine Configuration Window, on the Server Configuration tab, click Mixed Mode and type a user name and password for the SQL Server Database Engine. Then, click the Add Current User button next to the Specify SQL Server Administrators and then add the local Administrators group using the Add button.
- In the Reporting Services Configuration window click Next as well--acknowledging that Install and configure is selected.
- Click Next to start the installation process and wait for it to complete successfully--the install process may ask you to reboot Windows Server.
- Finally, open SQL Server Configuration Manager and expand SQL Server Network Configuration. Click Protocols for MAPS and enable TCP/IP and Named Pipes.
Install SQLSunSet database:
Open the CreateSunSetDBandSampleData.sql file within the SunSetSQL.zip file we've provided in this article and cut and paste the code in query window within SQL Server Studio and run the T-SQL code to create the SQLSunSet database, tables, views, functions, procedures, and sample data.
Next, we'll need to import the SSRS Report.
- Open Internet Explorer and go to http://localhost/Reports_MAPS/Pages/Folder.aspx on the Windows Server use used to install SQL Server Express.
- Provided that you've downloaded our content to a local storage, click the Upload File button command to upload the following report components from our zip file:
- SQLServerSunsetReportMain
- SQLServerSunsetReportSub
- Run the SQLServerSunsetReportSub sub-report first to validate that it pulls back data from the SQLSunSet database.
- Then, run the SQLServerSunsetReportMain report to verify that you can see pages one and two of the main report--which includes data from the sub-report.
Note: if you did not create the MAPS SQL Server instance or spell it correctly, these reports will not render properly nor will steps in the rest of this article.
Congratulations, if you rendered the report correctly and retrieved the data you've got the basic idea of what this article does. But let's take a strategic pause here and evaluate the usefulness of this article to you. Were you able to successfully follow all our steps? Do you like what you see in the report? Would you like to see your own data instead of our sample data in the report? If the answer is yes, please continue with the steps below to complete this article to generate data from your own network and SQL Servers. As you'll see, we do have quite a few steps to complete but most of them are a one-time thing. After you complete this article, you'll have to do very little to keep updating the data in SSRS report.
Download and install Microsoft Access Database Engine 2010 Redistributable (Really--a Microsoft Office engine--not just for MS Access):
As you'll see, we'll call Excel from within Power Shell before it gets to SQL Server. Follow these basic instructions to download and install:
Note: As you'll see, this isn't just for Microsoft Access but for Microsoft Excel integration with Microsoft SQL Server.
Listing A: Enable running distributed queries and configure OLEDB options to allow us to import Excel documents
exec sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Running this code gets rid of the following error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
Running this code also gets rid of this error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
Download and install the components of the 2014 SQL Server Feature Pack:
Download these components from : http://www.microsoft.com/en-us/download/details.aspx?id=42295 and install them in the following order:
- ENU\x64\SharedManagementObjects.msi
- ENU\x64\PowerShellTools.msi
Configure Power Shell:
Open Power Shell and run the following command:
set-executionpolicy unrestricted
When prompted select Y for Yes to allow this change.
Now, you'll need to prepare Power Shell for running some SQL commands that we'll use later. First, though, you need to verify that the directory shown in the first line of code is actually the directory that contains Microsoft.SqlServer.Management.PSSnapins.dll, and Microsoft.SqlServer.Management.PSProvider.dll from the SQL 2014 Feature Pack components we installed. If it's not, you need to change the code to reflect the location of those DLLs. Also, note that these DLLs are associated with the respective Power Shell snapins called SqlServerCmdletSnapin120, and SqlServerProviderSnapin120. Throughout the web you'll find variations of adding these snapins, but know that the 120 in the name is a reference for SQL Server 2014.
If the directory is correct, drag and drop the FindandInstallDlls.ps1 file into the Power Shell window from the unzipped SQLServerSunset.zip file. This code registers the SQL Server DLLs and adds the snapins. Look through the execution of the code to make sure that no errors occur.
Listing B: FindandInstallDLLS.ps1
cd "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS"
$framework=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())
Set-Alias installutil "$($framework)installutil.exe"
installutil Microsoft.SqlServer.Management.PSSnapins.dll
installutil Microsoft.SqlServer.Management.PSProvider.dll
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerCmdletSnapin120
}
if ( (Get-PSSnapin -Name SqlServerProviderSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerProviderSnapin120
}
Download and install Microsoft Excel:
Now, download and install any recent version of Microsoft Excel. Find copy of Microsoft Excel you can install--unfortunately it's not free and there's not an express version. For this article we installed Excel on Windows Server to help us do some data scrubbing with Power Shell that was easier than the SQL alternatives.
Note:
We had issues with Power Shell opening Excel. We found by opening Excel manually, that Microsoft was trying to impose automatic updates via a dialog box. We cleared the dialog box and closed Excel, allowing Power Shell to open Excel. Hopefully you won't encounter this issue.
Download and install Microsoft Assessment and Planning (MAP) Toolkit 9.3:
Download and install the Microsoft Assessment and Planning (MAP) Toolkit 9.3 from : https://www.microsoft.com/en-us/download/details.aspx?&id=7826. Follow the basic prompts to install the product.
Configure Microsoft Assessment and Planning (MAP) Toolkit 9.3:
Here are the steps we used to configure the Microsoft Assessment and Panning Toolkit. Please follow along:
- Start MAP from the Start menu.
- In the first window click the Create an inventory database radio button and type MAPDB n the Name text box underneath the Create an inventory database radio button and click OK.
- In the next window, click Usage Tracking in the left-hand column.
- Find the SQL Server box and click Collect inventory data link.
- In the Inventory Scenarios window select the SQL Server checkbox and click Next.
- Next, in the Discovery Methods window choose Use Active Directory Domain Services(AD DS) and click Next.
- Moving on, in the Active Directory Credentials window type the domain, domain account that has access to the domain and its associated password and click Next.
- In the Active Directory Options window, click Find computers in the specified domains, containers, and organizational units.
- Choose the AD containers you believe most likely to contain your SQL Servers.
- Click Next.
- Now, in the All Computers Credentials window click the Create button.
- Enter the Account name and password of an AD account that will most likely be able to login to your SQL Servers within AD.
- Click Save.
- Click the Next Button.
- In the Credentials Order Window click Next.
- In the Summary Window click Finish.
- Click the SQL Server box within the Usage Tracking section.
- Click Generate SQL Server usage tracking report.
Important: Wait for the assessment to complete.
- In the Summary window--Finish.
- Wait for the Inventory and Assessment window to perform the Data Collection and then click the Close button.
- Now, click the SQL Server box within the Usage Tracking section again to see somewhat different results from the first time we clicked it.
- Click the Generate SQL Server usage tracking report link.
- Wait for the report to be generated and click the Close button.
- Open the newly created Excel spreadsheet and validate that you can see some collected data. Then, close the spreadsheet so Power Shell can open it later without a conflict.
Importing Your Collected SQL Server Data and Running the Report
You've created the framework to allow you to collect your SQL Servers from MAP. Next, we'll show you how to import that data into the SQL Server SQLSunSet database so we can interpret the data using our SSRS report. From now on, you won't have to go through all the steps we went through above. Rather, you'll simply be able to run MAP to collect the most recent SQL Server information from Active Directory, import that data into SQL Server with Power Shell, and run the SQL Server Sunset Report.
First, to get the most recent SQL Server Active Directory data, you can simply open MAP, select Usage Tracking, click the SQL Server Box, and select the Collect inventory data link. We say this for future reference, because, with the steps above, we've already done this. Second, open Power Shell and drag and drop the CollectSQLServerReportData.ps1 file onto the command line and run the Power Shell file by pressing Enter.
For a quick walk-through of the code, note that it first check for snap-ins for SQL Server to make sure they are registered and if they are not, registers them. Then, the code assigns the MAP directory to the $dir variable. The code uses the variables $LatestMapsUsageDir.name and $LatestMapsUsageFile.name to find the latest usage tracking Excel file generated by MAP within the MAP directory. Using the $excel, $workbook, and $sheet variables the code opens Excel and removes the first three rows within the spreadsheet so SQL Server can read the Excel file correctly (surprisingly this was easier to do in Power Shell than within SQL Server). After deleting these rows, we close Excel, quit the application and make sure the memory is released. Finally, we run invoke-sqlcmd to run a stored proc within SQL Server that imports the Excel file we named using the $sqlvariable variable. Notice that sqlcmd is connecting to the MAPS SQL Server instance of the current Server via -ServerInstance $ENV:COMPUTERNAME\MAPS.
Listing C: CollectSQLServerReportData.ps1
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerCmdletSnapin120
}
if ( (Get-PSSnapin -Name SqlServerProviderSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerProviderSnapin120
}
$dir = "C:\Users\$Env:USERNAME\Documents\MAP\"
$LatestMapsUsageDir=gci $dir | ? { $_.PSIsContainer } | sort CreationTime -desc | select -f 1
$LatestMapsUsageDir.name
$dir = "C:\Users\$Env:USERNAME\Documents\MAP\"+$LatestMapsUsageDir.name+"\SQLServerUsageTracker*.xlsx"
$LatestMapsUsageFile = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$LatestMapsUsageFile.name
$file="$LatestMapsUsageFile"
$sqlVariable="@MapsExcelFile_SQLServerUsageTracker='$LatestMapsUsageFile'"
write-host $sqlVariable
# Star Excel, hide window
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $Workbook.WorkSheets.item("SQL Server Instance Details")
if ("Assessment Results for Microsoft SQL Server Database Instances" -like $sheet.Cells.item(1,1).Text)
{
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
}
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM
invoke-sqlcmd -Query "exec SQLSunSet.dbo.spImportMapSQLServers $sqlVariable" -ServerInstance $ENV:COMPUTERNAME\MAPS
Now, as you did previously, open SQL Server Reporting Services and run SQLServerSunsetReportMain report. Notice, that our sample data is now replaced with the data you collected with the Microsoft Assessment and Planning Toolkit.
To regenerate the report with the most recent Active Directory data just rinse and repeat.
- Open Microsoft MAP and select Usage Tracking. Click the SQL Server box in the Scenarios section and click Collect inventory data. When the inventory is complete, click the Generate SQL Server usage tracking report link.
- Then, Run the CollectSQLServerReportData.ps1 file within Power Shell.
- Finally, open and run and view the SQLServerSunsetReportMain SSRS report.
A Brief Explanation of the SSRS Report
Interestingly, you really don't need to know really know the inner workings of the SSRS report in order to use it. However, for a brief explanation, we did build our report(s) with Report Builder 3.0 for SQL Server 2014. As you probably guessed, our report is really the combination of a main report shown in Figure C and a sub-report shown in Figure D. The main report includes a variety of data sources that pull data directly from SQL Server to populate the pretty looking charting and graph. The sub report has only one data source that is used to query SQL Server for our SQL Server information.
Figure C: Main Report
Figure D: Sub-Report
As you can see in Listings D through L, we derived a majority of the values you see from the following SQL functions--which we describe later.
- GoodVersion_GoodSP
- GoodVersion_BadSP
- BadVersion_BadSP
For example, if you opened the main report and viewed the datasets you'd see the GoodServersGoodSp, GoodServersBadSp, BadServersBadSp associated with the respective T-SQL code below in Listing D.
Listing D: Queries for the three main datasets for the main report GoodServersGoodSp, GoodServersBadSp, and BadServersBadSp
select dbo.GoodVersion_GoodSp() as GoodServers
select dbo.GoodVersion_BadSp() as BadSp
select dbo.BadVersion_BadSp() as BadSp
Main Report
Listings E through M show the various ways that these functions are used to display data directly on the SSRS main report. Listings N through P mainly return the chart data for the editions in the SSRS main report.
Listing E: The Total Servers field is populated by the addition of GoodVersion_GoodSP , GoodVersion_BadSP, and BadVersion_BadSP datasets and their respective values from GoodServers, BadSp, and BadSp
=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!BadSp.Value, "BadServersBadSp")
Listing F: The Total Editions field is produced by counting the SQLVersion from the SQLVersion dataset
=Count(Fields!SQLVersion.Value, "SQLVersion")
Listing G: Unsupported Editions is produced by summing the ID from the UnsupportedEditions dataset
=Sum(Fields!ID.Value, "UnsupportedEditions")
Listing H: Up to Date Servers (percentage) is an equation containing the manipulation of GoodVersion_GoodSP, GoodVersion_BadSP, and BadVersion_BadSP from their respective datasets
=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")/(Sum(Fields!BadSp.Value, "BadServersBadSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))
Listing I: Up to Date Servers sums the GoodServers field from the GoodServersGoodSp dataset
=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")
Listing J: Expired Servers (percentage) is an equation containing the manipulation of GoodVersion_GoodSP, GoodVersion_BadSP, and BadVersion_BadSP from their respective datasets
=Sum(Fields!BadSp.Value, "GoodServersBadSP")/(Sum(Fields!BadSp.Value, "BadServersBadSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))
Listing K: Expired Servers sums values from the BadSp field in the GoodSErversBadSP dataset
=Sum(Fields!BadSp.Value, "GoodServersBadSP")
Listing L: Unsupported Servers is, again, an equation containing the manipulation of GoodVersion_GoodSP, GoodVersion_BadSP, and BadVersion_BadSP from their respective datasets
=Sum(Fields!BadSp.Value, "BadServersBadSp") / (Sum(Fields!BadSp.Value, "BadServersBadSp") + Sum(Fields!BadSp.Value, "GoodServersBadSP") + Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))
Listing M: Unsupported Servers sums values in the BadSp field of the BadServersBadSp dataset
=Sum(Fields!BadSp.Value, "BadServersBadSp")
Listing N: This is the SQL Version dataset we used to extract the SQL Server version from the vServerDetails view which you we will explain later
SELECT
count(*) as VersionCount, vServerDetails.SQLVersion
FROM
vServerDetails
group by vServerDetails.SQLVersion
order by VersionCount Desc
Listing O: This is the Unsupported Editions dataset we used to extract the variety of SQL Editions from the vSQLAgingReport view which we will explain later
SELECT
count(*)
from
(
select [SQLServer] + ' ' + [dbmsversion] + ' Edition' as SQLVersion
,[sql_extendedsupportenddate]
FROM [dbadmin].[dbo].[vSQLAgingReport]
group by [SQLServer] + ' ' + [dbmsversion] + ' Edition',[sql_extendedsupportenddate]
having [sql_extendedsupportenddate] <= getdate()
) t1
Bar Chart Axis Data
Briefly, we mention the data points for the bar char in the SSRS report. Figure E shows that the report the aggregated/summed versions that are displayed grouped by just says that we sum the version count per the Edition of SQL Server.
Figure E: Chart Data
Sub Report
Here's the query we use to generate the data in the sub-report. notice that it's selecting data from the vSQLSunsetReport view together with 33,66,99,100 gauge numbers used for the SSRS gauge as the very first column of the sub-report. We do some T-SQL gymnastics to get the right sort order we needed.
Listing P: Main sub-report SQL Query
SELECT
upper(vSQLSunSetReport.ServerName) as ServerName
,vSQLSunsetReport.InstanceName
,vSQLSunsetReport.SQLServer
,vSQLSunsetReport.dbmsversion
,vSQLSunsetReport.sql_extendedsupportenddate
,vSQLSunsetReport.sp_endsupportdate
,vSQLSunsetReport.LatestServicePack
,vSQLSunsetReport.servicepack
,case
when getdate() > vSQLSunsetReport.sql_extendedsupportenddate then 33
when getdate() > vSQLSunsetReport.sp_endsupportdate and getdate() <= vSQLSunsetReport.sql_extendedsupportenddate then 66
when (getdate() <= vSQLSunsetReport.sp_endsupportdate or vSQLSunsetReport.sp_endsupportdate is null) and
(getdate() <= vSQLSunSetReport.sql_extendedsupportenddate or vSQLSunsetReport.sp_endsupportdate is null) then 99
end gauge
,100 gaugetotal
FROM
vSQLSunsetReport
ORDER BY (CASE WHEN sp_endsupportdate IS NULL THEN 1 ELSE 0 END), SQLCompliance asc, SPCompliance asc, sql_extendedsupportenddate asc
Caveats and inner workings of the SQLSunSet Database
Congratulations, you've completed our how-to to retrieve and view your SQL Server/Active Directory data and service pack information. However, there are a few things you may want to be aware of within the SQLSunSet database. The first caveat shows how we programmatically deduce which server is in what cluster through the MAP data and how this works for our organization but probably won't work for yours. The second caveat, simply explains that you'll need to update the SQL Server sunset data manually from time to time.
We'll explain briefly what our SQL Server code does. Within the SQL SunSet database we have three tables, nine views, one stored procedure and four functions. The tables contain the servers for your organization, and the SQL Server sunset data from Microsoft. The views massage the data to make it report ready. The stored procedure imports the data and the functions create values specifically for the report.
Caveat One: MAP Data Doesn't Represent Windows Cluster Groups
First, the Microsoft Assessment and Planning Toolkit doesn't have a great way to pair up SQL Server nodes within a cluster. Since it doesn't know, when looking at the MAP data, we don't know either. However, we were able programmatically assume which servers were clustered nodes because the servers in our company have a V as the first letter in the computer name. What this means is that your clustered servers may not be represented accurately within our the SSRS report. For example, if you have an active/active cluster like SERVERA\INSTANCEA and SERVERB\INSTANCEB, you may see four combinations of servers in the report such as:
- VSERVERA\INSTANCEA
- VSERVERB\INSTANCEB
- VSERVERB\INSTANCEA
- VSERVERA\INSTANCEB
Rather than just:
- VSERVERA\INSTANCEA
- VSERVERB\INSTANCEB
As you might imagine, a 10 node active/active SQL Server cluster would give you 100 combinations of servers. We wish the MAP accurately reflected and documented Windows cluster groups to avoid this very issue, but it does not. Even so, I'm still amazed with the data the MAP provides! Just note, you see an unwanted V in front of your server names it's because our code is making assumptions specifically for "our" SQL Server cluster environment. Please alter our code to make the changes you feel are beneficial to yours.
Caveat Two: SQL Sunset Information Is Static and Must Periodically Be Updated
Also, what we did not mention in this article is that we painstakingly went through Microsoft's SQL Server sunset dates from https://support.microsoft.com/en-us/lifecycle?C2=1044 and input them into two tables: SQL Support and SQLSupportServicePack. These two tables are combined in the vSQLSupport view combine this data into a more readable format. Bad news: when Microsoft updates their Sunset dates, you will need to update these tables to reflect their latest changes. But at least, your changes will be incremental additions.
For example, table SQLSupport contains general information about the SQL Server release, whereas table SQLSupportServicePack contains the service pack information. Keep in mind that FK_SQLSupport within SQLSupportServicePack is the foreign key that matches the primary key within the SQL Support table which you'll need to enter manually when creating new rows within SQLSupportservicePack.
Using the T-SQL Code
All data comes from the three tables: SQLSupport, SQLSupportServicePack, and TempServerMap; the first two tables are static. We provide the data in the first two tables whereas we'll show you how to populate TempServerMap with Microsoft MAP data. Initially, we do populate TempServerMap with some static data so you can view the report initially from the zip file we provided. We supply nine views to aggregate and filter the data into data that is report worthy. The stored procedure spImportMapSQLServers imports the data from the MAP Excel file into the TempServerMap table. Also, we provide a few functions, BadVersion_BadSP, GoodVersion_BadSP, GoodVersion_GoodSp to assist in rendering data directly to the SSRS report. Finally, we have a function called MaxServers that helps us determine the maximum servers that exist in each SQL Server cluster. As we mentioned, our calculations for determining SQL Server clustered servers will probably not work for your environment because we're determining cluster servers from our very specific naming conventions for our clustered environments. Also, as we mentioned, MAP does not provide cluster groups for clusters so it's hard to determine which servers belong to which Windows cluster. Hopefully that will change in a future release. Additionally, we don't setup active/active clusters using the default instance MSSQLSERVER as shown in Listing D and E, our code assumes that as well--which may not be true for your environment. Our servers in a cluster are identically named except for the last letter. As you can see in listing D, we use CHARINDEX('.', [COMPUTER NAME]) - 1)) to find those identically named servers, and the MaxServers function shown in Listing R to count those servers for further calculations. Mileage will vary, but we're not optimistic that our naming convention and report data for clusters will match your organization's approach.
SQL Server Tables
As we mentioned, we use a small collection of tables SQLSupport, SQLSupportServicePack, and TempServerMap who's structure you can see below. SQLSupport contains data about the editions of SQL Server, Microsoft's lifecycle support dates, version and subversion numbers. The SQLSupportServicePack contains service pack numbers, start support and end support dates for each service pack. As you'll see later, we join these two tables together to get a complete view of SQL Server editions and their associated service packs. The TempServerMap table contains the data we imported from Microsoft MAP--which, by the way, is great for calculating licensing requirements for SQL Server as well due to the column that contains the number of cores per server.
SQLSupport
The SQLSupport table contains general SQL Server lifecycle start dates, mainstream dates and extends support dates along with parsed SQL version information.
Listing Q: SQLSupport table
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SQLSupport](
[PK] [int] IDENTITY(1,1) NOT NULL,
[SQLServer] [varchar](50) NULL,
[LifeCycleStartDate] [date] NULL,
[MaintstreamSupportEndDate] [date] NULL,
[ExtendedSupportEnddate] [date] NULL,
[Version] [int] NULL,
[SubVersionStart] [int] NULL,
[SubVersionEnd] [int] NULL,
CONSTRAINT [PK__SQLSuppo__321507876D5093CD] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SQLSupport] WITH CHECK ADD CONSTRAINT [FK_SQLSupport_SQLSupport] FOREIGN KEY([PK])
REFERENCES [dbo].[SQLSupport] ([PK])
GO
ALTER TABLE [dbo].[SQLSupport] CHECK CONSTRAINT [FK_SQLSupport_SQLSupport]
GO
SQLSupportServicePack
The SQLSupportServicePack table contains SQL Server ServicePack information such as the service pack number, version, and support dates.
Listing R: SQLSupportServicePack table
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SQLSupportServicePack](
[PK] [int] IDENTITY(1,1) NOT NULL,
[ServicePack] [varchar](50) NULL,
[ServicePackNum] [int] NULL,
[StartSupportDate] [date] NULL,
[EndSupportDate] [date] NULL,
[FK_SQLSupport] [int] NULL,
CONSTRAINT [PK__SQLSuppo__32150787380BD012] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SQLSupportServicePack] WITH CHECK ADD CONSTRAINT [FK_SQLSupportServicePack_SQLSupport] FOREIGN KEY([FK_SQLSupport])
REFERENCES [dbo].[SQLSupport] ([PK])
GO
ALTER TABLE [dbo].[SQLSupportServicePack] CHECK CONSTRAINT [FK_SQLSupportServicePack_SQLSupport]
GO
TempServerMap
The TempServerMap table contains the imported data from the SQL Server usage tracking report. This table is a holding container that gets the raw data from the MAP Excel SQL Server usage file. This table is deleted and recreated every time an import is run.
Listing S: TempServerMap table
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TempServerMap](
[Computer Name] [nvarchar](255) NULL,
[Machine Type] [nvarchar](255) NULL,
[Physical Machine Name] [nvarchar](255) NULL,
[SQL Server Product Name] [nvarchar](255) NULL,
[SQL Server Edition] [nvarchar](255) NULL,
[SQL Server Version] [nvarchar](255) NULL,
[SQL Service Pack Level] [float] NULL,
[Instance Name] [nvarchar](255) NULL,
[Operating System] [nvarchar](255) NULL,
[Operating System Architecture Type] [nvarchar](255) NULL,
[CPU] [nvarchar](255) NULL,
[Machine Serial Number] [nvarchar](255) NULL,
[Number of Processors] [float] NULL,
[Number of Total Cores] [float] NULL,
[Number of Logical Processors] [float] NULL,
[Number of Host Processors] [nvarchar](255) NULL,
[Clustered?] [nvarchar](255) NULL,
[SQL Service State] [nvarchar](255) NULL,
[IP Address] [nvarchar](255) NULL,
[WMI Status] [nvarchar](255) NULL
) ON [PRIMARY]
SQL Server Views
We use a handful of views to massage the data from either the TempServerMap table or the SQLSupport, SQLSupportServicePack tables. SQLAllServers contains data unioned from the following views SQLClusterActiveActive, SQLClusterActivePassive, SQLStandAlone. As you'd probably guess, SQLClusterActiveActive contains our active/active SQL cluster servers, SQLClusterActivePassive contains our active/passive SQL cluster servers, and SQLStandAlone contains our stand alone servers. As we've mentioned, the cluster views will probably not reflect the clustered SQL Servers in your environment because Microsoft MAP doesn't collect enough cluster information. It works for us because we use specific naming conventions for our clustered SQL servers.
The view vSQLSupport formats data from the SQLSupport table and the vSQLSupportServicePack view so it is more readable. vSQLSupportServicePack adds a row number to the SQLSupportServicePack table so we can do computations on the row number. vSQLSupportMaxServicePack displays the maximum service pack for each version of SQL Server. vSQLSunsetReport is actually what we query to generate our SSRS report details--it contains, either directly or indirectly, data from all the other views in the SQLSunSet database.
- SQLClusterActiveActive
- SQLClusterActivePassive
- SQLStandAlone
- SQLAllServers
- vServerDetails
- vSQLSupportServicePack
- vSQLSupport
- vSQLSupportMaxServicePack
- vSQLSunsetReport
SQLClusterActiveActive
The SQLClusterActiveActive view for our environment contains our SQL Servers representing active/active cluster configurations. Again, this feature is specifically hard coded for our environment and server names since MAP doesn't document Windows cluster groups.
Listing T: SQLClusterActiveActive view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SQLClusterActiveActive]
AS
SELECT SUBSTRING(SQLServerName, 0, CHARINDEX('.', SQLServerName) ) SQLServerName,
[instance name], t2.[Computer Name], t2.[Machine Type], t2.[Physical Machine Name], t2.[SQL Server Product Name], t2.[SQL Server Edition],
t2.[SQL Server Version], t2.[SQL Service Pack Level] , t2.[Operating System], t2.[Operating System Architecture Type],
t2.[CPU] , t2.[Number of Processors], t2.[Number of Total Cores], t2.[Number of Logical Processors], t2.[Number of Host Processors],
t2.[Clustered?] , t2.[IP Address], t2.[WMI Status]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1)
ORDER BY SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1)) AS Row, *
FROM (SELECT DISTINCT
'V' + [Computer Name] SQLServerName, [Computer Name], [Machine Type], [Physical Machine Name], [SQL Server Product Name], [SQL Server Edition],
[SQL Server Version], [SQL Service Pack Level] , [Operating System], [Operating System Architecture Type],
[CPU] , [Number of Processors], [Number of Total Cores], [Number of Logical Processors], [Number of Host Processors],
[Clustered?] , [IP Address], [WMI Status]
FROM tempservermap
WHERE [clustered?] LIKE 'yes' AND [instance name] NOT LIKE 'mssqlserver') t1)
t2
JOIN
(SELECT [computer name], [instance name], CASE WHEN row > dbo.maxservers([computer name]) THEN dbo.maxservers([computer name]) ELSE row END AS row
FROM (SELECT [computer name], [instance name], ROW_NUMBER() OVER (PARTITION BY [computer name]
ORDER BY [instance name]) AS Row
FROM (SELECT DISTINCT SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1) AS [computer name], [instance name]
FROM tempservermap
WHERE [instance name] NOT LIKE 'mssqlserver' AND [clustered?] LIKE 'yes') t1) t2) t3 ON charindex(t3.[computer name], t2.[computer name]) > 0 AND
t2.row = t3.row
GO
SQLClusterActivePassive
The SQLClusterActivePassive view for our environment this contains our SQL Servers representing active/passive cluster configurations.
Listing U: SQLClusterActivePassive view
select
SQLServerName
,[instance name]
,t1.[Computer Name]
,t1.[Machine Type]
,t1.[Physical Machine Name]
,t1.[SQL Server Product Name]
,t1.[SQL Server Edition]
,t1.[SQL Server Version]
,t1.[SQL Service Pack Level]
-- ,[Instance Name]
,t1.[Operating System]
,t1.[Operating System Architecture Type]
,t1.[CPU]
-- ,[Machine Serial Number]
,t1.[Number of Processors]
,t1.[Number of Total Cores]
,t1.[Number of Logical Processors]
,t1.[Number of Host Processors]
,t1.[Clustered?]
-- ,[SQL Service State]
,t1.[IP Address]
,t1.[WMI Status]
from tempservermap t1
join
(SELECT dISTINCT 'V'+ SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])-1) +'A' as SQLServerName FROM tempservermap where [clustered?]='yes' and [instance name] like 'MSSQLSERVER') t2
on charindex(right(SQLServerName, len(SQLServerName)-1),[computer name]) > 0
GO
SQLStandAlone
The SQLStandAlone view shows all the servers that our code considers stand alone SQL Servers.
Listing W: SQLStandAlone view
SQLAllServers
The SQLAllServers view combines views SQLClusterActiveActive, SQLClusterActivePassive, and SQLStandAlone with a union statement for usability.
Listing V: SQLAllServers view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[SQLStandAlone]
as
select
SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])) SQLServerName
,[instance name]
,[Computer Name]
,[Machine Type]
,[Physical Machine Name]
,[SQL Server Product Name]
,[SQL Server Edition]
,[SQL Server Version]
,[SQL Service Pack Level]
-- ,[Instance Name]
,[Operating System]
,[Operating System Architecture Type]
,[CPU]
-- ,[Machine Serial Number]
,[Number of Processors]
,[Number of Total Cores]
,[Number of Logical Processors]
,[Number of Host Processors]
,[Clustered?]
-- ,[SQL Service State]
,[IP Address]
,[WMI Status]
from tempservermap where [clustered?] like 'no'
GO
vServerDetails
The vServerDetails view preps the SQL Server data from the SQLAllServers view into more readable column names and parses and create columns for SQL Server versions so they can be used more easily in mathematical equations.
Listing X: vServerDetails view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vServerDetails]
AS
SELECT SQLServerName AS ServerName, [instance name] AS InstanceName, [Computer Name] AS ComputerName, [Machine Type] AS MachineType,
[SQL Server Product Name] + ' ' + [SQL Server Edition] + ' ' + 'Edition' AS SQLVersion, [SQL Server Product Name] AS SQLServerProductName,
[SQL Server Edition] AS DBMSVersion, [SQL Server Version] AS ProductNumber, [SQL Service Pack Level] AS ServicePack, [Operating System] AS OperatingSystem,
[Operating System Architecture Type] AS OSArchitecture, CPU, [Number of Processors] AS Processors, [Number of Total Cores] AS Cores,
[Number of Logical Processors] AS LogicalProcessors, [Number of Host Processors] AS HostProcessors, [Clustered?] AS isClustered,
CASE LEFT(CONVERT(varchar(255), [SQL Server Version]), 1) WHEN '8' THEN CONVERT(int, parsename([SQL Server Version], (3))) ELSE CONVERT(int,
parsename([SQL Server Version], (4))) END AS cVersion, CASE LEFT(CONVERT(varchar(255), [SQL Server Version]), 1) WHEN '8' THEN CONVERT(int,
parsename([SQL Server Version], (2))) ELSE CONVERT(int, parsename([SQL Server Version], (3))) END AS cSubVersion, CASE LEFT(CONVERT(varchar(255),
[SQL Server Version]), 1) WHEN '8' THEN CONVERT(int, parsename([SQL Server Version], (1))) ELSE CONVERT(int, parsename([SQL Server Version], (2)))
END AS cSPLevel
FROM dbo.SQLAllServers
GO
vSQLSupportServicePack
The vSQLSupportServicePack view pulls a subset of data back from the SQLSupportServicePack with one valueble addition--it adds a row_number to the mix to be used in calculations to count servers in our cluster environments.
Listing Y: vSQLSupportServicePack view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vSQLSupportServicePack]
as
select row_number() over(order by FK_SQLSupport, servicepack) as row,
[ServicePack]
,[ServicePackNum]
,[StartSupportDate]
,[EndSupportDate]
,[FK_SQLSupport]
FROM [SQLSunSet].[dbo].[SQLSupportServicePack]
GO
vSQLSupport:
The vSQLSupport view combines the tables SQLSupport with SQLSupportServicePack to reveal a denormalized yet informative view of the data.
Listing Z: vSQLSupport view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--sp_refreshview vsqlsupport
CREATE VIEW [dbo].[vSQLSupport]
AS
SELECT SS.SQLServer, SS.LifeCycleStartDate AS SQL_LifeCycleStartDate, SS.MaintstreamSupportEndDate AS SQL_MaintstreamSupportEndDate,
SS.ExtendedSupportEnddate AS SQL_ExtendedSupportEndDate, SS.Version AS SQL_Version, SS.SubVersionStart AS SQL_SubVersionStart,
SS.SubVersionEnd AS SQL_SubVersionEnd, SP.ServicePack, SP.StartSupportDate AS SP_StartSupportDate, SP.EndSupportDate AS SP_EndSupportDate,
SP.ServicePackNum AS SP_VersionStart,
(SELECT ServicePackNum - 1 AS Expr1
FROM dbo.vSQLSupportServicePack AS r
WHERE (row = SP.row + 1) AND (SP.FK_SQLSupport = FK_SQLSupport)) AS SP_VersionEnd, SP.FK_SQLSupport
FROM dbo.SQLSupport AS SS FULL OUTER JOIN
dbo.vSQLSupportServicePack AS SP ON SS.PK = SP.FK_SQLSupport
GO
vSQLSupportMaxServicePack
The vSQLSupportMaxServicePack view calculates the maximum service pack level for each SQL Server version.
Listing AA: vSQLSupportMaxServicePack view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vSQLSupportMaxServicePack]
as
select distinct t.sqlserver, t.servicepack, [SQL_ExtendedSupportEndDate],t.sql_version, t.sql_subversionstart,t.sql_subversionend from vsqlsupport v
join
(
SELECT [SQLServer],sql_version, sql_subversionstart,sql_subversionend,max([ServicePack]) as servicepack
FROM [SQLSunSet].[dbo].[vSQLSupport]
group by sqlserver,sql_version, sql_subversionstart,sql_subversionend
) t
on v.sqlserver = t.sqlserver
GO
vSQLSunsetReport
The vSQLSunsetReport view combines vServerDetails, vSQLSupport to produce the final SSRS report(s).
Listing BB: vSQLSunsetReport view
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[vSQLSunsetReport]
as
SELECT TOP 100 percent
[ServerName],
[InstanceName],
v.SQLServer,
s.dbmsversion,
v.sql_maintstreamsupportenddate,
v.sql_extendedsupportenddate,
v.sp_versionstart,
v.sp_versionend,
v.sp_endsupportdate,
[ProductNumber]
,[cVersion]
,[cSubVersion]
,[cSPLevel]
,v.servicepack
,vm.SQL_ExtendedSupportEndDate LatestSupportDate
,vm.servicepack LatestServicePack,
case
when v.sql_extendedsupportenddate< getdate() and v.sql_extendedsupportenddate is not null then 0
else 1
end SQLCompliance,
case when v.sp_endsupportdate < getdate() and v.sp_endsupportdate is not null then 0
else 1
end SPCompliance
FROM [SQLSunSet].[dbo].[vServerDetails] S left
join vSQLSupport V
on v.sql_version=s.cversion and s.csubversion between v.sql_subversionstart and v.sql_subversionend
and s.cSPLevel between isnull(v.sp_versionstart,0) and isnull(v.sp_versionend,v.sp_versionstart)
left join vSQLSupportMaxServicePack VM on
vm.sql_version=s.cversion and s.csubversion between vm.sql_subversionstart and vm.sql_subversionend
-- where servername like 'MCSQLTEST1'
--where sp_endsupportdate <=getdate()
-- ORDER BY v.sql_extendedsupportenddate desc,(CASE WHEN v.sp_endsupportdate IS NULL THEN 1 ELSE 0 END),V.sp_endsupportdate desc
GO
SQL Server Procedures
We only have one stored procedure spImportMapSQLServers that we used to import Microsoft MAP spreadsheet data into the TempServerMaps table.
spImportMapSQLServers
The spImportMapSQLServers stored procedure Imports the Excel SQL Server usage tracking report into the TempServerMaps table. We use Microsoft.Ace.OLEDB.12.0 installed from the Microsoft Office engine we installed earlier.
Listing CC: spImportMapSQLServers stored procedure
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[spImportMapSQLServers]
@MapsExcelFile_SQLServerUsageTracker varchar(1000)
as
declare @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET (''Microsoft.Ace.OLEDB.12.0'', ''Excel 12.0;Database=<MapsExcelFile_SQLServerUsageTracker>'', ''select * from [SQL Server Instance Details$]'')'
set @cmdstring = replace(@cmdstring,'<MapsExcelFile_SQLServerUsageTracker>',@MapsExcelFile_SQLServerUsageTracker)
if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap
--print (@cmdstring)
exec (@cmdstring)
GO<font face="Consolas" size="2"><font face="Consolas" size="2"> </font></font>
SQL Server Functions
We use the following functions to display numbers or percentages directly on our SSRS main report:
- BadVersion_BadSP
- GoodVersion_BadSP
- GoodVersion_GoodSP
- MaxServers
BadVersion_BadSP
The BadVersion_BadSPSQL function finds SQL Servers with versions and service packs that are not supported.
Listing DD: BadVersion_BadSP function
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[BadVersion_BadSP]()
returns int
as
begin
declare @BadVersion_BadSP int
select @BadVersion_BadSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where sql_extendedsupportenddate <= getdate()
return(@BadVersion_BadSP)
end
GO
GoodVersion_BadSP
The GoodVersion_BadSP SQL function finds SQL Servers with good versions but with service packs that are not supported.
Listing EE: GoodVersion_BadSP function
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[GoodVersion_BadSP]()
returns int
as
begin
declare @GoodVersion_BadSP int
select @GoodVersion_BadSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where sp_endsupportdate <= getdate() and sql_extendedsupportenddate > getdate()
return(@GoodVersion_BadSP)
end
GO
GoodVersion_GoodSP
The GoodVersion_GoodSP SQL function finds SQL Servers with both acceptable versions and service packs.
Listing FF: GoodVersion_GoodSP function
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[GoodVersion_GoodSP]()
returns int
as
begin
declare @GoodVersion_GoodSP int
select @GoodVersion_GoodSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where (sp_endsupportdate > getdate() or sp_endsupportdate is null) and (sql_extendedsupportenddate > getdate() or sql_extendedsupportenddate is null)
return(@GoodVersion_GoodSP)
end
GO
MaxServers
The MaxServers function returns the server count of a cluster given the computer name that is supplied. Given the environment where I work, this is a count it a calculation that works because we name our clustered servers based of a standard naming convention. This count may not be accurate if your naming convention differs from ours--as previously mentioned.
Listing GG: MaxServers function
USE [SQLSunSet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[maxservers](@computername varchar(255))
returns int
as
begin
declare @maxservers int
select @maxservers= count(*) from
(
select distinct [computer name] as dummy ,SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])-1) as [computer name]
from tempservermap where [instance name] not like 'mssqlserver' and [clustered?] like 'yes'
) t1
group by [computer name] having charindex([computer name],@computername) >0
return(@maxservers)
end
GO
Useful Links that assisted us
Getting all the software components to work in this article was initially a challenge as this was the first time we really worked with Power Shell. We found the following links useful for getting the Excel import to work with SQL Server and the SQL components to work with Power Shell. We hope you may find these links useful as well.
Excel/ 'Microsoft.ACE.OLEDB.12.0' provider:
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
Power Shell:
Getting invoke-sqlcmd to work:
http://blog.sqlauthority.com/2014/11/23/sql-server-fix-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet/
http://stackoverflow.com/questions/12521239/powershell-does-not-recognize-invoke-sqlcmd
http://serverquestions.com/questions/dzlu/invoke-sqlcmd-not-working-after-powershell-4-install
Getting DLLs and Snapins to work:
http://guidestomicrosoft.com/2015/01/13/install-sql-server-powershell-module-sqlps/
https://markcz.wordpress.com/2009/12/19/how-to-register-powershell-sql-server-snapin-in-windows-preinstalled-powershell/
https://msdn.microsoft.com/en-us/library/hh231286.aspx?f=255&MSPPError=-2147217396
Conclusion
In this article we showed you how to gather data about SQL Servers in your environment and display the information in an SSRS report. We believe that this report shows some of the most complete information you'll see about your SQL Servers as it relates to collecting, displaying, and aggregating your SQL Server information, SQL editions and service packs. We showed you a quick way to gather all this information in minutes using SQL Server 2014, Power Shell, SQL Server Reporting Services (SSRS), Excel, and the Microsoft Assessment and Planning Toolkit (MAP).
