What are all the various authenticating methods in which a user can use to connect to an SFTP server (depending on how the server is set up), How shall I keep track on my SFTP Port’s Location for data pile up, What are all the possible way to monitor the File count and Directory size in SFTP port, How does BizTalk360 gives an extensive SFTP support, wanted to know the answer for these queries and more, let us get into the blog.
In the upcoming version of BizTalk360, BizTak360 extends its support in SFTP by supporting the Multi-Factor Authentication and /n software SFTP Keyboard Interactive Authentication. We BizTalk360 always gives importance to the customer’s feedback/request and keeps business improvement as its vital important thus provide the customer outbox support which will keep the users in ease and more productive. Some of our customers are using /n software sftp adapter, we got a request on the same for supporting the /n software SFTP
How does BizTalk360 Support SFTP Location
Keeping up the connection of the SFTP server is very much important to ensure that the data are transferred between the SFTP port. There are many possible reasons that the SFTP Server connection may not be reachable like,
- SFTP server is down
- Time out in reaching the SFTP Server
- Incorrect password used
- Providing the incorrect Server Address
- SFTP port not reachable etc.
The major role of the BizTalk Administrator is to keep monitoring the Server connection, but it is not possible to keep tracking on the SFTP port’s location and SFTP server connection, thus with BizTalk360, you can monitor the SFTP File count and Directory size of a particular SFTP port by configuring the same to a BizTalk360 Threshold or Health Alarm with the preferred condition.BizTalk360 monitoring service will check for the configured SFTP location and will send an alert whenever the condition gets violated. It is also possible to monitor on a regular cycle to cross-check whether the SFTP location threshold condition is healthy.
SFTP Client Authentication Mode Support in BizTalk360
BizTalk Server provides the BizTalk SFTP adapter to get connected to the SFTP Server, additionally supports the /n Software SFTP adapter which holds different authentication modes. Authentication modes are,
/n software SFTP (Supports from V10.0)
BizTalk360 support in 9.1 and lower Version
BizTalk360 support in upcoming Version
In the BizTalk360 configure the SFTP Location in Monitoring > Manage Mapping > -File Location > SFTP, which will help to monitor the health of the Port’s Location. On configuring the SFTP Location, BizTalk360 will automatically pick the Client Authentication Mode data which is configured in the BizTalk Admin console port.
This is the simplest form of authentication using the traditional username/password method. A client logs in to SFTP using Username/Password. No user private/public keys are required. The “username” and “password” can be configured under: SFTP > Configure > Security > Client Authentication Mode
In BizTalk360 you can choose the Password by clicking on the Client Authentication Mode drop-down in the Security Details of BizTalk360 and will be allowing you to provide the username and password, it is also possible to switch the authentication to “PublicKeyAuthentication”.
Public key Authentication
Public key authentication is a method where the SFTP client identifies itself to the server by using public/private key pairs. Prior to connection, the user’s public key must first be uploaded and registered on the SFTP server, SFTP > Configure > Security > Client Authentication Mode > Private Key
In BizTalk360 you can choose the “Public Key Authentication” by clicking on the Client Authentication Mode drop-down which will allow you to provide the Username, Private Key, and the Private Key password. The password here does not mean the username’s password, instead, you need to provide the password of the Public Key, if you provide the password of the username then the Port location which you have configured will be orphaned which means the location is not reachable using the Provide Public key and its password.
In case SFTP Location / Server is not reachable BizTalk360 will show the state of the port as Orphaned and will let you know the reason for the port being Orphaned by hover on the warning symbol and also in the alert notification via mail.
In the Multi-Factor Authentication we need to provide the Username & Password and the Public/Private key and password. Public/Private key and password configuration are like steps described in the latter section, except that it includes the private key’s password.
In the BizTalk360 Current version, the Multi-Factor Authentication support will not be available for you but very soon in the upcoming BizTalk360 V10 release, you will be facilitated with this feature. Thus, you could choose the Multifactor Authentication in BizTalk360 in the Client Authentication Mode list under the security details blade by allowing you to provide the Username, Password, Private Key, and the Private Key password.
Keyboard Interactive Authentication
Keyboard-interactive (KBI) authentication is the most recently introduced form of authentication for SSH. It involves the server sending prompts to the client, which the client must respond to correctly authenticated connection. Its purpose is permitting the client to support a variety of authentication mechanisms without knowing anything about them. In edtFTPj/PRO, when the server sends its list of prompts that require responses, the client searches the list of loaded SSHAuthPrompts for each prompt. It then sends the set responses back to the server. If the expected responses are supplied, the authentication succeeds.
If the prompt is for a user’s password, it will typically be something like “Password:”. The SSHPasswordPrompt class is supplied to make it easier to set up a password prompt.
In the upcoming BizTalk360 release, you could able to choose the Keyboard Interactive Authentication in the Client Authentication Mode list under the security details blade by allowing you to provide the Username and password.
Extensive SFTP Support in BizTalk360
SSH is a standard for secure remote logins and file transfers over untrusted networks. It also provides a way to secure the data traffic of any given application using port forwarding, basically tunnelling any TCP/IP port over SSH. This means that the application data traffic is directed to flow inside an encrypted SSH connection so that it cannot be eavesdropped or intercepted while it is in transit. SSH tunnelling enables adding network security to legacy applications that do not natively support encryption.
Considering the importance of SSH Tunnel and its business impact, BizTalk360 supports the proxy SSH tunnel to connect the SFTP or another server by not keeping the username and password as mandatory.
To get connected to the SFTP server with a normal proxy connection in the current BizTalk360 provide the Address, Port, Type, Username, and Password, it will reach the SFTP location using the credentials and check for the connection and its location details of the SFTP port.
Remote path location pattern support
In the BizTalk server while configuring the remote location, the user may provide different types of patterns. As we BizTalk360 comforts the BizTalk user at every point to the core level.
Say for instance: Remote path can be configuring with the following combination, C:/Users/BT360Admin/nS_SFTP_in1
BizTalk360 is capable enough to monitor the File count and Directory size of the Receive Location or Sendport with all those remote path configurations
Extensive File Mask Support
In the BizTalk Server the file mask is a string that specifies the type of file that the File receive handler will pick up from the receive location. The file name is a string that specifies the name of the file where the File send handler will write the message. The key point to remember in configuring the file mask is that the file mask and file name are not case-sensitive.
To keep up its immense support, we are supporting all the combinations of file masks. BizTalk360 will fetch the File count base on the file mask configured, in spite of its case sensitiveness.
Here are the few samples for File mask,
CHPAPO*.txt *.txt * . * *outbound322* PPI* Tes*.*
.TXt *.tXT * . XMl Orderdata.Xml .XML TES*.*
You can use a predefined set of macros to dynamically create the files in which the File send handler writes messages. Before creating a file on the file system, the File send handler replaces all the macros in the file name with their individual values. You can use several different macros in one file name.
You can use the file name macros while configuring the File send handler in BizTalk Explorer, or by using the BizTalk Explorer object model.
The File send handler does not replace the macros with a value if any of the following are true:
- The corresponding system property is not set.
- The macro is misspelled.
- The value for the macro contains symbols that are not valid in the file name.
In BizTalk360, you can able to monitor the file count and Directory size of the Send ports with macros by keeping the BizTalk File mask configuration configurable.
Hopefully, this blog helps you in understanding the basics of SFTP client authentication, as well as the types of SFTP Client Authentication Mode, its importance, and BizTalk360’s extensive support in handling and monitoring variant authentication Mode. We are happy to announce that the /n software SFTP and Macros support will be available for your service soon in the forthcoming BizTalk360 Version 10.0 release. To explore further give a try on BizTalk360 will take only a minute to place over BizTalk environment
The post Different SFTP Authentication Support in BizTalk360 appeared first on BizTalk360.
We are delighted to inform you that BizTalk360 Stop Alerts for maintenance capability is enhanced, and it is renamed as “Schedule Maintenance”. As we know BizTalk360 keeps up its standard by improvising the existing feature and implements new functionality into it in a way that eases the user experience to the next level.
At present in BizTalk360, the user can only set immediate maintenance schedules. In the upcoming release, users will be able to create immediate maintenance, one-time maintenance and recurrence maintenance associating the business holiday calendar. In recurrence maintenance, users can create daily, weekly and monthly recurrence.
Importance of Having Maintenance
Software maintenance is a part of the Software Development Life Cycle. Its main purpose is to modify and update software applications after delivery to correct faults and to improve the performance of the system. It is a very broad activity that takes place soon after the development completed. It optimizes the system’s performance by reducing errors, eliminating useless development and applying advanced development.
In the BizTalk Server, you may have regular maintenance implemented depending on resource availability/skill-set and organizational focus based on the following cases,
- BizTalk Message Box Health Maintenance
- BizTalk Performance Tuning
- Management of BizTalk Server versioning, hotfixes, and service packs
- Verification of source code location and management
- BizTalk Security Lockdown
- BizTalk Source Code Control
- Configuration of BizTalk Test Environment
- Establishment of rules for proper BizTalk Server Administration, Release Management, Operations Management, Development Management, and Database Management
Why Schedule Maintenance is Implemented in BizTalk360?
BizTalk360 allows you to set up monitoring on application artifacts and allows you to auto-correct artifacts that have hit an unexpected state. During day to day operations, it is very helpful to have this monitoring in place as it saves the BizTalk administrator(s) a lot of time because they don’t have to do that monitoring manually. However, there are also times that you don’t want this monitoring to be active. Thinking of the scenario during maintenance windows because of deployments or installation of security patches. In such cases you may need to temporarily stop monitoring, BizTalk360 provides a feature called ‘Stop Alerts for Maintenance’, which enabled the user to set up a single maintenance window.
Enriched Schedule Maintenance Functionality
The latest BizTalk360 will hold the enriched form of stop alerts for maintenance that is nothing but the schedule maintenance. In which you can set the following maintenance,
- Immediate maintenance
- One-Time maintenance
- Recurrence maintenance
Note: Even if the system is set for maintenance you may need to get alerts for any of the configured artifacts. In such case BizTalk360 ease you with the so-called field “Exclude alarm” in the general maintenance configuration part. Select the alarm for which you wanted to receive the alert even under maintenance.
Immediate maintenance is used to set the maintenance from the current time (by enabling the immediate toggle button) till the end date and end time which is set.
Let’s consider a couple of scenarios where the BizTalk Server need immediate maintenance,
- The running BizTalk Server or services go down
- The BizTalk application does not function as expected
In this case, if your infrastructure is unhealthy, then you need to undergo maintenance on a swift to bring the BizTalk environment up and healthy. At the same time, you (based on the business impact there is a customer, who holds 200 plus alarms under an environment) would have configured alarm for monitoring the artifacts in the corresponding environment. So that the alarm will keep triggering the alerts as per the configuration. Since the environment itself is down the alarms will meet the threshold condition and will start sending the violation alerts, as the same health alarm and data monitoring alarm will be sending the alert too.
In addition to it, if an alarm holds Autocorrect then the auto-correct functionality will try to push the artifacts to match the expected state, due to which the down alert will be sent, which may lead to full your inbox with BizTalk360 alerts.
To avoid such a situation, you can set the immediate maintenance just by enabling the toggle and based on the maintenance prominence you can set the end date & time. So that the BizTalk360 monitoring service will no longer perform the autocorrect and will not send. At this point in time, you can set the immediate maintenance which prevents the monitoring service from sending the alerts for the environment.
By using the One-Time maintenance, you can able to schedule/plan your future maintenance period in advance. For scheduling the One-Time maintenance click on the One-Time in the selection bar and set the Start date and start time when you wanted to get the maintenance enabled in BizTalk360 and set the end date and time when you wanted your maintenance to stop.
Say for instance, if your BizTalk server going to progress on a continuous integration approach and effective deployment then this process may take about a day or two based on the impact of the application going to get deployed and it will be a pre-planned approach which BizTalk Server undergoes. For which you can set the maintenance schedule in advance using our One-Time maintenance in BizTalk360.
Note: BizTalk360 will allow you to set the span across days for One-Time and Recurrence maintenance as shown in the below figure.
In general, you might be aware of Administrative Maintenance in BizTalk Server, which is maintenance checks should be performed on a BizTalk Server system on a routine basis as part of a preventive maintenance program. Performing the checks will help you determine whether there are reliability, administration, security, or performance issues with the system.
BizTalk suggest best practices for Administrative Maintenance as below,
Considering the Administrative Maintenance in BizTalk Server, Recurrence Maintenance has been implemented as an icing on the cake in Schedule Maintenance. Recurrence maintenance is a type of maintenance which allows you to create maintenance once, and then have it recreated automatically after a designated time interval. You can make any existing maintenance into recurring maintenance by simply using the recurrence button at the top of the task window. It will let the maintenance run in BizTalk360 between the Start Date -Start Time and End Date – End Time set, during which the monitoring service will stop sending the alert for the corresponding environment.
Recurrence maintenance comes with the following maintenance possibilities,
- Monthly- Date of the Month & Day of the Week
Recurrence Maintenance – Daily
Maintenance will be set daily on stipulated the start and end time and based on the recur value set, the maintenance will be running on an alternative day, i.e. if the recurring value is set as 2, the maintenance will run 2 days once and so on.
Let us consider that if BizTalk360 sends an alert message during the non-business hours, the message would get piled up in your inbox. So that you may tend to miss any important alert which may have a huge business impact. As a solution to it, you can set the Daily maintenance during the non-business hours which will relieve you from this great mess.
Recurrence Maintenance – Weekly
Maintenance will be set weekly on stipulated the start and end time and based on the recur value set, the maintenance will be running on the alternative selected week, i.e. if the recurring value is set as 3, the maintenance will run 3 weeks once and so on. Say, for instance, If the recurring value is set to 3 and on days is set to Monday and Thursday, then by calculating the start date checks for the next 3rd occurrence Monday and will set BizTalk360 into maintenance mode. As the same checks for 3rd occurrence Thursday and the next 3rd occurrence Monday from that day on and goes on and on till the end date set.
Recurrence maintenance – Monthly
Maintenance will be set monthly on the desired start and end time. By choosing the monthly recurrence you can set maintenance in the below two combination
- Date of the Month
- Day of the Week
Date of the Month: Maintenance will be running regularly on the corresponding date of month which is chosen in the Date and Months field. Say for instance, if the month field provided with February, April, June and date field is provided with 2, 29 then BizTak360 will calculate the current date (e.g. 25-02-2020) based on which the maintenance will run on February 29 (only for leap year if not it will consider next cycle on April 2) on the maintenance gets completed it will calculate the next cycle April 2 and so on.
Day of the Week: Maintenance will be running regularly on the week of the month that is chosen on the month’s field. Wherein day of the week recurrence, you can able to precisely set the weekly maintenance i.e. if you want to create recurring maintenance repeating on 1st and 3rd Wednesday of every month. However, you can select the month FEB, APR and days e.g. 1st & 3rd WED. So that the maintenance will run on every 1st and 3rd WED of the selected month.
Schedule Audit History
Adding a feather to the crown, BizTalk360 provides you audit functionality for Schedule maintenance. Keeping the core impact of the maintenance in concern audit for the below action is provided,
As we know the importance of having maintenance in BizTalk360. It is always a good habit to keep the maintenance in a track so that you can able to know who had set/edited/stop/deleted the maintenance for what cause.
Note: Only the superuser is permitted to take control over the schedule maintenance
Note: When the maintenance is set, BizTalk360 will show the maintenance alert pop up in the operation and monitoring home dashboard and you can able to stop the maintenance from the home screen of operation and monitor section just by clicking on the “Stop Maintenance” button in which you can also provide the reason in the popup for stopping the maintenance same will be audited in the schedule audit history.
We can assure the new improvements in the schedule maintenance capability will give you smooth control over BizTalk360 monitoring during the infrastructure maintenance. Why not, give BizTalk360 a try! It takes about 10 minutes to install on your BizTalk environments and you can witness and check the security and productivity of your own BizTalk Environments.
The post Schedule Maintenance to Stop Alerts in BizTalk360 appeared first on BizTalk360.
In this blog, we will take a detail look about the feature Secure SQL Queries which is available in BizTalk360 and we shall look how effectively it can be used in by the BizTalkies and what are all the things we can achieve by having this feature and how we made this feature so reliable for you.
Secure SQL Queries
Secure SQL Queries is a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. BizTalk360, by default, comes pre-loaded with a set of queries and allows the users (with permissions) to build secure SQL queries that are more appropriate to the organization’s business.
Reliability of Secure SQL Queries
BizTalk Support members will typically have a bunch of standard SQL queries they run on a regular basis to get some reporting on BizTalk databases. Each individual team member will have their own set of SQL queries and they manage it on their local machines. Which will basically result in two things, useful reporting queries are not shared between team members and maintaining the queries in SQL files and accessing different files for the different environment is not productive.
BizTalk360 allows you to save the SQL queries so that the queries can be saved under a centralized area and available for access to all the BizTalk members.
SQL queries are used for fetching the report from the BizTalk Database where admins are concerned to provide full access as it may have confidential information and interested to give privilege only for the authenticated BizTalk members. This can be handled by using the User Access Policy feature in BizTalk360 which allows to create a user and provide the permission for the saved Secure SQL Queries (only the enabled query will be listed for the users). Permission can be provided for the Secure SQL Queries as follows Add Query, Edit & Execute Query, Edit and Save query, Delete Query and Export Query.
At any point in time, there may be a chance of data loss or change may happen in the queries. To handle this sort of situation BizTalk360 provides you the feature Governance & Audit for Secure SQL Queries. If any operation like Create, Edit, Delete is performed against the Secure SQL Queries will be captured in the BizTalk360’s Governance & Audit section. So that we can find the details of the operation performed through the feature Governance & Audit for Secure SQL Queries.
Functionalities of Secure SQL Queries
In BizTalk360, you can query against the table’s data. Based on the customer’s requirement it allows you to store and execute the stored procedures.
In real-time when more users are involved, or remote connections are being established with the SQL Server, SQL authentication can be used. In shared servers where different users should have access to different databases and when a client connects to an instance of SQL Server on another computer than the one on which the client is running, in that case, SQL Server authentication is needed. Such user can select the Authentication Mode as SQL Authentication so that the user name and password can be provided, and the query can be saved and executed
Using the SQL Authentication Mode, you can get connected to the Azure Database by providing the Azure Database’s instance name, username, password for saving the queries.
Let’s glimpse on Secure SQL Queries in BizTalk360
Secure SQL Queries is implemented in the Operation, Monitoring, and Analytics section. Since most of our customers use this feature widely.
Secure SQL Queries in Operation
BizTalk360 comes with a pre-loaded set of queries. The users can choose one of these pre-loaded queries from the drop-down list. When the item is selected from the drop-down, the query automatically executes, and the result is displayed in the Query Results pane. It allows users to define and add/edit/delete your own queries. In which you can execute the query to check for the results.
Secure SQL Queries in Monitoring
You can monitor the Database Query results by configuring the query which returns the scalar value. If there is any deviation in the query results, then BizTalk360 will trigger an alert notification.
Secure SQL Queries in Analytics
The BizTalk360 Reporting feature includes a widget called Secure SQL Queries, where you can map the existing Secure SQL Queries to such custom widgets. The custom SQL widgets hold the top 100 records of the mapped query and send that as a report on the scheduled time. This feature can be powerful as it enables you to automatically receive reports based on SQL queries, without the need to requesting it manually over and over again!
BizTalk databases can grow extremely bigger. It’s not uncommon to have over 1 million records in certain tables (ex: MessageInOut table in Tracking Database). Executing some queries like just a plain “SELECT * ..” on those tables without any query optimization or locks can have serious performance impact during business hours. Always try to write the query which returns the selective results.
Secure SQL Queries are designed to allow the users to execute only the ‘Select’ statement for queries and Execute command for Stored procedures. This will ensure the reliability of the data in the BizTalk Server and other databases.
Say, for instance, there may be a chance that the normal/a superuser runs a DELETE/TRUNCATE/DROP/UPDATE/INSERT commands through the secure SQL queries in BizTalk360. The above-mentioned commands are restricted by design to avoid data loss and secure the data.
Benefits of having Secure SQL Queries
- Single management tool for users to execute the queries
- No need for SQL Server Management Studio
- Central Query Repository – maintaining queries is much easier
- The end-users need not have direct access to the SQL database
- The queries will be executed in the context of the service account, therefore only the service account requires access
- Queries can be executed against any SQL instance/database
We are constantly improving on the features based on feedback in the customer forum. BizTalk360 will continue to provide more useful features in every release. Why not give BizTalk360 a try!
The post Effective Usage of Secure SQL Queries Feature in BizTalk360 appeared first on BizTalk360.
For those who spend most of their time working with Service Instance messages, ESB exceptions and Event Log entries, it might be good to know that BizTalk360 can ease you with the feature “Knowledge Base”. The Knowledge Base acts as a repository of articles that contain solutions to the common problems which are faced by users while working/supporting the BizTalk environment. One of the great advantages of the Knowledge Base is that you can store the information related to the exact error condition and share that with other users for reference.
In BizTalk360 you can create and refer KB articles for the following sections:
- Service Instances
- Event Log entries
- ESB Exceptions
- Throttling Data
Importance of having KB article
A knowledge-base is the one thing that can be instantly useful for both your support agents and customers. Support agents can refer to the knowledge base and answer the customer queries quickly, instead of spending time asking someone for guidance or trying multiple solutions. Customers can search for answers to their questions in the knowledge base, instead of contacting Support and waiting for a reply.
By having KB articles, it is very handy to document the solution to known issues, and most importantly it is fully customizable and easy to document. It’s a simple and intuitive user interface, through which the users will enjoy using this repository.
Let us consider a scenario that will help us to understand the importance of KB Articles a bit more. In an organization, not every user will be available on time for providing guidance. Some may be remote users, for whom the Knowledge Base will act as a self-service hub that will hold the referral documents or solutions.
Say for instance, while handling support, some issue has been raised and solved for a customer. After a while, another user has raised a support ticket for the same issue. In case the solution to the issue has been documented, this kind of situation can be handled much more effectively and less- time-consuming.
BizTalk related KB articles
When an issue occurs in BizTalk which ends up as a Suspended instance, it will hold an error code. By using this as a key point, you can document the solution for each issue. If needed, you can even use criteria like BizTalk application, Service name and (parts of) the Error Description to associate an article to an issue. Similarly, BizTalk360 will also let you document issues related to Event Log entries, Throttling data, and ESB exceptions.
Let me share with you a support case which we have handled recently. In a firm, we often used to upgrade our setup. While setting up the environment, we may face similar issues that occur frequently. Whenever any configuration related issue occurs it will be logged in the event viewer, along with the Description and Event Id. Say for instance, if the BizTalk server throws an “MSDTC” or “ENTSSO” exception while performing any operation. Using that Event Id, you can create a BizTalk360 KB article, through which you are providing visibility over the issue, which can be referred to in future cases.
Note: In the case of Event Log entries, BizTalk360 associates KB articles based on the Event ID. However, as the Event ID might not be enough to uniquely associate a KB article to a particular Event Log entry, you can also use properties like Event Log, Event Log Source, and few other properties. You can even take partial text from the actual Event Log entry description!
Similarly, BizTalk Server continually monitors for throttling conditions, calculates the severity of the throttling condition, and applies host throttling progressively depending on the calculated severity. To handle this, BizTalk360 contains an Analytics (Windows NT) service specifically to collect the throttling performance counter data from the various BizTalk servers in the environment. Throttling activity of a host instance is being monitored and at a point in time, the monitoring service plots the throttling state against, for example, the ‘System Memory’. Once the reason and a mitigation strategy are identified for the throttling, as a best practice, the information can be documented in a KB article.
Service instances can fail due to some error and BizTalk attaches the error with an error code and an error description. In the same way, other service instances can fail for the same reason as the previous one. I.e, if there is a suspended message, you can find the error code for the same. The error code 0xC0C01B4e represents routing failure messages.
With the BizTalk360 knowledge base, you can document the solution by referring to amongst others the error code and the error description. Once the Knowledge Base article is defined for one error code, event log, etc., it will be visible across all environments. In BizTalk360, whenever the issue occurs with the documented error code etc., then the user can see the KB article icon. By clicking on the properties (or eye icon), you can find the document under the KB article tab with the reason/solution you have created.
When creating multiple articles for the same error code, select the appropriate tags (Environment, Service Class, Application Name, Error Text, Host Name, Service Status, Service Name) for the error code. The tags will play the differentiator role to display the appropriate KB article.
Note: BizTalk360 allows super users to create KB articles specific to one environment. All the users in that environment will be able to see the articles.
Best practice of using the KB article
- Whenever an issue occurs and became resolved, make a practice to always document the solution in a KB article.
- Keep the Titles of the KB articles as straight forward as possible, the simpler, the more is it understandable. Ensure it is to the point and include the most important keywords.
- Formatting solution articles is extremely important. Clearly, differentiate your titles and subtitles. Split different sections using a horizontal line. Bold the action items in each step so it’s easy for the user to skim. Provide the step by step details of the solution.
- Refer to the link related to the article.
- Be descriptive with relevant points.
- Tag the article to have clear usage and better reference.
With the above-mentioned tips, you can make your KB articles a well understandable document.
With the Knowledge Base feature, we provide visibility over resolved issues and the same can be brought into the notice to all the users. With this feature, we can improve the visibility of issues and reduce time consumption in solving the issues which will lead to higher productivity.
The post An Effective Way of Using KB Articles in BizTalk360 appeared first on BizTalk360.
BizTalk360 always values customer feedback and we focus on improving the usability by adding new features and enhancing existing features as per customer requirements. Recently we have received a few support cases from our customer end about SQL Collation. So, we have analysed and improved it.
We are happy to share you that BizTalk360 has improved its standards by supporting case-sensitive collation. This will soon be available in the upcoming version 9.0 Phase 2. In the previous version of BizTalk360, we were supporting the general collation like Latin1_General_CI_AI for BizTalk360 Database. In this blog, we will see why we started supporting case sensitive SQL collation and what the benefits are of having it.
As you all know BizTalk360 is the one-stop monitoring tool to monitor BizTalk Server. Since BizTalk Server supports case sensitive collation, it is so important that BizTalk360 also supports case sensitive collation, to make it as a compatible tool.
About SQL Collation
A collation is a configuration setting that determines how the database engine should treat character data at the server, database, or column level. SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications across the world.
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
- CI refers to Case Insensitive
- CS refers to Case Sensitive
List of collation combination in SQL setup:
- SQL Server collation
- SQL Server and Database collation
- SQL Server, Database and Tables collation
- SQL Server, Database, Tables and Column collation
SQL has 4 levels of collation: server, database, table and column. If you change the collation of the server, database or table, then we don’t want to change the setting for each column. If needed, we can change the default collations at database/table/column level.
Example: If you change the default collation of a database, each new table you create in that database will use that collation, and if you change the default collation of a table each column you create in that table will use that collation.
To change the collation of SQL Server: SQL Server collation can be changed in two ways, one is by changing the collation during the installation and the other way is changing the collation after installation.
Changing the collation during the installation: while installing SQL server, you can find the Server Configuration section. Under this section, you can see two sections Service accounts and Collation. Click the Collation tab to verify the collation. The collation will have the default supported collation. Click on the Customize… button and select the required collation. Click Next and proceed further.
Note: To have case sensitive collation use the collation which holds CS_AS.
Why BizTalk360 supports SQL Collation
BizTalk Server supports all case sensitive and case insensitive SQL Server collations except for binary collations. So, it is important that BizTalk360 should support the same collations.
Collation refers to a set of rules that determine how data is sorted and compared. When BizTalk360 calls any functions or data from BizTalk Server which is of case sensitive collation, then it may lead to an exception.
Let’s consider that the BizTalk databases hold a case sensitive collation and BizTalk360 holds a case insensitive collation. In this case, when BizTalk360 gets integrates with BizTalk Server and tries to read the data, it is necessary to ensure all the procedure and column names should be exactly similar.
From the above picture it is clearly understandable that having a collation-based database, and when trying to fetch the data of an object, it will compare the case sensitiveness and provide the data. In case of uppercase and lowercase mismatch, it will lead to an exception.
Say for example, Invalid column name ‘nAdminHostId’ (or) Invalid object name ‘dbo.b360_ST_Schedules’ (or) Must declare the scalar variable “@expiryDateTime”etc.
Most of our customers configure BizTalk360 in the SQL Server instance where BizTalk Server is configured. In such cases, the below-mentioned scenario will add value for supporting collation.
Scenario I: BizTalkMgmtDb (Latin1_General_CS_AS) & BizTalk360 (Latin1_General_CI_AI)
Let us consider the scenario of having a SQL server with case insensitive SQL collation and case sensitive collation for BizTalkMgmtDb.
In this case, the BizTalk360 installation will be successful and BizTalk360 database will be created with case insensitive SQL collation since SQL Server holds a case insensitive SQL collation. But in the BizTalk360 application, you may face some exceptions getting popped up related to “Invalid column name ‘nAdminHostId”, in few sections like BizTalk Server, SQL Server, SQL Server instance, Analytics etc.
Scenario II: SQL Server (Latin1_General_CS_AS) & BizTalk360 Database (Latin1_General_CI_AI)
When creating a database in the SQL Server, by default the database will hold the collation of the SQL Server. In this case, the customer has the CS collation for the SQL server, thus all the BizTalk related databases hold the same case sensitive collation. In this case, when you try to install the BizTalk360 Db in the SQL server which holds Latin1_General_CS_AS collation, the installation will fail!
“ExecuteSqlStrings: Error 0x80040e14: failed to execute SQL string, error: Must declare the scalar variable “@environmentId”., SQL key: CreateTablesWin SQL string: ALTER PROCEDURE . @EnvironmentId uniqueidentifier”
The BizTalk360 database is created with a CI collation and BizTalk360 is installed by pointing the database to the CI collation DB. But, since the database was under the case sensitive collate SQL server, we had the same exception.
Query to create database with a case CI collation
CREATE DATABASE BizTalk360
In BizTalk360 v9.0 phase 2, the collation issue has been fixed across the application. We have revised the collation issue across each section in the application.
Checklist prepared to test SQL collation in BizTalk360
BizTalk360 supports many features like ESB, BAM, Azure etc. When having case sensitive collation for BizTalk360 Database, it is important that we look into all the sections of BizTalk360 (Operation, Monitoring, Analytics and Settings) to make it work correctly.
Say for example, BizTalk360 manages ESB exception data (EsbExceptionDb), in which it is important that all the column names, object names and procedures are exactly the same. We have nailed the compatibility of the BizTalk360 database with BizTalk Server database, ESB, BAM, Azure etc.
We always aim to improve the product by filling gaps in customer needs. Considering the same, we have improved the collation support in our upcoming release version 9.0 phase 2.
It is possible to have a combination of collation. Say, for example, SQL Server with the collation Latin1_General_CS_AS and BizTalk360 with Latin1_General_CI_AI. By achieving this, we have taken BizTalk360 to leading-edge technology in its standards.
The post SQL Collation Support in BizTalk360 appeared first on BizTalk360.
Recently, we released BizTalk360 v8.9. As always, each new release of BizTalk360 has completely new features in it, according to the customer feedback and suggestions we received. Keeping up with the business needs of our customers, version 8.9 has many beneficial features.
Along with the new features, we have enhanced quite a few features in BizTalk360. In our previous blog, we have covered few of these enhancements. In this blog post, I would like to provide a brief description about the following enhancements:
- ESB Service Name as a new filter option
- A new column “Resource Group” is introduced in Logic Apps (Operations & Monitoring)
- Data Monitoring Service Instances grouped by Error Description in Email Alerts
- Increased number of characters allowed in Alarm descriptions
- Ability to set Threshold Alert’s Start Time and End Time between 23.30 to 23.59 hours
- BizTalk360 v8.9 installer changes
ESB Service Name as a new filter option
In the ESB Exception Data feature, a query builder is available to fetch the precise data on a particular condition.
In version 8.9, we have added another filter property, namely Service Name. Choosing the Service Name in the filter properties will list the available service names as an option and the user can select the required option from the list. Thus, execution of the query will list the precise data in the grid. For even more detailed filtering, multi-combination of filter properties can be set.
We have implemented the same for ESB Data Monitoring; under the section Set Data Filter, the Service Name Property has been added. Selecting the Service Name in the filter will list the related options, so the Service Name can now be used for your ESB Exceptions Data Monitoring
Resource Group in Logic Apps
The Resource Group column has been implemented in the Logic Apps section, to have a clear picture over the Logic Apps being created under different Resource Groups in the same Azure Subscription.
In the previous version, all the Logic Apps under a subscription were listed. Quite often, a subscription will have different Resource Groups and each Resource Group can have their own set of Logic Apps.
There was an issue while display Logic Apps which are created in different Resource Groups, but with the same name. With the introduction of the Resource Group column, Logic Apps are now grouped by Resource Group in each configured Azure subscription.
Resource Group implementation is done in three areas, Operations, Monitoring and Data Monitoring.
Resource Group implementation in Operations and Monitoring
In the Operations section the Logic Apps are listed in the grid, based on their Azure subscription. This may lead to Logic App Name duplication, and to confusion in finding out to which Resource Group the Logic App is associated.
This is sorted in BizTalk360 v8.9, by adding the Resource Group column in the grid. Thus, once the Azure subscription is configured, the Logic Apps will be listed in the grid, along with the Resource Group to which they are associated. So, it will be easier to manage the Logic Apps by knowing their Resource Groups.
The Resource Group column is also implemented in the Monitoring Section, under Manage Mapping. When an alarm is set to monitor Logic Apps, it avoids name duplication, since Logic Apps are associated to the Resource Group.
Resource Group implementation in Data Monitoring
The implementation of Resource Groups in Data Monitoring helps in segregating the Logic Apps present under the associated Resource Groups.
In Logic App Data Monitoring, the Resource Group field is added under Set Data Filter. By filtering on Resource Group, only the associated Logic Apps will be listed and monitored.
Group By Error Description
In the Message Box Data monitoring section, the instance details were sent in the notification email, by grouping them based on the Error Code. In BizTalk360 v8.9, we have added the capability to group the instances by Error Description. This is done by implementing as a small feature under Settings / System Settings / Monitoring & Notification section, where you can find an ‘Enable Group By Description’ toggle button.
By default, Email notification errors are grouped by Error Code. Enabling this toggle button will result in grouping by the Error Description.
The Data Monitoring alert gets populated, grouping the instance details by the Error Description and list the count of it, along with its description.
As shown in the sample mail alert, having the grouping of description helps the user to have a very clear and precise clarity in the email alert for the suspended instances.
Increased the number of characters allowed in alarm descriptions
As you will probably know, the Alarm screen, in the Monitoring section, holds a Description field in which the user can feed up to 300 characters for providing a short description related to the alarm. This helps the customer to have a clarity over the alarm popped.
We now increased this limitation to 1000 characters, that in turn helps the customer to give a detailed description on the alarm. Exceeding the 1000 character will lead to a validation warning.
Hyperlink in Alarm Description
As an added advantage along with the 1000 characters, the Description field now accepts a Hyperlink and validates the HTML hyperlink tag. The field not only validates the hyperlink, it also validates basic HTML tags too.
Any syntax errors found in the hyperlink, will lead to a validation warning. By including the hyperlink tag, the customers can provide any necessary link which is related to the alarm.
We mainly have provided the hyperlink tag to comfort the user by allowing them to give more detail via a web page over the alarm created.
Threshold Alert’s Start Time and End Time increased to 23.59 pm
BizTalk360 has the capability to configure a threshold to trigger within certain time limits. The alarm configuration has a check field, “Set Alerts on Set day(s) and Time(s) only”. Once this check box is enabled, it will allow the user to access the Days Check and Time Selection, to precisely set the threshold time to trigger the alert emails during the particular days and times.
Any weekday can be selected, meanwhile timing for the day can be set by selecting the values in the Start Time and End Time dropdown boxes. The Start and End Time’s time value increases by 30 min, so any time can be set according to the need, under the condition “*Must be 60 mins later than start time”.
The lag was that, the Start Time and End time can be set only up to 23.30 pm. So, we were not able to fetch persisting violation details which occurred between 23.30 pm-23.59 pm.
As a solution to it, in the version 8.9, the End Time duration has been increased from 23.30 pm to 23.59 pm. By exceeding the End Time to 23.59 pm, the user will not find any lag in getting the violation alert. This way, the mapped artifacts of the Threshold alarm(s) can be monitored for the entire day.
BizTalk360 v8.9 Installer changes
The Installer is a facet of most software products. It is important to every Product organization to improve the installer from time to time, to provide a seamless installation experience. In our latest version, we made improvements in various aspects of the installer.
Rethinking the upgrade credential screen
The BizTalk360 installation package consists of three components:
- Web services
- Windows NT services (Monitoring, Analytics)
Users have the liberty to install the preferred components as per the business need. For an instance, the web component can be installed in a separate machine and the same counts for the other components.
Here is an interesting change we made in the Credentials update screen, for the upgrade scenario. What is the change? Previously, during the upgrade scenario, if all the components are installed in the same server, the user must provide passwords for all the components, even though all the components are using the same set of credentials. We have revisited this logic and made our installer to determine by itself whether all the installed components are using same credentials or different ones.
Let’s consider all the components are using the same credentials (User Name and Password). In that case, the installer will show only one section with the username and password. This reduces the user activity to enter the same password for the all the components.
Support for SQL authentication upgrade
Until the previous release, BizTalk360 did not support the upgrade, for the SQL authentication mode. In v8.9, the installer has been enhanced to support SQL authentication upgrade.
3.5 .NET dependency removal
For smooth functioning of the Azure services in BizTalk360, at a minimum .NET version 4.5 is recommended for BizTalk360 version v8.1 and later. The Installer blocks the installation if .NET 3.5 has not been installed in the machine. This restriction made users to install the .NET version 3.5. Now, this limitation has been removed.
Persisting config files during the update
To deploy BizTalk360 on HTTPS, your IIS must be enabled for HTTPS/SSL. To access BizTalk360 through HTTPS, there are few changes that need to be done in the BizTalk360 web.config file. Until our previous version, during the upgrade process, those changes were not persisted. After every upgrade, users had to do the changes again and again in the web.config files. As of the v8.9 upgrade, the changes made in the web.config files will be persisted.
We provided these improvements, hoping they will be helpful to you and make you feel easy while using BizTalk360. Happy migrating and try BizTalk360!!!