PeterKellner.net

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date.  That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years. 

Say you want to delete all but the last 30 days of activity.  If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error.  That’s the best case.  The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.

Many of you know of ORCSWeb either by reputation, or by way of Scott Forsyth, one of my ASP.NET MVP brothers.  In case you don’t, they are a managed hosting solutions company specializing in Microsoft technologies.  I’ve used their basic services for quite a while and have always been very happy.  It has always seemed that anytime I’ve called them (and it always seems like the middle of the night) one of their tech support staff is always available to help me, and go the extra mile if necessary.

The company I’m now working at is small and we don’t have a lot of resources to maintain hardware and do operating system type support.  We do have a high load requirement so we need a very robust supported solution.  Before this, I’d always been in the under $50 per month type plan with ORCSWeb, but I decided I needed more servers and a higher level of support.  I really did not know what level of support to expect when signing up for the managed servers but decided to go for it anyway.

All  can say is WOW!!!  I am over the top impressed.

We’ve been looking to find the best Cloud based host to put our soon to be virally growing web site up on.  Our requirements are it must run IIS7 and use Sql Server 2008 as it’s database. We have lots of wants (like Service Broker, Replication, etc.) but primarily we want the web tier to scale easily.  After doing research and testing, we got frustrated with our options at Amazon’s EC2.  We emailed their support (with our paid support contract) and were basically told we should go do our own Windows research to find out what works best.

At any rate, I emailed Steve Evan’s who is a frequent speaker at conferences and also an expert consultant on all things Windows and IT related and asked for some advice.  He told me that he had been asked that question often recently by others so he would do some research and get back to me.  He did one step better.  He wrote a blog post describing the ups and downs of the different cloud providers including Amazon’s EC2.  I respect Steve’s opinion a lot and rather than try and paraphrase it, I’ll just link to his post.

Here it is:  http://serktools.com/2009/04/20/load-balancing-iis-web-farm-on-amazon-ec2/

Good luck and feel free to post comments here if you’ve got opinions on Cloud Hosting of IIS and Sql Server 2008.

I’m sure many of you know Lynn Langit.  Her role at Microsoft has been a Developer Evangelist of many of the business software offerings from Microsoft including Sharepoint and SqlServer and it’s many offerings.  She’s always been a wealth of great information about those products and it’s great to see she’s got a book out about one of her favorite topics, Business Intelligence.

So far, I’ve read the first four chapters and have skimmed the rest.  I am so far very impressed with both how well it’s written and the depth of topics the book covers.  As I read more, I’ll come back and update this post.  I have no doubts I will get a lot out of this book, as I’m sure practically anyone else who buys it will also.

As a developer who has spent much of my life doing DBA type work, I really appreciate the simplicity and elegance of what Microsoft Visual Studio Team has put together with the Database Edition and can be seen for sale here as the Microsoft Visual Studio 2010 Premium with MSDN Download – Download Direct from Microsoft I’ll first try and explain in a nutshell what it is.  Then I’ll go through an example of creating a Visual Studio Database Project out of a site I’ve been working on to show the steps.  Finally, I’ll give a short summary reliving the experience and giving you some other pointers that may help you get through the experience.

What is VS2008 Database Edition?

Basically, what VS2008 does is to first process your existing schema into hundreds (maybe thousands) of little files.  That is, each file is one database thing. That thing might be a table definition, a stored procedure, a trigger, a constraint, a foreign key, etc.  The beauty of this is that now, each thing can be tracked separately.  Say for example, you want to update just one table and someone else on your team wants to update another table in the same schema.  Since the files are now separate files, it’s no problem.  When you grab the latest changes from source control, you will get your friends changes and he will get yours.  Very very clean!

Also, it has a very powerful comparison engine.  Say you update your data outside of this project.  VS2008 Database Edition will compare your definitions in all these little files with either a sql script you generate, or it will connect to an external database and compare to that.  Me personally, I like to use a tool called SqlManager to manage my data.  I can continue to do that, then when I’ve made my changes, I can simply connect VS2008 Database Edition to my updated development database and it will automatically figure out the changes.

So, you may ask, what version do I need to run this of Visual Studio?  The answer is in this link:  http://msdn.microsoft.com/en-us/vs2008/products/cc149003.aspx

Here We Go, Let’s do an Example

So, first, I will open my existing solution which has a web project in it, a data access project as well as other projects.  Then, I create the new Database solution by saying File/Open/New Project/Database Project.

Then, we follow the wizard…

Taking defaults…

Now, I choose my schema.

Press Start…

Once you’ve done that, you’ll now have your schema spread out across your project in little files.  Below is what the "file based" view of your schema looks like.

Notice that the file we are looking at also includes comments for both the table and the columns using the standard extended property documentation format as follows:

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'tracks all changes from each camp

title;link;guid;pubDate;category;description', @level0type = N'SCHEMA', 
@level0name = N'dbo', @level1type = N'TABLE', 
@level1name = N'CampFeed';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'if true, this is the main news feed from the site.  
there should only be one main news feed from any site.  this would 
not be feeds like twitter', @level0type = N'SCHEMA', 
@level0name = N'dbo', 
@level1type = N'TABLE', @level1name = N'CampFeed', 
@level2type = N'COLUMN', @level2name = N'PrimaryFeed';

Now, when you actually build the project as follows:

it will generate the full script combining all the files back into one again. Here is where it puts it:

So, that’s about it for now. I haven’t gone into any of the ways to compare and update your schema based on external changes, but we can leave that for another post.

To see a great video by Richard Hundhausen, President of Accentient, a Team Systems Consulting company, go to this link:  http://msdn.microsoft.com/en-us/vsts2008/cc659682.aspx

Also, if you are using Sql Server 2008, make sure you install visual studio 2008 sp1, then go to this link and install the download:  http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Conclusions

From what I can tell, this Visual Studio Database Edition will be a huge help in building database schema’s and database programs (sp’s, triggers,etc.) in a collaborative environment.   If you have the license to use this, I strongly recommend it.  It is definitely going to be part of my tool kit for now on.  Even if I’m the only author in the project.

http://www.linqpad.net/ – Use with Microsoft .Net for building Query type expressions with LINQ

I’m not a wizard at writing LINQ expressions so I often find myself in Google looking for something similar to what I want, then I put it into my application and run it. This has worked pretty well, but it takes a couple iterations to get it right. It occured to me that someone probably has written a LINQ interpreter so I just guessed the name, LINQPad, typed it into search, and I find the authors of the book C# 3.0 in a nutshell, Joseph Albahari and Ben Albahari, (which I like a lot) have written it.