Installing a trial of SQL Server 2014 and Windows Server 2012 in a VirtualBox VM

It's sometimes really useful to get a trial copy of SQL Server to play with. There are a number of reasons for this: certification testing, validation, upgrade testing, skills training, experimentation, recovery trials, etc.

There are a lot of caveats, however. You don't want to install it on your work machine because you don't want to mess up your current configuration. The next best option is using a virtual machine. But you don't want to spend hours or days installing and configuring Windows and you don't even have Hyper-V running on your operating system (for instance, Windows 7).

Luckily, all these problems have easy workarounds. Here's a preview:

1. Download the VHD file for the Windows Server 2012 trial
2. Downlload and install VirtualBox
3. Windows is already installed! 
4. Download the ISO for SQL Server 2014 
5. Mount the ISO virtually in your virtual machine
6. Start the virtual machine
7. Install SQL Server as you please
8. You're done! 
9. However, if you're really interested, you don't even need to sign in to your new VM to access SQL Server. Just make it accessible to the network and make sure your VM host and guest can talk to eachother. Then just install your Client Tools (such as SSMS) on your host and you can remotely manage the guest. AMAZEBALLS!

This process looks longer than it actually is. The most tedious part about this will be waiting for the downloads, honestly. 

Let's get started!

Download the VHD file for Windows Server 2012 Datacenter (Trial)

This trial version of Windows comes in a pre-packaged VHD (virtual hard drive) file directly from Microsoft here: Microsoft Windows 2012 Trial

Download it and put it somewhere safe on your local machine. From here forward, your local machine will be known as the host machine.

Download and install VirtualBox

Download from here: Download VirtualBox

You'll want the 64-bit version for Windows. Put it in a safe place on the host machine and then install it. You shouldn't have to worry about install options.

Create a new virtual machine using the VHD file

This is easy. Just start up VirtualBox and click [New]. When you're setting up your virtual machine, make sure to choose Microsoft Windows for the type and Windows 2012 (64-bit) for the version. See the screenshot. 

Next, you won't want to create a new hard drive. Instead, use the one you downloaded from Microsoft. 

You will most likely receive an error that the hard drive already exists See the screenshot. The error is approximately "Cannot register the hard disk file because a hard disk with UUID already exists." If that's the case, you need to cancel the process and go to the main VirtualBox window. From there, click the File menu, then Virtual Media Manager. Delete the old hard drive from the list. It will ask you if you want to delete the file -- don't do that (unless you'd like to redownload the file). See here for more details on the error "Cannot register the hard disk file because a hard disk with UUID already exists".

Next, you can play with the settings of your Virtual Machine. Give it as much or as little memory as you feel appropriate. Give it more or fewer CPUs. Do what you feel is appropriate for your situation. The defaults should run adequately for demonstration purposes. 

Here's some extra help: Microsoft VHD images on VirtualBox (although not everything in the article is accurate or applicable to this situation

Download the ISO for SQL Server 2014 

Download the Microsoft SQL Server 2014 trial. Make sure to grab the ISO.

Install the ISO as a Virtual DVD in your VM

Go into the settings of your VM, click on Storage, then click the little icon of a CD next to the CD/DVD Drive. Then choose "Choose a virtual CD/DVD disk file..." and browse to your SQL Server ISO file. Save the settings.

Start your Virtual Machine

In the Oracle VM VirtualBox Manager window, highlight your server and then click start. It will take a while to boot and it will look a lot like a Remote Desktop session. Jump through all the standard hoops. 

Next, you're wondering, how do I send CTRL+ALT+DEL to the Virtual Machine to log in? Click on the machine menu and then click on "Insert Ctrl-Alt-Del."

Install SQL Server

Once you're in Windows, go to This PC and double click the installation DVD for SQL Server. Then click the setup icon to start the installation. 

It is beyond the scope of this article to help you install SQL Server. If you're a SQL Server admin looking to become certified, this is the part when you should grab your certification manual and follow along so that you understand all your installation options.

Special note: You will need to install .NET 3.5 ... this is not automatically included in Windows Server 2012, but you can add it as a feature. Check out To install .NET Framework 3.5 by using the Add Roles and Features Wizard (scroll down about 3/4 of the way).

Install Guest Additions

VirtualBox guest additions are some utilities and features to help VirtualBox talk to your virtual machine and speed it up a bit. To install them, while your machine is running, click on the Devices menu then click on "Insert Guest Additions CD Image." Next, inside the virtual machine, go to This PC then open up the VirtualBox Guest Additions CD Drive and install VBoxWindowsAdditions. The installation is straightforward. Make sure to allow the installation of all drivers and to reboot afterwards.

Remotely Access Your Server

Your virtual machine is also known as the guest machine. You can install just the SQL Server client tools in your host machine and remotely administer the server running your guest machine. That configuration is also beyond the scope of this article, but most likely I will post a second article describing that. In the meantime, fire up your guest machine and start SQL Server Management Studio (under the Start menu or the All Apps menu). 

Good hunting!

SQL Server Agent Restart Notification / SQL Server Database Cluster Failover Notification

I work in an environment running a SQL Server Cluster (2-node, Active/Passive) on the production server. I want to know when the SQL Server Cluster fails over or when it's tested. It seems to have happened a few times. Since our IT department is somewhat regimental, I don't have access to a lot of system administrator functions. However, there is still a simple way to get that notification.

When I was researching this problem, I came across this:
SQL SERVER: How Can I Get Notification Alert When Fail Over Happened? This got me started down the right path but it turns out to be simpler.

First, a bit of background on the fail over. When fail over occurs, the clustering services on the remaining nodes detect that failure. They attempt to shut down services on the failing machine. As soon as that completes or when the machine is deemed inaccessible, the clustering service starts SQL Server services on the new active node. We can take advantage of that stop service/start service process because SQL Server Agent has a hook in its scheduler that can start a job when SQL Server Agent starts (see the screenshot to the right).

Here are the steps:

1. Create the new job.
2. Name it something like "Agent Restart Notification".
3. Add at least one step. 
• It can be an empty step with some do-nothing code in it, but it has to be there (otherwise the scheduler will not enable the job).
4. Add a schedule, but choose Start automatically when SQL Server Agent starts.
5. On the notifications page, send an email when the job completes.
1. Please note that you must have Database Mail enabled and properly configured.
2. You must also have an Operator properly configured.
6. Save the job.

That's it. Next time the SQL Server Agent restarts, you will receive a notification. There's one problem here, of course. What if the SQL Server Agent restarts because of some other event, like someone manually restarting it? Unfortunately more complex code is necessary for such a specific event. To get started with that, see the above referenced blog post. As for me, this serves my purposes well enough. 

T-SQL Error: Each GROUP BY expression must contain at least one column that is not an outer reference

I've written thousands (perhaps tens of thousands) of queries in at least half a dozen database engines. I consider myself pretty good at this query business. Unfortunately, I run into a quizzical error message every once in a while that makes me scratch my head.

Msg 164, Level 15, State 1, Line 163:Each GROUP BY expression must contain at least one column that is not an outer reference.

Gnarly.

In general, I feel that Microsoft's error messages leave something to be desired. Oh, for instance, meaning, context, and substance.

Here's the story. I had a regular old query and I wanted to sum the financials for each period.

SELECT 
 X, 
 Y, 
 @CURRENTYEAR as CurrentYear, 
 SUM( Z ) AS Z
FROM
 FINANCIALSUMMARY

No problem, right? Just add the non-aggregate columns into a GROUP BY statement and VOILA! But no. Didn't work after all. Why? First, let's parse the error message to see if we can actually get any meaning out of it.

• expression
• Any value made of constants or variables combined using operators
• examples:
• 1 
• 10
• @x
• 10 + @x
• must contain at least one column
• Columns are by definition part of a table or table expression! Therefore any expression that doesn't have a column must be excluded. 
• that is not an outer reference
• This is a very abstract concept in terms of the SQL query language. I can't really do it justice here in one sentence, so I recommend reading up on something like Aggregates with an Outer Reference. 

Because there are variables that do not make reference to columns in some of the expressions, those expressions cannot be part of the GROUP BY clause. In other words, I had to remove the variable  @CURRENTYEAR from the GROUP BY list.

T-SQL Error: The multi-part identifier could not be bound

Sometimes when I work with different databases I forget the rules that are exclusive to each one. For example, today I wrote a trigger in T-SQL (SQL Server) that would have probably worked in PL/PGSQL (PostgreSQL). When it bombed with a syntax error, I had to scratch my head. I read most of the manual page from Technet but had no real luck finding the answer. I've been trying to use my own mental knowledge-base to figure out the answers to a lot of questions lately, but this time I was under an approaching deadline so I just Googled it.

Here's the problem:

 
CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO 
AFTER INSERT, UPDATE AS 
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( inserted.REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = inserted.ID;  

 END

END
GO

Why didn't this work in T-SQL? Because inserted is a table and I'm trying to reference a column. The error message I received was The multi-part identifier "inserted.REF" could not be bound.

So it turns out that there's no way to directly reference a table in T-SQL without using a SELECT statement. Unfortunately, I write triggers so rarely that I forget this because it seems somewhat illogical and annoying to me. In PL/PGSQL that's not the case. In that language, the inserted and updated sets is a record variable that can be referenced directly, so referencing something like inserted.REF is perfectly legitimate. That's one of the really annoying "missing features" of T-SQL. So the solution is simple but long-winded.

CREATE TRIGGER 
 dbo.tr_FOO
ON  
 dbo.FOO
AFTER INSERT, UPDATE AS 
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- In T-SQL, 
 DECLARE @ID char(10);
 DECLARE @REF char(6);

 SELECT
  @ID = NID,
  @REF = REF,
 FROM
  inserted;

        -- Insert statements for trigger here
 IF( UPDATE( REF ) AND ISNUMERIC( @REF ) = 1 )
 BEGIN

  UPDATE
   dbo.FOO
  SET
   REF = 'Y'
  WHERE
   ID = @ID;

 END

END
GO

On another note, SQL Server has an AFTER and INSTEAD OF triggers but not the extremely useful BEFORE triggers that are in PostgreSQL. Dear Microsoft: Fix these two annoyances and I will love you more!