SQL Server WCF Web Service

The estimated reading time for this post is 6 minutes

Since Microsoft SQL Server 2012, SOAP endpoints are deprecated features and developers should not use this feature due to separation of concerns and also security purposes. I believe that feature was reducing a lot of development effort. In this blog post I would like to introduce SQL Server Web Service which is developed to generate and provide Windows Communication Foundation Service based on the given database name.

SQL Server Web Service is external software that enhances SQL Server abilities in order to provide proper web service with minimal development and administration effort. This software generates the web service codes dynamically and creates the assembly on memory for flexibility purposes. We are specialized in SQL Server database development and administration, and we are able to develop software to extend the SQL Server ability. By this software every SQL Server databases can expose their stored procedures as web method and let third party software consume the data. Be aware that web service security is managed by SQL Server itself.

Installation

Trial version can hosts up to two (2) web services at same time. 

You may download the trial version installation files, unzip the file once you download it. It contains few DLL files and one EXE file as the following:-

  • ServiceProcess.exe: This is the WCF service launcher.
  • SQLServerWebService.dll: This is the DLL file to generate WCF service classes and methods dynamically on the memory.

Copy the files in a folder path that is secure and SQL Server service account is able to access.

Open SSMS, and then execute the following TSQL statements to create the SQL Server Assembly. Therefore you can access to the SQLServerWebService methods through TSQL.

USE master;
go
EXEC SP_CONFIGURE 'show advanced options','1';reconfigure WITH override;
EXEC SP_CONFIGURE 'clr enabled','1';reconfigure WITH override;
go
ALTER DATABASE Master SET trustworthy  ON;
go
CREATE Assembly WebService FROM '<SQLServerWebService.dll Filename>'
WITH permission_set = unsafe;
go
CREATE PROC PREPARE
AS EXTERNAL name [WebService].[ServiceEnvironment].[PREPARE];
go
CREATE PROC SetupWebService(@server NVARCHAR(128), 
				@DATABASE NVARCHAR(128), 
				@servicename NVARCHAR(128),
				@serviceUrl NVARCHAR(255),
				@USER NVARCHAR(128) = null) 
AS EXTERNAL name [WebService].[ServiceEnvironment].[SetupWebService];
go
CREATE PROC RunService(@servicename NVARCHAR(255)) 
AS EXTERNAL name [WebService].[ServiceEnvironment].[RunService];
go
CREATE PROC StopService (@servicename NVARCHAR(255),@force INT = 0)
AS EXTERNAL name [WebService].[ServiceEnvironment].[StopService];
go
CREATE PROC RemoveService (@servicename NVARCHAR(255))
AS EXTERNAL name [WebService].[ServiceEnvironment].[RemoveService];
go
CREATE PROC GetServices
AS EXTERNAL Name [WebService].[ServiceEnvironment].[GetServices];
go
CREATE PROC About
AS EXTERNAL Name [WebService].[ServiceEnvironment].[About];
go
CREATE PROC UpdateAuthentication(@username NVARCHAR(200),@password NVARCHAR(200))
AS EXTERNAL Name [WebService].[ServiceEnvironment].[UpdateAuthentication];
go
CREATE PROC ExportService (@servicename NVARCHAR(200),@filename NVARCHAR(255),@overwrite BIT = 0 )
AS EXTERNAL Name [WebService].[ServiceEnvironment].[ExportService];
go
CREATE PROC AddFilteredSP (@spname NVARCHAR(128),@serviceId BIGINT)
AS EXTERNAL Name [WebService].[ServiceEnvironment].[AddFilteredStoredProc];
go
CREATE PROC RemoveFilteredSP (@spname NVARCHAR(128),@serviceId BIGINT)
AS EXTERNAL Name [WebService].[ServiceEnvironment].[RemoveFilteredStoredProc];
go
CREATE PROC RebuildService (@servicename NVARCHAR(200),@USER NVARCHAR(128) = null)
AS EXTERNAL Name [WebService].[ServiceEnvironment].[RebuildService];
go
CREATE PROC RestartService (@servicename NVARCHAR(200))
AS EXTERNAL Name [WebService].[ServiceEnvironment].[RestartService];
go
EXEC dbo.PREPARE;
go

Once above TSQL statements are ran, the assembly creates four tables on the database as the following:-

  • ServiceAssemblies: Stores the WCF service metadata and binary content.
  • ServiceConfiguration: Stores the WCF service launcher configuration such as Authentication.
  • ServiceFilteredMethods; Stores the WCF service filtered web methods.
  • ServiceLog: Keeps the WCF service error logs.

The following stored procedures are created once the above TSQ statements are ran:-

  • Prepare: It prepares the environment to host the web services, this stored procedure is called automatically during installation process.
  • SetupWebService: It generates the web service. It accepts few parameters such as @Server (the target database server name), @Database (the target database name), @ServiceName (the web service name, it is unique) and @ServiceURL (the web service URL and it is unique as well).
  • RunService: It loads and runs the web service. It accepts a parameter as @ServiceName.
  • StopService: It stops and un-load the web service. It provides two parameters as @ServiceName and @Force. In some cases the web service is not running but it is hosted by SQL Server Web Service engine, then administrators can use @Force=1 parameter to un-host the web service.
  • RemoveService: It removes the specific web service meta data from the environment. It provides @ServiceName parameter.
  • UpdateAuthentication: It updates the web service host authentication.
  • GetServices: It returns all running web service name and process id.
  • About: Returns the copyright and company information.
  • ExportService: It exports the web service contract classes into a DLL file. It provides the @servicename, @filename and @overwrite parameters.
  • RestartService: It restarts the web service. It provides @servicename parameter.
  • RebuildService: It re-generate the web service class and web methods. It provides @servicename parameter. Note: the service must setup first.
  • AddFilterSP: It filters the specific stored procedure from being the web method. It provides @spname and @servicename parameters.
  • RemoveFilterSP: It removes the filtered stored procedure from not being the web method. it provides @spname and @servicename parameters.

How It Works

sqlserverwebservice architecture

SQL Server Web Service, analyzes the user database and generates the WCF service classes in memory and compile it as DLL, then store it into the ServiceAssemblies table. Once the service required to be hosted, the SQL Server Web Service runs the ServiceProcess.exe to launch the WCF service on the given URL and port number.

Run a WCF Service!

I would like to use AdvantureWorks database to generate WCF service. The following statement setups the WCF service:-

EXEC SetupWebService @servicename = 'AdvWorks',
 @server='Fard-Solutions',
 @DATABASE='AdventureWorks2014',
 @ServiceURL='net.tcp://localhost:9998/AdvWorks';

Once the service is generated, a similar message appears on the Message tab.

wcf1

To verify the WCF service creation, I would like to take a look at the ServiceAssemblies table.

Use RunWebService stored procedure to run and host the WCF service, the following statements does that:-

EXEC dbo.RunService @servicename = 'AdvWorks';

To verify the WCF service, whether it is currently running, you may use GetServices stored procedure as following statement:-

EXEC dbo.GetServices;

GetServices stored procedures returns the list of currently running WCF Services and the related ProcessID.

wcf3

Once the WCF service is launched, you may verify the WCF service launcher by retrieving ServiceLog table records. The following resultset shows that ‘AdvWorks’ WCF service is launched and opened.

wcf5

Now, it is time to try to connect to WCF service through Visual Studio, the following figure illustrates the Web Service Reference window in VS which is already connected to recent generated and hosted WCF service.

wcf6

As above figure shows, all the stored procedures in the AdventureWorks2014 database have been generated as web method. You may use AddFilterSP to filter stored procedures to be generated as web method.

Features provided by SQL Service Web Service:

  • Easy installation steps on SQL Server instances.
  • Easy administration steps for pure DBAs.
  • Transport channel and data is encrypted and signed in the generated web service.
  • The user authentication and authorization is managed by SQL Server.
  • It is able to generate web method for every stored procedure in the target database.
  • It is able to host multiple web services at same time.
  • It is able to export the web service assembly as DLL file.
  • It is able to filter specific stored procedures.
  • It is able to rebuild and restart every web service easily when the web service is published.
  • It is able to distribute all modified and new data in every specific table automatically.(New) (Only in Full Version)
  • It can generate web methods base on specific user privileges. (New) (Only in Full Version)
  • It uses Net.TCP protocol to increase the web service performance and security. (New)
  • All data transferred through network is compressed. (New) (Only in Full Version)

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz