Help! Problems moving site

[Will]

I hope someone can help me with this. This is the first time I've done this and am a total loss at this point.

We've been developing our AC7 site on our development server and now need to copy it to our production server.

Here's the steps I've done so far:

1. Got the new license key

2. Created a new empty database

3. Completed a new installation of AC7 with the new license key and the new empty database. New target installation is working.

4. Backed up development database -- I did this using our hosting provider's browser-based backup tool (MyLittleBackup).

5. Copied all files from development location to production location except for:

/web.config
/app_data/commercebuilder.lic
/app_data/database.config
/app_data/encryption.config
/app_data/ablecommerce.config
/app_data/AbleCommerce.mdf
/app_data/AbleCommerce_log.ldf

6. Confirmed new installation on production is still working

7. Created a new database container on production. Created a new user for db. I did this with my shared hosting provider's Helm control panel interface.

8. Restored backup of the development database to the new db container on production using hosting provider's tool (MyLittleBackup).

9. Logged into AC7 admin and changed the connection string to point to the new database container, using the login info for the new user I created.

At this point, I get the following error:

Code: Select all

Server Error in '/' Application.
Invalid object name 'ac_Stores'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'ac_Stores'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Invalid object name 'ac_Stores'.]
   CommerceBuilder.Common.Token.get_Instance() +463
   CommerceBuilder.Stores.AuditEventDataSource.CountForStore() +9
   CommerceBuilder.Services.AbleCommerceHttpModule.Init(HttpApplication context) +230
   System.Web.HttpApplication.InitModulesCommon() +66
   System.Web.HttpApplication.InitInternal(HttpContext context, HttpApplicationState state, MethodInfo[] handlers) +1006
   System.Web.HttpApplicationFactory.GetNormalApplicationInstance(HttpContext context) +259
   System.Web.HttpApplicationFactory.GetApplicationInstance(HttpContext context) +114
   System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +350


Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

I have a copy of SQL Server Studio Express -- when I look at the table names of the new database, they all start with the user name for the development database. I can also see the development db user and the new user I created in the users list, but the development db user doesn't show up in the hosting provider's Helm control panel.

I don't know if that's even the issue, but it seems like the most obvious discrepancy I can find right now.

I dead in the water here. Does someone have some insight?

Thanks.

[jmestep]

Try giving the new user same permissions as the old. Or add the new user to your dev database with proper permissions, then try the restore.

I have had trouble in SQL Server 2005 when moving from dev to production if I haven't added the user to SQL security over all (I don't think you can do that in this case) before I added the user to the database.

Also, try to make an ODBC connection in your Helm control panel and see if you can get that to connect.

[Will]

I added the new user to my dev database and then modified the connection string to access the dev db as the new user.

The new user can access the database, but I then get the same error I was getting on the production db. The new user can't access the tables, I guess.

Helm doesn't let me change permissions. The user either has access to the db or not.

[AbleMods]

The security for the database is not stored in the database itself. It's stored in the SQL instance master.mdf table.

So when you created the initial development user and assigned it to the AC7 database, SQL noted that user had rights to the database.

When you move the database to another SQL instance, you must have already created the same SQL user as you used in your development SQL instance. If you don't the SQL engine cannot match what the database says as the db-owner against the available user accounts configured in the SQL instance.

To fix it, you usually you just delete the new DB since it doesn't work anyways. Create the correct user account in the new SQL instance identical in name and password to your development user instance, then create a new empty DB. Assign the new user account full DBOwner rights to the new Db.

Then restore your development DB backup to the new DB. You might need to manually edit the ac_StoreSettings table entries so accomodate the new URL, SSL enabled etc.

[Will]

When you move the database to another SQL instance, you must have already created the same SQL user as you used in your development SQL instance.

That's what I figured as well, but my Helm control panel wouldn't let me create a user with the same name and password.

I'm going to have to have my hosting provider create it for me it turns out. Not sure why, but that's what they say.

They also suggested changing the table ownership to dbo, which is also what Able recommended. That's beyond my skills and resources.

[AbleMods]

Yeah changing ownership to DBO is a good idea, but a mute point at this stage in your data move.

Moving SQL databases is rarely easily or painless. It doesn't help when ISP's have 17 different ways to present their site owner customers with a frontend to manage a SQL database. Some sort of standard would be quite helpful.

[sohaib]

The security for the database is not stored in the database itself. It's stored in the SQL instance master.mdf table.

So when you created the initial development user and assigned it to the AC7 database, SQL noted that user had rights to the database.

When you move the database to another SQL instance, you must have already created the same SQL user as you used in your development SQL instance. If you don't the SQL engine cannot match what the database says as the db-owner against the available user accounts configured in the SQL instance.

To fix it, you usually you just delete the new DB since it doesn't work anyways. Create the correct user account in the new SQL instance identical in name and password to your development user instance, then create a new empty DB. Assign the new user account full DBOwner rights to the new Db.

Then restore your development DB backup to the new DB. You might need to manually edit the ac_StoreSettings table entries so accommodate the new URL, SSL enabled etc.

This was quite informative.