January 2, 2009 at 6:09 pm
Hi,
We are using SQL Server 2005 EE X64 cluster setup on windows 2003 EE.
We ran SQLBPA and it detected the following issues that we have:
1.
Warning SQL Server Service [...\...] In Local Administrator Account On Host [...]. Service [SQLBrowser] on host [ABC] is found to be running under [ABC\sqlservice] account that is a member of BUILTIN\Administrators. We recommend that SQL Server services should not run under local administrator accounts.
2.
Warning Backups Must Be On Separate Volumes For Database [...] On Server [...] ABC. 1 Database [abc] on server [ABC] has logical volume [D:\] on machine [ABC] shared between it's backup and data files. We recommend backup and data files to be present on separate volumes.
3.
Warning Disk sector [...] alignment of server [...] is not configured optimally. ABC. Work with their hardware vendor on optimal alignment settings.In order to fix this potential issue, you will have to recreate and reformat the suboptimally aligned partition
More info from SQL BPA:
Hard disk sectors are optimally configured to have a starting offset of 64 KB or a multiple of 64 KB. Hard disk sectors were detected that have starting offsets that differ from this configuration. For RAID volumes, which are made of multiple physical spindles, this can adversely affect I/O response time.
Thanks
January 2, 2009 at 7:04 pm
Is there a question here?
FWIW - item #1 is debatable and you will get people on both sides of the issue. I personally setup my service accounts as local adminstrators on the machines.
Item #2 is just common sense - for a number of reasons.
1) if you lose the drive, then you also lose your backups and won't be able to restore.
2) backups utilize a lot of I/O which will interfere with database access
Item #3 is a little less known, but is a best practice depending upon your hardware vendor. I have two seperate SAN vendors, and each one has specific criteria for setting this up - and one will throw the same error in BPA but the SAN vendor says it's not necessary for their SAN.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2009 at 7:39 pm
Regarding item#2:
We have data files on D drive and log files on F drive and tempd on G drive and Backups are Z drive ,but still SQL BPA telling that we have Data files and backup files on same volume
My question is this anything related to SAN Issue? So that i can address to network team.
thank you
January 3, 2009 at 12:07 am
No - I don't think that it related to SAN. Most likely you have the default backup directory defined to the D: drive. You can find it by searching the registry - not sure the exact key.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2009 at 4:42 am
madhu.arda (1/2/2009)
Regarding item#2:We have data files on D drive and log files on F drive and tempd on G drive and Backups are Z drive ,but still SQL BPA telling that we have Data files and backup files on same volume
My question is this anything related to SAN Issue? So that i can address to network team.
thank you
is your Z:\ drive a seperate physical disk or is it a partition of the physical disk that D is on?
January 3, 2009 at 7:25 am
madhu.arda (1/2/2009)
We have data files on D drive and log files on F drive and tempd on G drive and Backups are Z drive
Are those separate physical drives, or partitions on a single drive/array?
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
January 3, 2009 at 12:01 pm
thank you
The drives are separate physical drives
January 3, 2009 at 12:11 pm
It could be that someone's done an adhoc backup to a drive other than Z. Use the following to see the backup history.
select * from msdb..backupmediafamily
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
January 3, 2009 at 12:15 pm
madhu
your service account does not need domain or machine rights as they are automatically assigned by the setup program or sql config manager. A series of local groups get created and the account is added to these groups. These groups then have the necessary permissions required to the disk files, etc. The right to logon as a service is also granted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2009 at 4:17 pm
Thanks Gail,
After reviewing the results of select * from msdb..backupmediafamily
I observed that the backups are placed on D, E,F like 4 months ago. But as I know from last 3 months the backups are going to Z drive itself.
January 4, 2009 at 2:29 am
madhu.arda (1/3/2009)
I observed that the backups are placed on D, E,F like 4 months ago. But as I know from last 3 months the backups are going to Z drive itself.
That's probably what the BPA is picking up then. So no worries.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply