2

I have an .net WCF service (hosted on IIS 7.5) that makes a high volume of calls to a SQL Server 2008 r2 stored procedure which inserts into several tables. The clients of this WCF service are used 24/7 although processing a lower volume of requests during the night. Most of the time this works ok but once a month an SSIS job must run which makes high volume inserts to the same table. When the SSIS job runs (takes approx 4 hours to complete) it causes problems for the WCF service which results in timeouts.

My initial thought was to amend the stored proc so that the insert is made to temporary table, which is later merged to the main table. However, the DBA responsible for the SQL server tells me that during the period that SSIS job runs the server is totally maxed out.

So, I need to come up with some form of cache / queue that doesn't hit the SQL Server. This cache also needs to be resilient and scaleable. Is App Fabric cache the best tool for this? I've had a quick look at the API and I couldn't see an obvious way to make use of it like a queue, the method to Get from the cache seems to require a key - which wouldn't be known by the new service to pull from the cache and write to the permanent SQL Store (once the SSIS job had completed).

In the past I've used MSMQ to solve a similar problem but all the documentation on this seems really old so maybe App Fabric Cache is the preferred choice nowadays?

Thanks,

Rob.

Rob Bowman
  • 7,632
  • 22
  • 93
  • 200

4 Answers4

1

I suppose you could use AppFabric for queueing writes/inserts to SQL, but I don't think that's what AppFabric was intended to do.

A way you could accomplish this though with AppFabric:

  1. Give each write/insert a random key.
  2. Create an index object that stores the keys for your inserts.
  3. When you need to do your bulk insert, you can just call a GET that will bring back the array of keys used for inserts.
  4. Iterate over the list of keys and start doing your inserts.

It sounds more like a job for MSMQ - and that was the first technology that came to mind when I started reading your question.

You could also consider some open source alternatives like RabbitMQ or ActiveMQ.

ryan1234
  • 7,237
  • 6
  • 25
  • 36
1

I see two immediate options: a) Ask the clients to use NetMsmqBinding. Note however that the Windows Server AppFabric is converging with the Azure AppFabric, and there there are other options. Particularly, you might like to look at Service Bus for Windows Server v1.0, which is a free extension to make Win Server more in-line with the Azure platform. Whichever, Windows Server Service Bus or MSMQ, it can be done fairly easily with advise from the community if you are not too familiar with the settings.

b) Use the Sql Server Service Broker. This is a messaging system native to SQL Server and will allow you to call the sp's asynchronously.

Sentinel
  • 3,582
  • 1
  • 30
  • 44
0

Thanks for your input.

After investigating various options I think the answer is to import the bulk data then "stitch-in" using sliding window partitioning

halfer
  • 19,824
  • 17
  • 99
  • 186
Rob Bowman
  • 7,632
  • 22
  • 93
  • 200
0

Well, I thought of Redis when I first read the question. It is damn easy to setup (compared to AppFabric), and with proven scalability. You could use it for distributed caching, or like in this case, you could use the built-in queuing functionality. No need to add more tools and complexity to the mix.

Jose Parra
  • 877
  • 9
  • 23