A Fine Slice Of SQL Server
David Seis talks about the DAC:
In SQL Server, a feature that often stands out for its unique capabilities is the Dedicated Administrator Connection (DAC). This not just another connection to your SQL Server instance, but a special diagnostic connection designed for administrators when standard connections to the server are not possible.
Read on to see how it differs from regular connections and how you can use it.
Comments closedJared Westover solves a problem:
Have you ever needed to prevent duplicate rows from popping up in a table, but the built-in unique constraint in SQL Server falls short? I ran into a table with duplicate rows, but we couldn’t delete them, at least for now. Ideally, you would delete all the duplicates and call it a day. If life were this simple, it would be boring. We needed to prevent new ones from showing up and keep the existing ones. The problem with a unique constraint is that it applies to all the rows in a column.
You can also use a filtered index to prevent the problem in the first place. This was actually an interview question I liked to give: with filtered indexes, you can ensure each non-NULL value was unique, but that you could have as many rows with NULL as your dataset required.
Comments closedVincent-Philippe Lauzon has an announcement:
Kusto databases, either in Fabric (KQL Database) or in Azure (Azure Data Explorer), are optimized for append ingestion.
In recent years, we’ve introduced the .delete command, allowing you to selectively delete records.
In February, we introduced the .update command in public preview. This command allows you to update records by deleting existing records and appending new ones in a single transaction.
Today, the .update is Generally Available (GA)!
Click through for more details, including a link to the documentation, where you can see several examples of the syntax.
Comments closedSteven Sanderson shows us the ways:
If you often work with Excel files and are looking to streamline your data import and export processes, R and Python offer some powerful packages to help you. Here, I’ll introduce you to some essential tools in both R and Python that will make handling Excel files a breeze.
Read on for three options in R and two in Python.
Comments closedNikola Ilic shows us the current options:
Options, options, options…Having the possibility to perform a certain task in multiple different ways is usually a great “problem” to have, although very often not each option is equally effective. And, Microsoft Fabric is all about “options”…You want to ingest the data? No problem, you can use notebooks, pipelines, Dataflows, or T-SQL. Data transformation needed? No worries at all – again, you may leverage notebooks, T-SQL, Dataflows…Data processing, you asked? Lakehouse (Spark), Warehouse (SQL), Real-Time Intelligence (KQL), Power BI…The choice is yours again.
In a nutshell, almost every single task in Microsoft Fabric can be completed in multiple ways, and there is no “right” or “wrong” tool, as long as it gets the job done (of course, as efficiently as possible).
Nikola lays out two pre-requisites and then shows us two options we can currently use, and three potential options we currently cannot use.
Comments closedYohei Kawajiri describes three techniques for performing a SQL Server migration:
It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind:
- Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid.
- Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity.
Building an availability group? Yeah, makes a lot of sense. Performing log shipping? Sure, I could see that working. Database mirroring? I did not expect to read that one, mostly because it’s been deprecated for a decade.
Comments closedRadim Marek compares two extensions:
Working with PostgreSQL, and virtually any database system, extends far beyond merely inserting and retrieving data. Many application and business processes, maintenance tasks, reporting, and orchestration tasks require the integration of a job scheduler. While third-party tools can drive automation, you can also automate the execution of predefined tasks directly within the database environment. Although system-level cron might be a starting point, the power of the database system lies in its ability to store all the necessary information alongside your data/schema. In this article, we will explore
pg_cronandpg_timetableas two distinct PostgreSQL-specific tools for scheduled task automation.
Read on to learn more about each.
Comments closedJeff Iannucci continues a series on security:
If you have been reading along with our series of “30 SQL Server Security Checks in 30 days” posts, you’ve probably noticed a theme for a lot of these posts, where we recommend reviewing which principals have CONTROL SERVER permissions or are in the sysadmin role.
Full disclosure: I hope you aren’t tired of that yet, because there will be more of those posts.
However, today I wanted to turn your attention towards what might be considered potential shadow members of the sysadmin role. These are the members of the server’s local Windows Administrators group.
Read on for Jeff’s explanation.
Comments closedJosephine Bush wants to find the deadlocks:
A couple of weeks ago, a developer came to me and wanted to know how to figure out what was causing a deadlock. I honestly didn’t know where to look or if this was even being captured in Azure SQL DB already. It turns out that Microsoft has you covered with deadlock tracking. At least for a period of time. It looks like you can go back about a month, maybe.
Read on to see how you can find this information in Azure SQL DB. If you’re working in on-prem SQL Server and you don’t have any tooling set up, you can find some deadlocks in the system health extended event.
Comments closedI recently encountered an issue where an index rebuild set to
wait_at_low_priorityended up blocking an asynchronous statistics update. This interaction led to a large blocking chain where queries were waiting on the async stats update and started to timeout.
Read on for an explanation of all of the players involved, then a demo, and finally two solutions.
Comments closed