February 25, 2015 at 5:36 pm
I'm working on a database for someone that's currently in Access. (Personally, putting mission-critical data in Access scares me to death... no tools to do automated backups and restores.) So, can I use Ola Hallengren's maintenance scripts if I'm using Express? The company is really small, but I don't want Access databases blowing up on me. Besides, if i set up security, I can get really fine-grained control over who can see what.
What I noticed when reading up on SQL Express is that it doesn't include the Jobs/Scheduler stuff. So how would I have to schedule maintenance (backups etc). Would I have to automate Windows Scheduler?
Thanks!
Pieter
February 26, 2015 at 8:23 am
Actually, in some versions of Access there is the ability to automate a backup, but I agree with the idea of using SQL Server Express. If they are using the backup capability in recent versions of Windows, the database and log files will be backed up automatically. If you want to automate a SQL Server backup, there is a MS support article that gives you some details on how to go about it. There is also a more recent article that I saw on the same subject at http://www.mssqltips.com/sqlservertip/3458/automate-sql-server-express-backups-and-purge-old-backups/.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 26, 2015 at 8:43 am
I use Ola's maintenance solution for backups (and more) on my SQLExress instances and can't say a bad word about it. just schedule a batch file in your windows task scheduler and it's ready to go.
Example batch file:
sqlcmd -E -S <Server> -d master -Q "EXECUTE dbo.DatabaseBackup @databases = 'USER_DATABASES', @Directory = 'C:\Backups', @BackupType = 'FULL', @CleanupTime = 168, @LogToTable = 'Y'"
February 26, 2015 at 5:58 pm
oh, sweet! That's handy! So I just have to use Windows Scheduler directly instead of using a job (which probably talks to Windows Scheduler)... Okay.
Thanks!
February 26, 2015 at 6:00 pm
I'm just trying to protect myself from the infamous MDB corruption issues. That and allow for a lot of data... so we'll see. Just don't know how I would explain to my client that their database just corrupted and I can't fix it. That would be really bad!
February 27, 2015 at 9:33 am
To safeguard yourself against MDB corruption, store as little dynamic information in Access as possible. I've got lookup tables for combo boxes in my current Access project, everything else is in the SQL side where a bulk of the work takes place.
Myself, as the developer I always kept a copy of the full MDB and the 'runtime' version in case someone mucked up their copy. In my current project, I'm working with a hosted server for the first time and am planning on setting up login scripts to always copy a fresh copy of the runtime Access DB to the user's desktop when they log in as I'll have some 100-200 users peak and don't want them all banging against the same copy of Access, so they each get their own.
I've had no problems with Express using batch files, SQL scripts, and OSQL/ISQL to do backups and DBCCs. Depending on the number of users on the system and how much back end processing is going on, you might need to be concerned about the 1 gig memory limit in Express.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 2, 2015 at 3:47 pm
Wayne West (2/27/2015)
... Depending on the number of users on the system and how much back end processing is going on, you might need to be concerned about the 1 gig memory limit in Express.
Note that the current versions of Express (2008, 2012 and 2014) will support databases up to 10 Gbytes in side (the data file - the log file isn't limited), but will only use up to 1 Gbyte of RAM, and the lesser of 1 socket or 4 cores, so you do get limited multi-thread processing. If your database is substantially larger than 1 Gbyte you may see some performance loss as data (and indexes) cannot be cached in RAM. Bottom line is we have a number of customers using Express, and are quite pleased with it.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply