Alternatives to SQL Backups
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/alternativestosqlbackups.asp>http://www.sqlservercentral.com/columnists/awarren/alternativestosqlbackups.asp
-
Andy did add the caveat that MS Access is not the answer to all problems, but personally I am sick and tired of resolving locking problems on SQL Server caused by MS Access users on the databases. If I had my way every copy of Access in the building would be burned, and then we could have a query tool with a bit better idea about resource locking!
-
Excellent article! It really brings out a very real issue that is overlooked by most -- backups don't protect you from everything. As you say at the end of the article, the job of the DBA is to protect the data and make sure it is available. In my experience user error is the biggest cause of data loss.
Another thing to consider is a policy-based solution. Who should have ad-hoc query access in the first place? Are developers truly testing and DML statements they are writing? What is *their* responsibility for data loss?
All to often I see an environment end-users and developers have the ability to change or delete data but no accountability for mistakes they may make to production or QA environments. I think it's the responsibility of the DBA to protect the data and ensure its availability, but I don't believe that this is an entirely *technical* issue.Policy needs to be set to establish guildlines for who has what level ad-hoc query access and to define accountability for altering or deleting data.
Two cents.
-
The thought of having users who can update data using SQL statements scares me.
I don't mind them doing selects but I have serious issues with non-user interface updates of data.
I worked in an environment where the users needed to recode entries within a data warehouse. The recoding depended on the varying beliefs of the marketing department so it couldn't be built into a standard package.
Every month I took a snapshot of the main datawarehouse and replicated it to another server so the marketing department could do whatever they wanted to the data.
There was a procedure in place so that the person who cocked it up had to notify their manager, who would then have to sign off a restore. Sounds bureaucratic but in practice it was quick to operate.
The advantages were
- Responsibility for said cock up was clearly visible
- The manager of the department was responsible for saying "over-write all our work", or getting their staff to correct the error.
- The main environment was protected.
Strangely enough, after implementing the policy the number of mistakes diminished dramatically.
LESSON:- Where possible make people responsible for clearing up their own mess.
-
Cant disagree that having users issuing direct updates is...well, less than optimal? In my case these users are reasonably well trained and are taught to be cautious. In last two years, less than a handful of incidents. The upside is that mgmt understands the risk. I still get stuck with fixing the problems though!
Andy
-
Very good article. I actually have been using Access to back up SQL tables, and it has saved me A LOT of time. Andy's suggestion is very relevant to what I do: I develop for a small company that has a software product which uses an MS Access 2000 front end with a SQL Server 2000 back end. My focus has been on data processing, and I built a small form for backing up tables in Access where I can mark the checkbox next to any of 7 commonly backed up tables, type in the name of another table if I want, and enter a label for the backup. It backs up the tables I've chosen with the source table name, date, and label making up the name of the backup. This practice has been very helpful for me and has saved me a lot of time.
-
Andy, Keep pushing the limits! Gotta agree with you, there is more to data integrity than just nightly backups. As for solutions, as you have indicated, it all depends. Concurency issues, skill of users, sophistication of the application (audit tables), etc. does make a good case for purchasing Log Explorer.
What's the business problem you're trying to solve? -
I kinda agree with you that when it comes to databases it all comes down to who's to blame. It's almost usually the DBA cause the one who did the mistake would say, "He Gave me Access". That's why there shouldn't be any "power user" other than the actual DBA. If it all gonna come on you like 1000 bricks it might as well be "your" mistake.
Gerald Nelson MCP/MCDBA
Gerald Nelson MCP/MCDBA -
Thanks to all who've posted. Pleasantly surprised by the reeeption, mentioning Access on a SQL site is usually a recipe for tar and feathers:-)
Andy
-
It is very helpful to me to "hear" a variety ideas about a variety of failure scenarios. Even if it is an idea that I don't adopt for whatever reason, it gets me thinking about the all the issues of keeping production systems running. My motto is "Murphy never sleeps." Very good article and hope for more contribution along these lines from the authors and other interested parties.
-
Personally, I dislike Access. Maybe that's an understatement. I hate Access and would like to personally burn a few copies. But Andy brings up a good point, as do most ofthe other posters.
I hate having people have SQL access. I fix bunches of problems just because they have that access and don't pay attention. I'm pretty conservative and careful and I still make mistakes. Fortuneately I've learned to do similar things to this to protect myself.
An interesting solution to a problem people don't often think about. 'Course, I don't allways mind the extra downtime. Gives me ammunition to revoke rights
Steve Jones
-
I used to use Access (still do for simple manual tracking).
I have ran into this problem in large and small shops. I have found the following to be the best solution:
Revoke ALL rights to the SQL server(s). Grant EXEC to stored-procedures that have been approved by the DBA team. This gives the DBA team the ability to know HOW data is accessed, WHO approved the code, WHY it was needed, WHEN the process is ran. 😀
All you need to do is ensure that the SP owner is same as TABLE.
OR create a replicated server and GRANT SELECT ONLY
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply