Change to SQL Server

Hi.

We have an existing FileVista 3.1 and we'd like to change the Access db to SQL Server.

I ran the script to create the db, I added User rights so that it can connect and I changed the db connection in FileVista\App_Data\FileVista.config.

After doing this it still looks at the Access db.  So I restarted the site in IIS and no change.  So I went into IIS and changed the DB settings (which I shouldn't have to do) and now I can't login so maybe it is looking at the SQL Server db.

OK, good, but I have no data in the SQL Server db so I can never login without at least one User.

Is there a better way to do this?

Thank you.

SQL Server 2005, IIS7, WinServer2008

Yes, there is an easy way to change your database to SQL Server, which is, using the configuration wizard.

1. Run the sql script to create the DB in SQL Server.

2. Set value of IsConfigurationComplete key to "0" so that you can run configuration.aspx.

3. In step 2 of the configuration wizard choose SQL Server as your database and enter the connection information. Then finish the wizard by choosing the name of the administrator and other settings.

You may need to restart application by saving web.config so that it recognizes the changes in FileVista.config.

Thank you, Cem, as always. :)

I also had to add db_datareader and db_datawriter access to the

NT AUTHORITY\NETWORK SERVICE

user.

And now it's live!

I hade no problem moving to SQL Server from access but all the user and group data was not migrated. 

Can i simply copy the tables from Access to SQL Server or is there anything special I need to think about when moving the data to SQL Server?

The above solution is only for changing the configured connection for empty database. Yes, you can copy the tables for migrating data but please make sure the field types match the ones as in created via the sql script.