|
| Iniciado por guest, 20,ene. 2016 09:48 - 5 respuestas |
| |
| | | |
|
| |
| Publicado el 20,enero 2016 - 09:48 |
Hi Everyone,
I m after a bit of advice on how to tackle a project i am working on.
A customer uses an off the shelf software package for their everyday running of the business, the software's database is SQL Server.
I have created a program that links to the SQL server database so they can extract and manipulate data further, i am not allowed to write to the SQL server DB or create tables etc so u i have created an HyperfileSQL database and change connections, but the SQL server tables are a mess IMO, to get the information the users needs i have to create complex queries which slows it down and changing connections all the time is proving erroneous.
So i had the idea:
a sync program that runs on the server and copies the SQL Server database into the HyperfileSQL DB
What i have done is created 3 queries in the SQL Server database which pretty much gets me all the data i want and created 3 tables in the HyperfileSQL DB which mirrors the structure of the queries.
My question is do you think this a good way and also whats the best way to get the data from the SQL queries into the HyperfileSQL tables,
The obvious way is to create a memory table that runs the SQL query then a procedure that changes connection and checks each line of the table deciding if its hadd/hmodify, this seems slow to me so was hoping if someone had a better solution?
any help appreciated
thanks
ISO |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,enero 2016 - 10:33 |
| Hi, first thought: a separate Windows Service could take care of the sync between SQL Server and HFSQL. I don't know MS SQL Server at all, but with your customer there should be "logging" possible, a log file which contains added / changed and removed records in the database. (e.g. http://www.techrepublic.com/blog/the-enterprise-cloud/help-my-sql-server-log-file-is-too-big/ ) This would separate A and B completely by only reading / interpreting the log file. Next, mirroring. Imho, mirroring of an MS SQL Server database ist possible too. Question is whether your customer wants to have that, it will slow down the server a bit. Maybe, you could access the mirror directly then, just a thought. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,enero 2016 - 11:30 |
Hi all,
On SQL Server you can have DB mirroring (between SQL servers). The way to implement that is to activate log shipping from one SQL server db to the other.
Cheers,
Peter Holemans |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,enero 2016 - 11:45 |
Hi Iso,
It's dificult the help you with only this information but i will try.
Do you need the data in real time?
How big you your data?
How often are you going to sync?
You don't need to change the connection all the time, you can run selects against the mssql db using the hquerywithoutcorrection sintax.
You can create views in the mssql db to get the data you need and run then using WD or WB. If you can't change the existing DB you can create a new one and create the views in it just use db_name.dbo.table_name (dbo is only one sample check the owner of the tables). |
| |
| |
| | | |
|
| | |
| |
| Publicado el 27,enero 2016 - 10:51 |
| Thanks for all your advice guys :spos: |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,enero 2016 - 10:02 |
| If they will allow you to create views on the SQL box, that's a good way forward. |
| |
| |
| | | |
|
| | | | |
| | |
|