SQL server 2012 – Introduction

SQL Server 2012

    Agenda:
  •  History of SQL Server
  • Editions of SQL Server
  • Server Components and Tools
  • Instance and Browser Service
  • Starting and Stopping SQL Server Service
  • Types of Authentication
  • Management Studio
  • Types of System Databases

Introduction to SQL SERVER

  • Microsoft SQL Server is an application used to create relational databases for the Microsoft Windows family of server operating systems.
  • Microsoft SQL Server provides an environment used to generate databases that can be accessed from workstations, the Internet, or other media such as a personal digital assistant (PDA).
  • Microsoft SQL Server 2012 is a full-featured relational database management system (RDBMS) that offers a variety of administrative tools to ease the burdens of database development, maintenance, and administration
  • It is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.

History of SQL SERVER

Prior to version 7.0 the code base for MS SQL Server was sold by Sybase SQL Server to Microsoft, and was Microsoft’s entry to the enterprise-level database market.

SQL Server Versions are as follows:

  • SQL SERVER 7.0
  • SQL SERVER 2000(8.0)
  • SQL SERVER 2005(9.0)
  • SQL SERVER 2008(10.0)
  • SQL SERVER 2008R2(10.5)
  • SQL SERVER 2012(11.0)

SQL SERVER Editions

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

Example: Enterprise, standard, workgroup, Express etc.

SQL Server Express Edition:

  • SQL Server Express Edition is a free, lightweight, version of SQL Server.
  • While there are no limitations on the number of databases or users supported it is limited to using one processor.
  • The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10 GB

Different Express editions in SQL Server2012

SQL Server Express (Database Only) Includes only the relational database engine.
SQL Server Express with Tools The SQL Server Express with Tools edition includes the basic relational database as well as the SQL Server Management Studio Express (SSMSE)
SQL Server Express with Advanced Services It includes SSMSE, SQL Server Data Tools (SSDT), full-text search, and the Reporting Services subsystem. The Reporting Services implementation in SQL Server Express with Advanced Services is limited to 4GB and can access only the local instance

Server Components and Tools

Components:

Database Engine: SQL Server Database Engine includes the Database Engine, the core service for storing, processing, and securing data, replication, full-text search, tools for managing relational and XML data, and the Data Quality Services (DQS) server.

Reporting Services: Reporting Services includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports.
Analysis Services: Analysis Services includes the tools for creating and managing online analytical processing (OLAP) and data mining applications.

Integration Services: Integration Services is a set of graphical tools and programmable objects for moving, copying, and transforming data. It also includes the Data Quality Services (DQS) component for Integration Services.

Full Text Search: Full-Text Search

SQL Server 2008 provides the capability to issue full-text queries against plain character based data in your SQL Server tables. This capability is useful for searching large text fields, such as movie reviews, book descriptions, or case notes. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Tools:

SQL Server Configuration Manager: SQL Server Configuration Manager provides basic configuration management for SQL Server services, server protocols, client protocols, and client aliases.

SQL Server Data Tools: SQL Server Data Tools (SSDT) provides an IDE for building solutions for the Business Intelligence components: Analysis Services, Reporting Services, and Integration Services.
(Formerly called Business Intelligence Development Studio)

SQL Server Agent: SQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs. SQL Server Agent also handles automated alerts

SQL Server Management Studio: SQL Server Management Studio SSMS is an integrated environment to access, configure, manage, administer, and develop components of SQL Server. Management Studio lets developers and administrators of all skill levels use SQL Server.

Communication between front-end and Back-end

communication
 
Instances and Browser Service
From SQL Server 2000 the capability is provided to use multiple instances of database engine on same server.
browserservice
Default Instance:
  • One instance can be defined as default instance
  • Default instance is identified by Server Name.
  • Ex: Microsoft (Microsoft is server name)

Named Instance:

  • There can be many Named Instances.
  • Named instance is identified by ServerName \ InstanceName
  • Ex: Microsoft\SQLEXPRESS
Note: If there is one instance of SQL server it can listen to default port no 1433.
SQL Server Browser Service:
  • SQL Server Browser, was introduced with SQL Server 2005, runs as a Windows service
  • Upon startup, SQL Server instances are assigned ports. The Browser Service reads the registry, finds the ports and instances and stores them
  • It listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  • The basic purpose of the SQL Server Browser service is to provide instance and port information to incoming connection requests.
  • It contributes to following actions:
Browsing a list of available servers
Connecting to the correct server instance

Starting and Stopping SQL Server Service:

Method1:
Control Panel > Adminitrative Tools > Services > Sql Server (SQLEXPRESS) > Right Click Start if not already started or stop
Method 2:
StartAll Programs > SQL Server 2012 > Configuration Tools  > SQL Server Configuration Manager > Click SQL Server Services > Select particular SQL Server Instance > Right Click > Start or Stop

Types of Authentication in SQL Server

Windows Authentication:
  • The identity of the client on the Domain of the OS / Network is used by Sql Server to allow or deny access to the resources in the database
 SQL Server Authentication:
  • The permissions to the client are granted based on the identity which was created and stored in SQL Server database.
Steps for Configuring Sql Server to support both the types of Authentication:
 
1. Start > Programs  > Microsoft Sql Server 2012 >  SQL Server Management Studio > Connect… > Right Click on Root of the Tree >  Properties  >Select Security >  Check SQL Server and Windows Authentication Mode.
2. Expand Security > Logins > Select User “sa” > Right Click – Properties > Set Password
3. Also Select Status (on left side)  Check Login Enabled.
4. Disconnect and Connect again with SQL Server Authentication so that we are sure the above steps are correct performed.
Introduction to SQL Server Management Studio
 
  • SQL Server Management Studio is an integrated environment or tool for accessing, configuring, managing, administering, and developing all components of SQL Server.
  • It has a rich graphical interface with script editors.
  • It works with all components of SQL Server such as Reporting Services and Integration Services, Analysis Service.
Types of System Databases
 
Master:
 
  • The master database contains all information about running servers configuration .It includes all of the logins, linked servers, endpoints, and other system-wide configuration settings.
  • The master database is also where SQL Server stores information about the other databases on this instance and the location of their files and records initialization information for the instance.
  • Master database is the logical repository for the system objects residing in the sys schema.
  • It holds information for all databases located on the SQL Server instance and is the glue that holds the engine together.
  • SQL Server cannot start without a functioning master database, you must administer this database with care and it is vital to make regular backups of this database.
Resource:
The resource database is hidden, read-only system database.
System objects are no longer stored in master but the Resource database from SQL Server 2005.
System objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
The Resource database does not contain user data or user metadata.
The resource database is designed to make it easy for quick database upgrades. If new system objects are being put in place, it is only necessary to swap out the resource database MDF file
Model:
 
  • Model is essentially a template database used in the creation of any new user database created in the instance.
  • You can place any stored procedures, views, users, etc. in the model database so that when a new database is created, the database will contain the objects you have placed in the model database.
 Tempdb:
  • As its name implies, Tempdb holds temporary objects such as global and local temporary tables and stored procedures.
  • This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database.
  • Tempdb also houses other objects such as table variables, results sets from table-valued functions, and temporary table indexes.
  • Because tempdb will hold these types of objects for all of the databases on the SQL Server instance, it is important that the database is configured for optimal performance.
 Msdb:
  • The Msdb database is used by SQL Server Agent for scheduling alerts and jobs
  • The msdb database stores information regarding database backups (backup history), SQL Agent information, SQL Server jobs, Database mail, and some replication information such as for log shipping
 Note:
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine.Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

INTRODUCTION TO SQL SERVER REPORTING SERVICES(SSRS)

SSRS

  • SSRS is server based software reporting platform.

  • It is used to design, develop, test, and deploy variety of interactive and printed reports.

  • Report can contain data from relational and multidimensional data sources.

  • It is administered via web interface.

 

SSRS Reporting Life Cycle Features:

Authoring

  • Reports syntax is Report Definition Language (RDL)

  • Included report authoring tools include:

  • SQL Server Data Tools(Report Designer)

  • Report Builder

Management

  • Report Manager web interface

  • Role Base security model based on Windows Authentication

  • Control over “Execution” (When reports get their data)

Delivery:

  • Web based delivery via Report Manager web site.

  • Subscriptions allow for automated report delivery.

  • URL Access, Web Services and Report Viewer control.

MSBI – SQL Server Reporting Services Introduction

ARCHITECTURE:

SSRS ARCHITECTURE

HTTP Listeners:

  • Reporting Services includes an HTTP listener that monitors incoming requests directed to HTTP.SYS on a specific port on the local computer.

  • The host name and port are specified on a URL reservation when you configure the server. When the HTTP listener processes a request, it forwards it to the authentication layer to verify the user identity.

  • The Report Server Web service is called after the request is authenticated.

Note: SSRS 2005 created virtual directories for Report Server & Report Manager (discussed next), but SSRS 2008

leverages the OS level HTTP listener making SSRS independent of IIS

MSBI – SQL Server Reporting Services Introduction 5

Authentication Layer

  • Reporting Services includes an authentication layer that verifies the identity of the user or application that makes the request.

Report server:

  • It is heart of reporting service.

  • It is implemented as windows service. It includes three applications which run in same windows service

  • Report manager, Report Server Web Service and background processing areas.

  • The report server includes two processing engines

Report processing:

  • The Report Processor retrieves the report definition or model, combines layout information with data from the data processing extension, and renders it in the requested format.

Scheduled and delivery processing:

  • The Scheduling and Delivery Process processes reports triggered from a schedule, and delivers reports to target destinations.

Report Manager:

  • It provides the main UI for SSRS

  • It is an ASP.NET web based application (http://computername/Reports) that in turn interacts with Report Server Web Services.

  • Report Manager allows you to manage reports in terms configuring security access, subscribing to them organizing them into folders (none of these folders map to physical directories but are stored as details in Report Server Database)

  • It also serves as the launch point for Report Builder.

The Report Server Web service

  • It is the core engine for all on-demand report and model processing requests that are initiated by a user or application in real-time, including most requests that are directed to and from Report Manager.

Background processing

  • It refers to operations that run in the background and are initiated by the report server.

  • Most background processing consists of scheduled report processing and subscription delivery, but it also includes report server database maintenance tasks.

Extensions:

  • SSRS uses extensions to modularize the types of input or output it accepts for authentication, data processing, report rendering, and report delivery.

MSBI – SQL Server Reporting Services Introduction

Security extensions

  • Security extensions are used to authenticate and authorize users and groups to a report server.

  • The default security extension is based on Windows Authentication.

Data processing:

  • Data Processing extensions are used to query a data source and return a flattened row set.

  • Reporting Services uses different extensions to interact with different types of data sources

  • Extension for SQL Server, OLEDB, Oracle, ODBC

Rendering extensions:

  • Rendering extensions transform data and layout information from the Report Processor into a

  • device-specific format. Reporting Services includes seven rendering extensions: HTML, Excel,

  • CSV, XML, Image (tiff), PDF, and Microsoft Word.

Report processing Extension:

  • By default, a report server can process tables, charts, matrices, lists, text boxes, images, and all

  • other report items. If you want to add special features to a report (Ex: Embedding Microsoft

  • MapPoint map), you can create a report processing extension to do so.

Delivery Extension:

  • The background processing application uses delivery extensions to deliver reports to various

  • locations.

  • Reporting Services includes an e-mail delivery extension and a file share delivery extension.

  • The e-mail delivery extension sends an e-mail message through Simple Mail Transport Protocol

  • (SMTP) that includes either the report itself or a URL link to the report.

  • The file share delivery extension saves reports to a shared folder on your network. You can

  • specify a location, rendering format, and file name, and overwrite options for the file you create.

Report server database:

There are two SQL Server databases used for internal storage.

Report Server Database(Primary Database/Persistent data storage)

  • The report server database stores all properties, objects, and metadata in a SQL Server database.

  • Stored data includes published reports, report models, and the folder hierarchy that provides the addressing for all items managed by the report server

Report Server TempDb

  • It stores temporary data, session information and caching information, work tables generated by report server.

Introduction to Sequences in SQL 2012

SQLServer 2012 introduces Sequence as new schema object which has been requested by the SQL community  and due for release since many years.

  • It is similar to sequence object of  oracle database
  • Unlike identity column sequence is not attached with table. It is  separately created
  • A sequence once created can be used with any table.
  • Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
  • Sequences, unlike identity columns can generate numeric values in an ascending or descending order at a defined interval and can be configured to restart when ever needed.
  • Sequence can also have a Data which is mostly any integer type but by default it is bigint.

How to create sequence?

CREATE SEQUENCE Test.CountBy1 START WITH n INCREMENT BY m;

GO

Where n,m can be any positive of negative integer

OR

CREATE SEQUENCE Temp.NumDesc   AS decimal(3,0)    START WITH 125

INCREMENT BY 25   MINVALUE 100  MAXVALUE 200  CYCLE;

 

How to use sequence?

To activate the sequence or to fetch  the first value

SELECT NEXT VALUE FOR Temp.NumDesc

 

To fetch current value

SELECT current_value FROM Temp.NumDesc

To insert value in a column of a  table using sequence

INSERT  Test.TestTable(CounterColumn,Name) VALUES(NEXT VALUE FOR Test.CountBy1,’Samuel’);

           Identity Sequence
Defines as part of a table It is not tied to the table
Auto generates values on insert Value needs to be generated Explicitly
Can not restart the value Can be restarted  all over again
Value can not be  Cached Can be Cached
Same value cannot be used again Same value can be used several times without regenerating new value