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 |
|
cristianotestai
Starting Member
4 Posts |
 Posted - 2015-04-27 : 17:17:07
|
| Hello! I have my project where i have one centralized database, where for every company that uses the system has a schema with tables and objects, which have one common schema that is used by all companies, with some tables, as follows: CRM Database Common Schema: Users and Companies Tables Company X Schema: Table Accounts, Contacts, Opportunities, Activities Company Y Schema: Table Accounts, Contacts, Opportunities, Activities Company Z Schema: Table Accounts, Contacts, Opportunities, ActivitiesCurrently not use the concept of Filegroups and how few clients and bases not very large, do not know if it is still recommended to use. I would like the opinion and recommendation on how to proceed to manage bkups, creating Automatic routines bkups and if you could have bkups partitioned by schemas (from what I've been searching is not possible) even using filegroups. What would really like is to perform scheduled and incremental bkups from a first bkup and if possible have them separated by schemas, where if a problem occurs on information from a particular company, only that company would stop to perform the restore process thereof. Any ideas and help are welcome. Best Regards, Cristiano Brazil |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-27 : 17:29:35
|
| I don't understand why your database is designed like this. Typically if you want this kind of separation, you'd just maintain multiple databases. Yes you could use filegroups, but I don't see the need here. Just backup the database. In the event of a restore, which should be very, very rare, you restore the whole thing or the filegroups.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
cristianotestai
Starting Member
4 Posts |
Posted - 2015-04-27 : 22:09:00
|
| Hi tkizer!I have this structure for a few reasons:1) With multiple databases I can not get Referential Integrity between tables of different banks, with Schemas I can.2) I believe that query's with joins between tables in the same database are more performing than the joins between multiple databases.3) I think bkups management with one database with multiple schemas would be simpler than one separate database for each company, but do not know if I'm right.4) As my application is centered on the Web for different companies, with the Common Schema, which has the table of users and companies in the validation of the login screen I connect to the bank (which is unique) and after validating the user logged I identify the schema (company) and set up the connection. If you had separate databases, I would need a connection at login to the database with the User information and company and then would have to make new connection to the specific database of the user company logged.Basically these are the reasons for using this structure.Thanks for your help.Cristiano |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-28 : 01:32:34
|
| Unless you go down the filegroup option , then the only other option you have is a Full\Diff option. Focus on maintaining security, therefore users should not be able to read\write objects in other schemasJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Bkup using a database with multiple schemas