SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Multiple company
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IPFL
Starting Member

Belgium
4 Posts

Posted - 01/03/2014 :  04:56:52  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/03/2014 :  07:37:54  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

IPFL
Starting Member

Belgium
4 Posts

Posted - 01/03/2014 :  07:51:25  Show Profile  Reply with Quote
Thx for your reply
Maybe 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2014 :  08:18:42  Show Profile  Reply with Quote
quote:
Originally posted by IPFL

Thx for your reply
Maybe 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 scenario
Bringing 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 01/03/2014 :  11:12:08  Show Profile  Reply with Quote
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 data
2) If the client decides that they want their data back we can easily backup their database and send it to them
The 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

Edited by - Bustaz Kool on 01/03/2014 11:18:54
Go to Top of Page

IPFL
Starting Member

Belgium
4 Posts

Posted - 01/06/2014 :  02:38:45  Show Profile  Reply with Quote
Thx a lot for your answers I will go for separate databases.
The applications calls directly the data from the database no OLAP
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/06/2014 :  06:43:12  Show Profile  Reply with Quote
Ok..then separate db should be the best choice.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000