locked
Sorting in BizTalk Server 2013 Administration Console RRS feed

  • Question

  • The Issue: I find it very strange that the first column in BizTalk Server Administration Console (BSAC) is not sorted in any way. For me it is natural to have it sorted Asc on the first column. I have to click on the column header each time Send Ports is displayed or when the content is refreshed.

    The solution: I found out that if I change the clustering of fields in the SQL Server table I get a sorted display in BSAC. Read more in the Solution Details below.

    The Question: Does any one know if the solution could bring any negative effects on a production BizTalk. Is there any other solution that accomplish the same thing?

    I know that this could be on the edge of what MS does allow you to do in the BizTalk database. I know that you are not allowed to add indexes in any way to get support from MS. However I have not seen anything about changing the clustering of the indexes.

    Solution Details: If you want to try this you need a fair amount of knowledge in SQL Server. You also need to consider if it is advisable to do this change if you want to do it in an production environment.

    My BSAC starts by looking like the left part when first displayed and after the change like the right part in the picture

    Unsorted and sorted Send Ports

    Start SQL Server Management Studio and connect to the DB that your Biztalk is using. Go to the BizTalkMgmtDb and find the bts_sendport table. Right click it and select Design. Right click any row and select Indexes/Keys. When bts_sendport_unique_key is selected on the left side, change property Create As Clustered to No. Select index bts_sendport_unique_name on the left side and change Create As Clustered to Yes. Click on Close in Indexes/Keys window. Close the design of bts_sendport and click Yes to save the changes. Click Yes to confirm also changing/saving additional tables. 

    This will mean that the rows in the table will physically be stored in nvcName order instead of nID. Since BSAC does not seem to do any sorting when it displays Send Ports this will enable the sorting.

    Changing clustering in sql server

    To enable the same for Receive Ports find the bts_receiveport table and do the same change on the indexes there. 

    I have not found a way to do the same change on Orchestrations. In the Orchestration table there is not a suitable index to change.

    Regards
    Joakim


    • Edited by JoakimPe Sunday, October 12, 2014 9:23 AM Had to change to only 2 pics
    Sunday, October 12, 2014 9:22 AM

All replies

  • The Question: Does any one know if the solution could bring any negative effects on a production BizTalk. Is there any other solution that accomplish the same thing

    I know that this could be on the edge of what MS does allow you to do in the BizTalk database. I know that you are not allowed to add indexes in any way to get support from MS. However I have not seen anything about changing the clustering of the indexes.

    You need consider opening a support case with Microsoft. :)  Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone .

    Hope it helps you.


    Monday, October 13, 2014 9:57 AM
  • Not sure I understand the issue. In my environments the view is sorted by default by the first column. And I can sort the view by any column. 

    Is this issue appeared after you change the clustering?


    Leonid Ganeline [BizTalk MVP]

    Monday, October 13, 2014 6:10 PM
    Moderator
  • Might do that if I get the time. BSAC has had issues like this one since version 2001. You get used to them :-)
    Thursday, October 16, 2014 12:30 PM
  • What version of BizTalk are you using? I'm having this issue in BizTalk 2013 and it occurs in all servers I have come across (7) at 3 different customers.

    The issue is appearing from the beginning. It disappears when I change the clustering.

    Thursday, October 16, 2014 12:37 PM
  • Hi,

    This is an issue in both BizTalk 2013 and BizTalk 2013 R2. Can't understand why Microsoft have not fixed this.

    When you sort on Name the list of Send port is sorted corerctly but as soon as you make any Changes like enable/disable a port it goes back to default (what ever sorting that is) and the ports are no more sorted by name.

    This worked fine in BizTalk 2010. It remembers the sorting and does not re-arrange the port list after enable/disabling ports.


    RL

    Wednesday, March 25, 2015 1:58 PM