Moving, Changing, Migrating, Restoring of your SCCM 2012 SQL Database?

Are you performing any one of the actions above to your SCCM 2012 SQL database? After you did that, please make sure that the new SQL server configuration is correct. Especially the Allow Snapshot Isolation, Is read Commited Snapshot On, Trustworthy, Broker Enabled. and Honor Broker Priority settings. All these settings need to be set as TRUE. If these settings are not configured correctly, you might end up getting errors “Microsoft SQL Server Reported SQL Message 50000, Severity 16: *** Unknown SQL Error!” from SMS_POLICY_PROVIDER Component or this error. Please also ensure that the owner of the database is SA!!!

 

To alter the settings above:

ALTER DATABASE <CM_XXX>
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <CM_XXX>
SET READ_COMMITTED_SNAPSHOT ON

--- Enable the SQL Broker on the Site database

USE master;
GO
ALTER DATABASE CM_XXX SET ENABLE_BROKER
GO

--- SET the Site Database as trustworthy

USE master;
GO
ALTER DATABASE CM_XXX SET TRUSTWORTHY ON
GO

--- SET the Database to honor the HONOR_BROKER_PRIORITY

USE master;
GO
ALTER DATABASE CM_XXX SET HONOR_BROKER_PRIORITY ON;
GO

To change the owner to “sa”
EXEC sp_changedbowner ‘sa’

Thanks for reading…

References:

1. http://blogs.technet.com/b/configurationmgr/archive/2013/04/02/how-to-move-the-configmgr-2012-site-database-to-a-new-sql-server.aspx
2. http://support.microsoft.com/kb/2709082/en-us