2

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in copying the SPs to the Client DB.

Problem: All the Business Logic gets copied at the Client Side and results in proprietary issues.

Solutions Tried Earlier:

  1. CREATE PROCEDURE Proc_Name WITH ENCRYPTION

    This method results in encrypted and non-maintainable SP code. We cannot judge which version of code is running at the client side, hence cannot debug. Version control cannot be applied. Moreover, client cannot perform DB replication since encrypted SPs do not get replicated.

  2. CREATE SYNONYM SchemaName.Proc_Name FOR LinkedServerDB.SchemaName.Proc_Name

    This allows for creation of references (synonyms) at Client_DB which access the actual SPs residing on a Remote_Linked_Server_DB. In each SP call, entire data is accessed from Client_DB and transmitted to Remote_Linked_Server_DB where the calculations are done and the result is sent back. This results in acute performance issues. Also requires 24x7 internet connectivity to the remote linked server.

Requirement (Edited):

  1. We are looking for a solution whereby the stored procedure code could be compiled (secured) and separated from the Client Database.
  2. The compiled stored procedure code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the stored procedures as in the case of using SYNONYMS.
  3. I have a slight hint of using CLR-SQL in Visual Studio Database Projects, but I guess that would require re-writing all the stored procedures (which is nearly impossible since we have more than 100 SPs).
0

If you can upgrade your system to SQL 2014 then have a look here to read about natively compiled stored procedures. This will protect your code and possibly improve performance.

It's probably a bit late but business logic should be done at the application layer when possible. This keeps all the logic in one place and reduces the need for CPU resources at the database level which can be very expensive when using Microsoft SQL Server.

  • We can upgrade to SQL 2014, no issues there. But is SQL 2014 stable enough? Surely, you are right in saying that business logic should not reside in SP. We cannot achieve that here, too late – Ish Goel Aug 1 '14 at 7:51
  • Well I wouldn't advise rushing 2014 into production and you will need to upgrade your dev environment first so that you can make use of these natively compiled SPs. You could wait for 2014 SP1 but Microsoft are taking there time with these now days and focusing on cumulative updates instead. Check here for the latest updates sqlserverbuilds.blogspot.co.uk – James Anderson Aug 1 '14 at 8:07
  • 3
    Natively compiled stored procedures work for in-memory tables only. Won't work for normal tables in the database – Ish Goel Aug 1 '14 at 9:56
  • 3
    Just as an FYI, SO is running on SQL Server 2014 in production and has been since CTP2: nickcraver.com/blog/tag/sql-server-testing – Shawn Melton Aug 1 '14 at 13:00
0

Whenever you have to pass the databse to the customer, first restore a recent copy as a staging DB. Run this against that staging DB:

select 'drop procedure ' + name + ';' from sys.objects where type = 'P';

You may have to involve sys.schemas, too, if the SPs are spread across multiple schemas. Take the output of the above and run it against the staging DB. You can do something similar for functions, triggers, other executables and any type of object. Once done, backup the staging DB and send that to the customer. The whole thing can be automated.

  • How will this help? If we drop all procedures from staging database then where will the logic go? How will the client use the application without the stored procedures in the database? – Ish Goel Aug 1 '14 at 17:18
  • I misunderstood your problem. I thought you were trying to stop your IP from leaking out of your control. Sorry. – Michael Green Aug 2 '14 at 0:34
2

I do not think that your requirements are complete as there are reasons that your prior attempts did not work that are not reflected in the stated Requirements. You mentioned in attempted solution #1 that you need to be able to debug the code / know what version it is, and the client needs to be able to replicate the code. Are these two issues current requirements?

If the need for replication is still a requirement, then that conflicts with the requirement to be "compiled (secured)".

I will assume that the client's server is something they have full sa access to such that DENYing the VIEW ANY DEFINITION permission is not a viable approach.

One thing to try is taking the T-SQL of your Stored Procedures and encapsulating each one, unchanged, into CLR Stored Procedures. You would place one or more related Stored Procedures into an Assembly. Here are some thoughts about this:

  • Assemblies conveniently have a version number that can be seen in the sys.assemblies catalog view, SSMS (right-click on the Assembly to view the Properties), and the ASSEMBLYPROPERTY function.
  • You probably don't want to put all of the Stored Procedures into a single Assembly, but you also don't want to have one Assembly per Stored Procedure. You can have one class file per Stored Procedure and several of those included in a single Assembly.
  • All of the Assemblies should use WITH PERMISSION_SET = SAFE
  • The only code in each CLR Stored Procedure would be:
    • Create a SqlCommand with a CommandType of CommandType.Text and CommandText set to the current T-SQL Stored Procedure content
    • No SqlConnection should be needed (if it is, then it is just "context connection=true")
    • Map the SqlParameterCollection of the SqlCommand to the input parameters of the CLR Stored Procedure
    • Call SqlContext.Pipe.ExecuteAndSend(_YourSqlCommand);
  • The Assembly and related Stored Procedures would be loaded into each Client DB
  • CLR Stored Procedures are not able to be replicated, but that could probably be worked around
  • Is it possible to take the T-SQL of stored procedures, unchanged, into CLR stored procedures? I thought we have to re-write the entire code. Can you give an example? – Ish Goel Aug 2 '14 at 5:22
  • Or is it that your description should be treated as an example? We use temporary/variable tables, cursors, UDF inside the stored procedure code. Can that all be migrated to CLR without any change? – Ish Goel Aug 2 '14 at 5:29
  • @IshGoel: yes, I am saying that under most circumstances, you should be able to keep the current T-SQL as it is, without modification. I just tested using a table variable, a cursor, and some other stuff. The only issue might be the need for direct DML permissions to the tables as the code will now be considered Dynamic SQL and that breaks the ownership-chain. And if that is the case and if that is a problem then there is something that probably could be done about it. – Solomon Rutzky Aug 2 '14 at 7:35
  • @IshGoel: have you had a chance to test this? Just curious. – Solomon Rutzky Aug 8 '14 at 19:38
  • I am still working on it. I don't think we can keep the T-SQL as-it-is when converting to CLR-SQL. – Ish Goel Aug 9 '14 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.