How do I link to a remote databse with ASP.NET MVC?

Question

I have an ASP.NET MVC application up and running, using a SQL Sever express 2005 database instance that is running on my development machine. When the app gets deployed to production, however, the IIS instance and the SQL Server instance will be on different logical machines. I dont know if they're different physical boxes, but i doubt that matters.

The production DB server is currently in place, with a populated DB on it. Since I have read access to that instance, I'd like to point the project on my dev machine to it to make sure the remote DB Connection still works. The schema is the same, obviously, just the location and contents are different.

I currently have the project linked up to the .mdf file located on my development machine. What changes to I need to make to switch it over to the DB instance on the remote server?

Answer 1

The easy answer: change the connection string for your data layer (Linq to SQL?) in the web.config to point to your out-of-process database instance.

In more detail, here are the steps I took to split the database out of my site on my development box. By doing the following, you can test the two-server configuration locally before deploying it to production.

1) Install SQL Server Management Studio http://tinyurl.com/ynl9tv

2) Open Management Studio and connect to your local SQL Server Express instence (typicaly [boxname]/SQLEXPRESS)

3) Right-click on the Databases folder in Management Server and select Attach...

4) Locate your .mdf file in you project and open it.

5) Rename the newly attached database to what you want it called.

6) Give whatever service account your IIS application pool runs under permissions to the database. First create an account under the top level Security/Logins folder, and then add that account to the new database's Security/Users folder. For my configuration, I am using NETWORK SERVICE.

Now that your database is mounted as a stand-alone database in SQL Server Express, you can connect to the database using a normal SQL Server connection string (http://tinyurl.com/dltc2t).

At this point, your development environment is simulating the production environment where the database and the IIS process are split. You only have to change your connection string in production to point to the production database.