SQL Server Versions Explained

The product SQL Server from Microsoft has been released over the years in several different versions. The original version was released for the IBM PS/2 machines under OS2 environment. This MS SQL Server version was released back in 1989 and was jointly developed by Microsoft along with Ashton Tate and Sybase on the Sybase’s SQL Server product. Versions followed in 1991, 93, 95, 96 and 98. Each had improved features than the previous MS SQL Server version and they were based on the original codebase. Significant changes to the product, happened with the 2005 version. Versions that were released prior to 2005 are 99, 2000, 03 beyond the 98 version. The 93 version was released for the WNT. The product then on has been compatible with Windows operating systems. The 99 version of the SQL Server was significant too. It introduced business intelligence features (in addition to the RDMS features) to the MS SQL Server database. The 2003 version of the MSSQL Server was fully 64 bit version released for working with the newer computer systems that use the 64 bit processors and the operating systems. By the time the 2005 version came to the market, the code base of the original product was replaced by MS developed code. The MSSQL Server was completely owned by Microsoft since then and Sybase has been selling a similar product with a similar name built on their own codebase.

Quite a few useful features were built into the product SQL Server 2005 when it came to the market. An IDE or an integrated development environment was included that helped development of database applications on the SQL Server product. On the data analysis and business analysis side of the product, ETL capability, OLAP and data mining features were added.  The ETL (extract-transform-load), etc. functionality helps with loading of large amount of data into the database. OLAP and data mining functionalities helps with the BI angle of the MS SQL Server product. Data mining feature helps with discovering trends from available data. Many such trends may not be visible to the naked eye easily. XML support also came with this Microsoft SQL Server version. The T-SQL was upgraded to add support of XML (Xquery). Data mirroring introduced from MS SQL Server 2005 helps higher availability. This made manual or automatic failover operations possible.

Product release/versions introduced since 2005 were in 2008, 2010, 2012 and 2014. The latest version of Microsoft SQL Server is MSSQL Server 2016. The community technology preview CTP) is already out. The general release can be expected to be in couple of months’ time. Besides the regular upgrade in version in 2010 (SQL Server 2008R2), a cloud version was released. Azure SQL database was released in 2010. Web services support also was added in this version. Version 2008 was a significant release as this introduced unstructured data support. For BI, unstructured data is something that needs to be handled most (up to 80%) of the time. “Filestream” is a type that supports unstructured and semi-structured data. Binary large objects or “BLOB” was another type that helped support multimedia data. In-memory execution available for some time helps quick execution of larger analytics applications work much faster.


SQL Server Integration Services (SSIS)

SQL Server Integration Services or the SSIS component available with the SQL Server product can facilitate a large range of data migration services for the user. The SSIS 2012 component will take care of a wide diversity of data integration as well as workflow automation applications. It will facilitate setting up data warehouses for ETL (or data extraction, transformation and loading) applications. The SSIS component is useful for automatic maintenance and update of OLAP cubes (and hyper-cubes). OLAP, as we know is an on-line analytic processing of 3 or more dimensional data. OLAP processes can help users underlying data patterns and insights. Due to the large volume of these multi-dimensional data, patterns may not be visible easily otherwise.

Data migrations are an integral part of upgrades to enterprise systems. This may involve transfer of data from one storage type to another, format changes or moving of an application to the next generation of computing equipment. This SSIS component could be a huge facilitator in all such cases. Tools available in SQL Server Integration Services let you the migration to be completed automatically and without errors. Many different situations can call for such migrations. Technological changes, server or the storage technology upgrade or a simple reason like the need for relocating a data center. Services available in SSIS 2012 can be very useful in any such situation.

Data migration, typically, involves the so called D-T-L or the data extraction, transformation (if, and as required) and then loading into the new system. SQL server tool SSIS can be useful in each of these stages. There is a data verification stage that is mandatory establishing that the data, as loaded, is error free. Tools available in SSIS 2012 can be useful for these tasks. In a typical migration process, each of these stages may have to be repeated. It may be essential to run the legacy as well as the new system simultaneously for some time until the SSIS 2012 can demonstrate that the process has been successful. The SQL Server Integration Services tool is now available only with the standard, BI and enterprise editions from the version 7 (1998) release. It used to be available for all editions earlier. One of the most useful features on the SSIS must be the import/export wizard. It is a simple transfer tool for transfers that do not involve any transformation. The SQL Server Integration Services can handle multiple source and destination types. On the development front, the SSIS provides BI development studio or BIDS. This is based on Microsoft’s VB studio. Developers can develop and debug package with drag and drop ease of the VB like features. A package defines a workflow and can have many elements. BIDS have since been updated in SSIS as the SQL Server data tool-Business intelligence (SSDT-BI). Connectors, event handlers, parameters, precedence constraints, takes, and variables are some features that are useful in working with the SQL Server Integration Services (SSIS). There are some additional tools in the integration services.

Executing packages from command prompt and setting up of configurations for packages to reused are possible. Storage of a package and retrieval are possible with these tools.


SSRS is a Great Reporting Tool

SQL Server comes with several tools named services. The SQL Server Reporting Services (SSRS) is one such tool that lets you compose reports exactly the way you need them. The suite of services provided by Microsoft also includes an Analysis services module and integration services module. Creation and management of a wide variety of these reports is made possible through this server based reporting service or the SSRS. The types of reports that can be created include the basic one that contain data tables and associated charts. Complex data visualizations are made possible by the SQL server reporting services. The complex reports may contain charts, maps and sparklines. Sparklines make more sophisticated visualizations possible and they help add additional illustrative small charts to data in a chart already. This reporting service tool can handle these easily. All these reports can be generated in different formats. The data used for the reports could not only e SQL Server data but can also come from other RDMSs. SQL server analysis services can be a source as could be Parallel data warehouse or even Teradata. The reporting service can handle XML based data sources too.

The reports generated by the SQL server reporting services can be included in the applications you develop. These could as easily be viewed on the report manager or the report services website. The required reports in reporting services can be generated as required basis. These could be scheduled as subscriptions too. This component of SSRS can be arranged to install on SharePoint.

Originally SQL Server Reporting Services (SSRS) used to be shipped as an adjunct to the main SQL Server product. It was first released more than 10 years ago in 2003. From the SQL Server release 2005, the services have been completely integrated. The SSRS was enhanced significantly in the 2008 and 2008 R2 versions. These new set of features enhancements include enhanced charts, data regions, a new gage feature, formatting of text an introduction of a Tablix feature. This lets a report area to be converted to matrix and the other way round. A complete overhaul of the report designer was done at this release. This redesign introduced a grouping pane in the interface. Basic features of this reporting services have not changed since this 2008 R2 release.

A Power View feature was added, however, with the SQL server reporting services release 2012. Power View provides a new way of creating reports that business users need. A SSRS installation needs to be associated with a database instance, of course. A ReportServer and a ReportServerTempdb exist in such an instance. ReportServer holds quite a few details about reports created. These include definitions of the report, its configuration, security and history, etc. On the other hand ReportServerTempdb provides a workspace for building of reports. A Report Server Web Service will need to be hosted. End users can publish their own reports when they have permissions. The sequence of events in publishing a report starts with a http request to the web server. The reporting services server retrieves metadata related to the report and requests data from the data source and returns a complete report when data is available to it.


SQL Server 2012: overview of SQL Server 2012


Microsoft’s RDBMS product for desktops and enterprise is the SQL Server and SQL Server 2012 is the version released to the market in 2012. The SQL Server 2012 evolved from the 2005 version. The SQL server code started out to be jointly owned by Microsoft and two other companies named Ashton Tate and Sybase. It was released as the desktop product on PS/2 with OS/2 environment back in 1989. By the time several upgrades were released to arrive at the sql server version 2005, Microsoft has own version of the code contributed by Ashton Tate by this time. The 2012 version evolved through 2008, 2008 R2 versions to become the SQL Server 2012.

The SQL Server 2012 grew into the SQL Server 2014 version and the latest product from Microsoft stable is the community technology preview 2016 that has just been released recently. The sql server 2012 adds two major trends to the feature list that was available with the previous 2008 R2 version.

  1. One is to make the existing RDBMS product easier to administer and to simplify development work.
  2. Equipping the products to help in ever larger data handling and analysis is the other set of goals. The SQL Server 2012 adds many such important features to the existing repertoire.

With the SQL Server 2012, Microsoft decided to support only ODBC (open database connectivity). Even the interconnectivity between MS document types supported through OLE was to be dropped. The general release of the SQL Server 2012 came in Mar, 2012 followed by release of Service pack 1 in Nov 2012 and the pack2, in Nov, 2014. The added features include a PowerView. When creating business intelligence reports, you can mash up various data sets in SQL Server 2012 through this feature. Operationally, the AlwaysOn feature adds handling of database failures easier. Multiple copies created by the system in SQL Server 2012 helps make disaster recover quite straightforward. There is even a stripped down version which is not as resources hungry as the full version of the SQL Server 2012 but can let you carry out the most important operations. Programming features enhanced in the SQL Server 2012 include a ColumnStore indexes feature. These are read-only indexes that help optimizing processing queries on large data warehouses. Testing of the SQL Server 2012 procedures and queries can be played out (tried out) on a non-production server without risking crashes affecting your production operations. Operationally, an audit trail is a very important tool for systematic management of the database operations but a security safeguard against malicious activities, if any.

By this point in the development of the SQL Server product, SQL Server 2012 has evolved into an enterprise level DBMS with the ability to handle large volume of enterprise level data, supported by the administrative, program development and security features required for efficient operations. The next upgrade to SQL Server 2012 came in 2014.

If you have any questions, please contact us at SQL Server Tutorial 2012; we are always happy to help!


Follow us on: