Amazon has just done something amazing... Amazingness... They are essentially doing a conditional year free of their services. While I am a huge fan of VMWare, what Amazon is offering is something completely unmatched by VCloud or MS Azure.  (Please see Craig's comment as this claim has the possibility of being debunked. Thanks Craig!)

So what can I do with this you might ask? Here are some ideas: (I am doing these off the top of my head so feel free to criticize them by suggesting a better ideas)

1. If you have never played with Linux/Ruby or anything else, here is a good/free way to to dabble without the 'will this mess up my system' fear. Microsoft is continuing their adoption of features from these platforms and it can only benefit you to see these ideas they are using from Ruby(gems) or Apache(modularization). The more you know, the more you grow.

2. NOSQL? What is a NOSQL? Want a simple way to learn about NOSQL? Then try out Amazon's simpleDB. They have various demos and such you can run and play with and start trying to see what all the hype is about. Remember fellow fearful DBA's, NOSQL stands for "Not Only SQL" not for No - SQL as in SQL Void.

3. Embrace the awesome of Amazon S3. Ever wonder why Dropbox/JungleDisk and various other companies can give you so much storage for so cheap? Amazon S3 is pretty much the answer to that question. I typically use the cheapest hosting plans possible and S3 as a CDN to smoke/mirror performance.

4. SQL Server testing - Microsoft provides trial software for pretty much all their projects. If you are curious about ... say playing with some feature of replication or maybe wondering about online indexing ... Fire up 1 or more instances of EC2 and actually try it. You no longer have an excuse to put it off.

5. .Net testing or running a service - With shared hosting, I don't have a way of keeping an ongoing on demand service per say... So why not have a micro instance where you can deploy services and such. For example, say you need to add SMS services to your app.... Sign up for Twilio, create a little service, throw it on your free AWS EC2 instance and poll every hour or so. Most shared hosting sites don't really support SQL Server SSIS... so why not have an AWS EC2 instance that fires up at night and starts some SSIS packages?

Now, some of this isn't without a learning curve, but we are in the software development industry... if we can't navigate learning curves or don't even pursue learning to begin with we add more nails to our career coffin as well as start becoming the problem.

As you start playing with SQL CLR, you learn pretty quick that the built in namespaces can be a little handicapping. You can skip this by creating an 'unsafe' assembly in your database. In the following demo, we're going to load a 3rd party .library for XMPP messaging into our SQL Server instance, and use a stored procedure to send XMPP messages.

The XMPP library I'll be using is agsXMPP. As you'll see in my code below, I tend to just use a common shared library folder so I can house various libraries and it makes it easier from an organizational standpoint to cram them into the database. I use the following code to 'cram' the library into my instance so I'll be able to use it in a CLR procedure:

 
CREATE ASSEMBLY [agsXMPP]
FROM 'C:\Subversion\Shared Libs\agsxmpp\agsXMPP.dll'
WITH PERMISSION_SET = UNSAFE
GO
 

Once that is out of the way, we can roll our CLR proc, deploy and message to our hearts content (or until the network drops)

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
using agsXMPP;
using agsXMPP.protocol.client;
// yes,  domains and names have been changed to protect the innocent servers.
public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void clr_SendXMPP()
	{
		XmppClientConnection xmpp;
		xmpp = new XmppClientConnection();
		xmpp.AutoPresence = true;
 
		xmpp.AutoResolveConnectServer = true;
		xmpp.Port = 5222;
		xmpp.UseSSL = false;
		xmpp.Server = "xmpp.datachomp.com";
		xmpp.Username = "SQLAlert";
		xmpp.Password = "SqlClr";
 
		xmpp.Open();
 
		xmpp.OnLogin += delegate(object o) { xmpp.Send(new Message(new Jid("Gatir@xmpp.datachomp.com"), MessageType.chat, "This really should be a variable yes?")); };
	}
};
 

Start: Anyone who has done scripting tasks in SSIS knows how valuable being able to leverage .NET can be when you are building data solutions. Based on a recent presentation, by Tim Mitchell, I was encouraged to extend .NET past SSIS and start exploring SQL CLR. Let's get started:

First, we need to enable CLR on our database:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Next, we fire up Visual Studio and create a Database project. C# is my language of choice so I choose it:

After that, it will likely ask you for some DB credentials, much like creating a datasource when you are doing other .net applications. Like other .NET solutions, you can right click on your project, go to properties and change your connection on the 'database' tab. Once you get your project up, right click on add a new item, and select stored procedure. Give it a name bearing in mind that the name you give it will be what the procedure gets deployed as. Once created you will see that VS takes care of a lot of the plumbing for you:

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void clr_Chomp()
	{
		// Put your code here
	}
};
 

Let's throw in some code:

 
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void clr_Chomp(out string Chomps)
	{
		Chomps = "Chomp Chomp Chomp Chomp";
	}
 

What did we add? We basically declared an output parameter (out string chomps), and then said that variable is just going to return the text 'Chomp' X 4.
Compile, and deploy. Gotcha - For those of us that use VS 2010 our default framework is 4.0 .... this won't jive in SQL Server so you will get a successful build, but a failed deployment. Follow the yellow brick errors and change your target framework down to 3.5 (if deploying to SQL Server 2008). Once that is done, it is time to see this guy in action!!!
Make sure you are in the proper database and run it:

 
DECLARE @words VARCHAR(25)
EXEC dbo.clr_Chomp @Chomps = @words OUTPUT
SELECT @words
 

and it should spit out some Chomps for you. If it worked, go reward yourself with some M&M's. If it didn't work for you then:

 
GOTO START;
 

In Part 2, I will begin leaving the playground of basic examples and start showing real practical implementations of SQLCLR.
In Part 3, I will likely look at performance and some other items to help round out the series.

So hopefully you have moved to databasemail and started enjoying its many benefits. Remember that cool benefit of it logging all the emails for you and such? Well that comes at a price, and the price is an ever growing MSDB. So, you should consider setting a retention date for your servers and create or add the following to your maintenance packages/jobs.

This script will clear out the mail items and the log based on the @Retention_Days variable:

 
DECLARE @Retention_days SMALLINT
	, @Delete_Date DATETIME
 
SET @Retention_Days = -90  -- 3 months
SET @Delete_Date = DATEADD(d, @Retention_Days, GETDATE())
 
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Delete_Date
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Delete_Date
 

Simple CSV file with some embedded delimiters. No big deal in SQL 2000 DTS right? Well, it's a huge deal in SQL Server 2005 and SQL Server 2005 SSIS. Of course, it is a bit of a mystery while they keep removing functionality but that isn't the point of this post. The point of this post is to show a solution...

The solution comes by way of the Microsoft SQL Server Community Samples: Integration Services (yes, it is a long name for 'Look dudes! We are giving you a work around!' but it works non the less).

http://sqlsrvintegrationsrv.codeplex.com/

I tried the delimited file reader in a variety of scenarios and it passed with flying colors. How nice it is to be able do the same imports that were effortless in SQL 2000.

Anyone who has to do reporting on a recurring basis knows what a pain it can be when you have to provide columns totals for the app. Treatment for this pain can be found with the ROLLUP command.
Run this example to see what I mean:

CREATE TABLE #temper (row_id INT IDENTITY(1,1)
   , Account_Name VARCHAR(50), balance smallmoney);
INSERT INTO #temper 
VALUES ('Jimmy Johns', 50),('Qdoba',60)
   ,('Big Truck Tacos', 70),('Hideaway', 80),('Teds', 90);
SELECT Account_Name, SUM(balance) AS BALANCE
FROM #temper
GROUP BY Account_Name WITH ROLLUP;
DROP TABLE #temper;

Pretty cool right?  The downside being that there is a NULL to deal with and that isn't a very descript name for the total.  So maybe we plaster on a CASE statement to replace the NULL with something a little digestible to a user:

CREATE TABLE #temper (row_id INT IDENTITY(1,1)
   , Account_Name VARCHAR(50), balance smallmoney);
INSERT INTO #temper 
VALUES ('Jimmy Johns', 50),('Qdoba',60)
   ,('Big Truck Tacos', 70),('Hideaway', 80),('Teds', 90);
SELECT CASE
   WHEN (Grouping(Account_Name)=1) THEN 'Total'
   ELSE Account_Name
   END AS Account_Name
   , SUM(balance) AS BALANCE
FROM #temper
GROUP BY Account_Name WITH ROLLUP;
DROP TABLE #temper;

For more a more wordy version of this, consult the 4 Guys:
http://www.4guysfromrolla.com/articles/073003-1.aspx

Compared to email options presented in SQL Server 2000,   Databasemail in Sql Server 2005 rocks.  Here are some implementation examples:

-- send a normal mail
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @@ServerName
,@recipients='name@domain.com;number@cingularme.com'
,@subject = 'Dev Transfer to 19'
,@body = 'Task Complete'
,@body_format = 'HTML'

-- send with an attachment
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @@SERVERNAME
,@recipients='name@domain.com'
,@subject = 'Dev Transfer to 19'
,@body = 'Task Complete'
,@body_format = 'HTML'
,@file_attachments = @SourcePath;

/*
--Odds and Ends
If sending to multiple peeps and one fails to send... queued up and resends to all.

I usually make a profile based on the machine name, so that for notifications, I can keep using @@servername and it be portable.

-- SMTP Authentication
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-07-02T08:08:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for name@domain.com). )

-- antivirus port 25 block
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-06-18T07:15:51). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )

--Select * from sysmail_allitems order by mailitem_id desc   -- look up mails
--Select * from sysmail_event_log WHERE mailitem_id in (95,94,93,97) -- look up the mails details
*/

/************************************************************************
*By default, your tempDB files are in the following locations:
*C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf
*C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.ldf
*
*The below commands will change the default tempDB locations for SQL Server
*and have it create the tempDB files there upon start up.
************************************************************************/
USE master
GO

/************************************************************************
*You will need to restart SQL Server for tempdb's file to actually move.
*
*More reading on the topic:
*http://www.databasejournal.com/features/mssql/article.php/3379901
************************************************************************/


First day of the month:

DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,0,GETDATE()))

First day of the week:

DATEADD(yyyy, DATEPART(yyyy, DATEADD(weekday,1-DATEPART(weekday, GETDATE()),GETDATE()))-1900, 0) + DATEADD(dy, DATEPART(dy, DATEADD(weekday,1-DATEPART(weekday, GETDATE()),GETDATE()))-1,0)

Last day of the month:

DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0))

Last Business Day of the Month:  (buckle up for this one)

CASE WHEN DATEPART(DW, DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0)))=7 

THEN DATEADD(DAY,-2, DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0)))
    WHEN DATEPART(DW, DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0)))=6 

THEN DATEADD(DAY,-1, DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0)))
    ELSE DATEADD(ms, -3, DATEADD (m,DATEDIFF(m,0,DATEADD(m,1,GETDATE())),0)) END AS Last_Business_day_of_month 

What can I do with this you ask? Well, You can put it in a procedure for SSRS reports to get a dynamic date on subscriptions with a hidden parameter.

ALTER PROCEDURE usp_CommonDates_Report
AS
BEGIN

, CASE WHEN DATEPART(DW, @LDOTM)=7 THEN DATEADD(DAY,-2, @LDOTM)
    WHEN DATEPART(DW, @LDOTM)=6 THEN DATEADD(DAY,-1, @LDOTM)
    ELSE @LDOTM END AS Last_Business_day_of_month
END

So you get your handy dandy SQL Server Report Server all set up for email and are looking good then you go to have it email to an outbound address.  All of a sudden you get the 'The e-mail address of one or more recipients is not valid.'   While there could be a couple reasons for this issue, the main two I run into is SMTP authentication and user aliases.  The fix to both of these involves updating the ReportServer.Config file usually found in the filesystem with a folder structure resembling - "c:\program files\microsoft SQL Server\MSRS10.MSSQLSERVER\"

You want to pop it open and then search for "SMTPServer" which will take you to something that looks like this:

This will let use the report servers service credentials to authenticate to the SMTP server. In addition, Report Server start using the actual email address instead of trying to turn everything into an alias depending on what you have populated for DefaultHostName.