Tuesday, April 12, 2016

Service Broker and External Activator for Sql Server...

Introduced in Sql Server 2005, Microsoft's Service Broker is a Messaging Queue for Sql Server.  You can audit tables, trigger events, and even call web services (Using the External Activator).

Follow this excellent tutorial by Dev Kimchi which helps you step by step and clearly explain how to setup your Service Broker messaging queue.

To get started with the external activator you will require the version for the version of Sql Server you are running, click on any of the Microsoft download links and follow the install instructions:

Microsoft® SQL Server® 2012 Feature Pack
Microsoft® SQL Server® 2014 Feature Pack

One last thing before you get frustrated installing the External Activator service on a remote server.  At least for the 2012 version, it still required .Net 3.5 installed on the destination computer/server.  If you are trying to install on a Windows 2012 server follow this helpful guide on how to get .Net 3.5 installed now that the latest packs have been released...http://serverfault.com/questions/705702/installing-net-3-5-on-windows-2012-r2

Follow along and enjoy your journey,

Service Broker External Activator for SQL Server Step by Step


 Once you are done with the tutorial and you are happy with the results, you may notice the response queue begins to fill up.  What to do?

http://www.madeiradata.com/service-broker-asynchronous-triggers/
using the logic for closing the dialogs (SB_AT_CloseDialogs) you can set this procedure to clear out any completed dialogs (messages).  only the error messages will remain and allow you to troubleshoot by removing what's been processed.

Enjoy!

Thursday, March 10, 2016

But can you Tuna Fish? Database Optimizations worth reviewing

There are always new articles about the web that will offer new ways on how to optimize the performance of your database.

There are hardware focused optimizations that can and do improve your users data access, and you can fix things at the code level too.  Then there is optimizing your database engine to squeeze out every last bit of performance.

Are you just a bit curious on how to get started?


A recent blog article targets many areas in your code that you can mitigate to help deliver the best performance, there are also indexing guides which I don't think I've ever covered in my blog, check it out here:

https://www.toptal.com/sql/sql-database-tuning-for-developers

which covers these topics,
  • Database Optimization (in the Codebase) Indexes
  • Tune SQL query performance by avoiding coding loops
  • Avoid correlated subqueries
  • Select sparingly
  • The use of temp tables SQL Server Performance Tuning: Execution Plans

To optimize your existing indexes you can refer to my previous article Boosting Performance and review how to get that database back on track!

cheers!