Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Multi database to SQL Server at record level Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
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

Post #1358795
Posted Sunday, September 16, 2012 3:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
Hi all

I am till struggling with this one, any ideas on the bet way to handle it?

Cheers

Dj
Post #1359873
Posted Tuesday, September 18, 2012 7:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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

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
Post #1360790
Posted Tuesday, September 18, 2012 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
Can compact edition act as a publisher in replication? I know Express and Web edition are subscriber only, but not done much with compact.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1360804
Posted Tuesday, September 18, 2012 9:21 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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

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
Post #1360855
Posted Tuesday, September 18, 2012 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
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
Post #1360921
Posted Tuesday, September 18, 2012 12:22 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
Douglasjbell (9/18/2012)
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


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

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
Post #1360932
Posted Tuesday, September 18, 2012 12:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
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
Post #1360962
Posted Tuesday, September 18, 2012 1:13 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
Douglasjbell (9/18/2012)
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


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

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
Post #1360979
Posted Tuesday, September 18, 2012 1:20 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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

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
Post #1360985
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse