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. (Figure 1)

Figure 1 - Save all messages in a database

Saving all messages in a database (Quick Steps)

  1. Open the 'Reporting' from the Edit menu
  2. Click on 'Create new SMS Reporter'
  3. Select MS SQL type database
  4. Enter the connection details (Server, Port, Database, UserID and Password)
  5. Make sure that the database connection is established
  6. Go to the Table layout tab
  7. Copy the corresponding query to create the table for the reporting
  8. Go to the SQL tab
  9. Paste the create table query and execute it
  10. Send test message from Admin user
  11. Go to the MS SQL Reporter
  12. Query messages from database

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

open reporting menu
Figure 2 - Open Reporting menu

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

create new mssql reporter
Figure 3 - 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 4).

specify connection details
Figure 4 - Specify connection details

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

database connection established
Figure 5 - Database connection established

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

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

execute create table query
Figure 7 - 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 8).

send test message
Figure 8 - 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 9).

query messages from database
Figure 9 - 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.

FAQs

I have a database user in the system, and the logging is slow at times, the events appear in the log with some delay. Why? How can I fix this issue?

Ozeki SMS Gateway can optimize performance with proper database indexing.

Here's why indexing helps:

  • When you have a high number of messages, logging can become slow because the system needs to constantly update the database.
  • Indexing specific database fields speeds up these updates, leading to faster logging overall.

How to set up indexing:

  1. Open the "Edit" menu and select "Server preferences."
  2. Navigate to the "Databases" tab.
  3. Look for tables related to message logs, such as "Received" or "Accepted for delivery."
  4. Within each table, identify the "where" conditions used for filtering messages during searches. These conditions often involve specific message fields.
  5. Consider indexing those fields for faster database updates and improved logging performance.
Tip: Consult the Ozeki SMS Gateway documentation for detailed information on available database tables and their corresponding fields.

What is the recommended database server for SQL logging?

Here are a few ways to rephrase "We recommend Microsoft SQL Server. It has a relatively bug free native OleDb driver, and a good implementation for storing large volume of data.":

Formal:

For optimal performance and scalability, we recommend using Microsoft SQL Server. Its native OleDb driver offers exceptional reliability and stability, while the database itself excels at handling large datasets.

Benefit-focused:

Ensure seamless data management with Microsoft SQL Server. Its robust native OleDb driver minimizes the risk of errors, and the database is specifically optimized for storing and managing massive amounts of data.

More concise:

Microsoft SQL Server is our preferred choice due to its reliable OleDb driver and efficient handling of large datasets.

Any configuration lost after making any restarts? What can I do about this?

Your message recipient might be outside your service area, or the number format is incorrect.

Here are two possibilities:

  1. International Number Format: If you're trying to send messages internationally or to roaming recipients, you need to use the international number format. This typically includes a country code prefix (e.g., +44 for UK, +1 for USA). For details on international number formats, visit our FAQ on SMS number formats.
  2. SMS Service Provider Restriction: It's also possible your SMS service provider restricts sending messages to certain countries or mobile networks. To confirm, contact your provider and provide details about the specific country and mobile network where delivery is failing.
By addressing either of these potential causes, you should be able to resolve the issue and successfully deliver your messages.

My messages are not received abroad. What can cause this problem?

We're having trouble sending your message. Here are two possible reasons:

  1. International Number Format: If you're trying to reach someone internationally or a roaming recipient, you might need to use the international number format. This typically includes a plus sign (+) followed by the country code and phone number (e.g., +44 for UK, +1 for USA). For details on international number formats, visit our FAQ on SMS number formats.
  2. SMS Service Provider Restriction: It's also possible your SMS service provider restricts sending messages to certain countries or mobile networks. To confirm, contact your provider and provide details about the specific country and mobile network where delivery is failing.
By checking these two things, you should be able to resolve the issue and get your message delivered!

More information