Andrew Fraser DBA

1 SQL Server Overview

(Part of a series of SQL Server 2012 training course notes. Contents | Next)

SQL Server was originally (1996) the Microsoft rebadged version of the Sybase relational database. It offers several advantages over Oracle:

1. SQL Server licence prices are approximately half of the equivalent Oracle licence prices.
2. SQL Server is easier to manage than Oracle, allowing for staff savings on DBAs and Sys Admins.
3. Microsoft fully support SQL Server on virtualised VMWare environments, while Oracle do not fully support Oracle on VMWare.

And disadvantages:

1. SQL Server does not offer anything equivalent to Oracle RAC (active-active clustering). There are SQL Server equivalents to dataguard and replication, and SQL Server does offer database mirroring and failover clustering (active-passive clustering).
2. SQL Server is only available on the Microsoft Windows operating system, while Oracle is available on linux.  However this is less of a disadvantage than it was in the past, with newer versions of Windows Server being stable and performant.

For a DBA, the two most noticeable differences between SQL Server/Sybase and Oracle are:

1. SQL Server has no undo or rollback segments and therefore no rollback/commit functionality, or read consistent data views; other than what little can be accommodated from the on line redo logs.
2. SQL Server architecture is: 1 host machine to 1 or more Instances to many Databases. There is no such thing as a tablespace within SQL Server. On line redo logs exist at database level.

A more complete list of differences is given in the appendix.

Note that SQL commands are not executed by ; or / as in oracle, but with go.

SQL Server is mostly managed from the SQL Server Management Studio (similar to Oracle Enterprise Manager – and was called Enterprise Manager in older versions).

The exception is the optional components (below) are instead managed from the SQL Server Business Intelligence Development Studio (which is a cut-down version of Microsoft Visual Studio).

Stopping and Starting SQL Server is done with SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2012 > Configuration) or, less correctly, services.msc.

SQL Server has optional components which come at no extra cost (if hosted on same machine):

• SQL Server Reporting Services – Business Intelligence reporting, Microsoft’s alternative to OBIEE, Discoverer, Crystal Reports. Has very good integration with Microsoft client products such as Excel which is a significant for end-user usability.
• Analysis Services – OLAP (on line analytical processing), cubes, data mining.
• SQL Server Integration Services – ETL (extract transform load) and export/import tools. Is equivalent to export/import, data pump, sql loader, DBA shell scripts. This was called DTS (Data Transformation Services) in older versions.

Version History:

(Part of a series of SQL Server 2012 training course notes. Contents | Next)