SQL Diag Configuration Tool


SQL Diag Configuration Tools is an easy to use tool which will help you in configuring SQLDiag.xml to collect performance data from SQL Server 2005/2008/2008 R2

SQL Diag Configuration Tool is a free tool available on Codeplex. You can download it from the following link

http://sdct.codeplex.com/ 

Let us walkthrough the steps of using SQL Diag Configuration Tool

After installation, run the tool

image

Select your desired SQL Server version and then click on “Authentication Method” option available on left side

image

Specify the Server name and SQL Server Instance Name on which you are planning to use the SQLDiag.xml and then click “Machine Wide Diagnostics” option

image

Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”

image

If you want to collect Blocking information, select “Blocking” and the specify the Maximum File Size of log and the maximum number of files and the polling interval on which the Blocking information should be collected from SQL Server

Next Click on “Profiler" Options

image

Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.

Available Trace Templates are

image

After selecting, you can also add further Events you want to collect and then specify the maximum file size, maximum number of files and polling interval on which profiler information should be collected from SQL Server

Next click “Perfmon Options” to proceed

image

Select the Performance Counters which you want to collect or select the predefined template available and then specify Maximum File Size and the polling interval on which performance counter data should be collected

Next Click on “Producers” to enable the Producers options

image

Next Click on the “Analyzers” to enable the required Analyzers

image

Click “Save” to SQLDiag1050.xml and use it as input for SQLDiag to collect SQL Server Performance Data

You can use the  “Open” option reload the saved SQLDiag xml file and modify the profiler or perfmon or other above specified options.

I hope you find this useful…

Advertisement

Issue of using Static Methods calls and Unhandled Exceptions


Recently one of my fellow developer had an issue of application crashing after making a new change.

Application keeps crashing and errors are not getting logged

While checking the Event Viewer found the following details

Event Type:      Error

Event Source:   .NET Runtime 2.0 Error Reporting

Event Category:            None

Event ID:          5000

Date:                4/18/2011

Time:                12:52:55 PM

User:                N/A

Computer:         XXXXX

Description:

EventType clr20r3, P1 dataprocess.exe, P2 1.0.0.0, P3 4da27055, P4 dataprocess, P5 1.0.0.0, P6 4da27055, P7 30, P8 b, P9 system.typeinitialization, P10 NIL.

Developer spent almost more than 5 hours to troubleshoot the problem.

Finally issue was due to the Static calls

public partial class Form1 : Form
{
  public static String _email_DevTeam = Configuration.GetValue("Email_DevTeam").ToString();   public static String _email_SentFrom = Configuration.GetValue("Email_SentFrom").ToString();   public static String _smtp = Configuration.GetValue("smtp").ToString();

Configuration.GetValue method didn’t had Exception handling and since Static calls being made before constructor or Form_Load() Event, Exception handling code in Form didn’t work.

Developers, Beware of using Static Functions !!!

Make sure all methods are having exception handling !!!

Migrating Access Databases to SQL Server “Denali”


SQL Server Migration Assistant for Access 5.0 has been released and available for download here

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4b37df74-7522-41cf-9c1a-01b6415d9608

New version now supports SQL Server “Denali”

image

Free SQL Azure Diagnostics Tool has been released !!!


You can download and install SQL Azure Diagnostics tools from

http://csssqlazure.blob.core.windows.net/csssqlazuredeploy/publish.htm

image

Click “Install” to download the setup, After download, run the setup

image

Click “Install” to proceed

image

After Install, Start “CSS SQL Azure Diagnostics”

image

Please provide SQL Azure server name, Database name, User name and Password and then click “Go”

Now you should able to view the following reports

1. Top 10 CPU Consuming Queries

image

2. Top 10 time taking Queries

image

3. Top 10 Logical I/O consuming queries

image

4. Top 10 Physical I/O Consuming Queries

image

You can export the report to Excel, PDF or Word

image

You can also print the report.

Thanks to Microsoft Customer Service and Support (CSS) SQL Support for providing such a nice tool freely !!!

Migrating Access Databases to SQL Azure


Step 1: Download and Install “Microsoft SQL Server Migration Assistant 2008 for Access v 4.2” from

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b&displaylang=en

Step 2: Register SSMA 2008 for Access and download License and install the License

Step 3: Run SSMA 2008 for Access v 4.2

image

Click Next to create a new Migration Project

image

Specify Project Name and Select Project File Location and select Migration To: “SQL Azure

image

Click “Add Databases” to Select the Access Database(s) you want to migrate

If you are not sure the location of your database , click “Find Databases” to find it

image

You can search using the following criteria

1. Specify a folder such as C:\Databases

2. Specifying a part of the file name

3. Created or Updated Date

4. Size of the database

5. Owner of the database

image

After adding you databases, Click “Next” to proceed

image

Select the Tables and Queries you want to Migrate

image

Specify “SQL Azure” Server Name, username and password and the click “Browse”

image

Click “Yes” to Continue

image

You can select any existing SQL Azure database, or Create New SQL Azure Database

image

Specify Database Name, You can select Database Size as 1 GB or 10 GB and then click “Create”

image

Click “Ok” to proceed

image

If you still want to continue using Access as Front End for your SQL Azure database, check “Link Tables” option to create Linked Tables in your Access database and click “Next” to proceed

image

Once the scripts are prepared and ready, you will be prompted to confirm the SQL Azure information again

image

Enter the Password and click Connect

image

Review the List of tables and its associated Indexes and Triggers ready to be migrated and then click “Ok” to proceed

image

The above Legends explains the directions of data migration

image

You can click on legends “Blue Arrow” to change the direction, in the above screenshot, we have selected “Order Details” not to be migrated

Click “Ok” should start the migration process

After migration, you should be able see the tables migrated in SQL Azure server

image

For all the tables, a TimeStamp field will be added as last column for enabling continuous Data Migration

image

Using SQL Azure Metadata Explorer, You can generate Reports to identify the issues in migration or Migrate data from one or multiple tables

image

Sample Migration Report

image

image

Table level report

image

Now you can access your data in SQL Azure using SQL Server Management Studio or SQL Azure Management Portal or Any Applications supporting SQL Azure connections

image

Happy Migration !!!

%d bloggers like this: