Database Move issue

[wooju]

Hello,

I am trying to move the database from a SQL 2008 R2 cluster to SQL 2014 cluster.

 

Our current set up is: Windows 2008 r2, SCCM 2012 (cu2 only, planing to upgrade to 2012 R2 as soon as DB is working), SQL 2008 r2 SP3.

 

I took a backup of the DB on the old cluster, restored in the new one without any issues. I left the compatibility mode on 2008 - Schema 100.

I run

USE master;

GO
ALTER DATABASE CM_PR1 SET ENABLE_BROKER
GO

USE master;

GO
ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON
GO

USE master;

GO
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON;
GO

Now, run the following query to check the settings again

select name, collation_name, user_access_desc, is_read_only, state_desc, is_trustworthy_on, is_broker_enabled,is_honor_broker_priority_on from sys.databases where name = 'CM_PR1'

to update all parameters.

 

Next step was to install SQL Native client 2014 on the SCCM server and run the SCCM Configuration Manager Setup. I point to new DB and after about 10 min the setup finished successfully.

 

I started the SCCM console and to my surprise all task/boot images/OS images and programs were gone. I still had all computers/collections/drivers/applications.

 

I tried to change the SQL compatibility mode to 2-14 but no effect.

 

Why only part of information is displayed? Is there another DB i need to take care off?

 

Any help will be appreciated.

[GarthMJ]

No there is only one database for CM12 + the WSUS database. You should see everything in the db unless the security is not setup correctly on the new SQL server. and by the sound of it, it is at the table/view level.

 

I always recommend that you contact CSS anytime you are preforming a CM12 restore or DB move, for this exact reason.

[wooju]

Thanks for the reply Garth. Can you expand a bit on the "security is not setup correctly on the new SQL server. and by the sound of it, it is at the table/view level."? Is this something that can be changed/updated vis SQL tools or do i need to move the DB in another way?

[GarthMJ]

It is not supported to edit the database. Hence why I always recommend opening a case with CSS directly on these type of issues.