Mirroring

[jadn]

Humble Greetings,
I'm a programmer - new to DBing.
Through ODBC, we're using an Access .mdb as our primary DB but plan to migrate to a more robust SQL server eventually. So far my application uses ADO/ODBC and simple SQL statements.

To be brief, I'd like to "mirror" [certain tables on] a "remote" DB "locally". A few tables should not be mirrored locally. Those tables, and only those tables, are changed locally - their contents flushed to the Primary DB, whenever it's available.

Are methods for "mirroring" implementation-specific (MySQL vs Oracle vs MsAccess)? Can I "brute-fore" this with a reltively primitive set of SQL commands?

This question is longer than I wanted - any help/insight is appreciated!

Regards

 

[mrdenny]

So your plan will be to have SQL installed on all the users machines? Then the users will update the tables locally, then when the network is available the changes will be uploaded into the main server?

In SQL Server you'd use merge replication to handle this. The setup and config for each database will be totally different and for the most part you aren't going to be able to cross database platofrm boundries to make this work. At least not without a lot of manual coding on your part.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jadn]

Hi Denny - thanks for replying,

Re: "... plan will be to have SQL installed on all the users machines"
I'm not sure - do I need "SQL installed"? Right now my applications can connect to ODBC "Datasources" ("File DSN") - yet no explicit SQL install has been done.

Re:
"... the users will update the tables locally, then when the network is available the changes will be uploaded into the main server?
".
Yes

Thanks for SQL Server "merge replication" keywords - Sounds like the right way to go.

Cheers

 

[mrdenny]

If you are going to have the users edit the records offline on there local machine you will need some sort of database to hold the records as well as an interface to connect to the database with to edit the records.

I'd recommend an MSDE or SQL 2005 Express Edition install with SQL 2000/2005 Standard Edition as the main server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jadn]

Hi Denny,
"you will need some sort of database to hold the records as well as an interface to connect to the database with to edit the records."

... yes, well, my app. presently, writes directly to a .mdb (through the ODBC/DSN connection) - but it might just as well be a MySQL - server, I believe. It'd be nice to keep the workstation(/local) DBs as .mdbs (as they are now), even after upgrading the primary(/remote) DB to an SQL Server.

Thanks/Cheers.

 

[mrdenny]

If you want to have different platforms on each side (MS SQL on the server and Access on the workstations/laptops) then getting replication to work will be much harder to do.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com