Every Scooby-Doo episode has one thing in common — there’s always a fake ghost. Sometimes, that “ghost” is really just a guy in a mask trying to scare everyone with smoke machines and spooky sounds. In the SQL Server world, this ghost appears as the canned-query consultant. You’ve seen them. They roll in with a big name and an even bigger slide deck. They click “Run All,” hand you a 200-page report filled with screenshots and DMV outputs, and vanish into the night. They didn’t solve your mystery; they just dumped a list of clues in your lap. Or worse, you hired a big-name consulting firm, which brought on a…
-
-
One of the biggest impacts on resource consumption for Azure SQL DB are repeated data pulls by the application layer. No matter how fast those queries execute calling the same procedure or issuing the same SQL statements hundreds, thousands, or million times a day can wreak havoc on database performance. Death by a thousand cuts can easily bring a system to its knees. Sometimes it’s hard for DBAs to troubleshoot these actively as the execution of the statements happens so quickly they don’t even show in tools like sp_whoisactive. It’s not until you begin to dive into things like Query Performance Insights or Query Store that you start to see…
-
Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal. By right clicking on your database properties and choosing the Configure SQL page you can change your Subscription and Azure Location. Even more and what is really cool is I can also scale up and down my Edition (which is generally referred to as Service Tier outside of SSMS), Size and Storage Tiers as well.…
-
What is Azure SQL Database Serverless? When I hear the term serverless my mind gets confused. How can a database exist without a server? Azure is a cloud platform, is my database just floating in the air? No, I am not really thinking that, but still the word serverless can be hard to understand. So, let’s walk through what it is. Serverless is a term commonly used for function as a service patterns like Amazon Lambda, or Azure Functions, where you have a piece of code that is called and executed without you deploying any infrastructure. Azure Logic Apps are another version of this—a program that exists to do a…
-
Every day, more IT organizations decide to move their SQL Server databases to Azure. In fact, over a million on-premises SQL Server databases have been moved to Azure. There’s an interesting blog about how Microsoft is faster and cheaper than its competitors that’s worth a read. To assist with your move to Azure Microsoft offers a number of migration tools and services to make this move as smooth as possible which I think attributes to their success. Two of those options are below with some informational links. If you’re migrating a number of large SQL Server instances, Azure Database Migration Service is the best way to migrate databases to Azure…
-
Following up from last week’s post on Azure Key Vault in this blog I will show you how to the setup Key Vault logging I mentioned for auditing access and usage of your key vault. Once we walk through the process of enabling your logging, we will configure Azure Log Analytics as a way to analyze that data. Azure Log Analytics uses advanced analytics and machine learning to analyze your azure log files. It adds intelligent insights to your monitored data such as Key Vault usage and access as well as latency in key retrieval from your Audit Event Logs. Here is the PowerShell Script you can use to enable…
-
Keys and secrets (AKA passwords) are an essential part of data protection management not only on-premises, but within the cloud as well. One of the many advantages of cloud is the ability to have a secure, persisted key store. If you have used a password manager like Keepass or 1Password, you can consider Azure Key Vault to be an enterprise level password manager, but also a lot more. One of the functions that Azure Key Vault supports is for you to keep small secrets such as passwords, tokens, connection strings, API keys as well as encryption keys and certificates in a safe tightly controlled secure location in the cloud. It…
-
Azure offers a lot of value-added services included with the price of what you pay . One of the things really, I like is Azure Advisor. this service provides information about your entire Azure landscape. It gives you a complete overview of your system needs including possible ways to save money. High Availability shows you ways to improve the continuity of your business-critical applications. Security detects threats and vulnerabilities that might lead to security breaches. Performance shows you ways to speed up your applications. Cost give you ways to reduce your overall Azure spreading. As you can see from the above screenshot, we have several recommendations on how we can…
-
Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor, has gotten better over time it is still not a tool more senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and run-time performance information. Microsoft has taken an additional leap with…
-
Microsoft gives you two purchasing models for your Azure SQL Databases (SQL DB), a DTU and a vCore based model (logical CPU’s). Both options allow you to scale up or down based on your compute and storage requirements. Regardless of which model you choose performance tuning can save you money using SQL DB. DTU (s) (Database Transaction Units) measure “a blended measure of CPU, memory, and data I/O and transaction log I/O” that Microsoft uses to help estimate which DTU based Service Tier you should be using. By reducing your I/O, memory or CPU consumption you utilize less DTUs or cores and thus can step down service tiers. Stepping down service…
