Read an Excerpt
SQL Server Fundamentals for the Accidental DBASQL Server Fundamentals for the Accidental DBA PrefaceIntroduction
I have worked with SQL Server for many years and in that time, I have worked with many different individuals from developers, to system administrators, to data architects. One thing I have noticed is that while SQL Server has sprung up all over the place, there is not always an experienced database administrator looking after them. Client applications will install free versions of SQL Server, known previously as the Microsoft SQL Server Desktop Engine or MSDE and more recently as SQL Server Express Edition. These free versions have many of the same care and feeding requirements as full-blown copies of SQL Server, but often they are left to their own devices. In addition, I have worked with many developers over the years who are fantastic at their jobs and know how to write applications, but they sometime falter when it comes to understanding database structure and design methodologies. It is for these system administrators and developers, these accidental DBAs that I did this LiveLesson series.
What Is This LiveLesson About?
In this LiveLesson, I have tried to take my experience and knowledge of SQL Server and database design and put it into an easy to follow series of lesson to help you understand the fundamentals of administer SQL Server. By no means is this course meant to cover everything about the product or even cover every aspect of the topics discussed. My goal is to provide you with a foundation of knowledge about SQL Server so that you can properly manage it or work with it as a developer.
Deciding what to put in this LiveLesson was a challenging task. Often it came down to a simple question, would a company need to hire a full-time DBA if they where to use a particular feature? Where the answer was yes, I choose not to cover the feature. For example, managing SQL Server replication can be a daunting task and probably not one to be undertaken by an accidental DBA. What you will find in the LiveLesson is everything you will need to know in order to manage SQL Server enough to keep it backed up, secure, and running.
Who Should Use This Course?
Anyone who has found that he or she must manage or work with some version of SQL Server when his or her primary job function does not include such tasks will find this course useful. Windows administrators who find SQL Server behind SMS or Sharepoint or .NET developers who are told to write an application that uses SQL Server as a back-end data store should have a working knowledge of SQL Server and that is what this course provides.
How This Course Is Organized
The course is structured around the basic task that one may find himself needing to perform on a SQL Server. Some lessons will build on knowledge gained in an earlier lesson but in general each lesson covers a very specific topic and you can skip around if you find one area more important than another. The LiveLesson format makes it perfect for you to cover a subject and then come back a month later and view a different subject. The lessons and their content are described as follows.
- SQL Server Overview
This lesson aims to give you an overview of what SQL Server is and the different flavors of SQL Server you may encounter. We will also cover the different components of SQL Server that are included in the product.
- Common Uses of SQL Server
In order to manage all your SQL Servers, you first must find them. In this lesson, we will look at different places where you might find SQL Server in your environment. The goal is to provide you with more information about how SQL Server is used so that you can recognize situations where SQL Servers may be in use.
- SQL Server Management Studio
You will work with many tools in the process of managing SQL Servers, but none more so than the SQL Server Management Studio (SSMS). The goal of this lesson will be to orient you with SSMS and show you how it is used.
- SQL Server Configuration Manager
Continuing our look at tools, this lesson will introduce you to the Configuration Manager and how it is used to manage the settings of the various services.
- SQL Server Profiler
A fantastic tool for analysis and troubleshooting is the Profiler. In this brief introduction we will show you the basics of Profiler and make sure you know how to get around with the tool.
- Business Intelligence Development Studio
If you’re developing database projects such as Integration Services packages or Reporting Services reports, you will use the Business Intelligence Development Studio (BIDS). This lesson will help you get familiar with the tool.
- Understanding Tables
This lesson serves as the first of two lessons that will cover relational database basics. It is easier to manage SQL Server if you have at least a passing understanding of what databases are and how they are structured. In this lesson, we will focus on the foundation of databases, namely tables.
- Understanding Relationships
This lesson builds on the preceding by extending our discussion of relational databases. We will look at how the data in tables is joined through the concept of relationships.
- Database Files
This lesson provides you with some detail as to how SQL Server manages databases. We will look at the different files that make up a database and how they are managed.
- The Transaction Log
Crucial to the operation of SQL Server and therefore crucial to your knowledge is the transaction log. This lesson aims to explain the workings of the transaction log and to give you an idea of how recovery models affect its use.
- Understanding System Databases
System database are used by SQL Server to keep all its internal processes up and running. We will look at each system database and how each contributes to the function of SQL Server as a whole.
- Processor Settings
Often SQL Server is installed on a server with a few other applications running. In this lesson, we will look at the settings that control how SQL Server interacts with the server’s CPUs.
- Memory Settings
Just as important as the CPUs, memory also plays an enormous role for SQL Server. This lesson will show you the available memory settings and explain why you might need to make changes.
- Backing Up Databases
A crucial task when working with SQL Server is backing up the databases. This lesson will show you what you need to know to make sure you can back up your databases and protect yourself from data loss.
- Scheduling Backups
This lesson will be an introduction to SQL Server jobs. We will look specifically at how you can schedule routine maintenance such as backups in order to reduce the amount of manual work you need to put into managing SQL Server.
- Restoring Databases
What good is backing up if you can’t restore your database in the event of a loss? This lesson will provide you with the information necessary to perform a database restore.
- Restoring System Databases
Restoring System databases is a little different than restoring user databases. In this lesson we will look at those differences and walk through the process of restoring system databases.
- Managing Logins
Your first line of security is the login. This lesson will explain logins and show you how to create them and manage the permissions assigned to each login.
- Managing Users
Users are separate from logins and control the access granted into individual databases. In this lesson, we will look at what users are and how they are managed.
- Database Roles
Database roles are used to manage groups of users and assign the users permissions to do things in a database. This lesson aims to arm you with the information you need to manage database roles or even create your own.
- Ownership Chaining
This lesson will focus on a discussion of ownership chaining. This is an important concept in security and without fully understanding it, you may be handing out more permissions than your users need. We will talk specifically about what ownership changing is and look at some real-world examples.
- Data Manipulation Language
This will be the first lesson where we look at Transact SQL or T-SQL. T-SQL is the language used to work with SQL Server data and objects. In this lesson, we look at Data Manipulation Language (DML), which is used to work with the data being stored in your database.
- Data Definition Language
In order to create and modify objects in databases such as tables or views, you use Data Definition Language (DDL). In this lesson, we will look at the basics of DDL and how it is used.
- Stored Procedures
In addition to structures such as tables, databases are also made up of programming structures that allows you to run T-SQL code. The most common of these structures are stored procedures. In this lesson, we will look at what stored procedures are, how they are created, and how you set up security on them.
- Functions
This lesson will cover functions, another programming option in SQL Server databases.
- Views
This lesson will cover views. Views provide access to data without the need to access a table directly. We will look at what you need to know to use views effectively.
- Triggers
Triggers allow you to have bits of T-SQL run automatically in response to an event such as data being inserted into a table. This lesson will cover the different types of triggers and how you can use them in your databases.
- CLR Integration
With the release of SQL Server 2005, we were given the ability to used CLR code structures within SQL Server. This lesson will show you how this is accomplished and how you can make stored procedures, functions, or types in SQL Server that reference CLR code.
- Indexing Overview
Indexing is a huge topic in the world of SQL Server. This lesson will provide you with the basics of what indexes are and how SQL Server uses them to find data.
- Working with Indexes
This lesson will build on the last by providing you with what you need to know about the different types of indexes. In addition, we will look at how each type of index is implemented.
- Included Columns
This final indexing lesson will discuss a relatively new feature of SQL Server: included columns in indexes. We will look at what they are and how they are implemented in SQL Server.
- Application Security
We have looked at several security concepts and this lesson will bring these concepts together to help you make a real-world decision. We will look at the most common options for allowing applications access to SQL Server and the pros and cons of each.
- Abstraction Layers
This final lesson will be a discussion on creating abstraction layers. Abstraction layers have several uses and we will examine each as well as take a look at the problems that can arise when you don’t have an abstraction layer.
If your Windows system is configured with AutoPlay on, the video will automatically start when you insert the DVD into your drive. However, if AutoPlay is off, you will need to insert the DVD into the drive, launch Windows Explorer, navigate to the root folder of the DVD, and double-click on the file called “Start_livelesson.exe.”
This LiveLessons product is designed to run at a screen resolution of 1280 ¥ 1024 or higher. Please adjust your screen resolution for the best playback experience.
LiveLessons DVD System Requirements
Operating system: Windows 98, 2000, XP, or Vista. Multimedia: DVD drive, 1024 ¥ 768 or higher display, and sound card with speakers. Computer: 500MHz or higher, 128MB RAM or more.
© Copyright Pearson Education. All rights reserved.