Overview

Introduction

The Designing a SQL Server Instance and a Database Solution module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Design a monitoring solution at the operating system level
  2. Design a monitoring solution at the instance level

Exercise 1 – Design a monitoring solution at the operating system level

In this exercise, you will perform the tasks required to monitor a database in SQL Server 2008. To understand this technology, refer also to your course material or read the material available at the following link: http://technet.microsoft.com/en-us/library/ms365356.aspx

Exercise 2 – Design a monitoring solution at the instance level

Instance

For monitoring instance you can use Activity Monitor. Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.

Exercise 3 – Design a monitoring solution at the instance level

Dedicated Administrator Connection

Dedicated Administrator Connection (DAC) is a privileged connection type in SQL Server. SQL Server reserves a special scheduler for this type of connection.

The restrictions of DAC are:

  • You should be a member of sysadmin role to use DAC.
  • Only one DAC is allowed per SQL server instance.
  • Since DAC resources are guaranteed, that’s why they are limited. You cannot run BACKUP/RESTORE using DAC.

DAC should be used only to query DMVs/DMFs, and to run basic DBCC commands and KILL abusive processes.

A DAC port is assigned during SQL Server startup. So to enable remote DAC connections, you need to enable SQL Server Browser service or specify the port number explicitly within your connection string.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.