High performance SQL to SMS gateway configuration using Oracle database server (500 MPS+)
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:
Ozeki Cluster Site
(Requires Ozeki NG SMS Gateway v4.5.0
or newer version)This guide gives you information on how to configure your Ozeki NG SMS
Gateway software to achieve a performance of 500 Messages Per Second (MPS)
or more in an SQL to SMS gateway environment. The solution is based on Oracle
and it requires the installation of Ozeki NG SMS Gateway.
Introduction
The configuration works the following way: Ozeki NG SMS Gateway connects to
an ORACLE database using an oledb connection. It reads outgoing messages from this database and sends these messages
to the Short Messages Service Center (SMSC) of an SMS service provider over the
Internet through an SMPP connection (Figure 1).
Figure 1 - Architecture overview of ORACLE to SMS configuration
How to setup
To setup this system, you need to perform the following steps:
- Setup your ORACEL database server
- Setup Ozeki NG SMS Gateway
- Configure an SMS Service Provider connection in Ozeki NG SMS Gateway
- Configure SQL to SMS forwarding
- Configure Ozeki NG SMS Gateway for high performance operation
Step 1.) Setup your ORACLE database server
For this solution to work you need Microsoft SQL Server 2008 (or Microsoft
SQL Server 2005). In ORACLE you should create a database. The recommended
name for the database is 'ozeki'. You should also create a user with SQL
server authentication, who can connect to this database. The username should
be 'ozekiuser', the password should be 'ozekipass'. In the database, create
the following table structure, and grant SELECT,INSERT and
UPDATE privileges to the ozekiuser user.
CREATE TABLE ozekimessageout (
id int ,
sender varchar(255),
receiver varchar(255),
msg varchar(160),
msgtype varchar(30),
stat int,
operator varchar(30)
);
CREATE INDEX idindex ON ozekimessageout (id);
CREATE index index_id3 ON ozekimessageout(id);
CREATE SEQUENCE z;
CREATE TRIGGER ozekimessageout_auto BEFORE INSERT on ozekimessageout
for each row
when (new.id is null)
begin
SELECT z.nextval INTO :new.id FROM DUAL;
end;
/
CREATE TABLE msgreferences (
id int primary key,
msgid int,
stat int,
reference char(200),
senttime varchar(100),
errormessage varchar(2048)
);
CREATE INDEX refindex ON msgreferences (id);
CREATE SEQUENCE M;
CREATE TRIGGER msgreferences_auto BEFORE INSERT on msgreferences
for each row
when (new.id is null)
begin
SELECT M.nextval INTO :new.id FROM DUAL;
end;
/
CREATE TABLE ozekimessagein (
id int ,
sender varchar(255),
receiver varchar(255),
msg varchar(160),
senttime varchar(100),
receivedtime varchar(100),
msgtype varchar(30)
);
CREATE index index_id4 ON ozekimessagein(id);
CREATE SEQUENCE U;
CREATE TRIGGER ozekimessagein_auto BEFORE INSERT on ozekimessagein
for each row
when (new.id is null)
begin
SELECT U.nextval INTO :new.id FROM DUAL;
end;
/
Figure 2 - Database table layout
If you take a look at this table structure you will notice several differences
to the default table structure used in the
standard Ozeki SQL to SMS solution. The first difference is that the
ozekimessageout table contains a stat field instead
of status and this field is an integer. The reason for the change in the name
of the field is that the status word is a reserved word in ORACLE. The reason
for the change in the
field type is that it is significantly faster to update integer values, then to
update strings in a database.
In this ozekimessageout table the stat field can have the following values:
| Value | Meaning |
| 1 | Message waiting to be submitted |
| 2 | Message queued for delivery |
| 3 | Message submitted to the SMSC |
| 4 | Message rejected by the SMSC |
| 5 | Message delivered to handset |
| 6 | Message delivery to handset failed |
Figure 3 - Stat field values
If you want to send an SMS message you need to insert the message with stat
value 1 into the ozekimessageout database table. To send a message you can use
the following SQL command:
INSERT INTO ozekimessageout (sender,receiver,msg,msgtype,stat)
VALUES ('+111111','+222222','Hello world','SMS:TEXT',1);
Figure 4 - Sample SQL command to send an SMS message
After you have inserted a message for submission, Ozeki NG SMS Gateway will
read it and will change the value of the stat field to '2'. This indicates that
the message is queued for delivery. A few moments later Ozeki NG tries to submit
this message to the SMSC. If message submission is successful the value of the
stat field is updated to '3', if message submission fails, the value is updated
to '4'. In addition to this update a new record is inserted into a different
table called msgreferences, to give you details about message submission results.
This brings us to note the second change in the database structure: There is a
new table called msgreferences. This table contains records that are
inserted by
Ozeki NG SMS Gateway after a message is successfully submitted to the
SMSC or after the message submission fails. The 'id' field in this table
contains a value matching the id field of ozekimessageout (this is a foreign key).
The second field, called reference, will contain the callback id (message
reference value) returned by
the SMSC after it accepted the message for delivery. The senttime contains
the timestamp of message submission and the error message field holds a text
explaining the error in case the message could not be submitted to the SMSC.
The reason for introducing this table was that it is faster to insert a new
record into a database table then to update an existing record with new text
content.
Step 2.) Setup Ozeki NG SMS Gateway
Download and install
Once your database is defined, you are ready to setup Ozeki NG SMS Gateway.
To start download Ozeki NG SMS Gateway from the
download page,
then run the setup wizard by clicking "Next" in each dialog box. This will give
you a default configuration.
Step 3.) Configure an SMPP mobile network connection
The first step in configuring Ozeki NG SMS Gateway, is to configure a mobile
network connection. In our example this is an SMPP connection. The
SMPP service connection
setup page gives you details instruction on how to setup SMPP. If you could
successfully configure your SMPP connection, you should test connectivity by
sending a test message from the graphical user interface to your mobile phone.
Do this by clicking on the "Compose" button.
Step 4.) Configure SQL to SMS forwarding
To setup the SQL to SMS gateway service, you need to setup an instance of the
"Database user with batch processing" module. This can be done by clicking
on "Add user" menu item the "Users and applications" menu, then clicking on
"install" in the "Database user with batch processing for Oracle" section (Figure 4).
Figure 4 - Setup Database user with batch processing
During the installation provide a unique user name. "oracle1" is a good choice (Figure 5).
Figure 5 - Configure a unique user name
On the configuration form, that comes up after this, turn off logging, by making
sure the "Log sent and received messages in human readable format" and the "Log
low level communication" checkboxes are not checked. Logging should be turned
off to get better performance (Figure 6).
Figure 6 - Turn off logging
The final step in configuring the SQL to SMS gateway system, is to specify the
database connection string. The database connection string can be specified by
clicking on the "ORACLE Settings" link in the left hand panel. The default
connection string is:
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB; User Id=myUsername;Password=myPassword;
If you use Oracle Expres edition, the following connection string might work for
you:
Provider=OraOLEDB.Oracle;dbq=localhost:1521/XE;User Id=SYSTEM;Password=qwe123;
In the connection string change settings to point to your ORACLE
instance. Please set the username and password and
the name of the database (Figure 7).
Figure 7 - Setup the connection string
Step 5.) Configure Ozeki NG SMS Gateway for high performance operation
The final step is to adjust Ozeki NG SMS Gateway to give you the throughput you
desire. In order to achieve high throughput, you need to disable some
functionality, that eats up resources. In short do the following:
Open the "Server preferences form" from the "Edit menu". In the server
preferences form open the "Advanced" tab and apply the following configure:
- Uncheck the "Match delivery reports" to original messages checkbox
- Check the "Delete messages that were forwarded to a client, or processed by
an application" checkbox
- Uncheck the "Use persistent message queues" checkbox
The appropriate configuration can be seen on Figure 8.
Figure 8 - High performance configuration.
Testing
To test the configuration you can insert several messages into the
ozekimessagout database table and check the event log of Ozeki NG SMS Gateway
to see the performance they were sent.
More information
Next page:
SQL statements
| 
