Multi-computer access to program's data
-
Thanks guys. The sharing of the database would definitely only be behind a firewall, although theoretically possibly on different subnets. I like the quality of not needing File Sharing to work. On SQL Express, someone said it 1)is not "in-process" so there is "a hassle deploying the service, etc" 2)is server-less (said as if it is a negative) What do these things mean?
modified on Saturday, July 25, 2009 12:12 PM
copec wrote:
1)is not "in-process" so there is "a hassle deploying the service, etc"
This means that it doesn't run in the same memory space. I fail to see how this has much to do with deployment. (In short, a DLL usually shares the space, some external app doesn't)
copec wrote:
2)is server-less (said as if it is a negative)
Microsoft Access is "serverless", SQL Express isn't. Sometimes you want something that acts as a server (app is always there, ready to talk to lots of clients) or server-less (a Word-document, or an MSAccess database). (In short; a serverless app writes the data locally, whilst a client would send it to the server, and let the server take care of the writing)
copec wrote:
What do these things mean?
It means that this person probably doesn't like SQL Server. If he's the customer, you'd best investigate an alternative. Tutorials for the most frequently operations can be found here[^], in case you want to take a peek :)
"Some mainframe users still wonder if SQL Server is reliable enough for them. We're a nuclear power plant—how much more reliable do they need it to be?" --Janice Hoerber[^]
-
copec wrote:
1)is not "in-process" so there is "a hassle deploying the service, etc"
This means that it doesn't run in the same memory space. I fail to see how this has much to do with deployment. (In short, a DLL usually shares the space, some external app doesn't)
copec wrote:
2)is server-less (said as if it is a negative)
Microsoft Access is "serverless", SQL Express isn't. Sometimes you want something that acts as a server (app is always there, ready to talk to lots of clients) or server-less (a Word-document, or an MSAccess database). (In short; a serverless app writes the data locally, whilst a client would send it to the server, and let the server take care of the writing)
copec wrote:
What do these things mean?
It means that this person probably doesn't like SQL Server. If he's the customer, you'd best investigate an alternative. Tutorials for the most frequently operations can be found here[^], in case you want to take a peek :)
"Some mainframe users still wonder if SQL Server is reliable enough for them. We're a nuclear power plant—how much more reliable do they need it to be?" --Janice Hoerber[^]
Thanks for the info, Eddy. So, I think I understand. a) using a db with server capability is what front-end/back-end means. They are independent and have to talk to one another (using tools like ADO.NET and LINQ to SQL?) b) there can be multiple clients that connect to a back-end, which is a db with server capabilities c) the db's server ability handles the read, write, change, etc calls from a front-end and sends back whatever is asked for d) if I want to allow more than one PC to connect to the same db, I need a db with server capability Is that right? I'm guessing that choosing a server-capable db means more complicated coding of the program, but would it also mean more difficult deployment (for me) or installation (for the end users), or are there other negatives? Is there any worth to using serverless now (for ease) but coding in such a way that transitioning to server-capable later isn't too bad? Thanks for the link. It's bookmarked and I'll be spending hours there it looks like.
Eddy Vluggen wrote:
It means that this person probably doesn't like SQL Server.
He did recommend SQL Server Compact edition, but said that it is serverless. (I tried to confirm this at the MS website but couldn't tell either way) If they're coreect and I understand what you've said, this person must've overlooked that I would want to be able to have multiple clients.
modified on Saturday, July 25, 2009 2:16 PM
-
Thanks for the info, Eddy. So, I think I understand. a) using a db with server capability is what front-end/back-end means. They are independent and have to talk to one another (using tools like ADO.NET and LINQ to SQL?) b) there can be multiple clients that connect to a back-end, which is a db with server capabilities c) the db's server ability handles the read, write, change, etc calls from a front-end and sends back whatever is asked for d) if I want to allow more than one PC to connect to the same db, I need a db with server capability Is that right? I'm guessing that choosing a server-capable db means more complicated coding of the program, but would it also mean more difficult deployment (for me) or installation (for the end users), or are there other negatives? Is there any worth to using serverless now (for ease) but coding in such a way that transitioning to server-capable later isn't too bad? Thanks for the link. It's bookmarked and I'll be spending hours there it looks like.
Eddy Vluggen wrote:
It means that this person probably doesn't like SQL Server.
He did recommend SQL Server Compact edition, but said that it is serverless. (I tried to confirm this at the MS website but couldn't tell either way) If they're coreect and I understand what you've said, this person must've overlooked that I would want to be able to have multiple clients.
modified on Saturday, July 25, 2009 2:16 PM
copec wrote:
a) using a db with server capability is where the concept of front-end/back-end comes from b) there can be multiple clients that connect to a back-end, which is a db with server capabilities c) the db's server ability handles the read, write, change, etc calls from a front-end and sends back whatever is asked for
I don't know if that's the origin of the word, but this is indeed, broadly, what a database does.
copec wrote:
d) if I want to allow more than one PC to connect to the same db, I need a db with server capability
In general, yes :)
copec wrote:
I'm guessing that choosing a server-capable db means more complicated coding of the program, but would it also mean more difficult deployment (for me) or installation (for the end users), or are there other negatives?
There are several variables. One thing to consider is that a server is an actual application that is "always" running when the computer is powered on. That can be a bit of a performance-impact. I haven't written much installation-scripts lately, but I guess that it's as easy to install as the .NET Framework itself, just ticking the box being a prerequisite. The central question should be whether you need a database-server (as a shared data-repository) or a local datastore (a private data-repository) Microsoft Access has a friendly interface, and is often abused as a server. You'll find lots of rants on Access, because it's widely used. Best thing of all is that there's an upsize-wizard that upsizes the tables of your Access-database to SQL Express/Server, once you're in need of such a migration.
-
copec wrote:
a) using a db with server capability is where the concept of front-end/back-end comes from b) there can be multiple clients that connect to a back-end, which is a db with server capabilities c) the db's server ability handles the read, write, change, etc calls from a front-end and sends back whatever is asked for
I don't know if that's the origin of the word, but this is indeed, broadly, what a database does.
copec wrote:
d) if I want to allow more than one PC to connect to the same db, I need a db with server capability
In general, yes :)
copec wrote:
I'm guessing that choosing a server-capable db means more complicated coding of the program, but would it also mean more difficult deployment (for me) or installation (for the end users), or are there other negatives?
There are several variables. One thing to consider is that a server is an actual application that is "always" running when the computer is powered on. That can be a bit of a performance-impact. I haven't written much installation-scripts lately, but I guess that it's as easy to install as the .NET Framework itself, just ticking the box being a prerequisite. The central question should be whether you need a database-server (as a shared data-repository) or a local datastore (a private data-repository) Microsoft Access has a friendly interface, and is often abused as a server. You'll find lots of rants on Access, because it's widely used. Best thing of all is that there's an upsize-wizard that upsizes the tables of your Access-database to SQL Express/Server, once you're in need of such a migration.
Thanks again Eddy.
Eddy Vluggen wrote:
The central question should be whether you need a database-server (as a shared data-repository) or a local datastore (a private data-repository)
For now, for sure, I will only need a local datastore, but I can see that I would like to be able to (later) add the ability to have multiple client front-ends for one the datastore. Can I develop the first version in such a way that I allow myself the ability to later "plug in" multi-client ability with a minimum of rewriting, etc.?
-
Thanks again Eddy.
Eddy Vluggen wrote:
The central question should be whether you need a database-server (as a shared data-repository) or a local datastore (a private data-repository)
For now, for sure, I will only need a local datastore, but I can see that I would like to be able to (later) add the ability to have multiple client front-ends for one the datastore. Can I develop the first version in such a way that I allow myself the ability to later "plug in" multi-client ability with a minimum of rewriting, etc.?
copec wrote:
Can I develop the first version in such a way that I allow myself the ability to later "plug in" multi-client ability with a minimum of rewriting, etc.?
Yes, hence the suggestion to start with a new project in Microsoft Access[^]. This is a local database that can easily be migrated[^] to it's bigger SQL-brother - moving the data to the server, and letting the reports fetch their data from there. And you're welcome, off course :)
-
copec wrote:
Can I develop the first version in such a way that I allow myself the ability to later "plug in" multi-client ability with a minimum of rewriting, etc.?
Yes, hence the suggestion to start with a new project in Microsoft Access[^]. This is a local database that can easily be migrated[^] to it's bigger SQL-brother - moving the data to the server, and letting the reports fetch their data from there. And you're welcome, off course :)
I must not have mentioned it already: I have had this db runnning in Access for over three years and have now decided to write it into a stand alone program. I'm researching and reading and trying to figure out what components and tools to use. I've narrowed the language to VB.NET or C# with a WPF gui. I think the only main part left to narrow and decide is which SQL db to use. That's why I'm asking about the possibility/feasibility of using a local-only db for now but writing the program, etc, in preparation for the multi-client capable db later. Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later? Thanks again.
-
I must not have mentioned it already: I have had this db runnning in Access for over three years and have now decided to write it into a stand alone program. I'm researching and reading and trying to figure out what components and tools to use. I've narrowed the language to VB.NET or C# with a WPF gui. I think the only main part left to narrow and decide is which SQL db to use. That's why I'm asking about the possibility/feasibility of using a local-only db for now but writing the program, etc, in preparation for the multi-client capable db later. Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later? Thanks again.
copec wrote:
Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later?
Not quite; it's not as simple as throwing a switch. It is, however, quite easy to upsize. That wizard makes it a few-clicks operation to migrate your data to SQL. The way you'd talk to them is also very similar. When you want data from Microsoft Access, you'd typical run a query over a
connectionto fetch that data. With SQL Express/Server, you'd also run a SQL-query over aConnectionto fetch data. In both cases you'd be using aConnection-object to connect to the database, running a query and reading the results. The main difference is that Access is specialized in fetching/updating the data in a local database, or at least with not all-too-much clients. SQL Express is the larger brother, living on a server, always present while the computer runs, ready to update it's datastore :)Just introduced to 'Southern Comfort'
modified on Saturday, July 25, 2009 8:58 PM (not GMT?)
-
copec wrote:
Are you saying that I can use any serverless SQL-style db (including Access) now and it won't take too much coding to transition and add the multi-client ability later?
Not quite; it's not as simple as throwing a switch. It is, however, quite easy to upsize. That wizard makes it a few-clicks operation to migrate your data to SQL. The way you'd talk to them is also very similar. When you want data from Microsoft Access, you'd typical run a query over a
connectionto fetch that data. With SQL Express/Server, you'd also run a SQL-query over aConnectionto fetch data. In both cases you'd be using aConnection-object to connect to the database, running a query and reading the results. The main difference is that Access is specialized in fetching/updating the data in a local database, or at least with not all-too-much clients. SQL Express is the larger brother, living on a server, always present while the computer runs, ready to update it's datastore :)Just introduced to 'Southern Comfort'
modified on Saturday, July 25, 2009 8:58 PM (not GMT?)
-
Thanks. Given the similarity, does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I wouldn't state it like that, but that's what a lot of people did. Many of them started out with the combination Access/VB6 and have moved to SQL Server/C# in the meantime. Reading from the datastore will be roughly the same, but you might run into a concurrency-problem once a lot of people start meddling around with the data. You'd have to decide what happens when "user one" is changing the countryname of the Northpole to "New Southpole" and "user two" is (at the same time) changing this name to "Old Northpole". That's rather a logical problem that you'll encounter with any database. Most of them provide locking-mechanisms, even Access (to some degree). Some of the older applications have been converted to webapplications, utilizing ASP.NET/SQL (or PHP/MySQL). Those applications can often be used from every country, introducing differences in measurement (metric vs imperial), differences in the formatting of numbers and dates (and some locations on daylight saving time) and such.
copec wrote:
does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I'm not saying that it's not a concern, but that a lot of developers have faced these challenges before, and the problems that you're likely to encounter will probably be well-documented.
-
I wouldn't state it like that, but that's what a lot of people did. Many of them started out with the combination Access/VB6 and have moved to SQL Server/C# in the meantime. Reading from the datastore will be roughly the same, but you might run into a concurrency-problem once a lot of people start meddling around with the data. You'd have to decide what happens when "user one" is changing the countryname of the Northpole to "New Southpole" and "user two" is (at the same time) changing this name to "Old Northpole". That's rather a logical problem that you'll encounter with any database. Most of them provide locking-mechanisms, even Access (to some degree). Some of the older applications have been converted to webapplications, utilizing ASP.NET/SQL (or PHP/MySQL). Those applications can often be used from every country, introducing differences in measurement (metric vs imperial), differences in the formatting of numbers and dates (and some locations on daylight saving time) and such.
copec wrote:
does that mean that while I code the first version it's not worth concerning myself with the later addition of multi-client capability?
I'm not saying that it's not a concern, but that a lot of developers have faced these challenges before, and the problems that you're likely to encounter will probably be well-documented.
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
-
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
copec wrote:
If so, the program won't open and gives the person a message
That might be a bit frustrating for the user. Especially if they don't know for how long they have to wait.
copec wrote:
it totally does away with the concurrent editing problem.
That's true :)
-
copec wrote:
If so, the program won't open and gives the person a message
That might be a bit frustrating for the user. Especially if they don't know for how long they have to wait.
copec wrote:
it totally does away with the concurrent editing problem.
That's true :)
Eddy Vluggen wrote:
Especially if they don't know for how long they have to wait.
The message would tell them that they have to wait until the other instance on the other computer is closed. It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
-
Eddy Vluggen wrote:
Especially if they don't know for how long they have to wait.
The message would tell them that they have to wait until the other instance on the other computer is closed. It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
copec wrote:
It would be in their house, maybe the other instance is opened by their wife or husband. Sounds ok, no?
The wife? Perhaps it's a good idea to put a password on it? :laugh: Well, it wouldn't be as much a problem as it would be in a full office. It would be frustrating if there's "some" co-worker using it, blocking you. But that's less of a problem at home.
-
Ok, I won't worry too much, but I think I'll go ahead and use a db that works for both purposes. As for the concurrency issue, I'm thinking to avoid the issue all together I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else. If so, the program won't open and gives the person a message like "Program is open on another computer. Close the other instance and try again." I'll have to allow for stale lock data somehow though. But it totally does away with the concurrent editing problem.
copec wrote:
I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else
How are you going to do that? You would have to be able to check for what machines are attached to the network and for each machine check which processes it is running to see if it is running your program. What would happen if two users on different machines simultaneously start your program? This may not be a problem on a small home network, but if you go commercial it could be.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
-
copec wrote:
I would have it so that when a person tries to open the program, the program checks to see if anyone else already has it open anywhere else
How are you going to do that? You would have to be able to check for what machines are attached to the network and for each machine check which processes it is running to see if it is running your program. What would happen if two users on different machines simultaneously start your program? This may not be a problem on a small home network, but if you go commercial it could be.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
riced wrote:
How are you going to do that?
Of course I don't know (yet, I hope), but I thought there would be some way the program could find out as it opens if the db was already opened by another instance of the program. Since it is a home-use app, it wouldn't be necessary to know which computer had it open, just that another one did. Am I wrong about the feasibility/possiblity of this?
modified on Monday, July 27, 2009 2:29 PM
-
riced wrote:
How are you going to do that?
Of course I don't know (yet, I hope), but I thought there would be some way the program could find out as it opens if the db was already opened by another instance of the program. Since it is a home-use app, it wouldn't be necessary to know which computer had it open, just that another one did. Am I wrong about the feasibility/possiblity of this?
modified on Monday, July 27, 2009 2:29 PM
copec wrote:
find out as it opens if the db was already opened by another instance of the program
For Access you could check for the existence of a .ldb file in the same directory as the .mdb. This would tell you that the db is in use (the .ldb is used by Access to control locking and removed when the last user closes the db). For SQL Server I think you could query the master..syslogins table but I'm not absolutely certain. There may be easier ways to do this.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
-
copec wrote:
find out as it opens if the db was already opened by another instance of the program
For Access you could check for the existence of a .ldb file in the same directory as the .mdb. This would tell you that the db is in use (the .ldb is used by Access to control locking and removed when the last user closes the db). For SQL Server I think you could query the master..syslogins table but I'm not absolutely certain. There may be easier ways to do this.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis