Saturday, October 4, 2008

Using SQL Server 2008 Express with LoadRunner Analysis

After trying several methods to avoid having LoadRunner's Analysis run out of memory with large files, which I will share in another post, I finally broke down and decided to install SQL Server. I saw a post on the internet that said not to use the SQL version 7 that came with the LoadRunner CD for version 7.8 and use MSDE 2000. That was obviously a very old article. :) I found other articles in which people failed to get LoasRunner to work with the latest SQL server version with no solution. Hopefully, this article helps resolve some of the issues people ran into. I, for one, wouldn't have figured out some of the obstacles without the help of a SQL guru. For the installation I used the latest LoadRunner 9.1 and SQL Server Express 2008.

Why should you use SQL Server?


From HP's help:
"If your Analysis result data exceeds two gigabytes, it is recommended that you store it on an SQL server or MSDE machine."

Installing SQL Server


Before you install SQL Server Express, you must first install the following:


Once installed, you should install SQL Server 2008 Express with Advanced Services. This comes with Management Studio which we will make use of.

When installing SQL Server make sure to install the two items highlighted in red:



Once installed to see if your installation is working, open up a command window and type the following (assuming you names your instance SQLEXPRESS):

C:\Documents and Settings\blah>sqlcmd -S (local)\SQLEXPRESS
1>

If you see the 1>, this means that your instance is working.

Configuring Analysis



Go into Analysis and go to Tools > Options > Database




  • Select "SQL Server/MSDE".
  • For a server name, enter: [machine name]\[DB instance name]. (This isn't fully detailed in the HP help. It might be obvious to those who have installed SQL Server before, but it took me awhile to figure this out. You instance name will most likely be SQLEXPRESS. I appended a 2 when installing since I had a previous version of SQL installed which had taken that name.)
  • Select "Use Windows Integrated Security"
  • Click "Test Parameters," you should be able to see the following:




Hopefully, "Connection parameters" now works. We'll now tackle the "Shared server directory exists on server.", "Write permissions on shared server directory." and the "Synchronizing shared and physical server directories."

Whether the above works or not, go to the Windows Start button > Programs > Microsoft SQL Server 2008 > SQL Server Management Studio. You'll be presented with a screen which should look like:



If your Server name in the config didn't work above and doesn't match the one shown when you see the screen above, try using that one. If it did work, hit connect. Expand out the databases and highlight master.



Right-click and go to properties. Select files and copy the path name:



Go to the path in Explorer and share the Data directory. Do this by right-clicking on the folder and going to properties then Sharing. Input a name, then click Permissions.



In permissions, give everyone full access. I do this just in case. I hate nitpicking with permissions to things.



Your directory should have a little hand on it now:



Double check to see if it is shared by going to \\[machine name]\[share name]



Go back to Analysis and input the shared directory we just set up for "Logical Storage Location" along with the original directory for the physical storage location. Test the parameters again.



If you get an error on "Synchronizing shared and physical server directories." . Right-click on "My Computer" and select manage. under System Tools > Event Viewer > Application.



Look for any MSSQL messages. If you see the following:



This issue is described on Microsoft's site. Go back to Management Studio, click "New Query" and enter in the following:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO



Hit the "! Execute" button.

Hopefully, this fixes the issue and you are able to save the Analysis configuration.

Opening a file



When opening a file, you may run into an issue where after opening results half way through, it hangs.

Check the Event Viewer again:

If you see the following:



Create a new query in Management Studio with the following (changing 'user' to your login) and hit the "! Execute" button.:

EXEC sp_grantlogin 'Domain\user'

Then change it to the following (changing 'user' to your login) and hit the "! Execute" button.:

EXEC sp_grantdbaccess 'Domain\user', user

Hopefully this allows you to open up the file now. This will now allow Analysis to be a bit more stable.

However, it still won't totally solve Analysis's out of memory issues if you set the granularity to really low (1 second) for a long run.:



Deleting the results



When Analysis closes, it will delete the table from the database. If not, you can delete the table from the management studio by right-clicking on the table and selecting Delete. I do not recommend deleting the physical file from Explorer directly.