Dynamic DB Connection based on user type
-
I need to create an application: upon user login, check the user type and decide the database that application will use for that user. Here is what I have: 1: Master database: stores user information and application configuration data, such as which db to use and connection string 2: Database I on server 1: contains all the application data for user group I. 3: Database II on server 2: contains all the application data for user group II. 4: Database I backup on server 2: as backup for user group 1 in case server 1 fails. 5: Database II backup on server 1: as backup for user group 2 in case server 2 fails. 6: Through log shipping between server 1 and server 2 to keep DB I and I and their backup up-to-date. 7: The web site is running on web farm. The session state management is using SQL server session state management, which is on the same server as Master DB. My questions are: 1: How do I determine if server 1 or server 2 is down and it's time for application switching to use the DB backup on the other server. 2: What to do if the master DB server is down? 3: Is there a better approach than the above design to achieve the same goals: using different DB for different users, and achieving fast recovery by using the backup server? I would really appreciate any suggestion and help! Thanks in advance!
-
I need to create an application: upon user login, check the user type and decide the database that application will use for that user. Here is what I have: 1: Master database: stores user information and application configuration data, such as which db to use and connection string 2: Database I on server 1: contains all the application data for user group I. 3: Database II on server 2: contains all the application data for user group II. 4: Database I backup on server 2: as backup for user group 1 in case server 1 fails. 5: Database II backup on server 1: as backup for user group 2 in case server 2 fails. 6: Through log shipping between server 1 and server 2 to keep DB I and I and their backup up-to-date. 7: The web site is running on web farm. The session state management is using SQL server session state management, which is on the same server as Master DB. My questions are: 1: How do I determine if server 1 or server 2 is down and it's time for application switching to use the DB backup on the other server. 2: What to do if the master DB server is down? 3: Is there a better approach than the above design to achieve the same goals: using different DB for different users, and achieving fast recovery by using the backup server? I would really appreciate any suggestion and help! Thanks in advance!
There are commercial products that work with Databases to perform mirroring, monitoring and automatic rollover on failure. Is something like that an option for you? Trying to roll your own solution will certainly be a sizeable effort and you will likely not achieve the level of confidence a commercial product will have.
led mike
-
There are commercial products that work with Databases to perform mirroring, monitoring and automatic rollover on failure. Is something like that an option for you? Trying to roll your own solution will certainly be a sizeable effort and you will likely not achieve the level of confidence a commercial product will have.
led mike
-
I need to create an application: upon user login, check the user type and decide the database that application will use for that user. Here is what I have: 1: Master database: stores user information and application configuration data, such as which db to use and connection string 2: Database I on server 1: contains all the application data for user group I. 3: Database II on server 2: contains all the application data for user group II. 4: Database I backup on server 2: as backup for user group 1 in case server 1 fails. 5: Database II backup on server 1: as backup for user group 2 in case server 2 fails. 6: Through log shipping between server 1 and server 2 to keep DB I and I and their backup up-to-date. 7: The web site is running on web farm. The session state management is using SQL server session state management, which is on the same server as Master DB. My questions are: 1: How do I determine if server 1 or server 2 is down and it's time for application switching to use the DB backup on the other server. 2: What to do if the master DB server is down? 3: Is there a better approach than the above design to achieve the same goals: using different DB for different users, and achieving fast recovery by using the backup server? I would really appreciate any suggestion and help! Thanks in advance!
and if the master database goes down....? :P
Mark Churchill Director, Dunn & Churchill Pty Ltd Free Download: Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.
Alpha release: Entanglar: Transparant multiplayer framework for .Net games.