WCF net.tcp service and stored assembly in SQL Server

Question

I have a simple client of WCF service which works with net.tcp protocol. This client should be added to database as stored assembly.

From service config:

<service name="ServiceDocument.DocService" behaviorConfiguration="TCPServiceBehavior">
        <endpoint name="DocServiceTCP" address="net.tcp://localhost:5430/DocService" binding="netTcpBinding" bindingConfiguration="netTcpConfig" contract="ServiceDoc.IDocService"/>
        <endpoint name="DocServiceMex" address="net.tcp://localhost:5431/mexDocService" binding="mexTcpBinding" contract="IMetadataExchange"/>
</service>

I added to the client service reference used address net.tcp://localhost:5431/mexDocService and then client is working fine.

Service reference generated code like:

namespace DocContent.DocServiceTCP {
    using System.Runtime.Serialization;
    using System;

    [System.Diagnostics.DebuggerStepThroughAttribute()]  
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "4.0.0.0")]
    [System.Runtime.Serialization.DataContractAttribute(Name="Document", Namespace="http://schemas.datacontract.org/2004/07/ServiceDoc")]
    [System.SerializableAttribute()]
    public partial class Document {
    ....

But when I try to add this client to the database as stored assembly(using SMSS) then I'm getting an error like:

assembly system.runtime.serialization was not found in the sql catalog

Before add assembly I run:

ALTER DATABASE DBNAME SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON DATABASE::DBNAME TO SYSTEM_USER

As described in MSDN system.runtime.serialization is not supported in SQL Server.

How can I add the assembly to the database in this case?

Answer 1

the SQLCLR was designed as an alternative to implmenting complex stored procedures/functions/triggers etc in raw SQL.

It was not designed to host a full-featured .net runtime.

It was certainly not designed to execute code which opens up local tcp sockets and tries to transmit data across to somewhere.

Even if you could do this, in my opinion you should not. Some concerns should be kept separate from each other and this is one example. Apologies if this sounds prescriptive but I have experience of using both SQLCLR and WCF and I struggle to think of any upside to combining them into the same layer in the stack.

If you need to integrate to .net code from your SQL database, there are other options, such as SqlDependency. This will allow you to trigger a service call from .net code by listening for database changes.

If you want to send data to other SQL server instances then there is Service Broker.