Overview of SQL Server in a SharePoint environment (SharePoint Foundation 2010)

Applies to: SharePoint Foundation 2010

This article describes the relationship between Microsoft SharePoint Foundation 2010 and supported versions of Microsoft SQL Server. It also describes how you can interact with the databases, and it introduces ways of using the reporting and business intelligence (BI) features of SQL Server with SharePoint Foundation 2010.

For more information about the supported versions of SQL Server, see Hardware and software requirements (SharePoint Foundation 2010).

In this article:

• SharePoint 2010 Products and the SQL Server database engine

• SQL Server as a data platform for business intelligence in SharePoint 2010

SharePoint 2010 Products and the SQL Server database engine

SharePoint Foundation 2010 is an application that is built on the SQL Server database engine. Most content and settings in SharePoint Foundation 2010 are stored in relational databases. SharePoint Foundation 2010 uses the following kinds of databases:

• Configuration   The Configuration database and Central Administration content database are called configuration databases. They contain data about farm settings such as the databases used, Internet Information Services (IIS) Web sites or web applications, solutions, Web Part packages, site templates, default quota, and blocked file types. A farm can only have one set of configuration databases.

• Content   Content databases store all site content: site documents, such as files in document libraries, list data; Web Part properties; and user names and rights. All the data for a specific site resides in one content database. Each Web application can contain many content databases. Each site collection can be associated with only one content database, although a content database can be associated with many site collections.

• Service application   Service application databases store data for use by a service application. The databases for service applications vary significantly in what they are used for.

For a full list of all of the databases that support SharePoint Foundation 2010, see Database types and descriptions (SharePoint Server 2010)Database types and descriptions (SharePoint Foundation 2010).

Working with the SQL Server databases that support SharePoint 2010 Products

The SQL Server databases that support SharePoint Foundation 2010 can be created either by SharePoint Foundation 2010, or by a database administrator. For more information, see Deploy by using DBA-created databases (SharePoint Foundation 2010).

Microsoft does not support directly querying or modifying the databases that support SharePoint Foundation 2010, except for the Usage and Health Data Collection service application database, which can be queried directly and can have its schema added to.

The SQL Server databases that support SharePoint Foundation 2010 are subject to sizing limitations and to configuration recommendations that are not standard for SQL Server.

SQL Server as a data platform for business intelligence in SharePoint 2010 Products

SharePoint Foundation 2010 can be used with SQL Server BI tools to analyze and display BI data in meaningful ways. SQL Server provides the primary data infrastructure and business intelligence platform that gives report authors and business users trusted, scalable, and secure data.

The following sections describe the technologies and features in SQL Server that support business intelligence functionality and features in SharePoint Foundation 2010.

SQL Server database engine

The SQL Server database engine is the core service for storing, processing, and securing data. BI data can be collected from the SQL Server database engine. For more information, see SQL Server Database Engine (https://go.microsoft.com/fwlink/p/?LinkId=199540).

SQL Server Analysis Services (SSAS): multi-dimensional data

Microsoft SQL Server Analysis Services (SSAS) multidimensional data enables you to design, create, and manage multidimensional structures that contain detail and aggregated data from multiple data sources. A cube wizard is available in SQL Server 2008 R2 that simplifies how you can create cubes. Dimensional data or cube data is a prototypical data source for the types of analysis that can be done by using the business intelligence-related service applications in SharePoint Foundation 2010. For more information, see SQL Server Analysis Services - Multidimensional Data (https://go.microsoft.com/fwlink/p/?LinkId=199541).

SQL Server Analysis Services: data mining

SQL Server Analysis Services data mining tools provide a set of industry-standard data mining algorithms and other tools that help you discover trends and patterns in your data. The following Excel add-ins help you perform predictive analysis:

• Table Analysis Tools for Excel provide easy-to-use tools that take advantage of Analysis Services Data Mining to perform powerful analytics on spreadsheet data. For more information, see SQL Server Analysis Services - Data Mining (https://go.microsoft.com/fwlink/p/?LinkId=199543).

• Data Mining Client for Excel lets users build, test, and query data mining models within Microsoft Office Excel 2007 by using either worksheet data or external data available through Analysis Services.

SQL Server Reporting Services (SSRS)

Microsoft SQL Server Reporting Services (SSRS) and SharePoint Foundation 2010 are easily integrated. SQL Server Reporting Services has a full range of tools with which you can create, deploy, and manage reports for your organization. It also has features that enable you to extend and customize your reporting functionality.

The available functionality includes:

• Creating reports with Report Builder 3, one of the SQL Server Reporting Services authoring tools, which you can launch directly from SharePoint Foundation 2010.

• Publishing SSRS reports in SharePoint Foundation 2010.

  You can publish report server content types to a SharePoint library and then view and manage those documents from a SharePoint site.

For more information about SSRS, see SQL Server Reporting Services (https://go.microsoft.com/fwlink/p/?LinkId=199545). For more information about how to install the different integration modes, see Documentation for SSRS reports in SharePoint (overview).

SQL Server Integration Services (SSIS)

Microsoft SQL Server Integration Services (SSIS) provides rich data integration and data transformation solutions. You can create a repeatable extract, transform, and load (ETL) process to automate moving data from sources such as XML data files, flat files, or relational data sources to one or more destinations. If data comes from disparate sources and is not mined or cleansed for the benefits that are provided in BI applications, SQL Server Integration Services helps prepare the data. For more information, see SQL Server Integration Services (https://go.microsoft.com/fwlink/p/?LinkId=199546).

Business Intelligence Development Studio (BIDS)

Microsoft Business Intelligence Development Studio (BIDS) provides intuitive wizards for building integration, reporting, and analytic solutions in a unified environment. BIDS supports the complete development life cycle of developing, testing, and deploying solutions and reports. BIDS is based on the Visual Studio 2005 development environment but customizes it with the SQL Server services–specific extensions and project types for reports, ETL data flows, OLAP cubes, and data mining structure.

PowerPivot for Excel and PowerPivot for SharePoint

PowerPivot is an add-in that enables users to create self-service BI solutions. It also facilitates sharing and collaboration on those solutions in a SharePoint Foundation 2010 environment. PowerPivot also enables IT organizations to increase operational efficiencies through Microsoft SQL Server 2008 management tools. Components of PowerPivot include the following:

• PowerPivot for Excel 2010 is a data analysis add-in that delivers computational power directly to Microsoft Excel 2010. PowerPivot for Excel (formerly known as "Gemini") lets users analyze large quantities of data, and its integration with SharePoint Foundation 2010 helps IT departments monitor and manage how users collaborate. The add-in removes the one-million-row limit for worksheets and provides rapid calculations for large data sets. For more information, see PowerPivot Overview (https://go.microsoft.com/fwlink/p/?LinkId=199547).

• PowerPivot for SharePoint 2010 extends SharePoint Foundation 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint sites. For more information, see PowerPivot for SharePoint (https://go.microsoft.com/fwlink/p/?LinkId=199547).

Master Data Services

SQL Server Master Data Services lets you centrally manage important data assets companywide and across diverse systems to provide more trusted data to your BI applications. Master Data Services helps you create a master data hub that includes a thin-client data management application for a data steward. The application can also apply workflow to assigned owners, apply extensible business rules to safeguard data quality, and apply hierarchy and attribute management strategies. For more information, see Master Data Services (https://go.microsoft.com/fwlink/p/?LinkId=199548).

StreamInsight and complex event processing

Microsoft StreamInsight is a new feature in SQL Server 2008 R2 that provides a powerful platform for developing and deploying complex event processing (CEP) applications. CEP is a technology for processing streams of events with high-throughput and low-latency. StreamInsight lets you analyze data without first storing it, and helps you monitor data from multiple sources to detect patterns, trends, and exceptions almost instantly. The ability to monitor, analyze, and act on data in motion in an event-driven manner provides significant opportunity to make more rapid, informed business decisions. For more information, see Microsoft StreamInsight (https://go.microsoft.com/fwlink/p/?LinkId=199549).

Related content