Database Replication (2k)

Topic

New Reply

Does anyone have any experience with database replication with Access 2k?
White papers on Microsoft’s site make is seem relatively painless and, dare I say it, easy. What are the caveats and challenges?
At this point we would expect to have the original database and one other one with which to synch/replicate.
Is it time to seriously consider SQL or will database replication meet meager needs?

FYI – database to be replicated has about 80 tables, and 2500 records with expected addition of 500-1000 records per year.

Any experience and recommendations is GREATLY appreciated.

Ken

Reply | Quote

Replies

You need to supply more information on the reasons for considering replication. Given the size of the database and the number of records being added, SQL Server sounds like total overkill. However, replication requires a certain amount of maintenance. Are the machines on a network, is one a laptop, or what? Is the database split into front and back end files? I wouldn’t recommend replicating a front end, which requires much more maintenance than simple back end replication.

Reply | Quote

Thank you Charlotte! I agree on the “overkill”, but these corporate IT folks only like to talk SQL and Oracle.

Better defining the “system”.
We have an excellent custom built Access 2k based system for tracking incidents and occurrances. Each workstation has an mde front end linked to an mdb backend on a Windows 2000 server. We now want to get corporate folks out of the data entry mode, and more in the role of oversight and reporting. In turn allow the remote sites to enter their own data. The remote sites have access to a common server, but not to the corporate server where the existing database resides. The idea is to have a means of synchronizing the two databases. The maximum number of expected concurrent users would be 5 on the remote system with normally just one person in the system. The maximum expected concurrent users in the corporate system is 3, with one person expected in the corp system most of the time.

There is no plan to replicate front end mde’s, as they work fine as is.

Does this help? Again, I am looking for challenges that others have experienced in using the replication feature, and an idea of what to expect in the overall scope of the project.

Thanks VERY much Charlotte.

Have a great day!

Ken

Reply | Quote

I can add a little more detail on this situation (since I’m involved…. ). Ken’s client has an Access 2000 database, which works just fine and dandy on it’s own. I built an .asp page, which allows them to view/edit/add data to a portion of this database, from the web. The problem lies in the old ‘IT distrust’ of Access. The setup required (currently) for the .asp page, is to have the database ON the web server. This allows for the tightest possible IIS security, and also allows for virtually unlimited web users. (theoretically speaking, of course).

The IT folks don’t want to give the web server access to their network. Duh. Why they don’t want to give their internal users access to a share on the webserver is beyond me, in fact, I am going to be contacting their IT department in a bit, to find out. That leaves SQL Server (which gives IT that false warm fuzzy), or replication, where the db would be on both the webserver, and the network.

Personally, I think it should stay on the webserver, and then just create a share that the users at the corporate site can see. I have a strong feeling though that they are going to want to push it into SQL Server though.

I hope this explains things a bit more.

Ken, I really recommend we stay away from Replication. Yes, the white papers make it sound easy. But like it has been pointed out, there are a lot of pitfalls, which Microsoft is more then happy to gloss over!

Reply | Quote

Unfortunately, that is true of SQL Server as well, Drew. And unlike Access, SQL Server really requires a DBA to administer is. I assume the IT people are going to provide that??

Reply | Quote

I believe they already have a SQL Server. They just want the tables put into it. We have an Oracle database where I work, and it actually requires very little ‘maintenance’, per se. Our boss is supposed to be the ‘expert’, but my co-worker and I have figured everything out so far, that needs to get done with it.

Reply | Quote

All I can say, Drew, is that the SQL Server databases I’ve worked with and the Oracle databases I encountered in my last contracting job were not maintained by anyone except a trained dba who knew the products and understood how to program and manipulate them. Doing it on a seat of the pants basis may work … for awhile or for a long time. It isn’t when it works that is the problem, it’s when it doesn’t!

Reply | Quote

I may not be a certified DBA, but I like to think I can hold my own! I know what you mean. Our Oracle db is a backend to our production database, which has a frontend written in a language called PROIV. It’s called Glovia, and is an ‘off the shelf’ production database. Personally, I think it’s a pain in the . We don’t use it’s reporting engine, instead, we have a HUGE Access database called Glovia Reports, which runs the various reports that the upper echelon want. I have written a handful of utilities to automate processes also. Quite frankly, the Oracle db requires no maintenance, the only issues we have ever run into are user locks, when a user forcefully closes the front end (which communicates through telnet).

We have a SQL Server too, but have never used it, because, quite frankly, I have yet to have a project land on my lap, that I couldn’t handle with Access, VB, and/or ASP. In all honesty, getting into the web based front ends, with an .mdb as the backend practically eliminates any need for a server side db. In my not so humble opinion. Any who, I’ve contacted Ken’s client’s IT guy, and hopefully will get this situation sorted out.

Reply | Quote

I have to agree with Charlotte on SQL Server. We work with it frequently, and yes, you may get by for a long time (years) without needing any maintenance. Then you get something like the Blaster worm that some clever chap devises, and the wheels fall off and you NEED a trained DBA. Besides, the IT folks are likely to decide in this case that they need to have a SQL Server replicated database for local people to work on since they won’t let users look at the one on the IIS.

When do you need SQL Server (or Oracle, DB2, or whatever)?

• When you have a mission critical database that needs to be available 24/7.
• When you need recovery right up to the point of a crash (logging).
• When the size exceeds Access limits.
• When you need the engine to enforce complex data validation or relationship rules.
• When you want to track changes with an audit trail.
• When you have performance needs that Access can’t handle – i.e. 100 users hammering at a database.[/list]

Reply | Quote

Wendell and Charlotte,

Thanks for the excellent feedback. You have prompted a few questions.

RE:Maintenance of SQL
The client has a well trained staff of IT professionals, have SQL running for several applications. My question now is “is the maintainence issue of SQL” that you mention relating to SQL, or to the specific database? In this instance if we “upsize” the access tables to SQL what are the primary considerations? If the situation demands either an Access database replication model, or an SQL table conversion (assuming the client has inhouse SQL expertise) which is the better route?

RE: Wendells criteria
W>When you have a mission critical database that needs to be available 24/7.
Mission Critical? Probably not. 24/7 absolutely

W> When you need recovery right up to the point of a crash (logging).
Not a huge issue, considering the limited amount of data at this point.

W>When the size exceeds Access limits.
Not in my lifetime.

W>When you need the engine to enforce complex data validation or relationship rules.
Not in the existing Access database. Security and server permissions is the primary reason client has given for requiring SQL. They got hit hard during the latest blaster and SOBIG episode and have changed their policies and permissions.

W>When you want to track changes with an audit trail.
Not required for this app.

W>When you have performance needs that Access can’t handle – i.e. 100 users hammering at a database.
Not expected.

FYI – in another life, I used to sell and help develop niche market Foxpro apps. In those days, corporate IT gurus wanted Oracle or they didn’t want to play. It didn’t have anything to do with whether their database needs required Oracle. They only wanted Oracle and that was that. These days, they want Oracle or SQL, of which the extent of my knowledge on either does not exceed how to spell them. Though at one time I think I knew what the SQL acronym meant.

Obviously Oracle and SQL offer these folks something they need. My question is, do they ALWAYS _need_ these high end databases for every application or is it a matter of convenience?

Thanks again for ideas and opinions.

Ken

Reply | Quote

If the client has an IT staff that includes SQL Server dbas, then that base is covered. However, with SQL Server, don’t expect to be able to “tweak” the structure or change things at short notice because the IT people will require time to schedule the changes and perfrom all the arcane rituals that go with getting anything done by IT.

The answer to your NEED question is NO, they don’t. However, they FEEL like they do, never mind that SQL Server had a huge security hole that was hammered by a worm. Desktop databases simply don’t need SQL Server, but IT people like the sense of control they get from having the critical part of the application under their authority for security reasons. While I can understand that, it makes it tough on people who just need to get the job done NOW, not in a couple of months when a SQL programmer becomes available or the IT department can schedule creation of a new server for the back end and allocate the server space.

Reply | Quote

Charlotte and Wendell,

You are guyes are awesome. I do appreciate your comments. I was afraid that in the myst that is my ignorance of the high end databases that I was overlooking something. To me it seemed like overkill when these IT folks required Oracle or SQL. It is reassuring to hear from well respected and admired professionals like you that I was not full of it.

It seems everyone is in agreement on this. SQL it is, unless we can convince client that Access will meet their needs. Don’t hold your breath.

Is converting the tables to SQL a no brainer? Or, are there challenges to address? For example, I seem to recall reading someplace that converting Access tables to SQL does not bring the indexes with them. Are there any limitations that apply to SQL that don’t apply to Access? I mean Access seems designed for the masses and the common person, allowing a lot of nonstandard “stuff”. SQL designed by professionals for professionals (who have lots of money). Fear of the unknown here I think.

Have a great day!

Reply | Quote

Actually, the Upsizing Wizard does a pretty good job most of the time – but be sure you download the latest version if you are going to SQL 2000. It will tell you if a tables doesn’t upsize, and will sometimes give you a reason. In general you should get the indexes. Another alternative is to use the DTC utility with SQL Server – it can read and import Access .mdb files. The only gotcha’s I know about are pretty arcane. For one thing, autonumbers work a bit differently in that the number isn’t assigned until you actually save the record. That can be a problem in code if you are trying to do things on BeforeInsert events and need the autonumber value. Another is that Date/Time values in SQL Server aren’t quite as precise from a time perspective as Access values. But we frequently just upsize, and then use the SQL Server ODBC driver with linked tables, and that seems to work well.

Reply | Quote

Actually, the Upsizing Wizard does a pretty good job most of the time – but be sure you download the latest version if you are going to SQL 2000. It will tell you if a tables doesn’t upsize, and will sometimes give you a reason. In general you should get the indexes. Another alternative is to use the DTC utility with SQL Server – it can read and import Access .mdb files. The only gotcha’s I know about are pretty arcane. For one thing, autonumbers work a bit differently in that the number isn’t assigned until you actually save the record. That can be a problem in code if you are trying to do things on BeforeInsert events and need the autonumber value. Another is that Date/Time values in SQL Server aren’t quite as precise from a time perspective as Access values. But we frequently just upsize, and then use the SQL Server ODBC driver with linked tables, and that seems to work well.

Reply | Quote

Charlotte and Wendell,

You are guyes are awesome. I do appreciate your comments. I was afraid that in the myst that is my ignorance of the high end databases that I was overlooking something. To me it seemed like overkill when these IT folks required Oracle or SQL. It is reassuring to hear from well respected and admired professionals like you that I was not full of it.

It seems everyone is in agreement on this. SQL it is, unless we can convince client that Access will meet their needs. Don’t hold your breath.

Is converting the tables to SQL a no brainer? Or, are there challenges to address? For example, I seem to recall reading someplace that converting Access tables to SQL does not bring the indexes with them. Are there any limitations that apply to SQL that don’t apply to Access? I mean Access seems designed for the masses and the common person, allowing a lot of nonstandard “stuff”. SQL designed by professionals for professionals (who have lots of money). Fear of the unknown here I think.

Have a great day!

Reply | Quote

If the client has an IT staff that includes SQL Server dbas, then that base is covered. However, with SQL Server, don’t expect to be able to “tweak” the structure or change things at short notice because the IT people will require time to schedule the changes and perfrom all the arcane rituals that go with getting anything done by IT.

The answer to your NEED question is NO, they don’t. However, they FEEL like they do, never mind that SQL Server had a huge security hole that was hammered by a worm. Desktop databases simply don’t need SQL Server, but IT people like the sense of control they get from having the critical part of the application under their authority for security reasons. While I can understand that, it makes it tough on people who just need to get the job done NOW, not in a couple of months when a SQL programmer becomes available or the IT department can schedule creation of a new server for the back end and allocate the server space.

Reply | Quote

In addition to Charlotte’s comments, which are spot on, if you have to choose between replication and SQL Server, I would opt for SQL Server since they already have several other SQL apps. The care and feeding of one more database isn’t a big deal in that situation. But I still think a single Access database on the IIS server is the best answer if you need to make occasional design changes at the table level.

Reply | Quote

In addition to Charlotte’s comments, which are spot on, if you have to choose between replication and SQL Server, I would opt for SQL Server since they already have several other SQL apps. The care and feeding of one more database isn’t a big deal in that situation. But I still think a single Access database on the IIS server is the best answer if you need to make occasional design changes at the table level.

Reply | Quote

Wendell and Charlotte,

Thanks for the excellent feedback. You have prompted a few questions.

RE:Maintenance of SQL
The client has a well trained staff of IT professionals, have SQL running for several applications. My question now is “is the maintainence issue of SQL” that you mention relating to SQL, or to the specific database? In this instance if we “upsize” the access tables to SQL what are the primary considerations? If the situation demands either an Access database replication model, or an SQL table conversion (assuming the client has inhouse SQL expertise) which is the better route?

RE: Wendells criteria
W>When you have a mission critical database that needs to be available 24/7.
Mission Critical? Probably not. 24/7 absolutely

W> When you need recovery right up to the point of a crash (logging).
Not a huge issue, considering the limited amount of data at this point.

W>When the size exceeds Access limits.
Not in my lifetime.

W>When you need the engine to enforce complex data validation or relationship rules.
Not in the existing Access database. Security and server permissions is the primary reason client has given for requiring SQL. They got hit hard during the latest blaster and SOBIG episode and have changed their policies and permissions.

W>When you want to track changes with an audit trail.
Not required for this app.

W>When you have performance needs that Access can’t handle – i.e. 100 users hammering at a database.
Not expected.

FYI – in another life, I used to sell and help develop niche market Foxpro apps. In those days, corporate IT gurus wanted Oracle or they didn’t want to play. It didn’t have anything to do with whether their database needs required Oracle. They only wanted Oracle and that was that. These days, they want Oracle or SQL, of which the extent of my knowledge on either does not exceed how to spell them. Though at one time I think I knew what the SQL acronym meant.

Obviously Oracle and SQL offer these folks something they need. My question is, do they ALWAYS _need_ these high end databases for every application or is it a matter of convenience?

Thanks again for ideas and opinions.

Ken

Reply | Quote

We hashed this over on the AccessD list. All of those points are relative. Access can be used for 24/7 mission critical systems, it can be recovered (a SQL Server can crash just as hard as an Access Db. You loose too many drives, it doesn’t matter if it’s logging stuff or not!), size limits can be worked around (though I think it’s the strongest reason to move to a Server side db), data validation and relationship rules can be handled by the front end, when would you ‘need’ it to be handled by the engine? You can track your own changes, and create your own audit trail, and Access can handle 100 users, if built right, ESPECIALLY if it’s on a web server.

Reply | Quote

Well, I hate to argue about it, but . . .

In 10 years of working with SQL Server, I’ve seen one corruption, and that was caused by a turkey who pulled out software RAID drives and then put them back in a different order. On the other hand, I’ve seen numerous Access databases go corrupt during that time, and have to be restored. So in my book, if stability is an issue, the nod goes to SQL.

The same thing is true if you are retrieving a few records from a table with several million records – SQL will do it in milliseconds, while Access has to pass the entire table to the Jet engine to find the appropriate records, which even on a 100Mbit LAN can take a while.

As to the value of a log, you can put it on a different server or different drive. Major catastrophes such as fires or earthquakes require you to have an off-site backup, while disk failures hardly ever involve more than one drive, and controller failures may take down one RAID cluster at most, so I still have a backup with no lost transactions.

Yes, you can create a front-end with audit trails and complex validation, but why expend the time and energy to do that when triggers can do it for you (I agree that validation should be done on the form in general – tell the user before you ever try to save the record). And with audit trails done in the front-end, the minute someone gets into the database at the table level and starts making changes, your audit trail is history.

Access can handle lots of users – we’ve been as high as 125 – but the performance can get pretty slow, and it’s pretty tough to get a web interface to offer the same functional behavior that you get with subforms and performance will never be quite as good as a native Access form.

All that aside, in this case, I would certainly recommend Access, though it doesn’t appear that putting the tables in SQL Server would be a huge hardship, other than the issue of making table design changes.

Reply | Quote

Not really arguing, just voicing my opinion. I certainly respect yours, though I would like to make a correction to what you said. JET DOESN’T pull the entire table across, to find a few records. If the table is indexed, then it reads the index, and it actually is ‘smart’ about that too, because it knows the sequence of the index, so it only has to pull parts of the index. This of course is dependant on solid db design.

All of my statements are based on that. A crappy developer is going to fail horribly with Access….but then again, they would do the same with SQL Server, probably too!

My most active database has had 185 concurrent users in it. I redesigned the Front End later, though, so that it rarely sees more then 20 to 30 at once. It had a corruption issue, once a week for about 4 or 5 weeks, due to a faulty NIC on someone’s docking station.

I must say that I don’t fault server side db’s, they certainly have their place, and definitely have advantages, I just constantly get hit with systems that are just plain poorly designed, and the urge is to upgrade the backend, instead of FIXING the db!

Reply | Quote

Not really arguing, just voicing my opinion. I certainly respect yours, though I would like to make a correction to what you said. JET DOESN’T pull the entire table across, to find a few records. If the table is indexed, then it reads the index, and it actually is ‘smart’ about that too, because it knows the sequence of the index, so it only has to pull parts of the index. This of course is dependant on solid db design.

All of my statements are based on that. A crappy developer is going to fail horribly with Access….but then again, they would do the same with SQL Server, probably too!

My most active database has had 185 concurrent users in it. I redesigned the Front End later, though, so that it rarely sees more then 20 to 30 at once. It had a corruption issue, once a week for about 4 or 5 weeks, due to a faulty NIC on someone’s docking station.

I must say that I don’t fault server side db’s, they certainly have their place, and definitely have advantages, I just constantly get hit with systems that are just plain poorly designed, and the urge is to upgrade the backend, instead of FIXING the db!

Reply | Quote

Well, I hate to argue about it, but . . .

In 10 years of working with SQL Server, I’ve seen one corruption, and that was caused by a turkey who pulled out software RAID drives and then put them back in a different order. On the other hand, I’ve seen numerous Access databases go corrupt during that time, and have to be restored. So in my book, if stability is an issue, the nod goes to SQL.

The same thing is true if you are retrieving a few records from a table with several million records – SQL will do it in milliseconds, while Access has to pass the entire table to the Jet engine to find the appropriate records, which even on a 100Mbit LAN can take a while.

As to the value of a log, you can put it on a different server or different drive. Major catastrophes such as fires or earthquakes require you to have an off-site backup, while disk failures hardly ever involve more than one drive, and controller failures may take down one RAID cluster at most, so I still have a backup with no lost transactions.

Yes, you can create a front-end with audit trails and complex validation, but why expend the time and energy to do that when triggers can do it for you (I agree that validation should be done on the form in general – tell the user before you ever try to save the record). And with audit trails done in the front-end, the minute someone gets into the database at the table level and starts making changes, your audit trail is history.

Access can handle lots of users – we’ve been as high as 125 – but the performance can get pretty slow, and it’s pretty tough to get a web interface to offer the same functional behavior that you get with subforms and performance will never be quite as good as a native Access form.

All that aside, in this case, I would certainly recommend Access, though it doesn’t appear that putting the tables in SQL Server would be a huge hardship, other than the issue of making table design changes.

Reply | Quote

We hashed this over on the AccessD list. All of those points are relative. Access can be used for 24/7 mission critical systems, it can be recovered (a SQL Server can crash just as hard as an Access Db. You loose too many drives, it doesn’t matter if it’s logging stuff or not!), size limits can be worked around (though I think it’s the strongest reason to move to a Server side db), data validation and relationship rules can be handled by the front end, when would you ‘need’ it to be handled by the engine? You can track your own changes, and create your own audit trail, and Access can handle 100 users, if built right, ESPECIALLY if it’s on a web server.

Reply | Quote

I have to agree with Charlotte on SQL Server. We work with it frequently, and yes, you may get by for a long time (years) without needing any maintenance. Then you get something like the Blaster worm that some clever chap devises, and the wheels fall off and you NEED a trained DBA. Besides, the IT folks are likely to decide in this case that they need to have a SQL Server replicated database for local people to work on since they won’t let users look at the one on the IIS.

When do you need SQL Server (or Oracle, DB2, or whatever)?

• When you have a mission critical database that needs to be available 24/7.
• When you need recovery right up to the point of a crash (logging).
• When the size exceeds Access limits.
• When you need the engine to enforce complex data validation or relationship rules.
• When you want to track changes with an audit trail.
• When you have performance needs that Access can’t handle – i.e. 100 users hammering at a database.[/list]

Reply | Quote

I may not be a certified DBA, but I like to think I can hold my own! I know what you mean. Our Oracle db is a backend to our production database, which has a frontend written in a language called PROIV. It’s called Glovia, and is an ‘off the shelf’ production database. Personally, I think it’s a pain in the . We don’t use it’s reporting engine, instead, we have a HUGE Access database called Glovia Reports, which runs the various reports that the upper echelon want. I have written a handful of utilities to automate processes also. Quite frankly, the Oracle db requires no maintenance, the only issues we have ever run into are user locks, when a user forcefully closes the front end (which communicates through telnet).

We have a SQL Server too, but have never used it, because, quite frankly, I have yet to have a project land on my lap, that I couldn’t handle with Access, VB, and/or ASP. In all honesty, getting into the web based front ends, with an .mdb as the backend practically eliminates any need for a server side db. In my not so humble opinion. Any who, I’ve contacted Ken’s client’s IT guy, and hopefully will get this situation sorted out.

Reply | Quote

All I can say, Drew, is that the SQL Server databases I’ve worked with and the Oracle databases I encountered in my last contracting job were not maintained by anyone except a trained dba who knew the products and understood how to program and manipulate them. Doing it on a seat of the pants basis may work … for awhile or for a long time. It isn’t when it works that is the problem, it’s when it doesn’t!

Reply | Quote

I believe they already have a SQL Server. They just want the tables put into it. We have an Oracle database where I work, and it actually requires very little ‘maintenance’, per se. Our boss is supposed to be the ‘expert’, but my co-worker and I have figured everything out so far, that needs to get done with it.

Reply | Quote

Unfortunately, that is true of SQL Server as well, Drew. And unlike Access, SQL Server really requires a DBA to administer is. I assume the IT people are going to provide that??

Reply | Quote

I can add a little more detail on this situation (since I’m involved…. ). Ken’s client has an Access 2000 database, which works just fine and dandy on it’s own. I built an .asp page, which allows them to view/edit/add data to a portion of this database, from the web. The problem lies in the old ‘IT distrust’ of Access. The setup required (currently) for the .asp page, is to have the database ON the web server. This allows for the tightest possible IIS security, and also allows for virtually unlimited web users. (theoretically speaking, of course).

The IT folks don’t want to give the web server access to their network. Duh. Why they don’t want to give their internal users access to a share on the webserver is beyond me, in fact, I am going to be contacting their IT department in a bit, to find out. That leaves SQL Server (which gives IT that false warm fuzzy), or replication, where the db would be on both the webserver, and the network.

Personally, I think it should stay on the webserver, and then just create a share that the users at the corporate site can see. I have a strong feeling though that they are going to want to push it into SQL Server though.

I hope this explains things a bit more.

Ken, I really recommend we stay away from Replication. Yes, the white papers make it sound easy. But like it has been pointed out, there are a lot of pitfalls, which Microsoft is more then happy to gloss over!

Reply | Quote

Thank you Charlotte! I agree on the “overkill”, but these corporate IT folks only like to talk SQL and Oracle.

Better defining the “system”.
We have an excellent custom built Access 2k based system for tracking incidents and occurrances. Each workstation has an mde front end linked to an mdb backend on a Windows 2000 server. We now want to get corporate folks out of the data entry mode, and more in the role of oversight and reporting. In turn allow the remote sites to enter their own data. The remote sites have access to a common server, but not to the corporate server where the existing database resides. The idea is to have a means of synchronizing the two databases. The maximum number of expected concurrent users would be 5 on the remote system with normally just one person in the system. The maximum expected concurrent users in the corporate system is 3, with one person expected in the corp system most of the time.

There is no plan to replicate front end mde’s, as they work fine as is.

Does this help? Again, I am looking for challenges that others have experienced in using the replication feature, and an idea of what to expect in the overall scope of the project.

Thanks VERY much Charlotte.

Have a great day!

Ken

Reply | Quote

You need to supply more information on the reasons for considering replication. Given the size of the database and the number of records being added, SQL Server sounds like total overkill. However, replication requires a certain amount of maintenance. Are the machines on a network, is one a laptop, or what? Is the database split into front and back end files? I wouldn’t recommend replicating a front end, which requires much more maintenance than simple back end replication.

Reply | Quote

In addition to Charlotte’s comments, what problem do you hope to solve with replication? There are a number of downsides to replication, not the least of which is the bloat in database size that it causes. Other issues are the difficulty in removing it if you discover you don’t really need it down the road, the added complexity if you discover you need to make table design changes, and the managment of the synchronization process when you have conflicts. Just a few of the things they don’t tell you in the marketing material!

All that aside, there are situations where it really is useful. The one we’ve seen most frequently is where a person travels with a laptop and needs to carry a database with them. We have a client who has used that situation, as well as having one remote employee 1000 miles away, but from time to time we get called in to resolve problems. You might also take a look at the tutorial on replication on our website.

As to SQL Server, replication works somewhat differently, and is far more complex than it’s Access cousin, although you can synchronize Access and SQL Server databases. I would base any decision on moving to SQL Server on the basis of the number of simultaneous users, desired response times, and the criticality of your database. Hope this helps.

Reply | Quote

In addition to Charlotte’s comments, what problem do you hope to solve with replication? There are a number of downsides to replication, not the least of which is the bloat in database size that it causes. Other issues are the difficulty in removing it if you discover you don’t really need it down the road, the added complexity if you discover you need to make table design changes, and the managment of the synchronization process when you have conflicts. Just a few of the things they don’t tell you in the marketing material!

All that aside, there are situations where it really is useful. The one we’ve seen most frequently is where a person travels with a laptop and needs to carry a database with them. We have a client who has used that situation, as well as having one remote employee 1000 miles away, but from time to time we get called in to resolve problems. You might also take a look at the tutorial on replication on our website.

As to SQL Server, replication works somewhat differently, and is far more complex than it’s Access cousin, although you can synchronize Access and SQL Server databases. I would base any decision on moving to SQL Server on the basis of the number of simultaneous users, desired response times, and the criticality of your database. Hope this helps.

Reply | Quote