Upgrading to SQL 2005

[mfreeze]

I am upgrading my SQL servers to SQL 2005. The scenario I am following is:

1. Install a new SQL server
2. Backup SQL 2000 database
3. Restore backup to SQL 2005 server

My questions are:

1. After restoring an Able Commerce 7.0 database that was created on SQL 2000, can I now run it in 2005 native mode or do I need to run in compatibility mode?
2. When I upgrade Able Commerce and an upgrade of the database is necessary, do I choose SQL 2000 or SQL 2005 as the platform for the upgrade?

I moved one database and set the new database to compatibility mode before the restore but am thinking that I should leave it set to native mode then do the restore. I read somewhere that the tables will be restored in 2005 format.

I have a number of AC 7.0 databases I need to move from SQL 2000 to 2005 and need to know the correct process to ensure no errors with the restored database or with future upgrades (like 7.02).

[mfreeze]

Since I didn't get a response and I needed to test this, I took a development site SQL 2000 database backup and restored it to SQL 2005 and left the mode set to native mode. I then upgraded the site to 7.02 and specified SQL 2005 when asked. Then, I tried to go to the admin to perform the 7.01 post upgrade tasks and got the following message:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Invalid column name 'OrderNumber'.
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 column name 'OrderNumber'.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

<%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:


[SqlException (0x80131904): Invalid column name 'OrderNumber'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) +181
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +82
CommerceBuilder.Data.Database.ExecuteReader(DbCommand command) +164
CommerceBuilder.Orders.OrderDataSource.LoadForStore(Int32 maximumRows, Int32 startRowIndex, String sortExpression) +848
Admin_Dashboard_OrderSummary.BindDataSources() +60
Admin_Dashboard_OrderSummary.Page_Load(Object sender, EventArgs e) +38
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Control.LoadRecursive() +141
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

I also get the following message when I click on a product category:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Invalid column name 'TaxRate'.
Invalid column name 'TaxAmount'.
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 column name 'TaxRate'.
Invalid column name 'TaxAmount'.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

<%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:


[SqlException (0x80131904): Invalid column name 'TaxRate'.
Invalid column name 'TaxAmount'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) +181
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +82
CommerceBuilder.Data.Database.ExecuteReader(DbCommand command) +164
CommerceBuilder.Orders.BasketItemDataSource.LoadForBasket(Int32 basketId, Int32 maximumRows, Int32 startRowIndex, String sortExpression) +905
CommerceBuilder.Orders.Basket.get_Items() +51
CommerceBuilder.Orders.Basket.Package(Boolean resetGiftOptions, Boolean doCombine) +113
CommerceBuilder.Orders.Basket.Package(Boolean resetGiftOptions) +13
ConLib_MiniBasket.Page_PreRender(Object sender, EventArgs e) +45
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnPreRender(EventArgs e) +8679510
System.Web.UI.Control.PreRenderRecursiveInternal() +80
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal() +42
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053



From all this, I assume that during any upgrade I will need to specify SQL 2000 as the database platform?

Is there a script to upgrade SQL 2000 AC database to SQL 2005 format that I could run after restoring the DB to a 2005 server?

I am restoring the site back now but need to know how to proceed. As stated in my first post, I need to move SQL 2000 databases to SQL 2005 and still be able to perform upgrades.

[Logan Rhodehamel]

As you suspected, you have to continue to select SQL 2000 as the platform since that is what you originally created the database in. I have had in mind to change that piece of code so we could try to automatically detect what kind of structure is in place. The scenario you describe is too confusing.

I am not sure about conversion. It would be a major undertaking to write a script to convert in place. The bottom line is that the 2000 and 2005 databases use different kinds of structures.... nvarchar max is used in 2005, and triggers must be used for certain kinds of referential constraints in 2000. However a conversion might be accomplished by something like this:

1) create a new database in SQL server
2) run the database creation script (install/AbleCommerce.sql) for your target version
3) export the from the original database to a script using a tool like aminsert
4) import the script into the target database

Step 4 - you probably have to turn off constraints before the import, and turn them back on after. Once data is moved over into the new structure, you could point the install to the target database.

[AbleMods]

Could Dataport be made such that it dumps an entire store in 7.0 format?

Then you could unload the SQL 2000, build a new store with a 2005 db and use Dataport to load it all back up?

Or is there a lot more to it than that? I probably just way over-simplified things so I apologize if I did....

[mfreeze]

Since I have 30 to 40 AC 7.0 sites at this time and more to convert, It would be a massive task to keep track of which ones were originally installed on SQL 2000 and which ones were installed on SQL 2005. Even Joe's suggestion of exporting the store then importing into a new store would encompass a lot of work with this number of sites but could be workable if dataport could sense the SQL version. It would be better than keeping a document listing which sites are 2000 and which are 2005 then consulting it for every upgrade.

I need a way to move these databases to a SQL 2005 server and have the ability to specify 2005 when performing an upgrade. I will eventually be decomissioning my 2000 servers so leaving these sites on their own server is not an option SQL 2008 is now general release and Microsoft will at some point cease supporting 2000.

Without a way to upgrade these databases, what will happen with subsequent SQL versions? This could become a maintenance nightmare.

[Logan Rhodehamel]

The structure of the databases are forward compatible. The reason we have to ask which database type is employed is because SQL 2005 had a better (more efficient) method of handling string data with the varchar(max) concept.

AC databases that were originally created for SQL 2000 can run perfectly well on SQL 2005. From your description of the problem, I will log a bug to have the upgrade script modified. If we can discover what database type was used to originally create the database, we could remove the question from the AC upgrade page completely.

[mfreeze]

Thanks Logan. It was keeping track of the initial SQL database version that was a major problem for me.

I have one more small question. When I create the new database on SQL 2005, do I need to specify compatability mode instead of native mode? In other words do these databases need to run in SQL 2000 compatability mode?

[Logan Rhodehamel]

No, you do not have to set compatibility mode for 2005 databases.