After you add Host and Host-Instances to BizTalk, they are not useful and cannot be associated with Send/Receive ports until you relate the Host to the Adapter. This can be done in BizTalk Admin Console, but below is a program that allows you to do it with the BizTalk Powershell Extensions.
In BIzTalk, the flag is called the 32BitOnly flag. To create a 64-bit Host, set this flag to false (same as unchecking it in the BizTalk Admin Console).
Instead of setting it on the New-Item command, you have to set it as a property on the following line of code, as shown below.
$temp = New-Item -path $hostName -HostType:$HostType -NtGroupName:$myNTHostGroupName -AuthTrusted:$AuthTrusted
Set-ItemProperty -Path $hostName -Name 'Is32BitOnly' -Value 'False'
#similarly, to set whether or not the Host is a "Tracking Host", use the following line
Set-ItemProperty -Path $hostName -Name 'HostTracking' -Value 'True'
To get a list of all the miscellaneous properties, I set a break point and use the Get-Members cmdlet:
Sites below are for BizTalk 2010 – but same applies for most any other release:
https://msdn.microsoft.com/en-us/library/aa577661.aspx – List of Active Domain (AD) groups and service accounts.
I took this, put it in MS/Word and added a column to the left with a suggested name for the client. Then I asked the client to verify my suggest name to fit their existing AD group/account naming conventions. I included an “X” for each environment, e.g. (D=DEV, T=Test, Q=QA, P=Prod), or a 3-character (DEV, TST, QUA, PRD). Example, BTSPRDADMINS (or if you like mixed case, BtsPrdAdmins).
https://msdn.microsoft.com/en-us/library/gg634639(v=bts.10).aspx Windows Configuration Checklist –
– turn off hyperthreading,
– Ensure Windows Server processor scheduling is set to “Background services”
– Place the Windows paging file on a separate local physical drive.
– Stop real-time virus scanning of database files and .xml, .csv, etc…
1) Max Degree of Parallelism (MDOP) is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from the value of “1”. Changing this to anything other than “1” can have a significant negative impact on the BizTalk Server stored procedures and performance. If changing the parallelism setting for an instance of SQL Server will have an adverse effect on other database applications that are being executed on the SQL Server instance, you should create a separate instance of SQL Server dedicated to hosting the BizTalk Server databases.
2) Auto create statistics and auto update statistics are turned off for the BizTalk MessageBox DB.
zTalk Server stored procedures have exact joins and lock hints specified on the queries. This is done to ensure that the optimal query plan is used by the BizTalk Server queries in SQL Server. The distributions and expected results for the queries are known; the approximate number of rows returned is known. Statistics are generally not needed.
3) BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.
Index fragmentation is not as much of a performance issue for BizTalk Server as it would be for a DSS system or an OLTP system that performs index scans. BizTalk Server does very selective queries and updates and BizTalk Server stored procedures should not cause table or index scans.
The next very, very important thing to understand is that while the database is installed on your server, it is not “your” database. It is our (or if you catch me on the wrong day, not thinking, “my”) database
A quick list of things which can effect the query plans:
– Statistics (don’t enable these)
– Parallelism (don’t turn this on )
– Table structure (don’t add indexes, columns, triggers, … If you do you will hear silence when you call for help)
– Stored procedures (don’t change them. You can look all you want, but no touching)
--select top 10 * from dbo.dta_DebugTrace
select COUNT(*) from dbo.dta_DebugTrace as TotalRowCount
SELECT YEAR(dtBeginTimeStamp) AS Yr,
Month(dtBeginTimeStamp) AS Mo,
COUNT(*) AS [RowCount]
FROM dbo.dta_DebugTrace nolock
The results show you by year/month how many rows. In most cases, you will find you need to purge data prior the current month. At least on a test system, I can think of any possible use of month old orchestration trace data (or even on Production for that matter). But if you have long-running dehydrated orhestrations you might need it).
Orchestrations write data to these tables each time they run (when Trace is enabled).
Go to SQL Agent on the SQL Server that supports your BizTalk server, check the job entitled: DTA Purge and Archive (BizTalkDTADb)
I take the original code that is there, and copy it to the line below, then comment out the code that is there with the T-SQL comment (two dashes).
Then change the second line to the parameters you want. Follow-that by running the job manually, or set it up to run on a scheduled basis.
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --
Suppose you walk into a new company, they don’t have documentation, and they use a lot of content-based routing. The first thing I do is usually draw a Visio diagram. There never really been one widely adopted standard for how to draw such diagrams. The secret is to pack the most useful information that you can in the smallest space.
When drawing BizTalk diagrams, I like to keep everything one page if possible. Of course, sometimes you have to break on multiples pages, and when I do, I don’t like cross-page lines or pointers, if they can be avoided.
In BizTalk, a Send Port or a SendPort Group can subscribe to the data coming in from a Receive Port (and of course one Receive Port can have multiple Receive Locations). The subscription however, is by the ReceivePortName, not the ReceiveLocationName. You can have multiple send ports subscribe to the same message (thus the name Pub-Sub, short for Publisher/Subscriber model, upon which BizTalk is based).
To get to the above screen, I found a SendPort in BizTalk Admin Console, right clicked on it, then selected “Filters” on the left side. The above has one simple filter. All messages received from a ReceivePortName of MSMQDemo_RcfFromMSMQ_biztalktrans will be routed to this SendPort. You can add and change the filter/subscriptions on the top part of the screen. For convenience, the filters are shown at the bottom (and you can copy/paste them from there). It’s hard to see the entire names on the top, because of the limited width of the columns.
So often you can use business logic, naming convention, and and guessing to determine which SendPorts subscript to which ReceivePorts. Or you can open each SendPort and write them down, or copy/paste the above filters into a Word Doc, Spreadsheet, or Visio diagram.
But all the data in BizTalk Admin Console is stored in one BizTalk’s SQL Databases (BizTalkMgmtDB). The query below allows you find all SendPorts subscribing to a ReceivePort.
go -- If you have the "use" above, without the "GO" here you will get an error on the "With" statement
( SendPortName, ApplicationName, tempXMLcolumn )
SP.nvcName AS SendPortName
, APP.nvcName AS ApplicationName
, CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), SP.nvcFilter),'>','>'),'<','<'),
'xmlns="http://www.w3.org/2001/XMLSchema-instance"','') AS XML) AS tempXMLcolumn
bts_sendport AS SP
INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
CONVERT(VARCHAR(MAX), nvcFilter) <> ''
CONVERT(VARCHAR(255), nref.query('data(@Value)')) AS FilterValue,
CONVERT(VARCHAR(255), nref.query('data(@Property)')) AS FilterProperty,
CONVERT(VARCHAR(255), nref.query('data(@Operator)')) AS FilterOperator
TmpXMLNode.tempXMLcolumn.nodes('/Filter/Group/Statement') AS R(nref)
CONVERT(VARCHAR(255), nref.query('data(@Property)')) =
'BTS.ReceivePortName' -- filter type (do not change the value in quotes)
AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) =
'YourRcvPortHere' -- change this value in quotes
You can of course put a LIKE clause on the final where clause:
AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) like 'MSMQ%'
The receive port name is stored in the FilterValue column when the FilterProperty is set to “BTS.ReceivePortName”.
Results of query in SSMS:
You may recall that you can also filter on your own schema/promoted fields. So you could have one SendPort subscribe to PurchaseOrderAmount > 5000 and a different one subscribe to PurchaseOrderAmount <= 5000.
Note in the above query, I added the “Operator” column, which was not in the original SQL sample.
The filters above are stored in a text column called nvcFilter. Part of the trick of the SQL is convert this to an XML column, so XQuery statement can be performed on it.
So to demonstrate what is going on internally, I created a dummy SendPort with several filters just to see something beyond the most simple example.
I then ran this query
FROM bts_sendport AS SP
INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
WHERE nvcFilter like '%msmq%'
I then copied the contents of the nvcFilter column to NotePad++ and formatted it:
So from this, we can discern that the > Operator is stored as a 3. One could continue this exercise and map each of the operators to the numbers, and then decode the numeric values back to the humanly readable values to show in the query results.
Notice also that the “and”s and “or”s are saved in separate “Group” elements.
So now, you know how to find and cross-reference all the SendPorts that are used by your ReceivePorts, and hopefully that will help you figure out content-based routing and better understand and document your system.
Most entities from BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…)Most updates in BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…). Thus BizTalk Admin Console is basically an update program to this database. You can also use WMI to update it.
This is the MessageBox containing information about the messages, instances and subscriptions which are processed by BizTalk. Can be large and has a lot of heavy processing. A large site can have more than one msgbox (see “Scaling Out”)
Contains information about all the processed messages and instances Archive/Purging
database for the Business Rules Engine, updated by the GUI “Business Rule Composer” program. Use the “Business Rules Engine Deployment Wizard” to migrate business rules from one environment to another.
Single Sign-On Database – BizTalk stores data related to SendPorts and Receive Ports here. For sure, any password that needs encrypted goes here (for example an FTP password). BizTalk will not function at all if this database is not available and the corresponding service (“Enterprise Single Sign-On Services”) is not running. This service must come up before BizTalk.
Obviously, these are used only if you have implemented BAM. I’d love to know what percentage of BizTalk shops use BAM. Most places I’ve worked don’t use it. See BAM Quick Start
Contains the staging table, and the measure and dimension tables which are set in originally in an Excel spreadsheet, then later deployed to this database.
Contains raw tracking data. For example, when you capture data from an orchestration, it is originally stored here, before being processed further. If you need to right custom queries against this database, be sure and use the views, not the underlying tables.
Archive of old business activity data. This keeps the BAM Primary Import database smaller by migrating older data here.
Contains the OLAP (three dimensional) data cubes. Learn more about OLAP here.
Contains alert information for BAM notifications. The web application, called the “BAM portal”, allows users to specify conditions and events on which they want notifications and alerts to occur.
Contains instance information specifying how the notification services connect to the system that BAM is monitoring.
Are you a GACer? Or do you redeploy every time? Do you like to GAC in BizTalk?
I suppose it depends a lot on the place you work, the attitudes, and the procedures in place, and what security you have, and who does the deploys.
The last place I worked, I inherited what I affectionately referred to as “the monolithic app”, i.e. a BizTalk application that was made up of about 24 different projects in one solution. So redeploying the whole thing was often a pain, and sometimes there were pieces of it that I didn’t want to deploy (perhaps multiple development threads going on in that same big solution).
If you are a beginner, let me explain. With BizTalk, you can export and import the MSI (with or without the bindings). But suppose you need to change for instance just a literal value in a map or an orchestration. Why redeploy the whole enchilada? You can rebuild that specific .dll, copy it to each of the BizTalk servers in the group, and run GacUtil against it;
Then you just restart the host instances that use that .dll, or all of them you are unsure, then you are good to go.
The downside of GACing
I just recently discovered that if you export an MSI from that machine, the MSI that is exported will use the original .DLL referenced by that application. Even though run-time picks up the new GAC’ed .DLL, the export MSI may not. You can get around this by going to “Resources”, adding the .DLL and clicking “Overwrite”. As a matter of fact, you can GAC it from there too! I generally use BTDF (BizTalk Deployment Framework) , so creating MSIs that way was not of interest to me. That was, at least until I got to my latest client. They don’t use BTDF, and that’s their Modus Operandi.
When does the GAC Trick not work?
If you add new entities that have to registered in the BizTalkMgmt Database, then you will need to import the MSI. That means new schemas, new maps, new orchestrations, new pipelines, etc… Or if you promote a field that was not promoted before, the database needs to know about that.
GAC is good redeploying code when you made logic changes, changes of literals, etc… Adding new functoids to an existing map or even new shapes to an orchestration is no big deal, they are not registered in the database. But you cannot add any new send/receive ports to your orchestration if you plan to GAC.
A Must! Keep an Audit Trail of Your GACs
It’s important that no man GAC’s unto himself. If you have other people administering your BizTalk environment, they need to know what you deployed (or GAC’ed) and when.
It’s common to keep a directory such as C:\Deploys. Under that keep a folder name for each BizTalk Application
Some Other GAC Tricks
I often just put the GacUtil and related files in the same folder as my deploy. There now seem to be too many versions of GacUtil, and I hate to waste time looking for the right one.
Here are the files required: 1) all your .dll’s that you want to GAC, and the three files: GacUtil.exe, GacUtil.exe.config, and GacUTlRC.dll:
I often create a file called GacAll.cmd and put in the same directory. It simply runs GacUtil on each of the .dlls there; something like this: