Readme for ASTrace Utility Sample

  • Article

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.

The ASTrace utility gives you the ability to capture an Analysis Services trace and log it into a SQL Server table. The table can then be queried or read using SQL Server Profiler. The ASTrace utility runs as a Windows service that connects to Analysis Services, then creates a trace, and logs trace events into a SQL Server table using the SQL Server Profiler format. The ASTrace utility creates the trace by using a standard trace template that you can author using SQL Server Profiler.

Scenario

Analysis Services does not have a built-in tool to extract traces and save them to a file or table. This ASTrace utility sample demonstrates how to implement that trace functionality.

Languages

C#

Prerequisites

Before running this sample, you should have Microsoft SQL Server and the following SQL Server components installed:

  • Database Engine

  • Analysis Services

  • SQL Server Management Studio

  • Business Intelligence Development Studio

    Note

    Business Intelligence Development Studio is not supported on computers that run the Itanium-based operating system; the 64-bit development environment is supported on x64-based operating systems. SQL Server samples can be modified and run on computers that run either x86 or x64-based operating systems when databases used by the samples are deployed on an Itanium-based operating system.

  • An updated version of the AdventureWorks database.
    You can download this sample database from the Microsoft SQL Server Samples and Community Projects Web site at http://www.codepex.com/sqlserversamples. For more information about how to download and install sample databases, see Installing SQL Server Samples and Sample Databases in SQL Server Books Online.

  • An updated version of the SQL Server Database Engine samples.
    You can download this sample from the Microsoft SQL Server Samples and Community Projects Web site at http://www.codepex.com/sqlserversamples. For more information about how to download and install samples, see Installing SQL Server Samples and Sample Databases.

You also need .NET Framework SDK 2.0 or Microsoft Visual Studio 2005 installed. You can obtain .NET Framework SDK free of charge. For more information, see Installing the .NET Framework SDK in SQL Server Books Online.

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

  1. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

    -- or --

    Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

  2. Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

    Note

    To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.

  3. At the command prompt, run the following command to generate the key file:

    sn -k SampleKey.snk

    Important

    For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

Option 1: To build the sample by using Visual Studio

  1. Locate the folder where the solution file ASTrace.sln is stored.

  2. Move the signature file ASTrace.snk to the solution folder.

  3. Open Visual Studio.

  4. On the Recent Projects tab, for Open, click Project.

  5. Open the folder where you saved the solution file ASTrace.sln, select the file, and then click Open.

  6. Press F6 to build the project.

Option 2: To build the sample by using msbuild.exe from .NET Framework SDK

  1. Locate the folder where the solution file ASTrace.sln is stored.

  2. Move the signature file ASTracer.snk to the solution folder.

  3. Open a Command Prompt window and change the directory to the location where the solution file is saved. Configure the environment according to the specifications in How to: Compile at the Command Prompt.

  4. Type msbuild.exe ASTrace.sln, then press Enter.

Installing the Sample

The following procedures describe how to install the ASTrace utility as a service.

Step 1: To copy executable files

  • Copy the following files into the folder where you intend to have the service running on your computer:

    • ASTrace.exe
    • ASTrace.exe.config
    • Standard.tdf

Step 2: To register the service

  1. Locate InstallUtil.exe in your installed version of the Microsoft .NET Framework SDK 2.0.

  2. Open a Command Prompt window.

  3. Change the current directory to the directory where you copied the executables files in previous step.

  4. Add the folder that contains InstallUtil.exe to the path.

  5. Run the following command to install the service:

    InstallUtil.exe ASTrace.exe

  6. In Administrative Tools, open Computer Management, expand Services and Applications, and then select Services.

  7. Search for ASTrace Service in the right pane and double-click it. Verify in the General tab that the Path to executable shows the correct path to where you copied the files. The service is now registered and using your files.

    Important

    Do not start the service until it has been configured. You will configure the service in the next step.

Step 3: To configure the service

  1. Open the ASTrace.exe.config file in Notepad or any other text editor that does not include control characters.

  2. Add content to the following fields according to their definition:

    • AnalysisServerName – the name of the instance of Analysis Service to trace.
    • SQLServer – Name of the instance of SQL Server where the trace will be logged.
    • SQLServerDatabase – the name of the SQL Server database that will hold the trace tables.
    • TraceTableName – the name of the table to log the trace into.
    • TraceDefinition – the name of the file that contains the trace template you used to create the Analysis Services trace. If you want to your modify ASTrace template, do so by using SQL Server Profiler.

Running the Sample

The following procedures show how to start the service and configure security.

Step 1: To start the service

  • Open Windows Service Control Manager, navigate to the ASTrace utility and start the service.

    If the service starts successfully, it means it was able to connect to an instance of Analysis Services and create a trace; also, it was able to connect to SQL Server, create a trace table and started logging to it.

    If any problems occur during startup, you will see the error logged into the ASTraceService.log file created in your local installation directory. Check the names again make sure all server names are correct and the ASTrace service has appropriate security permissions. The ASTrace utility is installed using LocalSystem account to enable you to test its functionality.

Step 2: To configure security

  1. Make sure that you change the ASTrace service account to an appropriate local or domain user account. This will minimize the surface attack area.

  2. To run the ASTrace service application, you must make sure that the service account that runs the service has following rights:

    • Analysis Services administrator permission.
    • SQL Server database administrator rights to create a table in the database you specified and log events.

Removing the Sample

Use the following procedure to uninstall the service.

Step 1: To uninstall the ASTrace service

  • Open a Command Prompt window and run the following command:

    InstallUtil.exe -u ASTrace.exe

Step 2: To remove the ASTrace sample

  • Remove your solution folder.