DaveWentzel.com            All Things Data


Service Broker Demystified - Monitoring the Canaries

Service Broker doesn't have a native series of monitoring scripts nor a GUI to show you the health of your queues.  In this post I'll show you what I monitor and why.  I even have some interesting "canaries" that I monitor that will tell me when my SSB design is nearing an imminent failure.

Polyglot Database Development with 0xDBE

This is an unsolicited review of OxDBE, a universal SQL IDE by JetBrains.  Universal SQL IDEs are meant to make your life easier developing code for multiple DBMSs.  Instead of learning the nuances of multiple DBMS IDEs you can spend your time learning the nuances of each vendor's SQL dialect.  The problem is universal SQL IDEs tend to be either pricey or buggy.  0xDBE is polished and has almost no learning curve if you are familiar with IntelliJ.   Try it while it's still free.  

(This is an unsolicited review of a product I really love.)  

Dynamic Linked Server Generator

It's handy to be able to setup and teardown a linked server at will.  Especially in dev envs.  You may need to refresh some data from a different environment and firing up SSIS is overkill when a simple INSERT INTO...SELECT is sufficient.  In this post I'll show you a utility I've been using for years that allows you to build a dynamic linked server quickly and easily.  


Service Broker Demystified - Can I model monologs? Yes you can!

The most common use case for Service Broker is to run a stored procedure asynchronously.  This is best modeled as a monolog, commonly known in the SQL Server world as "fire-and-forget".  Every other messaging system (MSMQ, JMS, Rabbit MQ) support monologs natively, in fact they are the default.  But not Service Broker.  If you try to model a monolog you'll find that it is rife with danger.  In this post I'll show you how you can, safely, model a monolog.  

PMP Certified!

Last week I sat for, and passed, the PMP certification.  In this post I'll discuss problems with vendor certifications and why an industry certification like the Project Management Institute's Project Management Professional certification is valuable.  Even a non-manager, propellor-head should consider the PMP certification to advance his career and improve his brand.  

Service Broker Demystified - Fire and Forget Anti-Pattern

After you work with Service Broker for some time you'll learn about the dreaded "fire-and-forget" (anti-)pattern.  In this post I'll show you exactly why fire-and-forget is so bad.  I'll also show you the one simple rule to remember to avoid accidentally modeling fire-and-forget. Finally, we'll learn how to fix it production systems where fire-and-forget has bitten you.  In the next post we'll cover how to model a real fire-and-forget design safely...using a monolog.  

PostgreSQL for the SQL Server Guy

PostgreSQL is an open source RDBMS that is very similar to SQL Server.  In this post I'll compare the features and syntax of the two.  Learning a new DBMS tool can be a lot of fun and will help you understand SQL Server even better.  

Service Broker Demystified - Must my SQL Server be wired to the internet?

Service Broker uses a lot of syntax that is unfamiliar to data professionals.  This scares them away from using SSB.  For instance, DBAs will see references to URLs like http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification and immediately assume that their SQL Server will need to be wired to the internet to resolve those references.  It doesn't.  Those are simple URIs (namespaces) and SSB never connects to them.  In this post I'll prove it and show you why they are used.  

Service Broker Demystified - CLOSED conversations

Those new to Service Broker are confused by CLOSED conversations.  They hang around even when they are CLOSED.  A quick google search will tell you this is for security purposes.  In this post I'll show you exactly why that is.  Also, invariably, one day a DBA will see a huge number of CLOSED conversations that never get cleaned out.  I'll also cover why that happens and how to avoid it.  

Service Broker Demystified - Case-Sensitivity

Some SSB objects are case-sensitive, but which ones?  Shouldn't they all follow the case sensitivity of your server or database?  The answer:  No.  Only "exposed" SSB objects are case-sensitive.  This post will cover why.  


Subscribe to RSS - blogs