Many of you already know that you can expose your data as a web service natively in SQL 2005. No IIS. There are plenty of great articles that talk about it; one of them is at http://www.developer.com/db/article.php/3390241.
There are a few articles and blogs that talk about when you should use SQL endpoints. For example, check out When to Use HTTP/SOAP Endpoints section at the bottom of this article — http://www.devx.com/dbzone/Article/28525/1954?pf=true.
But I found no information on the resource cost of SQL EndPoints. So, I ran my own tests comprised of getting data from a simple small table with a hundred or so rows via SQL endpoints and by calling the stored proc directly, lazily using SQLDataAdapter.Fill method. I found that the processor on the SQL box is highly utilized for a significantly longer period of time, when using endpoints. Other server resources did not show a big difference.
#Threads Web Service Performance Stored Proc Performance
1 24 ms/iteration 3 ms/iter
5 58 ms/iter 4 ms/iter
10 109 ms/iter 5 ms/iter
Now, I know… it’s not quite an apple-to-apple comparison. But the data led me to the following conclusion: don’t use SQL endpoints unless you need web services without IIS; otherwise, just create a traditional web service using .NET, which calls the stored proc. After all, scaling web/middle tier is easy, scaling SQL is not.