Using SQLExpress in Production

  • eric-810666

    Ten Centuries

    Points: 1221

    Hi All,

    I must first confess that I am not a network admin and I am by no means a SQL Server expert. I am a .NET developer that is completing a small e-commerce application that I am about to roll out.

    I plan to use the hosting company that I have been with for a number of years and sign up for a virtual server package where I will be placing my new site. This offers me many advantages but also requires that I handle more administration responsibilities.

    I expect that my site will start small and as it grows (I hope) I will expand my server requirements as well as my database needs. For virtual servers the hosting company offers MySQL and SQLExpress only. I can purchase SQL Server (2005 / 2008) and install if I wish however the cost is prohibitive at this point. I also have the option to use the shared SQL2005 server (associated with one of my other accounts there). I do not plan to keep credit card information at his point.

    My question is what to do? My initial thought was to use the shared SQL server 2005 however I like the idea of having the database on my server and am now thinking SQL Express might be my best option. I have been using SQL Express for the development process with no issues.

    I would appreciate any thoughts or experiences others have had with using SQL Express in a semi-low transactional type senerio.

    In addition I will be responsible to backup my server. Is there any issues with an automated backup system accessing and backing up the SQL Express database and log files?

    Thanks to all!

    Eric

  • AndrewSQLDBA

    SSC-Insane

    Points: 21827

    You need to think about the maintenance of the database. With a shared database server, the database will reside on a database server with others. There are no issues with that, there is a very high limit of databases per instance. 32,565 if my memory serves me well. I would be more concerned about database maintenance.

    Like you stated, Express is great for development on a workstation, it is not however something that I would want to use in a production environment. One, it is very difficult to maintain. I have never been a big fan of express, other than some development. For myself, I still use an actual SQL Server to perform development. I have used express only once, and then I did not install it during the next server rebuild cycle. there are many things that Express just will not do, that SQL Server will perform easily and quickly. I use SQL Server databases for many things, and almost all of them require some type of data pump, either in or out. Then there are the scheduled jobs that maintain the databases and the server itself

    I would recommend that you use SQL Server on a shared server before using, or attempting to use Express. You will thank yourself many times over when something goes wrong, and you take a chance of losing data.

    Andrew SQLDBA

  • eric-810666

    Ten Centuries

    Points: 1221

    Thank you for your comments Andrew,

    I was looking for others to chime in with their experiences as well. I am in a really bad spot here and don't have the full finances to do it right.

    Maybe it is my setup (in SQL Server Management Studio) but the shared SQL2005 server doesn't give me any (other than Copy Database) options then SQL express. I have no DTS / SSIS capabilities. No replication, etc.

    I can backup the database maunally (don't want to do that everyday, or more).

    So I am at a loss as to how to handle my issues.

    I looked at the pricing for SQL server and it is confusing. If I use a virtual webserver for my website and install SQL 2005 or 2008 I am not sure which package I need. Also CALs needed etc.

    Would a website only need 1 CAL? I found a listing for SQL Server 2008 Standard 32/64-Bit (10-Client) Full Retail Box

    for $1950.

    Can you or someone tell me is that what I need?

    Since I am currently using SQL2005(Express) and am comfortable with it. Can SQL2005 still be purchased? (and supported by MS?) I noticed a price of $6300 for the enterprise edition but $24 for the standard, Workgroup (32 bit) for $550 - $600.

    I simply don't know what I need. If I need to provide more information about my site / senerio please advise.

    I thank you as well as all others for their comments and suggestions.

    Eric

  • Steve Turner - SQL Managed

    SSC Eights!

    Points: 836

    Eric, I think SQL Express could probably do just fine for your situation. One of the purposes of SQL Express is for ISVs to package together with their applications, and from your description you sound to me like an ISV (Independent Software Vendor). I install many applications that run just fine with SQL Express. If the hardware limits and limited feature set of SQL Express are OK for you for now then just go with SQL Express and switch over to standard later on when you have a better revenue stream and need more than SQL Express can offer. SQL Express 2005 is limited to 1 physical CPU (Not applicable since you have a dedicated virtual server), 1 GB of RAM, and a 4 GB database size. It also doesn't support Full Text Searches, which would be important if your web application needs to support that. Here are a couple of links to feature comparison pages for SQL 2005 and 2008. http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx for 2008 and http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx for 2005.

    As for pricing, you would need to buy per processor licensing for a SQL Server instance supporting a web based application, user CALs don't apply. A virtual server would need one "per processor" license. Those are quite expensive.

    You can automate backup jobs with SQL Express by writing a SQL Script and using Scheduled Tasks on the server to schedule the backup job. Post back if you want some help with that.

    Steve

    SQL Managed

    SQL Managed

  • eric-810666

    Ten Centuries

    Points: 1221

    Hi Steve,

    Thank you for your response. The site will be very slow and have few transactions to begin with. It will take time to grow interest as well as participation in the site. I would like to use SQL Express initially and as you said upgrade as the site grows.

    After reading the links you provided I can see where the Workgroup and Standard (per processor) editions are where I should eventually be. Possibly starting with the 2005 Workgroup version and upgrading to the 2008 Standard edition if the site warrants the change.

    I will look into SQL scripts and would like to ask detailed questions when I get to that point. Thank you for your offer. I am sure you will hear from me.

    I have another question in the forum concerning my data upgrade from SQL 2005 Express to SQL 2005 server. I have not looked at the suggestions the Ron and Jeffrey have given but will do so in the next day or two. If you have any additional comments I would appreciate your input.

    Thank you again,

    Eric

  • bytesizedata

    SSC Enthusiast

    Points: 106

    Dear Eric:

    For whatever it's worth to you, we've been using SQL Express 2005 for a DB of about 70 tables and 50 users. Table sizes up to a couple of hundred thousand rows. DB total size now at a couple hundred meg. No problems at all.

    Bystesize data

  • eric-810666

    Ten Centuries

    Points: 1221

    Thanks Bytesize,

    It is good to hear how others are working with SQL Express.

    Eric

  • 4e7daf3a

    SSC Enthusiast

    Points: 100

    Steve Turner - SQL Managed (2/28/2010)


    It also doesn't support Full Text Searches, which would be important if your web application needs to support that.

    Just to add on Steve's answer: SQL Server Express does support Full-Text Search. You need to install SQL Server Express with Advanced Services to get the feature.

  • eric-810666

    Ten Centuries

    Points: 1221

    Thanks 4e7daf3a for the additional information.

    Eric

  • jmoss111-506838

    SSChasing Mays

    Points: 651

    i've been using SQL Express for production for over three years without a problem, some database tables with 10+million rows. this serves a light volume user community of about 200 users who use basically for searching and no transaction processing

    As far as backup, scheduling and the like, get SQL Scheduler from lazycoding.com which is a free download and works great.

  • eric-810666

    Ten Centuries

    Points: 1221

    Thanks Jmoss,

    I will check it out.

    Eric

  • Jeff Braunstein-302643

    SSC Enthusiast

    Points: 146

    eric-810666 - Tuesday, February 23, 2010 11:41 PM

    It can certainly be used for significant production applications. We have used it at over 1500 healthcare clinics all with separate SQL Server Express instances installed to process millions of transactions each day.

    You can easily get around the SQL Server Agent disadvantage by using one of the following:

    1. Third party product such as SQLAutomate
    2. Windows Task Scheduler
    3. A Standard or Enterprise Edition of SQL Server with the master servercapability installed

    1 and 2 are verylow cost or free. 3 is expensive unless you already have it available in yourenvironment.

  • spaghettidba

    SSC Guru

    Points: 105661

    Jeff Braunstein-302643 - Wednesday, June 28, 2017 12:09 AM

    Thank you for your insight, but...you understand this is a 7 year old thread, right?

  • Jeff Braunstein-302643

    SSC Enthusiast

    Points: 146

    spaghettidba - Wednesday, June 28, 2017 2:09 AM

    Jeff Braunstein-302643 - Wednesday, June 28, 2017 12:09 AM

    Thank you for your insight, but...you understand this is a 7 year old thread, right?

    Yes, I understand this is an old thread.  Unfortunately, many DBAs still strongly suggest that SQL Server Express should not be used in a production setting.  This is an ignorant position and I want to point that out.  Microsoft has continued to increase the value of SQL Server Express with the updates in 2012, 2014 and 2016.  Also, most DBAs state the biggest reason is the lack of SQL Server Agent.  As I pointed out, fully functional and affordable tools such as SQLAutomate provide the robustness to the environment that DBAs indicate is lacking with SQL Server Express.  DBAs could be saving their companies from the expense of Standard Edition in many situations.  Obviously, SQL Server Express is not right for everyone and every situation.  It has it limitations intentionally.  Understanding the limitations and the options helps make the best decision.

  • Sue_H

    SSC Guru

    Points: 90260

    Jeff Braunstein-302643 - Wednesday, June 28, 2017 8:34 AM

    spaghettidba - Wednesday, June 28, 2017 2:09 AM

    Jeff Braunstein-302643 - Wednesday, June 28, 2017 12:09 AM

    Thank you for your insight, but...you understand this is a 7 year old thread, right?

    Yes, I understand this is an old thread.  Unfortunately, many DBAs still strongly suggest that SQL Server Express should not be used in a production setting.  This is an ignorant position and I want to point that out.  Microsoft has continued to increase the value of SQL Server Express with the updates in 2012, 2014 and 2016.  Also, most DBAs state the biggest reason is the lack of SQL Server Agent.  As I pointed out, fully functional and affordable tools such as SQLAutomate provide the robustness to the environment that DBAs indicate is lacking with SQL Server Express.  DBAs could be saving their companies from the expense of Standard Edition in many situations.  Obviously, SQL Server Express is not right for everyone and every situation.  It has it limitations intentionally.  Understanding the limitations and the options helps make the best decision.

    Interesting....the president of the company that sells SQLAutomate has the same name - Jeff Braunstein
    You wouldn't be posting to older threads to promote your product would you?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply