Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

Microsoft SQL Server 2008 R2 : SQL Server Management Studio - Administration Tools (part 2)

2/27/2012 4:31:28 PM

Log File Viewer

The Log File Viewer is another nonmodal window that is essential for administering your SQL Server. Like the Activity Monitor, it houses information that was previously displayed in the document window in the SQL Server 2000 Enterprise Manager. It can display log files that are generated from several different sources, including Database Mail, SQL Server Agent, SQL Server, and Windows NT.

The Log File Viewer can be launched from the related node in the SSMS Object Explorer. For example, you can select the Management node and expand SQL Server Error Logs. If you double-click one of the error logs listed, a new Log File Viewer window is launched, displaying the SQL Server log file entries for the log type selected (see Figure 6).

Figure 6. SQL Server logs displayed in the Log File Viewer.

Note

By default, entries are shown in the SQL Server Log File Viewer from newest to oldest. This is different from the default order in the SQL Server 2000 Enterprise Manager, which displayed the log file entries from oldest to newest.


One of the first things you notice when you launch the Log File Viewer is that a tree structure at the top-left corner of the screen shows the log files you are viewing. You can see that there are four different log types available: Database Mail, SQL Agent, SQL Server, and Windows NT. You can choose to display multiple log files within a given log type (for example, the current SQL Server log and Archive #1), or you can select logs from different sources. For example, you can display all the current log entries for SQL Server and the current log entry for the SQL Server Agent.

When multiple logs are selected, you can differentiate between the rows shown on the right side of the Log File Viewer by looking at the Log Source column and the Log Type column. The Log Source values match up with the names shown in the tree structure where the log was selected. The Log Type column shows the type of log, such as SQL Agent or SQL Server. Rows from the different log types are displayed together and sorted according to the date on which the row was created. The sort order cannot be changed.

Tip

You can rearrange the order of the columns shown in the Log File Viewer. You simply click the column header and drag the column to the desired location. When you are viewing rows for more than one log type or multiple logs, it is best to drag the Log Type and Log Source columns to a location that is easily viewed so that you can distinguish between the entries.


Other noteworthy features in the Log File Viewer include the capability to filter and load a log from an external source. You can filter on dates, users, computers, the message text, and the source of the message. You can import log files from other machines into the view by using the Load Log facility. This facility works hand-in-hand with the Export option, which allows you to export the log to a file. These files can be easily shared so that others can review the files in their own Log File Viewer.

SQL Server Utility

The SQL Server Utility was added in SQL Server 2008 R2 and is geared toward multiserver management. It provides several new hooks in the SSMS environment that improve visibility and control across multiple SQL Server environments. Access to these new hooks is provided through a new Utility Explorer that can be displayed within your SSMS environment. This Utility Explorer has a tree-like structure similar to the Object Explorer, and it provides rich content related to the health and integrity of the SQL Server environments you have selected to manage using the SQL Server Utility. Figure 7 shows an example of the type of information the Utility Explorer can display.

Figure 7. Utility Explorer content.

The SQL Server Utility must first be configured to facilitate the display of information in the Utility Explorer. The configuration is relatively straightforward, but you must meet several requirements before starting it. The following requirements apply to the utility control point (UCP), which is the SQL Server instance capturing the information and the SQL Server instances being managed by the UCP:

  • SQL Server must be version 10.50 or higher.

  • The SQL Server instance type must be Database Engine.

  • The SQL Server Utility must operate within a single Windows domain or domains with two-way trust relationships.

  • On Windows Server 2003, the SQL Server Agent service account must be a member of Performance Monitor User group.

  • The SQL Server service accounts on the UCP and all managed instances of SQL Server must have read permission to Users in Active Directory.

In addition, the UCP must be running the Data Center, Developer, or Enterprise Edition of SQL Server.

When you have met these requirements, you are ready to start using the SQL Server Utility. The first steps are to establish a UCP and to enroll SQL Server instances for the UCP to manage. This is accomplished by selecting View on the SSMS menu bar and then selecting Utility Explorer. A content pane is displayed in SSMS that contains options for configuring the SQL Server Utility (see Figure 8). It also contains links to video that can guide you through each step.

Figure 8. Utility Configuration Steps.

The first thing to do when configuring the SQL Server Utility is to click on the Create a Utility Control Point (UCP) link on the Getting Started tab. This initiates a wizard that will guide you through a five-step process that creates the UCP. The first wizard screen that outlines these steps is shown in Figure 9.

Figure 9. Create Utility Control Point Wizard screen.

The first step of the wizard is the most critical because you choose the SQL Server Instance that will be the UCP. The SQL Server instance you select in this step will store the information related to the UCP and any other instances enrolled within that UCP. The information collected by the UCP is stored in a database named sysutility_mdw created on the UCP instance. This database drives the health and status information displayed in the Utility Explorer.

After you complete the wizard steps to create a UCP, the UCP appears in the Utility Explorer Tree, and summary information about the UCP is displayed in the Utility Explorer Content tab. The UCP is the top-most node in the tree and contains other child nodes that contain the different types of information managed by the UCP. An example of the Utility Explorer tree is shown in Figure 10.

Figure 10. Utility Explorer tree.


The first child node displayed in the Utility Explorer tree is named Deployed Data-tier Applications. A data-tier application, or DAC, is a single entity that contains all the database objects and related instance objects used by an application. This includes tables, stored procedures, SQL Server Logins, and so on. DACs can be created from a Visual Studio 2010 data-tier application project or by using the Extract Data-Tier Application Wizard in SSMS.

Note

Two different SQL Server features use the same DAC acronym. The aforementioned data-tier application is one of them, but a dedicated administrator connection is also referred to as a DAC.


After creating a DAC deployment package, you can deploy it to another SQL Server instance. This deployment creates the related database, the database objects along with the related server objects. If the server to which the DAC is deployed is managed by the UCP, you can show the deployed DAC information by clicking on the Deployed Data-tier Applications node of the Utility Explorer.

The next node in the Utility Explorer tree, named Managed Instances, contains information about SQL Server instances enrolled in the UCP. Enrolling an instance essentially means you want to manage the instance through the UCP and gather information about it. You can easily enroll this instance by right-clicking on the Managed Instances node and selecting Enroll Instance.

Each instance enrolled in the UCP is listed at the top of the Utility Explorer Content tab. When a managed instance is selected from this list, a set of resource and policy information is made available in the lower half of the window. The available tabs in this window which define the type of information that is captured include CPU Utilization, Storage Utilization, Policy Details and Property Details. Figure 11 shows two managed instances and the related CPU Utilization graphs for the top-most SQL Server instance.

Figure 11. Managed Instances.

The last node, Utility Administration, can be used to manage policy, security, and data warehouse settings for a SQL Server Utility. These settings drive the SQL Server Utility summary screen and set thresholds across the entities defined in the utility. Figure 12 shows an example of the Policy information that can be managed with Utility Administration.

Figure 12. Utility Administration.

The Policy tab is one of three tabs available on the Utility Administration window. You can see in Figure 12 that there are also Security and Data Warehouse tabs. The Security tab allows you to manage permissions for logins that can administer or read from the UCP. Logins can be assigned to the Utility Reader role on this screen, which allows them to connect to the SQL Server Utility and read information from the Utility Explorer in SSMS. The Data Warehouse tab allows you to adjust the amount of time data will be retained in the UCP data warehouse. The default time period is one year.

Over time, the amount of data collected in the UCP data warehouse can be substantial. By default, each managed instance enrolled in the UCP sends configuration and performance data to the UCP every 15 minutes. Consequently, the space used by the utility management data warehouse (UMDW) needs to be monitored. The UMDW database, named sysutility_mdw, is listed as a user database in the Object Explorer.

Other -----------------
- Microsoft SQL Server 2008 R2 : SQL Server Management Studio - The Integrated Environment
- Managing Exchange Server 2010 Clients : Using Mail Profiles to Customize the Mail Environment
- Managing Exchange Server 2010 Clients : Managing the Exchange Server Configuration in Outlook
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 6) - WMI on Configuration Manager Servers
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 5) - The Configuration Manager Client WMI Namespace
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 4) - Hardware Inventory Through WMI
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 3) - Looking Inside the CIMV2 Namespace
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 2) - Managing WMI
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 1) - WMI Feature Set and Architecture
- System Center Configuration Manager 2007 : Active Directory Integration
- Deploying and Managing BizTalk Applications : Deploying a BizTalk Solution (part 2)
- Deploying and Managing BizTalk Applications : Deploying a BizTalk Solution (part 1) - Steps in Deploying a BizTalk Application
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 3) - Publish the CRM Workflow & Testing the Portal
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 2) - Install CRM Reports & CRM Customizations
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 1) - Install CRM Customizations
- Microsoft Systems Management Server 2003 : Disaster Recovery - Using the SMS Site Repair Wizard
- Microsoft Systems Management Server 2003 : Disaster Recovery - Using the Recovery Expert
- SQL Server 2008 : Data Collector and MDW - Data collection
- SQL Server 2008 : Data Collector and MDW - Setup and configuration
- Microsoft Content Management Server : Adding a Search Page to the MCMS Site (part 4) - Building the MSQuery XML String
 
 
Most view of day
- Accessing and Using Your Network : Accessing Network Resources
- Windows 7 Mobility Features : Using Windows 7 with a Netbook
- Windows Server 2012 Group Policies and Policy Management : Group Policy Policies Node
- Creating DVD Movies with Windows DVD Maker (part 3) - Understanding DVD Movie Options
- Windows Server 2003 on HP ProLiant Servers : Migration Case Studies (part 2) - Eastman Chemical Company
- Microsoft Exchange Server 2010 : Completing Transport Server Setup (part 5) - Getting Edge Subscription Details, Synchronizing Edge Subscriptions
- Microsoft Exchange Server 2010 : Getting Started with Email Archiving - Enabling Archiving (part 1) - Archive Quotas , Exchange 2010 Discovery Operation Considerations
- Reporting in Dynamics AX 2009 : Building Dynamics AX Reporting Services Reports Using Visual Studio
- Windows Server 2003 : Protecting Hosts with Windows Host Firewalls - Firewall Basics
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 4) - Verifying cluster configuration using the Cluster Validation Wizard
Top 10
- Microsoft Project 2010 : Linking Tasks (part 8) - Auditing Task Links,Using the Task Inspector
- Microsoft Project 2010 : Linking Tasks (part 7) - Creating Links by Using the Mouse,Working with Automatic Linking Options
- Microsoft Project 2010 : Linking Tasks (part 6) - Creating Links by Using the Entry Table
- Microsoft Project 2010 : Linking Tasks (part 5) - Creating Links by Using the Task Information Dialog Box
- Microsoft Project 2010 : Linking Tasks (part 4) - Entering Leads and Lags, Creating Links by Using the Menu or Toolbar
- Microsoft Project 2010 : Linking Tasks (part 3) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 2) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 1) - Defining Dependency Links
- Microsoft Project 2010 : Defining Task Logic - Manipulating Your Schedule
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro