Importance of database management

On my sites, most developers turned SharePoint admins and most Desktop/AD administrators turned SharePoint admins tend to put up a "Somebody Else's Problem Field" around their databases, not realizing the implications improper configurations can have on their system.

From simple things like leaving the databases in Full restore with out accounting for the transaction logs or configuring backups but not checking to make sure they run, oversights like these and more complicated ones can come back to bite you in the end.

Here are some of the gotcha's I've run into if I turn my back on the implementation team for too long:

• Full restore mode vs Simple restore mode.
  What you chose can depend on several factors and can vary by database. You must factor in the difficulties and advantages of both and be vigilant once configured.
  
• Not configuring multiple content databases for web applications before utilizing it.
  Each new site collection will be created on a new database in a round-robin fashion when it is created. This makes it easier to restore a site collection if necessary, less collateral damage if something goes wrong with a single database, also if space issues are unaccounted for it's a lot easier to shuffle several small databases for compaction than one huge one.
• Just because you've allocated a separate drive for it, doesn't mean the database is there.
  You still have to move search databases and system databases to the proper locations after they're created. Make sure you verify that taking the database down will not affect any running processes.
• Make sure you have enough space for growth.
  In SQL server when a database grows it needs to be able to make a copy of itself. If it can't make this copy in the same drive that it exists in, it will not grow and your users will receive errors. So, not only do you have to account for actual growth of data, you also have to account for the databases to grow as well. I'll discuss this more below.
• A data externalizer like StoragePoint won't work until configured. Additionally, just because you remove data from a database won't automatically make it smaller.
  

I may have to come back and update this later, but these are a good starting point. As you can see, it's mostly common sense planning items, but things tend to get overlooked usually in the excitement and joy of a new implementation or migration.

Full restore mode vs. Simple restore mode is a decision that will have to be made. Sometimes your hand will be forced in the matter by what form of high availability and disaster recovery you chose to implement. Due to the nature of database Mirroring and Transaction log shipping you'll have to use Full restore mode...at least on the databases you'll be mirroring or shipping. You may want to use full restore mode to be able to get a very granular restore with less overhead than frequent database backups. When you use full restore mode you can make restores down to the transactional level. The problem there is that with SharePoint, the database communication portion of the product is designed to the administrator to be a closed book. It's not impossible to figure out what's going on back there, but most of the time it's unnecessary to know and until you do figure it out you can spend far too much time figuring out how it works rather than just getting the user's data back. The other concern with Full restore mode is that you'll have to manage the transaction logs for those databases, which means a lot of backing up and compacting depending on the space you've allocated for logs and how active your user base is. Going along with that, unless it's absolutely necessary to have full restore mode on, I recommend from experience that you move crawler databases and service databases to Simple mode. These databases have the potential (particularly products like Nintex reporting) to be very active and maintain several GB of transaction logs, even if backed up every minute. (I'll probably get some push back from actual DBAs for this, but...) I would recommend changing to simple mode to make transaction log maintenance a non-issue.

I recently went through a migration where utilized 2010 tenants to pull 6 web apps into one tenant web app. After I let the migrators go on the content I realized they'd only created one database for the web app. I took a look at said database and it was several hundred gigabytes and the drive was filling up fast. When performing an ALTER on a database, the smaller it is the quicker it goes. I tried to take a 200GB database off line at one point and it ended up taking over 8 hours before it completed. Additionally, it's important to remember that when a database in MS SQL server needs to expand, it first has to copy itself. As a rule you should make sure you have as much free space on a drive for 2 of your largest databases. If you run out of space, there's numerous options for cleaning up space but most of them involve compacting databases. Even if a database is going to be smaller, it still has to build out the structure before moving it. Setting the site collection creation to round robin between the databases is a cheap way of trying to balance out database sizes and keep them small, the obvious problem is that you don't know which site collections will be big and which will be small. If you leverage the SharePoint Admin Toolkit you can shuffle the site collections after creation to balance the collections by how much their used between the databases. This isn't the kind of maintenance that will need to be run frequently, probably once or twice a year and after a new group of users starts using it and after your initial set up is completed. 4 databases 50 GB each is much easier to manage than 1 database that's 200 GB.

Another thought in addition to this is to set the databases to start at the size that you hope is larger than they expect them to be. The size of a database does not always reflect the contents of the database. The database engine manages the data within the database and makes the database larger if required. The DBA has to manually shrink the database if needed. So rather than have your database grow as needed make it as large as it needs to be or as large as you expect the data to grow to over a period of time. It is a little more difficult to monitor that growth than just eyeballing the drive space, but you should monitor with this method to make sure that your expectations are accurate.

Remember, in all systems the devil is in the details, and SharePoint is a complicated system, don't ignore the simple stuff.