SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plans in SQL 2005


Maintenance Plans in SQL 2005

Author
Message
subha.mcts2005
subha.mcts2005
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 282
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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230670 Visits: 46347
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


subha.mcts2005
subha.mcts2005
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 282
we are not maintaining any High availability like replication/Logshipping/mirroing/clustering?

is there any way to create a maintenance plan for No dataloss?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230670 Visits: 46347
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100257 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30651 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38893 Visits: 9283
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
subha.mcts2005
subha.mcts2005
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 282
Hi Every one,

Thank you very much for your valuable Suggestions.

& i will speak with My Sr DBA.

Thanks& Regards
Subha......
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100257 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
jcrawf02
jcrawf02
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5052 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search