Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v6.0.0)

Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v6.0.0)

by | Jan 19, 2020 | BizTalk Community Blogs via Syndication

It was only 9 days ago that I released the latest minor version of this package, on that time the goal was to update the Security and Governance stencils to please my dear friend Nino Crudele. It was then that I notice that Microsoft did again a completely redesigned on several of the symbols and of course add new services so I decided that this was the time to not only refresh the package with the new icons but also but so extra work.

What’s new in this version?

With the growing number of stencils in this package, it was becoming hard to easily find or look for the right shape/representation. So once again based on some feedback I received from the community I decide to do so rearranging. I still have many things to do in this project in terms of organization but for now, these are the changes on this major release:

  • New shapes: The main additions are all the new shapes on the Azure Portal. New shapes added on:
    • MIS Azure Stencils: containing all the main Azure Portal Services
    • MIS AI and Machine Learning Stencils: all shapes related to AI or Machine Learning scenarios;
    • MIS IoT Devices Stencils: all shapes related to IoT scenarios:
    • Microsoft Integration Stencils;
    • MIS Azure Others Stencils: containing Azure Postal Services, features of the services and other interesting shapes;
    • MIS Azure Additional or Support Stencils: other shapes that may be interesting in supporting Azure diagrams and designs or presentations;
  • MIS Azure Stencils: Complete update to this category with many new shapes added and many shapes moved to MIS Azure Others Stencils file;
Microsoft Integration and Azure Stencils Pack for Visio v6.0.0
  • MIS AI and Machine Learning Stencils: Complete update to this category with many new shapes added and updating many others to there current stencils;
Microsoft Integration and Azure Stencils Pack for Visio v6.0.0
  • Add categories:
    • Microsoft Integration Stencils Old Version Stencils: containing all the oldest versions of stencils (obsolete, deprecated or replaced by a new shape)
  • Ordered by name: I recently started to sort shapes in alphabetical order. These are the categories already sorted:
    • MIS Azure Stencils;
    • MIS Azure Others Stencils;
    • MIS AI and Machine Learning Stencils;
    • Microsoft Integration Stencils Old Version Stencils;
    • MIS Security and Governance;
  • Text Annotations: This is a requested feature and I already started to work on it but it will get some time to finish it. For now, you will see text annotations on:
    • MIS AI and Machine Learning Stencils
Microsoft Integration and Azure Stencils Pack for Visio v6.0.0
  • SVG Files: Add new SVG files, and uniform the name of the files;

Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack

Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:

  • BizTalk Server
  • Microsoft Azure
    • Integration
      • Integration Service Environments (ISE)
      • Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
      • Azure API Management
      • Messaging: Event Hubs, Event Grid, Service Bus, …
    • Azure IoT and Docker
    • AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
    • SQL Server, DocumentDB, CosmosDB, MySQL, …
    • and so on
  • Microsoft Power Platform
    • Microsoft Flow
    • PowerApps
    • Power BI
  • Office365, SharePoint,…
  • DevOps and PowerShell
  • Security and Governance
  • And much more…
  • … and now non-related Microsoft technologies like:
    • SAP Stencils
Microsoft Integration (Azure and much more) Stencils Pack

The Microsoft Integration Stencils Pack is composed of 28 files:

  • Microsoft Integration Stencils
  • MIS Azure Stencils
  • MIS Additional or Support Stencils
  • MIS AI and Machine Learning Stencils
  • MIS Apps and Systems Logo Stencils  
  • MIS Azure Additional or Support Stencils
  • MIS Azure Mono Color
  • MIS Azure Old Versions
  • MIS Azure Others Stencils
  • MIS Buildings Stencils
  • MIS Databases and Analytics Stencils
  • MIS Deprecated Stencils
  • MIS Developer Stencils
  • MIS Devices Stencils
  • MIS Files Stencils
  • MIS Generic Stencils
  • MIS Infrastructure Stencils
  • MIS Integration Fun
  • MIS Integration Patterns Stencils
  • MIS IoT Devices Stencils
  • MIS Office365
  • MIS Power BI Stencils
  • MIS PowerApps and Flows Stencils
  • MIS SAP Stencils
  • MIS Security and Governance
  • MIS Servers (HEX) Stencils
  • MIS Users and Roles Stencils
  • Microsoft Integration Stencils Old Version Stencils

That you can use and resize without losing quality, in particular, the new shapes.

Download

You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from:
Microsoft Integration Azure Stencils Pack VisioMicrosoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack for Visio
GitHub

or from :

You can download Microsoft Integration Stencils Pack for Visio 2016/2013 from:

Microsoft Integration Stencils Pack for Visio 2016/2013 (10,1 MB)
Microsoft | TechNet Gallery

The post Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v6.0.0) appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’.

BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’.

by | Nov 1, 2019 | BizTalk Community Blogs via Syndication

Recently a
friend of mine that is working with me in a project send me an email reporting
a quite curious issue that I found while accessing the BizTalk Server
Administration console in our development environment:

This operation failed while accessing at least one of the Message Bix databases. Some results might be omitted. (Microsoft.Biztalk.Administration.SnapIn)

Additional information:

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. (Microsoft SQL Server, Error: 9002)

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’

Immediately I point out to the team that this issue
was related to lack of disk space.

Cause

The official cause of
this issue is that when the transaction log becomes full, SQL Server Database
Engine issues a 9002 error. The log can fill when the database is online, or in
recovery. If the log fills while the database is online, the database remains
online but can only be read, not updated.

And for us to know the exact cause for what is
preventing log truncation, we can use the log_reuse_wait and log_reuse_wait_desc
columns of the sys.database catalog view.

In our case, it was indeed a problem with disk
space and what happened was that the disk to where we were doing backup went out
of disk space, because we cannot do the backups the transaction log grow until
the point that disk (that contain the log file) also went out of disk space.

Solution

When you know the issue, the solution is quite easy. In this case, freeing disk space from both hard drives immediately solves the problem. Nevertheless, because the log file got quite big you should think of stopping your BizTalk Server environment and do maintenance in your databases in special reduce the size of the transaction log.

For that you should:

  • Perform
    a full back of your databases;
  • Stop
    all BizTalk Server services (host instances and enterprise Single Sign-on)
  • And
    run the following SQL Script
ALTER DATABASE BizTalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 2048);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO





    

  • Do
    
about the same steps for other databases whose transaction logs are also quite large.
    • 



The post BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. appeared first on SANDRO PEREIRA BIZTALK BLOG.
BizTalk WCF-ORACLEDB error: This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

BizTalk WCF-ORACLEDB error: This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

by | Aug 24, 2018 | BizTalk Community Blogs via Syndication

Another day, another error to report – still have plenty of them in my backlog to be published, they are an “easy and quick way” to publish something in my blog when I really don’t have much free time to write something different. Today is about “This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters” error message that I got when I was initially trying to connect for the first time to ORACLE database to insert some data:

Microsoft.ServiceModel.Channels.Common.MetadataException: Metadata resolution failed for OperationId: “http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/TRANSACTIONS/Insert”. —> Microsoft.ServiceModel.Channels.Common.ConnectionException: Due to an Oracle Client limitation, the adapter failed to open a connection. This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters, or (b) ambient transaction is present and a non-TNS based URI was used. To resolve this, use a TNS alias to connect to Oracle and make sure it is not more than 39 characters.

at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceCh.

This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

Followed by other similar warning messages:

A message sent to adapter “WCF-Custom” on send port “INSERT_PAYMENTS_WCFORACLE” with URI “oracledb://IP-ADDRESS:PORT-NUMBER/PATH” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Metadata resolution failed for OperationId: “http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/PAYMENTS/Insert”. —> Microsoft.ServiceModel.Channels.Common.ConnectionException: Due to an Oracle Client limitation, the adapter failed to open a connection. This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters, or (b) ambient transaction is present and a non-TNS based URI was used. To resolve this, use a TNS alias to connect to Oracle and make sure it is not more than 39 characters.

at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

MessageId: {DE7ABF70-D6B7-4FC8-A570-5AAE4FFACBB9}

InstanceID: {5842C3C7-6746-4A56-8707-FF53123A4101}

Cause

Non-TNS based URI is not supported under an ambient transaction. If you have to use transactions, you should use the TNS alias.

Note: TNS Alias needs to be less than 39 characters.

Solution

To solve this problem, you need to:

  • Open the send port properties, by double-clicking on the port;
  • Click on “Configure…” button under Transport
  • On the Transport Properties window, select the “Message” tab and then uncheck the “Use Transaction” box under Transactions

This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

If you try to resend the message this problem should be solved. Additionally, if this error still persists, you should change the following properties in the oracleDBbinding:

  • “enableBizTalkCompatibilityMode” = True (this is set to false per default)
  • And/or “useAmbientTransaction” = False
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database “Database-Name” requested by the login. The login failed

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database “Database-Name” requested by the login. The login failed

by | Jul 5, 2018 | BizTalk Community Blogs via Syndication

To finalize, maybe, these series of Errors and Warnings… Causes and Solutions let’s describe and address a fairly simple one that from time to time appears: Login Failed. While trying to communicate to a SQL Server database to invoke a stored procedure thru BizTalk WCF-SQL adapter I got this following error:

A message sent to adapter “WCF-Custom” on send port “STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//AsyncTransactions?InboundId=ins” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database “AsyncTransactions” requested by the login. The login failed.

Login failed for user ‘DOMAINBTSHostSrvc’. —> System.Data.SqlClient.SqlException: Cannot open database “AsyncTransactions” requested by the login. The login failed.

Login failed for user ‘DOMAINBTSHostSrvc’.

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

— End of inner exception stack trace —

Server stack trace:

at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

at Microsoft.Adapters.Sql.ASDKConnection.Open(TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnection(Guid clientId, TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)

at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.OneWayOperationSendPortRequestChannel`1.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open()

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.ICommunicationObject.Open()

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)

MessageId: {71CDC883-AE8D-4303-A72E-ADA9ECE91981}

InstanceID: {1EA7B77C-924F-4C74-87DE-BCC4D1AD8E6A}

The cause of the problem is quite obvious and the error message, this time because sometimes is not quite true like we saw in my last post, the error message clearly identifies the origin of the problem.

This problem occurs because the user account that you used to access the database, in my case the BizTalk Host Instance Account, don’t have permissions to connect the database.

Simple problem, simple solution. You need to grant permission to the user to access database

Once again, given “sysadmin” or “db_owner” would solve all our problems but it goes against security best practices and sometimes these tables contain sensitive data or personal data, and nowadays with General Data Protection Regulation (GDPR) in EU this sometimes can be a backdoor for other possible problems and teams need to start taking into considerations these security policies.

You always should give the minimum rights to a certain user for him to do the necessary tasks. Nothing more, nothing less… as things should be.

BizTalk Server 2016: Could not load file or assembly ‘Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.

BizTalk Server 2016: Could not load file or assembly ‘Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.

by | Oct 5, 2017 | BizTalk Community Blogs via Syndication

It is not the first time, neither will be the last that I encountered similar problems like this one or the same problem with earlier versions, we call it DLL hell (or nightmare) but I think that all BizTalk Administrator are familiar with it and vaccinated for the problem. Some months ago, while trying to communicate with an Oracle database within Visual Studio in a brand-new BizTalk Server 2016 Developer environment to generate the proper Schemas, we faced with the following Oracle.DataAccess problem:

Error saving properties.
(System.ArgumentException) Invalid binding.
(System.IO.FileNotFoundException) Could not load file or assembly ‘Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.

Could not load file or assembly Oracle.DataAccess Version 4.121.1.0

Cause

When installing the Oracle WCF Adapter for BizTalk Server 2016 there is a design-time requirement to use Oracle.DataAccess Version 4.121.1.0.

BizTalk Server 2016 requires that specific Oracle.DataAccess version, however, us we were able to very on the GAC, the DLL that existed in our environment had a different version.

Trying to find the correct ODP.NET 11.2.0.1.2 version under Oracle website can be a challenge

Note: depending on the BizTalk Server version that you are using, this the required Oracle.DataAccess version may change.

Solution

Trying to find the correct ODP.NET 11.2.0.1.2 version under Oracle website can be a challenge, so one of the easier and fast ways to solve this problem is using Assembly Binding Redirection in the machine configuration file (Machine.config):

  • 32-bit: c:WindowsMicrosoft.NETFramework[version]configmachine.config
  • 64-bit: c:WindowsMicrosoft.NETFramework64[version]configmachine.config

Note: You should apply this in both 32 and 64-bit machine configuration files.

By using the <assemblyBinding> Element for <runtime> that will contain all the information about assembly version redirection and the locations of assemblies.

In this case, you should apply the following configurations:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="Oracle.DataAccess"
         publicKeyToken="89b483f429c47342" />
      <bindingRedirect oldVersion="4.121.1.0" newVersion="x.xxx.x.x" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

By doing this when BizTalk Server will look to the Oracle.DataAccess version which not exists in your environment, it will be redirected to the existing DLL version.

For example, in our case we used:

<!--<runtime />-->
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89b483f429c47342" />
        <bindingRedirect oldVersion="4.121.1.0" newVersion="4.121.2.0" />
      </dependentAssembly>
   </assemblyBinding>
</runtime>

Just to be on the safe side, you should add this configuration in both 32 and 64-bit in .NET Framework 2.0 and 4.0 machine configuration files.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases

BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases

by | Jun 6, 2017 | BizTalk Community Blogs via Syndication

During my sessions about BizTalk Server Tips and Tricks, I normally ask: What RosettaNet, ESB or UDDI have in common? And the answer is: they all are BizTalk optional features that are not part of the primary installation process, you need to execute “secondary” installation processes to add theses features. These installation processes will create BizTalk custom databases for supporting all of these new optional features. But the big questions here are: do you think that these databases are being backed up? And if not, how to backup (other) BizTalk Custom Databases?

Do you think that these databases are being backed up?

To respond this first question, the answer is: No!

Because these BizTalk custom databases (we are calling “custom databases” because they are supporting optional features that are not part of the primary installation process) are not installed by default with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. The default list of databases that are, normally, being backed up by the Backup BizTalk Server job are:

  • BAMAlertsApplication
  • BAMPrimaryImport
  • BizTalkDTADb
  • BizTalkMgmtDb
  • BizTalkMsgBoxDb
  • BizTalkRuleEngineDb
  • SSODB

How to Backup (other) BizTalk Custom Databases?

If you want the Backup BizTalk Server job to back up these additional BizTalk custom databases, you must manually add the databases to the Backup BizTalk Server job.

You can achieve this by:

  • Taking Windows Explorer and browse to the “Schema” directory on the BizTalk installation folder, normally:
    • C:Program Files (x86)Microsoft BizTalk Server <version>Schema
  • Run “Backup_Setup_All_Tables.sql” and next “Backup_Setup_All_Procs.sql” against all your BizTalk custom databases that you want to back up. This creates the necessary table, procedures,  roles and assigns permissions to the stored procedures.
  • After that you need need to modify the adm_OtherBackupDatabases table, in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of the new BizTalk custom databases
    • Type the new server and database names in the corresponding columns, as shown in the following tab
      • DefaultDatabaseName: The friendly name of your custom database.
      • DatabaseName: The name of your custom database.
      • ServerName: The name of the computer running SQL Server.
      • BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.

To complete the process, you, mandatory, need to force Backup BizTalk Server (BizTalkMgmtDb) job to perform a full backup of the databases, otherwise you will receive the following error:

  • BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)

To do that you need:

  • Execute the “sp_ForceFullBackup” stored procedure present in the BizTalkMgmtDb database.

The next time you run the Backup BizTalk Server job, it will back up all your BizTalk custom databases.

Note: I will not recommend you to add any of your application support custom databases to the Backup BizTalk Server job since they may interfere with the execution times of this job. If the Backup BizTalk Server job starts to take a long time to execute, it may also affect the overall performance of the BizTalk platform.

Stay tuned for new BizTalk Server Tips and Tricks!

Check out the first blog of the series BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016.

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper

BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper

by | Mar 30, 2017 | BizTalk Community Blogs via Syndication

Finally, BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper is published! I think this was my crazy project ever because I started during an MVP Summit, probably in 2013 with a very basic whitepaper of 7 pages about Disaster Recovery and my initial reviewer was Tord Glad Nordahl… for some reason I never finished or published the content and last year again during the MVP Summit I revived this idea and basically annoyed all the MVPs that were next to me that day to review the initial document again… bad idea for me because, probably to get revenge on me, they were the most demanding reviewers that I ever had, always asking for more content making, therefore, the whitepaper more complete. So, what was a 7-pages whitepaper becomes a 34-pages whitepaper.

BizTalk Server Databases: Disaster Recovery, troubleshooting and best practices

What to expect about BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper

Microsoft BizTalk Server databases and the health of the databases are very important for a successful BizTalk Server messaging environment. BizTalk Server is an extremely database-intensive platform, persisting data to disk with high frequency, and one of the main reasons for that is because one of the primary design goals of BizTalk Server is to ensure that no messages are lost. Therefore, database performance is paramount to the overall performance of any BizTalk Server solution.

There are many factors that you need to take into consideration towards troubleshooting, maintaining, monitoring or recovering from disasters. This paper will provide you some important aspects to consider when working with BizTalk Server databases and addressing the most common and important aspects:

  • Size of databases and tables: performance degrades on High Size of BizTalk databases
  • Important consideration to avoid large BizTalk Databases
  • Separation of data files and log files (SQL Server disk I/O contention)
  • Important consideration BizTalk SQL Settings
  • Available tools for monitoring and troubleshooting
  • Recovering from disasters situations (Clean up your BizTalk databases)

However, the content is very valuable with regards to even preventing a disaster or limit the probability of it. After reading this paper you should be prepared for any disaster but also to preventing for happening because be able to prevent is better than resolving.

Where I can download it

You can download the whitepaper here:

BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices (1.14 MB)
BizTalk360

I would like to take this opportunity also to say thanks to my amazing reviewers: Steef-Jan Wiggers, Nino Crudele, Kent Weare, Mikael Hakansson and Salvatore Pellitteri for taking the time to review this whitepaper. And other people that were involved in making this “project” came true like Tord Glad Nordahl, Lex Hegt, Saravana Kumar and Sriram Hariharan.

I hope you enjoy reading this paper and any comments or suggestions are welcome.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.