A Fine Slice Of SQL Server
Kevin Wilkie gets down to tuning:
In our last post, we talked about some of my favorite queries I use in Snowflake to see various items of interest – such as finding the worst-performing queries. For today’s post, though, I want to talk about performance tuning.
Yes, you read that right. We’re going to use query history to do some fun performance tuning.
Click through for two queries that can help you find what you may need to tune.
Comments closedRecently, we faced a requirement to upgrade large number of Azure SQL databases from general-purpose to business-critical.
As you’re aware, this scaling-up operation can be executed via PowerShell, CLI, or the Azure portal and follow the guidance mentioned here – Failover groups overview & best practices – Azure SQL Managed Instance | Microsoft Learn
Given the need to perform this task across a large number of databases, individually running commands for each server is not practical. Hence, I have created a PowerShell script to facilitate such extensive migrations.
Click through for the scenarios Sakshi tested, some important considerations, and the script itself.
Comments closedDave Bermingham talks about availability:
Building a SQL Server Failover Cluster in Google Cloud Platform (GCP) is a powerful way to ensure your databases remain highly available, even in the face of unexpected failures. High Availability (HA) is crucial for any business-critical application. Downtime can mean lost revenue, decreased productivity, and even damage to your company’s reputation. However, creating HA clusters in the cloud, especially in GCP, presents unique challenges—most notably, the lack of shared storage, which has traditionally been a key component of SQL Server Failover Clustering.
Click through for an overview and a whitepaper on the topic.
Comments closedMatt Collins shares some recommendations:
You can use Databricks for a vast range of applications these days. From handling streaming datasets, running Deep Learning models to populating data model fact tables with complex transformations, choosing the correct compute option can seem a lot like a stab in the dark followed by (potentially expensive) trial end error.
You can choose from an incredible range of configurations in the Databricks Compute User Interface.
This variety is comprised of the Virtual Machine (VM) category, size, availability and access mode (also referred to as family).
Determining the right compute choice for you could be answered by the classic answer “it depends”, but this guide aim to inform the decision-making process — both in terms of cost and performance.
Read on for more information.
Comments closedMichal Bar has an update for us:
Welcome to the July 2024 update. We are excited to announce new features and improvements in ADX web UI.
Continue reading to learn more about:
- Copy query with syntax coloring and KQL IntelliSense improvements
- Ad-hoc visual data exploration
- Dashboards real time refresh rate
- Enhanced data interaction for dashboard tiles
- Resize and move dashboard tiles using keyboard only
Click through to continue reading.
Comments closedMihailo Joksimovic shows a use case for Azure SQL Managed Instance link:
Azure SQL Managed Instance link is a new feature enabling you to create a distributed availability group between your SQL Server and Azure SQL Managed Instance. It makes it super simple to connect on-prem. and cloud, providing near real-time replication speeds. Benefits are many and if you aren’t familiar, I’d strongly suggest you read our official blog post about it.
One great benefit for our use-case is that MI Link takes care of transferring the CLR assemblies for you! You can import your assemblies on your SQL Server instance, using all too familiar syntax (i.e. CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’), and MI link will ensure that those same assemblies get transferred to cloud. Easy peasy and no need to deal with hex literals anymore.
Read on to learn more about this capability.
Comments closedMicrosoft Fabric uses a data lakehouse architecture, which means it does not use a relational data warehouse (with its relational engine and relational storage) and instead uses only a data lake to store data. Data is stored in Delta lake format so that the data lake acquires relational data warehouse-like features (check out my book that goes into much detail on this, or my video). Here is what a typical architecture looks like when using Fabric (click here for the .vsd):
Click through for the image as well as James’s explanation of the components.
Comments closedDavid Fowler backs up a database:
Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.
Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.
And, going one step further, you can also use PolyBase to read data from S3 buckets in SQL Server 2022.
Comments closedPaul Hernandez doesn’t want to write ETL jobs:
In the ever-evolving landscape of data management and analytics, choosing the right tools and approaches is crucial for optimizing performance and achieving business goals. Two prominent solutions that have gained traction are Azure Synapse Link for Azure Cosmos DB and Mirroring in Microsoft Fabric. Both offer unique benefits and cater to different needs, making it essential to understand their differences and use cases.
Read on to see how each of these works, as well as a quick demonstration of efficacy.
Comments closedAndy Brownsword deletes some files but wants to keep other files:
A hierarchy of directories which contain files. That’s how we typically think about file storage. That’s not quite the same everywhere. In Blob Storage a file can appear to be in a directory, but when it’s removed so is the directory.
This can occur when using Lifecycle Management to help purge legacy blobs, which can be unexpected. Let’s look at a way we can help remediate this.
One important thing to remember about Azure blob storage accounts and S3 buckets is that there’s really no concept of a directory structure. It’s all keys, where your key might be dir1/dir2/dir3/file.txt. This is a bit different for Azure Data Lake Storage Gen2 and its notion of hierarchical namespaces (i.e., folders). But Andy does walk through some of the consequences of this and how to work with lifecycle maintenance policies to delete only certain sets of files.