DaveWentzel.com            All Things Data

SQL Server

Azure SQL Data Warehouse: Problems, Performance, Things You Need to Understand

This post isn't going to teach you the basics of Azure SQL Data Warehouse (ASQLDW).  This post is going to honestly cover the strengths and weaknesses of the product and will show you the *possible* pitfalls you may encounter.  Let me say upfront, ASQLDW is a *terrific* product, if you understand *exactly* what the use-cases are for the tool.  This is not an ASQLDW 101 post, if I write about a concept you don't understand I assume you can find that info using a basic google search.  To be clear, I have tons of experience in the MPP space...I've used Vertica for years (and I understand the costs/benefits of MPP.  Most of the same "gotchas" with ASQLDW/APS are also true of Redshift, Snowflake, and Vertica.  Each tool has a slightly different take on how to overcome perceived MPP shortcomings when compared to a traditional RDBMS.  Do your research!

PerformanceCollector is now available for SQL Azure

PerformanceCollector (PC) is free and open-source software (FOSS) that I developed to monitor and baseline performance of your SQL Server.  There are certainly plenty of commercial tools and even free scripts available on the net that handle all of this.

Service Broker Demystified Series - Summary

This blog series focused on the common complaints I hear about SQL Server Service Broker...namely that it is too confusing for a data professional to master.  The fact that there is no (good) GUI or monitoring tools doesn't help either.  In this series I simplified things and explained why SSB works the way it does.  I also provided tricks and shortcuts to make everything a little simpler.  SSB is a really great technology and I find new uses for it almost every day.  

Service Broker Demystified - How to Radically Simplify SB

Setting up Service Broker objects can be challenging.  Objects need to be deployed in the correct dependency order.  And then if something goes wrong you need to determine what objects need to be recreated as well as what messages may still be "stalled" and waiting for processing.  In this post I'll show you my "pattern" script that helps to ease these burdens.  Using the concept of "script idempotency" I can guarantee that all objects are deployed and running correctly.  At any time.  

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.  

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.  


Subscribe to RSS - SQL Server