Content Databases - Bradley Schacht



SharePoint for the DBAContent DatabasesA site collection is tied to a single content databaseA content database can be used for multiple site collectionsSize limitationsPre-Service Pack 1:200GB for collaboration1TB for document archivePost Service Pack 1:4TB for all usage No limit for document archiveIf more than 4TB are needed for content databases then a scale out methodology must be usedBreak site collections out into other databasesThis requires multiple content databases each of which can be up to 4TB in sizeFor instance a content database may be approaching 4TB and contain 4 site collectionsMove 2 site collections to their a second content database and leave 2 site collections in the current databaseThe guidance from Microsoft is if a site collection is over 100GB it should be the only site collection using a given content databasePossibly add some information about remote blobs ()If a site collection is over 50GB it should get its own content databaseMoving a site collection to a different content database:Moving a site from one content database to another can be done with…STSADM command line toolPowerShell Performing a backup and restore of the site collectionNo options in Central Administration or a GUI times content databases will have a GUID on the end of itTo rename content databasesMost common reason to do this is to remove the GUID or conform to naming standardsIn central administration switch the Database Status to Offline (as opposed to Ready)Check the box next to Remove Content DatabaseThis does not drop the database, it simply disassociates it from the farmAll site collections contained in this content database will no longer be availableAll data remains intact in the databaseBackup and Restore/Rename the database in SSMSAdd the renamed content database back to SharePoint via Central AdministrationThis should be avoided if at all possibleMoving a content databaseCan be done to load balance a database serverCan be done to load balance a web applicationRequires both SharePoint 2010 and SQL Server changesSharePoint changes can be through central administration or PowerShellProcessPause services and service applications using the content database(s)Remove the association in SharePoint (central admin or PowerShell)Move the databases (SQL Server)Add the content databases back into SharePoint (central admin or PowerShell)Restart services and service applications using the content database(s)Methods for renaming and moving service applications can be found here: tells if a service application can be associated by:Delete and recreate the service applicationUsing PowerShellUsing Central AdministrationAdding addition content databasesIn order to scale you may need to create new content databasesContent databases get full (up to the limits described above)Too many site collections in one databaseMethodsCentral Administration in the Manage Content Databases section click Add A Content DatabasePowerShell run New-SPContentDatabase command with appropriate parametersPowerPivotPowerPivot requires its own instance of SSASThis instance must be called PowerPivotInstallationFrom the SQL Server Setup Roles screenSwitch from SQL Server Feature Installation to SQL Server PowerPivot For SharePointSelect the option for a new or existing serverBackup and RecoveryEach PowerPivot workbook will have a database created when it is run in SharePointDatabase name will be the WorkbookName_<<GUID>>These cannot be renamedSharePoint will age these out per settings in Central AdministrationNo backup and restore as a resultIf a database is deleted when the user clicks on something in the workbook PowerPivot will recreate the databaseCubes can NOT be reverse engineered into an SSAS project like they can be with a UDM cubePerformanceCorrect HBA driver and firmware versionsStandard SQLIO.exe for disk I/O performance testConfigure correct NTFS Allocation Unit Size64k best, default can cause up to 30% performance drop offCorrect Windows “Sector Alignment”Up to a 50% performance drop if wrongFree space on disk partitions should be greater than 25%File Placement from fastest drive to slowest driveTempDB (data and log)DB Transaction logsSearch DB data filesContent DB data filesIf the sites are more read centric than read-write then move the content above logsMultiple data files# of data files should be <= # of processor coresOnly split data files for content and search databasesOther databases do not support splitting data filesStandard SQL best practices for data file sizes, auto grow, temp db, etc.MemorySmall farm – 8GB or moreMedium farm – 16GB or moreLarge farm – 32GB or moreDefrag the disksDatabase MaintenanceIntegrity checks should be done regularlyREPAIR_ALLOW_DATA_LOSS is not supported on the databasesREPAIR_REBUILD is available but may not always workContent and Search are most likely to become fragmentedIndex rebuild/reorg as you normally wouldDo NOT add or remove indexes!!!Backup and RecoveryPerform regular database backups Stagger backups of the databasesIncremental backup when possible on larger databasesCompress backupsFollow SQL Server backup/restore optimization recommendationsFarm and site collection backups should be handled by the SharePoint adminHigh AvailabilityIt is important to scale out to increase capacity Server failover clustering is available run on any combination of active and passive nodesSharePoint references the cluster as a whole, it is not cluster awareMembers of the cluster must be on the same subnetAll setup and maintenance is on the SQL Server sideSQL Server mirroring is available use high-availability mirroring (high-safety) mode with automatic failoverSharePoint 2010 IS mirror awareThe failover server must be specified in Central Administration (or configured through PowerShell)Principal, mirror and witness must all be on the same LAN (handle up to 1 millisecond latency round trip)Manual reconfiguration after failoverMirroring is NOT available on the User Profile Service’s Synchronization databaseMirroring is NOT available on the Web Analytics’ Staging databaseMirroring is NOT recommended on the Health Data Collection’s Logging databaseMirroring is NOT recommended on the Application Registry’s databaseRecovery time for clustering is lower than that of mirroring (in the milliseconds)Disaster RecoveryRolled in with the rest of the SQL Server DR planSee High Availability aboveSharePoint admins will have their own DR plan outside the scope of the databases as wellSome service applications will need to be configured on the primary and failover farms, such as Excel Services (for which there is no database associated)The following do not support log shippingApplication RegistryBusiness Data ConnectivityUser Profile ServiceSharePoint Foundation Subscription SettingsSearchWord automation servicesService ApplicationsThe following service applications store data in databasesSearch (3 databases)Search AdministrationCrawlPropertyUser Profile (3 databases)ProfilesSocialSynchronizationBusiness Data ConnectivityApplication RegistryUsage and Health Data CollectionManaged MetadataSecure StoreStateWeb Analytics (2 databases)ReportingStagingWord AutomationSharePoint Foundation Subscription SettingsPerformancePoint ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download