Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mourad
Starting Member
6 Posts |
 Posted - 2007-03-28 : 04:33:36
|
| Hello all,I am working on an ASP.NET that different companies will be loggin on to. The way the SQL db has been written is that each company will have it's own database. However, I am not sure if this is the right approach since modifying the database would be quite a hassle (i'd have to modify each one). Is there a way a single databse would have several instances that share the same structure but with different data ? What is the recommended approach in these situations ? Should I re-design the database so that ALL companies store their data in one huge database ? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-03-28 : 21:13:39
|
| There are pros & cons but without knowing the background I'd bundle them up as you suggest.About the only upside of having multiple databases that I have actually taken advantage of (inherited system) was that by having a region per database we could have different schedules for new version rollout and backups. Without that someone, somewhere in the world, would have downtime during working hours.Having said that, the data was actually related so there were cross-database queries and updates so in fact it was actually more wrong than right. You need to consider the operational stuff more than the capabilities of SQL Server to manage a 'huge' database. That is the least of your worries - we look after TBs of data on SQL Server and it's up to the job. |
 |
|
|
Mourad
Starting Member
6 Posts |
Posted - 2007-03-28 : 22:48:53
|
| Thanks for your reply. There's still one thing I don't get though: is it possible to create multiple copies of the same database without having to duplicate my work whenever I need to update the DB structure ? That, is my question. Is there such a thing as an 'instance' where everything is shared BUT the data ? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-04-01 : 21:04:39
|
| Not to my knowledge. It's really just basic change management, not a database issue.You should already have scripts to do this pushed through from development & UAT before you attempt live. It's just a question of applying your upgrade in multiple places. If you're going to say that you just dive into Enterprise Manager and apply your changes then you need to look carefully at that practice. |
|
|
|
ElManiak
Starting Member
4 Posts |
Posted - 2007-04-02 : 16:46:30
|
quote:
Originally posted by Mourad
Is there such a thing as an 'instance' where everything is shared BUT the data ?
Well you can use Viso to manage your database structure including views and SP, and when ever you have a modification you apply ti to Visio and then populate it to the DBs. this is the easyest way I have found.ElManiak |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2007-04-10 : 11:53:18
|
| Look at your business requirements. As they said, you don't want to tear down a wall unless you know why it was put there in the first place. I'd take a second look of what's really the business issue. Redesigning and recoding can be costly. Maintaining different databases can easy or can be hard. Same thing with single instance of database. I usually maintain a version table containing the build of the current database. Every time we modify, we run the alter statements as simply as connecting and running the script. Of course, we do try it first on a test environment. The decision to have multiple database was simple driven by the business need-- companies don't want to share their data to other companies. If they want their data, it can be as simple as copying the whole database. No special queries or things like that. When rolling out updates, we do it one at a time so if there is really a problem, the error will not ripple to other production database. Now, there are times when we do need to have one database for everyone, just because there are no business requirements stating otherwise. Heck, we have our standard system, we own that system, if there is someone who will adapt, the companies will have to adapt to us. If we provide new feature and updates, it just show up on the user screen next time they login. And they are thankful for that. From experience, look at your requirements before you leap. Your problem might not be redesigning the system but just finding an easier way to roll out updates ;). May the Almighty God bless us all!www.empoweredinformationsystems.com |
|
|
|
|
|
|
|
|
Multiple databses for multi-tenant ASP application