Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maintenance Plans in SQL 2005 Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 1:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 20, 2012 10:01 PM
Points: 76, 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!
Post #1006743
Posted Tuesday, October 19, 2010 1:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1006752
Posted Tuesday, October 19, 2010 1:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 20, 2012 10:01 PM
Points: 76, 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?

Post #1006758
Posted Tuesday, October 19, 2010 5:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1006864
Posted Tuesday, October 19, 2010 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1006884
Posted Tuesday, October 19, 2010 6:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:03 AM
Points: 6,748, Visits: 8,544
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1006894
Posted Tuesday, October 19, 2010 6:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1006899
Posted Tuesday, October 19, 2010 6:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 20, 2012 10:01 PM
Points: 76, Visits: 282
Hi Every one,

Thank you very much for your valuable Suggestions.

& i will speak with My Sr DBA.

Thanks& Regards
Subha......
Post #1006906
Posted Tuesday, October 19, 2010 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
Post #1006931
Posted Tuesday, October 19, 2010 8:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
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."
Post #1006989
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse