Solved

One large database or several small ones for different companies

Posted on 2010-08-29
13
669 Views
Last Modified: 2012-05-10

I am marketing an online application and I am thinking what is a best solution with respect to the database architect.

1- To have a large database with an Account field - size to under 5 billions records,. use mssql
Or
2- To have a similar database for each account size to millions of records, using mssql express

For item 2 is there a good way to keep the database structure same after modifications - How?

This is a very important question  please help - thanks
0
Comment
Question by:goodk
13 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 283 total points
ID: 33552675
As long as you use a single MSSQL instance, there shouldn't be a big difference. But the overhead for managing several DBs is slowly higher (more metadata to keep in mind) then for a single one.
And remember - the Express Edition does not allow for advanced techniques as parallel index query, materialized views (table snapshots) and some more, which come handy with the Enterprise (not Standard) Edition.

Using different DBs makes sense if you want to keep the data separate, e.g. for branches, or to be able to relocate the contents to other instances/servers some time in the future, or if you have different replication needs (if any). Or, of course for security reasons.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 55 total points
ID: 33552759
I would probably use a different strucutre:
-one database for all the references tables (tables with data common to all accounts).
-one database for the data specific to each account.

Pros:
-if one account close, you can quickly swap its data
-if one database gets corrupted, it doesn't affect others
-easy to set the security correctly

Cons:
-a bit more complex to update structure

If you'd like a tool to help you maintain the structure of your database, check http://www.red-gate.com/products/SQL_Compare/index.htm
0
 
LVL 11

Assisted Solution

by:mattibutt
mattibutt earned 54 total points
ID: 33552864
i would say it will make the maintanance task quite difficult in the event of debugging just think of basic stuff like scrolling up and down either in sp windows or table windows if the database becomes corrupted can happen then you are risking all different businesses the better idea is use different database for each company it will make portability simpler if the client no longer wants to be served by you but you are obliged to provide their data repository to them if you perfer to use the same database then each should use different database schema for instance company a can have schema dbo.a etc its a good idea to keep it separate for overall life cycle of each application  
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Assisted Solution

by:Marcjev
Marcjev earned 54 total points
ID: 33553380
I would say go for multiple databases. This is also the most easy to scale, in case your business booms. You can switch more easy to multiple servers this way.
Updating a database structure should always be done in script, which can easily be run against multiple databases (and servers). And in case of bilions of records, you want to make sure it's bugfree. Some tables changes can take lot's of time if there are very much records.
0
 

Author Comment

by:goodk
ID: 33553905
For a large database would there be any sluggishness.  When the records are 5 billion or so?
speed is my biggest concern
0
 

Author Comment

by:goodk
ID: 33553952
Marciey:  Thanks for you idea.  Do you have any experience in managing mulitiple databases from same origination?
How do a user alter a db when he is logged on to the database?  What if there is an error in the middle of the copy?  Please tell me what would be the safest process for upgrade?
thanks
 
 
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 283 total points
ID: 33554063
You can issue the necessary SQLs in a session while connected to the DB. Safest is to run the whole change in a single transaction, but that might block all other applications and user to access the touched tables, until the transaction is committed.

The SQLs are needed anyway for changing the DB - you won't do that interactively, that is by hand.
0
 

Author Comment

by:goodk
ID: 33554179
Olemo:
 
So, it appears I just modify the structure and not create a new structure and copy??
Do you have a small example of DB update?
i plan to use sqlexpress.
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 283 total points
ID: 33554573
You can do the changes on a test db manually, using Management Studio (Express), but you will need to manually write the ALTER TABLE commands for applying them to another DB. Nothing you get with MSSQL, or can download for free from MS, can compare DB metadata and create SQL from the differences. 3rd-party tools can, of course.

So best is to do *all* changes, even for test DBs, with SQL. That way you have already tested the necessary commands, and it does not matter much again how many similar DBs you have to roll-out the changes.
0
 

Author Comment

by:goodk
ID: 33554642
can you point me to an small example?
Actually, we plan to provide an upgrade button to the customer.  So the entire upgrade happens behind the scene.
Would we have a problem when they are working and we are upgrading?  So do have to suspend the website?  I do not know how would I close all the instances the customer have opened?
It will be helpful if we have prior knowledge to help us design our upgrade system.  thanks
 
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 283 total points
ID: 33554675
The traditional way to allow for user-triggered updates is to keep a history of changes, as successive SQL statements for each build ((sub)release):

/* 400.1 */ alter table customers add column FunnyColumn char(1);
/* 400.1 */ alter table taxes add column nonUS char(1);
/* 400.2 */ alter table customers drop column FunnyColumn;  /* no longer necessary */

and the "update" procedure just starts from the current release of the production database.

If other sessions are influenced depends on the operation. Adding a column needs to be done in real-time, so there is definitively an effect on other sessions. The smaller the tables, the less the effect, as a rule of thumb.
Dropping a column usually only marks that column as dropped, and existing records are not touched, at leat not before there are other changes (e.g. updates) on records.

Kicking out other sessions while updating will speed up the update process, and lessen the risk of creating blocking situations for all connected (including the updater). If I update a database, I only stop heave writers - in most cases applications are split into something like 10% writers and 90% readers, so it is easy to stop the writers only.
However, it is not necessary to do so.
0
 
LVL 7

Assisted Solution

by:jasonpaine
jasonpaine earned 54 total points
ID: 33555294
You can use Redgate's SQL Compare to sync the structure between databases.

It's real easy, just pick the two databases it compares them then it will create the scripts to make one database look like the other.

0
 

Author Closing Comment

by:goodk
ID: 33557225
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question