Download This File                   Download All Documents                        Go to Home Page

Call_ID             long                Primary Key, unique number for each call

Engine               long                Identifies which box (it could be several)

Account             char(30)         Account to which the call will be charged

ANI                   char(30)         Number of the calling party**

DialTime           Datetime       Time at which the call came in.

AnswerTime      long               Miliseconds from seizure to Answer Supervision

CallLength        long                Miliseconds from AnswerSupervision to Call Drop

InboundCh        long                Inbound Channel (span x 100 + Time Slot number)

OutboundCh     long                Outbound Channels (span x 100 + Time Slot number)

DialResult        long                Result code (see Result Codes list below).

DialedStr_i       char(30)         String dialed received at call setup.

DialedStr_o      char(30)         String dialed by the switch into the outbound channel

RouteIdx          long                ID of the destination.

InGroupID        long               Client ID

OutGroupID     long               Outbound trunk group

Sell_Cost          float               How much the Account was charged.

Buy_Cost          float               How much did the call cost*

*Unlike Sell_Cost that, in the case of prepaid accounts, requires to be calculated in real time, Buy_Cost is only needed for billing purposes and can be calculated at a later time at a Server or reporting computer. This reduces the switch CPU usage by OmniBox without any serious sacrifice.

**ANI: the value to be stored in this field is:

•     For tandem switching functions,  the ANI received 

•     For PIN verification functions the with auto PIN, the matching authorization code

    It not necessarily the ANI sent with the outbound call (protocol allowing) which is determined by the stored procedure proc_GetANItobeSent that will be called from the OmniBox just before making the call.

CREATE PROCEDURE proc_GetANItobeSent 
@ChIn int, @ChOut int, @RouteIdx int, @AccNum Varchar(30), @ANI Varchar(30)

AS

/*EXAMPLE 1: ANI starts with a 4 then has up to 4 random digits and ends with the slot number*/
--SELECT '4' + Cast(cast(RAND(@ChIn)*9999 As int) As Char(4))+Cast(@ChOut%100 As Char(2))

/*EXAMPLE 2: Return same value as it had*/

SELECT @ANI

/*EXAMPLE 3 Return '0000000000'*/

--SELECT '0000000000'

The result codes:

When this feature is enabled in the 16th record of the sys_Parameter table, the call is partially logged into table Part_Log when the outbound call is CONNECTED. Of course, information related to call length is not yet available and even the result code may be subject to modification, When the call is finished, the missing information is supplemented, the temporary information modified if necessary and the call  is logged into the Log_CDR table. If for some reason the switch crashes or power goes off. the information is kept and then recovered when the OmniBox is loaded again. In the case of real-time billing, the corresponding accounts are charged and re-activated (InUse flag set to false).

The Centralized Dual Server  setup may only be used when the link between the OmniBox and the server computer running the OmniBilling system is 100% reliable. This kind of reliability is only attainable if the OmniBilling and the OmniBox run on the same computer or on two different computer but in the same LAN. 

If the available link is only through a WAN (and sometimes even within a LAN) the link may not be 100%. If the link goes down, OmniBox, tries to reconnect but it may eventually use an emergency exit as a last try to reestablish the link, something very near to a crash, . For these cases, replication is the way to go. 

Microsoft SQL, standard editions (no desktop), provides sophisticated replication features that, in principle, can keep the necessary data sync among the OmniBilling and OmniBox, but:

• Can no longer use the developers free license of the MS SQL server, in other words...money

• Can no longer use the Desktop version under Windows workstation, you will need a server (more $)

• If the link to Internet is broken, the replication distribution may not restart on its own, requires close monitoring (still more $).

• SQL Publication, subscription, distributions and security issues make take a professional to install and maintain (and more $).

• Avoiding loops could become tricky for tables, like Balances, that must  be both published as well as subscribed articles. Loop problems will multiply if there's more than one OmniBox or more than one OmniBilling. 

The information is generated by data entry and is relatively infrequent if compared to that generated by traffic at the OmniBox, CDR's , account balance updates and ANI  black list entries. Microsoft Replication drawback accentuated especially for traffic generated data. The Direct Replication Scheme was developed to provide a better solution to this problem.

In the reverse direction, data entry at the OmniBilling database, rates, accounts or new destinations can be replicated manually or even using standard Microsoft Replication but now the Reverse Replication Scheme (see below) is also available. 

If using Microsoft Replication, the OmniBilling being the Publisher while the OmniBox would be the Subscriber. The Articles for the Publication should be:

• ANIBlackList

• AuthorizationCodes

• Balances

• IdxLookup

• LegBRateLookup

• Rep_Companies

• Stm_SpeedDial

• Tec_BlockList

• Tec_ExchCodes

The Direct Replication Scheme

When a call is finished, the data for SQL statement are saved to the table Log_PendingRepl, these may be CDR's and/or Account updates. Also when a call is received the same is done to update  the ANIBlackList table. These transactions are::

EXEC proc_CDR.<All parameters>

UPDATE Balances Set Last_Update = `<Current Date>`, First_Use = `<Date Value>`, Balance = '<New Balance' Where cl_id = `<Acc<`

UPDATE ANIBlackList Set AttemptCount = AttemptCount + 1 Where @ANI = '<ANI>'

Notice the use above of the accent instead of the apostrophe, this is because apostrophes have a functional meaning within SQL statements, this way you avoid the SQL interpreter from misinterpreting.  

With this scheme in place, why not use it to synchronize the trunk group information with Report Auxiliary Objects in the OmniBilling database? So, triggers for tables dia_InCh and dia_OutCh have been developed to also append INSERT INTO GroupInfo, UPDATE GroupInfo and DELETE FROM GroupInfo statements in the Log_PendingRepl table.

Log_PendingRepl

An application called SrcRepl.exe runs on the same computer as the OMNIBOX. It reads from the Log_PendingRepl table and sends the SQL in a UDP packet with a sum check to the SQLReplic.exe running on the Server Computer. The ReplicSQL commits the transaction on the SQL Server and, if successful, returns an acknowledgement UDP packet (ACK) to SrcRepl.exe. Only after receiving the ACK is that it will delete the record from the Log_PendingRepl table. SrcRepl will re-send the ACK packet to the OmniMonitor and it will blink the Replic indicator green.

If for any reason SQLReplic can’t perform the transaction, then it sends a not acknowledge packet (NAK) to SrcRepl. When a NAK is received by SrcRepl, it will wait 5 seconds and re-send the same SQL but will send a NAK instead to the OmniMonitor that, this time, will blink the indicator in red. The same SQL will be re-resent until the transaction is successful. After 20 replication failures in a row, ReplSrc will send and alarm to the Watchdog (Alarm 12).

The SQLReplic window shows the last transaction performed and the result message below. When there is a replication failure, you can check on this window to figure out what is going wrong

Possible reasons for transactions to fail:

SQLReplic makes an ODBC connection to the billing database, the default DNS is ReplSrc, but a different one may be specified in the S/ command line switch. This connection may require a user name and password, maybe a different port number than the default 1099 or if the computer has several network adapters you may need to specify which IP is it that you need to work with. You specify these parameters using the SQLReplic command line switches. Syntax below:

SQLReplic.exe N/[port Number]  U/[User name] P/[Password] I/{Ip address] S/[ODBC Source name]

There are also entries to the OmniBox Database that are needed for reports at the OmniBilling server, these are trunk names, its reference ID's and data like LegACost, provider ID's and default accounts. Though for manual entries the Microsoft Replication is good enough, since this scheme is already set up we might as well use it. Triggers procedures for the dia_InCh and dia_OutCh tables are provided that generate records in Log_PendingRepl that will do the necessary updates at the OmniBilling database.  

The ReplSrc.exe Setup

The ReplSrc parameters are not read from the command line as the SQLReplic, but from the sys_parameter table. ReplSrc parameters are the ones belonging to set 100, 

* Legacy from the old Watchdog, unless you are using the old Watchdog app, this parameter is irrelevant.

**This is needed for the acknowledge message sent to the OmniMonitor

***For trouble shooting by #include only, must be always set to 0. 

ReplSrc uses UDP port 1998. 

The Reverse Replication Scheme

The scheme is basically the same changing the source and the destination SQL servers. There is another difference, in the Direct Replication Scheme, it is the OmniBox.exe that does most of the population of table Log_PendingRepl while in the Reverse Population Scheme population is performed by triggers in the tables that must be synchronized, namely:

• IdxLookup
• Balances
• AuthorizationCodes
• LegBRateLookup
• tec_BlockList
• Tec_ExchCodes
• ANIBlackList
• Stm_SpeedDial

• Rep_Companies

These triggers will append a record into Log_PendingRepl for every change to any record in any of the above tables, this holding even for multiple row transactions.

The Extended Replication Scheme

To attain full OmniBox<->OmniBilling synchronization, the Reverse and Direct replication schemes are not enough, that's why the Replication Scheme had to be Extended. 

First problem is that not all the information to be replicated is equally urgent, balance updates, account blocking, etc. should have the highest priority, changes to routes and trunk groups come next, while CDR's can wait. To implement this feature a the field "Priority" has been added to the Log_PendingRepl table, those records with the smallest value in the Priority field will be replicated first.

There is another issue, what if there is more than one OmniBox in the system or, though less likely, more than one OmniBilling? The SrcRepl.exe application V2.0 can now send UDP messages to more than one destination, each at its own pace. Records won't be deleted until sent to every register destination. The default destination Set_ID in sys_Parameters is 100, a second destination destination can be registered a by a second set of records with Set_ID = 101 and so on.

The main difficulty in the implementation of this feature y not that of "broadcasting" the replication transaction messages, but allowing each destination to have its own record set within the Log_PendingRepl. No longer can a record be simply erased upon receiving an acknowledgement, because this record may still be pending for another destination. To implement the feature, still another field was added to the Log_PendingRepl table, the pnp, that stands for Prime Number Product.

Each destination will have a prime number assigned, the first will have a 2, the second a 3, next 5, 7, 11, 13, 17 and so on, up to the 16th primary number, which is 53. For, say 3 destinations, the default for pnp field, upon record append, will be set to the product of the first 3 prime numbers in the above list or 2 x3 x 5 = 30. When the replicator for the second destination, the one described in in sys_Parameters with Set_ID = 101, receives an acknowledge from its SQLReplic, instead of deleting the record, it will set the pnp field to its current value devided into its prime number (which is 3). The result could be 10, 2 or 1 depending on the acknowledgement order. When this division renders a 1, it would mean that the transaction has been already replicated to all the registered destinations and so, only then, it can be erased. Each replicator will only "see" those records in which pnp is divisible by its prime number.

The replication acknowledge indicator has been modified after OmniMonitor V3.2.0 to show from which destination the acknowledge message (or not acknowledge) is coming from:

Log_PendingRepl for the Extended Replication Scheme

Versions of the OmniBox greater than 4..5, support the Extended Replication Scheme, versions of the SrcRepl and SQLReplic, must also be greater than 2.0 .

Reporting will require information from some of the OmniBox tables that have been already discussed in Chapter 3 and 4. Information in the following OmniBox tables is required in the reporting process:

The tables below are mainly needed at billing time but will be queried by OmniBox in case of functions with PIN verification:

The LegB tables below are required only for billing, but in the case of prepaid accounts, billing must be made in realtime:

GroupInfo ( for centralized setups is a VIEW but for the Replicated Dual Server Setup is a TABLE)

These tables must be made accessible to the reporting system or its information synchronized, manually or automatically to local tables. In systems with more than one OmniBox, it gets somewhat more involved. Dia_OutCh and Dia_InCh won’t do because there is no field to identify which box a record belongs to. In this case, these names will correspond to views of the GroupInfo object joined to the QueryData.

Int2 , for the PullRecs row means Engine while Int1 will specify the temporary table Log_CDR1 is to be truncated (<> 0) or not (0) before pulling records from the main CDR table. Int3 and Int4 are used to introduce filters on Domain and Ranges respectively when pulling records, This is useful when you need to invoice only one client for a week, if you filter, the pulling query will run faster for you don’t need to pull all the records.  

The dia_InCh view will show GroupID as DomainID, Description, DefaultAcc and LegACost Where IO = 1 and Engine = 20. The dia_OutCh view will show the GroupID as RangeID, the Description and the Provider for IO = 0 and Engine = 20.

The GroupInfo table must be kept synchronized with all the dia_InCh and dia_OutCh tables in all the OmniBoxes. To do this the mentioned tables have triggers that, if activated, will either directly update this table if in Centralized mode or insert a transaction record into the PendingRepl table that will automatically update GroupInfo in the Billing server through the #include proprietary replication technology..

Local to the reporting system you must find the following tables:

Log_CDR      As define above

Log_CDR1    Temporary table with the same structure as Log_CDR but with two additional   integer number fields, namely RateID and CostID, that will store the rates and costs rate record identifiers that were valid at the time of the call. This table is indexed in almost every field to speed up the queries in the reports.

Rep_Engines        Names each of the OmniBox’s in the system.

This is an open table, the OmniBilling structure and queries only uses [Company ID], [Company Name] and Type fields. The invoice report also references the contact and address related fields, but the user is free to add or remove fields as needed. Also a  hierarchical company structure can be made using the [Parent Company ID] field.

QueryData:    Is an auxiliary table that holds data to views and stored procedures involved in the reporting process.  For example data in DataID=1, will instruct stored procedure Rep_PullRecs to insert into Log_CDR1 record in Log_CDR from Date1 to Date2. If Int1 were < > 0, Log_CDR1 would be truncated before insertion, Int2 will hold the Engine ID for the records to be pulled.

This Package has been programmed using Microsoft ACCESS but queries are run mostly from the Microsoft SQL Server using views and stored procedures.

The Log_CDR table is usually huge, it could hold millions of records corresponding to every call attempts ever made through the system. In a standard 4T in / 4T out system, 20000 attempts a day is a typical figure. Even a system like this will hit a million in 50 days. Indexes of big tables can become huge in size and also add overhead when inserting records, operation that must be done in real time by the switch or the replication system.  That is why Log_CDR is only indexed on Call_ID/Engine and DialTime. Only queries involving these fields are going to yield in reasonable times.

The above explains why the any report operation in this Package must be preceded by a Pull Records operation. This operation consists in copying record from  Log_CDR between two dates into the temporary table Log_CDR1.

Once the records are pulled, any of the reports may be previewed and then printed. Records can be pulled just copying the fields to Log_Call1 or with cost fields calculation (Buy and Sell costs will be calculated for each record using the valid rates to its DialTime). This last way is slower but you need it only for invoicing or to pay the providers, if you only need summaries or stats you may uncheck Calculate Moneys.

The invoice number is automatically generated and unique for each date and company. The formula is: (Year(First Call) Mod 1000)*100000+(Week number)*1000+Company ID

There is also an optional WEB reporting tool that can be accessed with a browser,  example

Dispute Reports

Is common practice among carriers and its clients or carriers and its providers, to send the CDR’s when a dispute generates concerning service charges. The CDR’s normally have more information than that required to settle a dispute, so actually the best is to supply only the information that’s needed to ease the review. To generate a dispute report you must first pull the records for the days in question and then hit the one two rightmost buttons in the Report Center form. Which one will depend upon the dispute being with client or a provider.  One of the two forms below will show:

The Dispute Table for a provider looks like this:

And for a client, very similar:

The differences being that the Company name is that of a client; the Ch in the case of a provider is an outbound channel while if a client, an inbound one; the charge also corresponds to the buy or sell rates respectively.

The dispute table can further be filtered by Destination, Ch, etc. to reduce the records to only those involved in the dispute. This table can be exported to any requested format using Microsoft ACCESS export features.

The information on client and provider companies must be entered into the Rep_Companies table in order for it to properly show up in the Invoices and Pay Provider reports. The following is an entry form that will help accomplishing this task.

The full mailing address field need not be entered, it is automatically assembled by the form.

One of the most difficult parts of the accounting task in telephony traffic wholesale is that route rates change in a daily and sometimes even hourly basis. In order to produce an invoice CDR and rates must be combined to render the right charges. This package can do this for you, all you have to do is enter a record into the LegBRateLookup table every time a route changes its rate for some client type. The ActivationDate field must bear the time at which this rate is to become effective (or became effective). During the Pull Records operation the only the valid rates will affect the Sell_Cost calculation. There is an entry form for  the selling rates:

The most frequent use of this form is to change rates, remember that for this, you don’t edit records but you append new ones with a later activation date.

The form will list all the companies in that type in the Companies by Client Type subform and below all the route records ever defined for that client type. You can filter and sort this list at will in order to help your new entries. A combo-box will help you pick among the available routes in the IdxLookup table and the activation date will default to the current day at midnight.

LegBRate Setup

It is frequent that you need to create a new client type which leads to create a new set of  rate records for it. The task of entering them one by one must immediately make you wonder if there is a better way, well… there is. Hitting the button Open Utility for appending multiple records, will open the form bellow that comes to the rescue.

The idea is to cut & paste from the IdxLookup Subform in the left into the temporary one in the right. Enter the right values in the textboxes at the bottom of the window and hit create Leg B Rates, the records will be appended to the LegBRateLookup table. Once created you may edit the rates and Peak time field issues.

The creation of new client types usually involves entering rates from a given rate table in EXCEL, comma delimited text or any other typical format. To help automate this task, the Prepare Import Utility form is provided in this Billing Package. To use this tool you must first import the rates data into the New Rates table. On how to do that, read Microsoft ACCESS help.

To import rates into a newly created set of records in the LegBRateLookup table, there must be joining fields and the only possible are the Destination in IdxLookup and Country in New Rates. Usually, you may find a pretty good match, but there will always be a few exceptions. On the left top the Destination’s not matching any of the Country’s are listed. Left bottom you find the opposite. On the right IdxLookup as well as the New Rates tables are displayed for editing and/or appending to reduce the number of mismatches.

Once an acceptable match is attained, you may hit Do Import and the rates will be imported to all joined records. The remaining mismatch must be entered manually.

Also, a means is provided to update all the rates in New Rates incrementing them by a  percentage you may enter in a text box at the right center of the window.  The button Up Mark % will do the update.

There is a similar problem for the cost rates, providers can change rates as fast as you, so in order to calculate what your provider must be paid, the solution is very similar. The cost rates are stored in a very similar table called LegBCostLookup. To ease up the entry task there is a form:

The form will display all the route records ever defined to the selected provider. Combo boxes will drop down a list of all possible routes. The list of routes can be filtered and sorted at will to facilitate the copy and paste of entered information. The Do Import

Create multiple records opens the LegBRateSetup form, described above.

The New Provider Company button will open the Companies form shown above, with the company Type preset as “P” for client. The Prepare Import Utility button will bring the same Import Rate Utility form described above. The Do Import button will update the rates in all joined records.

The IdxLookup table is what associates dialed numbers to route indexes. The entry for to this table is invoke by hitting the IdxLookup EntryForm button in the Reports and Entry Form.

Manual entries to this table is the most labor intensive task involving dynamic routing. Not only there are hundreds of destinations with distinctive rates, but prefixes could be such that, even using wild cards, a single destination may require multiple records. To alleviate this burden, a table (SW_IdxLookupPrototype) with  IdxLookup prototype records to 459 destinations is provided with the OmniBilling system. Also a form is provided to further help in the task, it may be invoked by hitting the button Show Prototypes Form.

You may append all its records or just those corresponding to the supported destinations. The operation is straight forward.

Customer Service:

Any toll telephony service requires a minimal customer service where a client may call and change his PIN, check his balance or place a trouble ticket. The form below serves this purpose showing all it is to know about an account. To use it you must first pull the records for a time interval most likely to be disputed, only calls made during this period will show in the Account CDR’s subform.

Access to the Balances and AuthorizationCodes tables may need to be subject to security procedures since they store sensitive information. Microsoft Access provides means to secure such tables, consult its documentation on the subject. Also SQL security and custom triggers to log changes to these tables may be ordered from #include.

Calling Cards:

Calling Card accounts are just prepaid accounts. They must be set to client types below 100 so as to perform automatically the real time billing. This kind of retail will generate a lot of accounts, but the good news is that you don’t need to invoice them. OmniBilling provides tools to generates the account and PIN numbers for the calling cards. These tools are accessible through the buttons on the lower right corner the main form.

 The Generate and Fix buttons will show the two forms below respectively. The one the left is good for generating the accounts and the second for fixing potential mistakes in the generated accounts or changes that must be introduced after card generation, for instance, 1000 cards got printed with $10 instead of $15… What, throw them all away ? better is to fix the accounts to match the print  

 Calling Card Forms

All the accounts in the same batch will share the same values in the above shown fields: 

• Initial Balance- is the price of the card; 
• Client Type- will specify which set of rates apply;
• Language- will select the set of prompts to use in the IVR after Login (before Login, is the Language in the default account that prevails), 
• WelcomeID- is the prompt number to be used to welcome the customers after a successful login, a zero will mean no welcome prompt. The prompts sequence is as follows: The first prompt the caller hears is the Welcome ID of the default account, next the "Enter PIN" and then, if the login is successful, the prompt specified by this field . 
• CompanyID- refers to the company distributing the cards and its data can be found in the Rep_Companies table, the combo box with list all distributor companies for you to select. 
• RoutingSet- as explained in Chapter 4, routing sets allow to have premium, standard and economy routes. I such choices exist in your system, here is the place to specify what kind of routing the cards of this batch will be getting. Calling cards may also have expiration dates and maintenance fees.
• LegAFactor: Unless the Calling Card service can be access by a local phone call, there is a Leg A cost involved. This field establishes how much of this cost is being passed to the caller. Charging for the A leg, even though it will only show on the small print, doesn’t help sales but it discourages long “chats” with the IVR and helps bring down the Leg B rates, that normally get greater attention from potential customers.
• Life Span: Once the card is used for the first time, it “lives” only for the number days specified in this field.
• ExpirationDate- A card won’t be honored after expiration date. This field will establish an initial expiration date after which the card can’t be sold, it will show a default of 1 year from current date. Upon first use, the expiration date is set to the Life Span plus this first use day even if it goes beyond the initial value.
• Monthly maintenance will show a default of $.50 but it may also be set as needed.

Default Batch number (Next Match) for a new batch will be the last batch generated+1, default card count in the batch is 10000 and 10 the PIN number default character count. PIN’s are actually characters, not numbers, this meaning that 0123456789 is not equal to 123456789. The account numbers will be generated as <batch>_<PIN>, for example the PIN above pertaining to batch 23 will have an account ID of 23_0123456789. The number generator in OmniBilling guarantees that it never generates the same number twice as long as old accounts are never erased even after the cards has been exhausted. Cards may be recharged for the same card tenant but a number may never be reused on a new card. 

The Export button will prompt you for the batch to export having the last one generated as default . Then it will prompt you for the txt file path to which the list of numbers will be exported, the default being:

  <path to OmniBilling.mdb>/Batch_<batch number>.txt

for example “C:\Work\OmniBox\Batch_23.txt”. The format of the file is one number per line in quotation marks, example:

“0123456789”

“4256874125”

“5874695123”

etc.

The Activate button will prompt you for the batch to export having the last one generated as default . If the batch is completely deactivated y will be activated, if it is already fully of partially activated you will be prompted for doing full activation, deactivation or to cancel the operation.

Security Features *

Balances and AuthorizationCodes tables store information very sensitive to a prepaid or even wholesale operation. Open access to these tables make them easy targets for fraudulent entries. Unless all the sales and customer service tasks are attended by reliable partners, access to these tables should be, not only password protected, but all the user activity should be logged in a table accessible only to an administrator. OmniBilling provides such a table ( OpLog ) and the necessary functionality to keep track of who, what and when.

OpLog Table example:

These features involves triggers that are not activated by default because it requires setting up SQL server logins for the customer server operators and administrators.

*This option is not part of the basic Billing system, it must be explicitly ordered to #include.

Database Setup Options

The fact that OmniBox runs on two ODBC sources, namely OmniBoxSrc and the OmniBoxAccSrc, introduces the possibility of options on where tables accessed through OmniBoxAccSrc may reside and how they will be maintained. Currently there are three options:

1. Centralized Single Server (Default)

2. Centralized Dual Server

3. Replicated Dual Server

Some History

In early versions of the OmniBox all the tables resided in the same database, we can call this the Legacy Setup. The need of removing the billing activity from the telephony application soon appeared and the solution given was to create a second set of routing and administrative tables in a remote SQL server at the billing site, the CDR's were for the first time replicated using an early but very similar replication scheme. The rest of the tables where synchronized manually or by using the SQL Server replication, which ended up being done manually.

The introduction of the IVR functions and the real-time billing made the replication more complex bringing about the Centralized Dual Server approach. This approach moved the Billing Server at remote billing site to the OmniBox LAN but, this time into a different computer. The two sets of tables became the same or "Centralized" at the Billing computer. The OmniBox read the routing and administrative directly tables from the Billing Server. Parameter 18 in the sys_Parameters table switches between Legacy and Centralized Setup options. 

The success of the Centralized Dual Server led to the modification of the original single database default setup (the Legacy Setup) into the Centralized Single Server, Simply, the Billing database (OmniBilling) was brought into the OmniBox Server

The development of the Reversed and Extended Replication Scheme allowed the Billing server to go back to the billing site with a replica of the OmniBilling database, becoming the Replicated Dual Server setup option.

Centralized Single Server

OmniBoxAccSrc tables reside in a different database, namely the OmniBilling, but in the same server as the OmniBoxScr ones. These tables are said to be centralized because the same "central" tables are used by the OmniBox and also by the billing and reports application. The opposite to  central would be if there were a different set of tables somehow synchronized.

This option is simple to setup, but reporting must be made on the same server, with the same CPU and same resources as the OmniBox,  When the number of record in the reporting queries become too big, the resources drained from the OmniBox can cause some telephony operations to timeout bringing down the app. To this resource draining, it must be added that of a remote control application like the LapLink, used  to access the OmniBox that seldom shares same location as the administration office.

Centralized Dual Server 

In order to avoid using the same CPU and memory resources for running telephony and reporting, the OmniBilling database can be pulled away to a different server running in another computer on the same LAN or one with a fast and reliable link to it. Since the tables are still centralized, they must be queried by the OmniBox in real-time, any failure in the link, can bring it down. Centralized tables, having the advantage of its inherent OmniBox-Reporting data synchronization, posses the problem of table locking during data editing or entry. Caution must be observed while doing mass data editing, entry or deleting because OmnibBox can not access the table during the transaction and it may crash by timeout.

How to change from Single to Dual Server: 

• Copy a #include generic  OmniBilling database files to C:/MSSQL7/DATA and attach the database to the BillingServer
  • sp_attach_db @dbname = 'OmniBilling',
             @filename1 = 'C:\mssql7\data\OmniBox_data.mdf',
             @filename2 = 'C:\mssql7\data\OmniBox_log.ldf'
• Register OmniBox server in the Billing server Enterprise Manager
• Link the OmniBox Server to the Billing Server:
  • Execute the following stored procedure: sp_addlinkedserver '<OmniBoxServerName>', 'SQL Server'
• Link the OmniBilling Server to the OmniBox Server:
  • Execute the following stored procedure: sp_addlinkedserver '<OmniBillingServerName>', 'SQL Server'
• Transfer data from the "old" OmniBilling to the "new" one for the following tables (use the "delete existing records" option):
  • IdxLookup
  • Balances
  • AuthorizationCodes
  • LegBRateLookup
  • LegBCostLookup
  • tec_BlockList
  • Balances
  • Tec_ExchCodes
  • ANIBlackList
  • Stm_SpeedDial

  • Rep_Companies

• At the OmniBox, the OmniBoxAccSrc and the OmniBillingSrc ODBC Sources must be modified to the "New" Server
• Now is the time to quit the Keep OmniBox up App and unload the OmniBox.
• At the OmniBox Server you must change OmniBilling.dbo.<ObjName> to <BillingServerName>.OmniBilling.dbo.<ObjName> in the following Objects:
  • View dbo.Centralized_CDR
  • View dbo.Centralized_Balances
  • Part_All_To_CDR
• At the OmniBilling Server you must change OmniBox.dbo.<ObjName> to <OmniBoxServerName>.OmniBox.dbo.<ObjName> in the following Objects:
  • View dbo.GroupInfo
• The Keep OmniBox up, thus the OmniBox,  can now be restarted. Routing data will now be read from and the CDR's stored in the "new" server. 
• Transfer data from "Old" Log_CDR table in OmniBox server to OmniBilling in the "New". 
  • You may specify only the latest by using a query in the transfer data wizard. 
• Re-attach the following tables in the the OmniBoxSetup.mdb 
  • IdxLookup
  • Balances
  • AuthorizationCodes
  • LegBRateLookup
  • LegBCostLookup
  • tec_BlockList
  • Tec_ExchCodes
  • ANIBlackList
  • Stm_SpeedDial

  • Rep_Companies

Replicated Dual Server

Contrary to the centralized concept, there will be two sets of  tables but in two different locations that will need just a soft link of small bandwidth between them. These sets of tables must be synchronized and for that, we must use the Replication Scheme. With this option, the following disadvantages disappear:

• OmniBox goes down if the link to the Billing Server goes down
• Editing, appending and deleting records during administrative action won't lock tables for OmniBox.

Also the following advantages appear:

• The Link can be established through a WAN, the Billing server can be at the office and the OmniBox at the site and still fully synchronized
• The Server can be rebooted or the WAN link downed, without affecting the traffic.

How to Change from a Centralized Single or Dual to a Replicated Dual Server

• Change  OmniBox.exe version to > V4.50
• Change SQLReplic.exe Version to > V2.0
• Change SrcRepl.exe Version to > V2.0
• Change OmniMonitor.exe Version to > V3.20
• In case of promoting from a Legacy Setup, copy a #include generic  OmniBilling database files to C:/MSSQL7/DATA and attach the database to the OmniBox Server
  • sp_attach_db @dbname = 'OmniBilling',
             @filename1 = 'C:\mssql7\data\OmniBox_data.mdf',
             @filename2 = 'C:\mssql7\data\OmniBox_log.ldf'

• Transfer data
  • In case of promoting from the Centralized Single, transfer data from the "old" OmniBilling in the OmniBox to the "New" remote billing server for the following tables (use the "delete existing records" option):
    • IdxLookup
    • Balances
    • AuthorizationCodes
    • LegBRateLookup
    • LegBCostLookup
    • tec_BlockList
    • Tec_ExchCodes
    • ANIBlackList
    • Stm_SpeedDial

    • Rep_Companies

    The relevant data in Log_CDR's must be transferred to the New Server too, but this step, may take some time and may wait until the Direct replication scheme is setup.

  • In case of promoting from the Centralized Dual Server, it is likely that the Billing server is physically transported to the billing office, in which case, no data needs to be transferred to the "New" Server, since it already is, however, the  data from above tables in the OmniBilling database of the Billing server must be transferred to the OmniBilling Database in the OmniBox SQL Server to be used by the OmniBox. 

   

• Setup the Direct Replication Scheme

  At OmniBilling (Remote SQL)

  • Create the ReplSrc ODBC at Billing Server computer (local server and OmniBilling Database)

  • Create a Login SQLReplic for users the OmniBilling Database, set the password to SQLReplic also

  • Create a Shortcut to the SQLReplic.exe on the desktop with the correct command line: if the port is the default (1099) and there's just one IP,  the general command line

        SQLReplic.exe N/[port Number]  U/[User name] P/[Password] I/{Ip address] S/[ODBC Source name]

        must go particular as:

        SQLReplic U/SQLReplic P/SQLReplic

  • Start  SQLReplic.exe at the Billing server

  At OmniBilling (OmniBox SQL)

  • Run the OmniBilling_OmniBoxSide_ReplicDualServer.sql script i

  At OmniBox (OmniBox SQL)

  • Create the ReplSrc ODBC  (local server and OmniBox Database)

  • Run the OmniBox_ReplicDualServer.sql script in the OmniBox database.

  • Set the the following values in the sys_Parameter table

    Parameter 10 ParamNumValue = Do Pending Repl must be set to 1

    Parameter 10 ParamStringValue  = Log Locally can optionally be set to 1 if a copy of the CDR is wanted to remain as backup in the OmniBox

    Parameter 18  ParamNumvalue = Centralized must be kept as 1

    Setup the parameter set 100 (see ReplSrc.exe Setup)

  • Create a batch to keep the SrcRepl.exe app up, and name it SrcRepl.Bat;

            C:\Work\Utilities\SrcRepl.exe
            C:\Work\Utilities\SrcRepl.bat

  • Start ReplSrc.bat at OmniBox 

• Do CommitDb Changes for Sys_Parameter only or restart the OmniBox. CDR's should now start replication into the new Log_CDR at the remote server.

• Start transfering data from "Old" Log_CDR table  to the "New" (if not already started)

• Setup the Reverse Replication Scheme

  At OmniBilling (OmniBox SQL)

  • Create DNS source SQLReplicSrc, pointing to the OmniBilling database

  • Create a Shortcut to the SQLReplic.exe at the OmniBox desktop with the command line: 

                SQLReplic.exe S/SQLReplicSrc

                 if the port is the default (1099), there's just one IP, and if there's a trusted connection or it resides in the same computer,

                  the general command line will reduce to: 

  At OmniBox (OmniBox SQL)

  • Start SQLReplic.exe at OmniBox

• At OmniBilling (Remote SQL)

  • Create a batch in the Billing computer to keep the SrcRepl.exe app up, and name it SrcRepl.Bat;

                  C:\Work\Utilities\SrcRepl.exe
                  C:\Work\Utilities\SrcRepl.bat

  • Run the OmniBilling_CentralizedDualServer.sql script at the Billing server

  • Setup the parameter set 100 in the created sys_Parameter table created by the above script (see ReplSrc.exe Setup)

  • Start ReplSrc.bat at the Billing server