Configuration Manager: Database Exploration, Part 2 - Notes

Before I continue on with this is "theme" that I've started, there are some very important issues I need to discuss.  Rather than boring you with a long introduction, I will just dive in and hit each one as I go.  I'll warn you that this is article is taking a sharp turn into a dark tunnel of seriousness.  No joking around here.  Very unlike my usual goofy stuff, but it's important to cover this before I continue on.

SMS Provider vs. SQL Server

From a "purely technical" aspect, you can interact with, and manage, a Configuration Manager site data store through the SMS Provider interface, or through SQL Server (ADO or ADO.NET, etc.), however, you absolutely NEED to be careful to avoid some easy mistakes.  This is all within the context of building custom applications which interface with your Configuration Manager infrastructure.  This is also regardless of whether you are working with Configuration Manager 2007 or 2012.

• While you can query (retrieve) information from either interface, the SQL interface is usually much faster to execute. I'm obviously talking about using the ADO or ADO.NET pipeline.  However...
• NEVER attempt to update anything directly through the SQL Server interface! All operations that involve modifying site resources, collections, or settings (and so on), should be performed through the SMS Provider only.  Some examples include adding a Package to a Distribution Point, or adding a Resource to a direct-membership Collection.  Going around the SMS Provider can cause serious problems for your Configuration Manager site.  I'll spare you the lengthy explanation of how the inboxes and outboxes are spooled and de-spooled in the background, and how it all weaves in and out of the database 
• Executing intensive queries (or updates, for that matter) against the SMS Provider interface can impact Configuration Manager processing, especially if performed at peak processing times (discovery cycles, software deployments, etc.).  The net result may cause a backlog in data processing and show up in your component status logs as well.  Try to limit such activity to off-peak times or days to avoid impacting Configuration Manager itself.
• Executing intensive queries directly against the site SQL Server database may also impact performance, and should be carefully monitored by using SQL profiling and performance logs to determine the level and duration of such impact.
• Use the most efficient tool to handle a specific task:  If you are post-processing query results and spending a lot of code cycles calculating date differences, cost values, or mapping integers to string values - do that instead within the query!!!  SQL is so much faster and more efficient at many common data manipulation tasks than standard 3GL, 4GL programming languages or scripts.
• Minimize Connections!  If you have code firing off multiple queries, be sure to pay close attention to how you open and close your data connections.  If you can use one connection for all of your queries, do it.  It will save time and reduce the overhead impact on the data store host itself. This is true for using SQL Server or the SMS Provider.

Database Separation and Isolation

Most any DBA with a fair amount of experience will advise you to avoid direct interaction with "mission critical" data stores if you can instead use a replica.  It really boils down to how time-sensitive the information is that you rely upon to accomplish the required task.  If you need to generate inventory reports, and your inventory is only updated every day or week, you probably could do just fine by pointing your queries at a replica database and avoid adding more overhead on your production database.  It's just one more thing to consider if you are worried about performance impact.

The Right Tools

If you haven't used SQL Server Management Studio, or haven't used it much, give it a try.  In fact, if you're testing your queries through your code debugger, STOP.  That's a bad habit and can yield some very skewed results.  As the old saying goes: "Just because you CAN, doesn't mean you SHOULD".  I can't count the number of times I've asked a programmer to minimize their code debugger and run the same queries in the SSMS console, and seen their reaction to how different the performance can be.  It can really highlight where program code is slowing down a conversion or calculation step that could be more efficiently executed within the SQL statement.  

It's not really about SSMS.  Any tool that lets you model and execute T-SQL statements directly against the data store will work fine.  It's when you run the SQL expressions from within the program code that things can get twisted.  Eliminating secondary and tertiary processing layers ensures you get an accurate, honest and clear picture of what's going on.

Safety

Living on the edge is cool, if you get paid to do commercials for Red Bull.  For the rest of us, it helps if we take certain precautions to avoid letting simple mistakes explode into disastrous calamities.  If you have the option of a test environment, use it.  If not, employ test-environment methods to mitigate unintentional impact on production systems.  It's really that simple.