<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
Multi database to SQL Server at record level
26 posts, Page 1 of 3
1
2
3
»
»»
Multi database to SQL Server at record level
Rate Topic
Display Mode
Topic Options
Author
Message
Douglasjbell
Douglasjbell
Posted Thursday, September 13, 2012 12:42 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20,
Visits: 60
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
Douglasjbell
Douglasjbell
Posted Sunday, September 16, 2012 3:43 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20,
Visits: 60
Hi all
I am till struggling with this one, any ideas on the bet way to handle it?
Cheers
Dj
Post #1359873
Abu Dina
Abu Dina
Posted Tuesday, September 18, 2012 7:43 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484,
Visits: 2,122
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.
-----------------------------------
http://www.SQL4n00bs.com
Post #1360790
anthony.green
anthony.green
Posted Tuesday, September 18, 2012 8:01 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Abu Dina
Abu Dina
Posted Tuesday, September 18, 2012 9:21 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484,
Visits: 2,122
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).
-----------------------------------
http://www.SQL4n00bs.com
Post #1360855
Douglasjbell
Douglasjbell
Posted Tuesday, September 18, 2012 11:38 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20,
Visits: 60
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
Abu Dina
Abu Dina
Posted Tuesday, September 18, 2012 12:22 PM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484,
Visits: 2,122
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!
-----------------------------------
http://www.SQL4n00bs.com
Post #1360932
Douglasjbell
Douglasjbell
Posted Tuesday, September 18, 2012 12:55 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:03 AM
Points: 20,
Visits: 60
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
Abu Dina
Abu Dina
Posted Tuesday, September 18, 2012 1:13 PM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484,
Visits: 2,122
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!
-----------------------------------
http://www.SQL4n00bs.com
Post #1360979
Abu Dina
Abu Dina
Posted Tuesday, September 18, 2012 1:20 PM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 484,
Visits: 2,122
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.
-----------------------------------
http://www.SQL4n00bs.com
Post #1360985
« Prev Topic
|
Next Topic »
26 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
