Multiple SQLServer databases
-
Hello, We're upgrading our application to use SQL Server, having used Access for some time and I need some insight, as I may have to redesign the tables. The application is a web based front-end for our data. In the Access version, we have a completely separate database for each Client/Project who logs on, each database had the same structure. It is easier to deal with things when they had this both physical and logical division. There is some data duplicated between the databases, and this must be manually copied, usaully when the database is first created. Is SQLServer designed to allow multiple databases to exist independently, but still allow some common data to be shared, and also allow Stored Procedures to be shared among the different but same structured databases. Perhaps we should go the way of combining everything into one database however I would really prefer to store them independently. I understand that Oracle may have something like this in the way of folders. I'd appreciate any words of wisdom. Thanks, John
-
Hello, We're upgrading our application to use SQL Server, having used Access for some time and I need some insight, as I may have to redesign the tables. The application is a web based front-end for our data. In the Access version, we have a completely separate database for each Client/Project who logs on, each database had the same structure. It is easier to deal with things when they had this both physical and logical division. There is some data duplicated between the databases, and this must be manually copied, usaully when the database is first created. Is SQLServer designed to allow multiple databases to exist independently, but still allow some common data to be shared, and also allow Stored Procedures to be shared among the different but same structured databases. Perhaps we should go the way of combining everything into one database however I would really prefer to store them independently. I understand that Oracle may have something like this in the way of folders. I'd appreciate any words of wisdom. Thanks, John
I would advice keeping all the data in the same database as that makes maaging the databases like backup etc. easy. Else if you want to stick to the multiple database option, you can definately store the common data in a common database and then acess the tables using notation [Common DB Name].[Table Owner].[Table Name]. As far as stored procedures are concerned, I would recommend keeping copy of same in all databases as using three part concvention as mentioned above will make the stored procedures creepy and bad performing. Gaurav