Introduction to SQL Server – Best Dot Net Training
SQL Server 2012
- 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
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:
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
- One instance can be defined as the default instance
- The 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
- 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:
Starting and Stopping SQL Server Service:
Types of Authentication in SQL Server
- 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
- The permissions to the client are granted based on the identity which was created and stored in the SQL Server database.
- 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.
- 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.
- 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.
- 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.
- 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
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.
