What happens if you have a SQL Agent job, but the logic is not aware (or cannot be aware) that you may have an Availability Group in place. There is nothing built-in to Agent to account for this, but there is a way can you configure an Agent job to check for the existence of an AG before proceeding. Here is the scenario. First, we have a 2-node cluster running a synchronous Availability Group with automatic failover and readable secondaries turned off. Next, we have a requirement to run a SQL Agent job every hour to collect data from a...
Continue reading...Patrick Keisler
Management Studio Edit Top 200 Rows
The other day I was asked why Management Studio limits you to only 200 rows when you right click on a table to edit the data. The easy answer is because that is how Management Studio is configured. However, it’s also something that can easily be changed. From the menu just select Tools and then Options. In the Options window, select SQL Server Object Explorer on the left side. On the right side you’ll see the the Table and View Options. Change the Value for Edit Top <n> Rows to something like 500 and then click OK. Now go back...
Continue reading...Management Studio Dark Theme
There is still no love for SQL Server Management Studio. SSMS v18 Beta included a Dark Theme, but somehow that did not make it into the final release of the product. Other apps have this capability (Visual Studio, Visual Studio Code, Chrome, Edge) but not SSMS. If you are like me, sometimes you want to switch your apps to a dark theme for added flair. A quick web search will reveal several ways to hack SSMS to display a dark theme, but that may not produce the best results. So, until Microsoft decides to include it, here is how I...
Continue reading...SQL Server Audit Recipe – xp_cmdshell
This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details. Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority. The first step is to create a server audit to hold the events that we collect. You’ll notice that we added a...
Continue reading...SQL Server Morning Health Checks
Every now and again as a Microsoft PFE, you get a chance to make a big difference for a customer. One such occasion happened just recently when I was asked to help find a way to automate the daily checks the DBA had to perform every morning. The result was a PowerShell script that reduced that manual task down from an hour to less than a minute. You can read the full article here on MSDN. https://blogs.msdn.microsoft.com/samlester/2017/12/29/sql-server-dba-morning-health-checks/ The PowerShell script can be downloaded from here. https://github.com/PatrickKeisler/SQLMorningHealthChecks
Continue reading...
