Office Communications Server 2007 R2 Tool: Database Analysis

  • Article

The Office Communications Server 2007 R2 Database Analysis Tool (DBAnalyze.exe) is a command-line tool that can be used to both troubleshoot and analyze a Microsoft Office Communications Server 2007 R2 deployment. DBAnalyze is an efficient and easy-to-use tool that provides its users with quick results. You can download this tool and other Office Communications Server 2007 R2 Resource Kit tools from the Download Center .

Author: Mike Adkins

Publication date: May 2011

Product version: Microsoft Office Communications Server 2007 R2

Introduction

The Office Communications Server 2007 R2 Resource Kit Database Analysis tool (DBAnalyze) is an easy-to-use command-line utility. It can be used as both a troubleshooting tool and an analysis tool. DBAnalyze provides Communications Server 2007 R2 administrators with an assortment of diagnostic information that ranges from the number of contacts for an Communications Server user, to the fragmentation statistics of the physical drive or drives that are hosting a Communications Server installation. DBAnalyze provides Communications Server administrators with user, diagnostic, conference, conferencing server, and disk reports. You can download this tool and other Office Communications Server 2007 R2 Resource Kit tools from the Download Center.

Description

Following is a brief description of the types of reports that can be created by using the various modes in DBAnalyze.

User Reports

Provisions contact, container, subscription, publication, permission, and contact-group data for a specified user or for users who have that specified user in their contact and permission lists. This mode also reports summary data for conferences that a user organizes or is invited to.

Diagnostic Reports

Provides a report that includes information about the Communications Server database tables (number of records, fragmentation, data size, and index size), data and log file sizes, the time of the last back-up, contact distribution among servers running Communications Server, the average number of permissions, contacts, containers, subscriptions, publications, endpoints per user, any improperly homed users, users that cannot be routed, the average number of conferences organized per user, scheduled conferences, active conferences, and the database version.

Important Do not extensively use the diagnostic reports feature of DBAnalyze during normal business hours. The diagnostic reports feature of DBAnalyze performs a resource-intensive set of queries on the Communications Server database to provide its full range of reports.

Conference Reports

When used during the conference, this reporting choice provides detailed data for any specific conference, including all schedule-time details for the conference, the invitee list, the list of media types that are allowed for the conference, active conferencing servers, the active participant list, and each participant’s signaling state.

Conferencing Server Reports

Provisions ID, media type, URL, heartbeat status, conference load, and participant load for each conferencing server in the Communications Server Enterprise pool.

Disk Reports

Provides the fragmentation status of all locally installed disks and mapped volumes (requires local administrator permissions for the server).

Output

Type DBAnalyze at a command prompt, and then press ENTER to get a list of the reports, switches, and usage examples in the Command Prompt window. This example information explains the versatility that DBAnalyze has to offer.

Using DBAnalyze in a production environment can result in some lengthy reports. This can be overcome by sending the DBAnalyze Command Prompt window output to an ASCII text file. By doing so, the report’s information can be opened in a text editor such as notepad.exe to be stored and reviewed as shown in the following example:

c:\>dbanalyze.exe /report:user /user:useruri@contoso.com > useruri.txt

Now use c:\>notpad.exe useruri.txt to view the newly created DBAnalyze report.

Following is the basic syntax that is required to use the DBAnalyze reports that are listed in the “Description” section of this article:

c:\>dbanalyze.exe /report:diag

c:\>dbanalyze.exe /report:user /user:useruri@contoso.com

c:\>dbanalyze.exe /report:conf /user:useruri@contoso.com /conf:1W9J71SKSX2X

c:\>dbanalyze.exe /report:mcus

c:\>dbanalyze.exe /report:disk

The following information describes the options that can be applied to the previously shown DBAnalyze command.

The DBAnalyze /SQLServer switch provides a way to point to the designated Microsoft SQL Server database software default instance or named instance that is hosting the Microsoft Office Communications Server 2007 R2 Enterprise Edition databases. Examples are as follows:

SQL Server named instance:

c:\>dbanalyze /SQLServer:SQLServerHostName\SQLServerInstance /report:user /user:useruri@contoso.com

SQL Server default instance:

c:\>dbanalyze /SQLServer:SQLServerHostName /report:user /user:useruri@contoso.com

The DBAnalyze /SQLServer switch is not needed when creating user reports from the Microsoft Office Communications Server 2007 R2 Standard Edition server because its SQL Server installation is local to the server that Office Communications Server 2007 R2 Standard Edition is installed on.

The following DBAnalyze switches are used with the DBAnalyze reports features to provide information in the required format:

/v Provides verbose output for the reports that it is applied to.

/maxcontacts Specifies the maximum number of contacts allowed per user (defaults to 150).

/maxpermissions Specifies the maximum number of permissions allowed per user (defaults to 300).

/conf Specifies the conference to query for. This switch is only used by the conf report type. The value is the external conference ID for the conference.

/pstnid Specifies the public switched telephone network (PSTN) meeting ID of the conference to query for.

Purpose

DBAnalyze can provide real-time reports for the various topics that were previously mentioned in the “Description” section of this article. Here are a few ways that these reports can be used to address certain Communications Server issues:

  • User sign-in issues.
  • User contact information.
  • User-specific reports.
  • Conference-specific reports.
  • Locating the Communications Server servers that are hosting specific conferencing servers in an expanded topology.
  • Locating the Communications Server web components server for the Communications Server Enterprise pool.
  • Gather baseline and peak-period reports about Communications Server performance.

Requirements

The Office Communications Server 2007 R2 Resource Kit tools are supported on the following Window Server operating systems:

  • Windows Server 2008 operating system
  • Windows Server 2003 Standard Edition operating system with Service Pack 2 (SP2)
  • Windows Server 2003 Enterprise Edition operating system with Service Pack 2 (SP2)

DBAnalyze requires the equivalent permissions from a member of the Domain Admins group in the Active Directory Domain Services domain that is hosting the Communications Server installation.

Examples

DBAnalyze provides quick and easy access to both Communications Server user and server infrastructure information. Here are a few examples of how to use DBAnalyze to gather important information while troubleshooting issues with Communications Server.

Load-balanced Microsoft Office Communications Server 2007 R2 Front End Servers separately host roles such the Address Book Server, User Replication service, and the Web Conferencing service. Researching each of these roles can become part of a troubleshooting process. Use the following DBAnalyze command to locate the Office Communications Server 2007 R2 Front End Server that is hosting one of these non-load–balanced roles.

c:\>dbanalyze.exe /report:diag > c:\>ocsDiagReport.txt

Task Name Fqdn

--------- ----

Endpoint Expiration server01.contoso.com

Subscription Expiration server01.contoso.com

Nightly Maintenance server02.contoso.com

User Replication / Address Book Server server02.contoso.com

Focus Maintenance server02.contoso.com

TimeBound Publication Maintenance server01.contoso.com

Conference Directory Replication server03.contoso.com

Remote PSTN Meeting Id Cleanup server03.contoso.com

Open ocsDiagReport.txt in notepad.exe, and then perform a text search for Task Name. Now locate the server fully qualified domain name (FQDN) that is hosting the role you are researching from a list that is similar to the previous list.

After updating some the Communications Server-enabled users’ work phone numbers by using the Active Directory Users and Computers Microsoft Management Console (MMC), make sure that the updated phone number information becomes part of the next Microsoft Office Communicator 2007 R2 Address Book download. To confirm that that this information will be available to the Communications Server Address Book Server, perform the following steps by using DBAnalyze.

Locate the Front End Server that hosts the User Replicator service as described previously in this article; for example, server02.contoso.com. Open the Office Communicator 2007 R2 Event Viewer, and then locate the following Informational event or events to ensure that the user replication service is operating properly.

Event Type: Information

Event Source: OCS User Replicator

Event Category: (1009)

Event ID: 30021

Date: 2/11/2011

Time: 4:48:05 PM

User: N/A

Computer: SERVER02

Description: User Replicator connected to domain controller SERVER02.contoso.com in domain contoso.com to perform synchronization

For more information, see Events and Errors Message Center.

Note The Communications Server User Replication service contacts every domain controller in the Active Directory Domain Services domain or domains that are configured to host Communications Server servers and users.

To ensure that the user account phone number updates are now part of the Communications Server user information located in the real-time communications (RTC) database, use the following DBAnalyze command:

c:\>dbanalyze.exe /report:user /user:useruri@contoso.com /SQLServer:SQLServerHostName > c:\useruri.txt

Open useruri.txt in notepad.exe, and then perform a text search for phone. Now locate the user’s phone number and confirm that it is correct:

User: jeff@contoso.com

------------------------------------------------

Resource Id : 4

Home server : pool01.contoso.com

GUID : 92e4150b-4ab1-4547-97a0-9e5352b978d0

SID : S-1-5-21-3336197963-1521846921-3909844188-1135

Display Name : Jeff Smith

OptionFlags : 0x180 UC RP

ArchivingFlags : 0

Enabled : True

MovingAway : False

Unavailable : False

ForwardingUrl : NULL

RichMode : True

Contact Version : 1

Permission Version : 1

Email : Jeff@contoso.com

Phone : +4255550101

Presence data :

The previous steps confirm that the user account’s phone number update has replicated to the Communications Server backend database and is waiting for the next Address Book Service synchronization to finish.

DBAnalyze can be used to research Communications Server user logon failures. Use the following DBAnalyze command to research and perhaps correct this issue:

c:\>dbanalyze.exe /report:user /user:useruri@contoso.com /SQLServer:SQLServerHostName > c:\useruri.txt

Open useruri.txt in notepad.exe, and then perform a text search for User. Now locate the user’s information and confirm that it is correct:

User: jeff@contoso.com

------------------------------------------------

Resource Id : 4

Home server : pool01.contoso.com

GUID : 92e4150b-4ab1-4547-97a0-9e5352b978d0

SID : S-1-5-21-3336197963-1521846921-3909844188-1135

Display Name : Jeff Smith

OptionFlags : 0x180 UC RP

ArchivingFlags : 0

Enabled : False

MovingAway : False

Unavailable : False

ForwardingUrl : NULL

RichMode : True

Contact Version : 1

Permission Version : 1

Email : Jeff@contoso.com

Phone : +4255550101

Presence data :

Notice that the Enabled value is set to False when it should be set to True. The Communications Server user’s account has inadvertently become disabled and can no longer be logged on to. To fix this problem, simply access the properties for the Communications Server user account, and then select Enable User for Office Communications Server. Click OK to close the User properties dialog box.

DBAnalyze can also be used to provide point-in-time analysis of the overall performance of the Communications Server services along with table and index statistics, and file sizes for the RTC and RTCDyn databases. Use the following DBAnalyze command to gather the Communications Server diagnostic information.

c:\>dbanalyze.exe /report:diag /SQLServer:SQLServerHostName > c:\>OCSDiagnostics.txt

Open OCSDiagnostics.txt in notepad.exe, and then view the contents of the diagnostic report.

Summary

DBAnalyze is an efficient command-line tool that allows its users the convenience of real-time reports for their Communications Server infrastructure. DBAnalyze is designed to provide user, diagnostic, conferencing server, disk, and conference reports that can be used for troubleshooting various issues or be used as a baseline reporting feature. DBAnalyze is an easy to use data gathering tool that provides a beneficial user experience.

Additional Information

For more information, see the following:

Lync Server Resources

We Want to Hear from You

Keywords: dbanalyze, contacts, SIP, conference, address, book