Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This applies to a SQL Server 2012 instance. We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that would contain any stored procedures that query that data. The stored procedures would then explicitly reference the "data" database when pulling any data.

Would there be any query performance problems in structuring things this way?

Edit: I'm coming at this from a developer's perspective; this is being proposed from the DBA level and I couldn't tell you what might be behind it. Best I can tell it's some sort of separation of interests - the data database is loading data from a 3rd party app so we can more easily report on it. I think they want to separate out stored procs we create to not clutter the "source data" db. This db is not a vendor database per se, but the application's data is unavailable in its native format. So there are daily processes that import the vendor data via ODBC into SQL Server so we can more easily get at it.

share|improve this question
1  
Could you please explain what you're trying to accomplish by separating them in this way? –  Jon Seigel May 22 at 14:21
    
@JonSeigel honestly, I'm coming at this from a developer's perspective; this is being proposed from the DBA level and I couldn't tell you what might be behind it. Best I can tell it's some sort of separation of interests - the data database is loading data from a 3rd party app so we can more easily report on it. I think they want to separate out stored procs we create to not clutter the "source data" db. –  Peter Tirrell May 22 at 14:28
    
I've used this approach myself, just be aware that if the reporting software builds the queries, that the reporting software inserts the queries in the DB with the Data, ie in Crystal Reports, there is only 1 Database Link. –  eyoung100 May 22 at 14:38
1  
Okay, now we're getting somewhere. The data databases being vendor databases is significant. Please edit the question body to include as many details as possible (including your last comment) so answerers have a good understanding of the situation. –  Jon Seigel May 22 at 17:27

1 Answer 1

up vote 2 down vote accepted

I've seen this type of thing done before. Particularly where the data & possibly some code is coming from an outside location and may be overwritten with restores (for example). It also allows the code to be backed up separately from the data (yes I know you could do that with filegroups also).

In general as long as your code is on the same instance as the data then there are no performance issues.

However you will likely run into some permissions issues. There is something called ownership chaining that allows someone to successfully execute a stored procedure that updates a table even though they don't have permissions to the table directly. This does not work cross-database.

Unless of course you turn on something called cross database ownership chaining. This is not turned on initially and there are of course security implications of turning it on. It basically allows for exactly what it says. Ownership chaining will work across databases. It can be turned on at a server or database level, although it is highly recommended that you not turn it on at a server level unless required for some reason. You can however turn it on for your two databases and you should be fine.

I recommend reading the links above. They appear to be quite good and will give you a better understanding of what I'm talking about.

share|improve this answer
    
Microsoft's documentation msdn.microsoft.com/en-us/library/ms188676.aspx says: "Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks." There may be some performance issues if handling referential integrity between databases. –  RLF May 22 at 17:27
    
Agreed, however the slight performance advantage is in the milliseconds I believe and the referential integrity should all be in the one database. Only code would be going in the other DB (based on my understanding of the OP) –  Kenneth Fisher May 22 at 19:51
    
Thanks for the links! For the most part, we're not doing updates or inserts into the other database, only reads, but that is interesting all the same! –  Peter Tirrell May 23 at 1:52
    
Just remember to take it into account. It can also affect views etc. Permissions are really the major issue in splitting up code/data into separate databases. I've never noticed any real performance issues. –  Kenneth Fisher May 23 at 3:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.