What are the implications of using a shared/default SQL Server instance vs a dedicated/named one for SP2010 DB Role?

Question

I have a database server that hosts multiple minor databases not related to SharePoint and I want to utilize this server for a SP2010 environment database role.

What are the implications of installing a dedicated NAMED SQLServer instance (e.g. SERVER\SPInstance) vs reusing the default instance (SERVER\MSSQLSERVER) containing other user databases?

I as understand it, a named instance has its own dedicated windows services and system databases. Sounds like pure extra overhead to me, but I'm thinking maybe SharePoint would benefit somehow from a dedicated named instance.

I suppose the main things on my head regarding the implications are:

• Performance

• Backup/Restoration

• Portability (i may need to move 1 or more CDB's eventually)

Thanks in advance for any thoughts shared.

Answer 1

You are right. Creating a new instance introduces overhead and the two instances will compete for resources.

If you share the instance, make sure the database naming conventions are clear to remove ambiguity around which dbs are for which product. In SharePoint's case, you will need to build the farm more or less with PowerShell so you can customize some of the database names (config DB and Central Admin content db).

No matter which way you go, I recommend you use a SQL Alias so if you change your mind later or need to move the instance it simplifies the data migration (move the dbs and repoint the alias vs rebuilding the farm to specify a new database server/instance)

Answer 2

First of all, no matter what you use be sure to configure SharePoint to use a SQL Alias instead of the direct server/instance as it solves your portability issue.

Second, SharePoint 2010 beats the daylights out of TempDB and if it is fighting for access to this with other applications then all applications will suffer, not just SharePoint.

Lastly, SharePoint creates a lot of databases (8-20 is normal) and occasionally does it without consulting you regarding the database names. As such, you will end up with databases all mixed in with your other applications making it very hard to weed out which is which. Many Third party components that you might purchase for SharePoint also create numerous databases, making this an even bigger mess.

In short, SharePoint in its own instance is not only easier to manage but is more performant as well.

Answer 3

An old question, but I want to add that the Sharepoint installers make some changes to the instance that potentially make performance worse for other apps that may be running from there, MAXDOP being the big one. SP should get it's own instance, regardless of named vs. default.