High performance database messaging or pushing

UDAParts
support@udaparts.com
04/17/2010

Contents

Reasons for message pushes from backend databases
One socket connection from database to SocketPro server
Load library into database management systems
Registration of external stored functions
Testing server
Error codes from functions Connect, Notify and SendUserMessage
Absolutely safe and no security problem
Further readings

Reasons for message pushes from backend databases

    a.    Fast updating one or more middle tier caches for different client applications whenever backend database is updated.
    b.    Fast replication and synchronization of multiple copies of databases hosted within different servers.
    c.    Improve cooperation among different clients within enterprise applications (desktop, web and device applications).
    d.    Improve application user experience especially for long-lasting queries.
    e.    Reduce middle tiers or clients development complexity.

One socket connection from database to SocketPro server

To push messages from a database, SocketPro starts to build one client socket connection from database to a remote SocketPro server. Usually, we treat database as a server, but SocketPro thinks your database is one of its clients. Therefore, your database server is required to install a proper version of (32bit or 64bit) npUSocketPro.dll and make it registered because it is a COM library.

SocketPro database push library is thread-safe. You can call any exposed functions under any conditions within a database system.

Although SocketPro database push library opens one socket connection only, it is very fast for its 100% non-blocking communication. Internally, it has a hidden memory queue which has very high communication rate without wasting network bandwidth.

Load library into database management systems

Before using SocketPro database push, you need to make sure that a proper version of (32bit or 64bit) npUSocketPro.dll is registered by calling regsvr32 npUSocket.dll from DOS command as described here.

Afterwards, you need to load one of dll located within the directory of ..\UDAParts\SocketPro\DatabasePush so that your database can access it. Usually, you are required to have DBA privilege at least. Also, see the SQL script file named as registration.sql for each of database management systems.

MS SQL Server:

The following script is written for loading SocketPro database push for MS SQL server. The first step is to enable CLR support. Next, set the property trustworthy on for the sample database Northwind. Afterwards, you need to load usocket.dll which is interoperation library between SpMSSqlPush.dll and npUSocket.dll. Finally, we load the DB push library SpMSSqlPush.dll. The .NET assembly is able to support both 32- and 64-bit SQL server enviroments.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE Northwind
SET TRUSTWORTHY ON
GO

--Load usocketnet.dll into SQL server
CREATE ASSEMBLY [usocketnet]
AUTHORIZATION [dbo]
FROM 'D:\cyetest\SpPush\bin\Release\usocketnet.dll'
WITH PERMISSION_SET = UNSAFE
GO

--Load SpMSSqlPush.dll into SQL server
CREATE ASSEMBLY [SpMSSqlPush]
AUTHORIZATION [dbo]
FROM 'D:\cyetest\SpPush\bin\Release\SpMSSqlPush.dll'
WITH PERMISSION_SET = UNSAFE
GO
Oracle:

It is very simple. First, copy C:\Program Files\UDAParts\SocketPro\DatabasePush\Oracle\SpOraclePush.dll into $(oracle_home)\product\10.2.0\server\bin. Next, execute the following statement using SQL*Plus after connecting as a SYSDBA.

CREATE OR REPLACE LIBRARY SpOraclePush
As
'C:\oraexp\app\oracle\product\10.2.0\server\BIN\SpOraclePush.dll';
MySQL:

Simply copy SocketPro database push library SpMySQLPush.dll into MySQL library plugin directory (c:\Program Files\MySQL\My SQL Server 5.1\lib\plugin.
DB2:

Similar with MySQL, simply copy SocketPro database push library SpDB2Push.dll into DB2 server function directory.

Registration of external stored functions

Once we have loaded our SocketPro database push library into a database server, we need to register the five functions IsConnected, Connect, Disconnect, Notify, and SendUserMessage. We take MS Sql server as an example as shown below. All of other databases are similar. Please see the attached file registration.sql for each of database management systems.

CREATE FUNCTION Connect(@ConnectionString nvarchar(2048))
returns int
As
EXTERNAL NAME SpMSSqlPush.[SpMSSqlPush.Messenger].Connect
GO

Create FUNCTION Disconnect()
Returns int
As
EXTERNAL NAME SpMSSqlPush.[SpMSSqlPush.Messenger].Disconnect
GO

CREATE FUNCTION Notify(
@Message nvarchar(2048),
@Groups nvarchar(4000))
returns int
As
EXTERNAL NAME SpMSSqlPush.[SpMSSqlPush.Messenger].Notify
GO

CREATE FUNCTION SendUserMessage(
@Message nvarchar(2048),
@UserId nvarchar(256))
returns int
As
EXTERNAL NAME SpMSSqlPush.[SpMSSqlPush.Messenger].SendUserMessage
GO

Create FUNCTION IsConnected()
returns bit
As
EXTERNAL NAME SpMSSqlPush.[SpMSSqlPush.Messenger].IsConnected
GO

Once the above functions are registered within your database, you can access them at your will. Again, please see the attached file registration.sql for each of database management systems. Connecting string to a remote server could be:

Note that the settings verify and strict are ignored if the encryption is 0 (no encryption, default). By default, zip is zero; encryption zero; verify string empty; and strict zero. The setting verify is used for verifying certificate subject, chains and validity. If the setting strict is not set to zero and encryption is either 3 (SSL3) or 5 (TLSv1), server authentication is fully tested through certificate before sending user id and password to a remote server.

Testing server

To test SocketPro database push library, you need to start SocketPro HTTP push server as described here and here. The sample server is written for supporting all types (middle tier, http server, silverlight, desktop, device and database) of push demonstrations. Please compile the sample project inside the directory C:\Program Files\UDAParts\SocketPro\tutorial\CSharp (VBNet)\Chat. After copying all files in the directory C:\Program Files\UDAParts\SocketPro\tutorial\HTTPChatSampleFiles into the directory C:\Program Files\UDAParts\SocketPro\tutorial\CSharp (VBNet)\Chat\Server\bin\Release, you can see messages on your web browsers from your databases.

If you do everything correctly, you are expected to get the following two pictures:

Error codes from functions Connect, Notify and SendUserMessage

The above three functions may return one of the below error codes:

0 -- No error and successful
1 -- No socket connection available. You can use the function IsConnected to check its availability.
2 -- Failed with the system even there is socket connection available. This case should never happen.
3 -- Invalid user id for the function SendUserMessage or invalid chat group ids for the function Notify.
4 -- Internal system error. This case should never happen.
5 -- No COM usocket object available. Check if there is proper version (32bit or 64bit) npUSocket.dll registered at your server.
6 -- No UI worker thread available. This case should never happen.
7 -- Encryption value is not one of 0 (no encryption), 3 (MS SSL) and 5 (MS TLSv1).
8 -- Certificate subject verification failed when verify is set with a subject string and strict is set to zero.
9 -- Certificate chain verification failed when connection string strict is set to none zero.

Absolutely safe and no security problem

SocketPro database push library doesn't change any database objects or properties. It sends data from database onto a remote SocketPro server only after starting a client socket connection. Actually, there is absolutely no possibility to change any database objects or properties. Therefore, it is absolutely safe from security view.

Further readings