|
|
|
What is SQL Server?
|
|
|
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.
http://msdn.microsoft.com/en-us/sqlserver/bb671149.aspx
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 Managment Studio looks like on our computer,
by the way we are using MS SQL 2008 R2 version:
|
|
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.
http://www.learningsqlserver2008.com/faq.htm
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.
|
|
|
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:
|
|
Options under SSCMHere 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.
http://www.youtube.com/watch?v=Jl0Vze2_6_o&feature=results_main&playnext=1&list=PLFB15F100105284A1
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:.
|
|
|
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.
http://www.youtube.com/watch?v=gjT8wL92mqE
.
|
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.
http://www.youtube.com/watch?v=Jl0Vze2_6_o
Here is what a SSIS package looks like in BIDS.
We are loading a flat file (.txt) into SQL Server.
|
|
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.
|
|
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:
http://www.sql-server-performance.com/2009/intro-ssas/
|
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 Managment
Studio.
|
|
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:
http://www.developerfusion.com/article/4278/using-adonet-with-sql-server/
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
.
|
|
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.
http://vyaskn.tripod.com/sqlserverres.htm
|
|
| |
| |
