Pro SQL Server 2012 Administration | springerprofessional.de

Springer Professional

Top

2012 | Book

Read chapter Read first chapter

Pro SQL Server 2012 Administration

Authors: Ken Simmons, Sylvester Carstarphen

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access

About this book

Pro SQL Server 2012 Administration is a one-stop source for all the knowledge you'll need to professionally manage a database under Microsoft SQL Server 2012. SQL Server administration is a complex field requiring an unusually broad and well-rounded skill set. A good database administrator must be part system administrator, part database design expert, and part SQL tuning optimization guru. Above all, he or she must be capable of restoring a damaged database at any time. Database administration requires excellent people skills for dealing with management and clients, and solid technical skills for dealing with the hardware and the software.

Pro SQL Server 2012 Administration addresses all aspects of database administration, regardless of the size of the implementation. Install SQL Server 2012 and create new database environments. Develop a sound backup strategy for your database. Perform recovery as needed, which many consider to be job #1. Authors Ken Simmons and Sylvester Carstarphen also help you automate your work through the use of policy-based management. Performance optimization gets good coverage, so that you can render assistance when developers need help with problem queries.

Rare among database and other technical books is solid guidance on the people side of the equation. Success as a database administrator comes from working well with others, becoming involved in the community, and building the right combination of technical knowledge and people skills to solve problems and get things done professionally and amicably. Simmons and Carstarphen pay special attention to these aspects of the job in this new edition of their comprehensive book on SQL Server administration.

Helps you manage a SQL Server 2012 database at professional level Covers the gamut of database administration skills Provides guidance on performance optimization

Advertisement

Table of Contents

Frontmatter

Introducing Microsoft SQL Server 2012

Frontmatter

Chapter 1. New Features Overview

Abstract

The release of Microsoft SQL Server 2012 has introduced many new features that increase scalability, manageability, availability, programmability, and security across the enterprise. With many organizations focused on consolidation and virtualization, this couldn’t have come at a better time. As the demand for data keeps growing and security and compliance keep tightening, the role of the database administrator (DBA) has become an increasingly critical part of the organization. It is important for every DBA to have a good understanding of the tools available to help maintain a highly available, secure environment.

Ken Simmons, Sylvester Carstarphen

Chapter 2. Pre-Installation Considerations

Abstract

Unfortunately, in most production environments, you do not have enough time to preplan the installation of SQL Server. In addition to ensuring your current production environment performs as expected, you provide server specifications for new systems. This requires a lot of research to determine things such as how much RAM the server will need; what type and how powerful the CPUs should be; and the layout, size, and RAID (redundant array of independent disks) levels of your storage system, along with many other considerations. In addition to providing server specs, you also have to manage the implementation of processes and procedures after the installation is complete. If there is a lack of preparation and research time, you sometimes make the wrong assumptions or decisions when providing your recommendations. Although bypassing some research up front will save time initially, there may be an extreme cost to you and the company by not taking the time to preplan properly. For the next two chapters, we are going to talk about some of the things that you definitely want to consider before installing SQL Server 2012.

Ken Simmons, Sylvester Carstarphen

Chapter 3. Choosing a High-Availability Solution

Abstract

High availability has become an increasingly popular subject in SQL Server. Not only have there been entire books dedicated to high availability, but we have also seen specialized books written on each topic falling under the high-availability umbrella. We will not be going into great detail here, but it is important to understand how each of SQL Server’s high-availability solutions can play a role in your environment. This chapter gives an overview of each solution to differentiate what factors play a role in designing the best, overall solution for your organization. By the end of this chapter, you should be armed with the information necessary to make an informed decision on the best usage scenarios for each highavailability technique offered in SQL Server 2012.

Ken Simmons, Sylvester Carstarphen

Getting Started

Frontmatter

Chapter 4. Installing and Upgrading

Abstract

The past two chapters discussed which edition of SQL Server 2012 to install, identifying and selecting the hardware requirements, and figuring out the high-availability solution to implement, if any. You have finally reached the point where you can validate your pre-installation considerations and install or upgrade to SQL Server 2012. The first half of this chapter provides multiple methods of installing Microsoft SQL Server 2012 while the second half of this chapter focuses on updating to SQL Server 2012 from previous versions of SQL Server. Based on current requirements, you may find it useful to bypass the first half of this chapter to focus in on upgrading your SQL Server.

Ken Simmons, Sylvester Carstarphen

Chapter 5. Post-Installation

Abstract

After you have installed or upgraded your environment to SQL Server 2012, we recommend spending some time configuring your instance to ensure that SQL Server runs optimally and is secure. There are many configuration options and different post-installation processes and procedures utilized across the globe by various database administrators. We cannot cover every scenario and describe the best configuration setting for all the different types of applications, but we would like to discuss the more important options and describe situations where you can take advantage of the settings.

Ken Simmons, Sylvester Carstarphen

Administering Microsoft SQL Server 2012

Frontmatter

Chapter 6. Multi-Server Administration

Abstract

With the number of database servers increasing and the tolerance for errors decreasing in today’s organizations, it is has become necessary to define and maintain database standards across the enterprise. One of the problems in the past has been making sure those standards are being followed. As a DBA, you would have to go out to each server and run queries using system tables to ensure that each server was in compliance. Luckily, you can now use Policy-Based Management to ensure that your standards are being followed. In addition, by using central management servers, you can use a single server as an access point to all the servers in the organization. Central management servers enable you to evaluate policies and execute queries against registered server groups with a single click. You can also use dynamic management views and functions with SQL dependency reporting to locate object dependencies between servers.

Ken Simmons, Sylvester Carstarphen

Chapter 7. Managing Security Within the Database Engine

Abstract

Managing security within the Database Engine is one of the most important tasks for a database administrator. Controlling access to SQL Server and the roles and rights that each user has within SQL Server, as well as the security of the data, are the main security concerns. Throughout this chapter, our goal is to discuss security in the manner in which we believe security measures are encountered: Windows security, SQL Server security, database security, and then protecting the physical data itself.

Ken Simmons, Sylvester Carstarphen

Chapter 8. Working with Database Objects

Abstract

The purpose for every SQL Server installation is the necessity of a database; however, simply creating a database will not make an application work. Many objects work in concert to provide a positive user experience when interacting with a database. If you look at the Object Explorer in SQL Server Management Studio, you will see there are several objects and features available to aid you in administering the database. SQL Server even has its own set of system databases that manage data within SQL Server itself. This chapter will walk you through many of these objects and features and explain the impact each one has on you as a DBA.

Ken Simmons, Sylvester Carstarphen

Chapter 9. Indexing for Performance

Abstract

Effectively creating indexes within your databases will single handedly provide the largest increase in application performance. Unfortunately, creating indexes incorrectly or without regard for the application’s behavior can actually hinder performance. A good database administrator will pay close attention to the actions of the application and understand that you may have to be flexible with regard to the index creation tips, recommendations, and guidelines that you have researched. The purpose of this chapter is to enhance your understanding of indexes and help you with the development of your rules for creating indexes.

Ken Simmons, Sylvester Carstarphen

Chapter 10. Managing Backups

Abstract

Understanding backups is extremely important for every database administrator (DBA)—it can make or break your career. If you need to restore a database, you can be a hero in a matter of minutes. Conversely, if you don’t have a good backup to restore, you may end up looking for a new job. In our opinion, having the knowledge to properly back up and restore a database is the most important skill a DBA can possess. That being said, we think backing up a database is often the most easily overlooked process. It’s easy to set up a maintenance plan or backup job and think you are covered—a kind of set-it-and-forget-it mentality. The problem with this attitude is that you do not know a backup is being performed incorrectly until you need to use it, which is not the best time to find out. There are several backup options available in SQL Server 2012 that you should master to ensure you do not find yourself explaining why you cannot restore a database everyone thought was being backed up.

Ken Simmons, Sylvester Carstarphen

Chapter 11. Restore and Recovery Strategies

Abstract

The ability to restore databases in SQL Server 2012 is essential to recovering a system when catastrophes arise. In order to restore your databases, you need a backup strategy that fulfills the requirements set forth by your business requirements. If your backup strategy is inadequate, then you will not be able to recover to the predetermined interval required to fulfill the requirements of your application. Ultimately, your application will lose data, and the users and your management will be very unhappy. In the last chapter, we covered the details of creating effective and efficient backup strategies. This chapter will focus on the skills needed to restore a database when the time arises.

Ken Simmons, Sylvester Carstarphen

Chapter 12. Automating Routine Maintenance

Abstract

One of the biggest responsibilities you have as a DBA is proactively making sure that all the servers in your environment are running smoothly. You should not wait for a user to call and complain about slow response times before rebuilding indexes or updating statistics. Also, if you encounter an issue, such as a torn page in a database, it is better to find out sooner than later. If an issue goes undetected long enough, you may not have the backups needed in order to correct it, and your only option may be to accept data loss. Sustaining a SQL Server instance that provides optimum performance requires constant maintenance. Luckily, you can use the same tools within SQL Server to automate maintenance tasks that you use to meet the business needs of the organization. By taking advantage of features like Database Mail and SQL Server Agent to automatically maintain your servers and notify you of failures, you will have more free time to work on all of those past due projects you haven’t gotten around to.

Ken Simmons, Sylvester Carstarphen

Troubleshooting and Tuning

Frontmatter

Chapter 13. Monitoring Your Server

Abstract

As a database administrator, you are required to monitor the performance of SQL Server, the users who are logging in to your server, and to identify changes to objects within your environment. When you are effectively monitoring SQL Server, then you can proactively make informed decisions about changes made in your environment. The alternative is that you make reactive decisions, which are often not thought out completely.

Ken Simmons, Sylvester Carstarphen

Chapter 14. Auditing SQL Server

Abstract

Auditing your SQL Server is an important aspect of protecting your data. As a database administrator, you need to know the users who are logged into your system or attempting to log into your system, and what they are doing once they have gained access.

Ken Simmons, Sylvester Carstarphen

Chapter 15. Extended Events Interface

Abstract

Extended Events is an extremely flexible and low overhead event handling system that was introduced in SQL Server 2008. There is actually an event session called system-health that is configured to run by default in SQL Server 2008 that keeps track of information that was deemed useful by the Product Support team. The Extended Events infrastructure has been built from the ground up based on the premise that a diagnostic system should not prevent a user workload from running. Even if you do add too much overhead to the server by poorly configuring a session, the server should still be responsive enough to disable the session.

Ken Simmons, Sylvester Carstarphen

Chapter 16. Managing Query Performance

Abstract

Managing query performance is another one of those subjects that requires an entire book to provide the coverage it deserves, especially when you dive into the execution plans. However, dealing with problem queries is an integral part of every DBA’s skill set. Luckily, Grant Fritchey and Sajal Dam have written SQL Server 2012 Query Performance Tuning Distilled (Apress, 2012), so this doesn’t have to be a 500-page chapter.

Ken Simmons, Sylvester Carstarphen

Chapter 17. Secrets to Excelling as a Professional DBA

Abstract

This chapter focuses on developing non-technical skills that can help separate you from every other DBA within your organization. Why is this important? In our opinion, the nontechnical skills place you in the top tier of DBAs and ultimately help you make more money. We have both been successful professionally in a short period. We would like to share with you some of the things that we have done to grow rapidly in our profession.

Ken Simmons, Sylvester Carstarphen

Chapter 18. What’s Next?

Abstract

I am sure you know by now that being a DBA requires a huge skill set, covering a wide spectrum of technologies. DBAs are expected to know a little bit about everything. But what makes being a DBA even harder is that technology is a fast-paced industry, with rapid advancements that seem to outdate what you thought of as new before you’ve even had a chance to learn it. The days are over when you learned a skill and then worked for several years before you needed to upgrade your skill set. You have to learn new things constantly and stay on top of the latest industry news in order to excel as a DBA.

Ken Simmons, Sylvester Carstarphen

Backmatter

Title: Pro SQL Server 2012 Administration
Authors: Ken Simmons
Sylvester Carstarphen
Copyright Year: 2012
Publisher: Apress
Electronic ISBN: 978-1-4302-3916-1
Print ISBN: 978-1-4302-3915-4
DOI: https://doi.org/10.1007/978-1-4302-3916-1

Premium Partner