Managing and troubleshooting the database engine

Managing And Troubleshooting The Database Engine
With the server now installed or upgraded, the nest thing you need to do is configure it for your environment. In this chapter, you will learn how to configure your SQL server instance. After configuring the instance, you will learn how to manage the instance with management studio and a number of stored procedures or dynamic management views(DMVs). The chapter also covers how monitor connections on your SQL server and how to troubleshoot problems. This chapter assumes you already know the basics of management studio navigation and focuses on what you need to know aspects of management Studio, so those points are not duplicated here (backing up your database, for example, is covered in chapter 18).
Configuration Tools
Now that youhave SQL Server installed, or have upgraded to SQL Server 2008, it probably isn’t configured exactly for your needs out of the box. In SQL server 2008, Microsoft has chosen to increase the out of the box security of SQL server dramatically 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 disable in developer edition by default, and very edition has CLR integrated turned off. This makes the environment more usable for you as an administrator, as features you don’t care about are not crowding your administration screen. It also reduces the options that a hacker can use to penetrate your system.

SQL Server Configuration Manager
The SQL Server configuration managerconfigures the SQL Server services much like the services applet in the control panel, but it has much more functionality than the applet. For example, the program can also change what ports SQL Server listens on and what protocols each instance uses. You can open the program from Start ---> All Programs -Microsoft SQL Server2008 Configuration Tools SQL Server Configuration manager.
Select SQLServer Configuration Manager SQL Server services to Configure the various SQL Server services. To configure an individual service such as SQL Server, double-click the service name to open the service properties page. In the log on tab, you can configure which account starts SQL Server. We recommended that you start SQL Server with regular domain user account with minimal rights. The accountshould not have the privilege to log locally, for example: there is no reason for the account to be a local or domain administrator in SQL Server2008.
In addition, create a non-expiring password so you SQL Server doesn’t fail to start when the password expires. If the SQL Server services do not need to communicate outside the instance’s machine, you could start the service with the local Systemaccount, but the account may have more local rights than you want(we talk more about this in chapter 9).
On the services tab, you can specify whether you’d like the service to start automatically, manually or be disabled. If you go to the advance tab for each service, you can configure the more interesting options. For example, here you can turn off customer feedback reporting. This feature enablesMicrosoft to receive utilization reports from your SQL Server. Even if you wanted to do this, in most production environments your SQL Server may not be able to send the report, due to a lack of internet access from production servers.
Checking the error reporting option in the advance tab will e-mail Microsoft whenever a critical error has occurred. The minimal information is sent over a secureHTTPS protocol. Alternatively, you may also wish to send errors to your own internal corporate error reporting (CER) System, which is a product that can download from Microsoft.
On the SQL Server 2008 network configuration manager, you can see a list of network protocols that SQL Server in listening on by instance. If you wish to turn a protocol on or off, you can do so by right-clicking the...