SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi database to SQL Server at record level


Multi database to SQL Server at record level

Author
Message
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 70
Hi all

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

Cheers

Dj
Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3325
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24266 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3325
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
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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
Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3325
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
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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
Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3325
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
Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3325
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search