This question came up a couple of times today, so I thought I'd address it in a blog post. The question goes something like this; "Why does SharePoint put %*&@ing GUIDs at the end of its databases? Argh!!!" This is a very good question, and has a very good answer.
Before we go any further, let me be clear. I'm not defending the practice, I'm just explaining it. J
What causes it?
So, why does SharePoint do this? Well, it's important to keep in mind that the only databases that have GUIDs at the end of them, are ones that SharePoint has to name itself. If you, as the admin, get to name a database, it won't have a GUID, unless you choose to put one there yourself. SharePoint puts GUIDs on those databases for two reasons:
- A SQL instance can contain databases from multiple SharePoint farms.
- To SharePoint, creating a database and mounting an existing database are the same thing (in most cases).
The combination of those two facts is why SharePoint throws GUIDs at the end. It doesn't know if that SQL instance already has database named "User Profile Service Application_SyncDB" from another SharePoint farm. Or, maybe that database is there and it was created manually by your DBA with specific settings. To prevent itself from mounting some other User Profile Service Application_SyncDB by accident (since mounting and creating are the same) it throws a GUID at the end to guarantee the database name is unique. Here is what it looks like in SharePoint Server 2010.
The databases in the red circles have GUIDs at the end, and were created by the Configuration Wizard (PSConfig or The Gray Wizard) and the Farm Configuration Wizard (The White Wizard) in Central Administration. Not only do they have GUIDs at the end, but to add insult to injury, some of the GUIDs have dashes (blue circles) and some do not (green circles). This is significant because in TSQL, the dash character (-) is a comment character. If you or your DBA has any scripts to work on your databases (like back them up), it's quite possible that they do not escape that dash, and the script will fail since there is no database named "SharePoint_AdminContent_978a91dc." That particular issue really cheeses off DBAs. That's okay, they normally deserve it, the big meanies.
How can we prevent it?
This is the part of the blog post that makes it all worthwhile. How can we fix or prevent these cursed GUID-riddled databases? There are a variety of techniques, but basically it all comes down to this; don't use Wizards, create the databases yourself. What does this mean? For one, it means don't let the Configuration Wizard (The Gray Wizard) create your farm. Instead use New-SPConfigurationDatabase to build your farm. That allows you to specify a name for the Admin Content database. If you miss that step, you can use the tip I outline in my blog post Get the GUID out of SharePoint databases to fix it after the fact. After your farm is provisioned, don't use the Farm Configuration Wizard (still The White Wizard) to build it. Instead create all of your Service Applications with Windows PowerShell or in Central Administration. That gives you control over their database names. If your farm is already created, you may have to delete the associated Service Applications and rebuild them correctly. Some might let you rename the database, though I haven't tried that personally. With Search you can create new Crawler and Property Store components and delete the ones with the GUID infested database. You'll need to run a full crawl afterwards.
The moral of the story though, is those GUIDs are there because we let SharePoint do all the hard work. If we do the hard work instead, we can prevent those ugly GUIDs from sullying up our SQL instance.