Share via

  • Article

How to Prepare the Service Manager Database in the Lab Environment

 

Updated: May 13, 2016

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

Use the following procedure to prepare the Service Manager (SM) database in the lab environment. Perform this procedure on the computer that is hosting the Service Manager database that is being used by the secondary management server, the management server in your lab environment.

To configure the database

  1. On the computer hosting the Service Manager database for the secondary management server, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  2. In the Connect to Server dialog box, follow these steps:

    1. In the Server Type list, select Database Engine.

    2. In the Server Name list, select the server name for your Service Manager or data warehouse databases.

    3. In the Authentication list, select Windows Authentication, and then click Connect.

  3. In the Object Explorer pane, expand Databases, and then click ServiceManager.

  4. In the toolbar, click New Query.

  5. In the center pane, type the following commands, and then click Execute.

    sp_configure 'clr enabled', 1  
go  
reconfigure  
go

  6. In the center pane, remove the commands you typed in the previous step, type the following commands, and then click Execute.

    ALTER DATABASE ServiceManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  7. In the center pane, remove the commands you typed in the previous step, type the following commands, and then click Execute.

    ALTER DATABASE ServiceManager SET ENABLE_BROKER

  8. In the center pane, remove the commands you typed in the previous step, type the following commands, and then click Execute.

    ALTER DATABASE ServiceManager SET MULTI_USER

To configure the service account

  1. In the Object Explorer pane, expand Security, and then expand Logins.

  2. Right-click Logins, and then click New Login

  3. Perform the following procedures in the Login – New wizard:

    1. Click Search.

    2. Type the username (domain\username) for the service account for Service Manager database in the lab environment, click Check Names, and then click OK.

      Note


      If the Data Access Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the management server.

    3. In the Select a page pane, click User Mapping.

    4. In the Users mapped to this login area, in the Map column, click the row that represents the name of the Service Manager database (ServiceManager is the default database name).

    5. In the Database role membership for: ServiceManager area, make sure that the following entries are selected:

      • configsvc_users

      • db_accessadmin

      • db_datareader

      • db_datawriter

      • db_ddladmin

      • db_securityadmin

      • dbmodule_users

      • public

      • sdk_users

      • sql_dependency_subscriber

    6. Click Ok

To configure Service Manager tables

  1. In the Object Explorer pane, expand Databases, expand ServiceManager, and then expand Tables.

  2. Right-click dbo.MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.

  3. In the center pane, locate the column SQLServerName_43FB076F_7970_4C86_6DCA_8BD541F45E3A.

  4. In the first row and second rows of this column, type the computer name of the computer hosting the Service Manager database in the lab environment. In the case of named instances, type computer name\instance name.

  5. Right-click dbo. MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore, and then click Edit Top 200 Rows.

  6. In the center pane, locate the column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA.

  7. In the first row of this column, type the computer name of the computer hosting the SQL Server for the Service Manager database in the lab environment. In the case of named instances, type computer name\instance name.

  8. Right-click LFX.DataSource, and then click Edit Top 200 Rows.

  9. In the center pane, locate the column DataSourceAddress.

  10. In the first row of this column, locate the entry that starts with Data Source = <server name>; Initial Catalog = ServiceManager; Persist Security Info=False. Type the name of the computer hosting SQL Server in the lab environment in place of <server name>.

  11. Right-click dbo. MT_Microsoft$SystemCenter$ResourceAccessLayer$SdkResourceStore, and then click Edit Top 200 Rows.

  12. In the center pane, locate the column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA.

  13. In all of the rows in this column, type the name of the computer hosting the Service Manager management server in the lab environment.

  14. Right-click [dbo].[MT_Microsoft$SystemCenter$ResourceAccessLayer$CmdbResourceStore], and then click Edit Top 200 Rows.

  15. In all rows update the column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA, type the name of the SQL computer hosting the Service Manager database in the lab environment

  16. In the toolbar, click New Query.

  17. In the center pane, type the following command, and then click Execute.

    Delete from dbo.MT_Microsoft$SystemCenter$ResourceAccessLayer$DwSdkResourceStore

  18. Close Microsoft SQL Server Management Studio.