2005 Maintenance Plan or Security bug?

Curious if this scenario is by design or a possible bug. As recommended by MS best practices for SQL 2000 we always removed the BUILTIN\Administrators login. After doing the same on our 2005 installations it appears to have caused errors in the edit\view functionality in the Maintenance Plans and jobs. One example is to open a plan in modify mode and then select the logging button. - 'Unhandled exception has occured in a component in your application' (have debug file)

Another error occurs when attempting to edit a step of the Maintenance Plan job. Select the subplan step > edit > select any tab other than General and this error occurs.

TITLE: SSIS Execution Properties

The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'domain\xxxxx'.). The SQL statement that was issued has failed.

I re-added the BUILTIN\Administrator to one 2005 instance and it corrected the errors completely. We are running Enterprise edition on W2K3 SP1 server using mixed mode auth. I do have Admin rights to the servers so it appears that windows security is being used for some maintenance plan functionality rather than the sql security. The errors could be reproduced running the Management Studio locally or remote connected as sa.

Any insight to this weirdness would be appreciated.

Allison

Check that you SQL Services have explicit access granted and aren't going through BUILTIN admins

|||Not sure exactly what you mean by 'have explicit access' but I did add the SQL Services start up account (domain account with server admin rights) as a sysadmin and it did not correct the problem.

All jobs execute with out the start up account having an explicit sql login.

Allison|||The (Login failed for user 'domain\xxxxx'.) is this a literal posting or have you blocked out the name of the account the SQL agent uses?
|||Might be that something in your plan accesses a location that doesn't exist or no permissions a granted for the user opening the plan...there should be useful information in the paramters passed to the LoadFromSQLServer method that could resolve this.
|||Yes I did block it out of the message but it was not the SQL Services account in the error message it was my user account. So what you are saying is that I can create a maintenance plan but when I attempt to edit it my credentials, not the service credentials are being checked?

Not sure hove to look at the parameters for LoadFromSQLServer.

Thanks
A|||Yes, when you automate a job it will run under a specified or agent account. When you are editing it, your account is being used to access the objects involved.

2005 Maintenance Plan Best Practices?

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

2005 Maintenance Plan Best Practices?

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

2005 Maintenance Plan Best Practices?

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/pr...in/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 singl
e
> 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/pr...br />
ps4.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 ba
ckup?
http://blogs.msdn.com/sqlserverstor.../23/675963.aspx
And other articles on that blog.

> In terms of the other maintenance tasks (rebuild indexes, update statistic
s, etc) how often should
> these typically be run?
http://www.microsoft.com/technet/pr...n/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@.TK2M
SFTNGP05.phx.gbl...
> Thank you for the comment so far. I think we will start off with daily fu
ll 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 statistic
s, etc) how often should
> these typically be run?
>

2005 Failover Cluster SAN drives best practices

> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the sam
e
set of disks, but at least it gives you some separation.

> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/writ
e
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.

> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:

> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
currently have two nodes and I am planning on adding more instances to
run on the other node.
First, I am not sure I have setup my existing default instance the best
way for my cluster. I currently only have one 'Physical Disk' in the
cluster group which is a LUN on our SAN. Both the data files and log
files are stored there. What is the best practice to move the logs onto
another spindle? Do I have to just create another LUN?
I am about to add a few more instances. Does each one need to have it's
own LUN?
There is a limited amount of drive letters that I can create. Is there
a way to share a LUN between instances? I seem to remember reading a
feature of 2005 that was related to this but, for the life of me, I
cannot find it again.
Any help would be appreciated!
Thank you
Karl|||> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the sam
e
set of disks, but at least it gives you some separation.

> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/writ
e
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.

> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:

> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>

2005 Failover Cluster SAN drives best practices

I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
currently have two nodes and I am planning on adding more instances to
run on the other node.
First, I am not sure I have setup my existing default instance the best
way for my cluster. I currently only have one 'Physical Disk' in the
cluster group which is a LUN on our SAN. Both the data files and log
files are stored there. What is the best practice to move the logs onto
another spindle? Do I have to just create another LUN?
I am about to add a few more instances. Does each one need to have it's
own LUN?
There is a limited amount of drive letters that I can create. Is there
a way to share a LUN between instances? I seem to remember reading a
feature of 2005 that was related to this but, for the life of me, I
cannot find it again.
Any help would be appreciated!
Thank you
Karl> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the same
set of disks, but at least it gives you some separation.
> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/write
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:
> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>