Ozeki NG SMS Gateway logo OZEKI NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: +36 1 371 0150
SMS Gateway to send SMS via GSM modem or IP SMS connection Overview SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Quick start SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Download SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Manual SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection How to buy SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection FAQ SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Contact Us SMS Gateway to send SMS via GSM modem or IP SMS connection
OZEKI NG SMS Gateway - Product Guide

Performance Contents | SearchHigh volume SMS messaging

Home > Product Manual > User Guide > Administrator's guide > Increasing the availability
running two Ozeki NG

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  User Guide
  Quick Start Guide
  User Interface
  Configuration
  Client software
  Administrator's guide
  Cluster
  Load Balancing for SMPP v3.3
  Troubleshooting
  Backup
  Manual uninstallation
  Manual upgrade
  Performance
  Increasing the availability
running two Ozeki NG

  High volume SMS messaging
  Recommended computer
  Bandwidth
  Manual installation
  Failover clustering
(Windows Server 2003)

  Failover clustering
(Windows Server 2012 R2)

  SNMP
  Migration/Hardware Replacement
  Message priorities
  Message queues
  SMPP via VPN
  Developers Guide
  Examples and Solutions
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
  Cookie policy
 


Automated phone calls?
Ozeki Phone System XE VoIP PBX software is an advanced PBX built for automated voice calls and 2 way SMS messaging. It has outstanding APIs for software developers. It can be used for:

Phone calls from SQL
Phone calls from HTTP
Voice and SMS applications

Callcenter developers
If you are working on telephone solutions, please check out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software.

Contact Us!
If you wish to get further information, do not hesitate to contact us!

E-mail: info@ozekisms.com

If you have a technical question, please submit a support request on-line.

How to send out SMS messages from the same database table using two Ozeki NG SMS Gateway software products

Recommended reading:
If you wish to protect your service against hardware failure and reach higher availability of your Ozeki NG SMS Gateway software, we recommend you to try our Ozeki Cluster software product. Ozeki Cluster automatically moves any service to another computer in case of a hardware failure. You can download it from the following website: http://www.ozeki.hu/cluster

This guide provides information about how to run two Ozeki NG SMS Gateway software products simultaneously on different computers connecting to the same database to send out SMS messages. In this way your system will provide better uptimes and better tolerance against hardware related errors. If one of your Ozeki NG SMS Gateway or the PC on which the gateway is running stops due to any error, the SMS sending will be continued by the other PC. The solution in this text will help you to setup a MySQL database server and a database user in Ozeki NG SMS Gateway.


Figure 1 - Physical scheme of an Ozeki NG SMS Gateway cluster

What you need:

  1. Installing the two Ozeki NG SMS Gateway software products on different PCs
  2. Connecting the gateway to a service provider
  3. Creating a stored procedure in a MySQL server to avoid the sending of the same messages from the two gateways
  4. Adding new Database user and modifying its database queries
  5. Adding a new outgoing routing rule

1. Installing the two Ozeki NG SMS Gateway software products on different PCs

In order to start using Ozeki NG SMS Gateway, first you need to install them to your two computers. If you have any problem during the installation process, just follow this guide: Installation Steps.

2. Connecting the gateway to your service provider

After you have installed the two Ozeki NG SMS Gateway software products, you need to setup at least one service provider connection to send out your SMS messages. There are two ways of sending SMS messages:


You can configure the service provider connections based on the guides that can be found here.

3. Creating a stored procedure in a MySQL server to avoid the sending of the same messages from the two gateways

If you would like to send out SMS messages from the two PCs using the same database server and table, you need to modify the default SQL queries to avoid the situation that the gateways will send out the same SMS messages from the two software products.

First of all, you need to create the tables for outgoing and incoming SMS messages: ozekimessageout and ozekimessagein.

MySQL create table script:


 CREATE TABLE IF NOT EXISTS `ozekimessagein` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `sender` varchar(30) DEFAULT NULL,
   `receiver` varchar(30) DEFAULT NULL,
   `msg` text,
   `senttime` varchar(100) DEFAULT NULL,
   `receivedtime` varchar(100) DEFAULT NULL,
   `operator` varchar(100) DEFAULT NULL,
   `msgtype` varchar(160) DEFAULT NULL,
   `reference` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `id` (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 CREATE TABLE IF NOT EXISTS `ozekimessageout` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `sender` varchar(30) DEFAULT NULL,
   `receiver` varchar(30) DEFAULT NULL,
   `msg` text,
   `senttime` varchar(100) DEFAULT NULL,
   `receivedtime` varchar(100) DEFAULT NULL,
   `reference` varchar(100) DEFAULT NULL,
   `status` varchar(20) DEFAULT NULL,
   `msgtype` varchar(160) DEFAULT NULL,
   `operator` varchar(100) DEFAULT NULL,
   `errormsg` varchar(250) DEFAULT NULL,
   `callbackid` varchar(500) NOT NULL,
   `reason` varchar(1000) NOT NULL,
   `rn` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `id` (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


Now a stored procedure has to be created in your database server which will help you to prevent multiple SMS sendings. If one row will be queried, it will be locked, so the other gateway will not select the same SMS messages.

First you need to login to your database management software. You can use mysql console, phpmyadmin or any other management software. Then select the database in which you created the ozekimessageout and ozekimessagein tables and issue the following create script.

MySQL stored procedure:


DELIMITER //
CREATE PROCEDURE selectMessages(IN counter INT(11))
BEGIN
  DECLARE random INT(11);
  SET random = FLOOR(RAND()*50000)+FLOOR(RAND()*50000);
  UPDATE ozekimessageout 
    SET status = 'polling',rn = random 
    WHERE status = 'send' 
    LIMIT counter;
  SELECT id,sender,receiver,msg,msgtype,operator 
    FROM ozekimessageout 
    WHERE rn = random AND status = 'polling';
END; //
DELIMITER ;


Figure 3 - Create script in PHPMyAdmin

4. Adding new Database user and modifying its queries

After you have created the two tables and the stored procedure, you need to add a new Database user to your two Ozeki NG SMS Gateway software products. In order to connect the gateway to a MySQL database, first you need to install a MySQL ODBC Driver to your Windows operating system and then add a new data source to it which will be connected to your database server.


Figure 3 - New data source

If you have added the new data source, return to your Ozeki NG SMS Gateway, install a new Database user and provide a connection string (related to your database server) under the Database connection tab in the Connection String textbox and select ODBC from the Connection string type dropdown menu.


Figure 4 - Provide connection information

Now go to the SQL for sending tab and change the default query of polling method. You need to call the previously created stored procedure (here you need to provide the number of messages that you would like to select). In the Check the ozekimessageout table every _ sec. for outgoing messages textbox you need to provide the number of seconds in which the gateway will always check for SMS messages with send status. In the Maximum number of messages to send with one poll textbox you need to provide the same value that you used when you called the selectMessages() stored procedure.

Note: do not check the database frequently (do not use very low values in the first textbox) and do not select a lot of messages in one query (do not use very high values when you call the stored procedure and in the last textbox).

Important: "selectMessages(25)" and "Maximum number of messages to send with one poll: 25" textbox have to be the same value.


Figure 5 - Give the previously created stored procedure

5. Adding a new outgoing routing rule

Finally, you need to create a new outbound routing rule which will forward the SMS messages from the database user to the the service provider connection that will send out the SMS messages.


Figure 6 - New outgoing rule

This guide shows you a solution how to send out SMS messages from the same MySQL database server, the same database and the same table using two Ozeki NG SMS Gateway software products. The stored procedure can also be created in different ways. It is also possible to use other database servers, such as MSSQL or Oracle.

If you have any other question, please send us an e-mail at info@ozekisms.com.



Appendix: If you wish to use MS SQL Server, you need to use the following create table script and stored procedure.

Create table script for MS SQL Server:


CREATE TABLE ozekimessagein ( 
    id int IDENTITY (1,1), 
    sender varchar(30), 
    receiver varchar(30), 
    msg nvarchar(160), 
    senttime varchar(100), 
    receivedtime varchar(100), 
    operator varchar(30), 
    msgtype varchar(30), 
    reference varchar(30), 
);

CREATE TABLE ozekimessageout ( 
    id int IDENTITY (1,1), 
    sender varchar(30), 
    receiver varchar(30), 
    msg nvarchar(160), 
    senttime varchar(100), 
    receivedtime varchar(100), 
    operator varchar(100), 
    msgtype varchar(30), 
    reference varchar(30), 
    status varchar(30), 
    errormsg varchar(250),
    rn int 
);


Stored Procedure for MS SQL Server:


CREATE PROCEDURE selectMessages
    @counter INT
AS
    DECLARE @random INT
    DECLARE @upper INT
    DECLARE @lower INT
    SET @lower = 1
    SET @upper = 50000
    SET @random = ROUND(((@upper - @lower - 1) * RAND() + @lower), 0)
	
    UPDATE TOP (@counter) ozekimessageout 
      SET status = 'polling', rn = @random 
      WHERE status = 'send'
    SELECT id,sender,receiver,msg,msgtype,operator 
      FROM ozekimessageout
      WHERE rn = @random AND status = 'polling'
GO


SQL for Sending \ Polling tab of the Database user:


EXEC selectMessages @counter = 25;

Important: @counter and "Maximum number of messages to send with one poll" textbox have to be the same value.

Dig deeper!
People who read this also read...





Next page: High volume SMS messaging
Copyright © 2000 - 2018 Ozeki Informatics Ltd.
All rights reserved

Software PBX for Windows | VoIP SDK   |   Legal information   |   Privacy policy   |   Terms of use
Please, address your inquiries to info@ozekisms.com