Saturday, April 5, 2008

No one seems to know

I've been doing some research lately into the different versions of SQL server into which you can store Sharepoint V3 data.


Firstly, you can install it into SQL 2000 but as we all know this version is old hat and not really recommended going forward, but if you have SBS 2003 ( as opposed to SBS 2003 R2 ) then you can still use the version of SQL 2000 that came with it to store Sharepoint V3 data.


Now the recommended method of storing Sharepoint V3 data is in an SQL 2005 server. Right, if you install Sharepoint V3 you are going to get WMSDE database, which from my understanding is a cut down version of SQL 2005. Why do I say that? Well, when WMSDE installs it ALWAYS installs to C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005 enough said. So you can also install Sharepoint v3 into WMSDE, SQL 2005 Express, SQL 2005 Workgroup, SQL 2005 Standard and SQL 2005 Enterprise.


I understand the limitations that have been imposed on each product (which can be found here). Aside from these limitation of database size, memory and processor usage what I don't understand is whether there is really any difference in performance or otherwise between the different versions of SQL 2005. Interestingly, I can't find anything or anyone who can tell me!


Why is this an issue? At the high end of the market where there are plenty of dollars it doesn't matter but at the small business end there is a HUGE difference. Let me illustrate. If you use the inbuilt WMSDE that comes with Sharepoint V3 you don't pay anything and you get no database size limitation. The major limitation with WMSDE is that it ALWAYS installs onto the C: drive. So, if you don't want that you need to install Sharepoint V3 onto SQL 2005 Express which can be located on the drive and directory of your choice. But, SQL 2005 Express (which is still free) has a limitation of a 1GB of RAM usage and a 4GB database size. For most small networks this is probably ok but let's say you have a bigger rollout planned. So, to overcome this you must upgrade to SQL 2005 workgroup. Now you are going to have to go out and buy the server software BUT if you are on SBS 2003R2 Premium you'll have it included. Again, you can install SQL 2005 workgroup data onto any drive you want but it still does have limitations. SQL 2005 Workgroup has a 3GB RAM limit but no database size limit. The great thing on SBS 2003R2 Premium is that you don't need any additional client licenses.


What now happens if you want to put Sharepoint V3 on a member server? You can still use WMSDE and SQL 2005 Express for free but if you want to use SQL 2005 workgroup you are going to have to buy the server software as you can't install the SQL software that comes with SBS 2003R2 Premium on any other server but the SBS server. Although you do have to have to pay for the SQL 2005 Workgroup server software if you are using SBS 2003R2 you automatically have the client rights for this second server, again saving you money. But let's say that you want to use an SQL version without limitations, then you need to upgrade to SQL 2005 Standard but when you do this you'll also need to purchase client licenses for each network user who is going to access the server or a per processor license. Don't forget that the SQL 2005 Standard edition software is pretty expensive. So as a business at the smaller end of town who wants to implement Sharepoint V3 there are some interesting questions.


Putting all that aside, I want to know what is the fundamental performance difference between WMSDE, SQL 2005 Express, SQL 2005 Workgroup and SQL 2005 Standard. I understand all the memory and database limitation but at its most fundamental level can anyone tell me whether there is any REAL difference? Now I can appreciate there is between WMSDE and all the other versions BUT is there really any difference between Express and Workgroup? What about workgroup and standard? Why so I pay so much more for standard just to get no RAM limitation. Surely there must be more to the higher price than that?


Interestingly, as I said before, I am yet to find anyone or anything that can give me hard evidence as to any differences between the versions of SQL apart from those imposed database and memory. I sort of get the impression that they are all the same product under the skin but simply artificially hobbled to create a range of versions for a range of prices.


Am I wrong? Can someone show me something that demonstrates the fundamental performance differences between the different versions of SQL 2005.