Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    38

    Unanswered: Is SQL Server an option?

    First off, I apologize for not knowing what I'm talking about and being long winded. I'm trying to determine if SQL Server Express is an option for a client of mine. Their needs are beginning to go beyond what I'm comfortable with in Access, so I'm looking into the option of upgrading to a SQL Server Express back-end with a VB front-end.

    Access doesn't require any setup beyond "File, new", so I know nothing about the background work required to get a database running on a platform like SQL Server.

    My first concern is what kind of network admin rights do I need to install & use SSE..

    It's unlikely that her IT group will just hand us the keys to any of their servers, so all we really have available is what we can put on her network drives. Am I right in assuming that using a SQL Server database would involve more network privileges than just dropping a file on the network and pointing my front end app at it?

    With that said, I think I remember seeing something on one of the MSDN pages about setting up a database to run off of a CD-Rom. Could I somehow use this capability to get what I need?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server really insists on having the database sit on a local drive... It isn't at all happy about using network drive space.

    You don't technically need anthing more than machine administrator privleges to install SQL 2005 Express, and once the service is installed it can actually run as "local system" as one of the choices, making it very comparable to MS Access. SQL Server provides many other choices, but this is pretty comparable to what you're accustomed to with Jet (the database engine used by MS-Access).

    You need to invest some time up front to learn about SQL Server Express. It offers so many choices that go far beyond what Jet allows. While it takes more time up front, the additional choices are definitely worth the effort in my opinion!

    -PatP

  3. #3
    Join Date
    Apr 2006
    Posts
    33
    You can't just point your app to read a SQL server MDF file off a network drive - SQL Server works as a service and needs to be installed on a PC somewhere on the network. Clients don't read the mdf/ldf files - SQL Server does this on behalf of the clients.
    Note: SQL Server Express introduces an xcopy deployment concept where you can automatically connect an mdf file to a running instance of SQL Server Express and detach the database when your app is finished with it. This makes MDF files portable; similar to access database files (providing users have SQL Express installed on their machines). If your scenario requires users accessing a central database, this is unsuitable – besides, additional time is required to attach the database every time you run you app and all the clients would require SQL Express (each having a local copy of the database)

    Installing SQL Server isn’t too difficult – SQL Express is easier to install than its predecessor, MSDE (which required command line options to set the sa password – at least in the earlier versions). Installing SQL Express is about as easy as installing any other program such as MS Office. The defaults are probably ok for most installations, but its worth while learning how they affect your server – things such as the collation settings.

    There are various ways for you to actually deploy you database to the server once SQL Server Express is installed – if you don’t want to go to the lengths of creating an installer, you could simply supply the database files and instructions on how to attach the database to the server. A set of instructions on a single side of A4 should be sufficient – A computer savvy person shouldn’t have any problems setting up SQL Express and attaching the database without need for your involvement.

    As for your front end apps – a connection string is required to specify the server, the database and authentication details (similar to a connection string specifying the path of the Access database file). Store this string in a config file (text/xml file) that can be modified without re-compiling your application.

    There are various options for upgrading Access databases to SQL Server. I believe the easiest option is to use the upsizing wizard – this can also upgrade your front end to an Access project that connects to the new SQL database. Play about with the upsizing wizard and perform thorough testing – it’s unlikely to get everything right. Check that the SQL data types it selects for your columns are the most appropriate type. Check indexes and constraints are created correctly.

    As Pat said above – take some time to learn SQL Server Express. Microsoft has certainly made the new version of SQL Server Express accessible to developers – many are now opting to use SQL Server over MS Access. Access databases are still appropriate for smaller projects and it remains a great client tool.
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  4. #4
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by Wiseman82
    You can't just point your app to read a SQL server MDF file off a network drive - SQL Server works as a service and needs to be installed on a PC somewhere on the network. Clients don't read the mdf/ldf files - SQL Server does this on behalf of the clients.
    Right.. That's what I gathered from "Figure 2" on this page: http://www.informit.com/articles/art...26863&seqNum=1

    So I gather that I have to have a dedicated machine that's always on (i.e. a server) that has SQL Server (or SSE in my case) installed and running. My apps (installed on each user's machine) contain connection protocol that connect to the server, which sends data back & forth as needed..

    I assume that it's not a good idea to have one of my user's machines operating as the server. It seems like to use SQL Server, I'd have to get my my client to spring for a dedicated box OR get her IT department to use one of their existing servers, correct?

    I'd really like to learn and use SSE for this because they're also talking about combining the two apps that I've built into one. I'd be really, really worried about Access handling that.

    I'll take some time later and re-read these two replies & see if I can come up with any other questions..

    Thanks again for your help!

  5. #5
    Join Date
    Apr 2006
    Posts
    33
    Yes - thats about right. You could use one of the clients as the server - for a small workgroup environment this is probably ok, but ideally you want this on a server computer that isn't going to get shut down. It's better to run SQL Server on a dedicated server, but this might be overkill for your scenario.
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    As far as upgrading an existing Access front-end to use sql server....

    Good Luck

    Have never seen one that didn't need to be re-written
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by Brett Kaiser
    As far as upgrading an existing Access front-end to use sql server....

    Good Luck

    Have never seen one that didn't need to be re-written
    Actually I did one a few years ago that worked out pretty well. I was in a department that had a drawing check-out/check-in/search system.

    Since I knew the process extremely well, I designed the new app from the ground up in Access 97. We used it for about a month to feel it out & make sure the data structure really was what we wanted. After that, we handed it off to a guy in the IT group who replicated the data structure in SQL Server 2000.

    When he was done, we ported the data over and went online with the new front end/back end setup. There were a few quirks to work out over the next week or so, but nothing major and definitely not a full re-write.

    Back to the original topic - I'm going to have a good heart to heart with her about my concerns and our options going forward. This isn't mission critical, 100%-uptime data, but I'd rather not set it up on a user machine.

    Ideally, we'll be able to find an ally in IT that will set up SSE for us. Next best solution would be buying a low-end box specifically set up to act as a server and stashing it in her area. (They have a purchasing agreement with Dell. Any reason their low-end Celerons wouldn't be suitable?)

    Thanks for working through this with me guys..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •