Here is the situation I have inherited:
- We have approximately 10 websites (Asp.net web forms) that each have their own database.
- Each of these databases houses some site specific data, and each has some form of a user table that will map to what is called a User Service.
- The User Service is just another website that has a database with one table (User) that have about 3 different unique identifiers for a user: a UserId guid, an internal desktop application id (third party app, needed to map back to it), and a username.
- On all the different websites the databases have the User table, sometimes called Person, or Student, or whatever else, and they are inconsistent in which UserService.User.[IdentifyingColumn] they map to.
The Problem:
It is getting to a point where these application are now needing to share some data with each other, and I see that need increasing as the departments are streamlined even more. With this current set up, it is a lot of hoop jumping to get the right UserId column to match to the right UserId column in the UserService.
Confused yet? Me too..
The Question:
How to approach correcting the issue? I have a few different ideas, some I like more than others, but in order to implement them I need some solid benefits I can bring up to the head of IT, as well as justify the programming time taken for the re-write.
Idea 1: Create a true intranet application. One web application with one database. This way they all live in the same project, and sharing data between the various departments is very simple and quick.
Idea 2: Create a “Master Key” identifying field that will be the end all to identify a user. Make this key available on each database and so there will not be any odd mapping and the various sites can talk to each other. To me this one solves one problem, mapping each db to the next one. It is still very fragmented and there is very little code re-use.
Idea 3: Work with it in the current form trying to continue the present fashion of many small databases and sites.
Which of the above options would you suggest, and why?
3
Single Sign On is an excellent idea. Just so you know, you don’t necessarily need to use a specific technology, such as Active Directory, WIF or OAuth. You can implement it with just ASP.NET forms auth. Here’s how you do it:
Selling it to the head of IT
- Don’t make it just about developer happiness, there’s a significant user happiness benefit of SSO.
- Take your current workload and project the time required to keep mucking with an imperfectly architected system over a year. Compare that with the time required to do a re-write. Most of the time, if it breaks even after a year, it’s a good trade.
Deciding on How
- Figure out the most common way users are identified, (such as AD name, or email address) and try to centralize on that – it should reduce the scope of the re-write.
- Once you have the best key defined, pick a technology or method that leverages that.
Random Tips from my experience
- I’ve had the least friction with OAuth. Mainly because we used google apps, so everyone already had a corporate google account.
- Windows Authentication has problems depending on if it’s set up with NTLM or Kerberos – I had a particular problem with NTLM over a VPN.
- ADFS was just too much hassle to get working correctly for anyone outside of our domain, it really wasn’t worth it.
To keep your application maintainable, I would make the configuration as simple as possible. To your question about the separate databases, if they don’t buy you anything by being separate, reduce the complexity and merge them. Since they are an intranet, I’m assuming they won’t ever be deployed separately.
The scenario you describe is a perfect match for a Single Sign On service. There are several paths you can follow to a solution:
- You mention that this is for an Intranet. Assuming you are using Windows Server for security, you have everything you need in place for a Single Sign-On solution. Your applications can use Windows Authentication and the users on the Domain will automatically be logged in once they navigate to your site.
- OAuth is another option for creating the infrastructure you mentioned (one application provides information to another based on trust established by an Identity Provider). Again, Microsoft provides an OAuth solution called Windows Identity Foundation (WIF) that provides the infrastructure for what’s called “Claims Aware” applications.
- Windows Azure Access Control Service/Active Directory is an option if you see the need to expose your application to the public. You’ll even be able to configure your app to accept logins from third party providers like facebook, Windows Account (formerly live ID) and Twitter.
Using these as a foundation for your identity management, will allow you to focus more on the important parts of your application.
Assuming a Windows domain environment, Windows authentication would be by far the most sensible way to go.
Unless the sites and databases are all very small, I would avoid having 1 merged site/database for all and keep the sites all separate for a couple of reasons:
- avoid downtime for all systems if you only need to change one system
- different teams can work in isolation on different apps
1
I believe you are using old RDBMS Database(like sql 2008)
i would suggest you to go for ORDBMS(Oracle 10g or 111g) which can work more efficiently specially for these types of large amount of data using single instance for multiple database that can share data across.
You can share other table data using *Object_User.table_name* in your own Table all those database would share one instance (connection) as a single SID service.
This way You can connect with any dataabse using a single connection.
for further research visit =
Oracle website
Oracle website for multiple database
or reply for further discussion….