October 19, 2010 at 1:15 am
Hi,
My Sr DBA has given this Task,
This is very Critical Database in Production. No Dataloss is Must.
prepare maintenance plan for this.
what are the constraints to follow the logically create a maintenance plan for New Database?
plz help me!
October 19, 2010 at 1:25 am
If there is absolutely no data loss allowed under any circumstances, then a maintenance plan, no matter what is in it, is not sufficient. You'll need a substantial DR plan, redundant hardware, etc, etc, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2010 at 1:30 am
we are not maintaining any High availability like replication/Logshipping/mirroing/clustering?
is there any way to create a maintenance plan for No dataloss?
October 19, 2010 at 5:28 am
subha.mcts2005 (10/19/2010)
we are not maintaining any High availability like replication/Logshipping/mirroing/clustering?
Then there's not a chance in hell of achieving 0 data loss no matter what the disaster situation. What happens if an entire server fails? What happens if a RAID array fails? What happens if the entire server room looses power? etc...
is there any way to create a maintenance plan for No dataloss?
As I said, achieving 0 data loss takes a lot more than just a maint plan. It takes a lot of hardware resources, planning, testing, etc. I'm sure your senior DBA knows that.
You do need to set up maintenance plans (note plural) for any database, but alone with no other investments at best it can help you with minimal data loss (assuming appropriate failover resources), not 0.
What maint plans you set up and how you configure them depends on the size of the DB, the maintenance windows, allowable downtime in the case of a necessary restore, etc. I suggest you sit with your senior DBA and discuss this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2010 at 6:01 am
The name "Maintenance Plan" pretty much defines what you're going to be building. A set of routines for maintaining your database and server. This is not the same thing as preparing a disaster recovery scenario or implementing any of the various means of high availability. So the request you were given is at odds with what's possible.
That said, as far as maintaining the system goes, you should schedule regular consitency checks and backups. How often is dependent on the system, the size of the databases in question, etc. For example, my team maintains somewhere in the neighborhood of a 1,000 plus databases on about 25 production servers. None of the databases are very large (biggest is around 250gb I think). We run DBCC checks nightly because it's effectively cost free. We also run nightly backups (after the DBCC checks, so we only back up databases that are consistent). In addition, we have, on most databases, but not all, regularly scheduled log backups. This will help us reduce the possibility of data loss (but not eliminate it as your admin has suggested). Again, here, you can vary the schedule. We have some instances that are running every 10 minutes, some every 15 and some every 30. It just depends on the system and the data needs.
In addition to all this, we also have routines that defrag/rebuild indexes based on the level of fragmentation and number of pages in the index. This runs daily (again, because our systems are small). We also update statistics daily, sampled. There are a couple of systems where we have a statistics update job that runs every 30 minutes on a particular table or set of tables. Once more, this completely depends on the needs of your system.
All this will help prevent data loss. Without a full set of DR and HA systems in support of these maintenance routines, you cannot get to a point where you effectively eliminate 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
October 19, 2010 at 6:12 am
A "maintenance plan" can produce sqlserver backups, but there's more to a Disaster Recovery Procedure than just a valid backup.
Search SSC for "Disaster Recovery planning" and it will come up with a couple of threads that can help you get on track.
Work out a decent plan to suit your business, present it to whoever is in charge and get approval for your implementation, including maintenance windows, actual down time procedures for recovery and their timings, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2010 at 6:19 am
In addition to the maintenance plans listed above, you should also code a job that checks file sizes verses available disk space to make sure you aren't running out of hard drive. Your server will crash (or at least SQL Server will not start) if the entire hard drive gets filled out by data and log files.
Other thoughts are to run regular DBCC CheckDB commands, restore your backups on a Dev or sandbox server to ensure the backups are valid (this can be automated) and schedule file cleanup jobs to archive off old ftp or text files.
EDIT: But as Gail said, no maintenance plan takes the place of a Disaster Recovery plan. There is no guarantee that maintenance plans will not lose data.
October 19, 2010 at 6:29 am
Hi Every one,
Thank you very much for your valuable Suggestions.
& i will speak with My Sr DBA.
Thanks& Regards
Subha......
October 19, 2010 at 7:03 am
Brandie, EXCELLENT point on testing backups. That is something we do, but I forgot to mention it. It's a must.
Just a side note, a Microsoft consultant told me that all I needed to use was the checksums for validation of the backup, but I still prefer doing the full restore.
"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
October 19, 2010 at 8:11 am
Grant Fritchey (10/19/2010)
Brandie, EXCELLENT point on testing backups. That is something we do, but I forgot to mention it. It's a must.Just a side note, a Microsoft consultant told me that all I needed to use was the checksums for validation of the backup, but I still prefer doing the full restore.
Check out Paul Randal's 30 myths document, he threw them all into a pdf, top of page 26:
http://www.sqlskills.com/blogs/paul/CommonSQLServerMyths.pdf
17f) page checksums detect corruption immediately
This myth was suggested for debunking by fellow MVP Gail Shaw (twitter|blog) and is of course untrue. A damaged page cannot be detected until it is read into memory and the buffer pool checks the validity of the page checksum.
I don't really know nuthin', but that seems to me that the checksum alone won't do it.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 19, 2010 at 9:52 am
jcrawf02 (10/19/2010)
Grant Fritchey (10/19/2010)
Brandie, EXCELLENT point on testing backups. That is something we do, but I forgot to mention it. It's a must.Just a side note, a Microsoft consultant told me that all I needed to use was the checksums for validation of the backup, but I still prefer doing the full restore.
Check out Paul Randal's 30 myths document, he threw them all into a pdf, top of page 26:
http://www.sqlskills.com/blogs/paul/CommonSQLServerMyths.pdf
17f) page checksums detect corruption immediately
This myth was suggested for debunking by fellow MVP Gail Shaw (twitter|blog) and is of course untrue. A damaged page cannot be detected until it is read into memory and the buffer pool checks the validity of the page checksum.
I don't really know nuthin', but that seems to me that the checksum alone won't do it.
I'm not sure it's right to trust restore alone either. Restoring into a sandbox and then running DBCC CheckDB on the restored database gives that extra little bit of reassurance.
And so far as guaranteeing no data loss: I don't care how good your maintenance is, how good your disaster recovery plan is, how good your hardware redundancy is, or anything else, it is just plain fact that (until you can repeal the laws of physics) guaranteed zero data loss is not possible. (The most obvious natural law preventing it is the exclusion of instantaneous communication between two separate locations.)
edit: put a slash into a [/i] tag that didn't have one
Tom
October 19, 2010 at 10:18 am
must agree... guaranteed 0 data loss is not possible, there are always cases/situations that will break everything.
However a maintenance job is not going to come anywhere close to giving you any sort of real security DR wise!
October 19, 2010 at 11:39 am
Grant Fritchey (10/19/2010)
Just a side note, a Microsoft consultant told me that all I needed to use was the checksums for validation of the backup, but I still prefer doing the full restore.
Checksum on database backup - yes kinda. Once you have that a restore verifyonly actually does check and verify that the backup is undamaged (and hence restorable)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2010 at 12:45 pm
GilaMonster (10/19/2010)
Grant Fritchey (10/19/2010)
Just a side note, a Microsoft consultant told me that all I needed to use was the checksums for validation of the backup, but I still prefer doing the full restore.Checksum on database backup - yes kinda. Once you have that a restore verifyonly actually does check and verify that the backup is undamaged (and hence restorable)
I'm still not comfortable with being able to say, "Yes, this backup can be restored" until I've done it.
"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
November 5, 2010 at 1:52 am
Here's a nice WP from SQLCAT that highlights about high availability and mentions the major topics a SLA/DRP should have:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply