| Author |
Topic |
|
Neon
Starting Member
13 Posts |
 Posted - 2011-09-28 : 02:46:05
|
| Need Some help in working out what version of sql to useas my app stands currently, is single user, single machine only (using access database)i have been asked if i can make it networkable and multiuser, i have managed to do this with my access database however it is just too slowaltho most of my clients will be using this software on only one machinebasicly i want a version of sql that can be easyly added to my installshield project (for easy unatteneded install)i've looked around the internet but the information is just too vast in the future i wish to add the ablitly to link to an online sql server (for real time upto date data) (read only)if someone can please help, or direct me to relevent information would be greatok i think this makes sense |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 02:53:10
|
| there are few other details also you need to consider like1. what would amount of data your app would be dealing with?2. do you want high availability options?3. do you require scheduling and job options ?etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-28 : 02:59:27
|
| If you need to create an installer package then sql server express is your only option in the sql server suite. It's free and supports most of the core features of the full sql server. It is also very easy to upgrade from express to standard version. Check out this link for a list of features and a comparison against other versions ->http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
Neon
Starting Member
13 Posts |
Posted - 2011-09-28 : 20:10:07
|
| Lumbago - Thank you i'll have a look and see what i can find outvisakh16 - very large amounts of data in the order of upto 200,000 records not sure what you mean by high avalibility all scheduling that i need is easyly handled by the appbasicly its to store, calculate and manipulate performance curves of pumping systemsin order to calculate correct pumps for the jobseach pump can have upto 200+ records of plotted datathanks all for the nice fast reply and food for thought |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-29 : 01:51:18
|
No offence Neon, but 200k rows of data is not considered very large...multiply by 1000 and we're starting to get a little closer (but still not there yet)  Nevertheless; what you can do is to offer two versions of your software. One with sql express and automated install procedures which will be "single-user" (or very few users), and a more "enterprise-friendly" version with sql server standard that has more functionality and is more multiuser-friendly. Express can easily be set up with networking support but it could be a way to differentiate your product a little. Here is a link to the Express download:-> http://www.microsoft.com/sqlserver/en/us/editions/express.aspx- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 02:58:42
|
| my thoughts:You could keep your Access front-end and store the data in SQL Server.SQL Server will provide more robust storage than Access. SQL Server is fault tollerent - so just turn off the power and the database should not be corrupted.SQL Server also provides "Full Recovery Model". If you use this mode then every time a change is made to the database it is recorded in a transaction log which is only "cleared" when you take a backup. You can roll forward the backups - so you can recover to exactly 10:51 - e.g. just before someone accidentally deleted all the customer records!You can also have two SQL Servers set up so that if one breaks the other one will take over. This is "high avalibility".So basically there are "levels" of safety that SQL Server can offer you, depending on how much you need, and how much $$$ you want to spend But ... you application is probably built in a way that suits storing the data in Access. To get good multi-user isolation, and also good performance out of SQL Server you may have to change the application quite a lot.For better performance you will need to use parameterised queries, although pass-through queries from Access may be sufficient. Some SQL Syntax in Access will not be valid on SQL Server.So there may be quite a lot of work involved. I haven;t used Access for many years, so the latest versions may make this easier than it used to be, but I think just "upsizing" your application from Access to SQL Server won't be enough without quite a lot of work adjusting the queries, particularly where the Access application has not been written in a way that is safely compatible with multi-user operation |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-29 : 03:06:51
|
| I didn't even think about the entire application being Access, I just thought to myself that Access was the database layer and that some other app came along with it! :) Changing an Access app to use sql server storage (express or standard doesn't really matter) _might_, as Kristen says, involve quite a bit of work but not necessarily.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 03:56:06
|
| Good point, the APP might be something different. The type of changes needed (if they are needed) will be the same though.Folk here can help with that part, plenty of people here who can advise on how to migrate and/or improve queries etc. |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-01 : 14:49:01
|
| Check out the row size and multiply by number of rows for each table. This should give you a rough estimation of database size (plus index size and space occupied by other objects). Express edition has limitation of 10GB per database. Though it doesn't look to me that your database will be close to 10 GB, however you should keep the limitations in mind :) SQL Server express will definitely provide you lots of features that you wont see in access db. |
|
|
|
Neon
Starting Member
13 Posts |
Posted - 2011-10-02 : 21:36:35
|
| Thanks alli did end up finding all the information i needed based on your helpi've converterd my data to sql express, and changed my connection code in my app, works greatso much faster than my access database (when accessing over network)even manged to get the silent installation workingHowever to all thanks for the help much apriciateddo have some other questions but will post in relevent forum |
|
|
|
|
Help Deciding on Sql Version