SQL Server Journey with SQL Authority

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.

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.

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

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