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.