We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we have to update 3 databases.
Would it be a better idea to delete the Users table from databases 2 and 3 and replace it with a Synonym that points to database 1?
Here's the Pros/Cons I can think of:
Pros
- Easier Maintenance. Can update Users in one location instead of 3
- User Ids would match between databases (Important since a lot of add-on apps are based on UserId)
Cons
- Don't think this is standard procedure, so might be confusing
- Users would have to have identical settings between databases
- (From gbn's answer below) If Database 1 ever goes down, Database 2 and 3 will also be unavailable. Also there is the potential problem of the data being inconsistent in the event of a restore
This is an option I'm considering for a few different tables containing Settings that are identical between the databases, not just the Users table. I am using Users in the example since it is easy to understand.
Best Answer
Why do you have 3 databases with the same users?
What happens if one database goes down now?
I'm asking these questions because the Con of the user-source database going down preventing use of the other two databases may not be as big a problem as it sounds.
Additionally, if a database goes down now you will already have consistency problems if users are modified in the other two DBs during that period. I don't think we can give the best advice without more context.
Right now I would create a fourth database for users, make changes only there, and sync across the other databases. You're already distributed-denormalized by changing essentially the same data in three places, and probably already have consistency problems. If fault tolerance is important then this scheme still gives that while solving the multiple entry problem.
I think that having this fourth user/settings-only database rather than using one of your existing ones is a good strategy as it becomes less likely to go down since it isn't tied to other resources or heavily used. I see now that you said you can't do this, but I'm not clear on why. Do the applications on the main database support user editing directly, or is user editing a completely separate function that can be pointed elsewhere?
True, with this "canonical user table" idea--whether you use synonyms or synchronize the data--you can't modify users during a down user DB, but that seems ok to me. Fix the problem and bring it up! One source, one place to edit, one thing to fix if broken. With synchronization, all other databases have temporary copies they can work from, but no editing. Minimizing data duplication across systems is a great and useful goal. It is a serious problem to enter the same data in three places, so do whatever you can to eliminate that.
To address more of your comments, if your user IDs are different across all your applications, then you should seriously consider a planned down time for your two new databases to bring them in sync with the main one (ask a separate question if you need ideas on how to accomplish this, which is tricky but not really THAT hard).
If you analyze your business needs and find that the main database must have the user data that's okay, you still use it as canonical, then choose between synonyms or synchronizing to address how unplanned down times affect all the databases.
One additional Con to using synonyms is that you would no longer be able to have proper FKs to Users in each database.