"Bi Thing" SQL Server 2008 Architecture

In the original: "Bi Thing" SQL Server 2008 Architecture

Microsoft SQL Server is a platform that provides database and data analysis for online transaction processing, data warehousing, e-commerce applications.
Architecture is a way of describing the relationship between components and elements of a system. The architecture of the Microsoft SQL Server System is a description of the main components of Microsoft SQL Server and the relationships between these components.
The Microsoft SQL Server 2008 System consists of 4 major parts, which are referred to as 4 services, namely the database engine, Analysis Services, Reporting Services, and integration services, which are the relationships between the existence and application of these services to one another.
SQL Server 2008 Architecture

Database engine
The database engine (SQL Server ENGINE,SSDE) is the core service of Microsoft SQL Server 2008, responsible for the storage, processing, querying, and security management of business data.
For example, creating a database, creating tables, performing various data queries, accessing databases, and so on, are all done by the database engine.
In most cases, using a database system is actually using the database engine.
For example, in an airline ticket sales information system that uses a Microsoft SQL Server 2008 system as a background database, the database engine service of the Microsoft SQL Server 2008 system is responsible for completing the addition, updating, deletion, Query and security control, and other operations.

Functional components
The database engine itself is a complex system that includes many functional components, such as service Broker, replication, full-text search, Notification Services, and so on.
Service Broker provides an asynchronous communication mechanism that can be used to store and deliver messages.
Replication refers to the technology of replicating and distributing data and database objects between different databases, ensuring synchronization between databases and data consistency. Replication is often used for data distribution between servers in different physical locations, and it can be distributed to remote or mobile users in different locations over LAN, WAN, dial-up connections, wireless connections, and the Internet.
Full-Text Search provides enterprise-class search capabilities based on keywords.
The notification service provides a notification-based development and deployment platform.

Analytics Services

Analytic services (SQL Server analysis Services,ssas) provides OLAP and data mining capabilities that enable users to build data warehouses.
In contrast to OLAP, OLTP is the responsibility of the database engine. With SSAS services, you can design, create, and manage multidimensional structures that contain data from other data sources, and analyze multiple perspectives of multidimensional data to support a more comprehensive understanding of business data by managers.
In addition, by using SSAS services, users can complete the construction and application of data mining models to realize knowledge discovery, representation and management.
For example, in an airline's ticket sales information system, you can use the SSAS service provided by the Microsoft SQL Server 2008 system to perform data mining analysis on customers, uncover more valuable information and knowledge, and provide effective support for reducing customer churn and improving customer management levels.
The main components of the analysis service are business Intelligence development Studio (Bids). Bids is a management tool that provides an integrated platform for Integration Services, Reporting Services, analytics services, and data mining. Visual Studio 2008-based bids enables users to develop business intelligence applications that allow users to write code, debug, and version control in the platform.

Analysis Services Overview	SQL Server 2008 contains a separate program for SQL Server 2008 Analysis Services that you can use to perform OLAP analysis on SQL Server and other OLE DB databases.
Create an Analysis service project	With Analysis Services, you must first create an Analysis service project. An Analysis service project contains data sources, data source views, cubes, dimensions, mining structures, roles, assemblies, and miscellaneous 8 subfolders, each of which holds the appropriate objects.
Create a cube	There are two ways to create a cube: The first is to define a dimension that is independent of any cube, and then define a cube based on those dimensions, and the second method is to use the Cube Wizard to create cubes and related dimensions.
Process and browse cubes	Processing a cube causes the analysis service to pre-calculate aggregations that contain data in the cube.
Add Filter criteria	When you browse a cube, you can add some filtering criteria to the displayed data to more accurately analyze the data.
Using the Data Mining Wizard	Use the Data Mining Wizard provided by the Analysis service to perform mining analysis on the current cube.

Reporting Services

Reporting Services (SQL Server Reporting Services,ssrs) provides users with enterprise-level reporting capabilities that support the Web.
By using the SSRS services provided by the Microsoft SQL Server 2008 system, users can easily define and publish reports that meet their needs.
Whether it's a report layout format or a report's data source, users can easily implement it.
This kind of service greatly facilitates the management work of the enterprise, satisfies the management personnel to be efficient, the standard management demand.
For example, in an airline's ticket sales information system, the SSRS services provided with Microsoft SQL Server 2008 systems make it easy to generate reports in the form of Word, PDF, Excel, and so on.

Reporting Services Overview	SQL Server 2008 Reporting Services (Reporting Services,ssrs) provides a variety of out-of-the-box tools and services to help database administrators create, deploy, and manage reports for their organization, and provides programmatic capabilities to extend and customize reporting capabilities.
Designing a report using the Report Wizard	Creating a report using the Report Wizard is a more common and simple approach that is accomplished in Microsoft SQL Server 2008 Tools Business Intelligence Development Studio (BIDS).
Using Report Designer	Although you can use the Report Wizard to quickly create reports that meet the needs of most users, there are some limitations to creating complex or free-form reports. In this case, you can use Report Designer to design your report on your own.
Designing parameterized Reports	After you create a report, to further refine the data that is displayed in the report, you can control the data information that is contained in the report by adding parameters to the report. In SQL Server 2008, you can use the input values to maintain the report, which is called a parameterized report.
Configuring the Report Server	Configures the contents of the current report server.
Publish a report	After you design the good one report, you need to publish the report to a report server that is already set up for other people to view.
Manage reports	In Report Manager, administrators can manage reports that have been published, such as creating folders, deleting files, and transferring files.

Integration Services
Integration Services (SQL Server integration services,ssis) is a data integration platform that can be used to extract, transform, load, and so on.
For example, for an analysis service, the database engine is an important data source, and how the data in the data source is properly processed and loaded into the analysis service for various analytical processing is the problem that the SSIS service solves.
Importantly, the SSIS service can handle a wide variety of data sources efficiently, and in addition to Microsoft SQL Server data, it can handle data from data sources such as Oracle, Excel, XML documents, text files, and so on.

SSIS is the short name for Microsoft SQL Server 2005/8 Integration Services and is the building of high performance data integration solutions.
Including data extraction, conversion, loading of a platform, referred to as ETL. This platform is mainly used in business intelligence, to extract data suitable data to the data warehouse of a tool, of course, can also be used in the general system, data import, export and so on.
As the saying goes: "A decade to sharpen a sword", Microsoft through 5 years of careful creation, in 2005, the strong introduction of SQL Server 2005, this is since the SQL Server 2000 after another masterpiece. This enterprise-class database solution includes the following aspects: Database Engine Services, data mining, Analysis Services, Integration Services, Reporting services, Among them, integration Services (that is, SSIS), is their transit, the link between the various sources of data, through the ETL to the data warehouse, the establishment of a cube, and then analysis, mining and the results through the reporting Services Delivery to enterprises at all levels of users, for the planning decision-making, supervision and implementation escort.

Now a lot of people have put SSIS as an ETL (extract-transform-load) tool, I personally feel not too accurate, perhaps everyone basically put him as ETL use, in fact, SSIS has surpassed the function of ETL, ETL is only one of them, It also has a very prominent performance in other areas:

In the context of database maintenance	Database backup; statistical information Update; Database integrity check; Index rebuild SSIS package execution; SSAS task processing.
Business Processing	Execute the SQL task. Web Service task.
Operating system Maintenance	WMI Event Watcher Task File system tasks.
Other	Execute SQL Task Perform process tasks ActiveX Script Task Script Task (vb/c#). Perform a Web service service

Especially the 4th above, can execute SQL task, can execute the Web service service, can execute system process, can execute (vb/c#) Script task, which gives us much imagination space, what is the exception? Strong AH. Have to admire the service.

SSIS (SQL Server Integration Services) is an embedded application for developing and executing ETL (unzip, transform, and load) packages. SSIS replaces the SQL2000 DTS. The Integrated services feature includes both the wizard-directed plug-ins, tools, and tasks necessary to implement a simple import and export package, as well as a very complex data cleansing capability. The functionality of Sqlserver2008ssis is greatly improved and enhanced, such as its execution procedures are better executed in parallel. In SSIS2005, a data pipeline cannot span two processors. The SSIS2008 can span two processors on a multiprocessor machine. And it has improved performance in handling bulky packages. The SSIS engine is more stable and has a lower deadlock rate.
The lookup feature has also been improved. Lookup is a common feature of SSIS to get relevant information. For example, to find CustomerName from CustomerID, get the DataSet. Lookup is common in SSIS and can handle millions of rows of data sets, so performance can be poor. SQL2008 makes great improvements to the performance of lookup and can handle different data sources, including Ado.net,xml,oledb and other SSIS compression packages.

SSIS Application Scenarios
SSIS application is mainly in some business intelligence large data system, the data extraction, cleaning, to obtain high-quality data, enterprises in the analysis of the time to analyze the accurate results. SSIS is also used in daily data management, such as DBA management of databases, database job management, database migration, merging, and so on.

Architecture of SSIS
Consists mainly of four parts: the Integration Services service, the Integration Services object model, the Integration Services runtime and runtime executables, and the Data Flow task () that encapsulates the data flow engine and data flow components ():

"Bi Thing" SQL Server 2008 Architecture