No, they are not returned in a deterministic order.
While you are unlikely to see them returned in a different order, that doesn't make the current behavior deterministic or reliable. Tricks like applying ROW_NUMBER() OVER (ORDER BY (SELECT 1)) to the output in an intermediate place like a CTE are similarly not guaranteed to work.
It's one of the features ...
The issue is that JSON_VALUE returns an NVARCHAR and you sent in an INTEGER. This causes a Convert_Implicit.
If you pass in a VARCHAR or NVARCHAR param/value into the WHERE condition, it does a seek and drops the rows read.
what are the actual benefits of doing so on the above described architecture (if any)?
There are some management benefits, particularly:
You get separate disk performance counters in the VM for Data, Log, and TempDb
You can migrate any one of them to a different storage device without reconfiguring the VM or SQL Server.
You can manage Storage Quality of ...
Originally the benefits were increased I/O performance by separating concerns across multiple physical disks. This is because concurrent changes can be (and typically will be) occuring to your data file, log file, and even TempDB file. The disk is the slowest hardware component that is used in a SQL Server environment, so optimizing it is important.
As disk ...
It turns out, the process was working normally, but I was missing a step at the end. Once I had configured the availability group, I didn't know that I then had to connect to the remote server and add the database. In the tutorials I had found online, none had included that step. It was a small but significant item.
Once I added the database on the remote ...
This seems to be a solution for a COUNT(*) OVER () like so:
;WITH c AS
SELECT TOP(@limit + 1)
p.ACOLUMN, p.ANOTHERCOLUMN, p.LATITUDE, p.LONGITUDE,
NumRows = COUNT(*) OVER ()
FROM table p
where p.LATITUDE < @latMax)
and p.LATITUDE > @latMin)
and p.LONGITUDE < @lngMax)
and p.LONGITUDE > @lngMin)
I don't believe the behavior has changed between SQL 2016 and SQL 2017. It may be that the SQL 2016 database was TRUSTWORTHY whereas the SQL 2017 database is not or has a database owner sid mismatch. The proper solution is module signing as Rusanu describes. Also see the Activation Procedures for Service Broker topic topic in the appendex of ...
Mirroring has been deprecated for a while - I wouldn't build out things with it at this point.
I think you may be better off spending time preventing issues that cause you to have to re-do your replication, but aside from that note I'd recommend looking at using Availability Groups instead of, or with, transactional replication.
For using with transactional ...
There are some newer DMVs designed specifically for troubleshooting what is happening in TEMPDB. Take a look at sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. See this link for a discussion of those DMSVs