PeterKellner.net

Problem

So, if you have been doing development with Visual Studio 2010, Entity Framework CodeFirst, SqlServer or SqlServerCE for any amount of time, you’ll quickly run into the problem that the database can not be reinitialized because it is open.  Basically, the scenario is this.

1)  Put in your Global.asax.cs file a line that always recreates the database (naturally because you are in a development mode and as you constantly change your model and seed data).  The line is something like this:  Database.SetInitializer(new DropCreateDatabaseAlways<SiteDB>());

2)  Run your application with something like Debug/Run (All is fine)

3)  Go into the database browser (either in Visual Studio or Enterprise Manager)  and look at some data.

4)  Run your application again and you will get this error: “[SqlException (0x80131904): Cannot drop database "NewYorkTimesDb" because it is currently in use.]”.  This is because your database browser has a connection to the database and until it is dropped, you can not drop the catalog.

Solutions

The easiest and guaranteed to work solution is just to restart your SqlServer database server (control panel, services).  This is what I use to do but finally got tired of that because it takes about 20 seconds and, well, I’m very impatient.

The other solution I found in the forums (can’t find the link right now) is to execute the following script from the master database catalog:

use master
ALTER DATABASE NewYorkTimesDb 
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE NewYorkTimesDb SET MULTI_USER

Basically, just switching from multi-user to single-user and back clears the connections.

HTH’s!

So, this is not a big tip, but worth at least 10 minutes to figure it out on your own.  Here is to saving you 10 minutes:

SELECT LEFT (userlastname, 1) as alpha,
       count(id)
FROM attendees
WHERE id IN (
              SELECT attendeesid
              FROM AttendeesCodeCampYear
              WHERE codecampyearid = 6
      )
GROUP BY  LEFT (userlastname, 1)O
ORDER BY LEFT (userlastname, 1)

It’s pretty self explanatory.  Our case is we have two tables that we track code camp attendees.  One is the master list, and the other is a detail by year.  (6 is this year)

I have heard all the hype about how great Sql Server CE 4.0 and that it is now standard with Visual Studio 2010 SP1.

I’ve got a small project (1 table) that I’d like to include in an asp.net website project so I decided to give it a try.  For the most part, things have gone smoothly, however I did have a couple hiccups I’d like to mention.  One cost me $9 because I was doing this implementation on an airplane and immediately, I had a failed connection with a confusing error that I needed Bing to lookup  (more details below).

I often forget that the simplest way (IMHO) to connect to a Microsoft SqlServer 2008 database is to use the web.config connection for making a Trusted connection.  Basically, it keeps you from having to put a username and password in the web.config and also from having to keep track of different username and passwords on different systems (like where you deploy to for example).  The simplest connection string I can think of looks like this:

<add name="MyConnName" connectionString="Server=.;Database=mydbcatalogname;Trusted_Connection=True;" 
    providerName="System.Data.SqlClient" />

Notice that I’m using the “.” as the server name.  This allows me to reference the local system where my sqlserver lives. Not necessarily a best practice, but often is the case.

Upside

One big benefit is if you fall victim to a disk scraping attack, or someone gets a hold of your source (maybe from version control?), you don’t give up your passwords.

Downside

If you SqlSever is not on the system system as your web project or application, then this become more tricky because of the cross system authentication issues.  If you have active directory installed on both systems, then this works also.

Introduction

If you’ve started using SqlAzure for your SqlServer with your Azure application, you’ve probably discovered that you get a reasonable number of connection failures.  The advice from the Azure team is add retry logic to all your connections to SqlAzure. There is a long discussion posted by the Azure team here.

The key paragraph states the problem as follows:

The Problem
One of the things that SQL Azure does to deliver high availability is it sometimes closes connections. SQL Azure does some pretty cool stuff under the covers to minimize the impact, but this is a key difference in SQL Azure development vs. SQL Server development.

Basically, what this means is that you must be able to deal with connections failing when you call SqlAzure.  Something that all of probably should have been doing forever, but because most of the time SqlServer is running on your local LAN and the likelihood if a connection failing was next to zero unless something else was going terribly wrong.  Certainly not something we had to do on regular basis.  To emphasize that even more, most of the controls built into asp.net that open connections to sqlserver don’t even do this and that’s from Microsoft itself.

The solution proposed in the thread mentioned above basically has you add tons of code to everyplace you access a connection object.  Personally, I don’t like that because I have hundreds if not thousands of places I open connections and inserting tens of thousands of lines of extra new untested code is a little scary.

So, what to do?

Fortunately, another team at Microsoft, known as the Windows Server AppFabric Customer Advisory Team published a general purpose solution using Extension Methods and some darn clever coding wrote a great article and published code including azure examples that solves this problem very elegantly without requiring a lot of changes to your existing code base.

In this article I plan on giving an example and publishing a sample project that uses this code with SqlAzure to solve the connection retry problem.  My goal here is not to simply restate what they published but to simply have a very simple concrete example of using their library.

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.