[cisco-voip] SQL dips

Wed Aug 26 11:07:43 EDT 2009

Another option is to manually use the CDR-> Export CDR/CMR option which 
can export all the records into a single dump file.

Adam

-----Original Message-----
From: Dennis Heim <Dennis.Heim at cdw.com>
Sent: Wed, Aug 26, 2009 10:34:07 Am To: Wes Sisk <wsisk at cisco.com>, 
Kevin Dunn <cheesevoice at gmail.com>
CC: Cisco Voice <cisco-voip at puck.nether.net>
Subject: Re: [cisco-voip] SQL dips
Flat files is really the only way to go with appliance model. Make sure 
you use a sftp server not ftp. I have run into issues related to ftp. Do 
not use CoreFTP, after the trial ends it only allows 1-2 logins which 
slows down the export to the SFTP server which will delay the records 
being exported by several days. Then you would  need to parse the flat 
files and put them in a db.

Dennis Heim
Network Voice Engineer
CDW  Advanced Technology Services
11711 N. Meridian Street, Suite 225
Carmel, IN  46032

317.569.4255 Office
317.569.4201 Fax
317.694.6070 Cell

dennis.heim at cdw.com <mailto:dennis.heim at cdw.com>
www.berbee.com <http://www.berbee.com/>

*From:* Wes Sisk [mailto:wsisk at cisco.com]
*Sent:* Wednesday, August 26, 2009 10:05 AM
*To:* Kevin Dunn
*Cc:* Dennis Heim; Cisco Voice
*Subject:* Re: [cisco-voip] SQL dips

Hi Kevin,

I think you are missing the core point - the appliance is sealed in an 
effort to prevent this.  Measures are in place specifically to prevent 
outside dips due to potential performance impacts.

If you want access to the data the best approach is to send flat files 
to SFTP server and import those into your own database.  On the separate 
server you can trend, report, update, delete, and modify to your heart's 
content without impacting call processing.
In my experience I get significantly improved cdr query performance from 
even a default instance of mysql than with CM's instance of informix.

To your specific questions:
1. you will not be able to 'remove' the SQL database from CM7.0.  it is 
a sealed appliance. you do not get access to management tools as you did 
with CM4.x
2. you can run queries via CLI and via AXL.  CLI is limited in what it 
will do - characters such as '&' are blocked and there is no ability to 
output to a file.  AXL is also limited and bounded - queries above a 
certain size will be aborted due to impact on Informix and Tomcat.
Those services are required for real time activities such as call 
routing, MWI, and extension mobility login.

You will not be 'moving' the database.  Please have a look at the CDR 
offload process:
http://www.cisco.com/en/US/customer/docs/voice_ip_comm/cucm/service/7_1_2/admin/sacdrm.html

when flat files are generated they are copied to your external SFTP 
server.  At that point you take the files and load them into your 
database.  This is independent of any other processing that happens on CM.

Regards,
Wes

On Wednesday, August 26, 2009 9:16:29 AM , Kevin Dunn 
<cheesevoice at gmail.com> <mailto:cheesevoice at gmail.com> wrote:

Okay I think I have a firm grasp on what these guys are asking for:

They don't want a flat file, but rather the actual SQL Database, in CCM 
4.1 they exported teh CDR to their SQL server and performed the queries 
from there.

1.  If I remove the SQL database from CUCM 7.0 what will that break?

2.  Can I add a login to the CUCM7.0 just for running queries (read only)?

I think it is going to be easier to train the db guys how to log into 
CUCM and run their queries than it will be to move the data.  I am 
afraid of what will break if the database is moved.

Kevin

On Fri, Aug 21, 2009 at 12:08 PM, Dennis Heim <Dennis.Heim at cdw.com 
<mailto:Dennis.Heim at cdw.com>> wrote:

With the appliance model you would need to setup a CDR/billing server 
via sftp (have had problems in the past with ftp). It will push flat 
files to this server. Then you would need to put those into a db. Then 
you would have your CDR source in an sql db. There are 3^rd part cdr 
apps out that that will do all that for you.

Dennis Heim
Network Voice Engineer
CDW  Advanced Technology Services
11711 N. Meridian Street, Suite 225
Carmel, IN  46032

317.569.4255 Office
317.569.4201 Fax
317.694.6070 Cell

dennis.heim at cdw.com <mailto:dennis.heim at cdw.com>
www.berbee.com <http://www.berbee.com/>

*From:* cisco-voip-bounces at puck.nether.net 
<mailto:cisco-voip-bounces at puck.nether.net> 
[mailto:cisco-voip-bounces at puck.nether.net 
<mailto:cisco-voip-bounces at puck.nether.net>] *On Behalf Of *Kevin Dunn
*Sent:* Friday, August 21, 2009 12:43 PM
*To:* Cisco Voice
*Subject:* [cisco-voip] SQL dips

In CCM 4.1 we pulled the CDR and moved it to an external SQL server, so 
my boss and other directors could quesry 911 calls and International 
calls by department, while not actually manipulating the CCM server.

Now that we have upgraded to CUCM 7.0.2 I notice there are run sql 
commands and enduser is actually a table in the SQL database now, not 
just stored in directory.

My question:  How difficult is it to copy the CDR instead of moving it?
I would like to push the CDR/CAR to the SQL server like before but keep 
the original copy on the CUCM Publisher.  Can I do that (if so how)?

Is anyone else doing that?

The reason is my director is "an SQL guy" who doesn't like the canned 
reports from Cisco, he wants his own queries.  So my thought is if there 
are ample [run sql SELECT whatever from Whatever] commands he needs we 
may not need to MOVE the CDR and Car, rather he can learn to get the 
information from teh new CUCM server without destroying it.

Thoughts, Comments?

Kevin

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

_______________________________________________

cisco-voip mailing list

cisco-voip at puck.nether.net <mailto:cisco-voip at puck.nether.net>

https://puck.nether.net/mailman/listinfo/cisco-voip

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

_______________________________________________
cisco-voip mailing list
cisco-voip at puck.nether.net
https://puck.nether.net/mailman/listinfo/cisco-voip