August 27, 2014 at 10:46 am
Trying to see what the Pros and Cons would be for the following...
Have a database that currently houses nothing but Stored Procedures for SSRS. One of my BI Developers is wondering if there is any advantage to moving these Stored Procedures to another database, and put them in a completely new scheme (so as to get rid of this current database that has no tables and does nothing more than hold the SP's for reporting). Is there any real advantage in overhead by doing this?
Any and all advice, experience, knowledge on this would be appreciated.
August 27, 2014 at 10:56 am
What do you mean by put in another database? Do you mean move the stored procedures into the database(s) that they source their data from or move them into another utility type database that has multiple uses?
My opinion is that, unless your database is for a 3rd-party product where your license restricts you from adding procedures to the database, your reporting procedures should be in the database that hosts the data. The main reason is security. By using a separate database you either need to enable cross database ownership chaining, maintain your users with different permissions in multiple databases, or have the pain of using certificates to sign your procedures to manage cross database access.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2014 at 11:01 am
Like many things, "It depends".
Are the sps in the special db used across many databases? ==> keep 'em together (maybe even in master/programmability/stored procedures)
Are there sps in the special db that are only used by one "data" database? ==> put 'em in THAT database.
Do you want to be able to backup/restore databases including the procs? ==> put the procs in the database.
Database schemas permit different security properties in the same database. We use them to logically separate tables into groups and avoid name collisions. It is common in a DW design to have some combination of Staging, Fact, Dim and Audit schemas. The procs go with the schema that holds the tables that they execute against. Database schemas are superior (IMHO) to separate databases for this purpose since you can backup/restore an entire database in one command and you don't have to remind yourself, "Don't forget to backup that other database too! (in your case, the SP database)"
August 27, 2014 at 11:05 am
Jack, thank you - and yes, sorry for the lack of more specific detail. The way it was presented to me was in the fashion of the SP's currently residing in a more utility like database, as opposed to the one that actually hosts the data.
The specifics I was addressing with this BI person was overhead - not security, but security needs to be considered as well.
Would there be any overhead advantages that you know of that would benefit the SP's being in the host database as opposed to say a report utility database?
Thanks again...
August 27, 2014 at 11:14 am
gbritton1, thank you! The scenario you described along with what Jack has asked, should make for some better discussion on my end.
From the standpoint of pure overhead (only) - is there truly any advantages one way or the other?
August 27, 2014 at 11:16 am
I'd agree with most of what gbritton1 has said although I'm not a fan of putting user procedures in master (with a few exceptions like sp_WhoIsActive).
I don't know of any performance issues that would come up (unless you are using linked servers which is a separate issue). The things I'd be concerned about would be:
DR - you need 2 databases restored to get full functionality
Migration - if you move one database to another server you need to move the reporting database as well.
HA - depending on how you do HA both databases have to be included in any solution (clustering, mirroring, log-shipping, always on).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2014 at 11:20 am
I believe what happened (prior to my arrival earlier this year) was that they had all of their queries embedded in SSRS reports, and they thought it would be better to export those to Stored Procedures. So - the question posed to me has been answered with great detail by you and gbritton1. I will take this and run with it.
Thanks to you both again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply