Ozeki NG SMS Gateway logo OZEKI NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: 00 36 52 532 731
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

Keywords Contents | SearchTesting on LAN

Home > Product Manual > Installation Guide > Users and applications > SMS from/to database > MS SQL high speed

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  Prerequisites
  Download Information
  Installation Steps
  Service Provider Connections
  Users and applications
  Standard user
  SMPP user
  SMS from/to database
  MSSQL
  MySQL
  Oracle
  Sybase SQL Anywhere
  PostgreSQL
  Pervasive SQL
  Date Format Strings
  Scheduled SMS
  SQL templates
  Informix
  Access
  Video
  Connection string builder
  Keywords
  MS SQL high speed
  Testing on LAN
  Optimize NG for high performance
  Ozeki Batch Processing Tester
  Oracle High Speed
  SQL statements
  Polling technology
  Dynamic Keywords
  Dynamic SMPP parameters
  Autoreply SMS from database
  Autoreply SMS using a script
  Simple autoreply SMS
  SMS from/to file
  SMS through E-mail
  SMS via HTTP Request
  SMS via colour protocol
  Application starter SMS
  SMS Voting
  SMS from MS Outlook
  SMS from/to FTP server
  PDF Request in SMS
  SMS to WCF service
  SMS Forwarder
  RSS to SMS
  Linux
  User Guide
  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.

High performance SQL to SMS gateway configuration using MSSQL 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 the following website: http://www.ozekicluster.com

(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 MS SQL server and it requires the installation of the ozAppMSSQL.dll file.

Introduction

The configuration works the following way: Ozeki NG SMS Gateway connects to an MS SQL Server 2008 database. 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 MS SQL to SMPP configuration

How to setup

To setup this system, you need to perform the following steps:

  1. Setup your MS SQL database server
  2. Setup Ozeki NG SMS Gateway
  3. Configure an SMPP connection in Ozeki NG SMS Gateway
  4. Configure SQL to SMS forwarding
  5. Configure Ozeki NG SMS Gateway for high performance operation

Step 1.) Setup your MS SQL database server

For this solution to work you need Microsoft SQL Server 2008 (or Microsoft SQL Server 2005). In MS SQL 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 IDENTITY (1,1),
                 sender varchar(30),
                 receiver varchar(30),
                 msg varchar(160),
                 msgtype varchar(30),
                 stat int
                );
                
CREATE INDEX idindex ON ozekimessageout (id);

CREATE TABLE msgreferences (
             id int primary key,
             reference char(200),
             senttime datetime,
             errormessage varchar(2048)
            );
            
CREATE INDEX refindex ON msgreferences (id);

CREATE TABLE ozekimessagein (
             id int IDENTITY (1,1),
             sender varchar(30),
             receiver varchar(30),
             msg varchar(160),
             senttime varchar(100),
             receivedtime varchar(100),
             msgtype varchar(30)
            );
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 MS SQL. 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:

ValueMeaning
1Message waiting to be submitted
2Message queued for delivery
3Message submitted to the SMSC
4Message rejected by the SMSC
5Message delivered to handset
6Message 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 MS SQL" section (Figure 5).


Figure 5 - Setup Database user with batch processing

During the installation provide a unique user name. "mssql1" is a good choice (Figure 6).


Figure 6 - 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 7).


Figure 7 - 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 "MS SQL Settings" link in the left hand panel. The default connection string is:

Server=.\SQLEXPRESS;User ID=ozekiuser;password=ozekipass;Database=ozeki;Persist Security Info=True
In this connection string change .\SQLEXPRESS to the name of the MS SQL server instance. If you want to use the default MS SQL server instance, this can be changed to a single dot: ".". Please also set the username and password and the name of the database (Figure 8).


Figure 8 - 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:

  1. Uncheck the "Match delivery reports" to original messages checkbox
  2. Check the "Delete messages that were forwarded to a client, or processed by an application" checkbox
  3. Uncheck the "Use persistent message queues" checkbox
The appropriate configuration can be seen on Figure 9.


Figure 9 - 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.

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





Next page: Testing on LAN

Copyright © 2000 - 2017 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