You don't mention whether this is a single server SharePoint installation or whether the SQL Server lives on another machine.
The reason why this is important is because you can save a lot of time by simply building up a new server, installing SharePoint to the same patch level as your original server, and connecting the new one to your existing server farm. Then run SharePoint Configuration Wizard on the old server to disconnect it from the existing SharePoint config, and finally amend your DNS entries.
If you are able to do this it will save a lot of pain!
Not to be too controversial, but I think you're making some very large assumptions with your question. Let's start with farm configuration.
In my experience, I don't think I've seen any one configuration that's more common than another with one notable exception: "development" servers. Devs tend to lump everything into one VM (or one box), simply because it's easiest to prototype. This is done without any real eye towards expected load or farm purpose -- it's a convenience thing.
When it comes to production farm configuration, though, the purpose and expected load on the farm should drive its configuration. This is the whole basis behind tools like Microsoft's System Center Capacity Planner (SCCP) (http://www.microsoft.com/systemcenter/en/us/capacity-planner.aspx - free download). You plug in your expected user load and what the farm will be doing, and you get a baseline recommendation out. It's not a one-size fits all, but it provides a solid starting point for further planning, customization, and tuning.
Though user load tends to be one of the bigger drivers in "number of boxes" to throw in a farm, the intended purpose of the farm is going to drive a number of configuration and tuning aspects. You asked about "better performance" in your question, but you need to answer this question: what is the farm going to be used for?
To use two disparate examples, performance tuning suggestions will vary significantly based on whether a farm will be used for collaboration (the classic "team sites" scenario) or publishing (that is, an Internet-exposed "banner site"). At a high level, optimizations for the former are going to drive towards maximizing R/W performance, while tuning for the latter will focus on maximizing caching performance and minimizing response time.
Though there are some general planning and performance tips that are relevant in most farm scenarios (for example, leveraging 64-bit hardware and software: http://technet.microsoft.com/en-us/library/dd630764.aspx), I would encourage you to start by nailing down two parameters:
- Expected user load (both average and peak)
- Intended farm purpose (collaboration, publishing, hybrid, etc.)
Once you know those two things, you're in a position to begin estimating your farm size/configuration; you'll also have an idea of where to focus some tuning efforts.
Good luck!
Best Answer
Yes, you can use multiple SQL Server database instances to host a single farm's databases. I've seen customers who separate out content databases (collab sites, MySites) to an instance separate from the one hosting their config database, SSP databases, etc.
You can even split content databases across instances as well.
It basically comes down to specifying the proper server and instance name when creating the database.
John