Windows Firewall Install Rule

Which Windows Defender Firewall profile causes the Windows Firewall rule warning?

It is the Public Profile.

If this profile is disabled, the warning does not show.

So the next question; when should the Windows firewall be disabled?

Answer: Never; unless you have other another firewall solution. Rather add an appropriate access rule. 

What Determines the Installation Collation?

When installing SQL Server, why do you sometimes see that the Database Engine defaults to the SQL_Latin1_General_CP1_CI_AS collation and other times another value?

This value is determined by the system locale which can be found via Control Panel -> Clock and Region -> Region -> Administrative -> Language for non-Unicode programs.

Here in New Zealand we typically use the Latin1_General_CI_AS collation.

When the system locale is changed to “English (New Zealand)” a prompt to restart Windows is received.

After a restart, the result of this change means that the SQL Server installation will now default to Latin1_General_CI_AS.

SQL Server Supported Operating Systems

Below is a quick reference guide of operating systems supported by the various SQL Server versions

Windows

Windows Server20082008 R220122012 R2201620192022
SQL Server 2005
SQL Server 2008✔ 7✔ 5
SQL Server 2008 R2✔ 6✔ 4
SQL Server 2012✔ 3✔ 2
SQL Server 2014✔ 9✔ 8✔ 1
SQL Server 2016✖ 10
SQL Server 2017
SQL Server 2019
  1. Requires SQL Server 2014 SP1 or later ref: here
  2. Requires SQL Server 2012 SP2 or later ref: here
  3. Requires SQL Server 2012 SP1 or later ref: here
  4. Requires SQL Server 2008 R2 SP2 or later ref: here
  5. Requires SQL Server 2008 SP3 or later ref: here
  6. Requires SQL Server 2008 R2 SP1 or later ref: here
  7. Requires SQL Server 2008 SP3 or later ref: here
  8. Requires Windows Server 2008 R2 SP1 or later ref: here
  9. Requires Windows Server 2008 SP2 or later ref: here
  10. Reporting Services – SharePoint & Reporting Services Add-in for SharePoint products can both be installed on Windows Server 2008 R2 SP2 or later ref: here

Linux


SQL Server 2017SQL Server 2019
Red Hat Enterprise Linux 7.3, 7.4, 7.5, or 7.6 Server
Red Hat Enterprise Linux 8 Server
SUSE Enterprise Linux Server v12 SP2
SUSE Enterprise Linux Server v12 SP3, SP4, or SP5
Ubuntu 16.04LTS
Ubuntu 18.04
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here

Docker

SQL Server 2017SQL Server 2019
Docker Engine 1.8+ on Windows, Mac, or Linux
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here

SQL Server Version Feature Selections Reference Guide

Below shows a quick reference guide of the feature selections presented based on the version of SQL Server being installed on Windows.

SQL Server 2022

  • Instance Features
    • – R
    • – Python
    • – Java
    • – Java connector for HDFS data sources
  • Shared Features
    • – Machine Learning Server (Standalone)
      • – R
      • – Python
    • – Client Tools Connectivity
    • – Client Tools Backwards Compatibility
    • – Client Tools SDK
    • – Distributed Replay Controller
    • – Distributed Replay Client
    • – SQL Client Connectivity SDK

SQL Server 2019

  • Instance Features
    • + Java connector for HDFS data sources

SQL Server 2017

  • First SQL version to support being installed on Linux
  • First SQL version to not include Reporting Services media
    (can be found here)
  • First SQL version to not include Documentation media (now online only here)
  • Instance Features
    • + Machine Learning Services (In-Database)
    • + R
    • + Python
    • – R Services (In-Database)
    • – Reporting Services – Native
  • Shared Features
    • + Machine Learning Server (Standalone)
    • + R
    • + Python
    • + Scale Out Master
    • + Scale Out Worker
    • – R Server (Standalone)
    • – Reporting Services – SharePoint
    • – Reporting Services Add-in for SharePoint Products
    • – Document Components

SQL Server 2016

  • First SQL version to use 64-bit media only
  • First SQL version to not include SSMS media (can be found here)
  • .Net 3.5 no longer required as a prerequisite
  • Instance Features
    • + R Services (In-Database)
    • + PolyBase Query Service for External Data
  • Shared Features
    • + R Server (Standalone)
    • – Management Tools – Basic
    • – Management Tools – Complete

SQL Server 2014

  • First SQL version to not include Data Tools media (can be found here)
  • Shared Features
    • – SQL Server Data Tools

SQL Server 2012

  • Instance Features
    • + Reporting Services – Native
    • – Reporting Services
  • Shared Features
    • + Reporting Services – SharePoint
    • + Reporting Services Add-in for SharePoint Products
    • + Data Quality Client
    • + SQL Server Data Tools
    • + Document Components
    • + Distributed Replay Controller
    • + Distributed Replay Client
    • + Master Data Services
    • – Business Intelligence Development Studio
    • – SQL Server Books Online

SQL Server 2008 R2

  • No feature changes from SQL Server 2008

SQL Server 2008

  • First SQL version where Reporting Services is not dependent on IIS
  • First SQL version to not include sample databases as option (various sample media links can be found here)
  • Instance Features
    • + Reporting Services
    • – Notification Services
  • Shared Features
    • + Client Tools Connectivity
    • + Client Tools Backwards Compatibility
    • + Client Tools SDK
    • + Management Tools – Basic
    • + Management Tools – Complete
    • + SQL Client Connectivity SDK
    • + Microsoft Sync Framework
    • – Connectivity Components
    • – Management Tools
    • – Software Development Kit
    • – SQLXML Client Features
    • – Legacy Components
    • – Sample Databases
    • – AdventureWorks Sample OLTP
    • – AdventureWorksDW Sample Data Warehouse
    • – AdevntureWorks Sample OLAP
    • – Sample Code and Applications

SQL Server 2005

continued
continued

SQL Server Mobile Report Publisher install

If you come across the following error when attempting to install SQL Server Mobile Report Publisher
image1
you’ll likely find that the currently link doesn’t work
image2
At this time the requirement can be found here. Note: After the install you may be requested for the server to be restarted. You don’t have to, but it’s a good idea to do so.
 
The install then should go smoothly.
image3
Given that Visual Studio 2017 is now out, we’ll hopefully be seeing this legacy 2013 requirement removed.

Dubious default settings

The installation process of SQL Server is continually evolving.
 
Recently the setup allowed tempdb file adjustments with a default of 4 files and the ability to grant the Perform Volume Maintenance Task privilege.
 
Such a focus on tempdb was a surprise to me as in the 13+ years I’ve worked with SQL Server (tackling literally tens of thousands of instances) there was only once when more than 1 data file was required. To put this in perspective, the SQL Server was accessed by all students at a large university and the tempdb GAM contention was obviously clear. Adding another single data file (2 in total) to this high spec server resolved the issue.
 
Adding additional tempdb files, to me is an advanced feature and should be carefully considered so as to not waste disk space, ensure even file growths, minimize complexity etc.
 
In my view it would have been more productive for Microsoft to have focused the setup on the following:

  • set memory limits (this has to be done 100% of the time) and don’t stop with the engine, include SSAS and SSRS
  • set max degree of parallelism (I believe the default of 0 is incorrect, rather it should be 1 as most instances are not dedicated to a hosting a single application due to licensing costs,consolidation etc)
  • set backup compression (should be defaulted to 1)
  • set optimize for ad hoc workloads (should be defaulted to 1)
  • allow setting Perform Volume Maintenance Tasks (tick)
  • allow setting Lock Pages in Memory
  • allow enabling remote admin connections
  • allow setting the listening port and adding the appropriate firewall entry (this would likely save thousands of can’t connect issues)

SQL Server 2017 CTP 2.1 Feature Selection

As mentioned, Reporting Services has now been extracted from the available features that can be installed.
 
Below is a screen shot from CTP 2.1
image1
Below is a screen shot from CTP 2.0
image2
Again, I suspect this is a backward step. I’d rather have SSMS, RS as part of the installer. Then allow the end-user to apply patches to get it up to date. Sending users to all parts of web to install SQL sounds crazy to me.

SQL Server 2017 CTP 2.1 released

The release announcement is here.
 
Interestingly,
“In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license. The new installation process keeps our packages lean and enables customers to deploy and update Reporting Services with zero impact on your SQL Server deployments and databases.”
 
As mentioned previously, installs look to be getting a lot more complicated. I can see why Microsoft would do this for easier internal development, but for an end-user experience, this is likely not ideal.
 
On a side note ~11 months ago SQL Server 2016 CTP 2.1 was released :)

SQL Server Management Studio 17.0 released

It took a while, but finally SQL Server Management Studio 17.0 is available for download.
 
At 729MB the download exe is smaller than the 16.5.3 version which was 898MB. This huge size difference could mainly be due to the PowerShell module being moved into the PowerShell gallery. Just imagine attempting to install this via the good old 1.44 floppy disks.
 
It seems that these days to install SQL Server you’ll spend a lot of time trying to locate the pieces to install i.e. installation media + ssms + powershell + snac + patches etc, that all adds up to a lot of links. I kind of liked the old install where everything was in one place, but times are definitely a changing.
 
Extract from the SSMS download site:
•Over 100 connect issues fixed
•SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery
•Icons updated to support high-DPI display modes
•Numerous performance improvements in Object Explorer
•WSUS support for upgrading to future 17.X versions
•Improved Multi-factor authentication support
•Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more)
•Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues
•SQL Server Analysis Server 1400 support
•New DAX Query Windows
•Numerous other enhancements for Power Query support