SQL SERVER – Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Blogging has never been an easy task. With close to 9 years of non-stop learning and sharing has got me into a rhythm that I rarely miss a single day without writing one. Over these years, there is one topic area that gets most number of hits based on subject. It is invariably around the performance and troubleshooting area. Most hits on the blogs are always around these areas. Though I see this is proven and become mature over years, still people are searching for topics that are common and even proven over the years.

This always baffles me on the access pattern and I wondered what could be done to mitigate such queries from budding new age DBAs. At the recent SQLPass conference I met a number of them who came to me asking how to become an effective DBA. I generally try to understand their patterns and look for solutions. One of the DBAs told me he was under constant pressure because the SQL Server used to run fine start of the week and the end users complain that the servers are unresponsive or the performance is not acceptable over the week. He was getting clueless to why this behavior was happening. He wanted some help here and I was clueless what to suggest.

Getting started with SQL Diagnostic Manager 10.0

I was recently trying to play with a number of tools in the market and I bumped into IDERA’s SQL Diagnostic Manager tool. They were releasing the new version and I wanted to see how it was different. The first thing that struck me was the ability for DBAs and developers to create automated baselines. This was the first thing that got me by surprise because this requirement was fresh on my mind from the recent trip.

After installation, get to the properties page of the server and go to the “Baseline Configuration” tab. This brings the ability to set automated backups through the week. In the below case, I have gone ahead a created through the week from 8AM – 6PM. This is exactly the timeframe the production is at the maximum stress levels.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

What to monitor?

The next phase is to know what we are going to monitor. I saw the “Analysis Configuration” section has the settings already available with pre-set configuration. In the example below, I have used the “Online Transaction Processing” and selected some of the readily available “Categories”. I personally felt such jump start configurations can surely help and guide the new DBAs who are clueless about what is happening in the system.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

What I would have loved to see few tooltips while I select each of the categories. Because it would become easy before doing any selection. Having said that, it was pretty easy for me to make the selections as it was almost self explanatory.

While selecting the “Advanced settings”, I was able to see further settings that were interesting. I was able to see some of the rules that can be run by default or can be blocked for analysis. Some of these rules are pretty advanced and interesting because it can be easily missed out in the normal run. Here some of the tempdb rules caught my eyes and I have shown below for reference.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

If that was not enough, in “Filter Settings” we can remove unnecessary databases or add multiple databases of our choice. This is powerful because in servers that are consolidated, we don’t need to worry about analyzing smaller databases but the databases of interest based on application can be worked out.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

I just wish this had some way to customize some of the rules rather than being fixed. But the 100’s of rules already available are pretty exhaustive to start. I am sure an advanced user will piggyback on some of them. Cases like number of VLFs created are different between versions and sometimes obsolete in newer versions.

Web is the new SSMS

Though the desktop version is powerful and has tons of reports to show by default, I think the web interface has improved by leaps and bound in this release for SQL Diagnostic Manager. The web interface to the monitoring server can reveal insights remotely too without installation requirement. The initial install of 220+MB file and configuration can sometimes be daunting and once configured, we can use the friendly web interface to visualize what is happening on the server.

Below is a typical workload that you see that my server suddenly peaked in the late evening when a batch service got initiated. This is typically how people would see their server’s vital stats remotely to check if something is going wrong.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

When such anomalies are detected, we can get into the “Top N” queries to see what is going wrong. Which databases are causing heavy workload. Which queries are consuming lots of reads / writes in the system. I felt this snapshot was very useful and made a lot of sense.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Attention to detail like – “When the last autogrowth happened” is something that I felt was quite useful. From the above, I can also see that a lot of tempdb activity is currently in the system that makes me understand how the developers have written their code and where I need to do the optimizations.

Self-Service the new silver-bullet

Though I was planning  to wrap up this blog, I found a unique customization feature that caught my eyes and needed a mention here. You can build your very own dashboards using the metrics that have been collected before. The usual performance counters are already available from the web version and building your own dashboard with widgets configuration. As you can see I am taking something simple as a dashboard, but these can show the health of multiple instances across the network based on the configuration.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

This ability to have a central web view to all the servers a DBA needs to monitor is critical in large installations and setups where DBAs need to monitor at least 8-10 servers at any moment of time.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Above is a classic example of how I have created my own SQLAuthority dashboard based on the servers that are of interest to me. I am sure your views are going to be complex and complete.

As I conclude, I will let you try the analysis tool which gives some ready solutions to some of the most common problems. There are many more options that are getting added. As I started with the baseline, let me see how the baselines have been performing over the week using their “Baseline Visualizer”.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

I am sure you will find some of these interesting and will share your experience as you play around with the tool. Do let me know via comments on what you found interesting.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backups are Non-negotiable Lifeline for DBAs

I have had my share of DBA friends who have told me a number of horror stories from their experiences. Thinking back, these look like moments to laugh away but there are some serious learnings we get from these experiences. I am a personal believer of learning something in everything that we do. That is one of the reasons I have been able to sustain active blogging every day for so many years. Being like a kid, with the inquisitiveness to learn, should never stop within us if we have to survive in the IT industry.

If you were to ask me to pick one single laughing and learning moment that stands out among the rest, I don’t think I could pick just one. Having said that, I do see one common theme when it comes to databases and backups. I am a regular visitor and contributor to SQL Server Bangalore UG and I learn a lot from my friends there. A number of them have asked how you restore system databases once it is corrupted. Do you ever take system databases backups proactively, as a just-in-case practice? How many of us do this in our testing, development and other integration servers today? I can bet you, not many of us.

In a recent user group meeting, someone asked me to simplify how a normal backup happens. For a second all the parameters involved in the UI screen came to my mind and I held my thought. I said, “Buddy it is simpler than you can think.” I told the following flow:

sqlsafe1 SQL SERVER   Backups are Non negotiable Lifeline for DBAs

This is the fundamental building block as backups happen. Now we can add spice to this workflow with options like compression, verify file, encryption and others. But the basic structure cannot be simplified more than this.

SQL Safe Backup

There is never just one way to do things. I had the opportunity to look at Idera’s SQL Safe Backup and some of the things that caught my attention are listed below. You need a structured method to automate and build a process for your backups if you are working in an enterprise.

First impressions – Starting a backup

I started the backup wizard to see what it can give me for a starter. The below screenshot shows the set of databases on a particular instance which can be configured for backups. It can be Full, Differential, Log or File backups. All these are out-of-box capabilities. What I saw immediately was the data about when the last backup was done. This is nifty and nice information to start when taking manual backups.

sqlsafe2 SQL SERVER   Backups are Non negotiable Lifeline for DBAs

Four settings that surprised me

The wizard has a number of settings that stumped me. For instance the CopyOnly backup option as part of General tab was a pleasant surprise. To add to it, on selection it gives me important information about how a CopyOnly doesn’t break the Log chain etc. If you have a Junior DBA in your organization, these tips will surely help.

sqlsafe3 SQL SERVER   Backups are Non negotiable Lifeline for DBAs

The next two settings are great best practices for me. The “Enable Checksum” is something I advocate everyone when working with versions of SQL Server 2005 and above. It is great to see as an option as part of the wizard.

The icing on the cake the tool gave was “Network Resiliency.” The attached screen shot shows how the retry logic happens and for how long when the backup needs to be placed in a network share (UNC path).

Finally, if these were not enough. There is a way to script the whole thing and run it as command line option or TSQL script. This can later be integrated with custom applications or even with SQL Agent. A typical script for my above system databases backup looks like:

Command line script:

“C:\Program Files\Idera\SQLsafe\SQLsafeCmd.exe” Backup master msdb model “C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe” -CompressionLevel ispeed -RetryWrites 10 300 60 -Server sqldude

T-SQL construct

@ResultCode = [master].[dbo].[xp_ss_backup]
@database = N'master',
@database = N'msdb',
@database = N'model',
@filename = N'C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe',
@compressionlevel = N'ispeed',
@retrywrites = N'10 300 60',
@server = N'sqldude'
IF(@ResultCode! = 0)
RAISERROR('One or more operations failed to complete.', 16, 1);

Though all these got me interested, I wish the tool had more options to integrate with native settings. Some of the options I love to see would include – utilizing the native compression technique of SQL Server, MaxTransferSize setting, BlockSize setting, BufferCount settings for backup can be really handy. Also for SQL Server AlwaysOn configuration databases, it would be great to add options or settings to check for secondary servers in case we want to create a backup. The restore process is simple and the wizard is really handy here too. Let me move to the next interesting part.

Defining Policies – Making a blueprint

Every organization has its own retention policies when it comes to backups. They build their own strategy of backups based on RTO and RPO requirements of the business. Having a strategy to recover is the most important task. So Idera’s SQL Safe Backup did give me some great ideas. The wizard for creating the policy can be for backup, restore or for log shipping. The basic backup wizard allows us to select instances, DB’s and create a plan accordingly.

sqlsafe4 SQL SERVER   Backups are Non negotiable Lifeline for DBAs

What I loved about the wizard is that, I can create a consolidated single plan in one shot for Full, Transaction logs and Differentials for my organizational mission critical database in one go. The above schedule is a classic screen shot of how you can schedule in one screen how the backups would be taken.

What I would love to see is some graphical representation on a week’s timeline to how backups are taken. This will help the DBA take a call if their strategy is meeting their SLA or not. I am so used to backup timelines with SSMS that I thought this would be a great addition. Apart from this, in enterprises we have a need to define our own custom encryption with SQL Server using Backup Encryption. I hope the feature to import a custom certificate for encryption would help many with the tool.

Final note

All tools evolve, and their fundamental motive is to make life of people easier. SQL Safe Backup is surely a tool geared in that direction. It uses the standard VDI interface to take backups. I wish as the new versions are released, the tool also aligns with utilizing the capabilities of new functionality. Some that I have pointed included external Encryption, native Compression, AlwaysOn secondary backups, backup to URL etc. For today’s deployments, a lot of them require these tools to organize their backup strategy efficiently. I think of SQL Safe Backup as like a Swiss army knife in the DBA’s pocket.

You can download SQL Safe Backup from here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Monitoring SQL Server Never Got This Easy

In administering SQL Server databases, the DBA is often presented with the task of identifying resource bottlenecks on the system and the sources of those bottlenecks. Also, the DBA is challenged with the task of identifying why specific queries take significant time to execute or affect the performance of other queries on the system. This allows the DBA to find opportunities to tune queries and improve their performance and the overall performance impact on the system. In this blog we will explore identifying if any disk I/O bottlenecks are present that impact query performance on the system. Such action would allow a DBA to further deal with the I/O bottleneck.

Download Diagnostic Manager if you want to run along with the example demonstrated in this blog post. .

Introducing Extended Events for IO monitoring

The applications users have worked with the System Administrator on slow-performing application issues. They suspect that slow disk I/O is impacting the performance of database queries. You may be asked to verify if this is the case and you can use SQL Server Extended Events to find if any queries are experiencing on disk I/O waits.

ADD EVENT sqlos.wait_info(ACTION(sqlserver.session_id,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([wait_type],(68)) OR [package0].[equal_uint64]([wait_type],(66))
[package0].[equal_uint64]([wait_type],(67)) OR [package0].[equal_uint64]([wait_type],(182)))
[package0].[greater_than_uint64]([duration],(0)) AND [package0].[equal_uint64]([opcode],(1)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\XeventIObottleneck.xel')

Let’s verify that the session is actively running and capturing data by executing the following DMV query:

SELECT * FROM sys.dm_xe_sessions WHERE name = 'XEvent_IO_Tracker';

The next step is to induce an IO operation and capture using our XEvent. Open a New Query window and copy and paste the following T-SQL command.

USE AdventureWorks2012
SELECT COUNT_BIG(*) FROM [Production].[ProductInventory] a
CROSS JOIN [Production].[ProductInventory] b
CROSS JOIN [Production].[ProductInventory] c
CROSS JOIN [Production].[ProductInventory] d
WHERE a.Quantity > 300

Once the query completes, go back to the other query window and execute the following command to stop the Extended Event session:


November 12, 2014 XEvent 01 SQL SERVER   Monitoring SQL Server Never Got This Easy

In Object Explorer, expand the plus next to XEvent_IO_Tracker and right-click on the package0.event_file and select View Target Data:

In the above example, I used the “View Live Data” feature of Extended Events to look at the data. We can see the type of lock and the actual query too.

This is a great start, but resource constraint inside SQL Server can be of various types and IO is just one of the resource.

Diagnostic Manager does more than the eye can see

There are a number of standard tools like PAL that can be used for monitoring SQL Server. I took a look at the new Diagnostics Manager from Idera to understand what it does. It has been like a Pandora box for monitoring point of view. Some of the things that struck my eye are:


When it comes to active server monitoring, it is important to understand that things can go wrong. We are always dealing with limited resources CPU, Memory, IO, throughput, Network etc. As an intelligent DBA, it is critical for us to know when the CPU is peaking, when the backups happen, when the services stop abruptly, when logs files expand and more. I found most of these are already configured and readily and are available to extend using configuration and templates.

November 12, 2014 Idera 02 SQL SERVER   Monitoring SQL Server Never Got This Easy

I sort of like the starter out-of-box templates readily available to get started with this tool.

Standard OOB Reporting

The next stand out I found was the ability which is available inside SQL Server Management Studio. A set of standard reports. It is surely beyond ~30+ reports neatly categorized under “Server”, “Virtualization” and “Activity” headings.

November 12, 2014 Idera 03 SQL SERVER   Monitoring SQL Server Never Got This Easy

I will not get into each of these reports but things like forecast based on existing and historical data is something caught my eye and has been worth a mention. With limited disk capacity, it is critical to know when to increase the hardware configuration based on usage pattern is critical. With SQL Server 2014, it is critical to add the capability of how In-Memory tables sizing will work and how memory needs to be accounted. If these get added, then this can be a great asset for DBA’s running on the latest versions.

Rich Dashboard

I think this is the strength of this tool. It allows standard configured values for known standard resources and gives us the flexibility to create additional counters / values to monitor. This is like opening a can of worms for many. But I am pleasantly surprised that the standard templates are exhaustive enough to start with.

November 12, 2014 Idera 04 SQL SERVER   Monitoring SQL Server Never Got This Easy

I wish these standard templates have some additional recommendations for monitoring some complex environments like SQL Server AlwaysOn using multiple AG’s health, Database Mirroring status, Cluster Resources health and basic activities like backup operations on databases. Though simple tasks like disk monitoring, PLE, Memory, CPU, Network, Waits etc are quite essential – the servers of today are doing way more work than the eye can meet.

Monitor with guidance

Who doesn’t require some help when it comes to monitoring workloads like SQL Server? I personally liked the concept of reference range values when working with SQL Server performance counters.

November 12, 2014 Idera 05 SQL SERVER   Monitoring SQL Server Never Got This Easy

Instead of me trying to monitor, track and look at each performance counters, I would love to see how I can bring my own counters and ranges from tools like PAL in the future. The ability to import and if required to export can be of great help.

Some of the salient points that caught my eye and worth a mention in the new versions are:

  • Monitoring of both Physical and Virtual environments of SQL Server.
  • Ability to monitor query from specific application.
  • Ability to look at historic data to troubleshoot past issues on the server.
  • Track database growth so that we can make corrective planning on capacity on servers.
  • Can look at data on mobile devices like iPhone, Android, Blackberry or Tablet to view real-time and historical SQL Server performance data.
  • Management pack available for Microsoft’s System Center Operations Manager (SCOM).
  • TempDb monitoring, CPU, Memory, WaitStats and more.

You can download Diagnostic Manager from here.


This blog post is to show you that most of the SQL Server work that we do as DBA is identify a needle in a haystack. Trust me with tools like Idera’s Diagnostics Manager, we are overwhelmed with information that we need to know – yet there is more to explore, enhance and extend what the tool gives out of the box.  The best learning experience one can get is by knowing what all the tool does and trying to understand how some of these have been implemented ourselves.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Exploring SQL Auditing with SQL Compliance Manager

As database administrators, we are constantly quizzed by our superiors to answer questions such as: how secure is our network, how secure is our data, is data secure at rest, is data secure in transition, who is accessing my data? All these are basic yet compelling queries businesses ask. In this age of competitive businesses, organizations are becoming tech savvy in building a secure fort for their critical data. Having worked on many projects in the past, these security measures are something I know are non-negotiable from an implementation point of view for administrators. When on this topic, I am reminded of a conversation I had with a junior DBA named Siva.

Siva: Hi Pinal!

Pinal: Hi Siva, how are you dude?

Siva: I am rocking and have been put onto a spot now from my internal auditing team.

Pinal: Wow, nice. Please shoot your question. Let me see what can be done.

Siva: Our internal auditing team wants to do auditing for our mission-critical sensitive database.

Pinal: Well, it’s easy and as simple as the question you asked.

Siva: Really, are you sure?

Pinal: Yes. Use the out-of-box capability of SQL Server auditing and it is something to play with too.

Siva: Oh, yeah. That is true. But there is a twist. They do want to log everything, but want to make sure our administration/maintenance activities are not logged per se.

Pinal: That is an interesting requirement for sure. But, it is not difficult either – trust me.

Siva: Now you are getting me interested. Please guide me.

Pinal: Well, start looking for filtered audit. Have you looked into it?

Siva: Oh yeah. I do need to look into it. Thanks for the pointer. If you get a chance, please write about these on your blog someday.

Pinal: Sure.

This conversation has been on my mind for quite some time and I have been wanting to write what Siva asked since then.

Simple Auditing Filtering with SQL Server

In the above conversation, I am making an assumption that the administrative tasks are done by the local administrator account like “sa.” The first step is to know the “principal id” of our administrator account. Use the following DMV to know the same:

SELECT name, principal_id FROM sys.server_principals

In our example, the “sa” account will have the principal_id of 1. So that is what we will use in our filter section of audit definition. The next is the audit definition we will create using the filter condition. A typical TSQL construct would look like:

CREATE SERVER AUDIT [Filtered-Audit-2014-10]
(  FILEPATH = N'C:\Temp\'
,MAX_ROLLOVER_FILES = 2147483647
(  QUEUE_DELAY = 1000
-- Change server_principal_id as needed
WHERE ([server_principal_id]<>(1))

This is an awesome capability introduced with SQL Server 2012 and is worth a mention today. Now we can go ahead and create a server audit for specific events. Once the audit is enabled, we can go ahead and attach our events to this using the TSQL like below:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141020]
FOR SERVER AUDIT [Filtered-Audit-2014-10]

In this example, we have gone ahead and created an audit for failed logins. The wish list was to automate for a broader auditing capability with SQL Server.

Advanced Auditing with SQL Compliance Manager

On the topic of auditing I would like to talk a little bit about SQL Compliance Manager from Idera. As much as we would like to customize and use the out-of-box T-SQL functionality, it is sometimes necessary to do the same functionality of auditing in an automated fashion. This is where SQL Compliance Manager comes into play. There are a number of things we can start auditing using this tool. Various configuration options you can do with this tool include:

  1. Logins
  2. Failed Logins
  3. Security Changes
  4. Database Definition
  5. Administrative Activities
  6. User Defined Events

In addition to these, we can audit based on filtered access or we can audit all the events by unchecking the checkbox too. I don’t think we can get an easier configurable option for auditing logins, DDL, admin tasks in such a user friendly format. This in my opinion is the fastest way to configure audits on important tasks.

sqlcm1 SQL SERVER   Exploring SQL Auditing with SQL Compliance Manager

If you are a savy DBA, then the chances are you can enable all these auditing capabilities using a similar T-SQL script. But this is where I felt there are nice additions to the tool that make it worth a second look.

sqlcm2 SQL SERVER   Exploring SQL Auditing with SQL Compliance Manager

I particularly want to call out the auditing threshold, which can be used to track abnormal activity and proactively alert a DBA of any suspicious activity that has been identified based on thresholds set by the organization around access, login failures, DDL activity and many more. In addition to those activities, there is also a laundry list of standard reports they generate after the data is collected. This list is pretty exhaustive to say the least.

sqlcm3 SQL SERVER   Exploring SQL Auditing with SQL Compliance Manager

The more I play with the product, there are more pleasant surprises with each additional screen.

Wrap up

As I wrap up, feel free to use the filtered auditing capability with SQL Server and explore how these can be controlled at a granular level. If you want a quick solution that will enable auditing, keeping security and compliance in mind, then it is worth looking into SQL Compliance Manager too. It is worthwhile to trial SQL Compliance Manager for your environment. I still look forward for plugs to the current SQL Server enhancements like filtered auditing, audit resilience capabilities in the future. I am sure these are present in some shape or form today and may be explored.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Idera SQL XEvent Profiler – a Free Tool for XEvent Monitoring

Download Extended Event Profile FREE

We often resist the change, we do not like change and we keep on using the same old technology which we have been using it to resolve the new challenges which we face in our daily life. The Extended Event is such a technology as well. It was added to SQL Server in SQL 2008 and expanded upon greatly in SQL 2012 is a powerful mechanism for monitoring activity in SQL Server. It has huge implications for tools like SQL Diagnostic Manager, which switched over to XEvent technology for wait monitoring, and for users of all stripes who need to see what is happening on their servers.

Though the product was well thought of it was yet not quite ready for average users due to many reasons. It offered a reduced set of events and could only be manipulated through TSQL. In SQL 2012 the number of events went up dramatically and Microsoft released an XEvent GUI as part of Management Studio. However, lots of users think that the XEvent tool is a bit overkill for a lot of purposes. Therefore, a lot of users continue to use SQL Server Profiler, which uses trace technology. It’s easy to see why – it’s familiar, it’s easy to use, and it takes only a few clicks to get a diagnostic session started.

Team Idera has put together a new free tool called SQL XE Event Profiler, which offers the simplicity of Profiler but uses the powerful and lightweight XEvent technology on the back end. The idea was to provide the simple 2 click behavior that users were looking for, along with the familiar SQL Profiler templates that users are already accustomed to using. The application has the familiar start, stop, and pause buttons, the ability to turn auto-scroll on and off, and the ability to clear the grid. While we were at it, we added some nice grouping functionality to the event grid, along with text searching and export.

XEvent Profiler Template SQL SERVER   Idera SQL XEvent Profiler   a Free Tool for XEvent Monitoring

I am downloading this tool this weekend and going to play with this tool and see how I can use it on my various servers. I encourage all of you to do the same.  Here is the link to the original blog.

Download Extended Event Profile FREE

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database

I recently downloaded Idera’s SQL virtual database, and tested it. There are a few things about this tool which caught my attention.

My Scenario

It is quite common in real life that sometimes observing or retrieving older data is necessary; however, it had changed as time passed by. The full database backup was 40 GB in size, and, to restore it on our production server, it usually takes around 16 to 22 minutes, depending on the load server that is usually present. This range in time varies from one server to another as per the configuration of the computer. Some other issues we used to have are the following:

  1. When we try to restore a large 40-GB database, we needed at least that much space on our production server.
  2. Once in a while, we even had to make changes in the restored database, and use the said changed and restored database for our purpose, making it more time-consuming.

My Solution

I have heard a lot about the Idera’s SQL virtual database tool.. Well, right after we started to test this tool, we found out that it really delivers what it promises. Using this software was very easy and we were able to restore our database from backup in less than 2 minutes, sparing us from the usual longer time of 16–22 minutes. The needful was finished in a total of 10 minutes. Another interesting observation is that there is no need to have an additional space for restoring the database. For complete database restoration, the single additional MB on the drive is not required anymore. We can use the database in the same way as our regular database, and there is no need for any additional configuration and setup.

Let us look at the most relevant points of this product based on my initial experience:

  • Quick restoration of the database backup
  • No additional space required for database restoration
  • virtual database has no physical .MDF or .LDF
  • The database which is restored is, in fact, the backup file converted in the virtual database. DDL and DML queries can be executed against this virtually restored database.
  • Regular backup operation can be implemented against virtual database, creating a physical .bak file that can be used for future use.
  • There was no observed degradation in performance on the original database as well the restored virtual database.
  • Additional T-SQL queries can be let off on the virtual database.

Well, this summarizes my quick review. And, as I was saying, I am very impressed with the product and I plan to explore it more. There are many features that I have noticed in this tool, which I think can be very useful if properly understood.

I had taken a few screenshots using my demo database afterwards. Let us see what other things this tool can do besides the mentioned activities. I am surprised with its performance so I want to know how exactly this feature works, specifically in the matter of why it does not create any additional files and yet, it still allows update on the virtually restored database. I guess I will have to send an e-mail to the developers of Idera and try to figure this out from them.

I think this tool is very useful, and it delivers a high level of performance way more than what I expected. Soon, I will write a review for additional uses of SQL virtual database.. If you are using SQL virtual database in your production environment, I am eager to learn more about it and your experience while using it.

The ‘Virtual’ Part of virtual database

When I set out to test this software, I thought virtual database had something to do with Hyper-V or visualization. In fact, the virtual database is a kind of database which shows up in your SQL Server Management Studio without actually restoring or even creating it. This tool creates a database in SSMS from the backup of the same database. The backup, however, works virtually the same way as original database.

virtualdb1 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database

Potential Usage of virtual database:

As soon as I described this tool to my teammate, I think his very first reaction was, “hey, if we have this then there is no need for log shipping.” I find his comment very interesting as log shipping is something where logs are moved to another server. In fact, there are no updates on the database from log; I would rather compare it with Snapshot Replication. In fact, whatever we use, snapshot replicated database can be similarly used and configured with virtual database. I totally believe that we can use it for reporting purpose. In fact, after this database was configured, I think the uses of this tool are unlimited. I will have to spend some more time studying it and will get back to you.

Click on images to see larger images.

vdb1 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Console
vdb2 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Harddrive Space before virtual database Setup
vdb3 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Attach Full Backup Screen
vdb4 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Backup on Harddrive
vdb5 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Attach Full Backup Screen with Settings
vdb6 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Setup – less than 60 sec
vdb7 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Setup – Online
vdb2 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Harddrive Space after virtual database Setup
vdb9 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Point in Time Recovery Option – Timeline View
vdb10 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Summary
vdb11 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
No Performance Difference between Regular DB vs Virtual DB

Please note that all SQL Server MVP gets free license of this software.

Reference: Pinal Dave (http://blog.SQLAuthority.com), Idera (virtual database)