How should I set up my database instances for multiple SP2010 installs?

Question

I'm setting up three SharePoint instances. We have a single database instance (DB\SP2010) that will hold all the databases for these three SP instances. Will this be overly confusing? Is there some way I can create prefixes for the databases upon install?

I'm contemplating just creating three db instances (DB\SPDev, DB\SPDemo, DB\SPTest).

What do you recommend?

To answer Mike's question, Demo could be considered production, but Dev & Test are for dev & test, respectively. This question isn't so much out of a performance consideration (it's one database server regardless) but more of a configuration consideration.

Answer 1

Each instance of SharePoint can spawn between 8 and 20+ databases and not all databases are created as part of the SharePoint installation (i.e. Web Applications and Service Applications are usually configured later as needed), I would strongly advise putting each SharePoint farm in its own instance of SQL.

Another "neat" feature of SharePoint is that, for certain operations that ask for a database name, if you specify a database name that already exists then SharePoint will happily append new data into the old database without warning you, possibly resulting in two farms being tied into the same database. This risk is also greatly minimized when the farms are in separate instances.

If you are a "Separation of Duties" shop, then odds are also good that you have security requirements where certain people can only take certain actions in certain environments. Having all of your farms in the same instance makes managing this a great deal more complex.

If one of your environments is Production then you also have to worry about overall performance as SharePoint hits TempDB quite hard. Having all three farms in one DB Instance means that all farms will be fighting over a single TempDB. This means that normal developer activities could easily bring your production performance to a halt.

Multiple instances is additional overhead but makes management, backups and security a great deal easier. SharePoint also performs better this way.

Answer 2

Yes, you can do a SharePoint 2010 install in a manner that will allow you to specify the names of all your SharePoint databases, so you can enforce a naming convention that you can use to distinguish between the various farms that the databases are associated with. The challenge is that you won't be able to use the Product Configuration Wizard GUI interface to make this happen, instead you'll have to do it via the command line with PSCONFIG.exe and PowerShell.

The good news is that there are a couple of good write-ups out there that describe the steps you'll need to take to make it work. I'd suggest taking at a look at these posts:

• http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=241
• http://www.sharepointben.com/blog/Lists/Posts/Post.aspx?ID=403

John