HELP- unsuccessfully moving to new server

[Brewhaus]

Our old (and I do mean old) server is starting to grumble, so we are trying to move to a new machine with upgraded hardware and OS.

AC Version- 7.0.7

Our old system:
Windows Server 2003 SBS
SQL 2000
IIS6

Our new system:
Windows Server 2008 R2
SQL 2008
IIS7.5

We have the new server functioning, and can successfully pull up a basic HTML site. We copied the current web folder from W2003, and DB file from SQL 2000, and placed them on the W2008 box with SQL 2008. Permissions appear to be correct (based on my previous notes and checking the current settings on the W2003 box), and I have tried both .NET2 and .NET4, but when going to the site we get a 500 server error (under .NET2 we get a Runtime Error). I have even pulled the license file so that the program should tell me that the license key is missing, but it is not even doing that. Does anyone have any thoughts? Is there anything special that we need to do on a W2008 / IIS 7.5 install that I may not be aware of? Will the SQL 2000 file work on SQL 2008 (and if not, how do we upgrade it?)?

I am sure that by now someone here has migrated from W2K3 to W2K8, so hopefully they can shed some light on this.

[Brewhaus]

I turned off custom errors, and under .NET2.0 I get the following:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Failed to decrypt using provider 'DataProtectionConfigurationProvider'. Error message from the provider: Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B)

Source Error:


Line 1: <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
Line 2: <EncryptedData>
Line 3: <CipherData>
Line 4: <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAav+UGUr5QUKhMJ5eyaKW8gQAAAACAAAAAAADZgAAqAAAABAAAAC4bUogHzdrTX1kus4xpyB0AAAAAASAAACgAAAAEAAAAO+0CcnFYmemP3m99B1Nh6nYAQAADPhyXfNFPsni1iqH4vJQCn7zi/0xLnAa6QuE7R1BrgcHQ0CrObcXvBzsKbSMhI8AMrfXPiWtZT0yuQEb+6vV4IhQqyH92i2Mh2lh19BI8fOZTbc3SELz/2+erhVHxLg4zVl/1jVfCfcBtRCETRZaeVBrJiX0GZguXJ8xmjMvoVW4Yg+9k5sZDWKjbkzFd0GPtSGqBmCQmjIaB2mwG3W4oKczsRt9+iHCEbN6EBwHIKKJT7Ze/mQ6O4Qf0BSPNhFOdEfvD9We0WLApfpg7Idz0MstP9sIX71k09cWNT2KqNDGrgJXVDyBoH0QDfugcu806+RB4ElNVuVbQhDxHJMaV3xVFaeB6R1pvXk3mE4QOb7o3lQDf+GnsqNx9BAxhqeW4Gu7HhuNnsa1dwMYbhQgfTJiaiU0SL8v1HoimD0b1jsI3t1rGt7Fs6hEeDAEKHSCv7d0/7t8KYkjfy8aqnC+h6A3EA48CAY2E+jy/Kc4Y1xo2PeTuNHMBvWNFFyOjzn79z2yUoVxgaIpjrp2+4tKyIngA3h8hvZn/TNoUW7dBIbQiWxTd9xOWqxrvPJPUQaHSTIDpXBk3avi3QeNNFAcQm9OG5nRP8WvH2TikLrDkExXdDS3TKM1KxQAAABGMOPPs4v2Hf23oFW49cggRRCySQ==</CipherValue>


Source File: E:\Websites\Brewhaus\App_Data\database.config Line: 2



Under .NET4.0 it is:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Failed to decrypt using provider 'DataProtectionConfigurationProvider'. Error message from the provider: Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B)

Source Error:


Line 1: <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
Line 2: <EncryptedData>
Line 3: <CipherData>
Line 4: <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAav+UGUr5QUKhMJ5eyaKW8gQAAAACAAAAAAADZgAAqAAAABAAAAC4bUogHzdrTX1kus4xpyB0AAAAAASAAACgAAAAEAAAAO+0CcnFYmemP3m99B1Nh6nYAQAADPhyXfNFPsni1iqH4vJQCn7zi/0xLnAa6QuE7R1BrgcHQ0CrObcXvBzsKbSMhI8AMrfXPiWtZT0yuQEb+6vV4IhQqyH92i2Mh2lh19BI8fOZTbc3SELz/2+erhVHxLg4zVl/1jVfCfcBtRCETRZaeVBrJiX0GZguXJ8xmjMvoVW4Yg+9k5sZDWKjbkzFd0GPtSGqBmCQmjIaB2mwG3W4oKczsRt9+iHCEbN6EBwHIKKJT7Ze/mQ6O4Qf0BSPNhFOdEfvD9We0WLApfpg7Idz0MstP9sIX71k09cWNT2KqNDGrgJXVDyBoH0QDfugcu806+RB4ElNVuVbQhDxHJMaV3xVFaeB6R1pvXk3mE4QOb7o3lQDf+GnsqNx9BAxhqeW4Gu7HhuNnsa1dwMYbhQgfTJiaiU0SL8v1HoimD0b1jsI3t1rGt7Fs6hEeDAEKHSCv7d0/7t8KYkjfy8aqnC+h6A3EA48CAY2E+jy/Kc4Y1xo2PeTuNHMBvWNFFyOjzn79z2yUoVxgaIpjrp2+4tKyIngA3h8hvZn/TNoUW7dBIbQiWxTd9xOWqxrvPJPUQaHSTIDpXBk3avi3QeNNFAcQm9OG5nRP8WvH2TikLrDkExXdDS3TKM1KxQAAABGMOPPs4v2Hf23oFW49cggRRCySQ==</CipherValue>


Source File: E:\Websites\Brewhaus\App_Data\database.config Line: 2

[jmestep]

If you don't have a machine key defined in your web.config file and you move the site to a different server, you can get that error because every server has it's on machine key. If you have backups of the encryption key from the admin, you should be OK if you re-enter them. If you can't do that, then take out the text in your app_data encryption.config file and replace with this:
<encryption />

Doing that would probably get you past the error, but your credit card data when you click view account details in the admin will be gibberish and you might have to re configure your payment and shipping gateways.

[Brewhaus]

Are you referring to the encryption key from Administration > Configure > Security > Encryption Key? If so, I created the backup files, but how do I use them when I cannot get into the AC admin menu? I apologize if this is a dumb question, but I have never run into this before. Of course, I may not have even been using encryption when I moved servers previously, as that was several years ago.

[jmestep]

Sorry, I was referring to the encryption keys. You will need to change the encryption.config contents to this <encryption />. Then if this is the problem, you should be able to go to the admin and re-enter the encryption keys. You were probably using encryption because of PCI compliance.

[Brewhaus]

This changes the error slightly:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Failed to decrypt using provider 'DataProtectionConfigurationProvider'. Error message from the provider: Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B)

Source Error:


Line 1: <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
Line 2: <EncryptedData>
Line 3: <CipherData>
Line 4: <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAav+UGUr5QUKhMJ5eyaKW8gQAAAACAAAAAAADZgAAqAAAABAAAAC4bUogHzdrTX1kus4xpyB0AAAAAASAAACgAAAAEAAAAO+0CcnFYmemP3m99B1Nh6nYAQAADPhyXfNFPsni1iqH4vJQCn7zi/0xLnAa6QuE7R1BrgcHQ0CrObcXvBzsKbSMhI8AMrfXPiWtZT0yuQEb+6vV4IhQqyH92i2Mh2lh19BI8fOZTbc3SELz/2+erhVHxLg4zVl/1jVfCfcBtRCETRZaeVBrJiX0GZguXJ8xmjMvoVW4Yg+9k5sZDWKjbkzFd0GPtSGqBmCQmjIaB2mwG3W4oKczsRt9+iHCEbN6EBwHIKKJT7Ze/mQ6O4Qf0BSPNhFOdEfvD9We0WLApfpg7Idz0MstP9sIX71k09cWNT2KqNDGrgJXVDyBoH0QDfugcu806+RB4ElNVuVbQhDxHJMaV3xVFaeB6R1pvXk3mE4QOb7o3lQDf+GnsqNx9BAxhqeW4Gu7HhuNnsa1dwMYbhQgfTJiaiU0SL8v1HoimD0b1jsI3t1rGt7Fs6hEeDAEKHSCv7d0/7t8KYkjfy8aqnC+h6A3EA48CAY2E+jy/Kc4Y1xo2PeTuNHMBvWNFFyOjzn79z2yUoVxgaIpjrp2+4tKyIngA3h8hvZn/TNoUW7dBIbQiWxTd9xOWqxrvPJPUQaHSTIDpXBk3avi3QeNNFAcQm9OG5nRP8WvH2TikLrDkExXdDS3TKM1KxQAAABGMOPPs4v2Hf23oFW49cggRRCySQ==</CipherValue>


Source File: E:\Websites\Brewhaus\App_Data\database.config Line: 2


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272


Any more thoughts? Is it an issue to be running a copy of the existing DB (from SQL 2000) on SQL 2008?

[Katie]

Hi Rick,

I'm guessing that you're going to have a hard time getting the installation to work with the new server requirements. I think you would have better luck trying to install a fresh copy of AbleCommerce on the new server. Make sure to use the same version and build that your original installation is running. Once you know that AC works on the new server, then you can move over just the files that are needed or customized.

We have a FAQ at the help site to help with the process:

http://help.ablecommerce.com/faqs/ablec ... ation_.htm

Towards the bottom, you'll also find some special instructions for the database changing from SQL 2000 to 2005 (2008)

Please let us know if you're able to get it working.

Thanks,
Katie

[Brewhaus]

Hi Katie,

I had thought of doing this, but the problem is that the upgrade software does not contain the files necessary to install from scratch, so I had to just copy the AC folder from the old server. So, I guess here are my questions:

1- Can I obtain the full Install folder for my build so that I can install from scratch? (version 7.0.7, Build 14600)
2- If I install from scratch, should I start with a 'base' DB file and then import the information to the tables instead of copying my old DB, especially given that the old system ran SQL 2000 and the new system is on SQL 2008?

Rick

[mikek]

It seems that you still have the database connection string encrypted. Replace the database connection string in database.config with a plain text
connection string. You can then encrypt the connection string again from the Admin interface.

[Katie]

Hi Rick

I still think you'll have better luck installing AC to begin with. I went ahead and attached the file to your order, so you can download the full build 7.0.7 14600.

As far as moving the data? You should be able to restore the SQL 2000 to SQL 2008 without too much trouble. It seems that SQL server will make the transition for you, and it will retain the correct properties on the database tables.

Good Luck!
Katie

[Brewhaus]

Thank you, Katie. I will try to get at this today. But, one question- on the User Guide it says that to move the data from one version of SQL to another you need to export and import the data. Is this necessary, or can we just do a Backup and Restore? The latter seems easier (I have not been able to figure out how to get the Import to work for me), but given that we have had a DB problem for years, I wonder if importing the data into a new DB may repair it.

[Katie]

Can you post a link to the User Guide where it says to export and import? That seems wrong to me.

Thanks,
Katie

[Brewhaus]

Hi Katie,

I tried to post the link earlier, but it showed up as http://help.ablecommerce.com/mergedProj ... notice.htm. I think that I have it now- http://help.ablecommerce.com/mergedProj ... ection.htm

Please let me know my best plan of attack. It would be nice to have a DB file that ran the anonymous user cleanup so that we could get our file size down, but I will defer to your advice.

Rick

[Katie]

Hi Rick,

Yep, that's a really old page and the export/import information is incorrect. Restoring the database from a backup is the best route.

If it helps, we have this page -
http://help.ablecommerce.com/upgrades/a ... enance.htm

There are some scripts on this page that might help reduce the database size before you restore.

Hope this helps,
Katie

[Brewhaus]

That's great- Backup and Restore is much easier.

And, thank you for the link on clearing up the anonymous user cleanup routine. I believe that our original DB was 7.0.0, but we never knew about the patch to fix the constraints, so hopefully that will fix the issue. We will just likely need to do it in steps because of the number of records (1.3 million anonymous users and 12000 affiliate anonymous users). Would this reduce the DB size dramatically, and would this number be the cause of slow website response?

Rick

[Katie]

Yes! Even after you upgraded from the old version, it's likely that the anonymous user records were unable to be deleted as part of the normal maintenance cleanup. The scripts should get the db size back to normal, and then the auto-maintenance routines should work from there.

Thanks,
Katie

[Brewhaus]

I installed the patch and ran it to change the constraints, but when I try to manually delete anonymous users I still get an error in the error log:

Exception of type 'System.Web.HttpUnhandledException' was thrown.; DELETE statement conflicted with COLUMN REFERENCE constraint 'ac_Users_ac_Orders_FK1'. The conflict occurred in database 'HotSauceDepot', table 'ac_Orders', column 'UserId'. The statement has been terminated.

Even after running the SQL query directly from the DB (which says that it completed successfully) we still get the same error when we try to delete the anonymous users. It just appears that the constraints will not change. Any thoughts?

I can successfully delete affliated anonymous users via the Admin menu.


Rick

[Brewhaus]

I think that I have found at least part of the problem with the new install- I cannot connect to the DB. When running the install/default.aspx file and I enter the DB connection information it comes back saying that the DB connection could not be opened. I have never seen this before, but have never worked with SQL2008. Any thoughts?

As for the existing DB, we need to find a way to correct the constraints or the file will continue to build until it really causes us problems, I imagine.

Rick

[Brewhaus]

The new installation is finally working, however, we still cannot remove the anonymous users. I had hoped that maybe, just maybe, when the DB was moved to a new system and upgraded version of SQL that we may be able to fix the constraints and delete the anonymous users. No such luck. Is there any way (or anyone here) to fix the Users table? Would there be a way to delete the ac_Users table and then import the information back into the DB? I am grasping at straws here, but with over 1.3 million anonymous user entries that will not delete, and a 1.5GB data file, we need to do something.

Rick

[david-ebt]

Rick,

Do you have QUERY access to the database? If so, you can run this query. Make sure you have a good backup first. Also check the LastActivityDate to make sure you're okay deleting users before the first of the year.

Code: Select all

begin tran

delete dd
from ac_users dd
join (
	select top 100 u.UserId, o.OrderId
	from ac_users u
	left join ac_Baskets b on u.UserId = b.UserId
	left join ac_orders o on u.UserId = o.UserId
	left join ac_Wishlists w on u.UserId = w.UserId
	where u.IsAnonymous = 1 and o.OrderId is null and b.BasketId is null and w.WishlistId is null
	and u.LastActivityDate < '01/01/2013'
) td on dd.UserId = td.UserId

rollback

The first time you run this it will try to delete 100 anonymous users who don't have a basket, order or wishlist. This should let you delete anonymous users with no constraints. Note that this is in a transaction and is set to rollback so no actual changes will take place.

If that works, then you can change the ROLLBACK to COMMIT and run it again. If that works, then you can change the "TOP 100" to something like "TOP 10000" and try it again. The reason for the TOP number is to limit the locking impact on your database. If that works, then just keep running it until you get 0 rows affected.

This won't delete any anonymous users who have an order, an open basket or a wishlist. But it should reduce the number of anonymous users in your database.

[Brewhaus]

The query seems to be doing the same thing as the code that we were given in another thread (viewtopic.php?f=42&t=17138&p=73436#p73208)- it just keeps saying that it is Debugging query, but it never does anything. The other code we left running over 12 hours before we finally gave up.

[david-ebt]

What happens when you just run the inner select:

Code: Select all

   select top 100 u.UserId, o.OrderId
   from ac_users u
   left join ac_Baskets b on u.UserId = b.UserId
   left join ac_orders o on u.UserId = o.UserId
   left join ac_Wishlists w on u.UserId = w.UserId
   where u.IsAnonymous = 1 and o.OrderId is null and b.BasketId is null and w.WishlistId is null
   and u.LastActivityDate < '01/01/2013'

Does it return values?

[Brewhaus]

45 minutes and counting... same thing...

[david-ebt]

What user owns the AC tables? Are they "dbo" or some other user?

Can you run any query against the database? Like "select top 1 * from ac_Categories"?

Your screen indicates SSMS is in debug mode. Can you do a simple F5 execute instead of an ALT-F5 debug?

[Brewhaus]

This is odd, but I retried the script from http://help.ablecommerce.com/upgrades/a ... enance.htm and it apparently worked, as I was then able to run the maintenance routine from the Admin menu and it deleted the old users, so I assume that the constraints were corrected. However, it did not reduce the file size at all- it is still at 1.5GB. Is this file size normal??