2 improved formatting
source | link

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): We are looking for a solution whereby the SP code could be compiled (secured) and separated from the Client Database. Also, the compiled SP code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the SPs. Maybe Visual Studio Database Projects could be used to compile SP code or something else.

  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).

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: We are looking for a solution whereby the SP code could be compiled (secured) and separated from the Client Database. Also, the compiled SP code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the SPs. Maybe Visual Studio Database Projects could be used to compile SP code or something else.

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).
1
source | link

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

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: We are looking for a solution whereby the SP code could be compiled (secured) and separated from the Client Database. Also, the compiled SP code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the SPs. Maybe Visual Studio Database Projects could be used to compile SP code or something else.