none
Upgrading/Migrating SQL Express 2005 Database To SQL 2005 Standard

    Question

  • Hey everybody i'm hoping you can help me out here. Go slow and include as much detail as possible when answering cause i have no idea how to use SQL Server, so just bare with me if i ask a stupid question ok.

     

    I presently work for a company that resells a point of sale software call Cash Register Express originally made by PC America and it comes integrated with SQL Express 2005.

    Now as you all might know and I just recently found out it that SQL Express has a limited database size of 4GB after the database hits about 3.4 GB all hell breaks loose and the POS software goes haywire.

     

    My question is there a SIMPLY (and i really mean that) to upgrade form SQL Express to SQL Standard without loosing any data and overcome that 4GB limit ?

    Tuesday, January 27, 2009 11:02 AM

All replies

  • Yes you can upgrade from SQL Server 2005 Express to SQL Server 2005 Standard directly (In place upgrade). SQL Server Express is free and for Standard edition you need to buy it.


    In SQL Server Express 4 GB limit is only for mdf file and it doesnt include ldf file. So check whether your MDF has grown or LDF has grown? If its the LDF then you can shrink LDF to bring back the size. If its MDF then you need to upgrade to higher version.

    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, January 27, 2009 11:39 AM
    Moderator
  • Hey Sagar that for responding so quickly I really appreciate it, but like i said your dealing the an SQL Amish here lol. I don't know where to locate either of the files you mentioned (MDF and LDF),however I have used the 'Srink' feature in SQL Management Studio Express before. Is That what  your referring to?

    Tuesday, January 27, 2009 2:01 PM
  • Answering the "question within the questions" here...Vidhaya is correct, we support upgrade from SQL Express to SQL Standard right in place, but we do not support running SQL Standard on POS systems. There are two things that surprise me about your situation:

    • That "all hell breaks loose" at 3.4 GB - there is nothing about the 4 GB limit that would cause this. I really thing you should take a look at the application to find the real reason for this issue, it's not the fact that 3.4 is near 4.0.
    • That you have 3.4 GB in a POS system - that actually seems like a log of data for a cash register.

    I now you'd like a simple answer here, but there is a lot of missing information hidden under "all hell breaks loose" and I don't think it has anything to do with the amount of data you have stored. If there is an underlying problem (i.e. not related to size) here, it's not going to go away just by moveing to Standard edition. Additionally, the fact that we don't support Standard on POS systems suggests that now would be a good time to look at your data storage strategy and find ways to reduce the amount of data being stored on the POS system. (Assuming that your data load is growing and that you will eventually hit 4 GB.) Two ideas that come to mind are to archive older data by moving it to a separate database or to off load your primary data storage to a central Server (you could use Standard for this) and only have the data on the POS system that it needs for it's immediate work. You could use a number of technologies for this data transfer between the central Server and the POS system, but Replication is probalby the most common.

    Incidently, the expected behavior when you hit the data size limit in Express is that you will get an error that you can not write data to the database. It's the exact same thing you would see if you set a fixed size for your database when you created it and did not allow for auto growth of the file. This is a very simple mechanism, it doesn't "get tired" the closer you get to a full load which is why I don't expect behavior out of the ordinary simply because you have 3.4 GB.

    Regards
    Mike


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 27, 2009 3:15 PM
    Moderator
  • " the expected behavior when you hit the data size limit in Express is that you will get an error that you can not write data to the database "

     

    Thats exactly what happens, but when your close to the 4GB limit the response time form the POS is generally slow and sometime takes up to 5 mins. to complete a simple transaction. By this time the lines get longer and the customers a pissed!

    How do i go about Replocation the data to a cenrtal server? A video would be nice if i'm not asking for too much lol

    Tuesday, January 27, 2009 4:19 PM
  • Tuesday, January 27, 2009 9:24 PM
    Moderator
  • Obviously you're getting the error you state; I'm just suggesting there must be other factors involved. If you have a database that is 3.4 GB in size and you hit the size limit error, you must be writing over .6 GB in your simple transaction, which doesn't seem so simple. If the performance is getting slower as the database increases in size that would seem to indicate that you're transactions are also changing with the database size, which doesn't make sense.

    I don't know the design of your system so I'm just theorizing here, but I look at it like this: a transaction on a POS system is going to go something like this:

    1. Some kind of input based on the item being sold. (UPC, SKU#, etc.)
    2. Input is used to query the price out of pricing table.
    3. If the POS is integrated with an inventory system, a tracking record is written to the inventory to show the reduction in stock on hand.
    4. Next item.

    This is a little simplistic, but it's a start. The point here is that the speed of these steps shouldn't really be impacted by the size of the database. Consider:

    • Collecting the input doesn't involve the database at all, so there should be no impact here.
    • The pricing table should be relatively static over time. Prices may change, but in most stores the number of different items doesn't change that much so querying for the item price should have a fairly consistent time impact.
    • Posting an inventory transaction shouldn't be a huge issue since it should just be an insert of a record indicating the sale of the item. There could be some size impact here depending on the number and types of indexes you have on the inventory table, but this should be highly optimizable for performance.

    Centralizing the data is a great way to take some load of the edge servers, and will likely be a useful part of an archiving system to keep the size of your POS database down, but I still think you should take a look at how your queries are running and try to identify why they are slowing down. Probably the easiest way to do this is to setup a trace on one of your POS systems. (Don't use a production system as tracing can also slow things down and we don't want the customers any madder than they already are.) We don't have SQL Profiler with SQL Express, but you can create traces directly using T-SQL, check out Books Online for topics about SQL Trace for more information.

    You may find that you have a relatively straight forward issue that can be solved by changing indexes or modifying your queries. You don't know until you've looked.

    Regards,

    Mike


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 28, 2009 7:01 AM
    Moderator
  • hi,

    in addition to what already posted by "all" [ :) ], I'd eventually check for database's file growth settings as well... I'd not expect that it will be set to increase it's size with such a "big" ratio, so that, say, your current 3.4gb db file is set for next growth with 0.7 gb, that will exceed the Express supported size.. but just in case :)

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Friday, January 30, 2009 12:24 AM
    Moderator
  • The database actually grows by 1mb
    Friday, January 30, 2009 12:55 AM