The Chipmonks on Virtualization

Here is a link to a 30 minute talk on Virtualization for Multnomah County. There was a problem with recording the audio, so this one-hour presentation turned into a 30-minute chipmonk fest.

Situational awareness with SqlContext

It's often handy for your code to know where it's running. I have a habit coding data access classes that use the caching mechanisms built into the System.Web namespace. These components can only use that cache when the assembly is hosted in IIS. This creates issues when I'm building unit tests that exercise the those classes outside of IIS. To avoid bogus caching errors the class can check to see if it's hosted by IIS by checking HttpContext.Current. If it's null then don't attempt to cache. If it's there, then go ahead and use it.

SQL Server exposes a similar context to your managed code called SqlContext. SqlContext has a number of methods to allow you to tap into the Sql Session that is calling your code. A simple check to SqlContext.IsAvailable will let you know if you're running on the server. Beyond that, you can explore the SqlContext.WindowsIdentity, TriggerContext or the Pipe. These will be topics of other posts, but for now we'll stick to basics.

It's possible for your managed stored procedures to perform data access. This feels a little inside out, but you can create an ADO connection inside your managed code and perform queries or updates. If you do this, you should use a special connection string that is only available when running on the server. Here is some code:

String conncetionString
If(SqlContext.IsAvailable){
connectionString = "context connection=true";
}else{
conncetionString = GetYourRegularConnectionString();
}
SqlConnection con = new SqlConnection(connectionString);
…

This simple check will allow you to write code that will run on the server, and still allow you to cover the code with unit tests. Hmmm…that's nice.

I Yield for Table-Value functions

One of the coolest set of complimentary features in SQL Server 2005 and Visual Studio 2005 can be found in the ability to code a Table-Value function using C#. In this exercise we will create a simple SQL Function in C# that returns a SQL table. Coding a Table-Value Function requires implementing two methods. The first method represents the entry point of the function. We decorate the method signature with some additional information defining the structure of the returned table, as well as naming the other method in the class that will return the columns for the table itself. Start by creating a new database project and connecting it to your sample database. We will not be using any data tables, so any database will do. Add a new function to the project and call it "SampleTable". All table value functions return IEnumerable. There are a number of framework classes that implement this interface in the System.Collections namespace. A new language enhancement in C# greatly simplifies the implementation of IEnumerable: the yield keyword. Yield allows you to return from the function in the middle of a loop. The internal state of the loop is maintained and the function can be called multiple times, with each successive call returning the next value in the loop until the loop is exhausted.

Here we see the fully coded function. Attributes define the column definition of the return table. It's a little unfortunate that this definition is a string, so there is no compile time error checking possible. Internal to the method we define one array for each column, fill it up with data and then loop thru each item. Notice that because we can only return a single value from the function, we package up each row to be return as an array of objects. The yield will return one row at a time. The other method attribute is the FillRowMethodName. This is the name of a method that SQL Server will call for each row fetched to redefine the columns. It will accept a generic object, and one out parameter for each column defined in the TableDefinition.

 

Our FillRow function gets passed the return value from SampleTable as an object, and one parameter for each column in the table. Its purpose is to explode the object into its constituent elements and fill in the needed columns. Notice how our two methods are tightly bound to each other. FillRow must have complete knowledge SampleTable implementation.

Go ahead and deploy your project from the build menu, then switch over to SQL Server Management Studio to test your work.

That was pretty darn easy if you ask me, and not that much code.

Drop Express and Go Pro

So far I've been using Visual C# 2005 Express edition to compile my CLR classes. Those classes can be loaded into SQL using T-SQL scripts and a deployment drop-point shared between the two environments. Visual Studio 2005 and SQL Server 2005 enjoy a level of integration that could shorten this deployment path. The version of the IDE that ships with Visual C# Express is missing a few pieces that I'll need to exercise these integration options, so we'll be saying goodbye to Express and load Visual Studio 2005 Professional Edition. I'm a bit tempted to try using Orcas, but with my newness, I better stick to the shipping product. Again, I'll be loading this on my Client PC, not the SQL Server (just in case you were wondering). I'm going to perform a custom install, skip installing SQL Express, VB.Net, J#, C++, and Crystal. All of our exercises will be in C#, and we have a real Instance of SQL Server 2005 Enterprise Edition as a deployment target.

While the install is running, now might be a good time to review the configuration we will be using for the remainder of our adventures: two (2) physical machines, the Server is running Windows 2003 R2, has Virtual Server 2005 R2 loaded as well as application roles for IIS and Active Directory Domain Controller. A Virtual Machine has been created on the server running Windows 2003 R2 and has SQL Server 2005 Enterprise installed. The other physical machine is acting as my development workstation and is loaded with Windows Vista CTP, Office 2007 and Visual Studio 2005 Professional Edition. It would be possible to re-create this lab environment using a single machine with Virtual Server. You would need quite a bit of memory, but it would be possible. The server role we have not talked about yet is Active Directory. Because we are using Windows Integrated Security as the only access method for SQL Server, it's important to actually build up an Active Directory network or hold your user account and computer domain members. Active Directory is where we may need to set up service accounts (user accounts that services can run under) as well as any groups. I can't stress enough how important it is to understand Active Directory Security. If you are not running a domain controller on your home network, take the plunge. Just don't load up too much other stuff on that same server. Domain Controllers have a special flavor (or smell) to them and I would not suggest running SQL Server on one. The exception to this is Small Business Server that is a special all-in-one version of Windows Server 2003 that gives you one stop shopping. I believe an instance of SQL Server gets loaded along with Active Directory, SharePoint, Exchange, and some other stuff.

Meanwhile, back at the freshly installed IDE of Visual Studio 2005, let's try to get a function to deploy the fast way. Just for grins, I'll create a connection to my SQL Server using the Server Explorer. I notice that along with my usual database objects (Tables, Stored Procedures, Functions…) I also have a section for Assemblies. Sounds encouraging. I'm going to code up a dummy project that I would like to be my SQL function library. In "Visual C#" I'll pick "SQL Server Project". Please note that this is different from the "Database Project" in the "Other Project Types". I'll call mine "ManagedSqlLibrary". Once the Project is loaded, I'll associate my SQL Server Connection and Add a new User-Defined Function. I'll call my function EasyDeploy.cs and we get the following code generated for us:

We can see quite a few things going on in the code. For one, we get a new namespace Microsoft.SqlServer.Server that has a part to play in the attribute that is decorating our function. We also see that this function is a partial class of a bigger class library called UserDefinedFunctions. If I want to deploy this to SQL Server I simply select "Deploy" from the "Build" Menu. Back over in Server Explorer, if I refresh, I see that some stuff was definitely deployed. Too easy!

So how to test? There is a Test.sql file conveniently located in the "Test Scripts" folder of the project. Let's add our own script called "TestEasyDeploy" containing some simple testing code, set it as the default test script and Run. I experience an interesting dialog telling me that the firewall on my machine is currently blocking remote debugging. I opt to unblock the ports, but get another error about it not being able to do so. I suspect Vista is the culprit and dive into the control panel looking to unblock TCP Port 135 for DCOM and UDP 4500/ UDP 500 for IPSEC. The final solution was to add DevEnv.exe (the IDE) to the Application Exception list on the Firewall.

So we've found a much simpler way to build and deploy our SQLCLR objects during development

An Argument for Managed Functions

To be honest, I’m having a hard time resolving when you would actually want to run managed code in SQL Server. This might be a reflection of my newness to the concept, but in general is seems there are limited, very specialized cases (like complex math or financial functions) where you would need it. Then it hit me: you can share utility functions with your applications.

Here is the scenario: you have a business rules utility library that has a bunch of static methods for validating data. Said library is chuck full of regular expressions you downloaded from web sites (where people really know how to write them) and you’ve wrapped them all with friendly names like IsSocialSecurityNumber() and IsDate(). The later even accounts for leap year and is a complete mystery to you.

Now you’re tasked with importing data from external source and you’ve decided the simplest way to do this is straight into SQL server. You’d like to save yourself a ton of effort by leveraging those regular expressions and now you can.

Compile your code using visual studio into a class library called ValidationLibray.dll and move it to a deployment drop point that SQL Server can see.

Next, create the assembly in SQL Server and create new SQL Functions to call your external ones.

Now you can leverage your managed code in SQL as well as your .Net apps and have one codebase for simple validations. Pretty cool.

I did notice that SQL is much pickier about what code it will allow to run. For instance, if you are a fan of the lazy load pattern for your classes (like I am), you’ll be disappointed that SQL considers this code “Unsafe”. So I ended up re-writing my class (even to the extent of marking private properties readonly) so it would load into SQL Server.

Another thing I noticed right off is that while I’m used to using Boolean types in C# (bool) there is no Boolean in SQL Server. I got lucky and used bit for the return type of my function. SQL is doing some implicit type conversion for me. It’s more common for me to use Int for my Boolean functions in SQL, but bit is the same as int (bit 1 = int 1) so no big deal there.

I hope to find more good reasons to incorporate the new CLR support in SQL Server, but I’m still skeptical and worry about performance.

VM Tip #3 Get Small

VHDs get big, really big. You might think an OS and a bunch of software is big, but just go look at your VHD. It's huge. I just installed Windows Server 2003 R2 and SQL Server 2005 on a fresh image and it's over 6GB. Does that sound right? Whether it does or not really isn't the point, over time you will eventually end up with a bloated VHD. When you do, follow these steps:

On the Guest OS:

1. Get rid of all the junk files you have laying around, run disk cleanup, empty the recycle bin.
   
2. Defrag your drive using a tool that moves all the used space to the top of the drive.*
   
3. Run the Precompact.exe utility. **
   
4. Shut Down the Guest OS
   

On the Virtual Server:

1. Inspect and then Compact the disk using the Virtual Server Admin web. ***
   

* Here is the defrag utility I use:

http://www.flexomizer.com/PermaLink,guid,ce99367e-158c-487a-879d-b32145cc1957.aspx

** You can find precompact.exe on the Virtual Machine Additions ISO that came with VPC or Virtual Server.

*** If your using VPC, then you should run the Hard Disk Wizard and make sure and check the Compact Option

Giving SQL some Class

One of the fancy new features in SQL 2005 is the ability to run Managed Code (C# or what-have-you) on SQL Server. That's where this blog gets its name. We will be exploring many facets of this integration but first, let's just jump in with a simple "Hello World" exercise. SQL treats your class code like any other object in the database, first you have to code the class and build into an assembly. Then you deploy your assembly to SQL using the CREATE ASSEMBLY statement. To build an assembly and a class we need some flavor of Visual Studio. Just for fun, I'm going to use Visual C# 2005 Express because it's free (and only a 30MB download) and I've never checked it out. The Installer gives you the option of installing SQL Express, but that's just going to confuse me so I skip that. I'll also skip the 200+MB MSDN library install because I'll be getting my help online.

Firing up the C# IDE I'm creating a new Class Library Project called HelloCLR. I'll Code a simple class called Say and a public property called Hello. It looks something like this:

using System;
using System.Collections.Generic;
using System.Text;

//please notice there is no Namespace defined
//more on this later

public class Say {
public static string Hello() {
return "Hello From Managed Code!";
}
}

I'll need to create a place to put this assembly, so I create a folder on my SQL Server box and grant myself some rights to it. In a production environment, we would not be creating shares on the SQL Server, but for now it's OK. Later on we'll talk about deployment issues. Now I'll build my solution and move it out to my deployment drop point.

C:\Projects\HelloCLR\HelloCLR\bin\Release\HelloCLR.dll
…Move this to…
\\NoSQL2005\DeployDropPoint

We are done coding, so fire up SQL Server Management Studio on the client machine and attach to our SQL Server instance. I notice I don't even have a testing database set up, so I'll create a new one that will hold all our exercises and call it SqlClrDB just for fun. Once selected in the Object explorer, I'll hit the New Query button. And attempt to add my assembly to the database with the following script:

create assembly HelloCLR
from 'c:\DeployDropPoint\HelloCLR.dll'

My command completed successfully. Now to access my Hello() method I'm going to have to wrap it in a SQL Function. So let's do that:

create function HelloClr() returns nvarchar(max) as external name HelloCLR.Say.Hello

and finally to see the fruits of out labor:

select dbo.HelloCLR()

but I got an error instead!

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Such a bummer. Now what? Seems the default install is to disable the .Net Framework. One way to turn this on is to remote into your SQL Server and run the Surface Area Configuration tool. There is a CLR Integration option that you can enable. If that sounds like too much fooling around with the mouse, you can also issue this:

sp_configure 'clr enabled', 1
go
reconfigure
go

and finally, when we re-issue the call to our function:

select dbo.HelloCLR()

------------------------

Hello From Managed Code!

(1 row(s) affected)

 

What did we learm from this exersize?

1. You can load your Managed Code Assemblies into SQL Server 2005.
   
2. You can call static methods from you classes if you wrap them in a SQL Function.
   
3. We hope to learm more about namespace and how they affect assemble loads.
   
4. The CLR is disabled by default when you install SQL Server 2005.
   
5. There are deployment issues moving your assembly to a location where SQL Server can see them.
   
6. Hello world in the SQLCLR world is just as boring as it is in any new platform.

Virtual Server and Virtual PC

I noticed that there is a new version of Virtual PC on the way for 2007. I read from the brief that support for Vista was added so I thought I'd check it out on my Tablet. I've used Virtual Server on a daily basis for a while now, and VS is the cornerstone of a virtualization effort going on at the office. I blindly loaded VPC2007 on Vista, uninstalled VS, and loaded my VHDs. With a few barks and grunts, VPC was able to load my VS VHDs just fine. I started poking around on the simplified interface and immediately felt functionality freefall. Assuming VS bias I started researching the differences and immediately found a great article from MS clearly explaining the differences:

http://www.microsoft.com/downloads/details.aspx?familyid=8ED0A6CB-0F24-408E-AF8F-51EDF508D361&displaylang=en

In a nutshell, VPC 2007 is a great product if…

1. Your running Vista as the host OS and are having troubles with the new User Account Control and your Virtual Server.
   
2. Your using a single PC for server and client.
   
3. You like a richer experience like dragging and dropping files between VMs and hearing sounds.
   
4. You want to try out Vista in a VM.
   

On the other hand some of us appreciate a greater level of control and don't really care about ease of use. So…

Virtual Server 2005 R2 SP1 is a great product if…

1. You running it on a server
   
2. You don't care about Vista quite yet
   
3. Said to yourself "Who needs drag and drop when you can just create a file share on the V-LAN" when you read #3 above
   
4. Would like to use BOTH of your CPUs
   

I'll prolly keep VPC on my tablet, but that's really the only place it makes sense for me. The rest will run Virtual Server.

VM Tip #2 Use Remote Desktop Connection

Once you get to the point of being able to see your VM on the LAN, I drop the web admin console and that goofy ActiveX control in favor of a Remote Desktop Connection. Not only is the user experience so much more pleasant (like being able to full-screen) but you also have additional features like the ability to expose client drives to the VM to simplify moving things around. This is especially handy when your Remote Desktop Client is the Virtual Server itself. I've noticed in some instances (like when the VM is not attached to a Domain) that Remote Desktop cannot resolve the machine name properly. I've been able to resolve this by creating a Virtual Network that includes the Guest OS and the Virtual Server NIC. Once the Client OS is attached to both the Virtual Server NIC and the Virtual LAN, the name resolution kicked in. My suggestion is to always attach you VMs to the same domain as the Virtual Server and the Client machines that are accessing it. Windows Integrated Security plays much nicer when you don't have to deal with domain boundary issues.

If this sounds too complicated, and your running your VMs on your laptop, you might want to look into Virtual PC. It gives you the improved user experience without having to go thru RDC. Be advised that there are less features and you limited to a single processor.

Sql 2005 Installation (part 2)

OK, a few days later and I'm not really sure where I stopped. I'll fire up the VM and see where we are at…

Looks like SQL completed installing on the server, and I'll test that by remoting in and launching the SQL Server Management Studio, connecting to localhost and there I see databases. All is good. Now I'm not really hip to running all the client tools on the server, so I'm going to install just the client tools on my client workstation, laptop, whatever. In my case it's a AMD Shuttle running the Vista CTP, so I'm taking some chances using beta software. I'd suggest using an XP box or another Windows 20003 Server instance. Either way, it a good idea to get the client tools on a client and the SQL Server on a server.

The client tools setup screen default to installing nothing. So I'm going to select Management Tools, Business Intelligence Development, the SDK, and SQLXML from the Client Components group as well as Books Online from the documentation. If I need the sample databases later, I'll get them when I need them. I prefer to build up my databases from scratch so I can really feel the pain.

Something I'm completely ignoring here is security. In my lab environment I'm a domain admin. It's typically a bad idea to run with such privileges, but I'm more concerned with feature additions to SQL Server 2005 and I will be creating a variety of lowered privileged user accounts as we go.

Client Setup is complete and I'll verify my connectivity the same way I did on the server, by running the Management Studio and connecting to my instance using the server name of NOSQL2005. You should use whatever machine name you installed you instance on.

The Management Studio in the replacement for Enterprise Manager and Query Analyzer from SQL 2000. The Business Intelligence Studio is an IDE for creating SSIS Packages that replaced MTS Packages. There are some pretty fundamental shifts in this version. As I understand things, MTS and SSIS are so different, that there is no upgrade path. You basically need to rethink and rebuild. This should be a major bummer for organization that reply heavily on MTS packages and SQL jobs. There may be quite the market for good SSIS developers over the next few years.

That's it for getting set up. We now have a client machine, a server set up on a virtual machine and all the software loaded. We will be taking "A Developer's Guild to SQL Server 2005" one chapter at a time recreating each example. This could take months J