When managing a small amount of databases, a simple database growth chart is an effective tool for capacity planning. However, as an environment grows into the 100s or 1000s of databases, performing chart based capacity planning can be very tedious. Machine learning is an effective tool for automatically calculating growth trends and predicting database growth.
One challenge with SQL Server database automation is that eventually you're going to have to execute a native TSQL statement. There are some very trendy tools out there, ie. Puppet, Ansible, etc... and it can be a challenge to accomplish everything using their native framework. Have you ever seen a linked server resource type in… Continue reading Installing SQLCMD on Linux
SQL Server 2017 introduced graph database functionality. Graphs are a core concept in computer science and I was excited to hear of this new feature. Robert Sheldon has a great series on SQL Server's graph database here. If you're interested in learning about graphs in SQL Server, it's a great place to start. While there's… Continue reading SQL Server Graph Database of US Capitals
When a SQL Server instance has a large number of databases (100s or 1000s), it can be very challenging to complete backups in a timely manner. Using common methods such as Ola Hallengren scripts or native maintenance plans often take a long time due to serial processing (1 processor). So the question becomes, how do… Continue reading Parallel Ola Hallengren Backups
This month's TSQLTuesday is hosted by Steve Jones on the topic of triggers! In a large enough environment, eventually the question will be asked "is this database still being used?" There's a few technologies available to help you answer this question and by far the worst is logon triggers. I work in a large mixed… Continue reading Logon Trigger Woes
If users are logging into your SQL Server with their Active Directory accounts, you may run into an issue if their login changes. This issue is usually associated with someone changing their account name to reflect a change in their legal name. If the user executes the function SUSER_SNAME(), it may return their old login.… Continue reading SUSER_SNAME() Returning the Wrong Login
If you work in a VMWare shop you're probably aware that VMs can be migrated to a new host under certain conditions. While the purpose of these migrations is good, I've found that virtual SQL Servers usually do not like to be VMotioned. At my current employer, this is often noticed in AlwaysOn Availbility Groups.… Continue reading Finding VMotions with Powershell and the VCenter CLI
Recently, I was asked to investigate high CPU on a production server. I began my routine which is a combination of wait stats, performance counters, and resource hogging statements. I had identified a CPU intensive stored procedure that had a cardinality issue in the plan. I was about to present my findings when.... POOF. Another… Continue reading So You Blew Up the Plan Cache…
So, is it possible to go from Enterprise to Developer in an AlwaysOn cluster? TLDR: Yes. I was working in a dev environment recently that had been setup with AlwaysOn Availability Groups using the Enterprise Edition of SQL Server 2016. This environment is completely devoted to development of our applications which means it qualifies for… Continue reading Rolling Downgrade From Enterprise to Developer
In March I did my first presentation at SQL Saturday. I'd say it went very well and I was very encouraged with the attendee feedback. My presentation was on infrastructure testing with the Pester Powershell module. I didn't go into depth on the advanced features of Pester (ie. mocking) but instead focused on how you… Continue reading Pester Your SQL Servers!