Hi,

I use VS2008 Pro with Oracle at work, and I'm trying to set up my home server for ASP.NET development.  I would appreciate any advice on how to do this, including how to do it correctly (i.e., best practices).  At home I am running Windows Server 2003 Standard with SQL Server Express 2005 and the 3.5 .NET framework on my server.  My PC has Visual Studio 2008 Standard and the version of SQL Server 2005 that ships with it.

At work we have DBAs that handle database setup, including the migration of tables from test to production.  Our databases reside on separate servers, and even when I'm developing on my PC, I'm using a test database on a network server.  And as I mentioned, we are an Oracle shop, so this will be my first taste of SQL Server.  Some of the questions I hare are:

1) When I created my first (and only) database on my PC, I did it through VS using 'Add New Item', 'SQL Server Database'.  When I did this, VS told me that I should place it in the 'App_Data' folder, which I did.  Is this the correct way to set up a database for my ASP.NET application, and is the App_Data folder the best place to store it (on both my PC and the server)?

2) This may be redundant, depending on the answer to number one, but if I create a database that will be used by multiple .NET applications, where would be the best place to locate that database (on both my PC and the server)?

3) When I publish my application to my server, will this handle all the SQL Server details, since I already have SQL Server Express 2005 running on the server, or are there additional steps required (such as somehow registering this new database on the server, etc)?

4) What is the best way to handle the test (on my PC) and production (on the server) databases, once I get that far?  What I'm asking is, once I have a production database established on the server, I assume that publishing an update from my PC would overwrite the production database, which I don't want to do.  Again, this is assuming that the database would be located in the App_Data folder.

5) Does anyone have any suggestions on tools for administering SQL Server databases?  We use Oracle's SQL Developer at work, which provides a nice GUI front-end for all our database work.

6) What is the best way to load data into SQL Server?  When I used Access in the past, I just imported a text file.  For Oracle I'll create insert statements for small loads; the DBA will load larger data files with the Oracle data loader.

I apologize for asking so many questions, but even though I've been a developer for years, this is my first attempt to do any kind of DBA work.  As I mentioned, it's also my first attempt at using SQL Server, and I just want to get things set up correctly to start with so that I don't have to make changes down the road.

Thanks for your help!

I will try to answer as many as I can, and may be somone else will have better answers.

jwelsh:

1) When I created my first (and only) database on my PC, I did it through VS using 'Add New Item', 'SQL Server Database'.  When I did this, VS told me that I should place it in the 'App_Data' folder, which I did.  Is this the correct way to set up a database for my ASP.NET application, and is the App_Data folder the best place to store it (on both my PC and the server)?

App_Data folder is to keep portable datafiles. For example, Access database, XML, .mdf files etc. If you are using Access or XML in App_Data folder, then you application is completely portable (I believe) but if you are using database files of type .mdf, you need to at least have SQL Server express edition installed on the server. And since you already have it, you should be ok. By portable I mean that when you publish your website, you don't have to worry about porting data also. It goes with your application.

jwelsh:

2) This may be redundant, depending on the answer to number one, but if I create a database that will be used by multiple .NET applications, where would be the best place to locate that database (on both my PC and the server)?

If this can be the case, I will suggest not to put datafiles in App_Data folder and maintain the database independently on the SQL Server.

jwelsh:

3) When I publish my application to my server, will this handle all the SQL Server details, since I already have SQL Server Express 2005 running on the server, or are there additional steps required (such as somehow registering this new database on the server, etc)?

Answer similar to 1.

4) What is the best way to handle the test (on my PC) and production (on the server) databases, once I get that far?  What I'm asking is, once I have a production database established on the server, I assume that publishing an update from my PC would overwrite the production database, which I don't want to do.  Again, this is assuming that the database would be located in the App_Data folder.

I am not sure about that. I would think it will give you some options to overwrite or not.

5) Does anyone have any suggestions on tools for administering SQL Server databases?  We use Oracle's SQL Developer at work, which provides a nice GUI front-end for all our database work.

SQL Srever Management Studio should work for you. 

jwelsh:

6) What is the best way to load data into SQL Server?  When I used Access in the past, I just imported a text file.  For Oracle I'll create insert statements for small loads; the DBA will load larger data files with the Oracle data loader.

That depends from where you are uploading. Express editions do not have Data Transformation Services (responsible for Importing/Exporting data).

Thanks for the response, bullpit!  This is the information I was looking for.  I've followed your advice, and I believe I have everything set up the way it should be.

Thanks again!