The SQL Server product has been around since the late 1980s. Back then, Microsoft worked together with Sybase to create a database on the OS/2 platform. Shortly after this effort completed, Microsoft realized it wanted to tightly couple SQL Server with Windows. By 1993, Microsoft had released Windows NT 3.1, and with it came the option of using a well-integrated version of SQL Server called SQL Server 4.2. This was to be the last version of SQL Server made jointly between the two companies. By 1995, Microsoft left the partnership with Sybase and released SQL Server version 6.0, followed a year later by version 6.5. These two versions were still on the original Sybase platform.
As a DBA today, it would be a rare occurrence to see a production server running any of these three versions of SQL Server. If you do, chances are the servers are probably running a database application from a company that no longer exists. Either way, if you encounter these systems in production, it may be worthwhile to do a few web searches and learn a bit more about how to administer these kinds of SQL Server instances.
By 1997, Microsoft decided to rewrite the database engine and effectively move away from the old Sybase design. This rewrite effort led to the release of SQL Server's next version, 7.0. To some, this version marked the first generation of SQL Server. By rewriting the database engine, Microsoft was able to provide key features to customers. Some of these advancements included the ability for offline users to update data and merge it back to live data when they reconnected to the server (merge replication). DBAs could also grow and shrink databases as needed as well as use new wizards such as the Copy Database Wizard to easily perform administrative tasks. SQL Server 7.0 also made Online Analytical Processing (OLAP) capabilities affordable to the market by including OLAP services as part of the SQL Server 7.0 license. This was SQL Server's initial entry into the data warehousing market. With SQL Server 7.0, users could now easily design, build, and manage data marts and data warehouses.
Seeing a SQL Server 7.0 in production isn't as uncommon as an older version but is still rare since that version is no longer being serviced or supported by Microsoft.
SQL Server 2000, released in 1999, built upon the success and lower total cost of ownership value that SQL Server 7.0 had introduced to the market. Major parts of the database engine were rewritten again to accommodate features such as multi-instances where DBAs could run more than one instance of SQL Server per server. Microsoft made continued investments in scalability and availability with features such as log shipping. Log shipping allowed DBAs to automatically copy and load transaction log backups from one database to one or more databases, enabling a highly available environment. Investments were also made for developers, including XML support and the ability to define user-defined functions. In the business intelligence (BI) space, a more mature version of Data Transformation Services was shipped as well as Reporting Services for SQL Server 2000. There was a large effort around the integration of Microsoft Office applications and Analysis Services. For example, it was possible to use Excel pivot tables loaded with data from cubes created with Analysis Services.
Although SQL Server 2000 is currently in extended support, there are many organizations still using it in production today. There is a good chance you as a DBA will be involved with managing and eventually upgrading these servers to SQL Server 2008. Between the release of SQL Server 2000 and SQL Server 2005, there was about a five-year gap. The result of this long wait was a product that contained many new enterprise capabilities such as database mirroring, online indexing, online restore, table partitioning, and many others.
It is highly likely if your company is using SQL Server, it will probably have some installations of SQL Server 2005 in production.
SQL Server 2008 is all about solving the problems that very large databases bring. Features such as data compression, resource governance, and enterprise-wide policy management headline the marketing brochures. Throughout the remainder of this book, you will be exposed to a lot of these key features of SQL Server 2008.
Are you considering a career as a database administrator? Are you an IT professional who is curious about what SQL Server is and how to manage it? Are you a database administrator for a non–SQL Server platform like Oracle or DB2 and have an interest in SQL Server? Do you want to learn about SQL Server? If you answered "yes" to any of these questions, then you will have invested your money wisely in this book. This book provides you with a solid foundation of the fundamental skills and concepts of managing a SQL Server instance.
For those readers who are not familiar with databases, it is probably best to work through each chapter sequentially since topics in later chapters build on ones described in earlier chapters. This is what each chapter covers:
Chapter 1: This chapter explores the database administration profession. Topics include typical tasks of DBAs as well as input from experienced real-life DBAs.
Chapter 2: SQL Server is an enterprise-ready database platform. Simply installing it onto a production server without much planning is a surefire way to getting shown the door by your current employer. This chapter explores some of the key issues to consider when installing SQL Server. Microsoft provides multiple editions of SQL Server. This chapter also reviews these editions as well as walks you through an installation of the evaluation edition of SQL Server.
Chapter 3: Getting familiar with a new server application like SQL Server can be a daunting task for most. There are many different applications installed such as management and development tools, Performance Monitor counters, and many other new things to learn about. This chapter will explore these bits that SQL Server installs.
Chapter 4: In this chapter, you get your hands dirty by using the database for its most fundamental purpose: creating tables and storing some data. You'll also learn to create indexes to help ensure efficient access to that data when you later want to retrieve it.
Chapter 5: This chapter covers Transact SQL (T-SQL) syntax. You'll learn to write and use stored procedures, functions, and triggers, as well as to perform many other common functions within SQL Server.
Chapter 6: This chapter gives you a heads-up on how a SQL Server installation is organized. You'll learn that a SQL Server install includes several databases used by the system in support of those databases that you create for your own use. These databases include the master database containing user logins and the tempdb database used for temporary storage. You'll also learn about the Windows services that run in the background that make up a running SQL Server installation.
Chapter 7: This chapter discusses issues to consider when creating databases such as data and log file placement and the fundamental differences between them. A discussion on database creation wouldn't be complete without covering storage devices and their effects on databases. Although you probably don't have your own personal storage area network (SAN) to play with, understanding these types of storage technologies is helpful in understanding how enterprises use SQL Server. This chapter reviews the common storage technologies used with SQL Server.
Chapter 8: If you know nothing else as a DBA, you better know how to perform a database backup. This is a core task that almost all DBAs are involved with. This chapter covers the different types of backups and when to use each. Additional topics include special backups such as compressed and encrypted.
Chapter 9: Backing up a database is only half the battle. If a disaster occurs, DBAs need to know how to restore the database and get the server back online or restored to a specific point in time. The overall plan for recovering from a disaster is the disaster recovery plan, and this chapter will cover what kind of information this plan should contain as well as how to perform database restores.
Chapter 10: Effectively backing up and restoring databases are critical skills for DBAs to possess. However, there are many other functions a DBA performs. Examples of these functions include tuning indexes, creating maintenance scripts, and scheduling jobs. This chapter covers the key tools within SQL Server that help DBAs perform these kinds of tasks.
Chapter 11: You as a DBA don't want to perform every single task yourself, especially the mundane tasks that are the same each day. Automation is a powerful tool that enables you to leverage your time and expertise over a large number of databases. Here you'll learn to automate the routine work so that you can focus your time and energy on more strategic and interesting tasks.
Chapter 12: A very powerful new automation feature introduced in SQL Server 2008 is the maintenance plan. A maintenance plan is an "object" that you create in the database that specifies certain tasks to be performed on a routine basis. Maintenance plans greatly reduce the need to write scripts. Just list the tasks you want to perform and specify dependencies and notification details, and the maintenance plan feature takes care of doing the actual work.
Chapter 13: This chapter covers the basic tools used for performance measurement as well as the tools used to tune databases. SQL Server 2008 introduced a few key features in this space including Performance Data Collector. These key improvements will be covered in this chapter.
Chapter 14: This chapter covers the SQL Server security model. You'll learn how users authenticate to SQL Server and are granted access to objects within the server.
Chapter 15: DBAs who work for publicly traded companies, health-care companies, or companies that stored and use credit card information will undoubtedly be asked into a few meetings with some auditors. These folks, if they are external to your company, are hired specifically to make sure your company complies with the applicable regulations such as Sarbanes-Oxley, Payment Card Industry (PCI), and others. This chapter describes how SQL Server enables features that help DBAs configure their servers to be compliant.
Chapter 16: This chapter explores how SQL Server is used in the enterprise. It also covers some of the more common applications such as Microsoft System Center Configuration Manager that can be used with SQL Server.
Chapter 17: This chapter provides valuable references that will help you as a new DBA continue to build your foundation of knowledge about SQL Server.
Learning how to manage a database is best done as a hands-on exercise. This book walks through an installation of the evaluation edition of SQL Server. This edition is a free download from http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx. The evaluation edition has all the functionality of Enterprise edition and is a great way to start working with SQL Server without any monetary investment. Of course, there are some licensing restrictions with the evaluation edition, such as that you can't run a production server using it. If you could, Microsoft would be out of a lot of money. To get the most of the book, you should install the product, work through all the examples within each chapter, make your own modifications to the scripts, and see what happens!
Apress makes every effort to ensure that there are no errors in the text or code. However, mistakes happen, and we recognize the need to keep you informed of any mistakes as they're discovered and corrected. An errata sheet will be made available on the book's main page at http://www.apress.com. If you find an error that hasn't already been reported, please let us know.
You can contact the book's lead author, Rob Walters, via his e-mail address at [email protected].