Category Archives: Review

Software Review: Idera’s Virtual Database

Ever since the upgrade from SQL Server 6.5 to 7.0 one of the most requested features I’ve heard people complain about loosing was the ability to backup a single table. During my tenure as Product Manager for Quest’s SQL Litespeed extracting tables was one of the things we were constantly asked about. Eventually built it, and it worked. So, believe me when I tell you it is no easy feat to extract data from a backup file, let alone make it look like a normal database and attach it to a running SQL Server instance. The holy grail to me was always “select * from backup”. Virtual Database is as close to using a backup file as a real database that I have ever seen.

Setting Up Your Virtual Database

The main screen isn’t your traditional style of GUI. This is where everything happens. It has options to attach backup files, ether a single full or multiple files to a point in time. There is a run queries option, which is redundant since you can use management studio to access the database like you would any other database. The Tips @Tricks is a little pointless since this is installed on your server and if you are like me it is locked down pretty tight so cruising web sites is out. Finally, Help & More it a lot more important than you think.

To attach a full backup is dead simple. Give it the file name, SQL Server instance name and a new database name.

If you have used Idera’s SQL Safe to do the initial backup with and had it generate the content map file the attach time is fast. I didn’t test with native backup files since we don’t do any native backups where I work. If the content file isn’t there it will have to generate it and depending on the size of the backup file it can take a while.

Attaching multiple files is a little more difficult. The wizard steps you through it though.

If you accessing files from a network share it will prompt you for credentials so it can read the backup files. Virtual Database fully supports UNC mounting.

If you have encrypted your backups using SQL Safe, it will prompt you for the password as well. You are encrypting your backups right?

It looks through the backup files to pull out key information needed to do the point in time recovery. This is also very fast. I actually had to do it a few times to get this screen shot!

Once you have the files added it will show them sorted. Any files it thinks there is a problem with it will highlight in red for you. These were LSN out of sync errors. It doesn’t stop you from going to the next screen though.

Once you have settled on a file list you can now choose your point in time. You can have multiple full, differential, or transaction log backup files specified. When you choose a point in time it selects the files needed to do the recovery. It will choose the backup files that are needed. If you have done what I just did and selected everything in a folder it won’t apply three full backups plus all the transaction log files.

Like the single file attachment process, you still need to provide an instance name and database name.

Finally, it gives you a summary of what is going to happen. After that the database is ether attached, or fails. If it fails you have to start all over.

Configuration Options

There are very few configuration changes that you can make the product.

I’m not thrilled with this screen setup. It’s got a lot going on and some of the functions like most of the help stuff, doesn’t work on my locked down server.

Where you put your support files may an impact on performance. SQLvdb has some support files it needs to do its magic. This is also where it records changes you make. If you plan on making a lot of changes you may want to move the location from the C drive to another faster and larger drive.

Some files aren’t deleted when you drop the virtual database and require a manual cleanup step. You can do it here or through the command line tool.

The drive letter is basically a protection mechanism. SQLvdb is looking for a specific set of file extensions like .mxf or .lxf. So, don’t use those extensions for anything else. You can change it if you do have a file extension collision.

The service connection allows you to change the port but not the server name. This means I can’t have the GUI installed on my desktop and access my SQL Server where the services and driver is installed. I don’t know if this is supposed to be this way or if it is a bug.

The Good

What can I say, it is select * from backup. You mount it and like magic your backup file is a live database again.

You can also create objects in this new database. That’s right, you can create a new table and put data in it. That data and structure aren’t stored in the backup file though and if you remove the database all changes are lost. You can do a backup of the “new” database just like you would a normal backup though, saving your changes to a new backup file.

You can also drop tables, alter stored procedures you name it. I haven’t found a DDL statement I couldn’t execute yet. Just like before, if you need to reset the database just drop it and re-attach it, just like magic you have a clean slate again.

It works across the network. If you don’t have enough space on your machine for the backup file you can still mount it via UNC. This allows me to look at backup files from my development or test servers without having to consume the space to house the database. Don’t expect the performance to be great, you are accessing a file across the network.

Any tool that works with SQL Server works with this. If you have a favorite scripting tool you can use it. Written your own programs? No problem you can use them too.

Never having to restore to a test or development environment again. When you are working with large databases getting an exact duplicate for functional testing is a huge undertaking, not to mention expense. You still have to buy all the disk space. Before SQLvdb I would restore the database, set it to simple mode, issue a checkpoint and then shrink all the files to the bare minimum. On our largest database this would take eleven hours or more. Now, I copy the backup file and attach the virtual database. Copying takes about an hour an the attachment takes about 20 minutes. This is a 380GB backup file of a 1.2TB database. If I didn’t mind accessing it from a network share it would cut that down to 10 or 20 minutes tops.

Never having to restore the entire database to get a single object from a backup again. Same as above but now I don’t have the boss standing behind me for several hours asking “Is it done restoring yet?”

Doing it all from the command line. This means I can automate a ton of stuff and build processes around SQLvdb.

The Bad

The User Interface

I really don’t like the GUI. It is a far enough departure from what we are familiar with that it it makes it difficult to use. Why split up single file and multiple file attachment? It took me some time to find where to change the configuration options as well.

Do I need a Run Queries option? The whole point of the product is being able to use SSMS or whatever you need to access the database. What if I don’t have SSMS installed on my production server?

Tips & Tricks is also a waste of screen space. Why isn’t this in the help system? It doesn’t point out the more creative uses like offsetting restores to development boxes that I can find.

Can’t use the console remotely. It has the option built in but greyed out. With a locked down server getting remote access may be a problem, not to mention all the help is build into the GUI as well.

The wizard for multiple file attach will warn you in one stage about missing or invalid backup files but doesn’t in the second stage. It will let you attempt to attach the virtual database then fail. You can’t edit the virtual database ether. Your only choice is to drop it and start over.

The Command Line

Almost zero documentation on the command line options. I had to root around and play with the options to figure out that there is some switches that are order dependent.

The GUI allows you to kill all users out of a SQLvdb before dropping it, the CLI doesn’t seem to have that option. This is just an extra step I have to put into my automation scripts and hope I get it right.

Other Issues

You can’t run a DBCC CHECKDB on it. Since DBCC uses sparse files and snapshots to get a consistent look at the data it can’t run on a SQLvdb. SQLvdb is also using sparse files to do some of the things it does. What’s worse is the DBCC command just hangs out and looks like it is running OK. I let it run for a couple of hours before killing it.

Doing anything in Management Studio that requires it to pull a list of databases locks up SSMS while the attach is in progress. If you leave SSMS alone it will come back. If you don’t and keep clicking around it can lock it up or crash it all together. I’ve been told they are working on a fix for this.

I ran into some other odd issues that may be bugs or something wrong with my server I don’t know yet. I am working with Idera to identify the problem, when I do I’ll update this post.

Final Thoughts

I can honestly say that this is a great product. It is everything I had hoped it would be. Aside from the small issues it has been a solid purchase. I don’t think Idera sees all the uses for SQLvdb. Being able to get a table or stored procedure from a backup file is nice, but it is an insurance policy. Being able to offset eleven ours of restores to development every week and cut disk space usage by 70%, well that is money in the bank.

Idera SQL Doctor First Look

I recently saw a tweet about Idera’s new SQL Doctor tool that is currently in beta. This differs from other tools you may think of like Diagnostic Manager. DM and other tools like it gather some of the same information but are geared for real time alerts. This is more like the Best Practices Analyzer. It takes a look at several key points on your SQL Server instance and the OS. It makes recommendations on how to improve any problems it identifies. You may ask, does this do anything I can’t do on my own? No, it doesn’t. If you are doing this in house with your own tool set you may not need this, or any other tool like it. To be honest, you aren’t the target audience for SQL Doctor. This is really designed for shops that ether don’t have enough DBA’s to watch everything, or don’t have a full time DBA at all. That isn’t to say it has no use even in a highly monitored and optimized shop. I picked a horribly abused development box and let SQL Doctor tell me just how bad things were.

Idera_sql_doctor1

The interface is clean and simple. First thing we have to do is pick a server and connect.

Idera_sql_doctor2

Next it starts the interview process. It isn’t extremely in depth, and it isn’t suppose to be.

Idera_sql_doctor3

I would be a little worried if I didn’t know if the server was in production or not. But, the person running SQL Doctor may not be the DBA.

Idera_sql_doctor4

I’m sure they wrestled with this one. How do you describe OLTP workload vs. OLAP workload if the person running the tool doesn’t know if the server is production or not?

Idera_sql_doctor5

That’s pretty much all the questions at all. You can change some things in the settings tab like the databases

Idera_sql_doctor6

I’d say the time estimate can be a bit optimistic. It took over an hour to run on this box. The progress bar is a tad misleading as well since it spent the bulk of that time basically at 99% complete.

Idera_sql_doctor7

Looks scary! Some of these problems are. They do scale them according to problem and push the less likely stuff to the bottom for you. It is a lot of information to look at though. 600 recommendations, wow. Lots of index issues and query issues. Time to dig in.

Idera_sql_doctor8

They do a good job of grouping issues together. Like other tools in this category, sometimes the recommendations aren’t 100%. They throw the disk queue around quite a bit but not disk latency, which is really what we are worried about here. It does pick up some pretty nifty stuff that other tools I have looked at don’t really catch. It warned me about a NIC setting being optimized for file sharing and that it could put memory pressure on the box. It also hit on some other fun stuff at the OS level for memory like lock pages in memory that I bet plenty of folks may not be using. I was pretty happy with the level of OS recommendations over all. I am concerned with some of the recommendations that seem to be out of date. It recommended a file per CPU core on tempdb for example. I would recommend that they get a few more SQL Server folks to look at the recommendations and submit tweaks to them. It also will script the SQL Server changes for you or point you to Microsoft Kb’s to get them fixed up. Again, my worry with this kind of tool is someone is just going to blanket run every recommendation, and some of them like disabling the CLR could be detrimental if that feature is on for a reason. I know its hard to get everyone to read the warnings. Lets be honest though, if they don’t know if it is a production OLTP system they probably don’t know if the CLR is on for a reason ether.

I have to say, for a beta and for a tool of this type, I am impressed. With a little extra work I think it will be a worthy addition to any shop that is lacking in deep SQL Server expertise.

Why does change control for SQL Server have to be so hard?

I’ve been dealing with change control and source code repositories for most of my professional career. While I’ve seen change control and integration advance steadily for writing programs it feels like the database part of things is just stuck in the stone age. For months now I’ve been researching solutions for source control, change management, and deployment of database objects. The conclusion I’ve come to is there is no solution. Well, no easy solution. I was very happy in the early days of SQL Server 2005 when they announced source control integration into management studio. It was a great pain for me personally to have Visual Studio, and the solution architecture it offered and not have that on the database side of things. Alas, it wasn’t meant to be. What they meant buy source control was using the previous generation of integration and then crippling it.

Really?

This doesn’t look like much of a solution to me.

I know what most of you are thinking. If you have Visual Studio use it. That works for me but not the people on my team that only have access to SSMS. It also means I have to jump between two tools to do one thing, work with SQL Server. I have been told that Microsoft is basically pushing you to Visual Studio for all of your development needs. Leaving SSMS as a management tool only. If Visual Studio did everything SSMS did it wouldn’t be that big a deal for me personally.

Options Available

SQL Server Management Studio Hacks

I tried several things to work around the limitations SSMS has. I found you could manually edit the solution file to get extra folders. The only problem with that is they all show up as ether Queries or Miscellaneous. Other than that one and the old fix for sorting files by name there aren’t any other hacks I can find.

Toad for SQL Server

Generally has a nice look and feel.It has all the development and management features to be a true replacement for management studio. I tried all the normal things that I do in SSMS in Toad and several things were better. The debugger was nice and the statement optimizer is also a nice addition. It does fall down flat in some basic key areas. I never could get it to display an execution plan. As a T-SQL guy the plan is a must. I know it is a bug somewhere. Having something this fundamental during and evaluation is a big red light though.

The only down side is it doesn’t support Sourcegear Vault/Fortress which is a real shame. Lots of SMB’s use Vault for source control since it is miles better than visual source safe and much cheaper than team system.

ApexSQL Edit

That left only one other contender in this fight. ApexSQL Edit has been around quite a while as well. Initially, it has a similar look and feel to Toad. I know there isn’t a lot that you can do to since both look like Office. I is also missing the management features but I can live with that. The goal is to get the developers a tool they can develop in and use our code repository easily. ApexSQL Edit did include support for Vault and it worked as expected. Again, I started using it daily like I would SSMS. Everything I tried worked, for the most part. 95% of the time it would generate an execution plan. Not as clean as SSMS but it had more options on how to display the plan, which I liked. I did have a few crashes, but this was a beta build and I will let that go until I test the full release. Since this was a beta I did provide feedback and initially the folks at ApexSQL were very responsive. Eventually though everything just went quiet accept for the sales guys asking me how things were going. Right now they are a no go until the stability issues are addressed and the RTM is out so I can do a full evaluation again.

Final Thoughts

What I hoped would be a pretty easy exercise turned out to be a real work out. For all of SSMS’s problems it is stable and familiar. I was really hoping that ether Toad or ApexSQL Edit would solve my problems. I haven’t given up on ApexSQL Edit yet, we will just have to play the waiting game and keep using an inadequate solution until someone comes up with something better.

Hardware Review – EVGA UV Plus+ 16

This isn’t exactly related to SQL Server. I did effect my productivity. I am a monitor junkie. I love multi-monitor setups and have had them for a very long time. Today, having two monitors is trivial. Almost every video card on the market supports two displays. If you want more than that you have to ether step up to a professional graphics card, which can be very expensive, or add another video card to your system. On my rig at home two video cards isn’t a problem I’ve got multiple PCIe slots and run two cards for a four monitor setup. My workstation at work only has one PCIe slot and a PCI slot. Getting a modern card using PCI is harder and harder.

I did some digging and found a card that wouldn’t break the bank and should work with my primary card without having two sets of drivers so everything looked good. I slotted the card in and powered up the computer. Not only did I not have anything on my third display, my network card dropped out too! So, I tried several other older PCI cards to no avail. I knew there were other adaptors out that connected a display via USB. Last time I had checked they were very expensive and didn’t work all that well. I made a trip down to my local Fry’s store. There I saw something truly amazing. Not only did they have USB to VGA they had USB to DVI, and they were under 100 bucks! I settled on the UV Plus+ 16 since it would do 1650×1080 with 32 bit color. The price was right at 60 bucks.

The UV Plus+ 16 is based on the Display Link DL-160 chip. Several other manufacturers also have adaptors based on ether the DL-160 or DL-120 and should be similar in performance and installation. The DL-120 doesn’t support the higher resolutions that I needed to run my 22” monitors.

What You Get

Out of the box you get everything you need.

This thing is small! I was also expecting something that wasn’t in the package, a power supply. You know, one of those lovely wall warts that take up way to much space. To my surprise, the UV Plus gets its power directly from the USB port. They include a carry case for the adaptor but not the USB cable or the DVI to VGA adaptor. To me this makes the case useless. The unit is sturdy and shouldn’t have any issues floating around in my laptop bag when I travel. The name, why Plus+? One plus not good enough? It is going to be hard selling these on eBay. “I give the Plus+ an A+++++.”

Installation

Installing the adaptor is simple.

Install the drivers
Plug it in using the supplied USB cable
Attach Monitor
Adjust settings via windows display properties or tray utility.

That’s it.

Once installed changing the options is just like working with any other monitor attached to your system.

If you don’t want to go that route you can always use the try icon control application that comes with it.

I actually moved the UV Plus to power a monitor I have that rotates from portrait to landscape since the tray utility handles it so well.

There are other benefits to running these adaptors. You can have six, yes six attached to a single system. Unless you are running an Nvidia card then you are limited to four adaptors. This is going to allow me to move up to six displays and remove the second video card in my primary workstation at home. With the free PCIe slot I’m putting in a hardware RAID controller, you can never have enough IO!

Experience So Far

Has been pretty uneventful, generally it just works. I did have an issue with opening a PDF from inside adobe reader in full screen mode. The display became corrupted with blocks and color bands. I just used the tray tool to turn off the display then back on and it was fine again.

Conclusion

The UV Plus+ 16 is EXACTLY what I needed. It is cheap, easy to use, and small. All of these things add up to a solid little device with lots of possibilities. If you need a multi-monitor setup for work this is a great solution for you. If you need a multi-monitor setup for gaming then you need to go with the right PCIe video card setup.

Product Review: SQL Pretty Printer

SQL Pretty Printer for SQL Server Management Studio $39.95 single user $99.95 site license

having coding standards is a must for solid quality code. There are lots of articles on the subject like this one on simple talk. Having standards is one thing, getting everyone on board is another, that’s where code formatters or beautifiers come in. I’ve used code beautifiers for may other languages over the years and have written a couple to help enforce coding standards. One of my pet peeves working with the default tools that ship with SQL Server is the lack of any kind of code formatter. When you work with more than one developer you will get code that is ether hard to read or formatted to their specific taste. That by its self isn’t a huge issue but when you are dealing with hundreds or thousands of stored procedures and other code bits written in T-SQL that you now have to dig into in can be a time sink. This is only compounded in an emergency where you are having to quickly look at a piece of code and figure out what exactly is going wrong. To add insult to injury SQL Server will also help mangle the code for you and good. Heaven forbid you should script out a complicated view and watch about 400 lines of code squeezed into say 10. There are some options out there to help with this, I had one criterion that had to be met though, it must integrate with SQL Server Management Studio. This one requirement stripped down the number of candidates very quickly. If you need to format a smallish piece of code there are several online formatters, the authors of SQL Pretty Printer have one at http://www.dpriver.com/pp/sqlformat.htm.

So, with that requirement I found a couple at the time that met the requirements Red Gate SQL Refactor and you guessed it SQL Pretty Printer. SQL Refactor offers a lot more than code formatting and has one feature I really liked that expands wild cards into column listings, but they want $369.00 for it, per user. I also had a problem that after the trial expired I uninstalled and it left grayed out menu items in SSMS that now just annoy me, and I don’t know what to do other than a reinstall of SSMS to fix it. After looking at SQL Refactor I loaded up SQL Pretty Printer. It doesn’t offer some of SQL Refactor’s options but it does reformat the code, it also will convert your statements to work with C# and VB.Net. It will also grab sql statements out of those two languages and put them back as standard T-SQL After using it for a couple of weeks I really grew to like it, it was quick and easy CTRL-K-CTRL-H and *POOF* code was formatted, well most of the time we will get to that.

It has several customizations that control the formatting its pretty straight forward through the options dialog.

With the preview window its pretty quick to get the formatting the way you like it. The things I like about SQL Pretty Printer: Simple install and direct integration into SSMS/VS. This is just a huge time saver for me, there is a stand alone client that has more features but using SSMS all day not having to flip applications, cut and paste to format is a real win. Fire and forget. Once you have it setup it just blends into the system a keystroke and everything is formatted, or just the text you select. Inexpensive, Since the site licenses is low enough getting it installed on every developers machine isn’t a barrier giving everyone the ability to use the same code formatting standards.

The issues I have:

Formatting fails to render. Sometimes a block of code I have selected doesn’t format, instead it disappears. Yeah, it sounds bad but the CTRL-Z undo works so I haven’t lost anything. I will be submitting a bug report.

Un-parse-able code doesn’t format.If it isn’t a valid SQL statement it won’t attempt to format it. Not a huge deal, but I like to format as I go if possible.

Needs more formatting options. It’s hard to please everyone but you can start with me :). I would like to see more options for code style how things rap and split lines.

It’s simplicity and affordability make it a win in my book. If someone comes up with a better formatter at a cheaper price I’d love to see it.

Wes