Hi Friends, Today we are going to discuss about Database Core Components\Tools of Sql server and purpose for those. It’s a very basic and introductory information which I am going to provide here. But we’ll discuss each component in details in upcoming sessions.
So let’s start.
The Database Engine component of SQL Server is the core service for storing, processing, and securing data. This is the component which actually execute queries and process the data. This component secure data by storing it in various data files.
By installing database engine, you can create new databases on that instance and store your data. We cal this as relational engine also. Sql is a descriptive language so it passes the query to the engine and then engine is responsible to handle all relational work.
Whenever we connect to any database using tools like management studio for queries, we actually connect to database engine.
SQL Server Agent
This is the component which use to take care of all automated tasks and jobs. It maintains list of all Sql jobs, their tasks and alerts. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.
Sql server agent is a window service which runs in background. We can configure this service to start on windows startup.
SQL Server Agent Components:
- Jobs: A job is a specified series of actions that SQL Server Agent performs.
- Schedules: A schedule specifies when a job runs.
- Alerts: An alert is an automatic response to a specific event.
- Operators: An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server.
SQL Server can notify operators of alerts through one or more of the following:
- Pager (through e-mail)
- net send
Database mail component is specifically for sending mails. There could be need of sending mails as alerts in case of any scheduled task failure. So one can configure SMTP and database mails, so that it can send automated alerts.
Not only automated alerts but one can send mails through T-Sql code also. The mail can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.
MSDTC (aka Microsoft Distributed Transaction Control) is a software or we can say it’s a windows service which is used by SQL Server and other applications when these want to make a distributed transaction between more than one machine\servers. This uses dual phase commit protocol.
When one server starts a transaction and this transaction include few statements which needs to be executed on another server and we want to secure this complete batch by one transaction, then MSDTC comes into picture. It tracks all success\failure of all statements and commits the entire batch if all statements are succeeded.
Business intelligence (BI) is a package or set of tools that enable the management of data for the purpose of analysis, reporting, mining, and visualization.
SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (RS), and Analysis Services (AS).
This is integration service which one can use for integration of data from various sources. It has a visual interface which we can use to design packages.
We can say SSIS as ETL i.e. Extract-Transform-Load tool. We can design package where it can fetch data from other source, file etc. and then perform the transformation according to requirement and then load to another system\file.
As it has visual interface so it becomes very easy to perform all these transformations\load rather than T-Sql.
Reporting Services (RS) for SQL Server 2012 is a full-featured, web-based, managed reporting solution. RS reports can be exported to PDF, Excel, or other formats with a single click and are easy to build and customize.
One can schedule these to pre-created and cached for users, e-mailed to users, or generated by user on-the-fly with parameters
There is new functionality in SSRS 2012 with the addition of Power View. This is a SharePoint integrated feature that provides for rich drag and drop visualization and data exploration. It is one of the hottest new features in SQL 2012.
The Analysis Services service hosts two key components of the BI tool set: Online Analytical Processing (OLAP) hosts multidimensional databases where data is stored in cubes, whereas Data Mining provides methods to analyze datasets for nonobvious patterns in the data.
Building cubes in a multidimensional database provides a fast, pre-interpreted, flexible analysis environment. One can include robust calculations in a cube for later query and reporting. We can use these results to reporting or pivot table later.
One can use data mining to explore non trending patterns and specific patterns based on various algorithms. This can be very useful to perform data analysis so that business can get information about specific data patterns and make business strategies according to that analysis.
In addition to above components, there are few tools which are also very popular and very much used to worth discussing about.
This is a tool which gives you visual interface for servers, databases, tables, schema etc. One can connect to any database engine and query.
This is very useful tool which is used by almost all those who involves in database work either developer or DBA.
We can use this tool to start and stop any server, set the start-up options, and configure the connectivity. One can launch it from the Start menu or from Management Studio. It can show you all the services and servers running on a particular server.
It looks something like below image.
Sql profiler is a visual tool which can track almost all events on any server. You can check queries, execution reports etc. One can use it for debugging\troubleshooting and performance purpose.
Profiler is being deprecated in favor of extended events.
When we install Sql server, it installs many performance counters. We can see these counters in Windows’s administrative tools. One can use these counters to check Sql server performance in terms of memory usage, I\O etc.
DB Engine Tuning Wizard
The Database Engine Tuning Adviser analyzes a batch of queries (from Profiler) and recommends index and partition modifications for performance. Database Engine Tuning Adviser examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.
Command Line Utilities
Sql provides few command line utilities to perform specific tasks through command. Following are few examples.
One can run this command and provide query to execute on database. Database connection will be provided as parameters in this utility.
sqlcmd -d AdventureWorks2012 -q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"
The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. One can use BCP utility to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
Below is the sample to export query data into file.
bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\BCP\People.txt -t, -c –T
Above is very basic information about major tools and components. In upcoming sessions we’ll discuss more details about each of these.