1.4 Main components and services

1.4 Main components and services

SQL Server is not just a database concept, but a complete business intelligence solution in addition to the database engine. Business intelligence also requires three additional services for SQL Server: Integration Services (SQL Server Integration Services, abbreviated SSIS), analytics Services (SQL Server Analysis Service, SSAS), report clothing SQL Server Reporting Services, referred to as SSRS.

1.4.1 Instances

An instance can be thought of as the largest container in SQL Server, with each instance containing its own set of components and services. As shown in.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5A/0A/wKiom1T0KKuiC7YSAAG7ooHucTg941.jpg "title=" SQL component. png "alt=" wkiom1t0kkuic7ysaag7oohuctg941.jpg "/>

SQL Server 2000 begins to support installing multiple instances on a single computer. SQL Server 2008 (Enterprise Edition) and subsequent versions can install up to 50 instances on a single computer.

Each instance must have a unique name. Depending on the type of instance name, it can be divided into the following two class instances.

If you choose to install "default instance" when you install SQL Server, you do not need to provide an instance name. SQL Server uses "MSSQLSERVER" as the default instance name by default, but the client does not need to refer to the name when connecting, simply by referring to the computer name.

If you select named instance at installation time, you need to specify a name for the instance. You can have a maximum of one default instance or all named instances per computer.

Scenarios that use multiple instances are:

Different instances correspond to different service levels (e.g. response time, no downtime).

Different instances correspond to different versions of SQL Server. This is often used in version upgrades.

There are different database administrators and different users, allowing them to manage and access only a subset of the databases.

Different instances correspond to different configuration options.

1.4.2 Database engine and peripheral components

The database engine is the core service that SQL Server uses to store, process, and protect data. Use the database engine to create a relational database for OLTP (online transaction processing) or OLAP (Online analytical Processing). This includes tables that store data and database objects (such as indexes, views, and stored procedures) that are used to view, manage, and secure data.

Tips:

OLTP The English name is on-line Transaction processing , is a transaction-oriented processing system, is the main application of the traditional relational database.

OLAP The English name is On-line Analytical Processing , is the core of the data Warehouse, supports complex analysis operations, and focuses on providing intuitive and understandable query results for decision support.

SQL Server 2008 and subsequent versions canceled the notification service (Notification services), instead of the service Broker. This is an important peripheral component that includes the ability to provide Message Queuing and message passing. Message Queuing can hold queries and other requests. Message passing allows asynchronous communication between database applications. You can prioritize your sessions to ensure that messages are processed in a reasonable order.

Replication (Replication) is used to distribute data across multiple databases, as a solution to improve database availability and as a way to read offsite databases.

In SQL Server 2005, full-text search is still a peripheral component, which is an OS-level service that the database engine accesses through an interface. Full-text SQL Server 2008 has integrated it into the database engine, called a full-text index (Full-text index).

1.4.3 Analysis Services

In an OLTP relational database, a relationship can be viewed as a two-dimensional table consisting of rows and columns that can be roughly understood as an Excel table.

SQL Server 7.0 provides OLAP functionality and introduces cubes for fast access to data in the Data Warehouse.

SQL Server 2000 provides data mining capabilities that allow users to define a variety of multidimensional models in OLTP or OLAP, with grouping and forecasting rules in the model. Complex data analysis is achieved by accessing the cube using Multidimensional Expressions (multidimensional expression, or MDX) language.

SQL Server 2012 introduces a tabular data model for Analysis Services. Unlike multidimensional models, tabular data models still organize data into tables that contain rows and columns, which are very similar to relational databases. Tabular models Access data using a data analysis expression, called DAX. In fact, the tabular data model has been introduced in the SQL Server R2 PowerPivot for Excel and PowerPivot for SharePoint.

SQL Server 2012 introduces the business Intelligence Semantic model (BI Semantic model), which creates a unified model in SSAS and PowerPivot for Excel, combining powerful multidimensional analysis techniques with common tabular data models to enable analytical model creation The flexibility to build and consume.

SQL Server 2012 also provides statistical semantic search. It is based on the word of the full-text search feature, but allows you to query "meaning" from unstructured documents beyond the keyword search category.

SQL Server 2014 adds Power View for multidimensional models.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/0A/wKiom1T0Kk_QiimvAAIFbaJpmJw921.jpg "title=" Analysis service. PNG "alt=" Wkiom1t0kk_qiimvaaifbajpmjw921.jpg "/>

1.4.4 Integration Services

Integration Services can help you build a comprehensive enterprise analytics solution with familiar tools for feasibility analysis.

SQL Server 7.0 's DTS (Data Transformation Services) provides a set of tools to extract, transform, and merge data from different sources into one or more destinations.

SQL Server 2005 and subsequent versions replaced DTS with SSIS. SSIS can extract, transform, merge data from multiple heterogeneous data sources, and move or copy to one or more destinations.

The SQL Server 2014 SSIS is not different from the version of SQL Server 2012.

1.4.5 Reporting Services

Microsoft launched a report plugin for SQL Server 2000 in 2004, and users can download and install them from the official website.

SQL Server 2005 integrates the SSRS Setup program into the SQL Server installation CD. Since then, SSRS has formally become a core service of SQL Server.

SQL Server 2008 improves the processing power and performance of SSRS so that large reports no longer consume all available memory. It also includes a TABLIX that spans tables and matrices. Application embedding allows the user to click on the URL link in the report to invoke the application.

At this point, SSRS implements a perfect combination with Microsoft Office 2007. For example, SSRS can export a report directly to a Word document. And with the report Authoring tool, Word and Excel can be used as templates for SSRS reports.

Based on the trend of Reporting Services, the following SQL Server R2 supports SQL Azure, SQL Server PDW, SharePoint list, PowerPivot as a data source. The map feature is also added to the report. And launch a more powerful Report Builder 3.0 reporting designer.

Power View is available in SQL Server 2012. This is a model that can be run on a remote database reporting client and can be invoked on a SQL Server database through a Web service. Power View follows the ribbon design style and supports similar interaction styles with Microsoft Office Excel, allowing you to set up a variety of filters, highlighting, and full-screen display capabilities. You can easily export data reports to a PowerPoint page, notifying you that publishing reports to a SharePoint 2010 server is supported. Power View is a Microsoft-based Silverlight technology platform and must be deployed in integration with SharePoint 2010 Enterprise Edition.

The SQL Server 2014 Reporting Service does not differ from the version of SQL Server 2012.

This article from "SQLServer2014 series" blog, declined reprint!

1.4 Main components and services