SQL Server 2017 to 2019 Upgrade – Error and lesson learned

When I was upgrading my SQL Server 2017 instance to SQL Server 2019 from a mounted ISO file, I encountered an error message during the upgrade process, “An error occurred for a dependency of the feature causing the setup process for the feature to fail.”. You can see the error in the illustration below.

At first, it was not clear what the cause of this error was. First, I looked at the summary log by clicking on the hyperlink provided by the above window to look for more information. Scrolling down in the log, I found several errors related to failed components:

After some google search, it seems to be some sort of bug with SQL 2019 setup and issue is related to pre-installed SSMS.

However, in the end, it turned out to be an issue with the SQL Server Management Studio (SSMS) versions 18.0, 18.1, 18.2 and 18.3, if they were preinstalled on the server before attempting to install SQL Server 2019.

Solution

What worked for me was taking the content of the mounted SQL Server 2019 ISO file and copying it to a local drive. After this, I restarted the installation ,and it worked without any problems.

This was found in Microsoft documentation:

We have below workarounds:

  • On existing machines, where SSMS 18.3 or lower is already installed.
    • Uninstall SQL Server 2012 Native Client which got installed with SSMS. Then install SQL Server 2019. OR
    • Instead of running setup from mounted ISO, copy the content of media to a local drive or a network share and start setup.exe from those locations.
  • On new machines, where SSMS is NOT installed.
    • First install SQL Server 2019 and then install SSMS 18.3 OR
    • Install version of SSMS greater than 18.3.1 and then install SQL Server 2019

Performance Decoder Ring

Get your top wait type with sp_BlitzFirst @SinceStartup = 1

If your top wait type isThen use @SortOrder =
CXPACKETCPU and Reads separately
SOS_SCHEDULER_YIELDCPU
THREADPOOLCPU
LCK_**Duration
RESOURCE_SEMAPHOREMemory grant
PAGEIOLATCH_**Reads
WRITELOGWrites

One query many plans – SQL Server

When you see this happening in your system. Here is the script that uses system DMVs to look at the cache for execution plans:

SELECT 
cplan.usecounts,
qryplan.[query_plan],
sqltxt.text,
planobj.pages_in_bytes / 1024 AS [PlanKB],
cplan.plan_handle,
cplan.cacheobjtype,
cplan.objtype
FROM sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_test(cplan.[plan_handle] sqltxt
OUTER APPLY sys.dm_exec_query_pla(cplan.[plan_handle] qrypln
INNER JOIN sys.dm_os_memory_ibjects planobj
ON planobj.memory_object_address = cplan.memory_object_address
WHERE cplan.parent_plan_handle is NULL
AND cplan.cacheobjtype IN (N'Compiled Plan',N'Compiled Plan Stub')
ORDER BY cplan.objtype, cplan.plan_handle;

You see the many plans scenario when dynamic SQL is being generated by ORM. It would help if you can convert functions or queries into a stored procedure so that fewer plans get cached into the system and get re-used.

Advantages of Kerberos over NTLM authentication

Kerberos provides several advantages over NTLM:
– More secure: No password stored locally or sent over the net.
– Best performance: improved performance over NTLM authentication.
– Delegation support: Servers can impersonate clients and use the client’s security context to access a resource.
– Simpler trust management: Avoids the need to have p2p trust relationships on multiple domains environment.

– Supports MFA (Multi Factor Authentication)

NTLMv1 hashes could be cracked in seconds with today’s computing since they are always the same length and are not salted.

NTLMv2 is a little better, since it variable length and salted hash, but not that much better. Even though hash it`s salted before it`s sent, it`s saved unsalted in a machine’s memory.
And off course, when we talk about NTLM, we talk about a challenge/response mechanism, which exposes its password to offline cracking when responding to the challenge.

What are the Kerberos dependencies?

Both the client and the server need to be running W2k or latter versions and be on the same, or trusted domain.

A SPN needs to exist in the AD for the domain account in use to run the service in which the client is authenticating.

What is NTLM?

NTLM is an authentication protocol. It was the default protocol used in old windows versions, but it’s still used today. If for any reason Kerberos fails, NTLM will be used instead.

NTLM has a challenge/response mechanism.

What is Kerberos?

Kerberos is an authentication protocol. It’s the default authentication protocol on Windows versions above W2k, replacing the NTLM authentication protocol.