Post reply

Any reason not to use SQL Server 2005 Express Edition?

  • December 26, 2006 at 7:19 am

    #176216

    When Microsoft rolled out MSDE, it was not supposed to be redistributed, nor did it have a front-end to maintain it. Now, SQL Server 2005 Express Edition has been rolled out, and Microsoft says it can be redistributed as can Management Studio Express. I deal with a lot of small businesses that I think this could be all they would need, and very few would ever need to upgrade to the full blown SQL Server. Is there any reason NOT to use the Express Edition?

    Thanks,

    Dennis

  • December 26, 2006 at 11:52 am

    #680000

    no built-in scheduled backups available in Express.

  • December 26, 2006 at 12:10 pm

    #680003

    Other than job scheduling there are some feature no available in Express edition...

    Check the following SQL Server 2005 Features Comparison...

    http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

     

    MohammedU
    Microsoft SQL Server MVP

  • December 27, 2006 at 6:00 am

    #680063

    Let's see:  No import/export capability so you can't simply upsize a Jet app to it.  No SQL Server Agent so you can't run any automated database maintenance task like backups or index rebuilds.  You can run them as scripts, but you have to run them and track them from your front end app.  4 gigabyte limit.

    Despite all that, we're still looking at it for our smaller customers who currently are using Jet as the data store.


    Student of SQL and Golf, Master of Neither

  • December 27, 2006 at 2:29 pm

    #680179

    Thanks alex, Mohammed, and BobAtDBS

    Alex,

    Manual backups should not be too big a burden to lay on the customer - especially for free vs. $$ for the full blown SQL Server 2005. (Or, I'll try to find info to help me script/automate it through the front-end app.)

    Mohammed,

    Thanks for that link. (It is a better comparison chart than what I saw before.)

    BobAtDBS,

    Wouldn't you still be able to upsize a Jet MDB to a 'project" (ADP), and at least start with that structure? Here is a quote I found:

    "If you select the option in the Upsize Wizard to create an Access project (.adp), the wizard will take a crack at migrating queries into Views, SProcs and Functions. It will also take stored queries behind bound objects such as combo boxes and potentially upsize them as well.

    This is a rough conversion at best, and in some cases the objects don't work correct and can break your Access application. In general, when migrating an Access application, it is best to go with the Link Table methodology, and then manually pick and choose which queries it makes sense to manually migrate to SQL Objects in order to improve the performance and functionality of your application. Migrating directly from you Jet based application to an Access project usually results in an application that is less performant that the original application because of the differences in the database engines.

    ...

    Regards,

    Mike Wachal

    SQL Express team"

    I would expect to have to start from scratch to write Stored Procedures for the 'new' SQL Server 2005 Express version of the database, maybe at best utilizing some Access Queries as starting points. (I took a few Access databases into SQL Server 2000, and found that my major re-write involved adding Stored Procs. I never used Access forms or reports, so that was never a problem for me.) So, I wasn't thinking of being able to go straight from Jet to SQL Server 2005 Express.

    This sure does sound like a viable solution for many small business customers, especially for those currently using Access. 4GB is WAY bigger than any database that any of my small business clients will probably ever need. I admit that I am more of a front-end programmer that can create an adequate database architecture, than a DB guru, so I probably don't even know what pieces of the puzzle are really missing. You do have me wondering enough about scripting index rebuilds to start searching for information (such as http://www.sql-server-performance.com/rebuilding_indexes.asp)

    Thanks again.

    Dennis

  • December 27, 2006 at 2:50 pm

    #680181

    In the past, we have used the SQL Import wizard to bring Jet tables into a new SQL database (manually).  With express, you don't have that option (as I read it).  You can of course upsize "from below" by using whatever  upsizing tools that Jet provides.

     


    Student of SQL and Golf, Master of Neither

  • January 6, 2007 at 11:51 pm

    #681667

    sql express is meant for just your type of customers. it's a freebie for small businesses and hobbyists to try to get them hooked on MS SQL, as opposed to where they might otherwise go: MySQL, PostgreSQL, Firebird, etc.

    Then once you are dependent on MS SQL, and need a more powerful system, it's too late. you are committed, so now you gotta spend the dough.

    Don't get me wrong though. I think SQL Server is great, and worth the money. I'm just glad it's not my money.

    ---------------------------------------
    elsasoft.org

  • January 14, 2007 at 7:03 am

    #683118

    Just to correct one thing you said. MSDE has been freely redistributable since the beginning. MS stuttered for a few months in the beginning but then allowed free distribution. As for a reason not to use it, we do not use SQL Server 2005 Express Edition because it cannot act as a replication publisher. We have an application that allows users to use MSDE as the replication publisher, e.g., one machine running MSDE as the master server could publish the database to several other machines on the network using MSDE replication. We used merge-replication so the other machines, which were portables, could disconnect, work off-line, and then reconnect to merge their off-line work. MS has changed the SQLMERGE.DLL so MSDE (Now SQL Server 2005 Express) can no longer act as the publisher in this scenario. To do this now, the replication publisher must run on a standard SQL Server 2005 machine.

  • January 17, 2007 at 6:57 am

    #683710

    A way around the backup issue is to use Windows Scheduled Tasks. Micheal Otey from SQL Server Magazine had an article about this although I can't seem to find it anymore (these are some of his other articles: http://www.sqlmag.com/Articles/ArticleID/93004/93004.html).

    You just need to set up a scheduled task (Built in to Windows: Control Panel>Scheduled Tasks>Add Scheduled Task) as a Command Prompt task with something like this command line:

    sqlcmd -S (local)/SQLEXPRESS -i c:\sqlbackup\myMediaBackup.sql -E

    You need a file called myMediaBackup.sql in the path provided and it can contain whatever backup script you wish. I use something like this:

    BACKUP DATABASE [dbname] TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dbname.bak'

    WITH NOFORMAT, INIT, NAME = N'dbname-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    At first I was using NO INIT but this keeps growing the backup file by simply adding on each subsequent backup into the same file. Using "INIT" writes over the dbname file each time. Either way has advantages and disadvantages. I use other scheduled tasks to put the backup files into folders labeled with each day so I keep a 7 day history.

    You could create another script with multiple times to do a differential:

    BACKUP DATABASE [dbname] TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dbname.bak'

    WITH differential, NAME = N'dbname-differential Database Backup'

    GO

  • January 18, 2007 at 8:39 am

    #684027

    Terry,

    Thanks for adding to the discussion. This is some excellent info.

     

    Dennis

Viewing 10 posts - 1 through 9 (of 9 total)

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