4

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.

Improve this question
4
  • 1
    Could you please explain what you're trying to accomplish by separating them in this way?
    – Jon Seigel
    May 22, 2014 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, 2014 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, 2014 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, 2014 at 17:27

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • 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, 2014 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, 2014 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, 2014 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, 2014 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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