2010
09.06

From MS SQL to MySQL, realtime row copy

3 people like this post.
Share

Or: “How to call a web server from a Microsoft SQL Server Stored Procedure”.
Customer has got a VoIP software PBX (Swyx). It logs incoming calls (the CDR) in a MS SQL Server database. The CDR structure is straightforward: a single table where each row is a call, indexed by CallId (transferred calls get, eventually, a new row and a “child CallId”).
I needed to process the CDR within these specs/restrictions:

  • Each row has to be processed as soon as it is INSERTed
  • Rows must be filtered (depending on the called number)
  • Filtered rows must be “mirrored” to a MySQL DB
  • MS SQL machine is heavily loaded and mission critical; the row-copy mechanism must be light and fast

The first and second specs imply the use of triggers/stored procedures.

I originally thought that the “DB link”-kind of functionality could be achieved natively on MS SQL. In theory it can, via Linked Servers (bound to ODBC Data Sources). There’s a catch though: you can SELECT stuff on linked servers at will, but as soon as you try to INSERT, you’ll hit error 73911. MS SQL, can’t really blame it, would like to be able to rollback any change made, even on the linked MySQL. It needs to start a (implicit, distributed) transaction on MySQL, but that’s not supported and the write fails. This workaround (forcibly switch off implicit transactions) didn’t work for me. Apparently, the Oracle OLEDB Provider is able to ignore/disable distributing transactions when the parameter DistribTX=0 is in the provider string. MySQL’s ODBC driver doesn’t provide a similar toggle.

The easiest way to push data “out” of MS SQL is (arguably) through HTTP. The DB GETs a full URL, passing key/value parameters to a Web Service that outputs to MySQL.

On with the code, starting with the “Web Service”. What follow is a mere Perl script, useful for testing. Depending on the expected load, you may want to use a proper application server, providing MySQL DB connection pooling. What you should really do, is serve the script through HTTPS and password protect it. Without SSL, a malicious user could sniff the cleartext requests sent by the source DB, forge similar ones and litter/DOS the MySQL instance. Of course, the Web Service could output to just any supported DB, not only to MySQL.

#!/usr/bin/perl

use DBI;
use CGI;
use strict;

my $DEBUG = 0;
my @FIELDS = qw(
CallId
OriginationNumber
CalledNumber
DestinationNumber
StartTime
ScriptConnectTime
DeliveredTime
ConnectTime
TransferTime
EndTime
DisconnectReason
TransferredToCallId
);

my $q = new CGI;
print $q->header(-type => 'text/plain', -charset => 'ISO-8859-1', -expires => '-1d');

# checks
my $checkresult = 1;
my $checkmessage = '';
sub setcheck ($$$$) {
    my ($rrc, $rc, $rrs, $rs) = @_;
    $$rrc = $rc;
    $$rrs = $rs;   
}
sub isnumber { return 1 if $_[0] =~ /^[0-9]*$/i; return 0; }
sub issane { return 1 if $_[0] =~ /^[a-z0-9%:\- ]*$/i; return 0; }
setcheck(\$checkresult,0,\$checkmessage,'NULL CallId') if $checkresult and not $q->param('CallId');
setcheck(\$checkresult,0,\$checkmessage,'CallId must be a number') if $checkresult and not isnumber($q->param('CallId'));
foreach (@FIELDS) {
    setcheck(\$checkresult,0,\$checkmessage,"$_ value contains invalid characters")
        if $checkresult and not issane($q->param($_));
}

if ($checkresult) {
    my $dbh = DBI->connect('DBI:mysql:database=dbname','dbuser','password') or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
    my $values = join ',', ( map { $dbh->quote( $q->param($_) ? $q->param($_) : '') } @FIELDS );
    my $sth = $dbh->prepare("INSERT INTO callslog VALUES ($values)") or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
    $sth->execute or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
    if ($DEBUG) {
        print $_.': '.$q->param($_)."\n" for @FIELDS;
    }
    print "OK\n";
} else {
    print "KO: $checkmessage\n";
}

exit;

Next, the trigger code. It acts after each INSERT on the IpPbxCDR table. If a called number ends with the given digits, calls the Stored Procedure spLogCall, passing it the fields we’re interested in. I use the (commented) raiserror call for debugging purposes.

USE [ippbxlog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_ProcessCall]
ON [dbo].[IpPbxCDR]
AFTER INSERT
AS
BEGIN
    DECLARE
        @RightMatch nvarchar (10),
        @CallId INT,
        @OriginationNumber nvarchar(50),
        @CalledNumber nvarchar(50),
        @DestinationNumber nvarchar(50),
        @StartTime datetime,
        @ScriptConnectTime datetime,
        @DeliveredTime datetime,
        @ConnectTime datetime,
        @TransferTime datetime,
        @EndTime datetime,
        @DisconnectReason nvarchar(50),
        @TransferredToCallId INT
    SET @RightMatch = '12345678'
    SELECT
        @CallId = CallId,
        @OriginationNumber = OriginationNumber,
        @CalledNumber = CalledNumber,
        @DestinationNumber = DestinationNumber,
        @StartTime = StartTime,
        @ScriptConnectTime = ScriptConnectTime,
        @DeliveredTime = DeliveredTime,
        @ConnectTime = ConnectTime,
        @TransferTime = TransferTime,
        @EndTime = EndTime,
        @DisconnectReason = DisconnectReason,
        @TransferredToCallId = TransferredToCallId
    FROM INSERTED
    IF (RIGHT(@DestinationNumber,LEN(@RightMatch)) = @RightMatch) OR (RIGHT(@CalledNumber,LEN(@RightMatch)) = @RightMatch)
    BEGIN
--raiserror('%s',16,1, @DestinationNumber)
        EXEC spLogCall
            @CallId,
            @OriginationNumber,
            @CalledNumber,
            @DestinationNumber,
            @StartTime,
            @ScriptConnectTime,
            @DeliveredTime,
            @ConnectTime,
            @TransferTime,
            @EndTime,
            @DisconnectReason,
            @TransferredToCallId
    END
END

Lastly, the Web Service contacting Stored Procedure. I use sp_OACreate to create an OLE object of class MSXML2.ServerXMLHTTP passing it the contructed GET URL (address + parameters). Depending on MS SQL’s version, you may have to explicitly enable in-database OLE automation, this way:

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

Timeouts for various operations are set to reasonably low values, we don’t want the DB to “block” for too long. And again: use HTTPS. Get your certificates right (on MS SQL’s server, install the root certificate for the CA who issued the cert you’re using on the web/application server) and use HTTPS.

USE [ippbxlog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spLogCall]
    @CallId INT,
    @OriginationNumber nvarchar(50),
    @CalledNumber nvarchar(50),
    @DestinationNumber nvarchar(50),
    @StartTime datetime,
    @ScriptConnectTime datetime,
    @DeliveredTime datetime,
    @ConnectTime datetime,
    @TransferTime datetime,
    @EndTime datetime,
    @DisconnectReason nvarchar(50),
    @TransferredToCallId INT
AS

BEGIN
DECLARE
    @Object INT,
    @hr INT,
    @openparams nvarchar(2048),
    @responsetext VARCHAR(8000);

EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT
IF @hr = 0
BEGIN
    SET CONCAT_NULL_YIELDS_NULL OFF
    SET @openparams = 'open("GET", "http://10.1.1.123/ws/CDR.pl?' +
        'CallId=' +               CAST(@CallId AS VARCHAR) + '&' +
        'OriginationNumber=' +    CAST(@OriginationNumber AS VARCHAR) + '&' +
        'CalledNumber=' +         CAST(@CalledNumber AS VARCHAR) + '&' +
        'DestinationNumber=' +    CAST(@DestinationNumber AS VARCHAR) + '&' +
        'StartTime=' +            CONVERT(VARCHAR, @StartTime, 120) + '&' +
        'ScriptConnectTime=' +    CONVERT(VARCHAR, @ScriptConnectTime, 120) + '&' +
        'DeliveredTime=' +        CONVERT(VARCHAR, @DeliveredTime, 120) + '&' +
        'ConnectTime=' +          CONVERT(VARCHAR, @ConnectTime, 120) + '&' +
        'TransferTime=' +         CONVERT(VARCHAR, @TransferTime, 120) + '&' +
        'EndTime=' +              CONVERT(VARCHAR, @EndTime, 120) + '&' +
        'DisconnectReason=' +     CAST(@DisconnectReason AS VARCHAR) + '&' +
        'TransferredToCallId=' +  CAST(@TransferredToCallId AS VARCHAR) +
        '", False)'
    EXEC @hr = sp_OAMethod @Object, 'setTimeouts(3000,3000,3000,3000)'
    EXEC @hr = sp_OAMethod @Object, @openparams
    EXEC @hr = sp_OAMethod @Object, 'Send'
    EXEC @hr = sp_OAGetProperty @Object, 'responseText', @responseText OUT
END
END

That’s it, the method performs and scales quite well. I think I’ll find other uses for it soon…

  1. The operation could not be performed because OLE DB provider “%ls” for linked server “%ls” was unable to begin a distributed transaction.
Share

4 comments so far

Add Your Comment
  1. I was seriously considering to do it your way, after at first I failed to get it working like its described here http://www.infi.nl/blog/view/id/4/How_To_MySQL_as_a_linked_server_in_MS_SQL_Server

    BUT I found out how to do it.
    I commented it on that blog site and I think its the better approach (its quirky anyway).

  2. In my case, I wasn’t able to convince MSSQL and/or the ODBC driver to give up on distributed transactions… And the “SET IMPLICIT_TRANSACTIONS OFF” didn’t work, maybe because I was using it wrong…
    Anyway: thanks for the heads up, I’ll try your method, it’s indeed the “right way to do it”.

    ciao,

    Giuliano

  3. Yeah the ODBC driver is a real blocker in that case.

    I cannot stress enough that you need an older one, use 3.51.22 – its a bit difficult to find it,
    but you can get it here http://mysql.biz.net.id/Downloads/Connector-ODBC/3.51/

    Also the first thing you should do in the Trigger is to do a COMMIT, right after the AS is the best place, only after that “SET IMPLICIT_TRANSACTIONS OFF” will have any effect.

  4. Got it, thanks again for the precious info!