ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

What is SQL Server

Updated on December 10, 2012
Source

(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.

Introducing Microsoft SQL Server 2012
Introducing Microsoft SQL Server 2012

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

 

RDBMS

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.

Transact-SQL

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.

Data Mart

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.

Data Warehouse

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.

Microsoft SQL Server 2012 Reporting Services 4/E
Microsoft SQL Server 2012 Reporting Services 4/E

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.

 

Report Server

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.

Analysis Services

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.

Integration Services

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.

Hadoop: The Definitive Guide
Hadoop: The Definitive Guide

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

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.

PowerPivot

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.

Comments

    0 of 8192 characters used
    Post Comment

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      5 years ago from Canada

      Thanks Kaili

    • Kaili Bisson profile image

      Kaili Bisson 

      5 years ago from Canada

      I always learn something from your Hubs Kevin...Hadoop was a new one for me. Voted up!

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      5 years ago from Canada

      Thanks dwachira I appreciate the great feedback

    • dwachira profile image

      [ Danson Wachira ] 

      5 years ago from Nairobi, Kenya

      Hi Kevin,

      I believe the analytical platform capability of SQL Server will bring more added value especially at this age when cloud computing is the in-thing in many organizations. Great article on SQL Server. Voted up and useful.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)