Biztalk vs OIC

 So we are developing integration solutions using Biztalk 2016 and recently we started working with Oracle's Integration Cloud iPaaS service. We have found it quite painful and limiting and until this week have been singing the praises of Biztalk, saying that it's just far easier to work with and more functionally rich. Well...

This week I had what seemed a simple task. Take an OAGIS BODFailureMessage (OAGIS is awesome) and send the details to Service Desk Plus, then put the resultant Request ID back into the original message and send it onwards. Seemed pretty straightforward.

Problem #1

Service Desk Plus has an API that enables us to POST a Request BUT the API does not accept a message-body; it wants you to send the details of the request (which is a JSON payload) as a parameter called input_data. 

Now from Postman, this is quite easy. Simply add a key-value pair in the Body using the x-www-form-urlencoded option like so:

However, when it comes to doing the same thing in Biztalk you are faced with the tricky issue of trying to take the contents of a message and somehow turning that into a variable:

The obvious thought is to promote a property in the schema of the OAGIS message, but you need to construct a whole JSON message to pass as a parameter. "Not a problem" you say. Simply generate an XSD from an example JSON payload (there's a Biztalk wizard for that) and add a map to turn the OAGIS message into the JSON payload.

Sure, that works but then how do you get the whole JSON message into a parameter? A promoted property might work if you generate a schema just for the parameter and promote the single parameter node, then use an orchestration to construct the parameter message. Well, that would work until the JSON payload exceeded 255 characters.

I was about to give up and declare bith Biztalk and Service Desk Plus stupid. Then I found this article which saved my bacon. Context properties don't have the 255 character limit! You simply add the Context property schema to your Biztalk project, with a single node in it and then in the orchestration, use an expression to set the property. Yeah Baby!

Problem #2 to follow...

Back from the Future!

We like to live on the edge, so we don't mind trying out the latest version of SQL in production do we? Sure, why not, SQL Server 2017 is stable and it's an enterprise class database platform (didn't used to be able to say that :-( ). Well what happens when you reach the maximum database size and have to move a database from the cutting edge SQL 2017 Express edition (slightly sarcastic here) to a Standard or Enterprise server and the only other servers available are 2016? Oops.

I discovered the problem when trying to restore a backup from the 2017 instance onto a 2016 instance. SQL coughed up this error message:

------------------------------
Restore of database '*******' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.4001. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
------------------------------

"Ok, that's inconvenient" I thought, "what now?"

So I googled (as you do in situations like this) and came across this nice article https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/ which explained how to export the entire database via script...nice! Only problem was that the script it produced was too large to open in Notepad or SQL Management Studio (5.8GB) and I needed to amend the CREATE DATABASE statement as well as execute it.

Well, to amend the script this article proved rather useful https://www.petri.com/tip-opening-large-files-windows . I downloaded EmEditor and fixed the script so that the database files were created in the right path. Then, I logged on to the SQL 2016 server, opened a command prompt and executed the massive script using SQLCMD:

C:\>SQLCMD -S .\MyInstance -E -i "C:\...\MyMassiveScript.sql"

Easier than I expected really, it all just worked...which is not a common scenario :-)

Later dudes!

PS: the script will take ages to execute so be patient. It is, without a doubt, the worst performing method of inserting huge amounts of data into a SQL database, i.e. one record at a time.

When does a SQL Table think it's a View?

Try this one out:

1. Create a SQL database
2. Create a couple of tables
3. Create a view that queries those tables and make sure to include a calculated column
4. Create an empty table from the view using SELECT INTO...WHERE 1=0
5. Drop the view
6. Rename the empty table to the name of the dropped view
7. Try and insert some data into the table
8. Scratch your head...

Yup, SQL still thinks the object with the same name is a view and because it includes a calculated column it won't let you insert data into it.

To solve the issue, execute the sp_refreshview proc using the name of the new table.

What a SISS-up!

Have you ever had the situation where you have set up automated deployment of a SSIS package and you upgrade the package and the SQL Server it is deployed to, then suddenly your packages start behaving weirdly?

Well believe it or not it can happen that you are able to deploy a SQL Server 2016 package to a SQL Server 2016 server using a 2014 SSIS deployment wizard and get no exceptions during deployment. Then your packages start misbehaving, like simply missing out script tasks and completing successfully. I kid you not!

So, there lesson here is that just because it deployed successfully using the wrong wizard, don't assume it will work correctly.

Oracle, CredSSP, what?? I just want to connect to my servers!

Hi all,

Two days ago I was doing some general maintenance using the new Windows Admin Centre server that I've just deployed to my test environment when all of a sudden, I encounter a WinRM error when attempting to connect to servers within my estate.

At first, I thought it was a bug with WAC but keeping an open mind, I attempted to establish a remote session via PowerShell to the same server and again... a WinRM error?

Now the sweat starts to form as I'm thinking that there may be a bigger problem with my estate.  I then attempted to establish an RDP connection to my servers and was met by this error:

Very Odd?  I then checked using another system which worked as expected so at least at this point the problem is with my PC and not my servers/network.

Next step, what has changed on my system in the past 24 hours?  Of course, windows updates...

I check for any newly installed windows updates and found KB4103727 had been deployed to my machine.

A quick look at the release notes from MS and this update does contain a fix relating to RDP and CredSSP. (https://support.microsoft.com/en-gb/help/4103727/windows-10-update-kb4103727)

I removed the update and rebooted my PC which seemed to fix the issue.  RDP, etsn via PowerShell and WAC all started to work as before.

Great? almost....Except that very same update contains a large number of security fixes.  So a little more diggin'

Adding the following keys and DWORD to the registry of the source computer seems to fix the issue without removing the update.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters]
"AllowEncryptionOracle"=dword:00000002

If the keys are missing, simply add them and remember, always backup before making any changes to the registry of a computer.

I've now left my PC with the amended registry and so far, so good.  If that changes I'll update you all.

Enjoy :)

I am a Defector!

Ever worked with SQL Server Master Job Servers? A great idea really - have a single server that pushes out job definitions to loads of other instances. The problem comes when you try and 'defect' a server and SQL just says 'MSX defect failed for JobServer', without any explanation.

Ok, so there are some obvious things to check, like whether or not the master server still exists and is online, whether there are any security issues between the two instances and so on. If you get to the stage that we were in where the master server doesn't know about the target server and the target server can't defect...well, this is what I did on the target server instance to resolve the issue:

1. Try force defection the standard way
EXEC dbo.sp_msx_defect @forced_defection = 1

if that fails...

2. Clear the MSXServerName registry key value

EXECUTE master.dbo.xp_instance_regwrite 

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXServerName',

N'REG_SZ',

N''

3. Delete the MSXPollInterval registry key value

        EXECUTE master.dbo.xp_instance_regdeletevalue

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXPollInterval'

4. Remove the entry from sqlagent_info

        DELETE FROM msdb.dbo.sqlagent_info

        WHERE (attribute = N'DateEnlisted')

5. Delete any master jobs

        EXECUTE msdb.dbo.sp_delete_all_msx_jobs @current_msx_server, @jobs_deleted OUTPUT

6. Restart SQL Agent

7. Delete the MSXOperator SQL Agent Operator

        EXECUTE msdb.dbo.sp_delete_operator @name = N'MSXOperator'

This worked for me but usual caveats apply.

Later dudes!

Size does matter!

Well, it does to WinRM. That is what I learned today. I have been using DSC to provision SQL Server instances for over a year now and never before had I encountered this particular error:

The WinRM client sent a request to the remote WS-Management service and was notified that the request size exceeded the configured MaxEnvelopeSize quota.
    + CategoryInfo          : LimitsExceeded: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : HRESULT 0x80338111
    + PSComputerName        : **************

Naturally, I googled and the solution is rather simple. Just check what the winrm setting is for the target machine and increase the size.

winrm g winrm/config
winrm set winrm/config @{MaxEnvelopeSizekb="8192"}

What was interesting though is why this was the first time I was getting the error?? It turns out that the MOF files I was sending to the server were slightly bigger than normal because I was configuring four instances of SQL rather than the previous three. The file was shade under 500kb, which when wrapped in an envelope must have exceeded the default envelope size of 500kb.

Simples...