SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


linked database opinion?


linked database opinion?

Author
Message
sqlguy-736318
sqlguy-736318
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 447
What is your opinion of linked databases?  I've used both linked servers and linked databases in the past.  I seem to remember that linked servers had occasional hiccups but linked databases on the same server were seamless.

I recently started a new contract where tables for different applications were stored in the same database.  This database has grown to over 1000 tables.  

So as an alternative to this data model structure would it be reasonable to put each application data model in its own database on the same server?  I used linked databases about 5 years ago on SS2005 and I seem to remember that the implementation was pretty solid.

One thing I don't remember is if linked databases supported fk references.  I also could not remember the performance impact of linked databases.  I seem to remember that there was a distinct performance hit for linked servers.  However, I seem to remember that the performance impact for linked databases was very minor.

Can you please provide your feedback on these questions based on your recent experience with this and provide your own opinions on the linked database model?
sqlguy-736318
sqlguy-736318
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 447
bump
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29605 Visits: 39986
my two cents:

There's no way to enforce referencial integrity (easily) between databases; you can try putting contraints using user defined functions, but that does nto guarantee anything,a s the databases can be restored/dropped seperately. that's just introducing a new level of complexity that you don't really need.

commands involving linked server are slow. If you select/update/delete data, the linked server table is copied over to tempdb, the joins are formed,a nd then the select/update/delete is finally executed.

that whole copying of the data, especially if big tables are involved, is a performance hit you can avoid by not breaking up the database.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patrickmcginnis59
patrickmcginnis59
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 Visits: 2333
The nice thing about SQL is you can test theories. I tried the foreign key constraint idea:

"Cross-database foreign key references are not supported. Foreign key 'BLAH.DBO.TEST_TABLE'."

So I'm guessing NO on the foreign key idea between databases ;-)

The approach of applications having their own databases is probably the norm, unless the individual applications work on the same data, and in this case are they really different applications or just different modules in the same application or system?

2 cents!
sqlguy-736318
sqlguy-736318
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 447
What if the core datamodel is for read-only purposes? Applications could each have their own database and connect to the core database for read-only purposes.

If all the databases reside on the same server then this should be pretty solid based on my past experience. The core datamodel could be accessed through SQL by basic dot notation by prefixing the table name with the database name.

Like I mentioned based on my experience 5 years ago in SS2005 linked databases were pretty solid - I'm not referring to linked servers in this scenario....
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29605 Visits: 39986
sqlguy-736318 (9/13/2012)
What if the core datamodel is for read-only purposes? Applications could each have their own database and connect to the core database for read-only purposes.

If all the databases reside on the same server then this should be pretty solid based on my past experience. The core datamodel could be accessed through SQL by basic dot notation by prefixing the table name with the database name.

Like I mentioned based on my experience 5 years ago in SS2005 linked databases were pretty solid - I'm not referring to linked servers in this scenario....


well like patrick mentioned, some of the impacts are easy to test.

copy your two biggest tables(that are related) to another database.

run
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.ID = T2.ID, with the actual execution plan.
do the same, but with one of the tables from the cross database.

SELECT * FROM T1 LEFT OUTER JOIN OtherDatabase.dbo.T2 AS T2 ON T1.ID = T2.ID, with the actual execution plan.

compare the differences.
now repeat that one more time, but with a linked server instead of a cross database call;

you'll see the execution plans getting more and more complex, and the linked server dumping data into temp.

if your tables are not all that big, it might be fine, but I try to avoid linked servers for info; I'd rather replicate the data, and allow a bit of stale data.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlguy-736318
sqlguy-736318
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 447
@SS - Like I mentioned - I'm not referring to linked servers at all but only linked databases that reside on the same server. I wanted to get some feedback regarding real-world experience with this implementation.

I don't do a whole lot of db work these days so I was curious if more db-centric folks on this forum had tried this approach to address specific problems and succeeded, or what kind of problems this approach may have created for people in a real-world implementation?
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8416 Visits: 7163
patrickmcginnis59 (9/13/2012)
The approach of applications having their own databases is probably the norm


Perhaps, perhaps not. But is that a good way to do it, regardless of how common it is?

Remember, you can use different schemas in the same db. This gives you separation for security and other purposes, but still allows you to use FK constraints, etc..
Kind of like two databases in one;-) !

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search