| Author |
Topic |
|
1druid1
Starting Member
4 Posts |
 Posted - 2012-09-17 : 17:08:01
|
| Hi AllI have created a touchscreen data collection system that stores its information on local Access 2007 databases. The reason for storing the information locally and not direct to the SQL server is to protect the data from Network Downtime. The touchscreens must still work even if the network is down.Now what I would like to do is be able to push or pull the data from the access databases to our back end SQL 2008 server say every couple of minutes, if the Network is down then the push / pull gets ignored until the database / sql server can see each other. This must be at the record level as some records may change within the 2 minutes between data push / pulls. At the moment I only have 2 touchscreen units but if successful this may be rolled out to the plant meaning I can have as many as 20 - 30 units. The database only has 3 tables and the information is linked by an id field via the Touchscreen. The tables themselves are not linked.Is it possible to create a replication on my SQL server to look at each access database, pull any new data and update any records that have been changed? I would like the SQL server to do the pull as it will reduce overhead on the touchscreen units although linking each remote database will be a pian.Hope you can help.CheersDJ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-17 : 17:18:55
|
| No, you can't use replication with Access, but you could create an SSIS package.-Chad |
 |
|
|
1druid1
Starting Member
4 Posts |
Posted - 2012-09-17 : 18:06:43
|
| HiThanks for the replys. I have never used SSIS before or seen it to be honest, I am very new to SQL, it was only installed with a product that uses it as a backend, but I will have a look and see what it entails. As for linked servers, 1 or 2 databases may be ok but if the testing goes ok then we could be talking 10 - 20 databases and trying to pull information from all of these databases every minute, wouldnt this cause a lot of overhead on the server and degrade server performance?CheersDj |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 18:17:33
|
quote:
Originally posted by 1druid1
HiThanks for the replys. I have never used SSIS before or seen it to be honest, I am very new to SQL, it was only installed with a product that uses it as a backend, but I will have a look and see what it entails. As for linked servers, 1 or 2 databases may be ok but if the testing goes ok then we could be talking 10 - 20 databases and trying to pull information from all of these databases every minute, wouldnt this cause a lot of overhead on the server and degrade server performance?CheersDj
are you looking at near to real time kind of solution? do you really need sync up every minute?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
1druid1
Starting Member
4 Posts |
Posted - 2012-09-18 : 02:46:44
|
| HiYes it needs to be synched as close to real time as possible as there will be a monitoring system that uses the information to display on large displays in the depratments that must be up to date, to be honest even a minute may be to much of a delay. The only reason I am having to create the local databases as one of our departments uses a wan link which is outwith my control and its not the fastest or most stable.CheersDJ |
|
|
|
1druid1
Starting Member
4 Posts |
Posted - 2012-09-18 : 13:44:01
|
| HiI have just been reading about SQL 2012 free LocalDB that can be deployed with an application. This looks like a very good database for my application instead of Access 2007. However as my knowledge of SQL is very limited, can I use an application written in VS 2005 to read and write to a 2012 LocalDb instance and can the information in this DB be mergerd or replicated to a full 2008 sql server as per my origional post.CheersDJ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 13:58:42
|
| You might want to look at the MS Sync Framework: http://msdn.microsoft.com/en-us/sync/bb736753.aspxIt's designed specifically for scenarios like you describe. Whether it's more work to implement I can't say, but it doesn't rely on things like SSIS. |
|
|
|
|
Multi Access 2007 dbs to SQL 2008