Nanostructured silver films with different thicknesses were prepared by vapor deposition onto the surface of the anodic aluminum oxide (AAO) template to be used as surface-enhanced Raman scattering (SERS) active substrates. Both the peak position of the surface plasmon resonance (SPR) band and SERS enhancement of silvered AAO samples displayed non-monotonous dependence on Ag layer thickness. Using 441.6 nm excitation and a water-soluble cationic porphyrin, Cu(II)-tetrakis(4-N-methylpyridyl) (CuTMPyP4), as a SERS-reporting analyte, two maxima of the SERS enhancement were obtained for Ag layers of 15 and 120 nm thickness. Thickness dependencies have been analyzed taking into account the type of SPR modes identified by means of quasicrystalline approximation (QCA) of statistical theory of multiple scattering of waves and multi-Lorentzian deconvolution. The analysis revealed that SERS enhancement is related to the absolute magnitude of the distance between excitation wavelength and spectral position of collective SPR mode. It was shown that matching of excitation wavelength and the most intensive SPR modes with non-radiative decay, generated mainly by coherent interaction of higher-order plasmon resonant modes (quadrupole and octupole), plays a dominate role in SERS performance. Besides, it has been observed that more intense SERS signal can be obtained when the analyte deposited on the Ag/AAO substrate was excited through the AAO template rather than from the silvered side. Our results demonstrate that appropriate excitation geometry and fine-tuning of the optical properties of the Ag/AAO substrate by adjusting the thickness of the Ag layer with respect to particular excitation wavelength can contribute to more effective SERS enhancement.