SQL Server Tutorial using SQL 2008 - Video Training DVD

In this video tutorial we walk you through the process of SQL Server 2008 installation on Windows Operating System. 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 and SQL Server New Features 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 for installation. (Running times are 11 and 23 mins)

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 SQL Server Network Protocols. We jump into the demo and this video lesson covers starting, stopping and restarting different services like Database Engine, Agent, SQL Server Integration Services (SSIS ), 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)

SSMS is an all inclusive tool for managing SQL Server if you are a database administrator (DBA) . It is equally important for database developers as you can use it to do database design work. In this video we demonstrate using SQL Server Management Studio for database development. 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, Database Replication and Management. Finally we finish the lesson with Vanilla Reporting capabilities, scripting options and long awaited Intellisense. (Running time is 32 mins)

In this training session, we cover database related topics in SQL Server 2008 and make learning databases a breeze. We discuss some best practices regarding database management in general. The first demonstration shows you how to create a database using Management Studio (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 (CREATE DATABASE). A nice new 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)

Managing security is critical if you are a Database Professional on any platform. 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 sql Catalog views that related to security. The video 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)

If you want to be a good Database Developer, strong knowledge of TSQL is a must. Transact SQL is the universal database language. In this database video training, 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 SQL INSERT INTO statement is used to populate data in the Person table. The next two statements are SQL UPDATE and SQL DELETE statements which are very powerful in nature. Using the WHERE clause is Extremely important with these two. We show you how to change data in Person table. (Running time is 32 mins)

SQL Server Administration, Maintenance and Job Automation are routine tasks for a SQL Server DBA, Regular database management 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 using Database Mail and Maintenance Plans. Using maintenance plan, you can easily configure regular maintenance tasks. (Running time is 46 mins)

As a Senior Database Administrator, 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 New 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)

A database is not much without relationships between tables. We kick off this database training session 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 keys and Foreign keys. We will use AdventureWorks2008 again as the source of our data. We discuss 1-1 (one to one relationship), 1-many (one to many relationship) and many–many relationships. In the training demo, we give you an idea about you how to create a database diagram, create a database table with a primary key constraint and establish a relationship with a foreign key constraint. We use SSMS and TSQL to go over these concepts. Joins are of prime importance to deal with database relationships and we go over several database join examples. We bring up Inner Joins and Outer Joins and go over Sales Order and Customer data using database joins. We finally end the lesson with Referential Integrity. (Running time 28 mins)

Monitoring and Profiling your database server is a critical tasks especially if you are the DBA. We kick off this comprehensive video tutorial on SQL Server 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 Transact SQL (TSQL) and Dynamic management views (DMV’s) to get vital stats. We end this training session with the most important monitoring tool, using sql server 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)

We start off this database lesson with SQL views 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 SQL Server 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 using SQL stored procedures which are the preferred option for SQL Server programming. They help in faster execution and code reuse and encapsulation. We combine the database mail feature with sql server stored procedure to generate a list of terminated employees and send the list via an email. (Running time 45 mins)