Time to move to SQL

Okay, my ears start twitching when someone mentions “Database” in the forum (ha ha).  Thank you for the accolade Greg .. much appreciated (your commission is in the mail didn't you get it already? LoL).

Seriously though, Greg offers good advice in that you should not attempt to use the upsizing wizards with a ProductCart DB.  I have seen many people try and inevitably it always ends up in disaster and they end up calling me in the end anyway to clean up and repair their DB. 

Additionally - although it can be done, and as advise from a ProductCart SQL Database guru (that would be me), I do not currently recommend SQL2008 "yet".   I say that with some reservation as I have seen a few stores work fine on it but I see a much larger percentage have some minor issues with compatibility.  It really depends on how the hosting/datacenter company has set that server up, how much control they give you over settings, and how well it is managed.  SQL2005 is hands down the best DB server architecture Micro$oft has put out (after a few service packs anyway).  I have no doubt that it will be succeeded by SQL2008 in time.  Not to worry though, the SQL2005 architecture has many more years of life in it. 

Additional info regarding Databases (for those that just cannot get enough reading in):

If you are at all interested (watch out when I get started typing it always turns into a novel) ...  Here are the summarized details on 2005 vs 2008 compared with older versions and the ProductCart DB: 

The ProductCart SQL database as you know has grown over the years from a DB with 60-80 tables to over 180 tables currently in the standard version (without add-ons).  If we go back in time before SQL2005 to when people were stuck on SQL7 and at that time, the new SQL2000, the database architecture contained a lot of 'text' and 'image' datatypes.  The only way to encompass large amounts of text for descriptions and such was to use the [ntext] datatype.  This was the only option and it works very well (you will find ntext datatypes all over the ProductCart SQL DB) - however, in the current world it is also part of the problem.  Why, well it comes down to sorting and grouping data and just overall speeding things up ... you cannot sort group, or search on ‘text’ or ‘image’ datatypes (e.g. ntext).

A few SQL2005 service packs ago, Micro$oft made the announcement that it wanted to turn off support for all ‘text’ and ‘image’ datatypes and released an upgrade that introduced new replacements:  instead of ‘ntext’ and ‘text’, we can now use ‘nvarchar(MAX)’ and ‘varchar(MAX)’.  There were others introduced but this is the most critical as it relates to ProductCart.  In essence, "ntext" was old school now while ‘nvarchar(MAX)’ is the state-of-the-art “new school".  

Consequently, the use of ntext in SQL2005 and 2008 is also ProductCart's Achilles heel as it keeps the DB (from a DBA's perspective) running slower than previous versions of SQL (especially on long queries and scripts … recall you cannot sort, group, or search on text and image datatypes).  I have done some tests privately on my own demo store replacing all the offending datatypes and not only do the speed benchmarks go through the roof but the overall average database size is reduced as well.  Sound great right?  Well, hold on a moment…

So here is the Question I am sure you are begging to ask: 

Why doesn't Early Impact just change all the ntext fields to nvarchar(MAX)? 

Well that is a bit easier to say than do when you consider that ProductCart has literally thousands of installations all over the world and believe it or not there are a good number of users still using SQL2000 and even SQL7 (NOTE:  the nvarchar(MAX) datatype is not a part of or recognized by SQL2000 or previous versions).  So, in order to keep the database backwards compatible and upgradeable, there is really no other easy choice right now but to keep the datatype structure the way it is.  VERY IMORTANT:  this is not exclusive to Early Impact ... there are literally thousands of database applications out there that are facing the same challenges.

Now with SQL2008, Micro$oft has edged a bit closer to what they threatened to do in SQL2005 and that is “turning off all support for text and image datatypes”.  This is where the quirkiness in SQL2008 as it relates to ProductCart comes in.  The only way to ‘really’ get it to work well, on 2008 is to run your database in SQL2005 compatibility mode (say what???).

I do not know about you but if it takes compatibility mode to handle text and image datatypes properly, and SQL2005 is still available, and will be for many years to come, and SQL2005 is tried, true and tested ... then I choose to stick with putting my ProductCart Database on SQL2005.