Microsoft SQL Server 2012 Administration

Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

Tom Carpenter

• Publisher: Sybex
• Year: 2013
• ISBN:
  9781118654903
• Edition: 1
• Topic: Informatique
• Subtopic: Stockage de données

Implement, maintain, and repair SQL Server 2012 databases

As the most significant update since 2008, Microsoft SQL Server 2012 boasts updates and new features that are critical to understand. Whether you manage and administer SQL Server 2012 or are planning to get your MCSA: SQL Server 2012 certification, this book is the perfect supplement to your learning and preparation. From understanding SQL Server's roles to implementing business intelligence and reporting, this practical book explores tasks and scenarios that a working SQL Server DBA faces regularly and shows you step by step how to handle them.

• Includes practice exams and coverage of exam objectives for those seeking MSCA: SQL Server 2012 certification
• Explores the skills you'll need on the job as a SQL Server 2012 DBA
• Discusses designing and implementing database solutions
• Walks you through administrating, maintaining, and securing SQL Server 2012
• Addresses implementing high availability and data distribution
• Includes bonus videos where the author walks you through some of the more difficult tasks expected of a DBA

Featuring hands-on exercises and real-world scenarios, this resource guides you through the essentials of implementing, maintaining, and repairing SQL Server 2012 databases.

Part I
Introducing SQL Server 2012

• CHAPTER 1 Understanding SQL Server’s Role
• CHAPTER 2 Installing SQL Server 2012
• CHAPTER 3 Working with the Administration Tools
• CHAPTER 4 SQL Server Command-Line Administration
• CHAPTER 5 Querying SQL Server

Chapter 1
Understanding SQL Server’s Role

TOPICS COVERED IN THIS CHAPTER:

• What Is Information Technology?
• Introduction to Databases
• Database Servers and Applications
• SQL Server’s Role

Microsoft SQL Server 2012 is a database management system that provides enterprise-class features for organizations of all sizes. If you are tasked with administering a SQL Server, you need to understand the various roles it can play within an organization. This understanding comes best by studying from the foundation up, and this chapter provides that foundation. From this foundation, you will move through this book to learn how to administer the essential aspects of SQL Server 2012. In addition, the contents of exams 70-461 (Querying Microsoft SQL Server 2012), 70-462 (Administering a Microsoft SQL Server 2012 Database), and 70-463 (Implementing Data Warehouses with Microsoft SQL Server 2012) are covered throughout the book.

The first major topics you’ll tackle in this chapter are the concept of information technology and the role a database or database system plays within this concept. Next, you’ll look at databases in more detail and gain an understanding of fundamental concepts that apply to all databases, not just SQL Server databases. Once you’ve sufficiently covered the general database concepts, you’ll investigate database servers and applications. Finally, you’ll explore SQL Server’s features and the roles SQL Server can play in modern organizations.

What Is Information Technology?

Many organizations differentiate between information systems (IS) and information technology (IT). In general, IS deals with software and system development, and IT is concerned with technology management. Certainly, IT is the collection of technologies and resources used to manage information. Organizations place great value on their information, as they should, and they expect the IT group to manage this information well. It is essential that those of us who work in IT remember the I stands for information and that our primary responsibilities are to collect, retain, distribute, protect, and when appropriate destroy that information. When a single group is responsible for these tasks, consistency is accomplished and security can be achieved.

The Importance of IT

Consider an organization that manufactures and sells the components used to make fishing lures. These components are used by many different fabricators and distributors. What would happen if a competing company stole the customer database of the world’s top fishing-lure company? The results could be catastrophic. However, if the company’s IT department creates and uses the proper information-protection mechanisms, the event could be mitigated or the theft itself could be prevented.

---

Throughout this book, the term SQL Server will refer to Microsoft’s database server product in general. When a discussion is applicable only to a specific version of SQL Server, the appropriate version number, such as SQL Server 2012, will be specified.

In addition, I pronounce SQL Server as “sequel server,” and I pronounce the SQL language as “ess-cue-el.” You’ll notice this based on the articles (“a” versus “an”) that I use. I have reasons for my pronunciations, but I’ll reserve those for a later chapter.

---

Although losing a database to a competitor is an extreme example of why an IT department is needed, there are many day-to-day problems and issues that arise within a company that are best handled by the IT department. For instance, customer service professionals aren’t as productive or effective when they cannot access data (information distribution) when they need it to answer customers’ questions. Customers may become impatient if their questions aren’t sufficiently addressed, and they could very well choose a different provider. An effective IT department helps everyone within a company manage information so each team can be successful.

Effective IT solutions enable the five key responsibilities of information management to be accomplished.

Information Collection Database systems and applications are used to collect information from users. Well-coded applications validate data integrity and ensure that only valid users can enter or modify information.

Information Retention A good information storage system provides effective storage and backup mechanisms. You’ll learn about SQL Server’s backup solutions in Chapter 17, “Backup and Restoration.”

Information Distribution The right people need the right information at the right time, and information distribution solutions allow for this. Examples include replication, mirroring, Integration Services packages, and more.

Information Protection There are many different types of information with varying degrees of priority and confidentiality. In most organizations, only certain users should have access to certain information. Security solutions from authentication to storage encryption should be used to protect valuable data. Additionally, codi...