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

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes the relationship between Microsoft SharePoint Server 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 Server 2010.

For more information about the supported versions of SQL Server, see Hardware and software requirements (SharePoint Server 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 authoring and publishing tools for business intelligence

SharePoint 2010 Products and the SQL Server database engine

SharePoint Server 2010 is an application that is built on the SQL Server database engine. Most content and settings in SharePoint Server 2010 are stored in relational databases. SharePoint Server 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 Server 2010, see Database types and descriptions (SharePoint Server 2010).

Working with the SQL Server databases that support SharePoint 2010 Products

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

Microsoft does not support directly querying or modifying the databases that support SharePoint Server 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 Server 2010 are subject to sizing limitations and to configuration recommendations that are not standard for SQL Server. For more information, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).

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

SharePoint Server 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 Server 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 Server 2010. To learn how relational and multi-dimensional data helps users analyze data, see Data warehousing, OLAP, and Analysis Services for SharePoint 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 Server 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 Server 2010.

• Publishing SSRS reports in SharePoint Server 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 Server 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 Server 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 Server 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).

SharePoint Server 2010 authoring and publishing tools for business intelligence

The following are authoring and publishing tools in SharePoint Server 2010 that contribute to how to create KPIs, scorecards, dashboards, and reports. Each of the tools can link to SQL Server relational and multi-dimensional data. To learn more, see Data warehousing, OLAP, and Analysis Services for SharePoint 2010. For more information about the services and to see how each tool uses SQL Server data, see Architecture for business intelligence in SharePoint Server 2010 and Choose the right business intelligence technology to suit your style (white paper).

• Excel Services   Use Excel 2010 and Excel Services to view, refresh, and interact with analytic models connected to data sources. Also use them for analysis, filtering, and presenting locally stored data. Excel 2010 is the authoring tool. Excel Services lets you publish Excel 2010 files to SharePoint Server 2010.

• Visio Services   Use Visio Services to build a visual representation of business structures that are bound to data. Examples include creating visual processes, systems, and resources that show visual performance. For example, an engineer can use the visualization to create data-bound objects to represent a process.

• PerformancePoint Services   Use PerformancePoint Services to create dashboards, scorecards, and key performance indicators (KPIs) that deliver a summarized view of business performance. PerformancePoint Services gives users integrated analytics for monitoring, analyzing, and reporting.

• Web Analytics service application   Use the Web Analytics service application to understand more about visits to the SharePoint sites. The Web Analytics service application collects data about how end-users access SharePoint pages.

Related content