January 10, 2012 at 11:49 pm
Hi,
We are developing CMS software which will be used by around 300-500 websites in future. All websites will be having same database structure but their content will be different. I have few questions in mind:
1. Separate database for each website will be good option. We will be having continuous ongoing changes to database structure, so keeping all databases structure (tables, indexes,procedures) in synchronize will be huge task. All databases will be on same server. What is better way to keep all these databases structures in synchronize with minimum effort?
2. Keeping single database for all websites, so maintenance will be easier. There will be WebsiteID in each table to identify website. But will have to take care of performance issues, if we go with this approach.
We are using asp.net with sql server 2008 for development. I have heard about Share point technology. Can Share point will be useful in this case. Please suggest technology/methodology that will be useful to achieve good performance with less maintenance.
January 10, 2012 at 11:58 pm
I will suggest have the database based on domain specific or "common group of users" specific.
----------
Ashish
January 11, 2012 at 12:01 am
How to keep all databases structures synchronized? They will be on same server and we will be having full access to them.
January 11, 2012 at 12:23 am
This definitely needs to be thought thru before committing to either solution. Here is the way I see it from the db design and continuity of business perspectives:
1. Separate database for each website will be good option. We will be having continuous ongoing changes to database structure, so keeping all databases structure (tables, indexes,procedures) in synchronize will be huge task. All databases will be on same server. What is better way to keep all these databases structures in synchronize with minimum effort?
Yes, maintenance would be quite difficult, but there are ways you can automate running database scripts. Also, if you would have a separate database for each website (300-500) on one server, it could become very slow, or your server would be very, very expensive. However, having a separate database for each website would enable you to perhaps separate databases to two or more db servers in the future when the load increases too much. This model would also allow only one website to be down for maintenance while others would remain online. If you ever decide to sell an individual website, you will be able to easily provide the database with it without much need to purge other data.
2. Keeping single database for all websites, so maintenance will be easier. There will be WebsiteID in each table to identify website. But will have to take care of performance issues, if we go with this approach.
Single database would be much easier to administer, but ... all your websites would be down during maintenance. Your single database will most likely experience a lot of performance problems even with very expensive hardware, and you will not be able to scale it at all. You will not have the option to separate it onto multiple database servers unless you recode your CMS application to work with federated database servers which is no trivial task.
Having multiple disk volumes for data and log files goes without saying for either solution.
+1 for a separate database for each website.
Jan Studenovsky
January 11, 2012 at 12:44 am
Hi,
Thanks for detailed analysis. I am also thinking for separate database for each website. But issue is with the maintenance as we will be having fixes or changes in database. We can write scripts for table alterations or stored procedures modifications and run that script for all databases. But are all database changes possible using script? Because making changes manually to each database will be a big task. Let say if we add index for table or changing column size? Or will there be limitations of automated script? I am asp.net developer and dont have expert knowledge of sql server. Can you suggest sql server tools which will be useful or any new database technology which might help in this case.
January 11, 2012 at 12:51 am
A practical long term solution which will scale is to have individual databases per website. This will help ensure better scale , security , feature driven applications and data management. There are plenty of feature available in the market for maintaining continuous integration and automated deployment , VS 2010 is one of them.
Having all website hosted in the same database is equivalent o putting all your eggs in one basket.
A general rule i follow during design of such systems , is what right for the customer (short term , long term and requires minimum support in future )even if its a pain in the *** for me.
January 16, 2012 at 4:35 am
Hi,
Thanks for detailed explanation. We are now thinking to go with 1st approach. i.e. separate database for each website.
But i am not sure on how to script the database changes. I will be running script(containing all the changes) for all databases. Can all database changes be scripted or is it necessary to use sql server mgt studio wizard to do some changes? Because if there is need to use sql server wizard to make some changes, it will be big task to do it manually for all the databases.
Please let me know the changes which can't be easily scripted and tools we can use to generate scripts containing changes. Or will I have to use some 3rd party tools to generate script for changes?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply