Today, I am going to go over 3 things that I encountered this week with SQL Server Express for a friend. At his job, they found a rogue SharePoint server that a developer had placed into production. Sadly, the only way my friend found about it was when the developer had run into an issue. They had hit the limit of database sizes with SQL Server Express and had this error:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
So I did a check and sure enough they had SQL Server Express installed, which has these limitations which are shown here:
- Limited to one CPU
- Limited to 1GB of RAM
- Database size limited to 4GB
- Does not offer database mirroring
- Does not offer log shipping
- Does not allow merge publication
- Does not offer Oracle replication
- Does not provide the Database Tuning Advisor, SQL Agent or SQL Profiler
- Does not offer SQL Server Analysis Services or Advanced Analytics
What was weird is that I know that this server already had SQL Server 2008R2 Enterprise Edition when they originally installed SharePoint 2010, because it was a part of their base build. (Then I checked in Add/Remove programs and sure enough it was installed before SharePoint) Then I went into research mode and I saw many reports of people with the same problem. The issue is that when you install SharePoint with the “Standalone” option (Which I figured was to allow you to have both SQL server and SharePoint components on one server) but apparently this option is only just for people testing SharePoint. It will install a separate version of SQL Express and it does not recognize any other versions of SQL Server that is already installed.
It turns out that this developer was using SharePoint for over 6 months in a full production environment but was only using SQL Server Express!!
I found this blog post which had a solution, it says to run the SQL upgrade with a special upgrade switch
Great I thought to myself and got excited at the simple fix, but then I found out that this only works with SQL 2005!
At first I looked at this with a SQL mindset, which I thought this was going to be a pain to deal with and my initial thoughts were that I would have to perform the following steps:
- Offline and move the databases (Detailed instructions here)
- Remove SQL Server 2008 Express
- Upgrade the Database to either SQL Server 2008r2 Express or a “full” version of SQL Server (Standard, Enterprise, etc)
- Then attach the database back
Then I went out and checked for another way to perform this process, which is even easier:
SQL Server 2008 and later, already has an upgrade method in place. Launch the SQL Server Installation Center (64-bit) from Configuration tools:
Then go into Maintenance:
Then go through the Wizard until you can select your instance and upgrade it:
But I was not so lucky, I had more issues to deal with and I will share those with you tomorrow