Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sending data from MS SQL Server do PostgreSQL server using CLR triggers Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 5:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
I need to send data from a MS SQL Server table to a PostgreSQL table on INSERT/UPDATE. I was thinking about developing a CLR trigger on the source SQL Server table: the assembly must then reference the npgsql.dll and the mono.security.dll (http://npgsql.projects.pgfoundry.org/), establish a connection to the PostgreSQL server, send the data, close the connection.
If I test the above mentioned dll in a desktop application, no problem.
After developing the CLR trigger, when I try to deploy the trigger to SQL Server and I try to create the assembly first using the next T-SQL code:

create assembly SimpleTrigger
authorization dbo
from 'd:\TTC\ArchivioCircolare\Lavoro\Tests\SimpleTrigger\SimpleTrigger\bin\Debug\SimpleTrigger.dll'
WITH PERMISSION_SET = unsafe
go


then I got the following exception:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'SimpleTrigger' failed because assembly 'Mono.Security' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::SquarePositive][mdToken=0x600005f][offset 0x0000003C][trovato address of Int32][previsto unmanaged pointer] Tipo ...


Any suggestion? Any other way to send data from MS SQL Server to PostgreSQL Server?

Thank you in advance, regards
Pier
Post #1560824
Posted Friday, April 11, 2014 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,394, Visits: 6,636
First thought would be the PERMISSION_SET, check this out on the subject: http://technet.microsoft.com/en-us/library/ms345101.aspx
Ciao
Post #1560957
Posted Friday, April 11, 2014 9:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
Hello Mr Eiriksson,
I plaied around a little with the PERMISSION_SET and the UNSAFE setting gets the best result, i.e. what you can read in my previous post (the other PERMISSION_SETs just required the UNSAFE PERMISSION_SET, even if I read it is a little dangerous).

Do you know of any other way to send data from SQL Server to another type of database. I am also considering Service Broker and an external .NET application that read messages and write autonomously into the other server.

Regards
Post #1561017
Posted Friday, April 11, 2014 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,394, Visits: 6,636
It all depends on how quickly you have to get the data from A to B, first option would be a linked server (via ODBC), have you tried this?

Post #1561022
Posted Friday, April 11, 2014 12:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
Gosh... don't do this with a trigger. If the destination can't be reached for some reason, your source system is dead. Also, the scope of the INSERTED and DELETED tables in the trigger is that they won't be available directly and you'll need to make copies of them in some sort of an array. You're begging for this process to be slow and VERY memory intensive.

My recommendation would be to have the trigger (and NOT a (bleaaahhh!!!!) generic trigger, for the same reasons) write the data to a permanent staging table and have a job do the transfer once a minute.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561106
Posted Friday, April 11, 2014 1:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,394, Visits: 6,636
I agree with Jeff here, a trigger is not a good idea. Could you shed a little more light on the setup?

Eirikur
Post #1561121
Posted Sunday, April 13, 2014 12:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
Thank you all for your suggestions. Short description of the system:
- the aim is production tracking
- there are 2 PCs each with its own MS SQL Server and a software that collects production data and feeds its database. Only one pc is active at a time: the other is a hot backup, i.e. powered on, but doing nothing. The database in the hot backup is synchronized by the primary pc using MS Sync Framework
- last but not least the factory has another system running PostgreSQL which must be updated continuously with production data as well.
That's why I was thinking about triggers firing on the primary pc to update PostgreSQL.
What about Service Broker? I could send messages in those triggers and then let a .NET application collect them and feed the Postgre database. Anyone who has esperience of Service Broker?
Post #1561265
Posted Sunday, April 13, 2014 8:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
I don't know if it's an option or not but this reminds me of a very early computer game that I played in the evenings after having a nice Brontosaurus steak for dinner. I got "stuck" in the game and it wouldn't let me move up, down, left, right, forward, or back but kept asking me what I wanted to do. I was ticked off because it seemed like it was an easy black'or'white question. That's when it struck me and I typed "change color" and it let me proceed.

The problem is similar here. You have SQL Server on two boxes on one side and they have absolutely no problem with data transfers from one to the other. You want to get the data to another box with a different SQL engine and are having great difficulty in doing so. You'll continue to have this problem for future "adventures", as well.

Since SQL Server has some nifty and high performance built-in tools to transfer the data, wouldn't it be easier, at this point, to "change color" and redact the Postgre SQL Server to be an MS SQL Server (SQL Express might even be enough and it's FREE) to solve not only the current problem but all future problems of the same and different nature?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561277
Posted Sunday, April 13, 2014 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
Thank you for your answer Jeff. This was also one of my thought but there are 2 cons:
- 10gb limits as of version 2012 express and the database should store at least 10 years production
And
- the other box runs Linux and they are in love with Postgre

Do you think that Service Broker is not a good solution? Why? Performance, memory, possible memory leaks?

Thanks
Post #1561293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse