So has anyone used SSDT to manage their DDL changes, and does it always generate a script to redeploy every single Table in the database?...or is the Vendor of my company's ERP system just dumb?
Yea I figured this one is more so on the ERP Vendor, not Microsoft lol.
I wouldn't mind, except they also love storing files in the database, which takes forever to upgrade, even though there's no schema changes.
@HannahVernon So your T-SQL is part of a project / solution / repo in Azure DevOps?...is it a database project or how do you go about managing the database in DevOps?
Interesting, yea I'd definitely read about it if you did. We use on-prem Azure DevOps too but the database layer is the only thing not managed, source controlled, or pipelined through it currently. Deploys are semi-manual unfortunately.
I'm starting to think more about DevOps for our process lately but just want to be careful not to get too stupid with how we implement it based on some of the poor implementations I've seen other people do and some of my past experiences with oddities in doing database projects in Visual Studio.
@J.D. Oh man, I have had that exact thing happen. I'm trying to remember what the specific problem was, but it was very tedious to track down. You basically have to compare the change script to what the table looked like at deploy time.
And you can't just look at the changes in the specific table. In one case, the difference was the ANSI_NULLS was different when the script was run vs. when the table was created previously.
@JohnK.N. that's unexpected behavior. When I get a chance and am in front of a PC will look into it.
@JoshDarnell it's important that client settings match exactly between your source control version and what's existing in the target database, otherwise sqlpackage.exe will rebuild stuff that makes you think you're doing it wrong.
@JoshDarnell Yea I was reading on that very thing yesterday. Apparently any different set options may cause such an issue. I'm guessing that's probably what I'm seeing with my Vendor's ERP system upgrades lol.
We had a developer who had set quoted identifier on in their ssms options for a while. So their code looked identical to what was in the database except it changed if another developer subsequently modified their code. Tracking that down was painful.
@HannahVernon How do you handle more complex cases like cross-database dependencies, complex features (e.g. replication), or things dependent on changes outside the database such as distributed transactions and enabling MSDTC on the server?
This might be the craziest logic I've witnessed on execution plans: > [...] different plans from different application servers, connected to a SQL Server on a NUMA box. What we see is, that on one application server we have a different access plan for the same SQL statement than for all the other application servers. It seems NUMA explains how two different plans can exists for the same statement, when the application servers are connected to different NUMA nodes.
Also, I just couldn't respond to that, but the first email that came through said, "Double check the SET options for the same query run from different app servers"
I type proc scroll down the suggestion list to create procedure, hit ENTER, add name and:
USE []
go
IF object_id('MyTestProc') IS NOT NULL
BEGIN
PRINT 'Dropping procedure MyTestProc'
DROP PROCEDURE [MyTestProc]
IF @@ERROR = 0 PRINT 'Procedure MyTestProc dropped'
END
go
CREATE PROCEDURE [MyTestProc]
/***********************************************************
* Procedure description:
* Date: 03.11.2022
* Author: John
*
* Changes
* Date Modified By Comments
************************************************************
*
************************************************************/
I'd pay for a SQLPrompt Core that didn't include all the extra crap, and just did the productivity stuff. They could work on finishing that aspect too.
You can create headers and stuff. Automate things. Fully cusotmisable. Connect to MySQL, PostgreSQL, SQL Server, Oracle Database. Intregrates into SSMS, TOAD for Oracle, .... Ultra Edit, UE Studio, ....
I tired ADS once, to query a database hosted on a MS platform. I had 50$ to spend on "free" Azure stuff. After a query took 5 minutes to return one result from a table with one record, ....
I'm running SQL Server 2022 RC1 setup on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether.
Setup is recommending MAXDOP 8:
But if you click on that link for configuring MAXDOP, the recommendations say:
Based on that KB article, MA...
"the people who write the installer and the people who write the docs are on separate teams who have no means through which they may communicate with each other, due to the precipitous fall of $MSFT$"
I have no idea but "Based on that KB article, MAXDOP should be 16" is wrong. Based on that article, it should be "at 16 or less". ;) — ypercubeᵀᴹ2 mins ago
And Israeli politics is such a crapshoot at the moment and Netanyahu is just sort of a scourge upon it. One can hope he has to step down when/if he's sentenced for his corruption charges.
@Zikato Am I high or is there no discrepancy? The rec is like: don't set it higher than 16... and the installer is recommending 8, which, last I checked, is less than 16.
As a someone who holds a degree in mathematics and routinely would hang around people who went on to receive doctorates in the field, I should have anticipated that.
I'm running SQL Server 2022 RC1 setup on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether.
Setup is recommending MAXDOP 8:
But if you click on that link for configuring MAXDOP, the recommendations say:
Based on that KB article, MA...
