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 |
|
IPFL
Starting Member
4 Posts |
 Posted - 2014-01-03 : 04:56:52
|
| Hello,We've about 20 SQL database on each company. Now we are going to centralize the database to one db in the cloud.There are a lot of users and a lot of data so how can I solve this the best?Thx |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-03 : 07:37:54
|
| byou need to migrate dbs to centralised server by taking backup and restoring. Once all dbs are copied you can bring on premise boxes down and switch connections to cloud boxes instead.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
IPFL
Starting Member
4 Posts |
Posted - 2014-01-03 : 07:51:25
|
| Thx for your replyMaybe I didn't specify very well my question.What is the best setup?In the databasecloud separate databases for each company so 20 separate databases or combine all them to one (because all tables in the different companies are the same)Or any other solutions? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-03 : 08:18:42
|
quote:
Originally posted by IPFL
Thx for your replyMaybe I didn't specify very well my question.What is the best setup?In the databasecloud separate databases for each company so 20 separate databases or combine all them to one (because all tables in the different companies are the same)Or any other solutions?
It depends on scenarioBringing all of them to same db with an additional companyid means you need test each and every code and make sure each company people have access to only corresponding company data. So development as well as maintenance perspective its much more work. Especially in case of tables containing sensitive company information extreme care has to be taken.Putting them in separate dbs would mean no such issues as you can maintain users separately for each db corresponding to company and doesnt need to worry on security of data much. So from development and maintenance aspect it would be much simpler.One question worth asking is whats the db used for. Is it for OLAP/reporting need or is it for use by application?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-01-03 : 11:12:08
|
| We ran into the same issue. We went with separate databases for two main reasons:1) Security - Very important that no one can see another company's data2) If the client decides that they want their data back we can easily backup their database and send it to themThe drawbacks are relate to increased maintenance; especially in keeping the schemas in sync. Inevitably, a client will want some change specific to their needs. Do you release it to everyone or try to track the customer specific schemata? You'll need to decide which costs/benefits work in your environment=================================================A man is not old until regrets take the place of dreams. - John Barrymore |
|
|
|
IPFL
Starting Member
4 Posts |
Posted - 2014-01-06 : 02:38:45
|
| Thx a lot for your answers I will go for separate databases.The applications calls directly the data from the database no OLAP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-06 : 06:43:12
|
| Ok..then separate db should be the best choice.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Multiple company