Using SQL Nexus to capture Sql Server Performance Issues


This Tutorial will give you a run through of how you can use the great free tool Sql Nexus from codeplex.com to capture performance data from SQL Server and create a set of reports on how your server is operating

Download Prerequisites

Download the following components on the machine where you wish to carry out the analysis and report creation

SQLNexus
http://www.codeplex.com/sqlnexus

SQLDiag (installed default with SQL2005 and SQL2008)
http://msdn.microsoft.com/en-us/library/ms162833(SQL.90).aspx

PSSDiag (to use with SQL2000)
http://msdn.microsoft.com/en-us/library/aa175399(SQL.80).aspx

RML Utilities
http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-
microsoft-sql-server-released.aspx

Note:

For the machine upon which you need to collect data from you only require the Performance scripts download which can be downloaded from

http://sqlnexus.codeplex.com/Wiki/Info.aspx?title=Sql2005PerfStatsScript&action=Info

There is a version of the scripts for Sql Server 2005 and 2008 so download the appropriate version for the Sql Server you wish to investigate

On the Analysis Server Install the Required Components

On the Server upon which you will be analyzing the collected data install the following

RML Utilities for Sql Server
Install Microsoft Report Viewer 2008 Redistributable

Extract the SQL Nexus download to a folder on your File System e.g. C:\sqlnexus

You are now ready to collect data from your server that you wish to investigate

Set Up Data Capture Process on the Sql Server to be tested

media_1298546712526.png

We now need to setup the Data Capture on the Sql Server that we will be collecting data from using the Performance Scripts downloaded in a previous step

In this example we will be carrying out a data capture on a SQL Server 2005 instance

Download the Performance Scripts for Sql Server 2005 and extract the zip file to a folder on your file system.In this case i used F:\sqldiag

Within this folder you must create a folder called SQLDiagOutput

Next you must edit the batch file that starts the data collection (StartSQLDiagTrace.cmd) and set the path to the correct values for your SQL Server Installation
Make sure you set the path to the output folder and the path to the Sql Server Tools/binn directory
Save the file

Launch the Data Collection

media_1298547186028.png

When you are ready to start the data collection navigate to the folder that contains the StartSQLDiagTrace.cmd via a cmd line and start the batch file

This will create a temporary service called SQLDIAG on the system and start the data collection of a set of Performance Monitor Counters and a Sql Server server-side trace and output the data to the SQLDiagOutput Folder

Allow the data collection to run for sufficient time to collect data when your performance issue occurs

Note:

On a busy system quite some data can be collected!!!!. Watch your free space carefully! Have it running for maybe 10 mins and then stop the SQLDiag service.

When you have collected sufficient data run the stop batch file StopSQLDIagtrace.cmd

This batch file will finalize the data collection and remove the service called SQLDIAG

Once the batch file has finished running you can then zip up the contents on the SQLDIAGOutput folder and copy it to your server upon which you have SQL Nexus installed for analysis

Launch SQL Nexus for Analysis of Collected Data

media_1298548162430.png

Launch the SQL Nexus Tool by running the binary sqlnexus.exe binary
It will then prompt you for connection information to the SQL Server where you wish to store the collected data and analyze it

Create the Connection to an existing SQL Nexus Database or Create a new one

media_1298548209909.png

SQL Nexus uses a standard Sql Server Database to store the imported data from the collection scripts and also the analyzed data and reports
You need to either choose an existing SQL Nexus Database ,using the drop down menu in the top right drop down menu

Import the Collected Data

media_1298548801751.png

Click the Import Link on the Left hand side and navigate to the file system location where you have stored the contents of the data collected from the server to be analyzed (Contents of SQLDiagOutput Folder)
Use the Options link to set the options shown in the above screen shot
Then Click the Import button to start the import of the collected data into SQL Nexus and to start the analysis.

Examine the Reports

media_1298549191017.png

Clicking on the Reports Link on the Left hand Side we can then examine the various reports that the tool creates
The reports can be exported into an excel sheet (Other report formats are listed but Excel is the only one that works in the current release of SQL Nexus).
Some of the reports are shown below

Blocking and Resource Waits Report

media_1298549212058.png

SQL Server 2005 Perf Stats Report

media_1298549232810.png

SQL Server 2005 Bottleneck Analysis Report

media_1298549255240.png

Top Unique Batches Report

media_1298549272625.png

How to Setup a Test LDAPS Bind using Softerra LDAP Browser


This lesson will show you how to configure Softerra’s LDAP browser to communicate with Active Directory over a secure LDAP connection for testing purposes

Ensure your Active Directory Server has a certificate in place

Check that your Active Directory Server will allow LDAPS connections

Export the LDAPS Certificates using the Netscape v4 Browser

Download and install the Netscape web browser version 4.x. Note that it’s vital to use version 4.x – later version like 5+ or Firefox use newer certificate store format which is incompatible with the version used by LDAP Browser 2.x.
Run the Netscape browser.

Open URL: https://yourserver:sslport/, where:

yourserver – Your LDAP server address, provided it is an IP or host name. For example: 192.168.234.33 or ldap.mycompany.com.
sslport – A TCPIP port number used by your server to accept SSL connections. Usually this port number is 636.

You’ll see the Netscape Certificate Name Check window. Follow the instructions provided there and accept the server certificate for this and future sessions.

Close the Netscape browser
Copy the key3.db and cert7.db files from the Netscape user profile directory to the LDAP Administrator or LDAP Browser root directory overwriting the existing files.

Modify Your LDAP Browser Connection Profile

media_1279707456462.png

Restart LDAP Administrator or LDAP Browser.
* Open the server profile.
* Change Port number at the General tab. Press Apply.
* Check the Try to use SSL box at the LDAP Settings tab. Press Apply.
* Press OK

Check you can bind successfully over LDAPS

How to Connect SLES 10 to a Free NAS iSCSI Server


This How To will give you a guide on how to connect a Suse Linux Enterprise Server 10 SP1 (SLES) to a Free NAS Server ,using iSCSI.
It is assumed that you have built and configured a Free NAS Server with working iSCSI Targets as detailed in the previous How To on creating a VMware of Free NAS

Install the iSCSI Initiator Package in SLES

media_1252509812673.png

Using the Software Management function in YAST add the iSCSI Initiator packages to your system.
Don’t forget to autocheck package dependencies.

Configure the iSCSI Initiator to connect to your Free NAS server

media_1252510089535.png

Once you have installed the iSCSI initiator packages it needs to be configured to connect to your iSCSI target in Free NAS
Fire up YAST and go to the Miscellaneous section where you will see iSCSI Initiator.
Launch iSCSI Initiator

Set the iSCSI Initiator Service to start when booting

media_1252510221993.png

Once the iSCSi Initiator GUI has launched on the first Tab set the Service Start to when booting

Add the iSCSI target

media_1252510331083.png

One the second Tab of the iSCSI Initiator you now need to input the Free NAS Target
Go to the Connected Targets Tab and click add
Input the IP address of your Free NAS Server and leave the TCP Port as the Default
For Free NAS keep the No Authentication Check box in place.
Click Next and the GUI will return to the previous screen with your target attached

Establish Target Connection

media_1252510588808.png

Once your new Target has been added Click Connect and in the next screen keep No Authentication checked
Click Next
You should now return to the screen that shows the list of iSCSI Targets and the one you just added should have a status of connected.
Click Next again to return to the main iSCSi initiator Screen

 

Change Startup

media_1252511050879.png

In the iSCSI Initiator GUI on the Connected Targets Tab Toggle startup to automatic by clicking the Toggle Startup button.
Click Finish.

Adding the iSCSI Target to the Linux File system

media_1252511218981.png

The next step is to add the newly added iSCSI target to the Linux File system using the YAST tool Partitioner
From YAST launch Partitioner from the system section.
In this screen you should see your iSCSI targets under the /dev/sdb and /dev/sdd devices in my example above
You next need to create a Linux Partition based on your iSCSI target and then assign it a mount point

 

 

Creating the Linux Partition and assigning a mount point Part One

media_1252511493828.png

Next Click the Create Button to convert Disk to Partition
Choose the relevant device for example /dev/sdd in my example
Click OK and Choose Primary Partition and then OK again

Creating the Linux Partition and assigning a mount point Part Two

media_1252511846477.png

You now need to format the newly created partition and set a mount-point
In my case I am using a mount point of /freenas
Click OK to return to the main Partitioner screen and don’t forget to click Apply
A pop up will appear asking if you really want to make the changes and choose apply
It will then return to the main partitioner screen where you should see your new mount point
If everything is ok Click Quit to finish
Close YAST

Check the new Mount-point

media_1252512153315.png

Now go to terminal and check that the new mount-point exists and is writeable
cd into your new mount-point and test it is writeable by touching a file there.
Well done you have successfully completed this How To !!!!!!

Apache LDAP Authentication How To


This lesson will show you how to install and configure Apache 2.2.x on Windows with LDAP Basic Authentication against Microsoft Active Directory

Install Apache 2.2.x

Download the latest Apache 2.2.x installer from http:://www.apache.org

Download the AUTH LDAP module for Apache 2.2 and also SUN C++ SDK

Download the SUN C++ SDK for Windows NT4 optimized version from the SUN SDK Website http://www.sun.com/download/products.xml?id=3ec28dbd
Download the version 3.11 of the pre compiled mod auth ldap dll from here http://muquit.com/muquit/software/mod_auth_ldap/mod_auth_ldap.html#directives Choose MS Windows binary v3.11 for Apache 2.2.2

Extract both zip files
Place the file mm_mod_auth_ldap.dll into the Apache modules Directory
Place the dlls from the SDK extracted zip file into the Apache Modules Directory

Open the Apache Httpd.conf file in your Favourite Text Editor

media_1243529535740.png

In order to control which URL requires LDAP authentication we use an Apache Location Directive

Add the Following to your httpd.conf and modify to fit your environment

All Values in the Screen Shot above with Red Arrows must be changed

# LDAP Stuff
LoadModule mm_auth_ldap_module modules/mm_mod_auth_ldap.dll

LDAP_UseMemoryCache_G On
LDAP_HashTableSize_G 1024
LDAP_CacheTTL_G 5000
LDAP_Persistent_G On
SupportNestedGroups_G On

<Location /wordpress>
Options Indexes FollowSymLinks
AllowOverride None
order allow,deny
allow from all
AuthBasicProvider “mm_ldap”
#AuthLDAPAuthoritative Off
#AuthAuthoritative Off
#AuthOnBind On
#Sub_DN “ou=CIS,ou=People”
Bind_Tries 3
AuthName “Authorized imaginary staff only”
AuthType Basic
LDAP_Debug On
LDAP_Server 192.168.254.40
LDAP_Protocol_Version 3
LDAP_Connect_Timeout 1000
Base_DN “CN=Users,DC=sjdec,DC=local”
UID_Attr “sAMAccountName”
Bind_DN    “CN=Administrator,CN=Users,DC=sjdec,DC=local”
Bind_Pass “livelink”
require valid-user
</Location>

Save the Httpd.conf and restart Apache

Test the Page in a Browser

media_1243529769751.png

Call the page defined by the Location Directive in a browser and a login popup should appear
Then Login with a LDAP User and Password

Use The Firefox Addon Live HTTP headers to check authentication

media_1243529910661.png

Install the Firefox Addon Live Http Headers and then repeat the login test above and view the HTTP response headers to check that authentication is working as expected
You should see the server issue an HTTP 401 when the page is called and then Basic Authorization being set once the user has logged in