Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

linked database opinion? Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 8:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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?
Post #1358301
Posted Thursday, September 13, 2012 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
bump
Post #1358658
Posted Thursday, September 13, 2012 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1358681
Posted Thursday, September 13, 2012 10:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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!
Post #1358685
Posted Thursday, September 13, 2012 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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....
Post #1358869
Posted Thursday, September 13, 2012 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1358883
Posted Thursday, September 13, 2012 3:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
@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?
Post #1358918
Posted Thursday, September 13, 2012 4:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1358972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse