What is SQL Server
(c) 2012 kevin languedoc (klanguedoc)
SQL Server is a database server developed and marketed by Microsoft. OLAP server, database engine, reporting server,data warehouse, data mart, multidimensional database, analysis services, analytical services. These are all terms that are associated with SQL server. They are are all true since SQL Server is the foundation of Microsoft’s Business Intelligence platform. The platform has a relatively low of cost of ownership when compare to other similar platforms like Oracle and can provide great insights into an organization's data.
SQL Server, in its current iteration (2012), has also introduced Hadoop to its architecture in order to better manage Big Data. Another addition to this multi purpose data storage and analytical platform is the availability and possibility of using and accessing SQL Server through the cloud, which is another way of describing the Internet but as a private network.
These series of books are among some of my favorites as they provide excellent how-to information along with theoretical information to understand the different features of SQL Server 2012
SQL Server is defined as RDBMS which means Relational Database Management System is its primary mandate is to store and organize data from different sources. It is so called because its main database engine organizes data in normalized tables for retrieval by allowing a dba or developer to create relationships between these tables. In addition to tables, views can be create a subset of the data for faster retrieval or to reduce to overall size of the data set.
Starting with version 2005, SQL Server includes an operating system that is called SQLOS. This operating system is a technological layer that is aware and takes advantage of the underlying hardware’s concurrency and partitioning.
SQL Server’s main query language is Transact-SQL or T-SQL. This query language fully implements the ANSI SQL specification with several additions of its own. T-SQL is a modern SQL language and allows queries in SQL a well as in XML using XQuery. Several specialized functions have be added over the years to take advantage of the Microsoft Windows, Office and .Net platforms that are Microsoft staples.
SQL Server supports most of the key BI and Data Management concepts including Data Marts. A data mart can be defined as a single source database. This means that the database is being populated from a single source which can be a custom web or native (pc or mobile) application. SQL Server supports data marts through its main database engine.
Data can be inserted and retrieved using a variety of means and technologies native connectors, Microsoft .Net integration, ODBC, Web services, Messaging (Transactional), linked databases servers (all main RDBMS’), DDE, amongst others.
A data warehouse is a database system that stores data from multiple sources. Data warehouses are the mainstay of business intelligence. In SQL Server a warehouse can contain data from multiple sources and stored in a database. A warehouse can also include cubes from the Microsoft Analytical Services (SSAS), the schemas of Microsoft SQL Server Integration Services (SSIS). The latter can also be stored on the file system.
Brian Larson really knows SQL Server Reporting Services. This book is the second edition which speaks for itself. Brian offers excellent advice and how-to information on configuring and using SQL Server Reporting Services.
Since SQL Server 2005, a reporting server is available with the server and sqlos. The Microsoft SQL Server Reporting Services includes a special IIS server that is integrated with the sqlos can be installed when the server is installed or afterwards by running the installation services. Report are built using Microsoft Report Builder which is a free download from the Microsoft web site or with the Microsoft Visual Studio or Microsoft Business Intelligence Studio which is a special implementation of Visual Studio. Reporting Services offer pixel perfect rendering of data in real time either through the Report Builder or from a web page which is hosted on the SQL Server Report server on a corporate network or in Azure, which is a Cloud based SQL Server that is hosted by Microsoft Data Centers.
The reports are stored in a special purpose database along with connection configuration details, reports parts, datasets, data sources and any special visualization components. These reports are available in native form or through Sharepoint as a shared service.
The SQL Server Analysis Services are OLAP cubes. They are stored in a special database that encompases the Data Warehouse architecture. These services are included in the Microsoft Business Intelligence platform.
Microsoft SSAS offers all the same capabilities as other leading BI solutions including metrics and aggregates, measures and attributes. The SSAS platform includes a state of the art OLAP server and the MDX (Mutli Dimensional Expressions) which is a language similar in syntax to T-SQL (SQL) but has been designed to query cubes by leveraging rows and columns of data.
Cubes are designed in the Microsoft Business Intelligence Studio or Visual Studio (Not Express) and are then published to the SQL Server Data Warehouse. You can write MDX queries from the SQL Server Management Studio by connecting to the Integration Services on a particular server if they are installed.
The SQL Server Integration Services are a set of special components that allows developers to build connections to other SQL Server databases or other business systems like Dynamics or SAP, even Oracle business applications and RDBMS’. Using Visual Studio, you can define a data source(s) to other systems and configure components to extract data from these systems, transform that data and store this data in a SQL Server database, Excel or other RDBMS’ or business systems. These integrations are deployed to the SQL Server on the file system or stored in a special purpose database on the server and available through the Integration Services in SQL Server Management Studio.
O'Reilly's The Definitive Guide series are among some of the best IT books available. This includes this book by Tom White.
Big Data is a relatively new term in data management. It consist of the large volumes of data that are stored in various public and private RDBMS’ in Data Centers round the world. To handle Big Data, Microsoft has implemented the Hadoop architecture and technology from Apache. Hadoop allows data to be organized in large clusters on disparate hardware systems. the core of the architecture arrangements data with a map/reduce technology that allows data to be accesses in a node in the cluster. Hadoop allows for petabytes of data to be accessed transparently from the various nodes in the cluster.
With the release of SQL Server R2, Microsoft released a new integrated analytical service called PowerPivot which creates cubes from flat relational data in real time. PowerPivot can be used with Sharepoint (web based) or Excel (native or web) or even from the Cloud.
SQL Server is a powerful data management platform and is one leading business intelligence solutions on the market today. It is consistently ranked in the Leader Quadrant of the Gartner Magic Quadrant.