How to use a database to save all SMS messages

This guide explains how to setup a Microsoft SQL Database server to save all SMS messages going through the system. This setup makes it possible for you to create reports, write bills or to calculate the cost of operating your SMS gateway system.

reporting-databse.psd

Firstly open the Reporting from the Edit menu in the navigation bar (Figure 1).

open reporting menu
Figure 1 - Open Reporting menu

In the Reporting menu click on the Create new SMS Reporter button and than select MS SQL type database (Figure 2).

create new mssql reporter
Figure 2 - Create new MSSQL reporter

Now enter the connection details like Server, Port, Database, UserID and Password. And enter the Connection name. Finally click on the OK button (Figure 3).

specify connection details
Figure 3 - Specify connection details

In the Events tab you can see the logging of the latest server events. As you can see on the Figure 4 the database connection is established.

database connection established
Figure 4 - Database connection established

On the Table layout tab you can find the corresponding query to ctreate the table for the reporting. Copy it from here (Figure 5).

copy table layout
Figure 5 - Copy table layout

On the SQL tab you can execute SQL queries in the database connection. Paste here the create table query and execute it (Figure 6).

execute create table query
Figure 6 - Execute create table query

From the Admin user you can send test SMS message. Provide the recipient address, the message and click on the OK button (Figure 7).

send test message
Figure 7 - Send test message

Finally in the MS SQL Reporter you can query the messages from the database and you will see there is a new message in the table (Figure 8).

query messages from database
Figure 8 - Query messages from database

How to test sms reporting

To test sms reporting, it is a good idea to setup an SMPP simulator system, and to send some test SMS messages to this system. The SMPP simulator is a simple configuration, that accepts sms messages using the built in SMPP server of the SMS gateway, and forwards these SMS messages to the sms tester connection. This simulator allows you to test sql reporting free of charge. We have used a similar setup when we have create a performance test of 1 000 000 sms messages to validate that Ozeki SMS Gateway can deliver up to 1000 sms / second.