Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's Office 365 Admin Blog > Posts > Why does SharePoint put those blasted GUIDs in its databases?
August 17
Why does SharePoint put those blasted GUIDs in its databases?

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:

  1. A SQL instance can contain databases from multiple SharePoint farms.
  2. 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.




I hope they crap it for uniqueness
 on 8/17/2010 11:30 PM


Why not create a db and have Sharepoint use that db?
 on 8/17/2010 11:46 PM

Rename Crawler and Property Store DBs

Great post!

I have been editing the properties of the Crawler and Property Store components and removing the GUID from the listed database. Upon "Apply Changes", it renames the databases in SQL. This way you don't have to create new ones and delete the old ones. I have not seen any issues with Search doing it this way........yet. :)
 on 8/18/2010 9:02 AM

Re: Why does SharePoint put those blasted GUIDs in its databases?

The dash is not a comment character, you need two dashes for that.  But it is still a special character (for subtraction), so it most likely would give a syntax error if not escaped.
 on 8/18/2010 11:26 AM

Re: Rename Crawler and Property Store DBs

Good to know. I'll need to try that.

Todd O. KlindtNo presence information on 8/18/2010 1:53 PM

Re: Why does SharePoint put those blasted GUIDs in its databases?

Yup, you're right, two dashes is a comment.

Todd O. KlindtNo presence information on 8/18/2010 1:54 PM

Re: DBName

You can create the databases ahead of time and SharePoint will use them. The problem is that if you use the wizards, SharePoint never asks you which databases to use.

Todd O. KlindtNo presence information on 8/18/2010 1:55 PM

Re: Why does SharePoint put those blasted GUIDs in its databases?

It would be nice if they used the same guid for each farm instance.... this way you could see what databases belong to each farm.

I still think you can use the same approach as the windows shell append a number to the name NewFolder(3)
 on 8/18/2010 4:28 PM

Checking for databases?

Not that I mind the practice of SharePoint tagging GUID's to databases, but I'm fairly certain there's a way to see if a database name is already taken programatically, probably as simple as querying a table in the master db for database names.  Why not just do that? 
 on 8/19/2010 3:34 PM

Re: Checking for databases?

Because in some cases you do want to mount an existing database. For instance, your DBA may require that they create the databases manually to make sure it's configured exactly how they want it; the location, the recovery model, etc. How is SharePoint supposed to know if you mean to mount the existing one, or if it's part of another farm? It can't know if you're using the wizard. Remember, the whole idea of the wizard is to ask you as few questions as possible. So instance of asking you, it creates GUIDs.

Todd O. KlindtNo presence information on 8/19/2010 10:22 PM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.


Body *

Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person


Want a message when I reply to your comment? Put your Twitter handle here.