CCX External Database High Availability

[James Hawkins]

Hello,

I have a new customer for whom I am developing a management portal for CCX that allows them to modify config data including opening hours, holidays, emergency states, forwarding, menu options etc.

As they have CCX Premiim I have decided to use MS SQL Server to store this data and use Access forms to provide the front end.

This is working pretty well but as (unlike using XML file based option management) the data lives off box from CCX's perspective so I need to provide some resilience for the database and am looking for recommendations for this.

Ideally the SQL Server would be resilient using some form of clustered IP address but I believe this need SQL Server Enterprise edition which is $$$$$$$$$$$.

Standard edition can do replication but then I would need to code my script to be able to try a second server if the primary is unavailable. Would I just wait for the Timeout value to expire in the DB Read script step and then try reading the data from the sub? - that seems very clunky as it would introduce a delay for every DB read (and there are approx six per call).

Are there any better ways of doing this?

[david.macias]

So when you move to 12.x a lot of the things you've built will be obsolete as they will be provided out of the box. Yes the timeout solution to go to a secondary server is clunky, but I think it's your best bet. If you want to get really fancy you could do this a different way. When you modify your DB, you automatically built XML that you can feed back to UCCX. At that point CCX only cares about the XML file which is always available and you remove the CCX to DB connection from the equation. Another option is to build a REST API as load balancing HTTP requests is significantly cheaper and easier.

david

[Anthony Holloway]

I don't agree that a lot of the things he's building will be obsolete in UCCX 12.0.

For example, the only new things UCCX 12.0 brings to the table, in the context of this discussion, are holidays and business hours. However, those actually have some limitations to them, which for me, are a show stopper for using them, in 80% of my work. I'll continue to use the existing methods for now.

Since you'll likely ask, here's one: The business hours feature is boolean, either you're in the hours or your not. There's no intelligence to know which hours you're in. So, if you need to know the difference between different time ranges in the day, you will not be able to use the built-in functionality. E.g., Route calls to a 3rd party answering service from 8:00pm to 12:00am.

Then, he also said things like: emergency status, forwarding and menu options, none of which UCCX 12.0 can do.

I do agree with you on the suggestions you made to improve the data access, especially since he could then leverage this functionality in his customers who are on Enhanced.

In any case, we should all be thinking: "should this fail, which path do I take by default?" In some cases, that might be an End step to force the SYSTEM PROBLEMS announcement, and in other cases, it might be a default route to an Agent. Only the business can say what they are willing to allow happen.

[James Hawkins]

Thanks for the responses guys.

I am aware that 12.x allows built in management of opening hours and holidays but I have more information than that to manage and want to provide a single portal for management. The extra information includes menu options that are created dynaically based upon calling number.

This customer has CCX already and the partner that implemented it provided a web based portal that outputs a (very large) XML file which is then uploaded to CCX by dialling an application that grabes the XML off the web server and uploads it to the repository. This portal is written in Classic ASP and is very difficult to modify - the customer is also concerned that the exiting partner will not allow them to continue using the portal plus has concerns about maintaining it.

I was tasked with coming up with a replacement and I believe that, in terms of supportability, flexibility and functionality SQL Server offers the best option. We do not employ proper developers so building a REST based portal would have to be subcontracted as would building a more modern version of the existing XML front end.

SQL Server makes data accessible via lots of methods and is easy to update. It also offers user based security and the ability to restrict access to subsets of data both of which are required in this case. The main downside is if the SQL database is not available everything goes to hell which is not a good thing as their business relies heavily on the contact centre.

The data being in SQL also allows user front ends to be developed easily using MS Access forms with linked tables. which is ideal for a non developer like me.

I guess I am going to have to try building another SQL Server and then replicate my database to it and do some testing.

I will also investigate how easy it is to export SQL tables as XML to be uploaded to the CCX repository - if anyone has any links for this please share.

[david.macias]

I think you will be very surprised how cheap it would be to build a webpage/REST endpoints. Honestly, I think what you're building with Access/SQL is going to end up being like your current ASP solution in a year or two. Hard to maintain by anyone but you. From someone who did a massive Access forms "CRM" in university, building a webpage is a lot easier and it might be a good opportunity to learn a new skill if you want to go down that route.

You've mention the importance of this information to be available at all times, have you had not failure in your ASP solution before?

david

[James Hawkins]

Hi David,

Thanks for the response.

I have done some REST based scripts in Python which was reasonably straightforward but I have not been able to find any easy method of coding a web page that can act as a front end to a REST API.

I have looked for a long time for a framework to do this but have not found anything that is simple to implement without lots of coding - certainly nothing that compares to SQL Server and Access Forms.

I do kind of agree with you that using a thick client in this day and age is going against the tide but I cannot see anything else that gives me the ease of building a front end. I did consider whether I could use SharePoint Lists and access them via REST but dismissed that as this client uses Office365 so the SharePoint would be hosted and therefore subject to Internet outages etc.

If you can recommend a framework/language that I could use I would be very interested - I am always trying to expand my skillset.

Re. the existing ASP setup this is not something I implemented but, from what I have gathered, it has been reliable. The fact that it generates XML files which are then uploaded to the CCX repository makes the portal less mission critical than my proposed solution which requires the SQL Server to be operational to work.

Cheers

James

[Anthony Holloway]

Would you be interested in leveraging a globally available session object, which caches the SQL responses? It still doesn't get around a hard down situation for more than a few hours, but it does get around the occasional connection error, as well as lighten the load on the network and SQL server.

I have written about the topic before, and even provided a sample script. Granted, I was working with data from an XML file, but you could easily adapt this to work with any data source.

https://community.cisco.com/t5/contact-center/global-session/td-p/1538686

[James Hawkins]

Hi Anthony,

Thanks for your post but I am not sure that caching the SQL responses will resolve my design issue.

The application will have 100+ numbers that may be called to access it (I am using a wildcard trigger in CCX and mapping the DIDs to trigger numbers in UCM).

When a call arrives the script will run a SQL request similar to that shown below:

SELECT EmergencyState, HolidaySchedule, OperatingHours, MenuOptions FROM TriggerNumbers WHERE TriggerNumber = $strCallingNumber

This SQL request will only load the data relevant to the called number which seems more efficient than loading an entire XML doc which does have the issues explained in your excellent linked post.

I have been doing some research into SQL Server resilience options and it does seem that a clustered server which presents a virtual IP to applications is possible - I have a meeting next week with the customer's DBA so I am hopeful that they can provide this.

If that is not the case then I think if two SQL Servers were deployed and a DB subscription created between them I could code my CCX script to minimise delay if the primary SQL server is unavailable. I am thinking of doing a simple SQL request to the primary SQL server as a test that it is there. If it is then a boolean variable bUseSQLPrimary would be set to true. If the read fails I would try a similar read to the secondary SQL server. If that suceeds the boolean variable would be set to false. If it fails then the script would do some SQL down emergency stuff.

SQL requests further down the script would be preceded by an If statement using the boolean value with SQL steps configured for the Primary SQL under the True branches and for the Secondary SQL under the False branches.

I think this should work ok and will only introduce a delay for the initial SQL request if the Primary SQL server is unavailable.

I will update the thread after my meeting with their DBA.

[James Hawkins]

Hi David,

Thanks for the response.

I have done some REST based scripts in Python which was reasonably straightforward but I have not been able to find any easy method of coding a web page that can act as a front end to a REST API.

I have looked for a long time for a framework to do this but have not found anything that is simple to implement without lots of coding - certainly nothing that compares to SQL Server and Access Forms.

I do kind of agree with you that using a thick client in this day and age is going against the tide but I cannot see anything else that gives me the ease of building a front end. I did consider whether I could use SharePoint Lists and access them via REST but dismissed that as this client uses Office365 so the SharePoint would be hosted and therefore subject to Internet outages etc.

If you can recommend a framework/language that I could use I would be very interested - I am always trying to expand my skillset.

Re. the existing ASP setup this is not something I implemented but, from what I have gathered, it has been reliable. The fact that it generates XML files which are then uploaded to the CCX repository makes the portal less mission critical than my proposed solution which requires the SQL Server to be operational to work.

Cheers

James