Linked Server Entities and multiple environments

Linked Server Entities and multiple environments

  
In Integration Studio, if I import entities from a external database via Linked Server, I get for each one a full physical table name that is used whenever I query that table from Service Studio.

The problem is when we have multiple environements: DEV and PROD.
Most of the time, on the DEV environements we never connect to a production external database, instead we connect to a clone of that database where we can do all the tests we want and reset it everytime it's needed.
The tables we import with Integration Studio will get a full physical name like this:

"TEST_DB_SERVER"."TEST_DB"."dbo"."TABLE"

When we send this extension to production, we need to "fix" the physical name to:

"PROD_DB_SERVER"."PROD_DB"."dbo"."TABLE"

If we have 2 or 3 tables that's not a big deal, but if we have dozens of external tables this is a job no one wants to do. And worst, if someone does not fix the name of all the tables we could end by having a PROD environment sending data to a Test External DB.

Is there any way to solve this problem? An easier and error proof way of doing this?
We need a way to say that this entities should use the full name XXX on DEV and the full name YYY on PROD automatically. Like a mapping or a name transformation process.

We can do this with the Database Connections. We can have a connection string for the DEV env and a connection string for the PROD env. But we mostly use Linked Servers because with Database Connections we can't join Outsystems tables with external database tables, and with Linked Servers we can.
Carlos -

You have to use Advanced Queries for this. Pass in the first part ("TEST_DB_SERVER"."TEST_DB" or "PROD_ADB_SERVER"."PROD_DB") into the query as a parameter, set that parameter to "Expand Inline" = NO, and then make the query look something like:

SELECT ID, VALUE1, VALUE2 FROM @ServerName."dbo"."TABLE"

That will do the trick. We use this to get to a DB2 server linked through SQL Server.

J.Ja
Thanks Justin.

That's an option, but a better solution would be something managed by the platform itself.
Something like a Site Property for the extension, where we could define different values for SERVER.DB part, in the DEV and PROD environments, and at runtime the platform would just concatenate the SERVER.DB to the table names. This would be a more flexible and easy way to achieve this, and would be near the Database Connections approach.

Your solution works, but we also loose the TrueChange validation of the query, since we will not use a valid Outsystems markup.

The entities imported from a Linked Server have many limitations, and Outsystems could improve some of them:
1) The problem above;
2) We cannot test queries with entities from Linked Servers. An error is thrown: "The object name XXX contains more than the maximum number of prefixes. The maximum is 3"
3) We cannot use entities from Linked Servers in Advanced Queries with only the Outsystems markup:
The example:
SELECT {Table}.[Field] FROM {Table}

would result in the error: "SERVER_X.DB_X.dbo.TABLE.FIELD could not be bound". I'm forced to use table alias and loose the TrueChange again:

SELECT T.[Field] FROM {Table} T

This works...
Carlos -

Yes, it's clumsy... we've lived with it nearly 3 years now on a project. It's not a great solution, but it is a working solution and that counts for a lot. :D

You can't use site properties in the extension. With this approach, you can pass a site property in for the parameter (which is what we do).

What you *can* do is make an extension pointing to that linked server, and change the connection information in Service Studio for the extension.

If you are trying to get to a DB2 database, OS 9 seems to have much improved DB2 support and you should take a look at that instead.

J.Ja
I know that I can't use a site property in extensions.
What I was suggesting was that extensions could have something like the espace site properties. Where in each environment Service Center we could set the SERVER and DATABASE to use for the linked server entities of that extension.

And at runtime the platform would consider that properties on each environment, DEV and PROD.
Something like waht we can do with Database Connections but for Linked Servers.

PS: the external DB is another MS SQL Server.

Carlos -

If the linked server is another SQL Server, why not just have an extension point directly at it, and change te DB connection? Is it because of security or networking? Unless there is a good technical/policy reason to not do that... I'd try to do that.

J.Ja
What do you mean by "have an extension point directly at it, and change te DB connection" ?
Hi Carlos,

He means the Database Connection feature of the platform.
You can configure it to connect directly to the external database without having the configure a linked server.



Then in the extension you can just remove the extra parts of the name and just leave the "TABLE" part.
(The import entity wizard still adds all the parts, but in most cases it is not necessary)

Note: using database connections you lose the ability to join external tables with local tables (not that it would be any performant anyway with )

Regards,
João Rosado
Joao -

Yup, exactly what I mean.

J.Ja
Hi João.

As I said in the first post the Database Connection is not a option, because I need to join the external tables with the local ones.
I really need to use a Linked Server, and I have not found any performance issues.

My point is that using Linked Server imported entities has some issues that could be improved and it's not very easy to manage across multiple environments like the Database Connection is.
Oh sorry, missed that part on the initial post.

Ok ...so I have another idea: can you create views in you platform database and import them locally in integration studio?
That way the linked server part of the information would be hidden inside view definition. You would only have to setup this once in each database catalog.

Regards,
João Rosado
That's a better option ;)
I will try it out, thanks!