What is SQL Server – SQL Tutorial

Microsoft SQL Server Tutorial

SQL Server is a Relational Database Management System (RDBMS) from Microsoft.  It is also known as MS SQL or simply SQL Server. It is an Enterprise level database application just like Oracle, DB2 or MySQL. As such it is able to store and manage millions of data records for any small to large corporation. There are several different versions of MS SQL in production, the most common ones are SQL Server 2005 and SQL Server 2008. If you want to try a free SQL Server version for 180 days so you can kick the tires, please visit this Microsoft site about
SQL Server Announcements and Featured Downloads

Historically speaking, SQL Server was developed as collaboration between Microsoft and Sybase. SQL Server version 4.2 was available in 1993 for Windows NT. The first version of MS SQL developed completely by Microsoft was SQL 6.0 and subsequently SQL 6.5. In 1998, Microsoft came up with SQL Server 7 which has a big improvement over 6.5, followed shortly by SQL 2000. After taking a long break, Microsoft pretty much built the next version MS SQL 2005 from the ground up. As such it had major changes in architecture and a vast improvement in performance plus security issues. Next version of Microsoft SQL Server 2008 was available for production in 2008. The latest product SQL Server 2012 is scheduled to release in March 2012.

SQL Server is a massive product with many moving parts and pieces. In is article, we are going to look at a handful of the important ones in the order of their importance. If you are interested in learning MS SQL, please visit our partner sites at SQL Server 2008 Tutorial and SQL Server Azure Tutorial. If you are looking for the original content we had on SQL Server, you can find them here on our MSSQL 7 Legacy page.

What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio is by far the most important tool in SQL Server. It is used for SQL Management in addition to development of objects like MS SQL databases, tables, stored procedures, logins and SQL jobs. In order to launch SSMS you can follow this path:

Programs > Microsoft SQL Server 2008 > SQL Server Management Studio

Here is a screen shot of What SQL Server Management Studio looks like on our computer, by the way we are using MS SQL 2008 R2 version:

sql server management studio

If you are a visual person like me, you can watch FREE SQL Server Management Studio tutorial along with sample training video on SQL Server at this location.
Kash Data Consulting

Important SQL components within SSMS are:

Databases: Using this icon/folder, you can create objects like MS SQL databases, tables, views, stored procedures, functions, triggers and constraints. Moreover you can manage users and database roles within the particular database. You will be spending most of your time in MS SQL right here, guaranteed!

Security: Within this folder, you have the ability to create new logins (user accounts with access to the actual MSSQL Server). You can also create server level roles (similar to Windows groups), setup  security audits and other safekeeping mechanisms.

Server Objects: Here you can create backup devices, endpoints (point of entry to a SQL Server), Linked Servers (non SQL providers that can interact with MSSQL) and Triggers.

Replication: This is the ability of SQL to synchronize data between a primary and a secondary server(s). With replication, you can configure items like publishers, subscribers, publications , articles, etc.

Management: You can perform SQL Server management tasks like data-tier applications, policy management, setup maintenance plans; look at SQL Server logs, setup Database mail and configure Full Text Search within this location

SQL Server Agent: Vital component in SQL 2008 component for database administration. You can manage SQL instance, create jobs, alerts, operators, proxy accounts and also review SQL Server agent logs for troubleshooting purposes.

A visual representation of the MS SQL Agent is as follows.

what is MSSQL Agent

SSQL Server Configuration Manager (SSCM)

This is another important management tool for SQL Server. You can control SQL Server services, Server protocols, limit Client level protocols, client aliases and other surface area configuration pieces. You can get o SSCM by doing the following:

Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configure Manager

I have included a screen shot of SSCM from our MSSQL server:

sql server configuration manager

Options under SSCM

Here are the available options under SSCM:

SQL Server Services:

With this, you can control items like MSSQL Server service (database engine), SQL Agent service, SQL Server Integrations service (SSIS) and SQL Server Reporting service (SSRS).Check out the SQL Server videos on YouTube that cover some of these topics

SQL Server Network Configuration:

Here you can configure protocols (network communication standards) for connection to Microsoft SQL including TCP/IP, Named Pipes and Shared Memory

SQL Native Client Configuration:

Just like on the server side, you can configure protocols at the client side. In addition you can setup alias to connect to the SQL Server.

Import and Export data:

Using the SQL Server import and export wizard (SSIEW), you can move data to and from SQL Server. Microsoft SQL supports a number of different providers like Oracle, IBM DB2, flat file (.txt), Office applications, OleDB and ODBC compliant drivers. SSIEW walks you through all the necessary steps and at the end will create an Integration  Services (SSIS) package which can be either saved to MSSQL or a standalone SSIS package file in Windows.

We will cover Integration Services under Business Intelligence Development Studio.

What are SQL Server Performance tools?

It’s just a fact of life, sooner or later your SQL Server database will grow and grow and run into performance issues. No offense to your technical know-how!! It may be a combination of higher volume of data, poor database design or inefficient Transact SQL queries. In order to solve the problem, SQL Server provides you the following two excellent tools right out of the box:

SQL Profiler:

This little tool is a lifesaver utility in SQL Server. It can capture every single SQL Command, stored procedure and database process that you are running on SQL Server. In a sense, you create a trace which stores a chronological list of every event on SQL Server. You can then open the trace and review it later for performance tuning and query analysis. Here is what the SQL Profiler looks like on our end:.

sql server profiler trace

Database engine tuning advisor (DETA)

This next performance tuning tool is also an excellent utility that can help you find not only missing indexes but show you how to improve existing indexes in SQL Server. First you have to either create a SQL Profiler trace or a TSQL script, next you need to be loaded it up into database engine tuning advisor which will then make its recommendations.
I found an excellent video on how to use DETA at is location on youtube so you can check it out .

 

What is SQL Server Business Intelligence Development Studio (BIDS)?

When you install SQL Server on your box, you will also be given Business Intelligence Development Studio. This is a cut down version of Visual Studio and allows you to create projects on Integration Services (SSIS), Reporting services (SSRS) and Analysis Services (SSAS). Further details on each of these topics can be found right below. You can launch BIDS by doing this:

Programs > Microsoft SQL Server 2008 > SQL Server Business Intelligence Development Studio

Like we mentioned in the Import and Export data section, SQL Server Integration Services is an ETL (Extract, Transform and Load) Tool that can move data back and for between MSSQL and other data providers. In addition you can customize a SSIS package to do data cleansing and manipulation as part of the data transformation.
We cover a basic way to create a SQL Server at Integration package in this sample training video.




Here is what a SSIS package looks like in BIDS. We are loading a flat file (.txt) into SQL Server.

what is ssis integration services

SQL Server Reporting Service

(SSRS) is a full blown reporting solution from Microsoft. SSRS uses RDL (Report Definition Language) and XML to create and manage reports. You can use BIDS or Business intelligence development studio to create a report.  You can deploy it to the Reporting Server which will process reports and handle end-user transaction. It has the ability to allow users to not only pull SQL reports but also push reports on demand at a fixed schedule.

This is what an SSRS report looks like in BIDS.

 

how to create sql server reporting service

SQL Server Analysis Services

(SSAS) is part of the business Intelligence set of tools from Microsoft. SSAS will let you perform Online Analytical Processing (OLAP) which lets you create multi-dimensional cubes. SSAS also enables its users to do Data Mining which lets you analyze massive data by different attributes. This will uncover patterns and useful information that may not be readily available from an Online transactional Processing (OLTP) system.
More information on SSAS can be found here:

Miscellaneous topics on MS SQL:

What else is there in MSSQL? you ask. Well a lot! SQL Server has many more components and features that we have not even mentioned. Here are a few important ones to consider as you discover MSSQL Server:

SQL Server Failover Clustering:

This provides High Availability support for the entire SQL Server instance. First you create Windows Server Failover cluster and then add one or more nodes to it.

Database Mirroring:

This is a High availability solution in SQL Server at the database level. In case of a database failure, the database is switched from the Principal (active) to the Mirror Server. In this manner, there is minimal interruption from the data consumer point of view. You can take a look at this Database Mirroring video on YouTube.

SQL Replication:

Ability to synchronize data between different MSSQL databases. This is a popular option for mobile Sales professionals who are on the road and can sync with the master database on as needed basis. You can setup on of these types: Snapshot, Merge and Transactional replication.

Transact SQL (TSQL):

This is the flavor of Structured Query Language (SQL) for Microsoft. You can manipulate your data using commands like SELECT * FROM TABLE. Our partner has more than 60 TSQL scripts to help you be a better SQL DBA or SQL Developer! Here is what a SELECT query looks like in SSMS. You enter the command in the top pane and your get the query results in the bottom pane of Management Studio.

what is sql server command

Sqlcmd Utility:

Sqlcmd is a command line utility in SQL Server. You can use it to execute TSQL statements, stored procedures and other script files from the command prompt. This is useful when you use batch files or run atutomated processes like setting up a new server.

Using ADO.net programming with SQL Server:

In order to create a Window or Web application that is driven by SQL Server, you can use ADO.net. This is one of the many ways you can connect to the database server. Choices are to either use Visual Basic (VB) or C Sharp (C#). :
You can get further information from this excellent webpage.

Services:

Using Windows Services manager, you have ability to Start and Stop MS SQL Services. You can get to this by either typing in services.msc in the Start menu or by following this path

Control Panel > Administrative Tools > Services

We have highlighted the services in the following figure. You can simply right click on the ones you want to control and select Stop/Start.

how to start stop MS SQL service

SQL Server Related Links:

SQL Server Home Page
MSSQL Architecture and components
SQL Server Central – Excellent resource on MSSQL topis
How to implement Security in MS SQL 2008?

If you would like more information about SQL Server then the link below is a useful one to look into.
SQL server resources





Connect with US!

Our YouTube channel