October 3, 2014 at 4:39 am
Hi. I've just started a new job and working with Access databases, but these are getting very close to the 2gb size limits. My predecessor simply created a new db and linked the tables, which is messy!
He also left a note saying look into upgrading to SQL Server.
I've never used it before, and have no idea if it would be appropriate to use. does it require a dedicated server or can it look at files from my desktop/ normal network drives?
I'm self taught at vba and sql so wont have any problems with the language side of things.
October 3, 2014 at 5:14 am
Migrating from Access to SQL is a good thing. Do NOT except Microsoft's recommendation for the conversion without checking it. We have a zip code column that converted to float. (That person is no longer here).
SQL Server can reside on a desktop but it is not recommended for multi-user/business situation. If you cannot talk them into a stand alone server, try a stand alone desktop. Again, I do not recommend this, however it will work.
I suggest getting a book on SQL Server administration - look for something general to start with.
Good luck.
October 3, 2014 at 5:51 am
MS Access is a fine program for small databases, but once you start growing, you'll run into problems quickly. Yes, upgrading to SQL Server gives you an awful lot of benefits, but they come at a price. SQL Server costs more than Access and while it can run locally, it likes to have its own machine. On the plus side, you can have multiple concurrent users, a host of clients connecting to it and you can write some pretty powerful procedures. You also get to use a very powerful language in T-SQL instead of Access SQL and VBA.
I am compelled to echo what djj said above - don't just use the migration tool and think you're done. They really are different database platforms and you should thoroughly check your SQL database to make sure everything works like you expect it to. Also, you'll probably find a host of things that can be improved after you move to SQL Server, but that's another discussion. Depending on your situation, this can also allow your application (as well as your data) to grow substantially.
Learning SQL Server has served a lot of people well and I wish you well on the journey you may be about to start. Like djj suggested, I think you should start reading. There are a lot of books freely available for download on this site that will last you for a while. If you have questions along the way (which everyone does) we'll be here. Good luck.
October 15, 2014 at 4:02 pm
If you can't get the powers-that-be to agree to buy a SQL Server license and server you might want to try to put in SQL Express (it's free). I think the 64 bit version can hold an 8GB database, otherwise 4GB (am I right folks?).
Search the MS site for 'learning' or 'training' for some free webinars, etc. also.
October 16, 2014 at 2:39 am
dwilander (10/15/2014)
I think the 64 bit version can hold an 8GB database, otherwise 4GB (am I right folks?).
10GB for SQL 2008 R2 and later. 64bit vs 32 bit has no effect on database size.
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
October 16, 2014 at 9:22 pm
Others has recommanded the free express solution as an replacement for the present access DB.
You will be surprised over how much you can do with that, and it's a totally no brainor to update to a paid for version when you get in space problems.
You should to remember, that the space is pr db - I havemore than 100 DB's one one server from 'Very small' to app. 1 GB.
Works as a charm!
Good luck!
Edvard Korsbæk
October 17, 2014 at 9:46 am
SQL Server is just used to store the data (and query it), so you'll probably still need Access to provide front-end screens for niceties like data entry and reporting. The difference will be that Access will link to the tables on SQL Server and instead of running its own queries and vba code it'll call stored procedures on SQL Server to manipulate the data and return only what's needed.
You definitely don't want to be sending half a million rows of data down the cable to Access only to have it select the 100 rows it needs and discard the rest! Do the querying on SQL server so it only sends you the 100 rows you wanted in the first place.
Although I'm not sure you can use Access to connect to an Express version of SQL Server, or at least I couldn't get it to connect when I tried it a few years ago.
October 17, 2014 at 8:49 pm
daniel.freedman 80164 (10/17/2014)
Although I'm not sure you can use Access to connect to an Express version of SQL Server, or at least I couldn't get it to connect when I tried it a few years ago.
In the good old days, there was huge differences between the free versions and the 'Paid for' via so socalled (Do I remember right?) 'Governator'. That one disapeared with MSSQL 2005, and all you can do with the full version, you can too do with the express.
Differences is in max DB size, and in some missing tools in the administrator tool, and 'One CPU only'
Best regards
Edvard Korsbæk
October 18, 2014 at 1:27 am
jonen (10/3/2014)
Hi. I've just started a new job and working with Access databases, but these are getting very close to the 2gb size limits. My predecessor simply created a new db and linked the tables, which is messy!He also left a note saying look into upgrading to SQL Server.
I've never used it before, and have no idea if it would be appropriate to use. does it require a dedicated server or can it look at files from my desktop/ normal network drives?
I'm self taught at vba and sql so wont have any problems with the language side of things.
MS Access is a file based database where SQL Server is a database server, effectively hiding all file manipulation from the user. Users do not open a database file but instead connect to a database on a server instance. File shares are inapplicable as a means of connection.
😎
Now just few quick questions
1) Can you briefly describe the role of the database and how it is being used?
2) Is it single user or multiple concurrent users?
3) Is the data stored in a "back end" database and linked to the user's "interface"?
Quick note on SQL Express and other free alternatives:
The CPU restrictions on SQL Express are 1 socket or four cores, that should not be a problem and neither the 10Gb database size. More limiting is the maximum memory 1Gb which effectively limits the usability although the "workaround" could be to have multiple instances (up to 50) on the same server.
Alternatively there are some very good free database systems available such as MariaDB and PostgresSQL. It would probably be worth while looking into those if a paid SQL Server is not an option.
October 20, 2014 at 1:00 am
Eirikur Eiriksson (10/18/2014)
jonen (10/3/2014)
Hi.Quick note on SQL Express and other free alternatives:
The CPU restrictions on SQL Express are 1 socket or four cores, that should not be a problem and neither the 10Gb database size. More limiting is the maximum memory 1Gb which effectively limits the usability although the "workaround" could be to have multiple instances (up to 50) on the same server.
Alternatively there are some very good free database systems available such as MariaDB and PostgresSQL. It would probably be worth while looking into those if a paid SQL Server is not an option.
I was not aware of the Max memory, which explais a lot of disk activity.
Thanks!
Best regards
Edvard Korsbæk
October 21, 2014 at 10:52 am
Talk about the story of my life. Self-taught in Access, VBA and JetSQL, then graduated to SQL Server. It's a daunting task, but not impossible. Just eat the elephant one bite at a time.
As far as SQL Server goes, get a full-blown copy if budget allows and park it on it's own server. You'll save yourself a lot of headache in the long run.
SQL Server Express is for the most part a viable option if cost is a consideration and scale isn't too big, but read up on the limitations. Most probably aren't a concern, but you'll definitely want to find a third party tool (or roll your own) for scheduled jobs since SSE lacks the Job Agent. You'll eventually want to automate as much as you can (backups!!).
JetSQL will familiarize you with the basics of SQL syntax, just be aware it has it's own idiosyncracies and T-SQL has no knowledge of the Access Object Model unlike JetSQL.
Overall, treat it as a tiered solution and make your data as independent as possible from your Access front-end. If the front-end has pretty much only Forms, Reports and just enough VBA to make it work, you're doing well. This also gets you to the point where you can create an executable in VB or C Sharp to replace the Access portion altogether, to say nothing of being able to leverage your data without relying on Access.
In any event, good luck.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
October 21, 2014 at 12:44 pm
The SQL Server Agent is a big deal. Without it, you'll have to create your own scheduler for things like backups, database consistency checks, index tuning, updating statistics and so on. If you can get the "powers that be" to get a full version, it's definitely the way to go.
October 22, 2014 at 6:52 am
Ed Wagner (10/21/2014)
The SQL Server Agent is a big deal. Without it, you'll have to create your own scheduler for things like backups, database consistency checks, index tuning, updating statistics and so on. If you can get the "powers that be" to get a full version, it's definitely the way to go.
Absolutely. There are some third party tools, I've seen some T-SQL workarounds to not having Agent and I believe there's a way to use Windows task Scheduler, but when all is said and done it's best just to bite the bullet and get a full version for business use.
I discovered the lack of Agent when I was tinkering aorund at home with Express 2012 trying to learn how to script job creation and scheduling as opposed to using SSMS. As you can imagine, it didn't work very well :rolleyes:
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
October 22, 2014 at 9:47 am
I have been using an Access front-end to SQL Server for many years. It has a few quirks but is mostly very good. At the beginning, simply use SQL as the back-end (on a test basis) and see how things work.
Over time you may find that some of the things you do (a Query that passes a lot of data over the network, for example) will be better handled by letting the SQL Server do most of the work (look at Pass Through Queries in Access or Views in SQL).
Note that very large tables in SQL may not be handled in Access - but, again, setup and run a test front-end.
ODBC is probably the best way to connect - but it too has a few quirks that you may need to work through.
SQL Server Express is likely the best way to start..easy to put data into full SQL Server later.
October 31, 2014 at 9:41 am
I have moved from Access, as the back end, to SQLServer Express. (Excel as front end to generate reports and to enter data).
Access started giving many errors when serveral users tried to access the tables at the same time. Corrupt databases.
Works like a charm now.
It was a steep learning curve (little experience with SQL), but worth it.
You can schedule jobs to do backups, shrink the database, bulk insert csv data, run stored procedures, etc, using the windows task scheduler.
Once setup it is easy to modify for varying needs.
J.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply