SQL Server Requirements for System Center 2012 - Service Manager

Updated: May 12, 2016

Applies To: System Center 2012 SP1 - Service Manager, System Center 2012 R2 Service Manager, System Center 2012 - Service Manager

Microsoft® SQL Server® hosts the databases that System Center 2012 – Service Manager creates. In addition, Service Manager requires SQL Server Analysis Services (SSAS) to work with Microsoft Online Analytical Processing (OLAP) cubes. SQL Server Reporting Services (SSRS) is required to support Service Managerreporting.

All SQL Server requirements are listed at SQL Server and SQL Server editions are listed at Operating System and Database Edition Support.

SQL Server 2014 and 2012 is available in Standard, Enterprise, and Business Intelligence editions. Service Manager will function with all editions. However, there are additional features available in the Enterprise edition that can enhance your experience with the Service Manager data warehouse:

• Analysis Services Files: In the Enterprise and Business Intelligence editions of SQL Server, you can decide where Analysis Services database files will be stored. In the Standard edition, there is only one default location for the files.

• Cube Processing: In the Enterprise and Business Intelligence editions, cubes are processed incrementally each night. In the Standard edition, the entire cube is processed each night and therefore, the amount of processing time required will increase as more data is accumulated. Cubes can still be queried when being processed however, reporting performance will be reduced.

• Measure Group Partitions: In the Enterprise and Business Intelligence editions, measure groups are partitioned on a monthly basis, instead of as one large partition. This reduces the amount of time it takes to process the partition.

• PowerPivot: In the Enterprise and Business Intelligence editions, you can use Microsoft SQL Server PowerPiviot for SharePoint.

You must make your decision to use either the Standard, Enterprise, or Business Intelligence editions of SQL Server 2012 before you install Service Manager. It is possible to use a combination of editions for the Service Manager database and use a different edition for the data warehouse databases.

For more information comparing SQL Server editions, see SQL Server 2012 Editions.

For information about the specific versions of SQL Server that are supported in Service Manager see Software Requirements for System Center 2012 - Service Manager.

SQL Server 2008 R2 is available in both Standard and Enterprise editions. Service Manager will function with both editions. However, there are additional features available in SQL Server 2008 Enterprise that can enhance your experience with the Service Manager data warehouse:

• Analysis Services Files: In the Enterprise edition of SQL Server 2008, you can decide where Analysis Services database files will be stored. In the Standard edition, there is only one default location for the files.

• Cube Processing: In the Enterprise edition, cubes are processed incrementally each night. In the Standard edition, the entire cube is processed each night and therefore, the amount of processing time required will increase as more data is accumulated. Cubes can still be queried when being processed however, reporting performance will be reduced.

• Measure Group Partitions: In the Enterprise edition, measure groups are partitioned on a monthly basis, instead of as one large partition. This reduces the amount of time it takes to process the partition.

• PowerPivot: In the Enterprise edition, you can use Microsoft SQL Server PowerPiviot for SharePoint.

You must make your decision to use either the Standard or Enterprise editions of SQL Server 2008 before you install Service Manager. It is possible to use SQL Server 2008 Standard for the Service Manager database and use SQL Server 2008 Enterprise for the data warehouse databases.

For more information comparing SQL Server editions, see Microsoft SQL Server 2008 Enterprise and Standard Feature Compare. (Adobe Reader is required.)

For information about the specific versions of SQL Server that are supported in Service Manager see Software Requirements for System Center 2012 - Service Manager.

To either install or upgrade Service Manager, computers running SQL Server that host databases must be configured to allow updates. If updates are not allowed, Service Manager Setup will not complete and the following error message will appear at the Create database stage of the installation:

"An error occurred while executing a customer action: _ExecuteSqlScripts. This upgrade attempt has failed before permanent modifications were made. Upgrade has successfully rolled back to the original state of the system. Once the corrections are made, you can retry upgrade for this role."

You can check the status of allow updates on SQL Server by executing the following stored procedure from within SQL Server Management Studio:

sp_configure 'allow updates'  

In the results table, examine the value for "run_value". If the value of "run value" is 1, set it back to 0 with the following stored procedure, and then run Setup again.

sp_configure 'allow updates',0 reconfigure with override  

SQL Server AlwaysOn Availability Groups functionality is supported by all versions of System Center 2012 – Service Manager for the default server instance. However, SQL AlwaysOn Availability Groups functionality is not supported for a named instance.

When considering SQL Server AlwaysOn Availability Groups for the Service Manager database or the DWDataMart, you should determine whether availability replica should support either or both of the following active-secondary capabilities:

• Read-only connection access which enables read-only connections to the replica to access and read its databases when it is running as a secondary replica.

• Performing backup operations on its databases when it is running as a secondary replica.

For more information about installing Service Manager with AlwaysOn availability groups on TechNet.

For more information about AlwaysOn Availability Groups, see AlwaysOn Availability Groups (SQL Server).