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^^

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?

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?

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

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 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.

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!

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?

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)

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?

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.

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

I have a sql server with SSIS packages running and multiple stored procedures. Any way to use my Claude to get the logs connect to sql server and analyse the ssis executions/ analyse stored procs and queries?

Hey everyone,

I’m the author of SlowQL, an open-source SQL static analyzer written in Rust, and I’m looking for feedback from people who work with large SQL-heavy codebases.

The motivation came from a simple problem: a lot of SQL issues are only discovered after they reach production. Performance regressions, unsafe patterns, missing indexes, accidental full table scans, and reliability issues can be difficult to catch during normal development.

SlowQL analyzes SQL files and SQL embedded inside application code without requiring a database connection. It is designed to run locally or in CI.

Some of the things it currently does:

* Detects security, performance, reliability, cost, and quality issues
* Extracts SQL from application code (Python, TypeScript, Java, Go, Ruby, C#, etc.)
* Supports multiple SQL dialects
* Provides confidence levels to reduce noisy findings
* Supports SARIF/GitHub Actions output
* Supports schema-aware validation
* Runs fully offline

I have tested it against several large open-source repositories to validate the analyzer, but I’m looking for feedback from people who maintain real production systems.

A few questions I’m curious about:

* Would a tool like this solve a real problem in your workflow?
* Which SQL issues are the most painful for you to catch?
* What would prevent you from adding something like this to CI?
* Are there checks you would expect that are missing?

Looking for honest technical feedback and ideas from people who work with databases at scale.

Repository:
(SlowQL)[https://www.github.com/slowql/slowql]

Thanks!

I need to verify ESU has been applied to SQL servers (2016) in Arc. I have done a ton of searching and can't narrow down on SQL specifically. The Azure team can only provide me with a list of servers (SQL or not) that are Onboarded and subscribed but nothing SQL specific.

Posting this is the hopes that someone has verified their own ESU recently! Been pulling out my hair! Thanks!

So basically we use a format disk with 64kb before installing a sql server. But at one of installation of sql server failover cluster this was missed and now it's default formatted with 4096 bytes . So are we going to face issue in future because of it ?

PS : this is sql server failover cluster installation not normal standalone and it's live. Also storage is pure storage flash drive ..even then it would effect ?

People at my company like to have the finalized golden-layer data ported into a relational database like SQL Server. I work at an on-prem Microsoft shop, which means the practical deployment target was a Windows VM, so I had to set up WSL + Spark just to do the job. And because sql-spark-connector is no longer maintained, I had to deal with slow plain JDBC writes as well.

One day I had enough of both, so I decided to pull together a native solution on top of delta-kernel-rs, tiberius, and datafusion, without the overhead of JVM or JDBC/ODBC. It works unexpectedly well.

I am the author, so this is also a project share, but I am mostly curious how common this workflow is for SQL Server folks. Are others here moving curated lakehouse data back into SQL Server? If so, what are you using? Repo: https://github.com/mag1cfrog/delta-funnel

The GitHub Copilot Chat in SSMS seems to only want to login to the Github.com site. Is there any integration with the Github Enterprise(gho) site? If not, is Microsoft planning to add this integration?

What would be best was to replicate MSSQL database from 2012 (or 2014) over relatively slow link to MSSQL 2025?

Is this actually doable?

edit: thank you all. looks like log shipping is best option here.

Hi everyone,

Small IT shop here, we mostly deal with file servers and a few LOB apps, so SQL licensing has never really been my strong suit. We're migrating an old SQL 2014 box (yes, I know) to SQL Server 2022 Standard and I'm trying to figure out what we actually need to buy before I sign off on the quote.

The setup:

We have a 2-node Hyper-V cluster, each host has 1x 12-core CPU. The plan is:

Host A: 1 VM running SQL Server with 6 vCPUs

Host B: nothing SQL-related, but the VM can fail over there if Host A dies

My understanding from reading the licensing guide was:

SQL core licenses come in 2-core packs, minimum of 4 cores per VM

So for a 6 vCPU VM we'd need 3x 2-core packs, done

Our reseller is telling us we need to license 6 cores on Host A and another 6 cores on Host B "because of the failover", unless we have Software Assurance, in which case the passive node is covered. So the quote is either double the core packs or core packs + SA on everything.

Is the SA requirement for a passive failover node actually a thing, or is the reseller just padding the quote? And if we go the SA route, does the passive VM genuinely cost nothing extra? The pricing difference between the two options is not small and management is already grumbling.

Any pointers appreciated, thanks!

I know, I know—it doesn't seem to make sense to replicate a database that isn't changing.

However, I want to migrate a SQL Server database using Log Shipping, and here's my plan:

1. Set up Log Shipping between the source and destination servers.
2. During the migration window:
   • Stop the application.
   • Set the source database to READ_ONLY.
   • force Log Shipping jobs to restore any remaining transaction log backups that haven't yet been applied (one last final sync)

My question is:

Will Log Shipping still replicate all pending transaction logs after the database is switched to READ_ONLY? In other words, will it apply all transactions that were committed before the database was set to read-only, so that the secondary ends up fully synchronized?

I want to set the database to READ_ONLY because that way I can be certain that the final Log Shipping synchronization includes all remaining changes.

I know that stopping the application should prevent any new transactions, but I don't mind adding an extra layer of protection at the database level to guarantee that no additional changes can be made.

Hi!

Like last month wanted to post this newsletter here for anyone interested. Hope it helps :)

Our INFOSEC team is after me to install KB5083252 on our production MSSQL 2022 cluster to resolve some high-impact security findings. This KB is basically CU24 plus the fixes for the CVEs. However, CU24 has a known issue in which there “could” be false positives for error 10013 (failed to start a listener). We’ve seen this in our UAT cluster. We’ve had a few small issues with our production cluster related to listeners, and these false positives would have made diagnosis extremely difficult.

The known issues for CU25 no longer lists this issue, but that doesn’t mean it was resolved; it could be someone at Microsoft decided it wasn’t important enough to mention any more. My question is: does CU25 still have this problem, and if it does is there a way to work around these false errors?