Condividi tramite

Name That Database!

  • Articolo

SharePoint 2010, with all of its new service applications and social features, just LOVES databases. I don't know the actual count, but I think I've heard as many as a possible 15+ databases if you light up every feature... and that's before you create any actual content! Anyone that has then popped open SQL Management Studio afterward and tried to figure out which database relates to which feature in which environment and which site has probably already run out of hair to pull out. We are CLEARLY in need of a solid naming convention... and I don't mean a GUID!

So... in coming up with a standard, we need to consider some things...

  1. Many SQL Servers are shared (for better or worse) with either other applications or other SharePoint farms. This means that any naming convention must help identify which environment the various DBs belong to, and group them together in an orderly fashion.
  2. SharePoint has some fairly specific types of databases, and those databases should also be grouped together.
  3. SharePoint has some built in categorization systems (i.e., Proxy Groups) that should also be taken into account.
  4. SharePoint has some fairly specific limits that the naming convention should help me enforce.
  5. Redundancy in a database name is usually not necessary.
  6. GUIDs are evil (at least in object names and database indexes)

Here's how I've standardized on addressing as many problems as possible. (Note that this isn't exactly carved in stone... so if something doesn't work for you, then don't do it. :)). The components listed are IN ORDER of appearance in the naming convention, separated by underscores for readability. So, just go down the list and you should be able to stick to an easy to use and understand standard.

Environment Name

Almost all of my customers continually use a common set of names for their environments. Some are more common than others, but on the whole they're very similar and serve purposes. I suggest we condense each down to a 3 digit code. Here's my go-to list for now:

  • PRD - Production
  • TST - Test
  • STG - Staging
  • DEV - Development
  • UAT - User Acceptance
  • PRF - Performance Testing (or Load Testing)
  • SBX - Sandbox
  • INT - Integration
  • MRG - Development Merge
  • EVL - Evaluation
  • ....or anything else you can come up with.

Examples: "PRD_...", "SBX_...", "UAT_..."

Database Type

Most databases in SharePoint can fall into one of 3 broad categories: It is either related to the Farm as a whole, is a content database, or is related to a service application. So, the following would be next in line:

  • Farm - Databases related to the farm itself
  • Content - Databases that are content databases containing web content
  • SA - Databases that support any service application

Third party customizations or functionality are up to you. If you can fit them into this model then that's fantastic... but that's beyond the scope of this article.

Examples: "PRD_Farm_...", "SBX_Content_...", "UAT_SA_..."

Context

Now things get more fuzzy. As we get more specific, the kind of database should get taken into account and the naming strategy changes slightly. For "Farm" databases, simply listing the database context, content, or purpose works fine. For example "Config" or "Configuration" or "Usage" make perfect sense when reading the database names.

Example: "PRD_Farm_Config" (are you seeing how you can now look at this database and know EXACTLY what it relates to? It's a good thing! )

Content Databases are the next easiest - They should have the hostname (and port number, but only if necessary) followed by a 2- or 3-digit number, starting at "00". This means that we can always associate a content database with the web application it is associated with (it can only be associated to one), we have a running count of how many content databases are on that web application, and we can generally see the order they were created in. There's the natural question of "but how do I know which database a site was in?" here... but I'm happy to respond to that in the comments if someone asks the question. Simple answer: with rare exception, I don't actually care which DB contains which site.

Example: "SBX_Content_MySite_00", "SBX_Content_MySite_01"

Service Applications are a bit more tricky, because each one is different and they have differing numbers of databases. Generally though, I focus on the Proxy Group (and in my world, EVERY application belongs to a proxy group... "custom" is not an option), the service application name (without redundancy), and any additional qualifiers. Because there can be a lot of permuations of this, I'll give several examples...

Examples: "UAT_SA_Default_Metadata", "UAT_SA_Default_Profiles", "UAT_SA_Default_Profiles_Sync", "UAT_SA_Default_Profiles_Social", "UAT_SA_Internet_Search", "UAT_SA_Internet_Search_CrawlStore", "UAT_SA_Internet_Search_PropertyStore"

In these examples (though I'll bet it's obvious... again, it's a good thing!), "Default" and "Internet" are Service Application Proxy Groups (Default being the "default" proxy group in SharePoint), "Metadata", "Profiles", and "Search" are the kind of service application, and any extra parameters the differentiate the different types of DB that may be required.

This is only intended to be a good baseline idea, and is widely applicable to many, many users exactly as it is listed. However, it is not sufficient to solve every customer's needs. For example, hosting providers may need to include a section that focuses on the target customer (though I would bet that could be mostly satisfied with the proxy group). If you see that this doesn't meet your needs, then feel free to modify this method into something that does. I would incourage that you follow some basic rules though:

  • Don't be redundant - There is no reason to include the word "Service" in the database name for a service application. You already said it was related to a service application by using the "SA" designation... there is no reason to say "Service" again. Also, don't include "DB" in the name... if I can see it in the SQL Server, there's a high likelihood I already know it is a "DB".
  • Meet your needs - Use categories and category placements that best help you manage your infrastructure. For example, if a customer name/code is included, it should probably be BEFORE the database type designation. This would allow the "Acme_SA" and "Acme_Content" databases to be next to each other so that I know exactly where to look when someone from Acme Corporation calls.
  • Violate properly - Every rule was made to be broken... but break it well. For example, if you need to restore a database, don't simply call it "_Restore". Name it something proper and useful. If it is going to be around for a long time, give it a name that properly fits the strategy. If it's not, include a "_DeleteAfter_20111231" indicator in the name that says when you're fairly certain you'll no longer need the database. If a DB is to contain only a single site collection, feel free to replace the number with the site name (though I would still contend the numbering is still fine).

SharePoint may occasionally be unhelpful when trying to conform to a naming strategy... but you were looking for an excuse to dig deeper into PowerShell anyway, weren't you? :)

Yes, it's worth it. If nothing else, your DB will call you good names instead of bad ones, and may even include you on their holiday card list. Any day that a DBA says I have the best naming strategy in the company is a VERY good day. :)

Update 7/23/2012:

Good references for those Service Applications that require PowerShell for database naming:

 $sb = {
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    $saAppPool = Get-SPServiceApplicationPool "DefaultServicesApplicationPool"
    $upa = New-SPProfileServiceApplication `
        -Name "Default User Profiles" `
        -ApplicationPool $saAppPool `
        -ProfileDBName "PRD_SA_Default_Profiles" `
        -SocialDBName "PRD_SA_Default_Profiles_Social" `
        -ProfileSyncDBName "PRD_SA_Default_Profiles_Sync" `
        -ErrorAction Stop
    New-SPProfileServiceApplicationProxy -name 'Default User Profiles' -ServiceApplication $upa
   }
   $cred = Get-Credential "litware\sp_farm"
   $job = Start-Job -Credential $cred -ScriptBlock $sb | Wait-Job
   Receive-Job -Job $job #Returns output to screen