Introduction
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 was 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, you will rarely 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, including 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 easily design, build, and manage data marts and data warehouses.
Seeing SQL Server 7.0 in production isn’t as uncommon as seeing a Sybase-style version, but is still rare since that version is no longer being serviced or supported by Microsoft.
SQL Server 2000, released in 1999, built on the success and lower total cost of ownership value that SQL Server 7.0 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 create 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 no longer supported, many organizations still use 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 2012.
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, and table partitioning.
If your company has been using SQL Server for a while, it is highly likely to have some installations of various versions of SQL Server including SQL Server 2005 and SQL Server 2008 in production.
SQL Server 2012 is the latest and most significant release of SQL Server to date. This version is all about mission-critical confidence, breakthrough business intelligence insight, and leveraging the cloud on your terms. In this book, you will be exposed to a lot of the key features of SQL Server 2012 from a database administration perspective.
Who This Book Is For
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, Sybase, or DB2 who has an interest in SQL Server? Do you want to learn about SQL Server? If you answered “yes” to any of these questions, 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.
How This Book Is Structured
If you are not familiar with databases, it is probably best to work through each chapter sequentially, because 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 of 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, so 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. Many different applications are installed such as management and development tools, Performance Monitor counters, and numerous other new things to learn about. This chapter will explore these bits that SQL Server installs.
Chapter 4: There are a few tools that you, as a DBA developer, can use in your quest to code against SQL Server. In this chapter, you will explore the development capabilities of SQL Server Management Studio and SQL Server Data Tools.
Chapter 5: 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 6: 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 7: 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 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, a DBA performs many other functions, including 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: 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 12: 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 13: This chapter covers the basic tools used for performance measurement as well as the tools used to tune databases. These key improvements in SQL Server 2012 will be covered in this chapter.
Chapter 14: Fault tolerance is an important concept for the DBA to understand. This chapter will cover service level agreements (SLA) and how to leverage the various high-availability features within SQL Server 2012 to accommodate your own SLA.
Chapter 15: Companies around the world are continuing to embrace cloud computing for specific workloads. This chapter will introduce SQL Azure, Microsoft’s database as a service offering.
Chapter 16: With the advent of high-core-count processors came a large push for database consolidation and virtualization. Today, SQL Server DBAs are being tasked with building or contributing to the design of private cloud SQL Server clusters. This chapter will cover the concepts of what makes up a SQL Server private cloud environment.
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 conventions.
Contacting the Authors
You can contact the book’s lead author, Rob Walters, via his e-mail address at [email protected].