Chapter 4

Managing and Troubleshooting the Database Engine


  • Configure Your Instance Using Management Studio
  • Use a Dedicated Administrator Connection to Access an Unresponsive Server
  • Monitor Processes Using Dynamic Management Objects (DMOs)

In this chapter, you learn how to configure and monitor your SQL Server instance. This is the key to a smooth running, database — proper configuration and active monitoring. In addition, you learn some troubleshooting methods that help you identify what is happening when the instance is not performing as expected. This chapter assumes you already know the basics of Management Studio navigation and focuses on what you need to know as a database administrator (DBA). Many other chapters in this book cover various aspects of Management Studio, so those points are not duplicated here (backing up your database, for example, is covered in Chapter 17, “Backup and Recovery”).


After you install SQL Server or upgrade to SQL Server 2012, you will likely need to configure it for your needs. In SQL Server 2012, Microsoft maintained its policy to increase out-of-the-box security established with SQL Server 2008 by turning off features after installation, thereby reducing the software footprint. The features turned off vary based on the edition of SQL Server. For example, TCP/IP is disabled in Developer Edition by default, and every edition has CLR integration turned off. This makes the environment ...

Get Professional Microsoft SQL Server 2012 Administration now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.