SQL Server Important Info

Microsoft SQL Server is a relational database management system developed by Microsoft.

Microsoft SQL Server's primary query languages are T-SQL and ANSI SQL.

The different versions of SQL Server: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users.

Mainstream editions

Datacenter

SQL Server 2008 R2 Datacenter is the full-featured edition of SQL Server and is designed for datacenters that need the high levels of application support and scalability. It supports 256 logical processors and virtually unlimited memory. The Datacenter edition has been retired in SQL Server 2012, all its features are available in SQL Server 2012 Enterprise Edition.

Enterprise

SQL Server Enterprise Edition includes both the core database engine and add-on services, with a range of tools for creating and managing a SQL Server cluster. It can manage databases as large as 524 petabytes and address 2 terabytes of memory and supports 8 physical processors.

Standard

SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes.

Web

SQL Server Web Edition is a low-TCO option for Web hosting.

Business Intelligence

Introduced in SQL Server 2012 and focusing on Self Service and Corporate Business Intelligence. It includes the Standard Edition capabilities and Business Intelligence tools: PowerPivot, Power View, the BI Semantic Model, Master Data Services, Data Quality Services and xVelocity in-memory analytics.^[41]

Workgroup

SQL Server Workgroup Edition includes the core database functionality but does not include the additional services. Note that this edition has been retired in SQL Server 2012.

Express

SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 4 GB database files (10 GB database files from SQL Server Express 2008 R2). It is intended as a replacement for MSDE. Two additional editions provide a superset of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic.SQL Server Express with Advanced Services adds full-text search capability and reporting services.

Specialized editions

Azure

Microsoft SQL Azure Database is the cloud-based version of Microsoft SQL Server, presented as software as a service on Azure Services Platform.

Compact (SQL CE)

The compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size (1 MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4 GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes supports ADO.NET Synchronization Services. SQL CE does not support ODBC connectivity, unlike SQL Server proper.

Developer

SQL Server Developer Edition includes the same features as SQL Server 2012 Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server. This edition is available to download by students free of charge as a part of Microsoft's DreamSpark program.

Embedded (SSEE)

SQL Server 2005 Embedded Edition is a specially configured named instance of the SQL Server Express database engine which can be accessed only by certain Windows Services.

Evaluation

SQL Server Evaluation Edition, also known as the Trial Edition, has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop.

Fast Track

SQL Server Fast Track is specifically for enterprise-scale data warehousing storage and business intelligence processing, and runs on reference-architecture hardware that is optimized for Fast Track.

Parallel Data Warehouse (PDW)

A massively parallel processing (MPP) SQL Server appliance optimized for large-scale data warehousing such as hundreds of terabytes.

Database Engine

**    The Database Engine is the core service for storing, processing, and securing data. 

**    The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise.

**    Use the Database Engine to create relational databases for online transaction processing or online analytical processing data. This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data. 

**     SQL Server supports up to 50 instances of the Database Engine on a single computer.

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.  

SQL Server databases have three types of files: (mdf, ndf, ldf)

Primary data files

The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.

Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf.

Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

Logical and Physical File Names

SQL Server files have two names:

logical_file_name

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

SQL Server data and log files can be put on either FAT or NTFS file systems. We recommend using the NTFS file system because the security aspects of NTFS. Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system.

**   A maximum of 32,767 databases can be specified on an instance of SQL Server  


**   The master database should be backed up whenever a user database is created, modified, or dropped.


**   When you create a database, make the data files as large as possible based on the maximum amount of data you  expect in the database.

Comparison of Netezza, Teradata, Exadata

Criteria		Appliance Name
		Netezza	Teradata	Exadata
Architecture Highlights		Scalability, Parallelism	Scalability, Parallelism	Scalability, Parallelism
Parallelism Concept		AMPP1	SMP2 & MPP3	MPP
Hardware		HP	NCR	HP Proliant DL180 G5
Network Interface		Gigabit Ethernet (1 GB/sec)	BYNET	InfiniBand (16 GB/sec)
Software	OS	Linux (Redhat)	Windows, Linux and NCR UNIX	Oracle Enterprise Linux
	RDBMS	Porstgre based	Teradata Database	Oracle
Query Processing	Managed by	Hardware	Virtual Process (Software)	RDBMS Software
	Key Processing Unit	FPGA4	VPROC5 (AMP6)	as Oracle
	Executed	completely in Disk	virtually in disk	Both in Disk and Database server
Scalability		Linear Scalable	Linear Scalable	Linear Scalable
Failover		Built-in	Built-in	Built-in
Availability		Built-in	Built-in	Built-in
Usage of Index		Not used	Extensively used	moderately used
ETL7		ELT8	ETL	ETL
Migration Effort		High as new architecture	High as new architecture	Comparatively less effort
Administration		Less	Less	More than Netezza and Teradata

  1- Asymmetric Massively Parallel Processing

  2- Symmetric Multi Processing

  3- Massively Parallel Processing

  4- Field Programmable Gate Array 

  5- Virtual Processor

  6- Acess Module Processor

  7- Extract, Transformation, Load

  8- Extract, Load, Transformation

Database Warehouse Appliance Basics

Data warehouse appliance consists of

o  An integrated set of servers, storage, operating system(s), DBMS and software specifically pre-installed and pre-optimized

o  Specific recommended hardware configurations

o  Offering terabyte to petabyte range.

Appliance Technology

o  Most DW appliance vendors use Massively Parallel Processing (MPP) architectures

o  MPP architectures consist of independent processors or servers executing in parallel.

Benefits

o  Parallel Performance

o  Reduced Administration

o  Built-in high Availability

o  Scalability [Adding servers increases performance as well as capacity]

o  Service [One number to call]

o  Reduction in costs

Traditional Multiprocessing Architecture

§  SMP (Symmetric Multi Processing)

§  MPP (Massively Parallel Processing)

SMP (Symmetric Multi Processing)

o  SMP systems consist of several processors, each with its own memory cache.

o  Load is balanced across the processors.

o  Unable to move large amounts of data as required in data warehousing and business intelligence applications.

MPP (Massively Parallel Processing)

o  Consist of very large numbers of processors.

o  Each processor has its own memory, backplane and storage, and runs its own operating system.

o  The no shared-resources approach of pure MPP systems allows nearly linear scalability.

o  High availability– when one node fails, another can take over.

o  Main objective is to take the performance and scalability advantages of MPP while reducing costs and administration time.

Both SMP and MPP have major drawbacks:

Requires massive data movement.

Multiprocessing Variations

·     Large Scale SMP

·     MPP on Clustered SMP

Large Scale SMP

-                 Larger SMP systems with additional processors and shared memory are available that deliver much higher computing power.

-                 As processors take turns accessing massive amounts of data in memory, the memory bus becomes a bottleneck that results in poor performance.

MPP on Clustered SMP

( Used by Teradata and the IBM DB2 Integrated Cluster Environment (ICE))

 -                 Small SMP clusters operating in parallel

-                 Sharing a storage area network and management structure.

-                 The resource-sharing built into this approach imposes a bottleneck that limits performance and scalability.

Memory-Storage Architecture used by data warehouse

-                 Three architectures used today by well-known data warehouse solutions.

o  Shared nothing (data not shared disk not shared) MPP

o  Separate data, shared storage MPP

o  Shared data and storage MPP

-                 All three are based on a hybrid combination of MPP on SMP clusters, but vary in sharing data and storage resources between MPP nodes.

Shared nothing MPP:(Used by Teradata)

-                 Major weakness: it requires significant data movement from disks to processors for BI queries.

Separate data, shared storage:

(Used by IBM in DB2 database management system)

-                 Drawback: Significant data movement from disks to processors

 Shared data and storage:

-                 multiple processors operating in parallel shared data residing on a common storage system

Netezza

Key Terms:

-   AMPP (Asymmetric Massively Parallel Processing)

-   Intelligent Query Streaming^TM Technology

-   FAST Engine^TM - FPGA Accelerated Streaming Technology

  

Netezza Architecture:

-   Uses AMPP (Asymmetric Massively Parallel Processing) – combination of MPP and SMP

-   Netezza’s AMP architecture is a two tiered system-

o  The first tier is a high performance Linux SMP host.

o  The second tier consists of thousands of SPU (Snippet Processing Unit) operating in parallel.

-   Each SPU consists of –

o  Processor

o  Dedicated memory

o  A disk drive

o  A FPGA 

-   FPGA is the heart of Netezza’s patented streaming architecture. By using FPGA, less computational stress is put on CPU and memory of each SPU.

-   Sorts, joins, aggregates are executed primarily on the SPUs, but can also be done on the host, depending on the processing cost of that operation.

-   Additional arrays of snippet processing units can be added to the NPS system without impacting performance.

Intelligent Query Streaming^TM Technology:

o  “Bringing the query to the data” approach is used.

o  Greatly reduces data traffic among SPU nodes and between SPU nodes and SMP host.

o  Intelligent Query Processing is performed on each SPU by a FPGA

FAST Engine^TM - FPGA Accelerated Streaming Technology- provides performance acceleration

     -   For each snippet there are two elements that are distributed to the SPUs:

§  The compiled snippet file

§  A set of FPGA parameters

-  FAST engines are customized at run-time (enables the FPGA to incorporate parameters passed to each engine to optimize the behavior of the FPGA for a particular query snippet) for each snippet executed in the SPU.

-   Five current FAST engines in the framework include

o  Control engine (do ZoneMap acceleration)

o  Parse engine (do Error-Correcting Code (ECC) checking and correction)

o  Visibility engine (Provide ACID (Atomicity, Consistency, Isolation and Durability) )

o  Project engine (filters based on the “SELECT”)

o  Restrict engine (filters based on the “WHERE”)

    All engines work in combined serial and parallel manner.

Example Query

SELECT count ( * ) , sex , age

FROM emp

WHERE state = ‘VA’ and age > 18

GROUP BY sex , age

ORDER BY age ;

Latency barrier with NPS

-    Integrates all major ETL tools from vendors including- Ab Initio, Ascential, Informatica, Sunopsis

-    With a data transfer speed of up to 500 GB/hour.

-    Customers have the flexibility to convert the portion of ETL process to ELT process. 

-    Fast Backups: The 500 GB/hour (2 TB/hour compressed) data transfer

-    Streaming Query processing: Rather than “moving data to the query”, the NPS system takes a
     different approach: “moving the query to the data”

Redundancy

-   Each processing and storage modules includes a data mirror.

-   If the system detects an error , or a processing/storage module stops responding, it deactivates the failed module

o  The system regenerates data on to a hot spare module from mirrored data, a process known as failover.

o  If a hot spare module is not available or fails, the system continues processing at a reduced performance by directly using the mirrored data. Note that, depending up on the system model, there are between four and sixteen hot spare modules available.

Strength

-   Performance: 10x to 100x faster query performance than traditional data warehouse

-   Effortless scalability

-   Flexibility: designed to grow with the easy addition of modular processing units for scalability &
     reconfiguration.

-   Reliability: Fully ACID and RAID compliant to secure maximum structural reliability.

-   No indexing required.

-   No tablespace & redo log sizing and configuration

-   No journaling/logical log sizing and configuration

-   No page/block sizing and configuration for tables

-   No extent sizing and configuration for tables

-   No temp space allocation and monitoring

-   No maintenance of OS recommended path levels

-   No software to install

Teradata

-   Based on SMP technology combined with a communication network connecting the SMP systems to 

     form a MPP system

-   All processors in a same SMP node are connected by a virtual BYNET.

-   The Teradata Database doesn’t rely on the hardware platform for parallelism, scalability, 

     reliability, or availability.

      -   A single processor node is connected to one or more disk arrays with the following installed on the   

          node:

o  Database software

o  Client interface software

o  Operating system

o  Multiple processors with shared-memory

o  Failsafe power provisions

 BYNET

o  Hardware inter-processor network to link nodes on an MPP system.

o  BYNET provides a node-to-node data transfer bandwidth of 120 MB/sec and can linearly scale, supporting up to 512 nodes on a single system.

              

  

PDE (Parallel Database Extensions)

Parallel Database Extensions (PDE) software provides the ability to:

•      Execute vprocs

•      Run the Teradata RDBMS in a parallel environment

You can start, reset, and stop the PDE on Windows systems using the Teradata MultiTool utility and on UNIX MP-RAS systems using the xctl utility.

Virtual Processors

-   There are two types of vprocs:

                        §  PE (Parse Engine)

§  AMP (Access Module Processor)

PARSE ENGINE

-   The PE performs session control and dispatching tasks as well as parsing functions

-   The Parsing Engine (PE) is the vproc that communicates with the client system on one side and with the AMPs (via the BYNET) on the other side.

      -   Each PE executes the database software that manages sessions, decomposes SQL statements into steps, possibly parallel, and returns the answer rows to the requesting client.

      -   The PE software consists of the following elements:

                  o  Parser

o  Optimizer

o  Generator

o  Dispatcher

o  Session Control

  

ACCESS MODULE PROCESSOR (AMP)

-   Heart of the Teradata RDBMS.

-   Retrieve and update data on the virtual disks (vdisks)

      -   A single system can support a maximum of 16,384 vprocs.

-   The maximum number of vprocs per node can be as high as 128.

      -   AMPs control the management of the Teradata RDBMS and the disk subsystem, with each AMP 

           being assigned to a virtual disk (vdisk).

      -   Each AMP provides the following set of functions:

o  Database management

§  Locking

§  Joining

§  Sorting

§  Aggregation

§  Output data conversion

§  Disk space management

§  Accounting

§  Journaling

o  File-system and disk management

§  Each AMP manages a portion of the physical disk space.

§  Each AMP stores its portion of each database table within that disk space.

How Teradata works

Both hardware and software provide fault tolerance, some of which is mandatory and some of which is optional.

   

  Software fault-tolerance:

o  Vproc migration

o  Fallback tables

o  AMP clusters

o  Journaling

o  Archive/Recovery

o  Table Rebuild Utility

 Hardware Fault-tolerance:

o  Multiple BYNETs

o  RAID disk units

o  Multiple channel and LAN connections

o  Isolation from client hardware defects

o  Battery backup

o  Redundant power supplies and fans

o  Hot swap capability for node components

o  Cliques