| SQL Server 2008
is the newest relational database management system from Microsoft. It
delivers a powerful, reliable and scalable enterprise solution in the high end
database platforms. SQL Server 2008 extends the functionality of SQL Server
2005 with new features like data compression and policy based management. It
enables you to store data from structured and unstructured documents, such as
images and music, directly within the database. SQL Server 2008 delivers a
rich set of integrated services that enable you to do more with your data such
as query, search, migrate, synchronize, report, and analyze.This video
series takes a practical approach towards working with SQL Server 2008. It is
organized and presented by a senior level database professional with more than
10 years of Industry experience. The videos not only cover DBA (database
administrator) topics like performance tuning, routine maintenance, job
scheduling, backing up and restoring databases, monitoring and profiling but
also discusses database development in detail. The development area focuses on
tasks like creating tables, views, functions, stored procedures, relationships
and ERD (entity relationship diagrams) plus TSQL (Transact SQL) samples. We
have also made an effort to discuss Industry wide Best Practices along the way
as they relate to SQL Server. This video series consists of more than 6 hours
of professional instruction. The actual videos come in either DVD format (AVI
files) or FTP format (WMV files) that will run on Windows Media Player or Real
Networks Real Player. Finally included in these videos are PowerPoint
presentations to help you in the learning process. The files can be downloaded
from one of our FTP sites. We will give you ftp site info after auction close.
This content is produced by and property of http://www.learningcomputer.com
Installation of SQL Server 2008
In this video we walk you through the process of installing SQL Server Evaluation Enterprise
edition on Windows Vista. We talk about the Hardware and Software
requirements; next we show you the software download location on Microsoft’s
website. In the beginning, it installs the .NET framework and updated windows
installer. After the reboot, we continue onto an Install wizard screen with
additional resources and tools. We go over the Installation Center, Setup
Support Rules, Feature selection and do a stand-alone installation. On the
feature dialog box, one can select different SQL Server services to install.
We recommend using Best Practice settings for service accounts; data file and
log file locations. After the final steps, the machine is rebooted and we are
all set at this point. This video is in two parts due to the necessary Reboot.
(Running times are 11 and 23 mins)
SQL Server 2008 Configuration Manager
We go over
Configuration Manager in SQL Server 2008. This tool is primarily used for two
things: Configuring SQL Server services and also managing the Network
Protocols. We jump into the demo and cover starting, stopping and restarting
different services like Database Engine, Agent, SSIS (SQL Server Integration
Services), Browser etc. We talk about Best Practices of using a domain service
account and also bring up startup mode settings. As far as Network protocols
are concerned, we go over server and client configurations. TCP/IP is the
preferred protocol due to ease and frequency of use. You can enable and
disable protocols and their ports here as it relates to security. Important
point is that the server and clients are using the same settings, whatever you
may decide to use. (Running time is 17 mins)
SQL Server Management Studio (SSMS):
SSMS is an all
inclusive tool for managing SQL Server. It is equally important for database
administrators and developers. We start off by mentioning important commands
on the File and Toolbar menu. Next we discuss how to register a server and
export the server list. We tackle the mighty Object Explorer that can connect
to different database services and is at the heart of SSMS. In object explore,
everything is laid out in a hierarchy of folders which are split by function.
When you are working with database, it trickles down to tables, field and
properties etc. We mention important tabs like Security, Server Objects,
Replication and Management. Finally we finish the lesson with Vanilla
Reporting capabilities, scripting options and long awaited Intellisense.
(Running time is 32 mins)
Working with Databases in SQL Server 2008
In this
training session, we cover database related topics in SQL Server 2008. We
discuss some best practices regarding database management in general. The
first demonstration shows you how to create a database using SSMS. Next we go
over some of the database options and settings like File location, Auto Shrink
option, Recovery model etc. We switch gears and create a database using
Transact SQL code. A nice feature in SQL Server is the ability to generate SQL
scripts that help with administration tasks. We execute the auto generated
code to create another database. Commands like ALTER DATABASE, sp_helpdb,
sp_dboption are explained next with some examples to change database settings.
Template Explorer is a time saver when it comes to unfamiliar code. We create
a database using Template Explorer and end this video with some highlights of
the Help section. (Running time is 33 mins)
Security in SQL Server 2008
There are many
layers of Security in SQL Server 2008, some of which are Physical, OS, Surface
Area, User Permissions, Domain Access, File and Folder Security. We discuss
Principals which are entities that can request SQL Server resources. At the
server level there can be Windows or SQL Server principals. At the database
level, there can be user, role or application principals. Next we talk about
different type of roles: Server or Database. Server roles are always fixed
like the sysadmin role. Database roles can be fixed like dbo or flexible that
can be user defined. We also talk about TSQL statements and Catalog views that
related to security. The demos include creating a SQL Server login which is
assigned to database roles. Next we assign the user access to a securable like
Table and test different permission scenarios. (Running time is 28 mins)
Transact SQL in SQL Server 2008
If you want to
be good at working with databases, strong knowledge of TSQL is a must.
Transact SQL is the universal database language. In this video, we will use
AdventureWorks2008 which is a sample database from Microsoft. We start off
with the SELECT statement which is used to return data. This is by far the
most commonly used SQL statement. The basic syntax of a SQL statement is
discussed with examples from Person and SalesOrderHeader tables including some
best practices. A quick trick to copy a table is using SELECT * INTO which is
shown next. We mention logical and standard operators. Demo on INSERT
statement is used to populate data in the Person table. The next two
statements are UPDATE and DELETE which are very powerful in nature. Using a
WHERE clause is Extremely important with these two. We show you how to
change data in Person table. Funny story regarding Delete statement (Running
time is 32 mins)
SQL Server Administration, Maintenance and Job
Automation
Regular
maintenance is necessary as your databases grow over time. Along with that,
job automation helps you focus on other responsibilities where SQL Server 2008
is able to handle routine administration tasks on its own. First off, we
discuss concepts like Events, Alerts, Operators, and Jobs. In the initial demo
we go over SQL configuration manager as it relates to SQL Server services.
Next we cover SQL Server Agent and demonstrate you how to create an alert for
when Transaction Log is full. This alert in turn sends a notification to the
operator and also kicks off a SQL job ‘Backup AW Log’. We discuss job steps in
detail and the TSQL code involved in these jobs. Next we go over Job Activity
Monitor and view Job History to see if the jobs completed successfully. We end
this lesson with Database Mail and Maintenance Plans. Using maintenance plan,
you can easily configure regular maintenance tasks. (Running time is 46 mins)
Backup and Restore plus Compression in SQL Server 2008
As a DBA, doing
backups is of utmost important especially in my position. We start off by
going over some terminology like backup, restore, full, differential, log,
filegroups and compression. Next we look at the various types of backups
available in SQL Server. There are essentially three ways to do backups in SQL
Server 2008, Management Studio, TSQL and Maintenance Plans. Using sample
database AdventureWorks2008, we modify some data and do Full, Differential and
Log backups one by one. We also show you how to do backups using TSQL code. We
go over the long overdue Compression feature in SQL 2008. After backups, the
next item is how to Restore (Recover) the database. Once more there are
various ways to restore the database and we illustrate you that using
demonstrations. We end the lesson with some backup strategies and backups
using maintenance plans. (Running time is 38 mins)
Entity Relationships in SQL Server
A database is
not much without relationships between tables. We kick off this video by going
over ERD (Entity Relationship Diagram) which displays a database structure or
schema. We tackle terms like Entity (table), Attribute (field), Record (row),
Primary and Foreign keys. We will use AdventureWorks2008 again as the source
of our data. We discuss 1-1, 1-many and many–many relationships. In the demos,
we give you an idea about you how to create a database diagram, create a table
with a primary key and establish a relationship with a foreign key. We use
SSMS and TSQL to go over these concepts. Joins are of prime importance to deal
with database relationships. We bring up Inner and Outer Joins and go over
Sales Order and Customer data using joins. We finally end the lesson with
Referential Integrity. (Running time 28 mins)
Monitoring SQL Server 2008
Monitoring and
Profiler your database server is a critical tasks especially if you are the
DBA. We kick off this LONG video by looking at Task Manager, Event Viewer and
Performance Monitor. We look at some system counters that are specifically
geared towards SQL Server. Next we look at some canned reports that give you
vital information at the server and database level. We use the demo on
Activity Monitor so get a feel for server activity. Moving onto Error Logs,
you can troubleshoot errors in Database Engine and SQL Agent. In addition to
graphical tools, one can use TSQL and DMV’s to get vital stats. We end this
training session with the most important monitoring tool, the Profiler. We
show you how to create a template, change events and columns. In order to see
this in action, we create two SQL scripts that select and update customer data
on AdventureWorks2008. (Running time 68 mins)
Views, Functions and Stored Procedures
We start off
this video with SQL view which is nothing more than a virtual table. It is
primarily used to simplify complex transact sql and can also be used as a
security mechanism. The example presented here includes data from Employee
table in AdventureWorks2008 database. Next we talk about functions which help
in processing and returning data back to the calling program. We use the AVG
function to build our user defined function on Sales Order and product data.
Finally we go over stored procedure which is the preferred option for SQL
programming. They help in faster execution and code reuse and encapsulation.
We combine the database mail feature with stored procedure to generate a list
of terminated employees and send the list via an email. (Running time 45 mins)
Visit our YouTube channel here
http://www.youtube.com/user/baghul
|
