September 19, 2012 at 3:42 am
Just so you know , the access approach works fine when ur dealing with few touchscreen app, once the number of users increase the SSIS implementation is going to be a pain to manage and worse yet could cause all kinds of data management issues.
Also what happens when u upgrade the application ? chances are you will have users with two or three different versions of the access database depending on whether they choose to upgrade or not.
If you want to redesign the system it will be easier to do now , than have it limp around for a few years before finally moving to a scalable rdbms
September 19, 2012 at 5:31 am
You can definitely use Merge Replication if you have SQL Express on your local touch station replicate data back to the central DB. I`ve done it in the past for test equipment running continously and uploading results back to the central DB for global reporting.
You also place a filter on the replication so each touch DB only merges its own data, not capturing everyone elses results. Use the machine name. Works like a charm.
September 19, 2012 at 6:45 am
Hi Yvan / All
Thanks for the replys, I have had a play with SSIS, but having problems, I have created my connections ect but had to install the Access Driver on my SQL 2008 server so it would see my Access accdb but its now complaining about the connection. Also having to create seperate flows for each table and each touchscreen will become a headache.
I am thinking about moving my touch to SQL but does the latest version of Express merge with SQL 2008. Also how heavy is express for PCs as my machines are very basic for the touchscreen system so wont do well with heavy overheads.
Cheers
DJ
September 19, 2012 at 7:06 am
You can still download SQL Express 2008, you don`t have to use 2012 version.
Of course, SQL Express would be one more thing running on the PC but it is not running all the time. The test stations we had were simple Dell desktop where most of them had 2G MEM. Unless there are continuously entering data on the Touch PCs, I am pretty sure it can handle the load, since you were using Access and I guess it is doing the job.
However, you can configure the memory usage to restrict SQL from grabbing too much memory, if you want to. Just look in the SQL Server Properties for that.
September 19, 2012 at 7:10 am
Hi Yvan
Thanks for the info. To be honest I have never used VB.net to write to SQL only Access but its probably only a change of connection strings needed. So I will create another version of my program to write to a local SQL Database and see if I can get the merge working.
The only big question I have is its easy to deploy a blank Access Database with all the tables setup with my application, how do I deploy an SQL Express database with tables ect via my VB.net application as it wont just be a simple case of copying a blank database to each touchscreen unti.
Cheers
DJ
September 19, 2012 at 7:45 am
You have many ways:
Initiallly, you can run the Import Data wizard in SQL which will build and run an SSIS package to import the Access data into SQL, in the central server. This will give you your central DB to set-up the replication.
The cool thing about replication is that you can set it up to push all the schema (tables), include views, stored proc down to the subscriber. So this way, after your first replication is set-up, the only thing you need in the SQL Express engine on the Touch PC is a blank database. Once you add the subscription on the remote SQL Express, the whole thing gets created by the replication process.
Once you have one replication with one Touch PC set-up, use the Generate Script... feature to script all of this. Then use the Subscriber portion of the script, tweak it so it applies to another touch PC (changing the filter for the computer name), create the blank db on the remove touch PC and run the tweaked subscriber script. Repeat for all other touch PC.
Pretty cool stuff once it is setup! 😎
Start by creating one replication with one PC. The rest after is pretty straight-forward.
September 19, 2012 at 7:58 am
Yvan Bouchard (9/19/2012)
You have many ways:Initiallly, you can run the Import Data wizard in SQL which will build and run an SSIS package to import the Access data into SQL, in the central server. This will give you your central DB to set-up the replication.
The cool thing about replication is that you can set it up to push all the schema (tables), include views, stored proc down to the subscriber. So this way, after your first replication is set-up, the only thing you need in the SQL Express engine on the Touch PC is a blank database. Once you add the subscription on the remote SQL Express, the whole thing gets created by the replication process.
Once you have one replication with one Touch PC set-up, use the Generate Script... feature to script all of this. Then use the Subscriber portion of the script, tweak it so it applies to another touch PC (changing the filter for the computer name), create the blank db on the remove touch PC and run the tweaked subscriber script. Repeat for all other touch PC.
Pretty cool stuff once it is setup! 😎
Start by creating one replication with one PC. The rest after is pretty straight-forward.
Yvan, I don't quite understand. See the following link: http://msdn.microsoft.com/en-us/library/ms165686(v=sql.105).aspx
SQL Express can only be a subscriber. DJ's requirement is different. The information flows from the touch devices to the central database not the other way round.
The only way I can see this working is if he goes with SQL Server compact edition 3.5 SP2 (not 4.0) and use merge replication but reconfigure it so nothing flows from central DB to touch device. I'm not even sure that's possible.
DJ has some serious research to do! This isn't going to be easy either way.
---------------------------------------------------------
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 8:39 am
The central Server can pull the data out of the subscriber. As long it uses an account with the proper permissions, everything will merge.
I don`t have access to the script I used right now, but I can share it later tonight - as long as I can find it!
Stay tuned. 🙂
September 19, 2012 at 9:54 am
I had to do this about 10 years ago with an Access (95!) based EPOS system in 10 retail outlets and IBM DB2 system at head office.
We were sending transaction data, gift vouchers data, customer demographic data and stock request/delivery confirmations to HO and getting back Group stock positions, active vouchers and stock issue notifications.
Eventually the solution we came up with was to run a windows scheduled job to extract new records into csv files and give each one a unique name.
StoreIDTableName.SequenceNumber (we were limited to 8.3 names)
These were then FTP'd to the head office server which ran a script to import the files (and validate that there were no missing files - sequential processing was important for us) and then move the processed file to an archive folder.
The same process flow should work for you. The only challenge we had was for one of the files (stock receipt confirmation) we needed the HO system to send back an acknowlegement file that they had received our confirmation and updated our stock levels. If not, then we had to resend it.
September 20, 2012 at 11:55 pm
Since the touchscreens will have thier own local copy of sql express , i would suggest that you build the system to sync on demand instead of creating a replication topology that your not familair with.
There are cases for and against this approach you , need to validated which one meets your requirement.
September 21, 2012 at 6:08 am
Here is the generated script I used.
Take a look at it and replace the server, db names - login etc... accordingly.
It only has 1 table involved in the replication but once created, you may use the SSMS to add more tables,view, stored procs, functions.
The scripts are numbered in the order they should be ran.
The replication is setup at the central server is the publisher and distributor.
Give it a try if you have the time and still wishes to go this way.
Yvan
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply