Automating SQL Server Database Creation for SharePoint

Talbott Crowell

More Related Content

1.Automating SQL Server Database Creation for SharePoint Talbott Crowell ThirdM.com http://TalbottC.spaces.Live.com
2.Automating SQL Server Database Creation SharePoint SQL Server Overview Planning for Document Content Storage Plan for MOSS Software Boundaries Search Indexing Backup/Restore and Availability Structuring Data in SharePoint Site Collections Content Databases Partitioning Data in SQL Server Sample Solution Other Considerations for Document Storage, Future...
3.SharePoint SQL Server Overview What is SharePoint? WSS (Windows SharePoint Services 3.0) – Free with Win Server MOSS (Microsoft Office SharePoint Server 2007) SharePoint Farm Small, Medium, Large SQL Server Configuration Database Content Database(s) Search Index Database Shared Service Provider Database SQL Server Windows Server Windows Server IIS (Internet Info Svcs) ASP.NET WSS 3.0 MOSS 2007
4.SharePoint Farm Sizes Single-server Farm Two-server Farm Four-server Farm Five-server Farm
5.SharePoint Large Farms
6.Planning for Document Content Storage MOSS Software Boundaries Logical Structure Site Collections, Sites, Lists, Folders Using Folders for Scalability Search Indexing Indexing Columns for Performance Backup/Restore and Availabilty
7.Typical Large-Scale Content Management Scenarios Large-scale authoring environment Document Center site template, most users are authors 50K or more docs, 500 or more folders, versioning turned on Single database up to 150 GB Large-scale content archive Knowledge base site, document archive, Records Center site template 1 million or more documents Single database up to 400 GB Extremely large-scale content archive 10 million docs across 5,000 or more folders Users (50K or more) browse content by searching Content submitted by using custom submission form
8.Plan for MOSS Software Boundaries Limitations SSP: 20 per farm (3 per farm recommended) Web app: 99 per SSP Content database: 100 per web app Site collections: 50K per content DB, 150K per web app Web site: 250K per site collection Recommended content child items Site collections: 50K per web app Site hierarchy: 2000 sub-sites for any parent site Site: 2000 lists (or document libraries) per site Document Library: 10 million documents, 2000 documents per view (folder) Folder: 2000 items per folder http://technet.microsoft.com/en-us/library/cc263028.aspx
9.Flat document library quickest drop in throughput occurs when the total number of documents is less than 2,000 Source: Technet article “Plan for software boundaries (Office SharePoint Server)” http://technet.microsoft.com/en-us/library/cc262787.aspx
10.Hierarchical document library 500 documents per folder No significant throughput degradation up to 1 million documents Source: Technet article “Plan for software boundaries (Office SharePoint Server)” http://technet.microsoft.com/en-us/library/cc262787.aspx
11.Search Indexing GB of disk space required = Total_Corpus_Size (in GB) x File_Size_Modifier x 2.85 File_Size_Modifier 1.0 for very small files (average 1 KB) 0.12 for moderate size (average 10 KB) 0.05 for large files (average 100 KB or larger) Example: 1 GB files average size 10 KB 1 GB x 0.12 = 0.12 GB (estimated size of index file is 120MB) Next, multiply the estimated size of the index file by 2.85: 120 MB x 2.85 = 342 MB See: Estimate performance and capacity requirements for search environments http://technet.microsoft.com/en-us/library/cc262574.aspx
12.Indexing Columns for Performance Improves performance for sorting or filtering list Not done in SQL Server! Changes made in SharePoint List Settings Manually via browser Programmatically through the API Declaratively using CAML (WSP)
13.Backup/Restore and Availability Databases to back up Content Databases Config Databases (Config, Admin, SSP Admin) Search Databases (WSS, SSP) Other backups IIS config, WFE 12 folder, search index files Under 200 GB You can use STSADM -o backup Over 200 GB Microsoft System Center Data Protection Manager 2007 (DPM) AvePoint or any backup vendor that supports MOSS 2007
14.Structuring Data in SharePoint Farm Can have 1 or more Web Applications Can have 1 or more Shared Service Providers (SSP) Web Application Logical “portal” or destination (simple URL like: http://documents ) Each web app must belong to a single SSP (MOSS only) Can have 1 or more site collections Site collection Can have 1 or more sites Site Can have 0 or more lists (document libraries) List Can have 0 or more items (documents, folders, etc…)
15.DEMO Walk through of SharePoint Logical Structure SharePoint Farm Web Application Site Collection Site List (Document Library)
16.Site Collections Can use site collection to partitioning data in SQL Server From database perspective is the smallest logical unit that can have its own database All sites and documents created inside site collection will be stored in same database Content Database Site collection Must have at least one “root” site Can be created programmatically
17.DEMO Manually creating SharePoint Structure Site Collection Content Database List (Document Library)
18.Content Database SharePoint Central Administrator (Central Admin) Disabled or Offline Database what does it mean? Still used by existing site collections Can still create a new site, library, upload document, changes Can’t create a new Site Collection! How do you force documents into a specific content database? Disable all content databases except the one you want to use Take “Offline” in Central Admin Create new site collection
19.Programmatic Approach Imitate manual approach Steps Disable all content databases (Offline) Create a new content database and make it enabled/online Create a new site collection Site collection is stored in new content database Disable new content database Restore the state back to the default online content database(s) for web application
20.Create Content Databases Programmatically Reference Microsoft.SharePoint.dll Using Microsoft.SharePoint.Administration SPWebApplication webApplication = SPWebApplication.Lookup(webAppUri); SPContentDatabaseCollection contentDBs = webApplication.ContentDatabases; ... if (contentDB.Status == SPObjectStatus.Online) { contentDB.Status = SPObjectStatus.Disabled; contentDB.Update(); } ... CreateContentDatabase(yearString) SPSite siteCollection = webApplication.Sites.Add(... Collection of databases Loop through collection and disable online databases Call routine (next slide) to crate DB Finally add new site collection
21.Create Content Databases Programmatically private void CreateContentDatabase(string suffix) { SPContentDatabase copyFromDb = webApplication.ContentDatabases[0]; string prefix = "MyApp_ContentDB_"; string dbName = prefix + suffix; int warningCount = 0; int maximumSiteCount = 1; int status = 0; // 0=ready, 1=Offline SPContentDatabase contentDb = webApplication.ContentDatabases.Add(copyFromDb.Server, dbName, copyFromDb.Username, copyFromDb.Password, warningCount, maximumSiteCount, status); }
22.DEMO Creating SharePoint Structure using API Site Collection Content Database
23.Sample Solution Document repository Needed to scale to millions of documents Needed to be searched Needed to be maintainable by SQL Admins Stored files programmatically Solution Single web app Site collection per time period Each site collection had one site with one document library Use folders to handle scalability Combination of date and business division and document meta data
24.MOSS Content Database Storage Estimates Year New Docs Total Docs Doc File Size (GB) Doc Lib Size (GB) Year 1 2,000,000 2,000,000 200 300 Year 2 2,300,000 4,300,000 430 650 Year 3 2,700,000 7,000,000 700 1000
25.Search Index Storage Estimates Search Index files stored on the WFE Search DB stores additional information used by search service Year Total Docs Document Lib Size (GB) Index Size(GB) Search DB Size(GB) Year 1 2,000,000 200 15 60 Year 2 4,300,000 430 30 130 Year 3 7,000,000 700 50 200
26.Content Database Partition Strategy Goals Optimize Availability or Manageability? Optimize Availability Backup/Restore recovery time Smaller (and quicker) is better Optimize Manageability Manageable number of databases Less is better
27.Database Partition Strategy Database Size and Count Database Partition Strategy 1 Content Database per Year 1 Content Database per Quarter Average Size 340 GB 85 GB # of DB Year 1 1 4 # of DB Year 2 2 8 # of DB Year 3 3 12 DB Per Year 1 4
28.Other Considerations for document storage RBS Remote BLOB Storage (SQL Server) EBS External BLOB Storage (SharePoint)
29.Remote BLOB Store Provider Library Implementation Specification Store BLOBs on a Remote Blob Store (RBS) RBS Typically a separate box on same the network as the SQL Server.  Managed by SQL Server Integrity between the database records and the RBS external store is maintained Microsoft SQL Server 2008 Feature Pack, October 2008
30.External BLOB Storage (EBS) in WSS EBS runs parallel to the site's SQL Server content database, which stores the site's structured data To coordinate the two data stores, you must implement a COM interface ISPExternalBinaryProvider Uses simple semantics to recognize file Save and Open commands Invokes redirection calls to BLOB store when it recognizes BLOB data streams You must install, register, and configure the EBS Provider on each WFE
31.Resources EBS for WSS Kyle’s Blog (extensive coverage of his implementation) http://www.kyletillman.net/blog/post/SharePoint_External_Binary_Store-PartI.aspx Plan for MOSS Software Boundaries (Folder Structure) http://technet.microsoft.com/en-us/library/cc262787.aspx
32.Future Tools Visual Studio 2010 Tools for SharePoint Server Explorer for SharePoint viewing Lists and other artifacts in SharePoint directly inside of Visual Studio Windows SharePoint Services Project (WSP file) Import to create a new solution Packaging explorer and packaging editor lets you structure the SharePoint features and WSP file that is created SharePoint speculation Will SharePoint v.Next utilize RBS or EBS for document storage? Maybe 3 rd party options?
33.Summary Automating SQL Server Database Creation Planning for Document Content Storage Plan for MOSS Software Boundaries Search Indexing Backup/Restore and Availability Structuring Data in SharePoint Site Collections Content Databases Partitioning Data in SQL Server Sample Solution Other Considerations for Document Storage, Future...
34.Thank you Questions? Talbott Crowell ThirdM.com http://TalbottC.spaces.Live.com