Archive for October, 2010


After some time adminitering BizTalk 2006 R2, I was told to deploy the BAM Portal site. The BizTalk group was formed by 3 servers plus the SQL backend, so I wanted to have my BAM portal balanced too. This sounds reasonable and, if we’re talking of a platform such as BizTalk, it should be fairly easy.

I knew for sure I was going to use another set of servers, which were already IIS balanced. After digging a little, I found this article on the MSDN to configure the NLB for BAM Portal. So, after some tests on the Development and QA environments, I prepared the following list of changes, which is a combination of the MSDN article and other things not included there.

Configuring BAM Portal

As I mentioned before, I had a multi-server environment and the BAM portal application was going to be installed into another pair of IIS servers, not on the biztalk ones. Anyway, I found that in order to make this up and running, I needed to install and configure BAM Portal in one of the BizTalk servers first, and once configured, install the BAM Portal on the other IIS servers and do the changes.

For this post, let’s assume the following conditions:
BizTalk Servers: BT01.domain.local and BT02.domain.local
BAM Portal Servers: IIS01.domain.local and IIS02.domain.local
Balanced URL: http://Portal.domain.local/BAM

1. Install and configure BAM portal on BT01.domain.local. This is because the portal configuration is only made on the first computer. All the others are configured manually.
2. Install the BAM Portal components on IIS01.domain.local and IIS02.domain.local. Once installed, join the two of them to the BizTalk group using the account Domain\BizBAMMng.
3. On BT01.domain.local, open a command window, navigate to “drive:\Program Files\Microsoft BizTalk Server 2006\Tracking” and execute the following: bm get-config -FileName:C:\BAM_Config_Prod.xml
4. Go to “C” drive and make a copy of BAM_Config_Prod.xml. Name it BAM_Config_Prod_New.xml
5. Edit the file C:\BAM_Config_Prod_New.xml with notepad and change the <globalproperty name=”BAMVRoot”> property. That value should reflect the NLB name, for example, it could be <globalproperty name=”BAMVRoot”>http://Portal.Domain.local:80/BAM</globalproperty&gt;
6. Save the file and import it to BizTalk running the following from a command line: drive:\Program Files\Microsoft BizTalk Server 2006\Tracking\bm.exe update-config -FileName:C:\BAM_Config_Prod_New.xml
7. Open the registry and export the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\BAM. Save it to C:\BAM_Export.reg
8. Login into IIS01
9. Copy the file \\BT01\drive$\Program Files\Microsoft BizTalk Server 2006\BAMPortal\web.config to drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal
10. Open the web.config file using notepad located on drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal
11. Change the following values to reflect the local URL address. In this example, I’m using the default web site
a. <add key=”BamQueryWSUrl” value=”http://IIS01:80/BAM/BAMQueryService/BamQueryService.asmx&#8221; />
b. <add key=”BamManagementWSUrl” value=” http://IIS01:80/BAM/BAMManagementService/BamManagementService.asmx&#8221; />
12. Add the following key under <system.web>: <machineKey validationKey=’6FFF3A2DB6F94A679FB9FB6D0BDDBCE9FEE4B3D6C1B220CEA11C23429331B52F4BC8FCC49BD122F272CF2816B0B392A2FB61CB030AC7138EF2A86061FEAF97DD’ decryptionKey=’B50BC9EA1B49B62D861E8645ED818596AD9984B248F3BE64′ validation=’SHA1’/>
The MachineKey value must be added because of the NLB. I found random issues when the requests are started in one node and completed on the other. Adding this ensures that the MachineKey is the same on all the nodes. For this case, I’ve used the automatic key generator available on http://www.orcsweb.com/articles/aspnetmachinekey.aspx
13. Save the web.config file and close it.
14. Copy the file \\BT01\drive$\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMManagementService\web.config to drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMManagementService
15. Copy the file \\BT01\drive$\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMQueryService\web.config to drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMQueryService
16. Open the web.config file using Notepad located on drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMQueryService
17. Under the <runtime>key, locate the <codebase>key and validate the path on the href value. For example, the key should be like the following: <codebase version=”″ href=”file:///drive:/Program Files/Microsoft BizTalk Server 2006/BAMPortal/BAMManagementService/bin/Microsoft.BizTalk.Bam.WebServices.dll”/>
18. If the reference to the library is OK, close the web.config file. If not, change it to reflect the real path, save the changes and close it.
19. Open IIS Manager and create one Application Pool named BAMAppPool. Set the identity on the application pool to be Domain\BizBAMAppPool
20. Create a Virtual Directory called BAM an point it to drive:\Program Files\Microsoft BizTalk Server 2006\BAMPortal. Change the setting so that the Virtual Directory uses the BAMAppPool. Also ensure that the security is set to Integrated Windows Authentication, that Scripts are allowed and that the ASP version in use is the .NET 2. Finally, change the default page to be default.aspx
21. Right click the BAMManagementService folder, go to Properties and create the Application Name BAMManagementService. Configure it to use the BAMAppPool application pool and the .NET 2. Follow the same procedure for the folder BAMQueryService
22. Import into the registry the values of the file \\BT01\C$\BAM_Export.reg
23. Get the aspnet_setreg.exe program and copy it to a local folder. It can be downloaded from <a href=”http://support.microsoft.com/kb/329290/en-us”>http://support.microsoft.com/kb/329290/en-us</a&gt;
24. Open a command window, navigate to the folder where the aspnet_setreg.exe is located and execute the following: aspnet_setreg.exe -k:”SOFTWARE\Microsoft\BizTalk Server\3.0\BAM\WebServices\identity” -u:”Domain\BizBAMMng” –p:”accout password”
25. Grant Read access to the account Domina\BizBAMAppPool to the registry path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\BAM\WebServices
26. Grant Read access to the account Domain\BizBAMMng to the registry path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0
27. Add the accounts Domain\BizBAMMng and Domain\BizBAMAppPool to the local group IIS_WPG
28. Run the following from a command window: c:\windows\system32\cacls “%windir%\Microsoft.NET\Framework\v2.0.<min>\Temporary ASP.NET Files” /T /E /G Domain\BizBAMMng. Be sure that you change the <min>for the valid version value.
29. Run the following from a command window: c:\windows\system32\cacls “%windir%\Microsoft.NET\Framework\v2.0.<min>\Temporary ASP.NET Files” /T /E /G Domain\BizBAMAppPool. Be sure that you change the <min>for the valid version value.
30. Grant the accounts Domain\BizBAMMng and Domina\BizBAMAppPool Modify access to the folder %windir%\temp
31. Repeat steps 8 to 30 for server IIS02, changing the value IIS01 to IIS02 where suitable.


Read Full Post »

SQL Unit Testing


Some time ago, I was asked to configure automatic builds in Team System for a solution, and that build should include as much unit tests as it could. This solution contained a mix of web sites, a wizard installer, OLAP cubes, Reporting Services reports and a set of databases. Because it was in the middle of a refactor, the development team would include tests together with the refactor on the web sites and the wizard installer had a set of unit tests for the installation modules. So I started to look for a way to test the deployed databases.

After searching for a while, I found a good tool in CodePlex called SQL Load Test. This tool has the ability of translating a SQL Profiler trace file into a Visual Studio 2005 C# file, generating unit tests for each entry in the trace file. This output file is intended to be added into a Test Project.
So I downloaded the tool and I read the usage notes: really simple. There are just a few considerations for setting up the SQL Profiler, which are:


The following columns must be present:

– EventClass
– TextData
– DatabaseName – if this is omitted then the trace is assumed to be all for the same database
– Events

The following events must be present:

– SQL:BatchStarting
– RPC:Starting
– Audit Login
– Audit Logout

Additionally, the tool will ignore the following entries:

– All entries related to the master, model and msdb databases.
– All entries with no database name at all.
– All calls to sp_reset_connection.

So, after setting the profiler with the above requisites, I’ve asked a QA co-worker to navigate through a set of different Reporting Services’ reports. After some minutes (about 5 or 6 reports, no more than 10 minutes spent), I stopped the profiler and save the trace to a file.

Using the Unit Test

Now, the following step was using the tool and transforms that trace file into a C# unit test. Well, nothing could be as easier as that. If you read the documentation, you’ll see that the tool is a command-line application, and that only three parameters are needed. The first one is a name for the scenario. This is a C# style name. Then, the path and the name of the trace file. Finally (and optional), is the path and name of the configuration file. This configuration file is provided by the tool, and the settings are really simple, like creating a test method for each operation or just one with all the operations, etc. If you don’t set a custom configuration file, the application will load the default parameters, which are fine for the initial tests.

I run the tool and in a few seconds I had my C# file containing the tests. Then I added the file into my project, configure the SQL connection string, and Voila: I had added more than 500 unit tests in a few minutes. I repeated this with my co-worker for different reports and modules of the application, and after 2 hours I had more than 4000 unit tests, separated into modules (Reports, OLAP queries, transactional queries, etc).

For demonstrating the use of the tool, I have a Solution called SQLUnitTesting with two projects: One database project (SQLUnitTesting) and one test project (SQLUnitTesting_TestProject). The database project has 1 table and 1 stored procedure. The sp is used to insert one record in the table. Also, I’ve configured the profiler to monitor the database SQLUnitTesting, executed in Management Studio two queries (one insert command and the sp) and save the trace file as SQLUnitTesting.trc.

After this, I run the following command from the command line to convert the trace file into the C# unit test

As you can see, the first parameter is the name (I used the same as the test project namespace), the name of the trace file and the name of the config file. This file is the default file that comes with the tool.

Next, two files called SQLUnitTesting_TestProject.cs and SQLUnitTesting_TestProject.stubs are created on the application folder. The first one contains the unit tests and the second contains the connection details. You can discard the .stubs file and use only the .cs file. Then, I’ve renamed the .cs file to SQLUnitTesting_TestProject01.cs and add it to my test project:

Finally, you must set the connection string in order to connect to the SQL server. You can do this using the method you want. For this test, I’ve added the following lines on the TestMethod that call the unit tests:

_connection = new SqlConnection(“Initial Catalog=SQLUnitTesting;Integrated SSecurity=SSPI;Data Source=(local)”);

After all this is done, you can run the test from visual studio and see the results:

As you can see, the report indicates I run only one test. This is because the tool created all queries in a single TestMethod:

public void SQLUnitTesting_TestProject()

If you want to see one test per statement, then you can separate them into different TestMethods. Attached, you’ll see the .cs file for my project for better understanding of how the statements are executed


Although there are lots of tools down there for unit testing, and even though Visual Studio provides Unit Testing for SQL, I found in this tool a great value: The user variable. Because these tests are based on database usage, you can have real-life queries to databases. Suppose you can launch the profiler on the production server and leave it running for one or two days. Then, you’ll have tests that reflect what users do. In addition, you can configure the Visual Studio Test Load Agent and monitor the performance of your SQL server during the execution of the test, simulating concurrent connections and so on.

I know that this tool won’t replace the Unit Tests a developer should do on the database solution, and also that there are lots of tools that can achieve similar results, but this application is a great starting point, allowing the configuration of unit tests easily and without knowing the code behind your databases.

In my case, this tool has provided me a great way for managing integrity checks on the solution databases. Usually when a change is made on a complex database, a break can occur in the most unthinking procedure or query. At that time, the dev team was changed from one contractor to another one, so until the new devs really knew the application, this approach helped to avoid releasing a build that has errors on the databases.

Read Full Post »

Some time ago I was dealing with a performance analysis on a SQL server, so I was using a combination of tools and scripts trying to find the root cause. After that, I decided to move a step forward and customize the sp_who2 that SQL 2005 introduced. What I wanted to combine on the same query was the processes running and, if there’s a lock, I wanted to know the statement causing it.

After playing around a little, I came with this stored procedure. The parameters that you can pass to it are just two: Show only transactions that are locked and show the application that executed the transaction. I excluded some columns that the normal sp_who2 shows because my goal was to quickly find statements that cause locks. Feel free to change it at will. This query can be improved, so if you want to use it as a starting point for more robust scripts.

CREATE PROCEDURE [dbo].[pla_who]
@blockedOnly bit = 0,
@ShowApp bit = 0
declare @cmd nvarchar(max)
set @cmd = ‘
sp.spid as ”SPID”,
sp.status as ”Status”,
sp.loginame as ”Login”,
sp.hostname as ”Host Name”,
sp.blocked as ”Bloked By SPID”,
sd.name as ”DB Name”,

if(@ShowApp = 1)
set @cmd = @cmd + ‘ sp.program_name as ”Application”,

set @cmd = @cmd + ‘case sp.blocked
when 0 then null
(select SUBSTRING(text, COALESCE(NULLIF((select stmt_start/2 FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0), 0), 1), CASE (select CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0) WHEN -1 THEN DATALENGTH(text) ELSE ((select CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0) – (select stmt_start/2 FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)) END ) from sys.dm_exec_sql_text((SELECT [sql_handle] FROM master.sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)))
end as ”Locking Statement”,
case sp.blocked
when 0 then null
(select [text] as ”Full Query” from sys.dm_exec_sql_text((SELECT [sql_handle] FROM master.sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)))
end as ”Full Proc or Query”
from sys.sysprocesses sp
left outer join sys.databases sd on sp.dbid = sd.database_id

if (@blockedOnly = 1)
set @cmd = @cmd + ‘where sp.blocked 0’
set @cmd = @cmd + ‘where sp.blocked >= 0’

set @cmd = @cmd + ‘order by sp.spid’

Read Full Post »