You are using an outdated browser. Upgrade your browser today or install Google Chrome Frame to better experience this site.

Service Manager Mercenary - Authoring, Ramblings, and Help

  • The Official Microsoft Service Manager Blog
  • System Center Universe

Monday, May 4, 2015

Getting started with sql queries for your scsm data warehouse.

service manager database tour useful queries

SELECT id, title, createddate FROM IncidentDimvw WHERE createddate > '01/02/2015'
"I've seen a few TechNet Gallery publishings do this JOIN or INNER JOIN thing. I just don't get it. It's nothing like PowerShell or other things I've used/seen."
Seems kinda limited...
  • The Incident table relates to the Work Item table
  • The Work Item table relates to the Work Item Affected User table (and assigned to, created by, etc)
  • Work Item Affected User relates to the User table
What?! It's established in the diagram! I mean the database! Why do I have to do it! Doesn't it know!?
SELECT ir. id, ir. title, ir. createddate FROM IncidentDimvw as ir WHERE ir. createddate > '01/02/2015'
SELECT dzak. id, dzak. title, dzak. createddate FROM IncidentDimvw as dzak WHERE dzak. createddate > '01/02/2015'
SELECT ir.id, ir.title, ir.createddate FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw on ir.EntityDimKey = WorkItemDimvw.EntityDimKey WHERE ir.createddate > '01/02/2015'
SELECT ir.id, ir.title, ir.createddate FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi. EntityDimKey WHERE ir.createddate > '01/02/2015'
SELECT ir.id, ir.title, ir.createddate FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey     INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey WHERE ir.createddate > '01/02/2015'
SELECT ir.id, ir.title, ir.createddate FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey     INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey     INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser .UserDimKey WHERE ir.createddate > '01/02/2015'
SELECT ir.id, ir.title, ir.createddate, affectedUser.DisplayName, affectedUser.Department FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey     INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey     INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey WHERE ir.createddate > '01/02/2015'
DECLARE @StartDate datetime SET @StartDate = '01-02-2015' SELECT ir.id, ir.title, ir.createddate, affectedUser.DisplayName as 'Affected User' , affectedUser.Department as 'Department' FROM IncidentDimvw as ir     INNER JOIN WorkItemDimvw as wi on ir.EntityDimKey = wi.EntityDimKey     INNER JOIN WorkItemAffectedUserFactvw as WIAU on wi.WorkItemDimKey = wiau.WorkItemDimKey     INNER JOIN UserDimvw as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey WHERE ir.createddate > @StartDate

0 comments:

Post a comment.

Aries's Sysadmin Blog

Saturday, march 8, 2014, scsm 2012 - exploring worflow in sealed mp.

service manager database tour useful queries

No comments:

Post a Comment

How the Database Query Tool Works

Sql query windows, query execution, server output, running scripts with multiple sql statements, asynchronous sql query execution, queries with parameters and macros, using master source, querying several databases at once.

Like most of SQL query tools, the Database Tour does not execute your database queries directly. It makes some checks and then passes the queries for execution to the core application intended for doing this: your database server or database engine ( FD, ADO etc. ). Therefore, SQL queries should be written using rules defined for currently opened database : for example, if you open Oracle database, write your query following Oracle database rules etc.

This topic describes the common technique and Database Tour specific capabilities which helps you to build your queries and get their results. You will not find SQL tutorial here (at least because it is impossible to know all details of SQL for all existing types of databases). For more details about writing the SQL queries, please read your database documentation.

service manager database tour useful queries

In the SQL window, there are several pages:

  • Edit . The place where you type your database query text.
  • Result . Here, the result of the SELECT query is shown. This page is shown only if the corresponding option of SQL Editor is on; when the option is off (default), the query result is shown at the bottom of the Edit page.
  • Output . This page shows server output for Oracle and PostgreSQL databases. See more below.
  • History . This page contains SQL execution history for this SQL window.

SQL query window

SQL editor is based on a 3rd party component called 'SynEdit', which available at www.sourceforge.net.

SQL statement can be entered manually or loaded from a file (including loading from command line ) on Edit page. There are several templates of the most often used SQL expressions, which are available by clicking Templates button; select a template and then just edit its SQL text to produce needed SQL expression.

A toolbar above the editor and the context menu of the editor contain a lot of functions, which facilitate editing and formatting the SQL text.

The status bar below shows the caret position (line and column) and selection size (if any).

SQL syntax highlighting

The SQL editor supports SQL syntax highlighting. Use Options | Environment | SQL editor | Font and Color to change the parameters of the syntax highlighting. In addition to this, there is an ability to specify the SQL dialect for the syntax highlighting on the Edit page. Different SQL dialects are usually applied to different database types and have different sets of key words and some other options, which can change the highlighting of some elements in your SQL code. In most cases the SQL dialect is set automatically depending on open database type (e.g. dBase, Oracle, SQL Server). But in some cases you may want to change it to more suitable (for example, change SQL dialect for your SQL Server database from MS SQL 7 to MS SQL 2K and vice versa). Please note that SQL dialect, as the syntax highlighting itself, does not have any influence on query executing, and is used only for convenient showing the SQL code.

SQL syntax highlighting

It is possible to save an SQL text to a file or print it. When printing SQL text or saving it to RTF or HTML documents, the syntax highlighting and colors are kept.

Clickable objects

If you press and hold Ctrl and move the mouse over the SQL text at the same time, the Database Tour tries to analyze the words under the mouse. If a word under the mouse is recognized as a special object which can be processed by the application in the current context, it becomes a hyperlink. If you click the link, a certain action is done to the object depending on its type:

SQL editor table hyperlink

You can customize the usage of SQL hyperlinks by the corresponding options of SQL Editor .

SQL token hints

If you slightly move the mouse over the SQL text, the Database Tour can show you some info about the token under the mouse, for example, when the token is a table alias or stored procedure variable.

Stored procedure variable hint shows the type and default value of the variable:

Stored procedure variable hint

Table alias hint shows the full name of the used database table:

Table alias hint

You can customize the usage of SQL token hints by the corresponding options of SQL Editor .

Execute button

Note: if there is selected text in SQL editor, then only selected statement(s) will be processed.

If the SELECT query was successfully executed, the result data will be shown on the Result page (or below the query text depending on the corresponding option ). To work with these data, you can use some items from the Tools menu or the corresponding buttons from the main toolbar (when you on the Result page). For example, you can export or print the query data etc.

To quickly build a query for selecting data from a table, you can just choose Generate SQL | Query Data item from context menu of the needed table in the table list.

Editing SELECT query results

Some databases allow to edit the result of SELECT query, if it comply certain criteria (e.g. using only one physical table, not using aggregate functions in the SQL command etc). Additionally, you have to turn on the Use editable queries, if possible option before opening the query. After SELECT query execution, active editing buttons ( Delete , Edit etc.) confirm the query was opened in edit mode.

History of executed queries and navigation between them

The application saves the SQL texts of the executed queries (their quantity specified by the corresponding option ) and some statistical information about them for each SQL window of each database. This information is available on History page of the corresponding SQL window. It can be copied to clipboard, which allows to automatically create the script with all queries from the history. To exchange SQL texts between different databases, use Export SQL History and Load SQL History buttons. To quickly copy SQL texts from one database to another, open the first database, then go to History page and open the second database holding SHIFT down. To navigate between SQL texts, use the Previous SQL , Next SQL buttons on Edit page, corresponding items from the Query menu or double-click needed row on History page.

History of all SQL windows of all registered databases are saved to a workspace file (full path of the file can be seen from Database | Registered Databases... menu). This saving is done in the following cases: before executing a query, before database closing, and before the application's closing. Please consider this information when working with multiple instances of the application. When the application is restarting, all history of all SQL windows of all databases are loaded from the workspace file into operating memory; then, when certain database is opening, all its SQL windows and their history are restored.

Execution by timer

Execute by timer button

On the Output page, it is possible to see the server output generated during the query execution (for Oracle and PostgreSQL databases, opened by FD interface). You can use the server output to debug your stored procedures or to control their execution.

In Oracle databases, it is a feedback sent using DBMS_OUTPUT package.

In PostgreSQL databases, it can be PostgreSQL errors, warnings, hints, or a feedback produced, for example, by RAISE NOTICE commands.

Example of the server output in PostgreSQL database:

Viewing server output

There is an ability to run multi-statement SQL scripts (scenarios). Each statement in the script must be separated with a statement separator (sometimes called terminator): a certain character or set of characters (without spaces), which is specified on the database connection level.

Executing multi-command SQL script:

Executing multi-command script

To change the SQL statement separator for a certain database, make sure the database is closed, then choose Database | Registered Databases... menu, select the needed database, click Edit button, find the SQL statement separator property, change its value to whatever you need, and click Apply Changes .

There is also an advanced way to execute multi-statement SQL scripts using a drop-down menu near Execute button, where you can also specify a log (result) file to save the information about each statement processing result. Attention! In this mode, when error is occurred during the statement execution, the current statement is skipped and the process continues with the next statement.

After the script execution ends, you can switch to the History page and see detailed statistics for every executed command in the pane below. If you double-click the line number of the command here (left-most column), you will be quickly moved to that line in the SQL editor.

Asynchronous execution means running more than one query at the same time (from different SQL windows) or working with the application interface while the query (queries) still executing. To enable asynchronous execution, turn the corresponding option from Database section on.

Please note that not all databases allow to run database queries in parallel. If your database does not support that, you still will be able to run multiple SQL queries at the same time and work with Database Tour interface, but the database will process your queries successively.

Asynchronous query execution in PostgreSQL database:

Asynchronous query execution

Here, we are working with SQL text in SQL 1 window, while four other windows execute their queries. One of them already finished successfully, another one finished with error, and two others are still in progress.

Parameters and macros are substitution variables in queries. They improve flexibility of query execution by allowing dynamically change the query (without changing of SQL text) in runtime before the execution. These variables get their values directly before query execution either by user input, command line parameters, report (when the query is executed as part of report), or they can be calculated automatically if they are predefined macros or parameters (see below). Macros are always text variables; their values just replaces macros text in the SQL text before query execution. Unlike macros, parameters have type; their values and types are passed to server and not inserted in SQL text. Macros and parameters inside comments or string literals are ignored by the application SQL engine, but is not recommended to use parameters inside comments, especially in ADO connections. Although macros are often more convenient to use, using parameters are preferable as they help in performance optimization and against SQL injection.

If parameter or macro is not predefined one and its value was not defined in command line or report (Database Tour Pro only), the user will be prompted to enter it in a separate window.

Macros begin with << and end with >>. Macros are defined mainly for substitution when executing queries via command line . In the following SQL example, there is CUST_TYPE_LIST macro:

SELECT * FROM payments

WHERE customertype in ( <<CUST_TYPE_LIST>>)

The value for this macro can be, for example, the following string:

3, 8, 12, 5

Note that such thing cannot be produced using parameters.

Parameters begin with colon. Parameters, which contain spaces, must be enclosed in single quotes. Parameterized queries are convenient for using the same SQL statement for many data values. In the following SQL example, there is a DATE parameter:

WHERE paydate = :DATE

When the application executes such a query, it suggests user to enter parameter value and data type, and then continues execution. To select parameter data type automatically by the application, you may specify this data type directly in SQL statement in the separate comments right after parameter, as shown in the following example:

SELECT * FROM orders

WHERE orderno < :ORDERNO /* ParamType=Integer */

There are several predefined parameters, which are calculated automatically by the application and does not require user or command line input:

If the predefined parameters begin not with colon, they are interpreted as predefined macros. In case of integer data, the result will be the same, but in case of their data types there may be problems, therefore please don't confuse parameters and macros.

You can use a master source for query. Master source is the table, opened in the separate window. If SQL text contains a column name of the master table followed by the colon, then this will be interpreted as parameter. Value of this parameter is taken from corresponding field of the master source (such field must exist in the source).

Example (table 1 is used to select data from, table 2 as master source, and column 1 is a column from table 1):

SELECT * FROM <table 1 >

WHERE <column 1> = :CUSTOMER

Select master source (table 2) from the table list and right-click it. Choose Open in the Separate Window from the context menu. The table must contain CUSTOMER field. Click the Use Master Source button (on Result page) and execute the query. You will see the record set from the table 1, in which value of column 1 equal to value of the CUSTOMER field from the master source (table 2). When you moved cursor of the table 2 to another record, then the query will automatically reopened to match value of column 1 to the new value of the CUSTOMER field.

There is an ability to build SELECT queries, which refer another database from open local database (only BDE connections). Moreover, you can join tables from several different databases (so called heterogeneous joins ). Local database is any database with Paradox, dBase, or FoxPro tables, connected via BDE alias or folder name. The external database may be folder reference (for local tables), ODBC DSN or a BDE alias. To refer a table from external database, prefix the table name with colon, external database name, and another colon, and enclose this construction in quotation marks, for example:

SELECT c.custno, o.orderdate

FROM ":CustDSN:customer" c, ":OrderAlias:order" o

service manager database tour useful queries

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Move the Service Manager and data warehouse databases to different servers

  • 12 contributors

This version of Service Manager has reached the end of support. We recommend you to upgrade to Service Manager 2022 .

After you've deployed Service Manager, you might need to move the Service Manager or data warehouse databases from one computer running Microsoft SQL Server to another for reasons such as the following:

You need to replace hardware that is experiencing issues and is no longer considered reliable.

You need to add additional hardware to improve scalability and performance.

You need to move a database and log file to a different volume because of space or performance reasons.

You need to change hardware that is leased and is due to expire soon.

You need to change or upgrade hardware to comply with new hardware standards.

You initially installed multiple Service Manager components on a single server, and you need to distribute some components to other servers.

You need to restore functionality in a failure scenario.

If you want the move the data warehouse database, and if you've installed Service Manager within the last 90 days, it might be easier for you to unregister the data warehouse, install a new data warehouse, and register the new database. If the data hasn't been groomed from the Service Manager database, there will be no data loss in the data warehouse database because it will be synchronized. By default, the grooming interval for work items is 90 days from the last time a work item was modified. Using this process is simpler than using the following guidance, which details how to move your databases from one server to another and requires many steps.

Move the Service Manager database

You must use the following high-level steps to move the Service Manager database.

  • These steps link to content in the Service Manager Upgrade Guide.
  • After deploying Service Manager on the SQL server nodes participating in SQL Always On, to enable CLR strict security , run the SQL script on each Service Manager database.

Open the inbound SQL Port on new Service Manager database server. The default port is 1433.

Stop the System Center services on all the management servers.

Back up the Service Manager database, as described in How to Back Up the Production Service Manager Database .

Restore the Service Manager database on the target computer that is running Microsoft SQL Server, as described in How to Restore the Service Manager Database in the Lab Environment .

Configure the Service Manager database, as described in How to Prepare the Service Manager Database in the Lab Environment .

Don't perform step 17 in the procedure for configuring tables.

After you move the ServiceManager database, ensure that you manually change all the Service Manager database and data warehouse registration information in the DWStagingAndConfig database. Old information about where the ServiceManager database is located remains in the DWStagingAndConfig database in the following tables:

MT_Microsoft$Systemcenter$Datawarehouse$CMDBSource

  • In the corresponding entry with DataSourceName_GUID = <Service Manager Data Source Name>, change the field DatabaseServer_GUID with the new name of the SQLServer\Instance where the ServiceManager database has moved to.

MT_Microsoft$Systemcenter$ResourceAccessLayer$SqlResourceStore

  • In the corresponding entry with DataService_GUID = ServiceManager, change the field Server_GUID to the new name of the SQLServer\Instance where the ServiceManager database has moved to.

Configure the registry on all the management servers that will access the new SQL Server instance by using the following steps:

Open Registry Editor.

Browse to HKEY_LOCAL_MACHINE\Software\Microsoft\System Center\<version>\Common\Database .

Configure two keys: one for the server name (DatabaseServerName) and one for the database name (DatabaseName). Set values to the new server name and database name if they're different from the original values.

If you're also upgrading the SQL server while moving, then upgrade the following SQL Server prerequisites for the Service Manager Management server. There are 2 SQL Server prerequisites:

SQL Native Client

Analysis Management Objects (AMO)

Start the System Center services on all the management servers, as described in How to Start Service Manager Services on the Secondary Management Server .

Install another Service Manager database that has a different name on the same computer that is running SQL Server by installing another Service Manager management server and choosing to create a new database. This step will populate the master database with error message text so that if an error occurs in the future, the error message can describe the specific problem instead of displaying generic text. After the database is installed, you can drop it from the computer that is running SQL Server and uninstall the additional, temporary management server.

Run the following query on the source Service Manager database server and copy the output script, and then run it on new Service Manager database server.

Move data warehouse databases

The following high-level steps are required to move the data warehouse databases. Each step in this list links to an associated procedure later in this article.

Locate user accounts and instances of SQL Server

Stop service manager services, back up the data warehouse databases, take the data warehouse databases offline, restore the data warehouse databases on the new computer running sql server, prepare the data warehouse databases on the new database server, update data warehouse management server with the new database server name, update the data sources on the reporting server, update the data sources for the analysis services, start service manager services on the data warehouse management server.

After you move the DWStagingAndConfig and DWRepository databases, they have to be restored on the same instance of SQL Server. Restoring them on separate instances of SQL Server isn't supported.

The collation on the new instance of SQL Server has to match the collation of the original instances of SQL Server where the data warehouse databases were originally hosted.

Use the following procedures to locate the user accounts and instances of SQL Server that are used by the data warehouse management server to identify the:

  • SQL Server database and instance names
  • Reporting server and instance names
  • Service account
  • Reporting account
  • OLAP Account

Follow these steps to identify the SQL Server database and instance names used by the data warehouse management server:

Sign in to the data warehouse management server as a user with administrative credentials.

On the Windows desktop, select Start , and select Run .

In the Run dialog, in the Open box, enter regedit , and select OK .

In the Registry Editor window, expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\<version>\Common\Database , and then make note of the following registry values:

DatabaseName

DatabaseServerName

DataMartDatabaseName

DataMartSQLInstance

RepositoryDatabaseName

RepositorySQLInstance

StagingDatabaseName

StagingSQLInstance

OMDataMartDatabaseName

OMDataMartSQLInstance

CMDataMartDatabaseName

  • CMDataMartSQLInstance

Follow these steps to identify the reporting server and instance names used by data warehouse management server:

In the Registry Editor window, expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\<version>\Common\Reporting , and then make note of the following registry values:

  • ServerInstance
  • WebServiceURL

Follow these steps to identify the service account used by the data warehouse management server:

In the Run dialog, in the Open box, enter services.msc , and select OK .

Locate the service System Center Data Access Service, and double-click it.

In the Properties window, select the Log On tab.

Make a note of the user account name under This account: .

Repeat Steps 3 through 5 for the System Center Management Configuration service.

Follow these steps to identify the reporting account used by the data warehouse management server:

The account that is configured by using the names in the following data sources in SQL Server Reporting Services is called the Reporting account .

Sign in to the server with SQL Server Reporting Services that are hosting the Service Manager reports.

In this procedure, you'll use values that you noted in the To identify the SQL Server database and instance names used by the data warehouse management server procedure.

In SQL Server Reporting Services, select Start , select All Programs , select the program group for the version of SQL Server you're running, select Configuration Tools , and select Reporting Services Configuration Manager .

In the Reporting Services Configuration Connection dialog, connect to the SQL Reporting instance that you noted in a previous procedure.

In Reporting Services Configuration Manager , select Reporting Manager URL .

On the Reporting Manager URL page, select the hyperlink that resembles http://Servername:portnumber/Reports to open it in your web browser.

Open the System Center folder and then open the Service Manager folder.

Select the DWDataMart data source and make a note of the User name value under Credentials stored securely in the report server .

In your browser, select Back to return to the Service Manager folder.

Select the DWStagingAndConfig data source and make a note of the User name value under Credentials stored securely in the report server .

Select the ConfigurationManager data source and make a note of the User name value under Credentials stored securely in the report server .

Select the MultiMartDatasource data source and make a note of the User name value under Credentials stored securely in the report server .

Select the OperationsManager data source and make a note of the User name value under Credentials stored securely in the report server .

Close the browser window.

Follow these steps to identify the OLAP Account used by the data warehouse management server:

Sign in to the Service Manager server, select Start , select All Programs , select Microsoft System Center <version> , select Service Manager , and select Service Manager Shell .

In the Windows PowerShell command prompt, copy the following command, and then press ENTER.

Replace <DWServerName> with the name of your data warehouse management server.

The $OLAPServer.Server cmdlet returns the name of the OLAP server that is hosting the DWASDataBase, and it contains the OLAP Account.

On a server where you have SQL Server Management Studio installed, do the following:

Open SQL Server Management Studio.

In the Connect to Server window, select Analysis Services in the Server type list.

In the Server name list, enter or select the name you noted from the output of the $OLAPServer.Server cmdlet in previous step, and select Connect .

In the Object Explorer pane, expand Databases , and then expand the DWASDataBase OLAP database.

Expand the Data Sources folder, and then double-click CMDataMart .

In the Data Source Properties - CMDataMart dialog, note the value of Connection String .

Under Security Settings , select Impersonation Account , and select the properties button (...) to open the Impersonation Information dialog.

In the Impersonation Information dialog, note the user name.

Select Cancel twice to close the dialogs.

Repeat the steps above to note the Connection string and the User name for the DWDataMart and OMDataMart databases.

Use the following procedure to stop the Service Manager services on the data warehouse management server.

Stop Service Manager services on the data warehouse management server

Follow these steps to stop Service Manager services on the data warehouse management server:

In the Run dialog, in the Open text field, enter services.msc , and select OK .

In the Services window, in the Services (Local) pane, locate the following three services, and for each one, select Stop :

System Center Data Access Service

Microsoft Monitoring Agent

System Center Management Configuration

Use the following procedure to back up the data warehouse databases on the original computer running SQL Server:

Sign in to the original computer running SQL Server that is hosting the data warehouse databases, and open SQL Server Management Studio .

In the Connect to Server dialog, follow these steps:

In the Server Type list, select Database Engine .

In the Server Name list, select the server name for your data warehouse database.

In the Authentication list, select Windows Authentication , and select Connect .

In the Object Explorer pane, expand Databases .

Right-click the DWStagingAndConfig database, select Tasks , and select Back Up .

In the Back Up Database dialog, enter a path and a file name in the Destination on disk text box, and select OK .

The destination location must have enough available free disk space to store the backup files.

Select OK in the Back Up Database dialog to start the backup.

Repeat these steps for the DWRepository, CMDWDataMart, OMDWDataMart, and DWDataMart databases.

Use the following procedure to take the data warehouse databases offline on the original computer running SQL Server:

Right-click the DWStagingAndConfig database, select Tasks , and select Take Offline .

In the Take database offline dialog, select Close .

Repeat the previous steps for the DWRepository, CMDWDataMart, OMDWDataMart, and DWDataMart databases.

Use the following procedure to restore the data warehouse databases on the new computer running SQL Server:

On the new computer running SQL Server, open SQL Server Management Studio .

In the Server Name list, select the server name for your Service Manager services database.

In the Object Explorer pane, right-click the Databases folder, and select Restore Database .

In the Restore Database dialog, under the To a point in time text box, retain the default, Most recent possible , or select a specific date and time by selecting the browse button to open the Point in Time Restore dialog.

To specify the source and location of the backup sets to restore, select From Device .

Select Browse to open the Specify Backup dialog.

In the Backup media list box, select one of the listed device types. To select more devices for the Backup location, select Add .

In the Select the backup sets to restore grid, select the backups to restore. (This grid displays the backups that are available for the specified location.)

On the General page, the name of the restoring database appears in the To database list. Select the DWStagingAndConfig database from the list.

In the Restore options panel, select Overwrite the existing database .

In the Restore the database files as options panel, verify that the original database file name and path are correct.

For the Recovery state option, select Leave the databases ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored (RESTORE WITH RECOVERY) .

Select OK to restore the database.

Use the following three procedures to prepare the data warehouse databases on the new database server:

To configure the DWStagingAndConfig database on the new computer running SQL Server

To configure the service account database permissions

To configure the DWStagingAndConfig tables

Configure the DWStagingAndConfig database on the new computer running SQL Server

Follow these steps to configure the DWStagingAndConfig database on the new computer running SQL Server:

In the Server Name list, select the name of the new computer running SQL Server that hosts the DWStagingAndConfig database.

In the Object Explorer pane, expand Databases , and select DWStagingAndConfig .

In the toolbar, select New Query .

In the center pane, copy the following command, and select Execute .

In the center pane, remove the command you typed in the previous step, copy the following command, and select Execute .

In the center pane, remove the command you entered in the previous step, copy the following command, and select Execute .

In the center pane, remove the command you entered in the previous step, enter the following command, and select Execute .

Configure the service account database permissions

Follow these steps to configure the service account database permissions:

In the Object Explorer pane, expand Security , and then expand Logins . Right-click Logins , and select New Login .

Select Search .

Enter the user name by using the domain\user name format for the data warehouse service account, select Check Names , and select OK .

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.

In the Select a page pane, select User Mapping .

In the Users mapped to this login area, in the Map column, select the row that represents the name of the DWStagingAndConfig database. DWStagingAndConfig is the default database name.

In the Database role membership for: DWStagingAndConfig area, ensure that the following entries are selected:

configsvc_users

db_accessadmin

db_datareader

db_datawriter

db_ddladmin

db_securityadmin

dbmodule_users

sql_dependency_subscriber

In the Database role membership for: DWRepository area, ensure that the following entries are selected:

In the Database role membership for: DWDataMart area, ensure that the following entries are selected:

Select OK .

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

Right-click Logins , and then click New Login .

Enter the user name in the domain\user name format for the reporting account, select Check Names , and select OK .

In the Users mapped to this login area, in the Map column, select the row that represents the name of the DWStagingAndConfig. DWStagingAndConfig is the default database name.

In the Database role membership for: OMDWDataMart area, ensure that the following entries are selected:

In the Database role membership for: CMDWDataMart area, ensure that the following entries are selected:

Right-click Logins , and select New Login .

Enter the user name in the domain\user name format for the OLAP account , select Check Names , and select OK .

Configure the DWStagingAndConfig tables

Follow these steps to configure the DWStagingAndConfig tables:

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

Select dbo.MT_Microsoft$SystemCenter$ManagementGroup , and select Edit Top 200 Rows .

In the center pane, locate the column SQLServerName_ 43FB076F_7970_4C86_6DCA_8BD541F45E3A , and then in the first row of the column, enter the name of the new computer running SQL Server that is hosting the DWStagingAndConfig database. For named instances, enter ComputerName\InstanceName .

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

Update the column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA for rows representing DWStagingAndConfig, DWRepository, CMDWDataMart, OMDWDataMart, and DWDataMart by entering the name of the new computer running SQL Server that is hosting the respective databases. For named instances, enter ComputerName\InstanceName .

Right-click dbo.MT_Microsoft$SystemCenter$ResourceAccessLayer$CMDBResourceStore , and select Edit Top 200 Rows .

In the center pane, locate the column Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA , and in the first row of the column, enter the name of the new computer running SQL Server that is hosting the DWStagingAndConfig database. For named instances, enter ComputerName\InstanceName .

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

In the center pane, locate the DataSourceAddress column, and in the first row of the column, locate the entry that starts with Data Source = server name ; Initial Catalog = DWStagingAndConfig; Persist Security Info=False . Replace server name with the name of the new computer running SQL Server.

Ensure that the values you entered were saved by querying the tables specified in the previous steps.

Close Microsoft SQL Server Management Studio .

Use the following procedure to update the data warehouse management server to use the new database server name:

Sign in to the computer as a user with administrative credentials.

Incorrectly editing the registry might severely damage your system; therefore, before making changes to the registry, back up any valued data on the computer.

In the Registry Editor window, expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\<version>\Common\Database .

In the right pane, double-click DatabaseServerName .

In the Edit String box, in the Value data box, enter the name of the new computer running SQL Server that hosts the DWStagingAndConfig database. If you're using a named instance of SQL Server, use the Computer Name\Instance name format.

Repeat the previous steps for the registry values to reflect the new name of the computer running SQL Server for the DWDataMart, OMDWDataMart, CMDWDataMart, DWRepository, and DWStagingAndConfig databases.

Use the following procedure to update data sources on the reporting server to point to the new computer running SQL Server:

Sign in to the Service Manager reporting server, and start Reporting Services Configuration Manager .

In the Reporting Services Configuration Connection dialog, connect to the correct reporting server instance as noted in the section To identify the reporting server and instance name used by data warehouse management server.

In the Reporting Services Configuration Manager list, select Report Manager URL .

On the Report Manager URL page, select the hyperlink that resembles http://Servername/:portnumber/Reports to open the Reports home page in your Internet browser.

On the home page, open the System Center folder, and then open the Service Manager folder.

Open the list of Service Manager items, and select the DWDataMart data source.

In the Connection string box, the string resembles data source=<server name>;initial catalog=DWDataMart . Replace the existing name of the computer running SQL Server by typing the name of the new computer running SQL Server.

Go back to the previous Service Manager folder webpage, and select the DWStagingAndConfig data source.

In the Connection string box, the string resembles data source=<server name>;initial catalog= DWStagingAndConfig . Replace the existing name of the computer running SQL Server by typing the name of the new computer running SQL Server.

Go back to the previous Service Manager folder webpage and select the ConfigurationManager data source.

In the Connection string box, the string resembles data source=<server name>;initial catalog= CMDWDataMart . Replace the existing name of the computer running SQL Server by entering the name of the new computer running SQL Server.

Go back to the previous Service Manager folder webpage and select the MultiMartDatasource data source.

In the Connection string box, the string resembles <root><source id='DWDataMart' connectionString='Data Source=<Server name>;Initial Catalog=DWDataMart;Integrated Security=True' /><source id='OMDataMart' connectionString='Data Source=<Server name>;Initial Catalog=OMDWDataMart;Integrated Security=True' /><source id='CMDataMart' connectionString='Data Source=<Server name>;Initial Catalog=CMDWDataMart;Integrated Security=True' /></root> . Replace the existing name of the computer running SQL Server by entering the name of the new computer running SQL Server.

Go back to the previous Service Manager folder webpage and select the Operations Manager data source.

In the Connection string box, the string resembles data source=<server name>;initial catalog= OMDWDataMart . Replace the existing name of the computer running SQL Server by typing the name of the new computer running SQL Server.

Close your web browser.

Use the following procedure to update the connection strings for the data sources on the server that hosts the Analysis Services database:

Sign in to the server that hosts the SQL Server Analysis Services database.

Open SQL Server Management Studio .

In the Connect to Server dialog, in the Server Type list, select Analysis Services .

In the Server name list, enter the server name that you received as output from the $OLAPServer.Server cmdlet. (You noted this information in the To identify the OLAP Account used by the data warehouse management server section earlier in this topic.)

In the Object Explorer pane, expand Databases , and then expand DWASDataBase .

Expand Data Sources , and then double-click CMDataMart .

In the Data Source Properties - CMDataMart dialog, select Connection string Provider=SQLNCLI10.1;Data Source=servername;Integrated Security=SSPI;Initial Catalog=CMDWDataMart .

Replace <servername> with the name of the computer running SQL Server that hosts the CMDWDataMart database.

You need to re-enter the impersonation account password when you've completed updating the Data Source server. Select the ellipsis button to the right of ImpersonateAccount and then add the password in the Impersonation Information dialog. Select OK to accept the changes.

Repeat the previous steps to update the connection strings for the DWDataMart and OMDataMart data sources.

Use the following procedure to start the Service Manager services on the data warehouse management server:

In the Services window, in the Services (Local) pane, locate the following three services, and for each one, select Start :

To learn about upgrading Service Manager, review Upgrade System Center 2012 R2 - Service Manager to System Center - Service Manager .

Was this page helpful?

Submit and view feedback for

Additional resources

IMAGES

  1. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

  2. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

  3. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

  4. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

  5. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

  6. Service Manager Database Tour & Useful Queries

    service manager database tour useful queries

VIDEO

  1. ServiceNow Database Queries: Best Practices & Things to Remember

  2. ServiceNow CMDB Fundamentals

  3. What is a Configuration Management Database (CMDB) (ServiceNow)?

  4. SQL Server Queries Part 1

  5. Lec-2: Introduction to DBMS (Database Management System) With Real life examples

  6. Database Queries 1

COMMENTS

  1. Service Manager Database Tour & Useful Queries

    The following are some useful database queries and in a way a tour of the Service Manager database. Sometimes it's just easier to query a database to get the information you need. Please use this information in a "read only" way.

  2. More Service Manager Useful Queries

    Dec 5, 2013 More Service Manager Useful Queries - Entity Change Log by Christopher Mank Awhile back, Travis posted a blog on some useful queries as a way to tour the CMDB (found here ). As he mentions, sometimes it is just faster and easier to get the data you are looking for just by querying the database itself.

  3. More Service Manager Useful Queries

    More Service Manager Useful Queries - Rule Workflows by Christopher Mank Awhile back, Travis posted a blog on some useful queries as a way to tour the CMDB (found here ). As he mentions, sometimes it is just faster and easier to get the data you are looking for just by querying the database itself.

  4. Getting Started with SQL Queries for your SCSM Data Warehouse ~ Service

    If you are a Service Manager army of one (or perhaps just a curious IT department member), you may find yourself juggling a host of technical abilities as you design basic management packs, query for certain types of data with PowerShell, but how long can you fend off your management before you're asked to produce reports about all the data flyi...

  5. SCSM 2012

    Service Manager Database Tour & Useful Queries. DISCLAIMER: USING SQL QUERIES TO ACCESS SCSM CONTENT IS NOT SUPPORTED AND RECOMMENDED BY MICROSOFT. BE SURE YOU ARE WIDE AWAKE WHEN YOU ARE DOING THIS. ... Throughout my experiences in implementing Service Manager 2012 (SCSM) for enterprise organisation, one of the challenge that repeat itself i...

  6. How to generate Incident Report with incident description field? in

    Service Manager - Data Warehouse, Reporting and Dashboards https: ... Search via Google for 'Service Manager Database Tour & Useful Queries', 31 Dec 2009 4:10 PM. I posted a nice query to use for reporting on system center service manager 2010 / 2012. Thursday, July 31, 2014 10:37 AM.

  7. SQL Queries

    Database Tour Documentation Working with SQL Queries How the Database Query Tool Works SQL Query Windows SQL Editor Query Execution Server Output Running Scripts with Multiple SQL Statements Asynchronous SQL Query Execution Queries with Parameters and Macros Using Master Source Querying Several Databases at Once How the Database Query Tool Works

  8. Data warehouse reporting and analytics

    In addition to its base that is built on the System Center common platform, the Service Manager data warehouse has two other databases: DWRepository-where the transformed data is stored and optimized for reporting purposes. DWDataMart-where the transformed data is loaded and where, ultimately, reports query from. The data warehouse was designed to:

  9. Prepare your RDBMS

    Service Manager supports both case-sensitive and case-insensitive Microsoft SQL server. To use Service Manager in case-insensitive mode, you must select a case-insensitive collation on the SQL Server before installing Service Manager.You can specify the desired case-sensitivity for sort order during the creation of the database. Set the SQL Server database to the desired collation when you ...

  10. Keys and indexes in Service Manager

    The full database dictionary record also includes field and column mappings. Typically, you will use database dictionary records to manage and move both logical keys and field mappings at the same time. Currently, Service Manager can only push keys as indexes on an RDBMS. It cannot read existing indexes (pull) from the RDBMS and create the ...

  11. Databases creation by System Center

    Before you start the installation of System Center - Service Manager, you may want to meet with your SQL Server administration team to discuss the effect Service Manager has on your computers running SQL Server, specifically the databases that are created.

  12. Service Manager Data Warehouse schema now available

    For custom fact tables, the Service Manager Data Warehouse infrastructure will automatically generate the code required to extract the data from Service Manager into the warehouse, and to load the data mart from the Repository database, but the transform code must be provided by the developer creating the custom fact table. 3. Outriggers.

  13. Reporting using the Servicemanager DB not the datawarehouse DB

    Friday, October 10, 2014 7:22 PM 0 Sign in to vote The operational DB is supposed to be hands-off. So, if you're going to do reporting directly against it, be aware of the following side-effects: Performance degradation: Workflows and background processes are constantly interacting with the database ensuring data stability.

  14. Manage the data warehouse

    The extract process starts on a scheduled interval. Extract is the process that retrieves raw data from your online transaction processing system (OLTP) store, which in this case is the Service Manager database. The extract process queries Service Manager for the delta data that has accumulated since the last time the extract process ran.

  15. Custom Reporting In Service Manager 2012

    How To Create a Custom Report in Service Manager 2012? I have to say it's amazing how difficult it is to get an answer to that question. The question sounds simple enough but after days of searching (yes I;ve spent days over the last few weeks trying to get a straight answer to this) The most common reply that I have seen is to go to the blog post at the below TechNet blog site that talks ...

  16. Using Groups, Queues, and Lists in Service Manager

    To create a new group. In the Service Manager console, select Library.. In the Library pane, expand Library, and select Groups.. In the Tasks pane, under Groups, select Create Group.The Create Group Wizard starts. On the Before You Begin page, select Next.. On the General page, do the following:. Provide a name for the group, such as Exchange Servers.. In the Description text box, enter a ...

  17. How to obtain Library list enums?

    Answered by: How to obtain Library list enums? Archived Forums 801-820 > Service Manager - Customization Using the SDK and Authoring Tool Question 0 Sign in to vote In the Service Manager Console, Click on Library on the left side. This displays a list of Lists. I'm trying to find out how to get access to these list items using the SDK.

  18. Service Manager workflows display Needing attention

    In the navigation pane, expand Databases, and then select ServiceManager. In the details pane, type the following command, and then select Execute: In the results that are displayed, verify the value that's displayed in the is_broker_enabled field. SQL Server Service Broker is disabled if this value is 0 (zero).

  19. Creating incident templates into Service Manager

    Incident Templates are stored in Management Packs. You can import Management Packs in the SCSM 2012 console: Administration -> Management Packs -> Tasks pane -> Import. I assume he means a csv file via powershell, not the connector. It doesn't matter.

  20. Move the Service Manager and data warehouse databases

    In the Server Type list, select Database Engine. In the Server Name list, select the server name for your Service Manager services database. In the Authentication list, select Windows Authentication, and select Connect. In the Object Explorer pane, right-click the Databases folder, and select Restore Database.

  21. SQL Query On Incidents

    This query is very useful but what if I want to display not only Tier but also status and classification category with a friendly name? They are all in one column in DisplayStringView. EDIT: Ok, I figured it out myself. I had to add more aliases in order to reference DisplayStringView.