If you don't have access to dual-port RAMs, you can also make it so only either the display-driving circuitry or the CPU can access the RAM at a time. There are quite large vertical/horizontal blanking intervals in which you can let the CPU draw into the memory.
Another alternative would be double-buffering, where you'd have two entire framebuffers. One can be accessed by the CPU without the display-driver getting in the way, just 'swap' the two buffers when you're done drawing (ideally during the blanking interval). This also requires single-port RAM, just twice the amount. It may be worth it for the extra performance.
I'm not sure if you want to do color or not, just know that you need 480 kbits of memory per bit of color depth. Also, you'd need a fairly fast RAM. When you look at these VGA timing tables, you'd see you'd have to be spitting out the pixels at 36 MHz, meaning you'd have 27.78 ns for the entire read cycle. You could also try pixel doubling (quadrupling, octupling) to reduce both the capacity and speed requirements for the RAM.
As an aside, the old 486 motherboards often had quite fast SRAM cache chips socketed on board in ~256kB capacity, which should be enough for 4 bits per color! Of course you can then use more of those to get to 8 or more bpc. You could recycle those if you have such things still lying around unused.
If you end up going with just one of those, you'd also need a mux then on the output as you'd be storing two 4-bit words per byte, and at that point you'd be clocking the RAM at half the pixel rate (~18 MHz, 55.56 ns). Finding muxes that are fast enough (still the 36 MHz) should not be as difficult.
