High performance database messaging or pushing

        UDAParts
        support@udaparts.com
        Updated on 02/26/2012

Contents

1. Reasons for message pushes from backend databases
2. One socket connection from database to SocketPro server
3. Load library into database management systems
4. Registration of external stored functions
5. Testing server
6. Error codes from functions Connect, Notify and SendUserMessage
7. Common errors and their fixes
8. Absolutely safe and no security problem
9. 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.

    When making the call Connect successfully, SocketPro DB push will automatically switch to chat service with given user id and password right after the client socket connection is established. Therefore, your remote SocketPro server must be enabled with built-in service chat. If this built-in chat service is not enabled, DB push will not work as expected.

    You can call all of DB push methods anytime from anywhere within a database server. Internally, it just uses one instance of client socket only.

    SocketPro DB push will periodically send base request DoEcho with receiving time out equal to 1 second. DB push uses this way to fast detect server abnormal shutdown.

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:

    "host|server=address;uid|userid=YourUserID;pwd|password=YourPassword;port=SomeNumber;zip=0|1;encryption=0|5|3;verify=paypal.com;strict=0|1"

    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.

Common errors and their fixes

    At start, you may meet a number of problems to bother you. Here is a list of common errors.

• Error code 1 -- No socket connection available

            REASONS:
                a. There is no SocketPro server available at all.
                b. Remote SocketPro server not reachable because remote server firewall may block the listening port.
                c. Remote SocketPro server does not enable built-in chat service.
                d. Connection string is wrong with ip address, port, user id or password.
                e. Authentication is failed at server side because of wrong user id and password.
            ANSWER:
                Check and fix these potential problems one by one.

• Error code 5 -- No COM usocket object available:

            REASONS:
                a. A proper version (x86 or x64) of client core COM library (npUSocket.dll) is not registered properly.
                b. Too lower privileges for calling DB push methods.
            ANSWER:
                regsvr32 npUSocket.dll for either x86 or x64, depending on MS SQL SERVER x86 or x64.
                Increase caller's DB privileges.

• MS SQL Server Specific Error 1

            When you enable CLR support for managed code, MS SQL server may emit the error, Msg 5847, Level 16, State 88, Line 2 Execution of .NET Framework code is disabled. Set "clr enabled" configuration option and restart the server.

            REASON:
                MS SQL Server may require you to restart it after enabling CLR for managed code.
            ANSWER:
                Simply stop MSQL server service and restart it.

• MS SQL Server Specific Error 2

            When you send SQL command and ask for installing usocketnet.dll or SpMSSQLPush.dll, MS SQL server may response the error message, Msg 33009, Level 16, State 2, Line 1. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Northwind'. You should correct this situation by resetting the owner of database 'Northwind' using the ALTER AUTHORIZATION statement.

            REASON:
                Database owner id is not correct after a new database is attached to MS SQL server.
            ANSWER:
                ALTER AUTHORIZATION ON DATABASE::NORTHWIND TO sa;

• MS SQL Server Specific Error 3

            When you register one of SocketPro DB push methods, MS SQL server may answer with the error message like Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

            REASON:
                You attached a database with one of old versions of MS SQL server data format, which does not support CLR external call with managed code
            ANSWER:
                EXEC sp_dbcmptlevel 'Northwind', 90 -- or 100 for MS SQL Server 2008

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

• SocketPro notification system
• Push message from HTTP server through SocketPro
• Push message onto silverlight clients
• Generic database access through MS OLEDB technology
• Fast remoting key ADO.NET objects, datareader, dataset and datatable