[cisco-voip] SQL dips

Wed Aug 26 10:34:07 EDT 2009

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 3rd 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20090826/996126fe/attachment.html>