March 6, 2020 at 9:56 pm
In almost every place I've worked, I've seen major applications purchased without even asking anyone in IT, their opinion.
March 7, 2020 at 2:01 pm
Well, I've been hearing this debate raging for many years now. But just imagine the rage if MSFT announced that the next version of SQL Server would have no ability for stored procedures.
I'm guessing that most of the opponents of stored procedures never experienced the lack of same, so do not have a sound basis for their opposition. Possibly it's even a control issue.
In my last position as a DBA for a company that had several very large custom in-house-developed applications and numerous SQL Server instances, we had literally hundreds of stored procedures per application and even had official positions within the DBA group that were SQL Developers who were focused on creating and testing stored procedures and enhancing performance of same. Embedded SQL code in front-ends was pretty much unheard of and taboo. Front-end developers focused on business logic and presentation and left data storage and manipulation to the DBA group.
I cannot imagine even having this discussion in that environment.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 7, 2020 at 2:41 pm
I guess my only exception to what Rick stated above is that business logic is frequently better done on the database side of the house. FKs, Defaults, Checks, and a whole lot more come into play especially when there's no front end involved, such as the processing of bulk data and the generation of reports. As with all else in SQL Server and data in general, "It Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2020 at 3:19 pm
I guess my only exception to what Rick stated above is that business logic is frequently better done on the database side of the house. FKs, Defaults, Checks, and a whole lot more come into play especially when there's no front end involved, such as the processing of bulk data and the generation of reports. As with all else in SQL Server and data in general, "It Depends".
100% agreement on that. I guess my thought was not well expressed! Sort of thinking of 'business logic' as the formatting, presentation, and collection of the data, and guiding the user through the same. I definitely believe validation and constraints belong in the database, along with most of the data manipulation.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 7, 2020 at 7:13 pm
It seems kind of obvious that relational constraints belong in the relational database.
March 7, 2020 at 7:34 pm
I can tell you that if you migrate to a DB platform with no stored procs or equivalent your world becomes one of pain.
Instead of nicely orchestrated work in the DB you end up with some form of client app having to trigger queries, determine if they were successful then choosing the appropriate next step. If it breaks was it the client app? A network glitch? Something else entirely?
You'll be vulnerable to"religious wars" about whether PowerShell/Bash/Python/Ruby should be used. You'll have moved off SQLServer do will have to find an equivalent of Sqlserveragent, SSIS, possibly all your 3rd party tools.
Much as I like StackOverflow I prefer this one
March 7, 2020 at 11:11 pm
I can tell you that if you migrate to a DB platform with no stored procs or equivalent your world becomes one of pain.
I believe I began working with SQL Server back in 1999, and I can assure you I would never move to a platform without sprocs. It's all I know how to do now. Of course, nobody tells me what I have to do any more, except my wife, but at the same time, nobody ask me what I think about it either, including my wife! I'm turning 77 in three days.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 8, 2020 at 12:46 pm
I find using stored procedures with my web-based C#/JavaScript projects to be really helpful as when debugging I will create a C# page with parameters that calls the stored procedure and handles the response. Then I can use the web browser console to see what URL was called (e.g. /Student/AddToCourse/1234/ABC123) and can also capture the call to the stored procedure in Query Profiler (e.g. sp_StudentAddToCourse(1234, "ABC123") and I can then call this stored procedure passing the same parameters in SSMS and view and debug it there.
I find this approach much easier to debugging complex SQL from within C#/php. Also Microsoft keep changing the LINQ language which led me to have to re-write a lot of my LINQ code so now I mostly just call stored procedures for everything and this keeps things consistent for the rest of the team.
Another reason is that often the systems are interfacing with various linked servers but I can place all the stored procedures in one place. No matter whether it is a C# project or an SQL Agent job everything is achieved with stored procedures. This also allows for scheduled jobs that can also be invoked manually if required from a web interface.
March 8, 2020 at 8:54 pm
Steve Jones - SSC Editor wrote:jarick 15608 wrote:...
I'd expect more for a commercial application but ...
I'd expect more from people buying commercial applications.
In almost every place I've worked, I've seen major applications purchased without even asking anyone in IT, their opinion.
@jarick-15608 : I agree. And, to add to your point, sometimes we need to knowingly take the bad with the good if we want the software. For example, two extremely popular packages out there -- WordPress (which SQL Server Central now runs on) and Atlassian Jira -- both have serious issues with their data models (and maybe other areas):
NTEXT and IMAGE datatypes instead of NVARCHAR(MAX) and VARBINARY(MAX), respectively.Latin1_General_CI_AI and SQL_Latin1_General_CP437_CI_AI collations for the Jira database, yet this is entirely arbitrary, makes no sense at all, and causes much frustration for SQL Server DBAs, especially if someone stumbles upon the infrequent code path that joins on a string column in a temp table, hence causing a collation mismatch error when the instance collation does not match the Jira DB collation since the creation of the temp table does not include the COLLATE DATABASE_DEFAULT clause (and I was told that it would be "too difficult" to add it to the embedded SQL since "the devs would have to change the code" -- though to be fair, they support several RDBMS platforms so moving to stored procs would present its own problems).NVARCHAR columns -- I am looking into this as well).Knowing all of this, and that there might even be additional concerns, such as security (I noticed that the SQL Server port of WordPress is doing SET IDENTITY INSERT which requires being dbo or the table owner, and not sure how they are dealing with that), I still use both.
Take care,
Solomon....
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 9, 2020 at 12:19 pm
It seems so many vendor applications just demand dbo access to the database, if not SA rights to the instance.
Had that one. The expression when you tell the company MD or Technical Director (a lot of them are small companies) a flat "No." to their request for 'sa' permissions is great. I am willing to give them db_owner on their own database, if the code they provide kills that products database I know it isn't my problem, and I have backups!
I have had one application that was written so badly that it had to run with 'sa' and wouldn't work otherwise, I told the department to buy their own SQL server as it wasn't running on ours. Luckily the head of BI backed me on that one (but he's an ex DBA/Dev himself and understood the implications).
We even had one company who needed Indexes explaining to them, they simply had no idea that they needed them.
March 9, 2020 at 12:20 pm
For people dismissing the security aspects of stored procedures -vs- direct table access through an ORM....
What are you going to do when some semi-technically savvy user opens up a copy of MS Access, and decides that they can just update values without the application's rules, just to get something done quickly? This is the nightmare scenario that keeps DBAs up at night.
Also, another bit of advice I was given when I first started my career and was deciding if I was going to focus on VB3 or SQL Server 4.21... the mentor said "A fast database can go a long way toward making up for a slower front end, but there's nothing that the fastest GUI can do to fix a slow database".
Points that other's have mentioned:
March 9, 2020 at 12:51 pm
wait until you get an application that wants to create sql agent jobs and then delete them after. not a hope in hell stopping that process running as SA
we ended up putting database triggers on to make sure that account was not being missued
MVDBA
March 9, 2020 at 2:19 pm
wait until you get an application that wants to create sql agent jobs and then delete them after. not a hope in hell stopping that process running as SA
we ended up putting database triggers on to make sure that account was not being missued
Heh... yeah... that's just not going to happen on my watch. At the very worst, there will be a properly written stored procedure that very carefully creates such a job (carefully means highly constrained to not allow any "creativity" by the app) that I'll give the app EXECUTE privs on, but that's about it. You also don't need to every grant such processes SA privs. MSDB has it's own special privs but I'm just as adamant about minimal privs there, as well.
My favorite project of all time used all stored procedures. It was my favorite because the only privs the app needed was CONNECT and EXECUTE on certain stored procedures.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply