August 20, 2010 at 8:54 am
Hi,
My application (dotnet) uses, on the same SQL Server 2008 instance, 2 databases : one (db1) for general data and settings and the other (db2) for data (customers, orders, invoices …).
In each database, I have stored procedures (many in db2).
This application will be used in many independent places.
To maintain easier the stored procedures of db2, is it realist to put all these stored proc in db1 ?
I know that it is possible in a stored proc in db1 to access a table of db2 with a complete name : db2.myschema.mytable.
But what about performances ?
Does anybody work as that.
Thanks in advance.
August 20, 2010 at 9:21 am
You COULD do that, and it should work most of the time. However, you will experience difficulties if any of the stored procedures need to access user-defined types and object. Most user-defined objects are database specific and cannot be passed between databases.
We have an app with a single master data database and 13 transactional databases. We have gone the route of placing database specific stored procedures in each database. This seems like the best way to do it, especially if there is a chance that you are going to be expanding the number of databases.
You can use Red Gates Multi-script to push stored procedures out to multiple DBs if needed. Very slick.
August 22, 2010 at 11:59 pm
Retrieving data thru Linked server is relatively expensive. It is ok to use it if you are expecting small data set.
Lynchi Shea has put together nice article on this
[font="Verdana"]--www.sqlvillage.com[/size][/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply