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
0 comments:
Post a Comment