Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Testing Troubleshooting

By Exforsys | on December 12, 2005 |
SQL Server 2005

SQL Server 2005 – Testing Troubleshooting

In this tutorial you will learn about Testing and Troubleshooting, SQL server services: Distributed Transaction Coordinator, Registry settings, SQL server settings, Configuring SQL server internal settings, Configuring Network Protocols.

Testing and Troubleshooting

Components of SQL Server may fail individually or individual components may fail when the SQL Server is run. This may be due to installation problems or other errors. A best practice that is followed is to enable the Error and usage Report settings at the time of installation. In SQL server 2005 the Usage data collection and Error Reporting features are enabled by default.

When Error Reporting is enabled SQL Server is configured to sent a report to Microsoft automatically if a fatal error occurs in any of the following installed components.

• The Database Engine
• SQL Server Agent
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Replication

The purpose is to enable Microsoft improve SQL Server functionality. This information is treated as confidential and is sent over secure connections to Microsoft. Error reports contain the following information:

• The condition of SQL Server when the problem occurred.
• The operating system version and computer hardware information.
• The Digital Product ID, which is not used to identify the license.
• The network IP address of the computer or proxy server.
• Information from memory or file(s) of the process that caused the error.

When an error of this type occurs, the user can see the response from Microsoft in the Windows Event log. This feature can be disabled by navigating to the Error and usage Report settings dialog box and clear the checkboxes for Feature usage and Error Reporting.

Feature Usage reporting configures SQL Server periodically to send a report to Microsoft. This includes details about the hardware configuration and how the SQL Server 2005 software and services are being used. The components monitored by this feature include:

• The Database Engine
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Replication
• Business Intelligence Workbench

This information is stored at Microsoft and has limited access. This feature can be disabled as in the case of Error Reporting.

SQL Server creates log files for all SQL Server Components during setup. Logging begins as soon as the Server is launched. Logs can be accessed by opening Windows Explorer and navigating to the relevant folder under All Programs > Microsoft SQL Server > 90 > Setup bootstrap > LOG.

The logging process for Microsoft SQL Server Setup has been changed in SQL Server 2005. The main Log is located in the LOG folder as Summary.txt. If a product failure is displayed in the Log, it is important to investigate the cause. The Product logs are located under files in the LOG folder.

The logs follow a specific naming convention. It contains the enumerated number of the install, the computer name, the Product name and if Microsoft Windows Installer file has been installed more than once during a single setup.

SQL Server Books online, Tutorials, FAQs and detailed documentation on likely problems for the different services provide a mine of information to the user. However, a detailed study of each of these problems is beyond the scope of the tutorials and the user is advised to examine the documentation and the logs for further information in this context.

SQL server services: Distributed Transaction Coordinator

Registry settings

Each instance of SQL server has its own set of data files and program files. The IDs are incrementally generated for each new instance. If any gaps occur due to uninstalls of MSSQL, new ID numbers are not generated to fill the gaps. The instances also share some common files. For purpose of uniquely identifying an instance an all the related program and data files unique instance IDs are generated for each component of a given instance. All common files are located in the folder called 90 under Microsoft SQL Server.

During the installation of SQL Server the instance ID generated for each server component is an ordinal number in the format MSSQL.n. This instance ID is used in the file directory and the registry root.

A registry hive is created for each instance under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n for instance aware components. The mapping of the instance ID is also maintained in the registry.

SQL server settings

Configuring SQL server internal settings

Post installation the components and internal settings of an SQL Server instance can be configured. The installation path is displayed in setup only for features with a user configurable destination folder.

Component

Default path

Configurable or Fixed Path

Database Engine server components

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Binn\

Configurable

Database Engine data files

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\

Configurable

Analysis Services server

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Bin\

Configurable

Analysis Services data files

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Data\

Configurable

Reporting Services report server

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\Bin\

Configurable

Reporting Services report manager

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\Bin\

Fixed path

SQL Server Integration Services

\90\DTS\

Configurable

Notification Services

\90\Notification Services\

Configurable4

Client Components

\90\Tools\

Configurable4

Replication and server-side COM objects

Program Files\Microsoft SQL Server\90\COM\

Fixed path

SQL Server Integration Services component DLL’s for the Data Transformation Run-time engine, the Data Transformation Pipeline engine, and the dtexec command prompt utility

Program Files\Microsoft SQL Server\90\DTS\Binn

Fixed path

DLL’s that provide managed connection support for SQL Server Integration Services

Program Files\Microsoft SQL Server\90\DTS\Connections

Fixed path

DLL’s for each type of enumerator that SQL Server integration Services supports

Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators

Fixed path

SQL Browser Service, WMI providers

\Program Files\Microsoft SQL Server\90\Shared\

Fixed path

Components that are shared between all instances of SQL Server 2005

\Program Files\Microsoft SQL Server\90\Shared\

Fixed path

The Database Administrator needs to ensure that the Program Files\Microsoft SQL Server\Folder is protected with limited permissions. Since the COM directory is used by all instances of the SQL Server, an update impacts all instances of SQL Server on the computer.

SQL Server Configuration Manager Tool helps manage the services and for configuring the network protocols. This is a Microsoft management snap in console that can be accessed from the start menu. It uses the Windows Management Instrumentation (WMI) to view and change server settings. It is a unified way for interfacing with the API calls that manage the registry operations requested by SQL Server tools. SQL Services can also be managed with the configuration manager. Services can be started, stopped or paused. Passwords can be changed and impacted even before starting a service.

Configuring Network Protocols.

The Client and server network protocols and connectivity options can be configured using the SQL Server configuration manager. The protocols are enabled using Surface Area configuration tool and the network connections can be changed. The configuration manager can be used to reconfigure the server connections so that SQL Server listens on a particular network protocol, pipe or port. The ability to force encryption can be set. Alias properties can be viewed or a protocol can be enabled or disabled. Aliases can be created or removed or the order of protocols can be changed. The configuration manager also provides the DBA the ability to view information on failover cluster instances in addition to the abilities being provided by the Cluster Administrator.

The available network protocols of SQL Server are Shared memory, TCP/IP, Named pipes and VI A protocols.

« « SQL Server 2005 – Upgrading from earlier versions of SQL Server
SQL Server Monitoring Tools – Server Profiler » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • XML Data Types in SQL Server 2005

    October 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT