Wednesday 30 September 2009

[Fix] SQL Server 2008 Install causes Access Denied error

Got a new database server at work. It was preinstalled with Win 2008.

So I went about installing SQL Server 2008 on it. All looked to be installing correctly until I got the error near the end of the installation that said 'Access Denied' when finishing the install. After ending the installation it looked like most of the programs were installed. The SQL Server service was also running. However I couldn't connect to the new instance.

Tried to reinstall SQL Server 2008 but it gave the same 'Access Denied' error. I thought it was a permissions issue somewhere so I tried to give the service account and the installing account admin rights to the server. It still was giving the same error.

Finally found a knowledge base article that said that the installing account had to have Debug Programs permissions in Group Policy by doing the following steps

  1. Log on to the computer as a user who has administrative credentials.
  2. Click Start, click Run, type Control admintools , and then click OK.
  3. Double-click Local Security Policy.
  4. In the Local Security Settings dialog box, click Local Policies, double-click User Rights Assignment, and then double-click Backup Files and Directories.
  5. In the Debug programs Properties dialog box, click Add User or Group.
  6. In the Select User or Groups dialog box, type the user account being used for setup, and then click OK two times.

This looked like it would do the trick however when I navigated to the setting the add user group buttons were greyed out so I couldn't add the current user.

After a bit more thinking I thought that the setting must be in our network group policy somewhere. I set the Debug Programs permissions for the server OU but it still didn't change the setting on the new server. Finally I realised that the new server hadn't been moved to the correct OU. After moving it into the correct OU all the settings came through and the installation completed successfully

Hope this helps someone.

Please share your SQL Server 2008 Tips in the comments

Wednesday 23 September 2009

SQL Server 2005 - Backup Strategies

Over the past 5 years we have tried a few different backup strategies for our SQL Server data. This includes SQL Server 2000 onwards.

I thought it would be a good excercise to discuss our current backup strategy and how it evolved into its current state.

Current Backup Setup
There are 3 sections to our current backup strategy.

1. Full Daily Backup
2. Transactional Log Backup
3. CleanUp old backups

Full Daily Backup
A full daily backup of all databases is done in the evening. This is done by using the selection 'All user databases' in the backup job setup.

Transactional Log Backup
The transaction logs for our mission critical database are backed up every 15 minutes. The rest of the user databases have their transaction logs backed up every hour during working hours.

CleanUp old backups
Cleanup is done before evening backups and it varies depending on the database. All databases have a maximum of 5 days full backups. Most databases are have their clean up to be less than 5 days.

Transaction logs are kept for a maximum of 3 days with most databases having between 1-2 days transaction logs.

Looking at our strategy you may have some questions in you mind. I will try to provide a rational for our backup strategy.

We are using one maintenance plan to backup all user databases because using this method you can be sure that all current databases will at least have a full daily backup. Occasionally we had developers or third parties install applications with databases onto our database server without DBA's knowing. This would mean that DBA's were unaware of new databases that required backing up until they checked the server or were told about it. The occurrence of this issue have been much reduced in recent times but it does still happen.

The cleanup routine should ideally occur after a successful backup however due to space restrictions on the backup drive we have had to do cleanup before doing the evening backups.

We have a standard Mirror, Mirror, RAID-5 setup on our server. This means the OS is on 2 mirrored drives, Logs are stored on 2 mirrored drives and the MDF database files are stored on a RAID-5. In order to protect from multiple drive failure we has set our backups so that all Log backups go to the database RAID-5 partition and all full database backups go to the Logs mirror partition. This backup configuration gives us fully backed up databases in the event that a whole set of disks fails. For example if the RAID-5 array fails that we will have the logs and the database backups on the Logs drive. If the Logs disks fail then we would have the live database files and the log backups from the RAID-5 drives.

This setup isn't perfect but I believe that it is a decent real world solution to backing up our data. We are lucky that we have a large backup window were all databases have minimal usage so that we can perform backups in the evening.

Please let me know if you have any comments about my backup strategy.