September 12, 2012 at 8:36 pm
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?
September 13, 2012 at 9:28 am
bump
September 13, 2012 at 9:59 am
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
September 13, 2012 at 10:03 am
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!
September 13, 2012 at 2:29 pm
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....
September 13, 2012 at 2:42 pm
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
September 13, 2012 at 3:43 pm
@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?
September 13, 2012 at 4:49 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply