Skip to main content
main-content

Über dieses Buch

Beginning SQL Server 2008 Administration is essential for anyone wishing to learn about implementing and managing SQL Server 2008 database. From college students, to experienced database administrators from other platforms, to those already familiar with SQL Server and wanting to fill in some gaps of knowledge, this book will bring all readers up to speed on the enterprise platform Microsoft SQL Server 2008.

Clearly describes relational database concepts Explains the SQL Server database engine and supporting tools Shows various database maintenance scenarios

Inhaltsverzeichnis

Frontmatter

Chapter 1. The Database Administration Profession

Abstract
The life of a database administrator (DBA) can be quite interesting. To some nontechnical folks within a company, a DBA is a necessary evil. They can look at a developer and say, “Oh, this is what you created.” When they look at a DBA, what can they see? Usually they don’t think much about the DBA until they have a problem. When this occurs, you are more important than anyone else, including most executives. It is in these situations where you as a DBA can make or break your career.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 2. Planning and Installing SQL Server 2008

Abstract
For some reason during my time as an application development consultant, I ended up working on a few projects where I was writing an InstallShield or Windows Installer package for the client. As a former developer working in various environments, I have made my own hypothesis that, in general, whoever gets stuck writing install scripts must feel like the little kid who got picked last for a kickball team in fifth grade. There is nothing glamorous about setup. The only time your code is being run is when the application is installed, updated, or removed. So if my hypothesis is correct and writing setup scripts is not the most exciting task for developers, then one can assume that, in general, the overall experience to the end user might not be optimal. After all, as a developer, why should you put much effort in something that is going to run only a few times in the life of the product?
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 3. What’s in the Toolbox?

Abstract
If all installing Microsoft SQL Server did was give you a database engine and nothing else, the management experience would be dismal at best. Luckily, Microsoft has shipped some relatively useful tools in conjunction with the database engine. In this chapter, you will explore most of the tools that are installed in a full installation of SQL Server.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 4. Creating Tables and Other Objects

Abstract
In this Chapter, you will learn how to create various objects within the SQL Server database. Primarily, we’ll focus on creating tables and objects such as indexes and constraints that are closely related to tables. To get the most out of this Chapter, you should follow along using SQL Server Management Studio and the instance of SQL Server you installed in Chapter 2.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 5. Transact-SQL

Abstract
Transact-SQL (T-SQL) is the dialect of the Structured Query Language (SQL) adopted by Microsoft’s SQL Server database engine. T-SQLhas been around since the early days of the Sybase code in the early 1990s. It has evolved throughout the years and today is a robust programming language based on the ANSI SQL:2008 standard. In this chapter, you will learn how to access data using T-SQL statements as well as how to use many of the programming capabilities within the database engine, including stored procedures and functions.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 6. SQL Server Internals

Abstract
SQL Server 2008 is a powerful and complex database platform. The relational engine itself is composed of many subsystems. One of these is the command parser, which takes the input from the connection, validates it, and parses it into an internal structure called a query tree. Another is the Query Optimizer, which takes the query tree and prepares the statement for execution. These preparation tasks include ensuring the user executing the query has enough access to the underlying database objects within the query and optimizing the query with respect to performance. Query optimization is cost-based, and SQL Server chooses the execution plan that is best for the given statement.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 7. Storage Management Strategies

Abstract
One of the most important aspects of a database is the storage system it is housed on and how it is managed. Advances in CPU, memory, and disk systems have allowed us to extend database technology to meet the needs of myriad data-intensive workloads. Indeed, advances in hardware continue to move at a fast pace. CPUs have for many years progressed according to Moore’s Law. Memory density and memory capacity follow a similar path, and we are now seeing solid-state disk drives that will eventually allow us to host an entire database in memory. Although technology advances allow us to do more, they also allow us to ignore the fundamental principles of good storage and database design. We have seen poorly designed databases run twice as fast on new hardware, and we have also witnessed well-designed databases see little or no performance improvement on the latest hardware. In this chapter, we will cover the various storage options that are available, how best to configure your database on the storage selected, I/O best practices, and finally high availability options.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 8. Database Backup Strategies

Abstract
SQL Server 2008 and the Windows platforms it runs on are very stable and safe technologies that can offer you years of uptime and service. However, things do go wrong. Software can have errors. Hardware can break. Users can make mistakes. Any one of these reasons and hundreds of others can lead to a loss of data that’s important to you. To protect against the possibility of data loss, you’re going to want some method of creating a copy of your database to store separately for use in the event of an emergency. This is called backing up the database.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 9. Database Restore Strategies

Abstract
Chapter 8 introduced the concept of backing up your database. Making a copy of the database and storing it separately from the original database is a wonderful safety mechanism. But it’s an incomplete model. After you back up the database, you need to be able to bring it back and put all the structures and data from the backup onto your server for use again. This is known as a restore. Restoring a database is every bit as important as backing up the database. Without the backup, you have nothing to restore, but unless you can restore, you might as well have skipped the backup.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 10. Common Database Maintenance Tasks

Abstract
Maintaining a SQL Server database is critical to ensuring that your database environment performs reliably and efficiently. Although advances in the SQL Server platform have made maintenance activities simpler and more straightforward, it is critical that DBAs understand the following:
  • Which database maintenance tasks need to be performed and why
  • When to perform them
  • How to perform them
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 11. Automation Through SQLCMD and PowerShell

Abstract
In Chapter 10, you learned about the most common maintenance tasks that you’ll be called upon to perform as a database administrator. Many of those tasks are routine and also tedious. They are great candidates for automation, and one way to automate is through scripting.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 12. Database Maintenance Plans

Abstract
In Chapters 10 and 11, we talked a lot about ways in which you can execute both interactive and batchscheduled database administrative tasks. In this chapter, we will introduce the SQL Server maintenance plan. SQL Server’s maintenance plans give you a way to perform an array of database administrative tasks on a defined schedule. There are two ways to create SQL Server maintenance plans:
  • The first is by using the Maintenance Plan Wizard, which we will walk you through in detail in this chapter.
  • The second is to use the maintenance plan authoring environment in Management Studio. Figure 12-1 shows that environment.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 13. Performance Tuning and Optimization

Abstract
SQL Server 2008 and the Windows operating systems that it runs on perform very well storing, maintaining, and presenting data to your users. Many of the processes that make them run this well are built into SQL Server 2008 and the OS. Sometimes, because of a variety of factors, such as data changes, new code, or poor design choices, performance can suffer. To understand how well your server is performing, or not, you need to understand how to measure its performance.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 14. SQL Server Security

Abstract
Understanding the SQL Server security model and how to effectively implement and manage a secure SQL environment is important for a DBA. If your employer’s data gets compromised, it’s not just your job that is lost; it could also affect the lives of many people outside your company. You have probably heard stories of hackers obtaining credit card information for thousands of helpless consumers. All these thefts could have been prevented had proper security measures been in place. SQL Server comes with a well-tested security model that enables DBAs to delegate access control from the server level down to the column level and, with the help of native encryption capabilities, to the individual column entries. In this chapter, you will learn how users are authenticated and authorized within SQL Server, and you’ll learn some best practices to follow with respect to the security of your SQL Server instances.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 15. Auditing, Encryption, and Compliance

Abstract
According to the Privacy Rights Clearinghouse, more than 255 million records with sensitive information have been breached since January 2005. If you browse through the detailed report at http://www.privacyrights.org/ar/ChronDataBreaches.htm, you’ll see that there are many kinds of breaches of stolen data. In some cases, there were stolen laptops; in others, it was in inside job by a disgruntled employee. This report yields an almost endless supply of breaches of stolen data.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 16. SQL Server in the Enterprise

Abstract
Now that you are armed with the DBA knowledge presented thus far, how do you keep track of all the databases that you create and manage? Better yet, how do you monitor changes and ensure that your best practices are applied throughout the enterprise? It turns out that there’s quite a bit of functionality to assist you in managing the data management environment in your enterprise.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Chapter 17. Where to Go Next?

Abstract
The great part about SQL Server and other Microsoft products is the amount of love that is given to the development and user communities. Many different resources are available for you to learn more about SQL Server. Some of these resources are in the form of online webcasts, some are white papers, and some are users groups. Perhaps the most important resource that you should get familiar with immediately is SQL Sever Books Online.
Robert E. Walters, Grant Fritchey, Carmen Taglienti

Backmatter

Weitere Informationen

Premium Partner

    Bildnachweise