Category Archives: Database Administration Tasks

Auditing Revisited, Makes No Sense To Me

Back in January, I did a series on simple auditing which walked through setting up an audit and creating a simple SSRS report.  I actually used the report and the query that I demonstrated for a couple of servers in my stack that require auditing.  The basis of this revolves around the following line of code:

SELECT * FROM fn_get_audit_file (‘g:\auditing\*’, DEFAULT, DEFAULT)

This line of code runs great for my login and I never gave it much thought because I am running it from a sysadmin account.  When asked for users to be able to run this report that is when things are getting sticky.  I cannot fathom why a simple query for log data cannot be attributed to a read-only account.  In order for you to run this select statement you either need a sysadmin level account or the CONTROL SERVER permission which pretty much gives you the keys to the kingdom.  Why would they do that?

I am sure there is a great technical reason, but why not allow it to filter down to read-only access to simply view the data?  With those permissions whoever is in charge of running the audit can manipulate the data however they see fit.  That makes no sense to me but then again who am I in the grand scheme of things?  Shortly I will post a few different scenarios in how to solve this problem.  Enjoy!

Online Restores

I was doing some mentoring today with online restores, so I thought I would share it with my blog readers.  The most important thing about online restores is that it is only supported in SQL Server 2005 and later versions.  In addition, for the bulk-logged recovery model the following conditions must be met in order to complete an online restore:

  1. All of the log backups must have been taken and completed before you start the online restore.
  2. If you have bulk changes in the database, the files must be online.
  3. All of your bulk changes must be backed up before starting the online restore process.

In addition, I have seen it recommended that you switch your bulk-logged recovery model to the full model before starting the online process and then switching it back afterward for smoother recovery.  Enjoy!

Weird Problem with SSAS Not Starting

After building a new cluster to replace a 2005 cluster here in the office, I discovered Analysis Services would not start.  Usually I do not put Analysis Services on the same box however one of our vendors requires SSRS, SSAS, and the Database Engine to be on the same instance.  Frustrating, I know.

When I installed the cluster, everything was operational.  However after I applied service pack two to the 2008 R2 Enterprise nodes, Analysis Services would not start.  I didn’t notice the first time that it was after the service pack was applied until I removed everything and started over fresh.

The logs were not much help and I could not find much on the internet until I eventually found a friend who suggested that the service running SSAS needed to be in the local administrators group.  Problem solved.  Now my lesson learned is here for you as well.  Enjoy!

Backup and Recovery Documentation

Today I began documenting my backup and recovery strategy for my servers.  If you have not documented your strategy, take my advice and do it.  This should include the following items:

  • Retention schedule
  • Recovery models
  • Disaster recovery plans
  • RPO and RTO or SLAs
  • Identify your backup schedule
  • Identify your recovery schedule
  • Identify offsite storage options and retention
  • Identify your compression strategy
  • Identify any third party vendor tools

I hope this helps.  Enjoy!

Dynamic Management View Categories

Thinking today about a new area to cover on this site and I thought dynamic management views (or DMVs) was a good area to cover next.  I was looking at some documentation today for some DMVs and I realized that there is an easy way to think about them and their function.

  • sys.dm_exec_*  These provide information about sessions, connections, query executions and requests.
  • sys.dm_os_*  These provide information on OS related information
  • sys.dm_io_*  These provide input/output process information
  • sys.dm_tran_*  These provide transaction information
  • sys.dm_db_*  These provide database information

Enjoy and stay tune for more DMV information.

MAP Toolkit in Action

I have written in the past about the MAP Toolkit (Microsoft Assessment and Planning) and how it helps with licensing issues including core counts.  With that being said, it is time for us to renew our Software Assurance maintenance agreement and this was the original reason that I installed the MAP Toolkit a couple of months ago.

This week I rescanned all of my instances to figure out how many cores we had licensed and for which version.  I wanted to get all of my documentation ready to go to our contract manager when I noticed a new server that had 24 Enterprise cores that I had never heard of before.  My supervisor had never heard of the box either.

When I logged into the box, sure enough it had SQL Server installed.  The server was for a monitoring tool and when the administrators installed the software on the box instead of asking me where they could install the database they found a disk and installed SQL Server themselves.  Unlicensed.  On the same box as the software.  Without telling anyone.

When the contract manager told them how much the 24 cores would cost their department they quickly called me and asked me to kindly move their 13 GB database to one of my other servers.  It just happened recently because I scan the network every few months and had never seen this before.  Not only did they put us in jeopardy with our licensing, they had no backups setup for the database.  When confronted they said yeah that was taking up too much space on the disk.

The lesson here today boys and girls is to scan your instances and look for unknown installations regularly.  Carry on and enjoy!


SP_SpaceUsed for Disk Space Monitoring?

Recently, I heard about a couple of developers that were using the stored procedure SP_SpaceUsed to monitor available disk space.  They had requested additional disk space because the utility had told them that there was only 4mb of unallocated space.  Try not to laugh because these could be YOUR developers.

Last month I blogged about using the procedure to show you the estimated backup size for the database.  But these developers were confusing unallocated space, which is actually just space in the database that has not been allocated.  For example, when you set a autogrowth interval and your database grows in accordance with the interval you will now have unallocated space until the database takes that free space.  Enjoy!

database_name database_size unallocated_space
<Database Name> 2449.81 MB 40.45 MB
reserved data index_size unused
2396472 KB 2386856 KB 5144 KB 4472 KB

HyperBac No More

In case you missed it last week, Red Gate announced the death of the HyperBac tool for backup and recovery.  I am not surprised by this move as the features were pretty much the same as the Backup Pro tool also offered by Red Gate. When I setup a test case for the tools in comparison with Backup Pro and native compression, it consistently had the worst compression ratios.

However, I was a little sad about the death of the Virtual Restore piece of HyperBac.  This tool had great potential, especially since we just figured out how to script it to autoMAGICally restore all of our backups on a nightly basis.  Maybe they will be able to incorporate this piece into the Backup Pro tool.  Only time will tell, I guess.  Enjoy!

Random Mirroring Facts

Today is a hodge podge of facts about mirroring, since I am in learning mode I thought I would share with you.

  • Mirroring is deprecated, meaning it is going away and moving to AlwaysOn availability groups.  Now is probably not the time to devise a mirroring plan.
  • You can mirror some or all of your databases on a particular instance.
  • You can only mirror databases in the FULL recovery model.
  • Mirroring supports only one principal and one mirror.
  • All versions support witness modes but only Enterprise supports high-performance mode.
  • Enterprise, Standard and the new BI version all support the high-safety mode.
  • You cannot mirror a database that is using FILESTREAM file groups.
  • You can only mirror user databases.


Restoration Recovery State Options

(Editor’s note: I just discovered that this blog did not post on 2013-Feb-06 as it was originally intended)

Yesterday, I was asked what the difference was in some of the restoration options.  Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.

On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs.  I have tried to explain them here in the order that they appear on the screen.

In the Recovery State options:

  • Leave the database ready to use by rolling back uncommitted transactions.  Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY):  Choose this option only if you are restoring the log files now (in full or bulk-logged recovery models).  This is the default option and allows you to only go back to the last backup file.
  • Leave the database non-operational, and do not roll back uncommitted transactions.  Additional transaction logs can be restored. (RESTORE WITH NORECOVERY):  Pick this option when you want to go to the very latest possible data!  Leaves the database in a restoring mode as you restore the backup and then all of the transaction logs generated since the backup up to the point of failure (if possible).
  • Leave the database in read-only mode.  Undo committed transactions, but save the undo actions in a standby file so that recovery effects can be reversed. (RESTORE WITH STANDBY):  The database will be left in read-only mode.  At this point you can still apply transactions logs and is generally used when it takes too long to restore the system to a full database and you wish to use the server as a stand by server.

I hope this helps, enjoy!

%d bloggers like this: