Beware: Compatibility Level 90 is no longer available in SQL 2014

This is a quick, perhaps Level 200 post – a rarity on my blog Smile but I am seeing this so often that I think it important to get this out there.

Those who are running databases with compatibility level 90 in SQL Server 2008 / 2012 need to be aware that there is no more support for level 90 in SQL Server 2014. The SSMS 2014 UI is a bit confusing in this respect, because it does show you the option to set level as 90:

image

But when you try to actually change this to 90, it fails:

Msg 15048, Level 16, State 3, Line 1
Valid values of the database compatibility level are 100, 110, or 120.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

This information is indeed documented in Books Online: “When a database is upgraded to SQL Server 2014 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 100. Upgrading a database with a compatibility level of 90 sets the database to compatibility level 100. “

Call to action to everyone who plans to upgrade to SQL Server 2014 and is running a database on compatibility level 90 is to thoroughly test with compatibility level as 120 (or perhaps 110 if you feel the changes in the cardinality estimator will warrant more testing in your workload case) before you upgrade. Both functional and stress testing must be done. Once the testing is signed off, you can confidently do the upgrade in production with the higher compatibility levels – 120 (recommended; subject to testing outcome) or 110.

My team of Premier Field Engineers within Microsoft Services can also be of help to your upgrade efforts and help detect and mitigate risks related to upgrading to the current version. Do engage us if you need help!

SQL 2012 Setup Rules – The ‘Missing Reference’

Troubleshooting SQL Server setup can be a ‘dark science’ at times. There is overall documentation on where to look, and some interesting articles on the Support website. But other times, you might be left clueless on the root cause of failures, or why you are receiving a specific error message.

Situation

For example, one of my customers was adding a node (running Windows 2012 and SQL 2012 SP1) to an existing cluster. They were receiving a specific message when adding the node: “You have selected a feature that is not supported on Windows Server Core. The supported features on Windows Server Core are: Database Engine Services, SQL Server Replication, Full-Text and Semantic Extractions for Search, Analysis Services, Client Tools Connectivity, Integration Services, and SQL Client Connectivity SDK.”

With a bit of probing we understood what was going on: the second node (this was part of a test setup) was setup as a Windows Core setup (no GUI) while the first node was a full edition. Now, we do NOT recommend such mixed setups but given that this was a test setup, we wanted to unblock them and skip the rule. But which rule? There is no documentation on the rule name (which is what the SkipRules parameter takes.)

The Ask

With a bit of looking in the log files, we found that the relevant rule name is ServerCoreBlockUnsupportedFeaturesCheck. But the question from my customer was two-fold:

  • Can we get a list of all the rules, proactively so that we can ensure some basic things are out of the way before we even start running setup?
  • In the worst case scenario, if we do need to skip a rule, which rule name should that be?

It turns out that in SQL 2008 R2, we have a good list of all the rules, neatly categorized into their groups. However, we do not have an updated version of that for SQL Server 2012.

The Answer

If we look at the C:Program Files (x86)Microsoft SQL Server110Setup BootstrapLog<timestamp>DatastorePackage.xml file you will find that it is a rich source of information on all the rules and their rule groups which would run during setup. Not all rules are always applicable; some are specific to scenarios like Failover Cluster installation, Upgrade etc.

But that in itself is not enough. For looking at more descriptive information on the rules, you have to look at the SystemConfigurationCheck_Report.htm file described in KB article 955396. The rule ‘name’ mentioned therein would be the one you would, for example, pass to /SkipRules.

I’ve also listed all the rules evaluated in SQL 2012 in an Excel sheet – I hope that this list will make up for the lack of official documentation (SQL 2008 R2 was the last time this kind of list was made available as I mentioned previously.) The spreadsheet is quite self-explanatory, and I’ve included a screen shot of an extract here:

image

Hope this helps you somehow! If you can share your experiences or have additional questions please do let me know!

How to assign a static port to a SQL Server named instance – and avoid a common pitfall

While Books Online clearly mentions the steps to Configure a Server to Listen on a Specific TCP Port we still see people missing out on one small but important detail in these steps: they forget to delete the entry (0 or some random port) for dynamic port. This firstly results in confusion and occasionally can result in connectivity problems as well. Let me explain how using an example from one of our lab setups.

As a first step, let’s see what the ‘administrator’ (in this case, yours truly Smile) had done:

image

As you can see, they have added the static port for ‘IPAll’ with a value of 1450. That part is fine. The problem though is they forgot to remove the entries for the dynamic ports (0 or some random port). That means that when they restarted SQL, the dynamic port setting is still valid. In fact if we query sys.tcp_endpoints, you will still see the engine thinks it is listening on dynamic port:

SELECT        name, protocol_desc, type_desc, state_desc, is_admin_endpoint, port, is_dynamic_port, ip_address 
FROM            sys.tcp_endpoints

gives us:

name protocol_desc type_desc state_desc is_admin_endpoint port is_dynamic_port
Dedicated Admin Connection TCP TSQL STARTED 1 0 1
TSQL Default TCP TCP TSQL STARTED 0 0 1

The important observation is that the engine reports that it is still using a dynamic port. It does not report the static port number 1450 which we selected in Configuration Manager. Let’s double-check in the errorlog to see if indeed the static port is being picked up at all. And lo and behold:

Server is listening on [ 'any' <ipv6> 1450]. 
Server is listening on [ 'any' <ipv4> 1450]. 
Server is listening on [ 'any' <ipv6> 49626]. 
Server is listening on [ 'any' <ipv4> 49626].

In our case, sqlservr.exe has a PID of 1240. Using the command netstat –ano, we can see what it is listening on.

 Proto  Local Address          Foreign Address        State           PID 
  TCP    0.0.0.0:1450           0.0.0.0:0              LISTENING       1240 
  TCP    0.0.0.0:49626          0.0.0.0:0              LISTENING       1240 
  TCP    127.0.0.1:49627        0.0.0.0:0              LISTENING       1240 
  TCP    192.168.1.101:1450     192.168.1.200:49386    ESTABLISHED     1240 
  TCP    192.168.1.101:1450     192.168.1.200:49396    ESTABLISHED     1240 
  TCP    [::]:1450              [::]:0                 LISTENING       1240 
  TCP    [::]:49626             [::]:0                 LISTENING       1240 
  TCP    [::1]:49627            [::]:0                 LISTENING       1240

So it is not only listening on the static port, but also on the dynamic port 49626. The DAC is listening on TCP port 49627. The values with a local address of [::] are the IPv6 ‘All’ address.

So depending on what got cached earlier in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect on the client (you can find some detail about the LastConnect registry key and the caching mechanism in this KB article), the client might attempt to connect to the previous dynamic port (which is still valid based on our observation above.)

FYI, if we run NetMon, we can see that the SSRP response from the SQL Browser correctly gives back 1450 as the port for this named instance:

image

For clarity I’ve reproduced the response from SQL Browser (using the SSRP protocol) back to my client (SQLCMD.exe):

.^.ServerName;SOMESERVER;InstanceName;SOMEINSTANCE;IsClustered;No;Version;11.0.2100.60;tcp;1450;;

From the above it is clear that SQL Browser is correctly sending the static port assignment. But if you are like me, I feel uneasy till I fix the root cause, which is to delete the dynamic port assignment!

To summarize here is what we saw in this walkthrough:

  1. The official steps (captured in Books Online) to assign a static port for a named instance involve also deleting the value (0 or some random port) for the dynamic port.
  2. Failure to delete the dynamic port value in SQL Configuration Manager will cause SQL to listen on both the static as well as the dynamic ports.
  3. This means that clients will succeed to connect to the erstwhile dynamic port if they had that cached in the LastConnect client side registry key.
  4. For clients which do not have cached connection details, SQL Browser seems to pickup the static port and sends that back to the client.
  5. So follow the steps in the BOL article to the T and delete the dynamic port value right after you type in the static port value, and in any case before you restart the instance.

FYI the steps to fix a static port for the Dedicated Admin Connection (DAC) are in the KB article How to configure SQL Server to listen on a specific port under the section ‘Configuring an instance of SQL Server to use a static port’.

I hope you enjoyed reading this post as much as I enjoyed bringing it to you! Please take a minute to rate this post and leave some comments!