I am primarily a web developer, not a DB admin, so hence this question!
We have a webservice that we have created which has a Database on our SQL 2008 server.
We are currently creating a web portal to manage some of that information that is within the database for the webservice. The web portal will need some of its own Tables which will not be used by the web service. It will need to use Stored Procedures to access some of the webservice DB tables and obviously have its own SPs for accessing its own tables.
From a performance point of view, what is the best set up to use for doing this? I have thought of the following scenarios, but am not sure which is best.
- Add the Web Portal Tables + Stored Procedures into the existing DB and just connect/call from there
- As above, but create a new Filegroup for the Web Portal Tables + SPs so that they are in a different physical file (possibly move to different Disk drive).
- Create a new DB for the Web Portal to create the Tables and SP in, and access the other database through cross-database queries within Stored Procedures (possibly use Views to simplify this)
I want to make sure that the web portal has as little performance impact as possible on the web service.
Any suggestions would be gratefully received!