July 15, 2010 at 5:53 pm
I'm building an Access FE for a SQL Server backend. We're in a situation where we're going to have 40 or so records that I want to available at all times including when the the app or server is down. Basically, its 40 records - 6 or so columns total with information that the operation should have access to at all times.
I'd like to have something in place so that the records are automatically exported when one of them is updated.
Any thoughts?
July 16, 2010 at 8:47 am
i'm not sure I understand the question..
are you looking for ways to do it, or asking if its a good approach?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 16, 2010 at 9:29 am
Both actually. To be specific, is it possible to do something where in a trigger fires a sp that grabs records and then essentially exports them to a text file, html, xml, whatever.
July 16, 2010 at 9:37 am
I don't know your application or backend, so I'll fight my urge to offer advise on better ways to manage DR
For what you're asking, you have some records which are important to you whether the app or server are running, I'm assuming they'll be used as some kind of manual process if everything goes pear shaped.
If thats the case, then yes, there are ways you can export based off a trigger..
if you're ok with xp_cmdshell being active.. In your trigger you could have something like:
EXEC xp_cmdshell 'bcp "SELECT * FROM yourTable WHERE <your where clause>" queryout "C:\drfile.txt" -T -c -t,'
(you might want to check the syntax and test it some, I haven't)
There are probably a ton of better ways to do this, so if someone else posts with something more sensible perhaps we could go that route - I don't like xp_cmdshell active on my own SQL Servers..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 16, 2010 at 10:46 am
Its geared toward providing the users with the information immediately if its not accessible, regardless of for how long. The department needing it is such that when we're busy they're pulling their hair out to begin with and don't have time to sit around and wait - beside they won't wait.
Yes, it is in support of a manual process which if used as the fall back would take some time to get up and running 30 minutes or so, which when we're busy just adds to the overall frustration & stress that they deal with to begin with.
Its a very high stress environment during our peaks.
July 16, 2010 at 10:52 am
I'm with ya.. something a company i worked with did as well.. they had access databases which updated every couple of hours so if the main system went down they could at least print out some reports.
Exporting to a file would work for that kind of situation if its just a stop-gap while your system comes back up - the danger there is that both your app and sql server will go down at the same time - where will the file be? On the same machine?
There are a lot of ways you could deal with this though.. possibly keeping a 2nd sql instance log shipped, or even just a linked 2nd sql instance on a different machine which you update when your main system updates..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 16, 2010 at 10:59 am
david.c.holley (7/15/2010)
I'm building an Access FE for a SQL Server backend. We're in a situation where we're going to have 40 or so records that I want to available at all times including when the the app or server is down. Basically, its 40 records - 6 or so columns total with information that the operation should have access to at all times.
Are we talking about 40 rows in a single table?
How about setting up a second server and doing transactional replication?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 16, 2010 at 11:00 am
How are the records changed? Is it any Access db that updates them?
I'd set up a job that exports this stuff to a known, off server location. That way you can set a proxy for the job, set rights for a share, have it secure. If the table updates, use a trigger to raise a flag and have the job run every minute. If the flag is up, export the data.
July 16, 2010 at 12:57 pm
It is an Access/SQL Server app.
I did think about using the Front End, however the fact that two users on two different machines might update it disqualified that, until a few moments ago.
I had been thinking about getting the information out of sql server and dropping it on the file server that we use for the LAN, which if I'm going to do that I might as well just use the Access front end. The catch though is that the people who need the info is in a separate building from the file server which introduces the added variable of the network connection between the two - thunderstorms in Florida at 3pm daily, not good.
Since the plan is to use an ASPX page to display the information, when everything is up and running. I might do something where the code-behind handles the backup.
We'll be using a PC connected to two monitors to allow the user to enter information where the second monitor will display it all on a board, however the data may be updated from another PC at the same facility. The page will automatically reload every 5 minutes or so.
Since IP address of the PC can be obtained, either dynamically or static, we might be able to do something where the ASPX page streams a file to it.
July 16, 2010 at 1:21 pm
Actually now that I think about it, I'm liking the idea of the ASPX page generating an email and then setting up rules in Outlook which move the message to a specific folder and deletes messages more than (x) hours old. Even though we're on Exchange, once the message is downloaded, its still available locally. To prevent spam, the ASPX page would check its last refresh time aginst the last time any of the records were modified and then send the email if needed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply