June 2, 2015 at 1:38 pm
My company has an old SQL Server 2000 system. We are planning to move it to SQL Server 2014, but this is an expensive option especially when considering the CALs needed.
I am trying to determine if we could instead move to the 2012 Express version. Our database is small comparatively with the largest tables just over the 500,000 row count. The employees use and Access 2010 ADP project to interact with the DB and I have built a lot of stored procedures to do much of the work. There is no direct DB manipulation by the users, that is nothing creates tables, or indexes for example they simply read, update and write data.
Using things like DBCC, Performance Monitor and other tools it seems to me that our existing DB is barely using the capabilities of the 15 year old software and a new, full blown SQL Server instance would be overkill. So far the best answer I can come up with is a definite maybe.
Has anyone attempted such a migration/downgrade before? The obvious answer is to install Express and try but I am not sure I'll be given enough time and the other IT person is not ready to commit to such an experiment without some idea that it could be successful. Any thoughts, anecdotes or war stories would be appreciated.
June 4, 2015 at 2:28 am
From your given informations... I don't know why this should not work...
But you have to consider that an Express Edition has its limitations and also doesn't have a real support.
So if it is a business critical application (7x24) it might not be very helpful to migrate to an Express Edition.
Afaik there is a Migration Assistant which could be used to find out more about a possible migration...
Otherwise the only way to find out is... try it on an extra machine.
June 4, 2015 at 2:44 am
You're probably going to have to do it in a two step process. Install SQL 2008 R2, migrate the DB to there, change the compatibility level, then migrate again to SQL 2012.
This is, of course, after you've run the upgrade adviser and fixed all the stuff it will complain about. You need to migrate, tests complete application, fix everything that broke and only then upgrade the production database
Bear in mind that Express is limited to very small amounts of memory and limited CPU resources. It's not really suitable for lots of users.
You will have to try it out, if only to identify and fix anything that used to work in SQL 2000 and doesn't in newer versions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2015 at 8:44 am
Has anyone thought about the licensing ? I don't think SQL Express is by license, allowed to support multiple users, regardless of the MS Access intermediary... If someone has better info, please let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 4, 2015 at 10:32 am
The maximum number of concurrent users that can connect is 32,767 although it would certainly run out of resources long before that. So says an MSDN post.
I did see that there are licensing concerns and Microsoft has a download available with the terms. This however seems to only concern the situation where you are embedding Express within some other application. Numara's (BMC Software) Track IT has an embedded SQL Server Express 2005 where it stores and provides access to all of the customer's data related to help tickets, inventory and similar IT tracking purposes. We use this and can have a number of concurrent instances running and found no issue.
I am continuing to monitor resource usage with some stored procedures like sp_monitor and sp_spaceused and a couple of custom scripts I found. It still seems like we are not even beginning to strain our 15 year old system.
The current instance of SQLSERVR.EXE running seems to stay at 1.2GB of memory. I see that Express would limit that to 1GB, but the only likely result I see would be more page faults and heavier use of virtual memory but nothing that should adversely impact the users.
June 4, 2015 at 12:44 pm
sgmunson (6/4/2015)
I don't think SQL Express is by license, allowed to support multiple users, regardless of the MS Access intermediary...
You're thinking about MSDE, the free version of SQL 2000. Express does not have a connection limit
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2015 at 12:47 pm
jwashburn 18707 (6/4/2015)
but the only likely result I see would be more page faults and heavier use of virtual memory but nothing that should adversely impact the users.
Errr, no.
SQL Server doesn't use the page file and Express can only use 1GB of memory.
Page faults and more virtual memory usage would occur if, for example, you had 1GB of physical memory and an application that 'consumed' 2GB of memory. 1GB would be physical, 1GB backed by the page file.
SQL Express can use only 1GB of memory, total.
What you'll see, unless the entire working portion of the database fits into that 1GB, is more IO usage, less effective usage of the data cache, more compilation due to plans being thrown out of cache more often.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2015 at 6:08 pm
GilaMonster (6/4/2015)
sgmunson (6/4/2015)
I don't think SQL Express is by license, allowed to support multiple users, regardless of the MS Access intermediary...You're thinking about MSDE, the free version of SQL 2000. Express does not have a connection limit
Actually, I didn't have access to that version's Developer Edition, and have always had to pay the $50 for each of the following versions DE DVD: 2005, 2008R2, and 2012. I had always thought that DE was $50 and that SQL Express was free. Of course, with a 1 GB RAM limitation, it can only serve the simpler needs. As the licenses for the DE version didn't allow for connectivity beyond 1 user, I assumed a free version would have similar restrictions. My bad...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply