December 13, 2013 at 11:38 am
First let me say that even though this question in part pertains to SQL Server 2000 I am posting it on the 2008 forum because I want feedback from people who have stayed current with SQL Server technology and hopefully have had some experience with SQL Server 2000 in the past. Also, most of these questions pertain to all version of SQL Server.
I have a client that is running financial application software whose backbend database is SQL Server 2000 sp4. The application has been running since 2003 and is critical to the company. The company is open 7 days a week and would have problems if their SQL Server database had issues that could not be fixed in a short period time.
Since its installation SQL Server was setup to back up the logs to disk every 2 hours and perform a complete database backup after the close of business every night.
More importantly, after the last backup all the database and log backups are zipped up and copied over a virtual private network to an offside server for disaster recovery purposes. The backups are then restored on a periodic basis to a development machine in order to test the recovery procedure.
The company has determined in the event of a critical database failure they could reproduce the transactions that might occur between the 2 hour log backups.
IT administration of the IT infrastructure is outsourced. The IT department is run by the CFO whose only qualifications for running an IT department is the fact the CFO can spell “IT”.
The system administrator has chosen not to update the server every month after Microsoft releases it patches. Therefore, the only time the server is rebooted is when there is a power failure and the UPS can no longer keep the server running. The server has been known to be up for more than 257 days without patches or restarts.
It appears that there is a known issue with SQL Server 2000 that when the server is restarted the Agent will not launch scheduled jobs unless the SQL Server Agent service is manually restarted.
Last week there was a power failure and the server shutdown. Once the Server was brought back up the scheduled maintenance jobs have not run and are still not running meaning no log or database backups to disk. There is a nightly backup to tape via Backup Exec.
I have requested that the systems administrator restart the SQL Server Agent Service and this was done. However, the scheduled jobs are still not running.
I have tried to get the company to upgrade SQL Server from SQL Server 2000 to a more current version to no avail. The cost is not an issue as the company has the ability to get steep discounts from Microsoft.
I have tried to get the company to resolve the issue with the maintenance jobs not running to no avail.
I would like to pose these questions to people who are much more knowledgeable than I and ask for feedback.
Question #1:
What are the reasons, if any, for this company to seriously consider a SQL Server upgrade from SQL Server 2000?
Question #2:
What are the ramifications from not performing periodic daily log backups and a nightly complete database backup?
Question #3:
Is it sufficient based on the scenario described above to have your only production database backup be a backup once a night via Backup Exec to tape?
Question #4:
Is it possible that the issue with the SQL 2000 Server Agent not running the scheduled maintenance jobs is a precursor to other problems that have not yet been discovered?
Questions #5:
Can anyone suggest what steps should be taken to get the scheduled jobs to run again?
I would appreciate any and all opinions.
I apologize in advance if I have posted this question on the wrong forum or I am not clear in my description above.
Thanks
Howard
December 13, 2013 at 1:22 pm
Question #1:
What are the reasons, if any, for this company to seriously consider a SQL Server upgrade from SQL Server 2000?
2000 is no longer under MS support.
Security vulnerabilities
Performance
Far better reporting
The list can go on for days...
Question #2:
What are the ramifications from not performing periodic daily log backups and a nightly complete database backup?
You can't do any better recovery than yesterday's data. You might loose an entire day of data.
Question #3:
Is it sufficient based on the scenario described above to have your only production database backup be a backup once a night via Backup Exec to tape?
Certainly not. It sounds like the 2 hour window of lost data is the outside of what is acceptable.
Question #4:
Is it possible that the issue with the SQL 2000 Server Agent not running the scheduled maintenance jobs is a precursor to other problems that have not yet been discovered?
Sure it is possible but without more information it is impossible to surmise what happened.
Questions #5:
Can anyone suggest what steps should be taken to get the scheduled jobs to run again?
Start by looking at the job history and see what errors if any there are. Again, without some details it is not really very feasible to offer much advice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2013 at 4:25 pm
I agree with Sean.
I'll also add that, if the company continues to resist the upgrade of a 13 year old product, it's time to get 3 envelopes... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2013 at 5:18 pm
hmbtx (12/13/2013)
Question #1:What are the reasons, if any, for this company to seriously consider a SQL Server upgrade from SQL Server 2000?
Media. Make darn sure you have several copies of the SQL Server 2000 media because you will have a hard time getting it from Microsoft going forward. Already mentioned, security patches, performance, OS patches, OS support (make darn sure you have media for your OS as well because the SQL Server 2000 support window on operating systems is also closing rapidly). Technical support (I'm not sure I could effectively troubleshoot 2000 any more. I haven't touched it for years now). Third party support. The list goes on and on and on and on.
Question #2:
What are the ramifications from not performing periodic daily log backups and a nightly complete database backup?
If the databases are in full recovery and there are no log backups, your system will be offline soon. Have fun. As to the general backup maintenance. Nothing to worry about.[/url] Nothing at all.
Question #3:
Is it sufficient based on the scenario described above to have your only production database backup be a backup once a night via Backup Exec to tape?
No. That makes me very nervous. Backup Exec is notorious for taking backups that it can't restore. Test the restore operation of a couple of the backups to ensure that the version of Backup Exec being used is transaction aware. If not, you could be "backing up" databases that can't be restored. I wrote an article[/url] about this too.
Question #4:
Is it possible that the issue with the SQL 2000 Server Agent not running the scheduled maintenance jobs is a precursor to other problems that have not yet been discovered?
I'd suspect so, yes. But, hard to say.
Questions #5:
Can anyone suggest what steps should be taken to get the scheduled jobs to run again?
Not without more understanding of errors from the Agent log, SQL Server log or the Windows log. We need to know why it's not starting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2013 at 5:20 pm
And I agree with Sean and Jeff too. I didn't look at their answers first. I was just excited by the questions. These are pretty classic types of issues that can lead to data loss.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2013 at 5:07 am
In addtion to the above posts...
Here is the link to start SQL Agent automatically.
http://technet.microsoft.com/en-us/library/aa213810(v=sql.80).aspx
You can also configure mail alert to notify when the SQL Server Agent starts than send e-mail so that you can take care of execution of required jobs.
There is a huge risk running the database on obsolet version which is not supported by Microsoft.
Even though company is open 7 days a week. It is quite possible to migrate the database to newer version smoothly during off peak hours. Considering business do not work 24x7 from your reply as off business hrs you copy all the backup to remote loaction.
I am not sure what is the DR solution in place? If data is important it is must. For remote location you can think of Logshipping or replication so that data loss can be minimal. And for same site you can have database cluster.
Also check and verify the recovery plan agreed by the customer.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply