If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
david wrote:
Dunno, haven't tried that one. I guess that includes Visual Studio Express? Otherwise you will be stuck using SQLCMD to do even the most basic tasks. Visual Studio does include SSE, but SSE is also a standalone download. Another option would be to download the full version of SQL Server to experiment on. Last I looked, the full version is FREE for you, a DEVELOPER, to do DEVELOPMENT on. You just use Express when your company starts using it for real data. I believe it actually costs $50 to have the developer version. Besides it's not strictly necessary because the SQL Server Management Studio (SSMS) is already available. In all versions it's bundled but I believe for Express, you have to separately download the SSMS but it's free as well. The advantage of the full version is that it comes with the complete client tools and documentation, including SQL Server Enterprise Manager, which is (was?) the primary administration tool for SQL Server. Dunno how Visual Studio/SQL Server Express compares with that now. It should be noted that the complete documentation is online at MSDN. If you google "Transact-SQL whatever", you'll find many good documentation there. I'm struggling with the fundamentals of SQL Server databases. Is it a single file like an .mdb file? Yes, it is a couple of files, like Access requires MDB, LDB and MDW files, and yes the data is in just one MDF file, like Access data is in just one MDB file. While this is technically accurate, this is conceptually misleading because in client's interaction with server, the question of "where is the .mdf?" will never be asked by the client. In fact, a properly set up server would _deny_ access to the users to those files. Only the daemon (aka SQLSERVER service) or the administrator would have access to those files. Thus, it's important to understand that you do not "browse and open a file" as you would with other applications such as documents or spreadsheet, but rather "connect to" a server using one of many protocols... TCP/IP, Named Pipes, Named Memory, and then some more. Thus the question of "where is the file" is totally nonissue because that's the daemon's job to manage it and you communicate with the daemon. Where does it get reside? Like Access, they reside where you put them. If you want to share them, you put them somewhere where they can be shared. Then you set up the shares so the user can use them. No. As I explained, daemon does all file handling here. Therefore a proper setup will _deny_ the users access to the files. All communication should go through the daemon. So you never put .mdf and ..ldf files in a share folder but rather merely open the port 1433 for TCP/IP or use Named Pipes or whatever for the clients to connect to. This is why if you look at various ODBC connection strings, the only time you do see a filepath is when you're working with a serverless source (e.g. a .mdb will have its filepath in the ODBC connection, but you'll never see that for a Oracle, MySQL, DB/2, SQL Server!) connectionstrings.com To be crystal clear: The users do not need and should not have access to the .mdf and .ldf files!! For Access, creating new databases is done from Access, and setting up network database shares in done from Windows Explorer: For SQL Server, it is done from Enterprise Manger, or Visual Studio, or using OSQL or SQLCMD Is it creating an SQL Server on the machine which is a different thing to an SQL server database? Creating a SQL Server on a machine means installing and starting the SQL Server service. For Access, the "SERVER" service is required (plus some other things). For SQL Server, a "SQL SERVER" service is required (plus some other things). The only difference is that the "SERVER" service is included and started by default on Windows: the "SQL SERVER" service you had to download and install. I do believe that "Server" service is not actually germane to the discussion here. If you read its description, it says it enables file, print and named piped sharing over the internet. That what it does. SQL Server services, OTOH, enables clients to connect to this machine and issue requests for data. In fact, if I turn off "Server" service, I still can connect to SQL Server on my SSMS, and reach a Sharepoint site remotely... IOW, "SQL Server" service and "Sharepoint" service are not dependent on the "Server" service. There is of course the notable exception that if I wanted to use Named Pipes, then yes, I do need "Server" service. But with TCP/IP, then no. They have nothing to do with each other. As I explained in my previous reply to Jon22, the term "server" actually refers to a role, rather than some kind of program. A server usually has a daemon (aka Windows service) that listens on a specific protocol (e.g. a port on TCP/IP, a region of address for Named Pipes/Memory, etc.) and react to the requests through there. It goes back to why it's important to not be so concerned with the questions such as "where is the file?" because those are wrong questions to ask. You will have to completely re-write that stuff in TSQL. It's not a lot more difficult, but it is different. I would question that. A well-written Access application usually will run on any backend, be it Oracle, MySQL, DB/2, SQL Server with minimum change (e.g. using linked tables). It all depends on whether one has written the application specifically for server-client architecture in mind, of which also benefits even ACE engine. There are times and places where re-writing a piece of functionality into T-SQL, stored procedure, views or other SQL Server objects does make sense, but I really don't believe it requires a wholesale rewrite. But that is largely a function of how well written the application was to begin with. |
#12
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
=?Utf-8?B?Sm9uMjI=?= wrote in
: Can anyone suggest a simpler way of achieving remote access to my database? Windows Terminal Server is by far the easiest approach. Also, if you can wait, A2010 + Sharepoint 2010 is going to make it even easier. Without a server (web server or terminal server or SQL Server) on the Internet or an Internet-accessible VPN, though, there's not much way to do it, regardless of what back end you use. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#13
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
"Albert D. Kallal" wrote in
: If you have an IT department at your disposal, then I often suggest windows terminal services. It doesn't take much at all of an IT dept. to support WTS. If you have a Windows server, it's pretty much a matter of setting up a VPN to get through the firewall, and then adding CALs on the server. Both of those things are one-time setup issues, and absolutely trivial for anyone who is getting paid to do IT support. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#14
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
=?Utf-8?B?Sm9uMjI=?= wrote in
: I think I am going to persevere with trying to get my head around SQL server. I think this is a mistake. Writing an Access app to run on SQL Server across the Internet means you have to really understand how to design and maintain server-side components (view and stored procedures) and then you have to carefully design your app to use those server-side objects, and you may have to jump through certain hoops (such as using ADO to get editable results from sprocs) that are not compatible with default Access behaviors/designs. Also, you'd have to set up a VPN (assuming you're not silly enough to just expose your SQL Server port directly to the wide-open Internet). If you've got a server to run SQL Server, you've got a server that can run Windows Terminal Server. Since you'd have the VPN anyway, the only step to use WTS would be to acquire the CALs to allow users to connect to run their Remote Desktop Sessions, and then the basic setup of the terminal server for your application (giving each user an individual copy of the front end, automating updates, etc.). And WTS requires NO ALTERATIONS to your application, and no learning curve. Going with SQL Server to support remote access has a very high learning curve, and would require substantial alteration to most Access apps designed to run with a Jet/ACE back end (or even most designed to use SQL Server on a local LAN). There really is no comparison in regard to the cost and difficulty level. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#15
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Stefan Hoffmann wrote in
: After that you can dive into SQL Server Replication which is quite easy to master, but you need some time to get into it. The only reason to do this would be to provide disconnected access. Keep in mind that SQL Server Express limits replication funcationality (though not to the point that it cannot be used for merge replication), so you might have to use a more expensive version of SQL Server. If you're not supporting disconnected editing for your remote users, then you wouldn't need to do this at all. All forms of replication are an order of magnitude more complex than using a database without it. Conflict resolution has to be implemented in some form (you can't ignore it without having your data end up in a de facto corrupted state), and there are certain schema designs that inherently do not work (for example, a self-join with a required field is one that has to be handled very, very carefully). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#16
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Banana Banana@Republic wrote in news:4B87420C.2070209@Republic:
Now, I didn't answer your question about files... That was deliberate as I wanted to emphasize that with SQL Server (and any other server-based RDBMS) you don't really deal with files. You deal with the daemon and the daemon manages the files. One of the issues here is that unlike Jet/ACE, you can't just copy the files to a laptop and use them on the laptop disconnected from your LAN. You have to have an instance of SQL Server running on the laptop, so that any PC where you have SQL Server as local data store is going to be running SQL Server, and this is a potential security risk. Now, in the case where you're not storing any data on the disconnected machines and just using the app when connected to the network (LAN or VPN over Internet), you don't need SQL Server running locally in order to connect to a SQL Server. But if you're contemplating allowing disconnected use, you'll need the SQL Server running on the laptops and you'll probably need to implement SQL Server replication in order to keep the laptop databases synchronized with the central one. Replication is not a minor issue, and if you can avoid it, you really should. But yes, there are files, and in SQL Server, it's .mdf and .ldf which may be stored in a certain folder, depending on how you installed SQL Server. But the only time you actually worry about the file is when you are dealing with backup & restore tasks and even then that is not strictly necessary. In general, backing up the live SQL Server files is not going to give you a reliable backup. It's just like backing up an MDB/ACCDB that is open by a user -- you may or may not get a valid file out of it (it's probably even less likely with the SQL Server files, I would think). SQL Server has a backup agent to take care of backups for you, but one of the disadvantages of SQL Server Express 2008 is that the backup agent is not included! Some backup software is able to talk directly to the SQL Server and get a backup file, but if that is dependent on the agent, it won't work with SQL Server Express. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#17
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
=?Utf-8?B?Sm9uMjI=?= wrote in
: I'm having trouble finding info on linking to tables on a SQL Server database from a computer outside the LAN. Remember, the SQL Server needs to be installed on a permanent server (in a small office, it could theoretically be a workstation, but this is really not advisable, unless nobody ever uses it), and for it to be accessible to remote users, the port on that server that SQL Server is listening for connections on has to be available for the remote users to connect to it. Think of using a web page: when you make am HTTP request, your browser connects on port 80 (by default -- you could request http://dfenton.com:80 to specify it explicitly), which is the default open port for the worldwide web. With a SQL Server, you have to have a port for client PCs to connect to but the big question is where it is available. You could expose it to the public Internet like you do your web server, but that would be extremely dangrous. Instead, it is most common that a SQL Server used for an app like this is made available on the LAN and remote users connect to the LAN across the Internet over a VPN (virtual private network). The security for initiating the VPN connection is usual very high (some VPN client software requires special key authorization and it's all encrypted so the data inside the VPN tunnel cannot be examined by someone as it passes through their servers), and thus it serves as a strong locked door to keep people out of the LAN. The alternative, i.e., making the SQL Server port public, is much more dangerous. Sure, SQL Server has its own authorization (or uses Windows authorization), but historically, there have been lots of security holes that allow people to skirt proper authorization and get into places they shouldn't be. A VPN is harder to exploit in that way. So, think of the VPN as a really long network cable that's connected to the local LAN. You have to "plug it in" in order to see the remote network (this usually involves initiating a connection similar to a dialup connection, with username and password provided), but once the VPN connection is established, you will have a "local" network that is identical to that you'd see if connected by wired cable to the LAN in the "home" office (with some caveats: a sysadmin can configure exactly what a remote user sees and has access to by managing certain configuration parameters, but the general practice is to make the VPN view of the LAN as familiar to the users as possible, i.e., similar to what they can see and connect to when in the office on the wired LAN). All that said, I'd still recommend Windows Terminal Server over VPN instead of upsizing to SQL Server, since to do the former requires no change to your application whatsoever, while upsizing to SQL Server is a pretty involved process (particularly if you need to design it for slow connections from the remote users) -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#18
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Banana Banana@Republic wrote in news:4B87C75A.8050100@Republic:
I do believe that "Server" service is not actually germane to the discussion here. It can also not be turned off without disabling your PC, even if you aren't sharing anything at all. (I tried it with my first copy of NT, as I wasn't sharing any files) -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#19
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
"David W. Fenton" wrote in message
Writing an Access app to run on SQL Server across the Internet means you have to really understand how to design and maintain server-side components (view and stored procedures) and then you have to carefully design your app to use those server-side objects, and you may have to jump through certain hoops (such as using ADO to get editable results from sprocs) that are not compatible with default Access behaviors/designs. I very much agree with your assessment. As a general rule due to having good skills with SQL server, then the issue of learning curve and converting an application to run correctly over a WAN or internet using SQL server is an easy task for me. (so, then sql becomes a cheaper setup). Comparing the learning curve of SQL server to a one time setup of terminal services as you state, I think the terminal services suggestion on your part wins hands down. There really is no comparison in regard to the cost and difficulty level. Cost for me using sql server is less. I have many successful applications running over the Internet using SQL server. However, I done the learning curve. We don't even used stored procedures. We use some pass-through, and mostly link to views for joins on reports or pick lists. I can zero in and get a access application up and running for SQL server in a WAN environment in VERY little time now. I am also using a hosted option for sql server. And, sql Azure can be had for $10 a month for a 1 gig database. I believe the trials are on now, and I can't wait to try Azure. However, WTS vs SQL server in BOTH cases, a server + VPN must be setup. WTS really is far less learning overall. And, one does NOT spend money modifying an already good working access application. So, the larger and more complex the access application, the the more work needed for SQL server. This thus again favors he WTS choice. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#20
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
dude you can't run Linked Tables outside the LAN.
I'm having trouble finding info on linking to tables on a SQL Server database from a computer outside the LAN. These people are just outright lying to you. It works best to move to Access Data Projects. Direct SQL Server is _ALWAYS_ more efficient than linked table BS. That's why 80% of professional developers would never use Access for any reason-- SQL Server direct is just more fun, faster, and easier. SQL Server allows you to do things to your data that would make Jet puke. Jet just plain sucks. File, New, Project (new database), browse for MSDE installation (or SQL Developers Editon) if you have Access 2007, then you can use SQL 2005 Express instead of MSDE 2.0 -Aaron On Feb 25, 8:42*am, Jon22 wrote: I'm close to completing the consolidation of various small Access databases and a couple of Excel spreadsheets that my little company uses (5 staff) into an all encompasing Access database and I was planning on splitting the database when I was finished to allow simaltaneous use of it by staff on our small office network. However it is becoming more and more obvious to me that I am going to need to give staff access to this new database from remote locations (outside of our office network). Two staff members are regularly overseas, one is mainly on the road with her laptop etc etc. I've been playing around with trying to link tables from remote locations (ftp path of the database on our Network Storage Device at the office) and needless to say - that don't work. So I've spent the last 10 hours downloading, installing and trying to get my head around Microsoft SQL Server 2008 Express and how it might help me out. It all started when I came across a very encouraging article on keeping your Access database as your front end application and linking to SQL Server database tables. I'm finding it MUCH more complicated than I had hoped. Can anyone suggest a simpler way of achieving remote access to my database? I have at my disposal a Network Storage Device which has remote access capabilities (this is where the database is currently stored), our website which it could be stored on, or I'd be happy to use my quite powerful workstation at the office as some kind of server. You may have guessed by now that I'm no IT guru by any stretch so please bare this in mind with any suggestions. |
Thread Tools | |
Display Modes | |
|
|