Geeks With Blogs

Mike H. - Another Geek In Need... WebLog

I had a challenge recently that was quite a blast sorting through.

The architecture included HIS 04 on the BTS NLB clustered nodes - where the HIS database objects were to be on a A/P Failover SQL Server 2000 Cluster.

By design, you cannot deploy the database objects to a clustered instance of SQL Server. I sent the following to PSS after implementing a successful 'work-around'.

Deploy Host Integration Server 2004 on a Clustered SQL Server 2000 Instance

 

PROBLEM

When you have SQL Server 2000 Enterprise installed on a A/A or A/P (active/active or active/passive) cluster, and you are running Virtual Instances of SQL Server, you will experience a problem when you try to install HIS on a different server, pointing the database connection to one of the virtual instances on the cluster. The ConfigFramework fails with the following found in the error log:

·          [12:50:59 PM Info DatabaseHelper] Configuring HISAdmin database role.

·          [12:50:59 PM Info DatabaseHelper] Executing database stored procedure:

·          [12:50:59 PM Info DatabaseHelper] sp_grantlogin '\HIS Administrators'

·          [12:50:59 PM Error ConfigHelper] Windows NT user or group '\HIS Administrators' not found. Check the name again.

In our case, we provided the \ for the database to connect to. For example, SERVER_A\SQLSERVER_A.

 

HIS passes the virtual machine name to SQL Server, and SQL Server cannot resolve the name because it is a virtual instance, and the groups are on the physical machine, and the command sp_grantlogin on that \GroupName will fail.

 

SOLUTION

Microsoft is aware of this problem, and provided us part of the following solution. We note additional items not provided in the solution, but necessary for the HIS, TI (Transaction Integrator MC), and ENTSSO to work properly.

 

For this to work, you must already have the ENTSSO installed on a SQL Server cluster where the SSODB resides. Also, you will need a stand-alone SQL Server that is not a virtual / clustered instance to install HIS onto.

 

  1. Install HIS, pointing it to the stand-alone SQL Server (if SSODB does not already exist on a stand-along SQL Server, create it at this time, otherwise JOIN it).
  2. Create the MSHIS_xxx database objects manually on the clustered, virtual instance of SQL Server. (MS)
  3. On each node of the cluster, manually create the HIS Administrators and HIS Runtime Users security Groups. (MS)
  4. Create logins for these security Groups on the virtual instance of SQL Server you created the database objects on. (MS)
  5. Add these logins to the database objects created. (MS)
  6. For each database object created, create 2 rolls – HISAdmin and HISRuntime.(MS)
  7. Add the HIS Administrators login to the HISAdmin roll in each of the databases. (MS)
  8. Add the HIS Runtime Users login to the HISRuntime roll in each of the databases. (MS)
  9. From the machine you installed HIS, copy the file HIPTables.sql from the \Program Files\Microsoft Host Integration Server\System folder to a folder where the clustered SQL Server instance is running.
  10. Run Enterprise Manager on the clustered SQL Server.
  11. Select ToolsàQuery Analyzer from the menu bar, and execute the Query Analyzer.
  12. Ensure that you are in the MSHISxx_HIP database.
  13. Open the file HIPTables.sql file – from within Query Analyzer.
  14. Execute this script at this time (MS).
  15. Close Query Analyzer.
  16. With Enterprise Manager still open, expand the MSHISxx_HIP database.
  17. Select Tables on the left tree, and locate the table Computers on the right.
  18. Right-Click and Return All Rows of this table object.
  19. Here you will see the machine name of the computer you are on. You will want to change this to the machine name that HIS is actually going to be running from. NOTE: If you are running HIS from a NLB cluster, you will have more than one machine name here. For each HIS machine that will be using this database, enter those machines names now (KB 885003).
  20. Save the edits, and exit Enterprise Manager.
  21. On each machine that HIS is installed on, and that you want to interface with the clustered SQL Server, you must make the following registry edits.
  22. Execute the registry editor – StartàRun and type in regedit and press .
  23. Locate HKLM\Software\Microsoft\Host Integration Server\HIP and select HIP.
  24. You will see and entry for Connectionstring in the right pane – double-click this (or right-clickàproperties) to edit this entry.
  25. Change the database reference here to the \ of your clustered SQL Server.
  26. Now, locate HKLM\Software\Microsoft\ENTSSO\SQL and select SQL
  27. You will see an entry for Server in the right pane – double-click this to edit this entry.
  28. Change the database reference here to the \ of your SSODB clustered SQL Server.
  29. Performing these registry edits on each HIS machine that must use the clustered server will allow all components of HIS to work.

 

We had a lot of fun sorting this one out.

Posted on Sunday, May 1, 2005 1:21 PM BizTalk | Back to top


Comments on this post: Clustering Host Integration Server 2004

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Michael J. Hamilton, Sr. | Powered by: GeeksWithBlogs.net