Does anyone have a link to resources or suggestions on best practices for
setting up proper maintenance plans with SQL 2005 (Workgroup)?
We have a new 2005 database that we are preparing for production and want to
make sure we cover off all the bases in setting up our maintenance plan.
This will be for an online reservation system that will process approx
300-600 transactions per day. The system needs to be live 24/7/365 or as
near to this as possible.
The initial database is expected to be around 600MB is size with approx 40
tables and grow from there with new transactions.
What we be a good plan for such a system for a starting point? Is a single
daily plan sufficient or should we be setting up multiple plans to handle
backups, tuning, shrinking, etc separately?
Any suggestions/comments would be appreciated.What ever you do don't shrink the database. See here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
How often you do backups are dependent on how much data you can afford to
loose. While this is for 2000 the principles still hold true.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Andrew J. Kelly SQL MVP
"ML" <schooner@.accesswave.ca> wrote in message
news:%23abfUZG2HHA.728@.TK2MSFTNGP05.phx.gbl...
> Does anyone have a link to resources or suggestions on best practices for
> setting up proper maintenance plans with SQL 2005 (Workgroup)?
> We have a new 2005 database that we are preparing for production and want
> to make sure we cover off all the bases in setting up our maintenance
> plan.
> This will be for an online reservation system that will process approx
> 300-600 transactions per day. The system needs to be live 24/7/365 or as
> near to this as possible.
> The initial database is expected to be around 600MB is size with approx 40
> tables and grow from there with new transactions.
> What we be a good plan for such a system for a starting point? Is a
> single daily plan sufficient or should we be setting up multiple plans to
> handle backups, tuning, shrinking, etc separately?
> Any suggestions/comments would be appreciated.
>
>|||On Aug 6, 1:13 pm, "ML" <schoo...@.accesswave.ca> wrote:
> Does anyone have a link to resources or suggestions on best practices for
> setting up proper maintenance plans with SQL 2005 (Workgroup)?
> We have a new 2005 database that we are preparing for production and want to
> make sure we cover off all the bases in setting up our maintenance plan.
> This will be for an online reservation system that will process approx
> 300-600 transactions per day. The system needs to be live 24/7/365 or as
> near to this as possible.
> The initial database is expected to be around 600MB is size with approx 40
> tables and grow from there with new transactions.
> What we be a good plan for such a system for a starting point? Is a single
> daily plan sufficient or should we be setting up multiple plans to handle
> backups, tuning, shrinking, etc separately?
> Any suggestions/comments would be appreciated.
There is this: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx|||"ML" <schooner@.accesswave.ca> wrote in message
news:%23abfUZG2HHA.728@.TK2MSFTNGP05.phx.gbl...
> Does anyone have a link to resources or suggestions on best practices for
> setting up proper maintenance plans with SQL 2005 (Workgroup)?
> We have a new 2005 database that we are preparing for production and want
> to make sure we cover off all the bases in setting up our maintenance
> plan.
> This will be for an online reservation system that will process approx
> 300-600 transactions per day. The system needs to be live 24/7/365 or as
> near to this as possible.
> The initial database is expected to be around 600MB is size with approx 40
> tables and grow from there with new transactions.
> What we be a good plan for such a system for a starting point? Is a
> single daily plan sufficient or should we be setting up multiple plans to
> handle backups, tuning, shrinking, etc separately?
> Any suggestions/comments would be appreciated.
>
Thank you for the comment so far. I think we will start off with daily full
backups and transaction log backups every 15 mins.
For the database integrity check, is it best to run this before or after a
full backup?
In terms of the other maintenance tasks (rebuild indexes, update statistics,
etc) how often should these typically be run?|||As you probably realize, there are not simple "one size fits all" answers to these question. Reading
up on the subject is what I recommend:
> For the database integrity check, is it best to run this before or after a full backup?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/23/675963.aspx
And other articles on that blog.
> In terms of the other maintenance tasks (rebuild indexes, update statistics, etc) how often should
> these typically be run?
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Above article hasn't been updated for 2005, but basic concepts still applies.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ML" <schooner@.accesswave.ca> wrote in message news:OabaWpN2HHA.1124@.TK2MSFTNGP06.phx.gbl...
> "ML" <schooner@.accesswave.ca> wrote in message news:%23abfUZG2HHA.728@.TK2MSFTNGP05.phx.gbl...
>> Does anyone have a link to resources or suggestions on best practices for setting up proper
>> maintenance plans with SQL 2005 (Workgroup)?
>> We have a new 2005 database that we are preparing for production and want to make sure we cover
>> off all the bases in setting up our maintenance plan.
>> This will be for an online reservation system that will process approx 300-600 transactions per
>> day. The system needs to be live 24/7/365 or as near to this as possible.
>> The initial database is expected to be around 600MB is size with approx 40 tables and grow from
>> there with new transactions.
>> What we be a good plan for such a system for a starting point? Is a single daily plan sufficient
>> or should we be setting up multiple plans to handle backups, tuning, shrinking, etc separately?
>> Any suggestions/comments would be appreciated.
> Thank you for the comment so far. I think we will start off with daily full backups and
> transaction log backups every 15 mins.
> For the database integrity check, is it best to run this before or after a full backup?
> In terms of the other maintenance tasks (rebuild indexes, update statistics, etc) how often should
> these typically be run?
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment