The magic comes from hardware more than software, as others have mentioned you want a good burner to do the rips.
Sorry for the rabbit hole, but media archival is its own can of worms that would take hours to dive into, so in general you want an Asus DVD drive that's internal, manufactured between 2004 and 2010, supports 52x CD speeds, doesn't have light scribe, and has the DVD-RW logo on the front bezel. Those are usually the magic drives, and you are generally going to need modified firmware to do some of the more bizarre reads. The Asus BW-16D1HT does Blu-ray and multi region discs, for just DVDs look for one of the DRW-**** drives without light scribe (they have a laser assembly that is higher power to write with, but usually doesn't have as clean of a signal in exchange, not the best for rips)
From there the software depends on your specific workflow. If you are just trying to recover clean disc rips then dd or ddrescue are going to be your friend. Get a disc image, mount it, use the data from the image instead of the disc. For archiving just the media look into MakeMKV, FFMPEG, and Handbrake for ways to encode the DVDs and audio into friendlier formats. Ffmpeg specifically can be scripted very easily and does audio just as well as video, it is the gold standard for transcoding media.