Mehta - SharePoint Data And Database Management

  • Published on
    13-Apr-2017

  • View
    1.493

  • Download
    1

Transcript

Data & Database ManagementJanuary 7th, 2009NYC SharePoint User GroupPresenter: Nilesh Mehta Sr. SharePoint Architect NGenious Solutions, Inc.*SharePoint TrainingProject Server Training*Challenge: Managing Unexpected data Issues with SharePoint Number of SharePoint Sites Size of your SharePoint farm SharePoint Site Backup / Restores SharePoint Site Corruptions - especially with host header sites*Target Audience Mid-scale to large-scale SharePoint implementations Business / Platform Owners SharePoint Administrators SharePoint Developers Need to understand Production limitations*Agenda*Key messages to remember:Think and plan upfront: How big could your SharePoint environment be in the next two years?Organize your content databases for easy management, moving and restoring sitesPrepare for potential site corruption: Dont put too many eggs in one basketTalk about our current environment issues Number of sites in 2007 vs. sites in 2008, Data size, about having to clean up disk space weekly, shrink database, having to move sites from one database to another. Downtime for end users while we perform maintenance.*Understanding your SharePoint SitesCollaboration Sites - # Team sites with regular site quota Possibly in the 1000sPortal Sites - Sites with Host Header URL or Large Content Size Possibly about 25+ sitesApplication Sites - Custom Applications built on SharePoint or Dedicated web applications for specific client requirements*SQL Server ConfigurationDeciding on the SQL Cluster ConfigurationNumber of nodes in the ClusterNumber of instances in the clusterDeciding drives per instance and drive sizeSQL Cluster Configuration Deciding on the Server Hardware, Number of CPUs, Memory, OS 32-bit vs. 64-bit, Number of drives, Drive sizes, Number of nodes in the cluster, Active / Passive vs. Active / Active*SQL Server Configuration - ExampleTotal data size = 1 TB2 nodes in the ClusterNumber of instances in the cluster = 1Deciding drives per instance and drive sizeDrives:C: = OSD: = 250 GB (Data)E: = 250 GB (Data)F: = 250 GB (Data)G: = 250 GB (Data)H: = 500 GB (Log)I: = 1 TB (Backup)Drives:C: = OSD: = 500 GB (Data)E: = 500GB (Data)F: = 500 GB (Log)G: = 1 TB (Backup)Drives:C: = OSD: = 1 TB (Data)E: = 500 GB (Log)F: = 1 TB (Backup)*Organizing Content Databases at SharePoint LevelWhere is my site collection?How many sites in a content database?*Organizing Content Databases at SharePoint LevelNumber of sites in a content databaseSize of content databasesBest Practices:Naming conventions for Content DatabaseSetting maximum site count in Content DatabaseSetting site quota on content databaseMicrosoft states keep content dbs around 25-30 GB. How real world is that? If you environment grows up to 1500 Sites at 1 GB / site, you can host about 25 30 sites per database. That means you will be hosting about 50 databases. When it comes to finding out which sites belonged to which content Database, you are going to have a really tough time finding that out. Especially if the site is deleted and needs to be restore, because SharePoint keeps no trace of the same, or if the site was moved from one DB to another and you need to restore a version from when it was in the original DB.*Organizing Content Databases at SharePoint LevelCollaboration Web Application:Target Content DB sizes around 100 GB for easier management@ 1 GB Quota = 100 Sites / Content DB. Lock Site Max @ 100 SitesCustom Content Databases for larger sites above 20 GB. Lock site maximum to single siteAssigning Site Quota500 Mb, 1 GB, 2GB, 5 GB or largerTalk about end user impact when you have to perform maintenance. Due to moving sites, corrupt sites? If you have more sites in the same database, when you bring it down you impact everyone. At less number of sites per content database, you can perform maintenance by impacting a smaller audience.Talk about pros and cons of setting really small site quota.*Managing Corresponding Databases on SQL ServerSites increase, so does database SizeToo many databases Usage increases, CPU & Memory Usage increases Get better performance by upgrading to 64-bitSQL Cluster Configuration Deciding on the Server Hardware, Number of CPUs, Memory, OS 32-bit vs. 64-bit, Number of drives, Drive sizes, Number of nodes in the cluster, Active / Passive vs. Active / Active*Managing Corresponding Databases on SQL ServerConsider 1st Scenario in Drive Configuration:@ 1 GB quota & 100 sites, you can have a maximum of two Content DBs / DriveHighly possible that some sites will need more than 1GB from these sitesDrives:C: = OSD: = 250 GB (Data) Collaboration Team sitesE: = 250 GB (Data) Collaboration Team sitesF: = 250 GB (Data) Portal sitesG: = 250 GB (Data) Application sitesH: = 500 GB (Log)I: = 1 TB (Backup)*Managing Corresponding Databases on SQL ServerOptions:1. Add extra drives to the SQL Server2. Moving Databases between Drives:Detach database from SQL Server.Move database files as necessary on the same serverAttach database back to SQL ServerNo issues from the SharePoint side. SharePoint auto-detects the databasesCons: You can only add so many drives and will soon run out of drives*Managing Corresponding Databases on SQL ServerConsider 2nd Scenario in Drive Configuration:@ 1 GB quota & 100 sites, you can have a maximum of four Content DBs / Drive. Here you do have room to grow in some mannerTape backup of these drives take more timeDrives:C: = OSD: = 500 GB (Data) Collaboration Team SitesE: = 500GB (Data) Portal and Application Team SitesF: = 500 GB (Log)G: = 1 TB (Backup)*Managing Corresponding Databases on SQL ServerData grows more than 1 TB:Add more drives to the same cluster instanceBased on SQL Server performance, create another SQL Instance on passive node and add drives thereCreate a brand new Cluster and add drives thereRestore sites from SQL backups:Need space to restore SQL backups twice the size of the database*Managing Corresponding Databases on SQL ServerMoving Databases across SQL Cluster:Perform full SQL BackupsPerform stsadm o preparetomovePerform stsadm o detachcontentdbCopy SQL backup files to new clusterAttach content dbs to the new SQL ClusterPerform stsadm o addcontentdb*Moving Sites across Web Applications / Content Databases : STSADM BackupNumber of site collections increaseSite collections need more spaceOptions:STSADM Backup / restore Best Solution for moving sites. Create a batch file for backups, deletes and restore. Dependency on local file system for backups. NOTE: Do not delete large sites from central administration. Always use STSADM utilityCON: Only CON is you cannot select which database to restore site in. If you use CreateInDatabase argument, it creates a brand new database. NOTE: Do not delete large sites from Central administration. Often we have seen the Microsoft Wheel spinning when it tells us that it is doing something (and not showing verbose progress ) and then the process times out. With large sites, if the process times out, there are possibilities that it will leave corrupt sites behind. *Moving Sites across Web Applications / Content Databases : Merge Content DBMerge Content Database Commandgood option for moving large number of sites, but has issues with Multi-Valued columns . It does not keep the values. Does not work across SQL Instances. Can move sites only in the same SQL Server Instance. *Moving Sites : Batch Site ManagerBatch Site Manager Another option to move sites from the Central Administration. Pros: Allows you to select which database to restore sites in. Works across clustersCons I have had very little success with this. Moved 5 sites and ended up with about 3 corrupt sites. Dependency on local file system disk space for backups and can cause corruption when you run out of disk space either on local server or SQL Server*Moving Sites: Batch Site Manager*Moving Sites: Batch Site Manager*Moving Sites: Batch Site Manager*Special Managing Host Header SitesSyntax for creating Host Header Sites: Stsadm o createsite url http://abc.mycompany.com ownerlogin owneremail sitetemplate hhurl Possible corruption caused when moving host header sites from development to staging to productionExample:Host Header Site in Development: http://devabc.mycompany.comRestore site in QA as http://qaabc.mycompany.com (Database keeps reference to devabc.mycompany.com)Restore site in Production as http://abc.mycompany.com (Database keeps reference to http://devabc.mycompany.com)Imagine creating multiple sites based on same backup because you want to keep template. They all will have reference to http://devabc.mycompany.comWhen you perform content database detach / attach , configuration database does not know which site was which because they all have same Host Header column value*Special Managing Host Header Sites, Avoiding CorruptionsAvoiding corruption with Host Header Sites: When you restore site from Development to QA or Prod, restore with the same URL http://devabc.mycompany.comOnce restored, perform stsadm o renamesite command on this URL and it modifies the host header information in database properlyPerform similar action if you have multiple sites coming in from same template*Key messages to remember:Think and plan upfront: How big could your SharePoint environment be in the next two years?Organize your content databases for easy moving, management and restoringPrepare for potential site corruption: Dont put too many eggs in one basketQuestions?Thank you & best regardsNilesh MehtaNGenious Solutions, Inc.1075 Easton Ave., Tower1, Suite #6Somerset, NJ 08873Ph: (732) 873-3385Cell: (201) 230-7922Fax: (413) 803-8749E-mail: Nilesh.Mehta@ngenioussolutions.comURL: www.ngenioussolutions.com*****Talk about our current environment issues Number of sites in 2007 vs. sites in 2008, Data size, about having to clean up disk space weekly, shrink database, having to move sites from one database to another. Downtime for end users while we perform maintenance.*Collaboration Sites - # Team sites with regular site quota Possibly in the 1000sPortal Sites - Sites with Host Header URL or Large Content Size Possibly about 25+ sitesApplication Sites - Custom Applications built on SharePoint or Dedicated web applications for specific client requirements*SQL Cluster Configuration Deciding on the Server Hardware, Number of CPUs, Memory, OS 32-bit vs. 64-bit, Number of drives, Drive sizes, Number of nodes in the cluster, Active / Passive vs. Active / Active***Microsoft states keep content dbs around 25-30 GB. How real world is that? If you environment grows up to 1500 Sites at 1 GB / site, you can host about 25 30 sites per database. That means you will be hosting about 50 databases. When it comes to finding out which sites belonged to which content Database, you are going to have a really tough time finding that out. Especially if the site is deleted and needs to be restore, because SharePoint keeps no trace of the same, or if the site was moved from one DB to another and you need to restore a version from when it was in the original DB.*Talk about end user impact when you have to perform maintenance. Due to moving sites, corrupt sites? If you have more sites in the same database, when you bring it down you impact everyone. At less number of sites per content database, you can perform maintenance by impacting a smaller audience.Talk about pros and cons of setting really small site quota.*SQL Cluster Configuration Deciding on the Server Hardware, Number of CPUs, Memory, OS 32-bit vs. 64-bit, Number of drives, Drive sizes, Number of nodes in the cluster, Active / Passive vs. Active / Active******CON: Only CON is you cannot select which database to restore site in. If you use CreateInDatabase argument, it creates a brand new database. NOTE: Do not delete large sites from Central administration. Often we have seen the Microsoft Wheel spinning when it tells us that it is doing something (and not showing verbose progress ) and then the process times out. With large sites, if the process times out, there are possibilities that it will leave corrupt sites behind. ********

Recommended

View more >