none
Unable to add a new software update group after a DB Move & Migration of SQL Server Version RRS feed

  • Question

  • Hello,

    After moving the SCCM database that was hosted on the same SCCM site server (SQL Server 2014) to another server (SQL Server 2017 Cluster), I had a problem creating a new group of software updates.
    For your information :

    - the group contains only one update.
    - TRUSTWORTHY is ON
    - the data base is owned by the SA account
    - The Isolation Level is set to READ_COMMITTED_SNAPSHOT

    Error message :

    ConfigMgr Error Object:
    instance of SMS_ExtendedStatus
    {
    Description = "There is a failure while generating lantern documents for this configuration item";
    ErrorCode = 1078462256;
    File = "e:\\qfe\\nts\\sms\\siteserver\\sdk_provider\\smsprov\\sspconfigurationitem.cpp";
    Line = 2154;
    Operation = "PutInstance";
    ParameterInfo = "";
    ProviderName = "ExtnProv";
    StatusCode = 2147749889;
    };

    -------------------------------
    Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryException
    The SMS Provider reported an error.


    Stack Trace:
       at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
       at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
       at Microsoft.ConfigurationManagement.AdminConsole.AuthorizedUpdates.CreateUpdateGroup.saveWorker_DoWork(Object sender, DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    -------------------------------

    System.Management.ManagementException
    Generic failure 


    Stack Trace:
       at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
       at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
       at Microsoft.ConfigurationManagement.AdminConsole.AuthorizedUpdates.CreateUpdateGroup.saveWorker_DoWork(Object sender, DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    -------------------------------

    Thank you in advance for your help.
    cordially

    Sunday, November 17, 2019 8:52 AM

Answers

  • Hi,

    What is the status of your SCCM components? Check it under Monitoring>System Status>Components Status.

    Did you have encounter any error message during the SQL DB migration?

    Check the SMSProv.log and smsdbmon.log log files for more details.

    Regards,

    SAAD Youssef

    ______

    Please remember to mark the replies as answer if they help, thank you!

    • Marked as answer by Hadjer YH Sunday, November 17, 2019 2:45 PM
    Sunday, November 17, 2019 11:02 AM
  • "Failed to get a SQL Server connection.  Either the SQL Server is not running, or all connections have been used."

    Based on the error message, there is an issue with the SQL server connection, which type of cluster are you using with SQL? Always On... ?

    Make sure that SCCM can connect to the SQL Server cluster and the following ports are allowed:

    • Site Server ==> SQL Server:
            1433, 135 and 445

    More details: https://docs.microsoft.com/en-us/configmgr/core/plan-design/hierarchy/ports

    Regards,

    SAAD Youssef

    • Marked as answer by Hadjer YH Monday, November 18, 2019 8:41 AM
    Sunday, November 17, 2019 9:31 PM
  • How *exactly* did you move the database?

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Monday, November 18, 2019 8:41 AM
    Monday, November 18, 2019 3:16 AM
  • That's not sufficient. You need to run a ConfigMgr site recovery as well. ConfigMgr must know that there is a change in location and must also install the necessary site system components on the systems hosting the SQL instance.

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Monday, November 18, 2019 2:50 PM
    Monday, November 18, 2019 2:47 PM
  • Yeah, that's correct. Why are you still running ConfigMgr 2012?

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Tuesday, November 19, 2019 7:22 AM
    Monday, November 18, 2019 3:04 PM
  • Hi,

    We can upgrade from sccm 2012 to sccm 1802 by 1802 media first, and upgrade sccm 1802 to 1902 in the console. Then operate the SQL Server upgrade.

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hadjer YH Wednesday, November 20, 2019 10:26 AM
    Wednesday, November 20, 2019 9:01 AM
  • Sorry i can't post images or links !

    What shoud i do please ?

    Regards.

    This blog will help. https://www.enhansoft.com/problems-posting-pictures-and-images-or-urls-to-the-technet-forums/

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 9:08 AM
    Wednesday, November 20, 2019 11:24 AM
    Moderator
  • > I could not migrate the SCCM before the base because the 1902 version does not support the SQL 2012.

    This is not a correct statement. See https://docs.microsoft.com/en-us/configmgr/core/plan-design/configs/support-for-sql-server-versions

    Also, as noted, 1802 is not supported anymore so using it for anything is a poor choice.


    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 3:45 PM
    Wednesday, November 20, 2019 2:48 PM
  • Hi,

    Thank you very much for the update. I'm glad the problem is solved now. And sorry for my negligence that SCCM CB 1802 is end support on September 22, 2019. Here's a short summary for the problem.

    Problem/Symptom:
    ===================
    After moving the SCCM database that was hosted on the same SCCM site server (SQL Server 2014) to another server (SQL Server 2017 Cluster), have a problem creating a new group of software updates.

    Solution:
    ===================
    The problem is resolved by changing the language of the registry of the service account that is running the SQL Server services. After the database migration, upgrade SCCM 2012 R2 to SCCM current branch 1902 and everything is OK now.

    However, it is recommend to upgrade SCCM first and then upgrade SQL Server(SCCM 1902 support SQL 2012).

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 3:54 PM
    Thursday, November 21, 2019 8:59 AM

All replies

  • Hi,

    What is the status of your SCCM components? Check it under Monitoring>System Status>Components Status.

    Did you have encounter any error message during the SQL DB migration?

    Check the SMSProv.log and smsdbmon.log log files for more details.

    Regards,

    SAAD Youssef

    ______

    Please remember to mark the replies as answer if they help, thank you!

    • Marked as answer by Hadjer YH Sunday, November 17, 2019 2:45 PM
    Sunday, November 17, 2019 11:02 AM
  • Hello;

    Thank's for your reply.

    The status of all SCCM components is "OK" except for : 

    SMS_DATABASE_NOTIFICATION_MONITOR "Critical"
    Message:  Failed to get a SQL Server connection.  Either the SQL Server is not running, or all connections have been used.

    SMS_STATE_SYSTEM "Critical"
    Message: The State System message file processing could not process file 'N_FM8AFL4A.SMX' and moved it to the corrupt directory. Review the statesys.log file for further details.

    SMS_ENDPOINT_PROTECTION_MANAGER "Critical"
    Message: Endpoint Protection Manager failed to generate malware detection alerts for type:"31". Verify that the site database is configured correctly. Error code returned is:"0x87d20002".


    Did you have encounter any error message during the SQL DB migration? No 

    Check the SMSProv.log and smsdbmon.log log files for more details.

    statesys.log :

    Error: SQL MESSAGE: dbo.spProcessStateReport - Error: Message processing encountered a SQL error 9802 at record 0 for TopicType 0: "The locale identifier (LCID) 3072 is not supported by SQL Server.", Line 0 in procedure ""

    SMSProv.log : 

    Execute SQL =select  all SMS_ComponentSummarizer.AvailabilityState,
    SMS_ComponentSummarizer.ComponentName,
    SMS_ComponentSummarizer.ComponentType,
    SMS_ComponentSummarizer.Errors,
    SMS_ComponentSummarizer.HeartbeatInterval,
    SMS_ComponentSummarizer.Infos,
    SMS_ComponentSummarizer.LastContacted,
    SMS_ComponentSummarizer.LastHeartbeat,
    SMS_ComponentSummarizer.LastStarted,
    SMS_ComponentSummarizer.MachineName,
    SMS_ComponentSummarizer.NextScheduledTime,
    SMS_ComponentSummarizer.SiteCode,
    SMS_ComponentSummarizer.State,
    SMS_ComponentSummarizer.Status,
    SMS_ComponentSummarizer.TallyInterval,
    SMS_ComponentSummarizer.Type,
    SMS_ComponentSummarizer.Warnings 
    from vSMS_ComponentSummarizer AS SMS_ComponentSummarizer  
    where (((SMS_ComponentSummarizer.SiteCode = N'CE2' 
    AND SMS_ComponentSummarizer.MachineName = N'XXXX.xxxx.COM') 
    AND SMS_ComponentSummarizer.ComponentName = N'SMS_DATABASE_NOTIFICATION_MONITOR') 
    AND SMS_ComponentSummarizer.TallyInterval = N'0001128000100008')

    smsdbmon.log :

    No Error

    Cordialy,

    Sunday, November 17, 2019 2:46 PM
  • "Failed to get a SQL Server connection.  Either the SQL Server is not running, or all connections have been used."

    Based on the error message, there is an issue with the SQL server connection, which type of cluster are you using with SQL? Always On... ?

    Make sure that SCCM can connect to the SQL Server cluster and the following ports are allowed:

    • Site Server ==> SQL Server:
            1433, 135 and 445

    More details: https://docs.microsoft.com/en-us/configmgr/core/plan-design/hierarchy/ports

    Regards,

    SAAD Youssef

    • Marked as answer by Hadjer YH Monday, November 18, 2019 8:41 AM
    Sunday, November 17, 2019 9:31 PM
  • How *exactly* did you move the database?

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Monday, November 18, 2019 8:41 AM
    Monday, November 18, 2019 3:16 AM
  • Hello,

    Thanks for your help.

    I just checked the ports, they are open.
    yes we are using Always On cluster with SQL.

    I made the move of the database by doing a full backup then a restore at the level of the cluster SQL (SQL2014 -> SQL 2017)

    Cordially.

    Monday, November 18, 2019 8:45 AM
  • That's not sufficient. You need to run a ConfigMgr site recovery as well. ConfigMgr must know that there is a change in location and must also install the necessary site system components on the systems hosting the SQL instance.

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Monday, November 18, 2019 2:50 PM
    Monday, November 18, 2019 2:47 PM
  • Hello,

    Yes it is done too, I think it is due to an incompatibility between the version SCCM (2012 R2 SP1) and the SQL Server 2017 AlwaysOn Availability Groups is not it?

    moreover when I changed the SQL Server (on Configuration Manager setup) the FQDN of the AG linstener indicated "Network Path was not Found" but the primary server of the cluster was accepted and it is currently the site data base system.

    Cordially.


    • Edited by Hadjer YH Monday, November 18, 2019 3:01 PM
    Monday, November 18, 2019 2:56 PM
  • Yeah, that's correct. Why are you still running ConfigMgr 2012?

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Tuesday, November 19, 2019 7:22 AM
    Monday, November 18, 2019 3:04 PM
  • Hello,

    this is the goal in fact of the migration of the version of SQL towards the 2017 in order to complete make the upgrade of sccm towards the version 1902.

    and now the upgrade does not pass here is the error:

    error 14: SQL Error Message A .NET Framework error occurred during execution of user-defined routine or aggregate "spSetupLanternDocuments_CLR"

    ERROR: Failed to execute SQL Server command; CCISource::InsertObject returned 14

    ERROR: Failed to install default configuration items (FinalSqlOperations)


    thank you very much for your help


    Cordially.

    Tuesday, November 19, 2019 7:23 AM
  • ConfigMgr 2012 doesn't support SQL 2017 either though. From memory the highest SQL Server version by 2012 is SQL 2012 thus upgrading SQL Server before an upgrade to ConfigMgr 1902 will not work and will need to be done after the upgrade to 1902.

    Jason | https://home.configmgrftw.com | @jasonsandys

    Tuesday, November 19, 2019 3:49 PM
  • Hi,

    We can upgrade from sccm 2012 to sccm 1802 by 1802 media first, and upgrade sccm 1802 to 1902 in the console. Then operate the SQL Server upgrade.

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hadjer YH Wednesday, November 20, 2019 10:26 AM
    Wednesday, November 20, 2019 9:01 AM
  • Hello,
    Thank you for your responses and your help.

    My problem is resolved by changing the language of the registry of the service account that is running the SQL Server services.
    Here is the link that describes the solution.

    I moved the SCCM database from a SQL Server 2012 to a SQL server 2017 and then I migrated SCCM from the 2012 R2 SP1 version to the 1902 and everything is OK now.

    PS:
      - I could not migrate the SCCM before the base because the 1902 version does not support the SQL 2012.
    - The migration of SCCM from the 2012 R2 version to a CB base line version is possible (1802 or 1902).

    Cordially.

    Wednesday, November 20, 2019 10:31 AM
  • Sorry i can't post images or links !

    What shoud i do please ?

    Regards.

    Wednesday, November 20, 2019 10:51 AM
  • Sorry i can't post images or links !

    What shoud i do please ?

    Regards.

    This blog will help. https://www.enhansoft.com/problems-posting-pictures-and-images-or-urls-to-the-technet-forums/

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 9:08 AM
    Wednesday, November 20, 2019 11:24 AM
    Moderator
  • We can upgrade from sccm 2012 to sccm 1802 by 1802 media first, and upgrade sccm 1802 to 1902 in the console. Then operate the SQL Server upgrade.

    Why you you suggest upgrading to an unsupported version of ConfigMgr? 1802 is now unsupported. A direct upgrade to 1902 is the best path here.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, November 20, 2019 2:47 PM
  • > I could not migrate the SCCM before the base because the 1902 version does not support the SQL 2012.

    This is not a correct statement. See https://docs.microsoft.com/en-us/configmgr/core/plan-design/configs/support-for-sql-server-versions

    Also, as noted, 1802 is not supported anymore so using it for anything is a poor choice.


    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 3:45 PM
    Wednesday, November 20, 2019 2:48 PM
  • Hi,

    Thank you very much for the update. I'm glad the problem is solved now. And sorry for my negligence that SCCM CB 1802 is end support on September 22, 2019. Here's a short summary for the problem.

    Problem/Symptom:
    ===================
    After moving the SCCM database that was hosted on the same SCCM site server (SQL Server 2014) to another server (SQL Server 2017 Cluster), have a problem creating a new group of software updates.

    Solution:
    ===================
    The problem is resolved by changing the language of the registry of the service account that is running the SQL Server services. After the database migration, upgrade SCCM 2012 R2 to SCCM current branch 1902 and everything is OK now.

    However, it is recommend to upgrade SCCM first and then upgrade SQL Server(SCCM 1902 support SQL 2012).

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hadjer YH Thursday, November 21, 2019 3:54 PM
    Thursday, November 21, 2019 8:59 AM
  • However, it is recommend to upgrade SCCM first and then upgrade SQL Server(SCCM 1902 support SQL 2012).

    It's not just recommended, it's the only supported path. The solution above is unsupported and may cause future issues.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Thursday, November 21, 2019 2:31 PM
  • Hello,

    Thank you all for your answers.

    I want to specify that the solution mentioned above solved the problem (SQL Config) encountered during the upgrade of SCCM and after the upgrade the problem of creating New update group is also solved.

    Personally the SQL upgrade before SCCM upgrade did not cause any problem for me.

    Cordialmenet.

    Thursday, November 21, 2019 3:55 PM
  • Personally the SQL upgrade before SCCM upgrade did not cause any problem for me.

    That doesn't mean it won't in the future. Doing explicitly unsupported things generally leads to unsupported results and often leads to issues at some point.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Thursday, November 21, 2019 6:21 PM
  • Hi,

    So it can be said that this problem is only temporarily solved because of the unreasonable order. 
    If you have any questions in future, we warmly welcome you to post in this forum again.

    Have a nice day!

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, November 22, 2019 1:39 AM
  • Hello Jason,

    Thank you for all your indications, I will take into consideration your advices,

    thank you so much.

    Best regards

    Wednesday, November 27, 2019 8:18 AM