Please recognize that you will need assistance in performing this upgrade.  MS-Access has an upsize wizard that allows *some* databases to be upgraded to MSSQL, but that is not successful for the PC database.

 

1 - Do not go with SQL 2008.  Compatibilty issues have been identified and SQL 2005 works just fine.  Unless you have some sophisticated requirements, you should not pay for the reporting service.

 

2 - Mark Shipp mailto:mark@cognecy.com - mark@cognecy.com  is the only person I trust to do this.  He is good, he is efficient, and uhh, well, he's actually the ONLY person I KNOW who does this.  heh heh.  Hey, he's the only, the best, and that's good enough for me.  (Hey Mark, about that commission program we were discussing - lol).

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.

 

 

 

 

 

Hi Lelady,

 

Not that I would condone running any significant web based application on an Access db, but I'm just curious what limitations you found. Honestly, this is really small potatoes (no offence), so unless you had mad traffic adding to cart but not going to checkout, I'm really currious what limitations you found here.

 

I would expect that at this level the limitations would be with the server, not the application or the database. That is, as Access is a file, it can only do one transaction at a time, meanwhile it locks the file for a split second with a .ldb -- I've seen that get stuck on some servers and never release. Another common issue on older servers (< Win2K3) and depending somewhat on hosting control panels is that sites running Access on a shared surver can experience common issues when one of those sites has an issue with Access if they are not all "quaranteened" into their own application pools. Finally, every now and again an Access db just gets corrupted and needs to have a compact and repair run on it.

 

I'm just curious what you could describe about the limitations you hit with Access as this level.

 

Meanwhile you should absolutely pursue upsizing to MS SQL -- any version would be better than Access.

 So with MS SQL, does it need to be compacted & repaired religiously like Access? Or do I need to refresh it once in a while?

Thank you in advance,

An Access user (try to be an Ex-user 8-D),

Mario