CDR Database Copy or Migration to Another Server

This document describes how to copy the Call Detailed Records (CDR) database (DB) from the CallManager server to another Microsoft Structured Query Language (SQL) database server. The CDR database contains information for quality of service, traffic, user call volume, billing, and gateways.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

Cisco CallManager
Microsoft SQL Server

Components Used

The information in this document is based on these software versions:

Microsoft Windows 2000 Server
Cisco CallManager
Microsoft SQL Server version 7 and later

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Background

Supported Data Sources

The Data Transformation Services (DTS) Import/Export Wizard provides the simplest way to copy data between Object Linking and Embedding (OLE) DB data sources. After you connect to the source and destination, you can select the data to import or to export and apply transformations to the data that is being copied.

With the DTS Import/Export Wizard, you can connect to these data sources:

Most OLE DB and Open Database Connectivity (ODBC) data sources, as well as user-specified OLE DB data sources
Text files
Other connections to one or more instances of Microsoft SQL Server
Oracle and Informix databases (You must have the Oracle or Informix client software installed.)
Microsoft Excel spreadsheets
Microsoft Access and Microsoft FoxPro databases
dBase or Paradox databases

Topology

In this document, the data source is a Cisco CallManager server named DOC2, and the data destination is a Microsoft SQL server named DOC 4 without Cisco CallManager installed. Both systems run Microsoft Windows 2000 server.

Figure 1 – Data Source and Data Destination

Copy the CDR Database

In order to copy the CDR database, run the DTS Import/Export Wizard. It consists of two steps.

Shrink the CDR database.
Export the CDR database.

Shrink the CDR Database

One way to shrink the CDR database is to issue the dbcc shrinkdatabase SQL command:

Figure 2 – Shrink CDR Database

Choose Start > Run.
In the Open field, enter cmd, then press Enter.

An MS-DOS window appears.
Issue the osql -E command (see arrow A in Figure 2).

The -E parameter requests a Windows Authentication connection using your current Windows login account. This is the most secure way to connect to an instance of SQL Server.
Issue the use cdr command to point to the CDR database (see arrow B in Figure 2).
Issue the backup log cdr with no_log command to back up the CDR transaction log (see arrow C in Figure 2).
Issue the dbcc shrinkdatabase (cdr) command to shrink the CDR database (see arrow D in Figure 2).

Note: If DBCC displays error messages, contact your system administrator.

There are two other ways you can shrink the CDR database. Refer to Shrink the CDR/CAR Database Size for details.

Export the CDR Database

Run the DTS Import/Export Wizard to export the CDR database that is on the CallManager server:

Choose Start > Programs > Microsoft SQL Server > Import and Export Data.

The initial window for the DTS Import/Export Wizard appears.
Click Next.

The Choose a Data Source window appears.
Figure 3 – Choose a Data Source
From the Data Source drop-down list, choose Microsoft OLD DB Provider for SQL Server (see arrow A in Figure 3).
From the Server drop-down list, choose (local) (see arrow B in Figure 3).
Choose one of these for authentication:
- If you run CallManager version 4.x, click the Use Windows Authentication radio button (see arrow C in Figure 3).
- If you run CallManager version 3.x or earlier, click the Use SQL Server Authentication radio button (see arrow D in Figure 3) and complete the Username and Password fields.
From the Database drop-down list, choose CDR (see arrow E in Figure 3).
Click Next.

The Choose a Destination window appears.
Figure 4 – Choose a Destination
From the Destination drop-down list, choose Microsoft OLD DB Provider for SQL Server (see arrow A in Figure 4).
From the Server drop-down list, choose DOC4 (see arrow B in Figure 4).
For authentication, click either Use Windows Authentication or Use SQL Server Authentication, based on the server setup (see arrow C in Figure 4). If you choose Use SQL Server Authentication, then complete the Username and Password fields.
From the Database drop-down list, choose <new> (see arrow D in Figure 4).

The Create Database window appears.
Figure 5 – Create Database
In the Name field, enter CDR (see arrow A in Figure 5).
Adjust the Data file size and the Log file size based on the size of the source CDR (see arrow B in Figure 5).
Click OK.
Click Next.

The Specify Table Copy or Query window appears.
Figure 6 – Specify Table Copy or Query
Click the Copy table(s) and view(s) from the source database radio button (see arrow A in Figure 6).
Click Next.

The Select Source Tables and Views window appears.
Figure 7 – Select Source Tables and Views
Click Select All to select all of the listed tables (see arrow A in Figure 7).
Click Next.

The Save, Schedule, and Replicate Package window appears.
Figure 8 – Save, Schedule and Replicate Package
You can run immediately, replicate the data, or schedule the package to be executed at a later time. For the example in this document, click Run immediately (see arrow A in Figure 8).
Click Next.

The Completing the DTS Import/Export Wizard window appears.
Figure 9 – Completing the DTS Import/Export Wizard
Verify that the information in the Summary text box is correct. If so, click Finish. Otherwise, click Back as many times as is necessary to reach the window that requires correction.
If the run is successful, two windows will appear. One is the Executing Package window (see Figure 10) and the other is the successful copy window (see Figure 11).
Figure 10 – Executing Package

Figure 11 – Successful Copy
Click OK in Figure 11.
Click Done in Figure 10.