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 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 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 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 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 closedLori Brown puts a halt to things:
I have always done this by having a monitoring job that executes on a schedule that runs at a time when you need other jobs to stop. Of course, you need to be aware that stopping jobs can come with unwanted side effects of some data change that may be unfinished (there may be a rollback) and the stopped job will have to gracefully be re-run at another time. You will also see the stopped job as cancelled in the job activity monitor. And, hopefully you are aware that you can tell a job to stop but if it is doing work using a linked server, it may not stop as expected or it can take a while if it is rolling back a transaction.
Read on for an example of how to do this.
Comments closedDavid Seis puts the orphans to work:
In SQL Server, a user becomes ‘orphaned’ when it exists within a database but lacks an associated login at the server level. This typically occurs when a database is either moved or restored to a different SQL Server instance. To understand why, it’s important to note that while logins are created at the server level, users are created at the database level. Each login is linked to a unique Security Identifier (SID). Therefore, during the process of moving or restoring a database, the SIDs may not align correctly, resulting in orphaned users.
Read on for a script to find and fix orphaned users.
Comments closedGilbert Quevauvilliers saves some money:
Stop and start your Fabric Capacity using Power Automate
With Fabric Capacities trial coming to an end, you need to make sure to stop and start Fabric Capacities.
In my blog post below, I am going to show you how I can start or stop my Fabric Capacity by simply sending an email to myself with the details in the Subject Line to start or stop the capacity.
That’s a pretty neat method, especially if you have odd hours you want to run the capacity.
Comments closedTanayankar Chakraborty troubleshoots a strange issue:
Issue
An issue was brought to our attention recently where an azure SQL DB was throwing TempDB related errors although the customer felt that the TempDB usage never came close to the value published in the official Microsoft document. Here’s the error the customer had complained about:
Error
Here is a more detailed error text :
The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.’. Possible failure reasons: Problems with the query, ‘ResultSet’ property not set correctly, parameters not set correctly, or connection not established correctly.
This was an interesting problem and, admittedly, I didn’t predict the twist.
Comments closedBen Johnston begins a new series:
Inheriting a server, whether as an inexperienced user or an experienced DBA, has many challenges. It’s very helpful to evaluate the servers, document issues, and record the current configuration. It can also be beneficial to evaluate the current state of servers you have owned since they were built or even in preparation for a formal audit. The discovery and documentation phase of an audit will set you up for later detailed audits, or it may serve as the complete scope of the audit.
This is the first part of a series on evaluating and auditing SQL Server and Azure SQL Database. Auditing SQL is a very broad topic, so I have broken it down into several sections. This section will cover the major categories that should happen in a basic SQL Server discovery audit. An initial examination of your environment is primarily documentation and looking for critical issues. This includes basic server and SQL engine configuration, physical configuration items such as disk and memory, critical items such as backup state, database configuration, basic code smells, application integration, and high-level security configuration.
Read on for some of the things Ben looks at.
Comments closed