locked
Move SCCM database to database server RRS feed

  • Question

  • Hi,

    Today I tried to move our SCCM Primary site database to a new database server. We are in the process of upgrading to SCCM CB and therefore we need a new SQL server version. We installed SQL 2014 on a Windows 2012R2 server.

    We followed procedures found on the internet and think we did everything that was necessary like CLR integration, add primary site computer account to the database server, running the setup tool (to modify sql config) with sccm_admin account which we gave SA on the new database server. We took a backup of the database after stopping the site and restored on the new server.

    As a lot of other people, we also get the certificate chain error which we tried to solve with the solutions provided, but as some other, it doesn't seem to work for us either.

    Does somebody know exactly which certificates should be exported from where and to where they should be imported?

    See loggings below:

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup 30/08/2016 21:48:01 11740 (0x2DDC)
    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection Configuration Manager Setup 30/08/2016 21:48:01 11740 (0x2DDC)
    *** Failed to connect to the SQL Server, connection type: SMS ACCESS. Configuration Manager Setup 30/08/2016 21:48:01 11740 (0x2DDC)
    INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure Configuration Manager Setup 30/08/2016 21:48:01 11740 (0x2DDC)

    This error is repeating for about 7 min and the the setup failes.

    Thanks,

    WiM

    Tuesday, August 30, 2016 7:54 PM

Answers

  • I would recommend you remove wsus and the role.  wsus is such a quick feature to install and setup. and especially with the various updates necessary to support win 10. Since the updates must be installed before you sync, I would remove the role upgrade the server, install the updates, install wsus and its updates, then you're ready to sync your updates.

    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Thursday, September 1, 2016 12:14 PM

All replies

  • Why not just upgrade the SQL Server instance in place?

    What you are seeing is because the certificate being used by your SQL instance is self-signed and not trusted by the site server. You need to go download that certificate and add it to the trusted root authority store on the site server.


    Jason | http://blog.configmgrftw.com | @jasonsandys

    Tuesday, August 30, 2016 8:55 PM
  • Look in the Sql configuration, TCP settings and in there you will find a Cert. It normally uses a cert that SCCM created during the install.  You can also you one of your own certificates. I have seen limited success with export import mainly with the root cert it does work.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Tuesday, August 30, 2016 9:09 PM
  • Thanks for the replies.

    Reason to move database: we only have 1 primary site which is taking all the load, so we wanted to split of the database (which was also recommended by Microsoft). Also, we are running SCCM on Windows 2008r2 with WSUS 3.0, which is not supported by SQL2014... and our IDBA guys never do inplace upgrades :-(

    So Jason, If I understand correctly, I need to go to the new database server, export some certificate and import it on the old SCCM server. So where do i find that certificate and where do I have to import it?

    In the old SQL server, I could find the certificate of the SCCM server (via sql configuration manager), in the new SQL server, this is empty and I cannot select any certificate, but this is not the place to look as you say that I need to import on the Site server (which is the server that contains the old SQL server)

    Matthew,

    I'm guessing you mean "protocols for MSSQLSERVER" properties, certificate tab. On the new server, the certificate is empty, on the old server it is filled in. In don't find a way to select the certificate on the new server, the dropdownbox is empty.

    Thanks again.

    WiM

    Tuesday, August 30, 2016 9:30 PM
  • Just to be clear, it is generally NOT recommend to have SQL Server remote from CM.


    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

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

    Tuesday, August 30, 2016 10:23 PM
  • Strange, we had a chalk 'n talk with somebody from MS and this was discussed and agreed to be a good option to lower the load on the primary site server...

    What do you recommend? Also inplace upgrade of the SQL server? How do we deal with the incompatibility of WSUS/SQL2014?

    So we need to update SQL server to 2014, update SCCM to 1602, uninstall WSUS,update the OS and reinstalling WSUS. Isn't that a lot to do at once?

    All advice is welcome...

    thanks,

    WiM

    Tuesday, August 30, 2016 10:55 PM
  • update the OS


    To what? You are already on 2012 R2. Or is this just a newly installed remote SQL 2014 server?
    How many clients are you managing in total? I second Garth's statement. Keep SQL local if possible.

    Torsten Meringer | http://www.mssccmfaq.de

    Wednesday, August 31, 2016 6:08 AM
  • Torsten,

    Our current site server is on 2008R2, the new database server is on 2012R2. If we update SQL to 2014, will we but able to run WSUS (unsupported) or will it just not work until the WSUS has been upgraded (so after OS upgrade)?

    Let say that we go through with the split of of the database, can anyone give some detailed information about the certificates that need to be imported?

    Forgot to say, our infrastructure is running virtual (ESX). @ the moment, we are talking about 16000 clients.

    Thanks,

    WiM


    • Edited by WiM8 Wednesday, August 31, 2016 7:22 AM
    Wednesday, August 31, 2016 7:19 AM
  • So let me ask the obvious questions, Are you planning on Upgrading to CMCB? If so why are you not considering a side-by-side setup and migrating objects?

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

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

    Wednesday, August 31, 2016 12:25 PM
  • Yes Garth,

    We are in the process of an upgrade to SCCM CB.

    We have chosen the upgrade path instead of the migration part because it is (again) recommended by Microsoft. Microsoft is putting a lot of efforts in making inplace upgrades possible.

    We still want to go for the inplace upgrade so has anyone an answer to these unaswered questions:

    - What will happen with our current WSUS 3.0 SP2 if we do an inplace upgrade of SQL 2012 to SQL 2014? Will WSUS stop working or will it still work (unsupported) until we upgrade the OS and install WSUS 4.0?

    -Is we still want to go ahead with the database split of, which certificate has to be exported and where do I need to import it? (I've found the certificate for the server identification on the old SQL server, I've imported it in several stores on the new server but still no luck. I don't see the certificate installed when I open the SQL configuration manager.

    Thanks,

    WiM 

    Thursday, September 1, 2016 6:56 AM
  • I would recommend you remove wsus and the role.  wsus is such a quick feature to install and setup. and especially with the various updates necessary to support win 10. Since the updates must be installed before you sync, I would remove the role upgrade the server, install the updates, install wsus and its updates, then you're ready to sync your updates.

    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Thursday, September 1, 2016 12:14 PM