How to handle switching between two databases
Let's assume I'm going to create project in ASP.NET MVC 4 and users should be able to easily switch between test/demo database and production one. Both databases will have same schema, but different users and data.
Should I simply use two versions of web.config with different connection string and deploy it to two separate IIS instances?
I thought also about choosing database from dropdown list during logging in, so I would need only one IIS instance and one config with two connection strings. Do I gain anything from latter approach other than more complex code to handle it?
In our system we use web.config transforms and use MSDeploy with Publish Profiles in the project for our two different servers, Dev and Production.
To deploy to Dev, you simply click publish and select the "Dev Server" Publish profile and out it goes. Same thing on production, but you select "Production Server" from the publish profiles.
Dev and Production are two different physical servers, which is ideal. What if you do something where you need to reboot the dev server to test some big changes, or some new updates etc, you don't want production going down with it.
Our environment is virtualized so it was easy to create a production server and a dev server. Really we created a dev server and once done, we based production off a snapshot of the dev server, so they are basically identical with different code bases.
We also setup Visual Studio Remote Debugger on both servers so we can debug code without having to install visual studio on them.
Being on two different servers, they have different urls, e.g.
something.com login.something.com admin.something.com dev.something.com dev.login.something.com dev.admin.something.com
Now, we also use twitter boot strap and out design has 2 columns on the left and 2 columns on the right for spacing. So when on the dev server, I render giant "DEV" images vertically in the column spaces.
I should also mention source control. We use SubVersion for a source control server and Tortoise SVN with the Visual SVN VisualStudio extension to keep our projects in source control.
It's setup to the point that any developer given access to the code can to a Get on the repository and open it in visual studio 2013 and click publish. The code is very easy to move from developer to developer.
We also have some versioned assets the site uses and we have the dev/prod servers setup with Tortoise SVN as well. E.g. EmployeePhotos are in source control, and a developer can add new photos to SVN and go on the server and go a get on the virtual directory containing them and they are uptodate. Handles deletes as well. If we delete a folder from source control and do an Update on it, SVN deletes the deleted images.
All code aside, I would host the two separately. The chance of someone screwing up production data when they think they're messing with test data is high enough that it's better to have explicitly different URLs. I'd even probably have visual cues in your Master layout (color differences, differences in the main page header,etc) to make it clear to the user where they are and what they're expected to do there.
Even if you're not worried about that per se, you're right that managing it internally will be more complex as well, and error-prone. I'd steer clear of it.