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
Recently, I found a neat little trick using dbatools to update service accounts for SQL Server service and agent. It is generally a good security practice to run the services under a dedicated domain account. The default local system account has more privileges than necessary. Great thing about this method is that it is similar to updating service account using SQL Server Configuration Manager. The process will grant all the minimum necessary permissions to the service account and you don’t need to do a thing!
Soon after I ran this, I got an error that the account could not be set. I was able to revert the account back to local system account and start back the services. Now I went onto investigate the root cause for the error. I also ensured that the account is not locked out, password is accurate. Finally I looked at the System logs under event view logs on the server. Bingo, now we are getting somewhere. This is the error I saw:
The MSSQLSERVER service was unable to log on as domain\ServiceAccount with the currently configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.
Service: MSSQLSERVER
Domain and account: domain\ServiceAccount
This service account does not have the required user right "Log on as a service."
User Action
Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.
If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.
I opened the security policy on the server (run secpol.msc) and found that the account was not present in the User policy named ” Log on as a service “. After talking to the systems engineer, I found out that the server is managed through a GPO. We had them update the GPO to include the service account in the policy. Once the GPO took effect, we were able to run the above dbatools command and set the service account successfully.
Obviously, this process of setting service account also requires that you restart the services in order for the change to take effect. Hope you enjoyed this!
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.
Database Blog 