Database Design workaround with Views or Synonyms

Question

Database Design workaround with Views or Synonyms

Coffee_&_SQL

SSC Veteran

Points: 230
More actions
March 16, 2023 at 3:26 pm

#4161527

Hey All,
I have 10 user databases on a single instance. I'm dealing with a connection limitation in which I'm running out of database connections to due licensing with a 3rd party data pipeline. I believe I could create views of all the tables in my 10 user databases in a single database.
The idea being that the connection would be made to the database that just houses views of the base tables in the other 10 user databases. This is specifically for a ETL type workload. This would just be a temporary solution.
What are the downsides to this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 1

The biggest downside would be that your "temporary solution" will become permanent. You may also be in serious violation of the 3rd party license agreement if you take this kind of action.

And is there a reason why you couldn't use synonyms instead?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Coffee_&_SQL SSC Veteran Points: 230 More actions · Answer 2

I wasn't sure what was more appropriate for the situation: Views, or synonym. Synonym sounds like a better fit. Noted on the license violation; I'm just going to assume it is and CMA.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Even moving everything to a single database probably will not resolve the licensing issue.

If you are exceeding the allowable concurrent connections and you want to create more than the allowable number of connections - all you can do is purchase more licenses. If the issue is that each pipeline must be licensed and each pipeline must be defined with a specific database, then defining a single pipeline to that one database would only require a single license.

The problem I see with using a single pipeline is that you probably will be forced to run your processes one after the other - instead of being able to run multiple processes concurrently.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs