T-SQL Tuesday 73 – Naughty or Nice?

As you work with SQL Server look around you. Is your environment Naughty or Nice? If it is Naughty what’s wrong with it? What would you do to fix it? Do you have a scrooge that is giving you the Christmas chills? Perhaps you have servers of past, present, and future haunting you. Maybe you are looking at SQL Server 2016 like some bright shining star in the east. [Read More]

Identity Column Increment Value (EVEN/ODD)

“Hey DBA’s, I have a thought about a really creative solution to this really hard problem. I have two tables in my database and each has an identity column, each of which is currently incrementing by 1. I’d like to change each to increment by 2. Also, one of the tables will need to only contain ODD numbered values, and the other will need to only contain EVEN numbered values. [Read More]

Using Extended Events to Detect Connection Pooling

Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling is handled differently for the .NET Framework data providers. Opening connections is a very expensive and time consuming operation. Connection pooling helps alleviate that by providing a reusable “pool” of connections that your application can share. [Read More]

Running Parameterized Queries against SQL Server using PowerShell

For many years I didn’t really think about the implications of how I was retrieving data from my SQL Servers in PowerShell. I was just happy that I was able to retrieve the data! As I learned more about SQL Server I started to think of things like SQL injection and using parameterized queries to promote plan reuse. I went back and looked at some of the old PowerShell scripts that I had written and found that I was way off! [Read More]

Pratical Use of a SQL Server Database Snapshot

I was doing a database code deployment the other night and the first step in the release plan was “Take a full database backup in case we have to roll back the deployment”. The database in question was about 100 GB in size, so not huge, but not small either. On our current hardware a full backup takes anywhere from 15-20 minutes on average. That’s 15-20 minutes longer than it needed to be because I knew there was a better way to offer that same rollback protection. [Read More]

TSQL2SDAY 70 - Strategies for managing an enterprise

Thanks to MidnightDBA (b/t) for hosting this month’s #tsql2sday. Strategies for managing an enterprise. What do you think of when you hear the word enterprise? I used to think mainframes and big iron. I would think of sprawling server rooms with dozens of server racks. I used to think monstrous storage arrays and backup tape libraries as far as the eye could see. I used to think of a large employee base. [Read More]

How many errors can the PowerShell error buffer hold?

PowerShell stores errors that it encounters in a circular error buffer variable named $Error. $Error also contains a count property to count the number of errors in the buffer. To figure out how many errors we could hold was pretty straight forward. All I had to do was create a script that generated an error and see how high $error.count got. At this point I didn’t even know if there was a maximum or not. [Read More]

Present at SQL Saturday

I have nothing but wonderful things to say about the SQL Saturday 386 leadership and the army of volunteers they brought along. It was a very well run event. The venue was nice, everything was very well organized, the people were amazingly helpful and friendly, and it made the perfect place for me to turn my presentation up a notch. It’s amazing how much of a calming effect can be felt when you are surrounded by such an inviting and amazing group of people. [Read More]

T-SQL Tuesday 68 - Just Say No to Defaults

Here is a link to the official #tsql2sday invitation from Andy Yun’s Blog. This month’s subject is “Just Say No to Defaults”. If you’ve read my blog at all you will know that I have a slight obsession with SQL Server and PowerShell. As far as I am concerned PowerShell is the new gold standard when it comes to scripting and automation in the Windows environment. Add a sprinkle of SQL Server with the SQL Server PowerShell module (SQLPS) and you have a deadly combination as a DBA. [Read More]

SQL Server Policy Based Management by Example

What steps do you go through to validate SQL Server configurations after a server build? Even a single server can take some time and is also prone to human error. Have you ever needed to validate a configuration setting across all of the SQL Servers in your environment? If your environment is big enough, doing this manually isn’t even a realistic option. Policy Based Management excels at these things. Introduced in SQL Server 2008, it can definitely help DBA’s manage their ever growing environments. [Read More]