Do you default to SET NOCOUNT ON in stored procedures?

What's the Big Deal with SET NOCOUNT ON? - Azure SQL Dev Corner

Every minute, a job wakes up, checks for work, finds none, and exits. Multiply that across a production environment, and the scheduler itself can become operational noise.

The real question isn't how frequently a job should run.

It's this:
Why is the scheduler searching for work that the application already knows has arrived?

In this article, I explore an on-demand orchestration pattern implemented using SQL Server Agent. The focus is on reducing unnecessary polling by allowing the application to signal when work is actually available.

📖 Read the full article here:
https://medium.com/towards-data-engineering/rethinking-sql-server-agent-scheduling-an-on-demand-orchestration-pattern-938b9fa360f6?sharedUserId=balurathinam79

I'd be interested in hearing how others have approached similar scheduling challenges in SQL Server environments. Have you experimented with on-demand execution patterns, or do you primarily rely on scheduled polling?

I can run a sql query to get the accurate results but the dbatools doesn't return all jobs, have not dug deep into why its missing most jobs just thought that was odd. Any insights as to why its misssing most job data? thanks

After applying SQL Server 2022 CU 26 to our servers several are producing stack dumps during database restores.

Am I the only one?

AWS is deprecating the 1.x drivers. When we have configured the 2.x version for testing, we keep getting the error "no password supplied" even though the password is definitely in the setup and when we test from there, it works. A command like "SELECT * FROM OPENQUERY (REDSHIFTODBC, 'SELECT * FROM table')" returns the error. We're stumped. Any ideas?

Hola a todos, Quiero implementar una instancia de producción de Microsoft SQL Server en un VPS Linux (Ubuntu). Para evitar un único punto de fallo y la dependencia de un proveedor, quiero alojar la base de datos principal con un único proveedor de VPS, pero almacenar las copias de seguridad automatizadas completamente fuera de su ecosistema; idealmente, en un VPS o almacenamiento de objetos alojado por una empresa completamente diferente. Mi objetivo es asegurar que, si mi proveedor principal de VPS quiebra, sufre una interrupción importante o bloquea mi cuenta, no perderé mis datos y podré restaurar los servicios rápidamente en otro lugar. Para quienes hayan implementado una infraestructura de copias de seguridad multivendedor similar, me gustaría recibir algunos consejos: 1. **Capa de transporte:** ¿Cuál es la forma más fiable de enviar los archivos .bak de forma segura entre proveedores? (Por ejemplo, ¿tareas programadas automatizadas con rclone, configuración de un túnel VPN WireGuard ligero entre los nodos o uso de scripts SSH/SFTP seguros?) 2. **Cifrado:** Dado que las copias de seguridad se transferirán a través de redes públicas y se almacenarán en el disco de un proveedor diferente, ¿cuál es la forma más segura de gestionar el cifrado? ¿Debería confiar en el cifrado de copias de seguridad nativo de MSSQL o cifrar los archivos a nivel del sistema operativo (como gpg o 7z) antes de enviarlos?

1. **Gestión de copias de seguridad:** ¿Qué herramientas ligeras recomienda para automatizar la política de retención en el VPS de copias de seguridad (eliminar archivos con más de X días de antigüedad, comprobar la integridad de los archivos, etc.) sin generar demasiada sobrecarga? Si ya ha implementado esto, ¿cómo es su arquitectura y qué problemas o cuellos de botella de rendimiento debería tener en cuenta (como límites de ancho de banda o picos de CPU durante la compresión)?

¡Gracias de antemano por sus aportaciones!

I am working on a migration project to move away from SQL agent to autosys to run the SSIS packages (2019) through DTexec utility. I am using 64 bit dtexec for running the packages. Now I am facing issue running the packages as they keep failing without any logs. The packages won't even start running and command will try to start for sometime and then the autosys cmd would fail after a few minutes.

Interesting thing is that it is running fine in the UAT but it is failing in the PROD. Even more interesting is sometimes the job will run perfectly fine and sometimes it would fail.

what could be the reason for these failings? How can I resolve this issue?

Hey folks, happy Friday!

This is a feedback item that exists both on the SQL feedback site and on the SSMS feedback site.

On the feedback item on the SSMS site, I added a few images with example mockups for how the instance name could show up. Please visit the feedback item and comment with what you prefer, thanks!

p.s. I know, there are a lot of feedback items related to Query Store reports in SSMS. I'm starting simple...please grace me a bit here.

First, it's about a SQL Server 2016

I'm designing a SQL Server high availability architecture and I'm trying to understand whether this configuration is supported with SQL Server Standard Edition.

The proposed architecture is:

Node 1 + Node 2: SQL Server Failover Cluster Instance (FCI) with shared storage (which I would have to configure AG as well) and this cluster will replicate to a Node 3 + Node 4: SQL standard configured as a Always On Availability Group in other datacenter. (in this case, I belive a can only have the node 3 in the secondary site, right? node 4 isn't supported since standard edition support only 2 AG)

My idea is for the FCI to host the primary database, and then configure an Availability Group so that the databases are replicated to the secondary datacenter.

My questions are:

Can a Failover Cluster Instance (FCI) participate in an Availability Group when using SQL Server Standard Edition?

What I'm not clear about is how SQL Server Standard counts replicas in this scenario. I know that Basic Availability Groups in Standard Edition are limited to two replicas (one primary and one secondary). However, my primary server is a 2-node Failover Cluster Instance (FCI) running on Windows Server Failover Clustering (WSFC). Since the FCI itself already spans two Windows nodes, I'm confused about how this interacts with the Basic AG limitation. Does SQL Server treat the entire FCI as a single Availability Group replica, allowing me to have:

Primary replica = the 2-node FCI

Secondary replica = one standalone SQL Server instance in the DR datacenter (I can't have a node 4 in the secondary site, right?)

Or does the fact that the FCI already has two WSFC nodes mean I've effectively reached the Standard Edition limit for Basic Availability Groups?

Or is this entire architecture only supported with Enterprise Edition?

Hi all, this is Burak. I have built an open-source CLI tool that allows replicating data from Postgres CDC changelog into 20+ destinations: https://github.com/bruin-data/ingestr

The overall idea is that:

• You have your prod postgres DB
• You want to replicate them to analytical databases for analytics purposes, e.g. to Snowflake, BigQuery, Databricks, or Redshift
• You have two ways:
  • You can either run a batch load using tools like ingestr, Airbyte, or Fivetran
  • If you cannot run batch workloads for some reason, e.g. due to the latency requirements, or not having proper cursor columns, you need to run CDC replication using tools like Debezium and Kafka

The problem with CDC using those tools is that they require a buy-in into their ecosystem, which is generally quite invasive, such as being able to run Debezium only with Kafka reliably, or having to deal with their Java client libraries if you ever wanted to integrate them elsewhere, tolerate their high resource requirements, etc.

I never liked running them on production. We have been working on ingestr for quite some time already for batch sources, and CDC became an obvious target.

ingestr has quite a few niceties:

• You don't need any extra services or tooling to run it: just put your credentials in the URI, and you are good to go.
• It is a simple and fast Go binary that runs anywhere, even in your GitHub Actions pipeline.
• It supports both batch and streaming modes in the same binary, which allows changing the deployment modes as your requirements grow. Run locally, deploy on Airflow, or put it in an EC2 server in a streaming mode if you want to.

It is open-source, and you can run it anywhere you like.

It supports:

• PostgreSQL CDC
• MySQL CDC
• SQL Server CDC
• SQL Server Change Tracking
• MongoDB CDC

Give it a try and let me know if you have any questions!

My current organization couple of years ago migrated from SQL server to snowflake and I noticed in some of the documentation, they have mentioned SQL Server as legacy technology.

I started working in SQL server some 20 years ago and some of the developers in our team are cloud first developers and they find it so difficult to navigate SQL server management studio and they ask me questions like is there timetravel feature in SQL server and can we see what data was there 10 mins back. I was totally dumbfounded when I heard that question, because I had never come across such a feature. Apparently snowflake has this. So I am curious is SQL server now really considered as legacy technology?

Hello

There is always change and so I must expand my knowledge deeper onto MS SQL too. I can run some basic SQL and know how to log onto our server.

But in the very near future I need to care take more in depth about our sql servers administration. Setup, users, permissions, replication.

I tried asking Google but got bombed with too much random SQL stuff: Where do I start best to make sure I get to know my stuff? Is there a version available I can install at home for education?

Thank you^^

I'm troubleshooting a script that dynamically builds a list of databases and a list of user accounts in those databases, and then uses them to generate a bunch of REVOKE CONNECT FROM [<username>]; commands to be executed by sp_executesql. At this point, I'm sending the commands to the screen instead of executing them and this is where I first noticed the oddness. There are a bit more than 4000 commands. If I write them to the screen with Results to Grid, it takes almost 44 minutes to finish. If I run the exact same script with Results to text, it completes in just over 20 seconds. I'm connecting to SQL Server 2019 and have seen this behavior in both SSMS v19.0.1 and SSMS v20.2.1. Is this expected behavior that I've just never noticed before? Why is there such a drastistic difference between grid vs text?

Problem: After failing over my SQL Availability Group primary to a different node(replica) the application works fine, but the reports no longer work

Back Story:

When we set up the new Microsoft Dynamics 365 environment the administrator could not get a new deployment created while using the SQL Server Listener Name, so he had to use the server name. The idea was to go back into the settings after the deployment finishes and change the server name to the listener name.

After updating the listener name in the deployment the application works fine after a failover from one replica to another, but the reports stop working as if the server name is hard coded into the application someplace. There is something not using the listener name and we cannot figure out where

What we have done:

On the application server we have updated the server settings in the Microsoft Dynamics 365 deployment, we have searched and updated every config file that we could find, I searched the registry and all settings point to the listener.

What I have found

So I looked at the SQL Server Reporting Service (SSRS) and in report manager and I see the data source that has all the current reports as dependencies. That data source is pointing back to the application for the connection string. Unsure of the exact details but the data source has

type = Microsoft Dynamics 365

Connection string - MSCRM_CONFIG...

What leads me to believe is that the SSRS data source is pulling the connection string to connect to SQL server from the application. But where is that string information in the application

My question:

Where in the application would I find the connection string for the SSRS data source

What I am thinking to do:

I am thinking to change the data source settings to using

Type = Microsoft SQL Server

Connection String = ...listenername...

I am hoping that modifying the data source connection information will allow the reporting to work even after failover

I also thought that maybe the connection string information might be in the actual MSCRM_CONFIG database, so I will be looking there

What I do not want to do:

I do not want to create another deployment using the listener name - it likely will not work

I am not a fan of just updating the data source, but will if I need to

Specifications:

Microsoft Dynamics 365 Application Version 9.3

SQL Server 2022 three node Availability Group on Windows 2022

Please, any feedback is appreciated.

I don't have backups of this database. Is there maybe any autologging that could have saved the SP somewhere? Running SQL Express 2022 on my laptop

I don’t know if this is the right place, but if anyone can help. Much appreciation.

Context:
41M 15 YOE clinical side of healthcare. I am trying to find out what skills or set of skills alongside SQL that make me the most valuable in the market in a healthcare environment.

The full stack I was thinking was SQL, dbt, snowflake, power BI and Python. I know some basics for some of these but I not a pro at all.

Question is: what are the best combos for high earning careers along with domain expertise. I don’t want to leave healthcare just want to move to the tech side. The more interesting side IMO. What stack should I learn to achieve this result and where would I start? All the info on the internet is so overwhelming and AI “Claude” just sends me in circles.

Any advice is much appreciated.

Once again if this is not the right place I apologize.

Hi everyone,

I'm trying to understand SQL Server licensing for a failover cluster.

Suppose I have a SQL Server Failover Cluster Instance (FCI) running on a Windows Server Failover Cluster (WSFC) with two nodes:

• Node A is active.
• Node B is passive and only takes over if Node A fails.

In this scenario, does the passive node require its own SQL Server license, or is licensing only the active node sufficient?

Also, does the answer differ between SQL Server Standard and Enterprise, or depending on whether Software Assurance is included?

Had a fun TDE implementation tonight, was helping a colleague actually as they were nervous of the process but I had done something similar, but separate (one mirror set up and one log shipping one, never combined)

The setup:

• Primary Server
• Secondary server where db's mirrored to
• DR server where the same db's log shipped to

(don't ask, it was there when I joined this team, and we will fix it with AAG or RSAG when we migrate from 2016 where it is on now).

This was the plan of attack we took:

1. On all three servers we created the server master key in master (no, we did not back it up, and yes, we used the same long random password for all)
2. We created the certificate on the primary in master and backed it up to a local TDE folder immediately (remember the password you back up with).
3. ***THE IMPORTANT PART**\* Copy and restore the certificate from the primary server to the secondary and DR. The reason to do this is if the mirror or log shipping destinations get transactions that are encrypted, yet the cert does not exist at the destination, it will potentially break the two. I haven't tested recovery process without reinitialization, such as going "oops, and try to copy the cert after the fact", but I also want to sleep and not fix a broken process.
4. Verify that the same cert is on all three servers by running: select \ from master.sys.certificates. The most important column is not the name. *It is the encryptor thumbprint.** They need to all match each other; you can call the cert itself whatever you like when you restore, but the thumbprint is what matters. Once this step is complete, the fun begins.
5. Take a backup of each database, either full or diff, before encryption (or if you're okay with your backup chain, transaction log backup is fine as well).
6. Per Database, create the database encryption key and turn on encryption. I start it on the single smallest database first to make sure if anything does go wrong, I can fix it without worry. Then I continue to the rest of the DB's when happy after it completes.
7. Once encryption is turned on, I use a variation of this script from SQL Shack and query the primary, mirror secondary, and dr log ship.. The primary and mirror will be almost identical in status of encryption state, but you will NOT see the percent complete on the mirror (or log ship DR later). When you run the query on the log ship DR, you will see nothing being encrypted at all, until the first log backup after you turn on encryption is made, copied, and restored per your normal schedule.
8. ***DO NOT PANIC**\* When you check the ERRORLOG of the log shipping DR if the encryption process will span more than 1 log backup, you will likely see a message about encryption verification being aborted, but the restore of the log completes without issue. It is not marked as an error, just an info message. I got worried the first time I saw this. After encryption is complete, the final backup and restore will show that message disappear.
9. For some housekeeping, ensure that your backups have COMPRESSION turned on. If you are using SQL Server 2019 lower than CU5, you need to use an approprimate MAXTRANSFERSIZE option in backing up. CU5 and higher versions of sql, don't need this, only the COMPRESSION option. Log shipping backups (as they are run from the OS) seemed to have compressed backups automatically when we checked the backup sizes. Details here.

That's pretty much it. This process also is similar to AAG. Make sure you run step 1-3 on all secondaries and DR's of the AAG. Same idea so when an encrypted transaction flows, it can be decrypted properly and not break. And as always, test this in a non production environment first to be safe. Good luck everyone!

Posted here a couple weeks back about safe-migrate, the migration linter that simulates your migration against a schema model instead of pattern-matching SQL. Figured since people were actually trying it, I owed it a real look.

15 bugs confirmed. Some were embarrassing — now() was getting flagged as a table-rewrite trigger because nobody told the expression analyzer it was STABLE. Totally safe migrations getting false HALTs. Others were scarier: the function-dependency rule was supposed to catch you dropping a function that a trigger depends on, and it was just silent. Function ID mismatch, never fired, no error, no warning. Worst kind of bug for a safety tool not a crash, just quietly wrong.

While fixing that batch I introduced a new one. DROP SCHEMA CASCADE runs, and the state simulator wasn't cleaning up the trigger and publication graph edges for the cascaded objects.So the in-memory schema still thought a trigger existed two statements after it was gone. Took hours to pin down, the symptom (a stale false positive later in the file) was nowhere near the cause. Mental note: when you cascade-drop things, you have to tell the graph too.

What's new in v0.4.0:

• 11 new rules — overbroad-grant, broken-compute, drop-database, schema-drift, irreversible-migration, chain-conflict, restrictive-policy, disable-trigger, partition-strategy-mismatch, alter-type-add-value, and conflict-rename-chain
• Confidence restores after ROLLBACK — a rolled-back DO block used to permanently taint the rest of the run, making everything look riskier than it was
• Multi-file chain linting — lint-chain --dir with state persisting across your migration directory
• Redesigned output — every finding now has object/reason/recipe/sql, plus four verdicts instead of two: HALT /CAUTIOUS / SAFE WITH RISK / SAFE. The "SAFE WITH RISK" tier is useful: it fires when your table stats show an operation could block, but there's no certainty. Regex linters can't do that.
• 235 tests, up from 185

Still does the same thing: sync reads your table sizes and stats from the catalog (no app data, just SELECT on pg_class/pg_attribute), then lint checks your migration against actual table sizes instead of guessing from SQL shape.

Repo: https://github.com/dsecurity49/safe-migrate

Hey folks! This week for a rather late Friday Feedback, I'm interested in your thoughts about Automatic Plan Correction. There are only a handful of feedback items on the site (aka.ms/sqlfeedback) but based on discussions with some customers, I think it's an area where we are due for some improvements. What are folks struggling with when it comes to using APRC?

Hi folks - I'm Drew, a PM at Microsoft involved in a few fun things including the SQL formatter in SSMS (and soon other places).

We're currently working on expanding the formatting options - and are currently tackling the addition of tabs instead of spaces. If you're looking to format SQL with tabs, which style do you prefer:

1️⃣ Go all-in on tabs like this:

2️⃣ Or indent with tabs and align with spaces like this:

... since this is reddit, feel free to comment that formatting with tabs is silly (team all-spaces here), or that both of these options are wrong and that it should be indented with tabs and aligned with artisinally-crafted non-breaking spaces.... as long as you're having fun.

Happy Friday ya'll.

Here's a list of key items to vote/comment on, if you're so inclined:

- Leading/trailing commas (https://developercommunity.visualstudio.com/t/SQL-Formatter-lacks-configurable-option-/11105147)
- Allow column alias with "=" (https://developercommunity.visualstudio.com/t/SQL-Formatter:-add-option-to-preserve-co/11105065)
- Indent with tabs (https://developercommunity.visualstudio.com/t/SQL-Formatter-lacks-option-to-indent-wit/11105064)
- Enclose object names with brackets (https://developercommunity.visualstudio.com/t/SQL-Formatter-reformats-leading-commas-t/11106703)
- JOIN / ON in the same line (https://developercommunity.visualstudio.com/t/SQL-Formatter-forces-newline-after-JOIN-/11108118)
- stored procedure parameters on individual lines (https://developercommunity.visualstudio.com/t/SQL-Formatter-collapses-ALTER-PROCEDURE-/11105063)

We released mssql-python 1.11.0 today.

This is mostly a bug-fix release, but the fixes are the kind people actually feel in production:

• fixed transaction semantics for with connection: blocks
• fixed Apple Silicon import issues on clean macOS machines
• fixed NULL BINARY / VARBINARY parameter binding edge cases
• fixed hangs in SSH-tunnel-style or in-process forwarding setups
• fixed a bulk copy freeze with ActiveDirectoryServicePrincipal

So this is less "new feature drop" and more "removes a bunch of annoying failure modes."

Upgrade:

pip install --upgrade mssql-python

Release notes: mssql-python v1.11.0

Full blog post: mssql-python 1.11.0: Fixes for transaction semantics, Apple Silicon imports, and bulk copy

Grab the latest and give it a try.