September 13, 2012 at 12:42 pm
Hi All
I 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.
Cheers
DJ
September 16, 2012 at 3:43 am
Hi all
I am till struggling with this one, any ideas on the bet way to handle it?
Cheers
Dj
September 18, 2012 at 7:43 am
Let me see if I've understood this correctly:
You have your touch screen devices with their own local ACCES DBs containing the three tables:
ACCESS-DB-1
ACCESS-DB-2
and you have a SQL Server 2008 instance with a DB that has the same three tables.
SQLSERVER-2K8
To use SQL Server replication, your SQL Server instance will need to communicate with another SQL Server. Are you able to swap the ACCESS database with SQL Server compact edition?
If this isn't an option then you will have to create your own custom replication. You will need to use timestamps on your records in the access database and have a scheduled job on the SQL Server instance that runs every 2 minutes to check for any record with a timestamp greater than the end time of the previous poll operation. Hope that makes sense.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 18, 2012 at 8:01 am
Can compact edition act as a publisher in replication? I know Express and Web edition are subscriber only, but not done much with compact.
September 18, 2012 at 9:21 am
anthony.green (9/18/2012)
Can compact edition act as a publisher in replication? I know Express and Web edition are subscriber only, but not done much with compact.
Good question....
See this: http://msdn.microsoft.com/en-us/library/ms172417(v=sql.105).aspx
SQL Server Compact supports data replication with SQL Server 2005 and SQL Server 2008 using merge replication and Remote Data Access (RDA).
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 18, 2012 at 11:38 am
Hi
Thanks for the reply, I 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.
Cheers
DJ
September 18, 2012 at 12:22 pm
Douglasjbell (9/18/2012)
HiThanks for the reply, I 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.
Cheers
DJ
It would be nice to get some experts to join in this discussion as I don't want to give wrong suggestions!
Your touch device DBs need to publish the information and your repository db needs to be the subscriber. With SQL server compact edition 3.5 sp2 I believe you can do merge replication (so info flows both ways) but I've never tried it so not sure how it will work.
Have you considered creating your own custom replication process?
As I said previously, you can have a scheduled job that runs every two minutes and checks each db for records that have been inserted or updated. Is this not possible?
Thanks.
P.s
LocalDB does not spurt replication I'm afraid. Even compact edition 4 no longer supports merge replication so it looks like Microsoft don't want this functionality available in this vision anymore!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 18, 2012 at 12:55 pm
Hi Abu
Thanks for the reply, adding timestamps and the such to the tables will not be an issue, and it is only one way synch, Access to SQL as its only a logging system so the clients will not need any info from the main DB.
As I say I am only new to SQL so just looking into your SQL scheduled job. My only concern is that I will have to create a linked server to each Access database then have the job pull the information.
I have a few concerns on this though, if I have 20 databases and the job has to pull data from all these databases each minute, I think the overhead on the SQL server may be to much and effect the main application running on the server.
What happens if a data pull from a database takes longer than 1 minute.
What happens if the SQL job cant see a database.
I would like to try and stay with the access databases on the local systems if possible.
Cheers
Dj
September 18, 2012 at 1:13 pm
Douglasjbell (9/18/2012)
Hi AbuThanks for the reply, adding timestamps and the such to the tables will not be an issue, and it is only one way synch, Access to SQL as its only a logging system so the clients will not need any info from the main DB.
As I say I am only new to SQL so just looking into your SQL scheduled job. My only concern is that I will have to create a linked server to each Access database then have the job pull the information.
I have a few concerns on this though, if I have 20 databases and the job has to pull data from all these databases each minute, I think the overhead on the SQL server may be to much and effect the main application running on the server.
What happens if a data pull from a database takes longer than 1 minute.
What happens if the SQL job cant see a database.
I would like to try and stay with the access databases on the local systems if possible.
Cheers
Dj
All your concerns are valid so forgive me for my advice if it's not appropriate!
If you want to keep the Access DBs then you can create an SSIS package to loop through each touch application DB and poll the required information. You can create an app table on your repository DB whicH will hold the necessary connection details. Your SSIS package can loop through each row in this table to generate the dynamic connection string etc....
As to you other concerns, you can find out more about the error handling capabilities that SSIS offers. It's a great tool and it might just do the job for you.
Good luck!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 18, 2012 at 1:20 pm
Just to clarify on the error handling in SSIS. Because you're able to configure the workflow of the package, you can redirect the package elsewhere so you deal with failed connections.
Sorry if I'm not making much sense but I hope I've said enough to get you to check out this great ETL tool.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 18, 2012 at 1:26 pm
http://msdn.microsoft.com/en-us/library/ms172461%28v=sql.105%29.aspx
looks like sql compact support merge replication based on the above link.
September 18, 2012 at 2:13 pm
Jayanth_Kurup (9/18/2012)
http://msdn.microsoft.com/en-us/library/ms172461%28v=sql.105%29.aspxlooks like sql compact support merge replication based on the above link.
So DJ has some choices to make.
1) Stick with Access and maybe use SSIS to poll the data at regular intervals.
2) switch to SQL Server Compact Edition (3.5 SP2) and use merge replication. (I don't like this option - :ermm:)
I don't see another way with his current setup. What do you think?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 19, 2012 at 12:45 am
Hi
Thanks for the replys.
As the application has already been designed from the ground up for use with the Access database I would rather stick with this as the backend database.
I have had a look at SSIS for SQL 2008 unfortuantly to use the Business Intelligence Development Studio I need to have SQL 2008 Professional which you get the developer edition with and unfortuantly I only have SQL 2008 Standard. I think I am going to have to look into writing my own VB program that sits as a service on the clients PC and updates the SQL Server Manually.
Regards
Dj
September 19, 2012 at 1:08 am
Douglasjbell (9/19/2012)
HiThanks for the replys.
As the application has already been designed from the ground up for use with the Access database I would rather stick with this as the backend database.
I have had a look at SSIS for SQL 2008 unfortuantly to use the Business Intelligence Development Studio I need to have SQL 2008 Professional which you get the developer edition with and unfortuantly I only have SQL 2008 Standard. I think I am going to have to look into writing my own VB program that sits as a service on the clients PC and updates the SQL Server Manually.
Regards
Dj
BIDS is included in Standard Enterprise and Developer editions.
Just a case of installing it from the SQL installation which installs the "shell" version of Visual Studio, or if you already have VS2008 installed, adds in the references to business intelligence projects.
September 19, 2012 at 1:28 am
Excellent, thanks Anthony. I will dig out the SQL software and have a look.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply