December 4, 2007 at 5:20 pm
My basic question is this: if we combine all of our databases into one database using schemas to differentiate the tables, will we be able to use stored procedures that are associated with a schema as a data source for a SqlDataSource object on an ASP.NET page? Details of what I have tried are below. Thanks!
So, today we took one of my databases (Marketing) and used it as a conversion test case by creating a schema (MarketingWorkOrder) within itself: we imported the data into the new schema-named tables, scripted the stored procedures as "CREATE" and changed "dbo" to the schema name (then executed them), and modified the one view I have defined. All objects show up in the Object Explorer with the schema name as expected. I have made sure that I am a User in a Role that is assigned to the Security Schema.
The Object Explorer tree looks something like this:
Databases
Marketing (Note: in the Properties the owner is "sa")
Tables
MarketingWorkOrder.mwoJobDesc
MarketingWorkOrder.mwoMediaType
MarketingWorkOrder.mwoProjectType
MarketingWorkOrder.mwoWorkOrder
Views
MarketingWorkOrder.vwWorkOrder
Stored Procedures
MarketingWorkOrder.usp_GetJobDescList
MarketingWorkOrder.usp_GetMediaTypeList
MarketingWorkOrder.usp_GetProjectTypeList
MarketingWorkOrder.usp_InsertWorkOrder
MarketingWorkOrder.usp_UpdateWorkOrder
MarketingWorkOrder.usp_DeleteWorkOrder
Next, I opened my web project (ASP.NET 2005 on .NET 2.0 Framework) and Refresh'ed the tables, views, stored procedures, and even the database in the Server Explorer.
The data connection in the Visual Studio Server Explorer looks like this:
Data Connections
myserver.Marketing.dbo ...... could this "dbo" be the problem?
Tables
mwoJobDesc (MarketingWorkOrder)
mwoMediaType (MarketingWorkOrder)
mwoProjectType (MarketingWorkOrder)
mwoWorkOrder (MarketingWorkOrder)
Views
mwoJobDesc (MarketingWorkOrder)
Stored Procedures
usp_DeleteMarketingWorkOrder (MarketingWorkOrder)
usp_GetMarketingJobDescList (MarketingWorkOrder)
usp_GetMarketingMediaTypeList (MarketingWorkOrder)
usp_GetMarketingProjectTypeList (MarketingWorkOrder)
usp_InsertMarketingWorkOrder (MarketingWorkOrder)
usp_UpdateMarketingWorkOrder (MarketingWorkOrder)
When I go to my SqlDataSource object to "Configure Data Source" to call the MarketingWorkOrder.usp_GetJobDescList sproc, the schema-version sproc is not in the list (the dbo-version is there, and selecting it will give a "does not exist" message). However, if I enter "MarketingWorkOrder.usp_GetJobDescList" as a SQL Query it will return the correct records when I press the "Test Query" button. I think this approach will work, until I need to pass parameters to a sproc. I want to call the schema-version sproc like I have called it before. Have I set up something incorrectly? Or perhaps this is a known issue with schemas and stored procedures?
I apologize for this long explanation. But I hope in laying out all of the details I can think of, someone will be able to spot what I have missed. Thanks!
December 18, 2007 at 8:29 am
I've played around with schemas a bit and the problems I have had have been to do with security. To be more precise, granting access to objects in one schema to a stored proc in another. I don't think the problem is insurmountable I just didn't have time to delve deeper into the issue.
There are more issues at stake than simply converting existing databases into schemas within a single database.
Don't forget that separate database will have separate data files where as, unless you are explicit in setting up multiple data files the single database instance won't.
Separate files = separate thread for each file which should aid performance.
Having separate files also allows you to put different databases on different physical devices. Obviously you can do this with schemas but it has to be explicitly set up.
Also, consider backup and restore times. If you have multiple databases that loosely depend on each other then being able to backup/restore these database independently is a useful thing to be able to do. If your databases are heavily dependent on each other then a restore of one database in isolation may be impractical.
I would recommend that you analyse your databases carefully to see what schemas you need. To give an example, you may find that some objects in all databases could go into a "Common" schema in your single database with the rest being a schema per database. In effect you end up with more schemas in your new database than you had original databases.
The opposite could also be true. Tables currently split across many databases could fit together much better in far fewer schemas.
Like I said, its a careful analysis job and check what your dependencies are going to be across the schemas
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply