Drop a SQL Server database from your .net code

Have you ever been in a situation where you need to provide uninstall funcitonality for database installation? Imagine you have just created your nice new database in Sql server and all the scripts have been created but at the last line something happens and you need to rollback. This also includes dropping the database and it isn't straight forward. Closing your connection(s) doesn't help as they don't close straight away. While looking for a solution I came accross one and decided to share it here.
This is the method I created:

private string GetKillProcessStatement(string database)
{
string result = string.Empty;
StringBuilder sql = new StringBuilder();

sql.AppendLine("DECLARE @SQL varchar(max)");
sql.AppendLine("SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'");
sql.AppendLine("FROM MASTER..SysProcesses");
sql.AppendLine("WHERE DBId = DB_ID('" + database + "') AND SPId <> @@SPId");
sql.AppendLine("SELECT @SQL");

SqlConnection dropDbSqlConn = new SqlConnection(_masterConnStr);
dropDbSqlConn.Open();

using (SqlCommand sqcomm = new SqlCommand())
{
sqcomm.Connection = dropDbSqlConn;

sqcomm.CommandType = CommandType.Text;
sqcomm.CommandText = sql.ToString();

sqcomm.Prepare();

object obj = sqcomm.ExecuteScalar();

if (obj!=null)
result = obj.ToString();
}
dropDbSqlConn.Close();
return result;
}
A few clarifications: _masterConnStr is the connection string to the master db on this server. If you need to create a db you will probably already have this one available. In this case it is a private member in the installer class. I explicitly need it to be inline sql as i don't want to install stored procs in other databases on the server. Database access rights to do that will be available because the user we require also needs to create dbs.
The inline sql when executed returns a list of kill statements like this one: 'Kill 11;Kill 23;Kill 45;' . The numbers will be the process ids in Sql Server. Once you get this you are just a simple ExecuteNonQuery of this string before you will be able to drop your database regardless of the number of connections on it. Ok I know there might be more arguments for connection initiated between your kill execute and your drop statement execute but there is cure for this as well. If this is a worry then execute this sql script before you issue the drop statement:
"ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
I wouldn't be bothered to do so as in my case this is a newly installed db and there is no chance someone will know about it.
Back to the point I believe this is a good solution to ensure that you can drop a sql server db and the statement won’t fail because of open connections.

C# Regex to split multiple SQL statements

You know if you want to use SQLCommand to execute a big list of SQL Statements you cannot have the reserved SQL word GO in the script. So ideally you would want to split these statements to exclude the "GO"s and (perhaps in a transaction) execute each line separately. I wanted one of those for long time and was getting decent results with "^GO" + the multiline option, like this:

Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

But with xml columns in tables this quickly becomes useless - Yes I script data to insert statements also. Then finally today I found one that seems to be a lot better or at least works with all my data. Here it is:

string[] lines = Regex.Split(sql, "\\s+GO\\s+|^GO\\s+", RegexOptions.Multiline);


And NO I do not know what it means - I only know that it works ;)

“Scaling agile is the last thing you want to do” — Martin Fowler

Here's some alchoholic perspective about Scrum. I quite enjoyed reading it.
Now, seriously it is an excellent REAL world example and I recommend reading this article before beginning any scrum scaling.

SCRUM: Lessons learned

I wrote this article several months ago but only managed to publish it now which actually helped me improve it. Enjoy ;)

First project with Scrum (Lessons learned)

I know the first thought that will come to almost everyone’s mind when you read the title will be – yeah we all know that, but is it that simple in practice? This article tells a story about how all those “waterfall” years’ affect people’s thinking about releases and what we’ve learned from experience.

When on a sunny day in June we have had our first ever planning meeting I didn’t really realise what kind of roller coaster I am getting on. This planning meeting happened to be in the middle of a huge project aiming to release five products in total, consisting of three majorly reworked and two brand new products. To make things more clear about the size of the work: our decent spec build server takes 2+ hours to build the full set.

As the person responsible for the core products in the company at that time I welcomed the idea about scrum and joined forces with Rich and Sri to persuade senior management that we should go with implementing Scrum in an attempt to save this release which by that time was already 2 months late. While we had the full senior management support once we started doing sprints a few things seemed to have changed immediately in the entire organization. First everyone without really understanding Scrum expected that this “new way of working” will somehow “magically” enable us to release the software in a few weeks time and second they reminded us that they need the whole lot of functionality by a certain date and this is business critical. This of course added some more pressure to the team which was the last thing we needed.

Lesson number 1 - “End of the world” mentality
There is one phrase that will always remind me about this specific release – “good that we found it now”. This of course refers to discovering a fault and the fact that we’ve discovered it before doing the release. To me it pictures a big problem – we are so much focused on getting things right by a specific deadline that we forget a few simple facts – one - whatever we’ve found does not come from the end user hence might really not be that important to fix; and two - this is not the last release we will do and as long as we release often customers should get what they ask for. So the lesson learned was plan regular releases that add small amount of functionality to reduce the pressure.

Lesson number 2 – All the legacy stuff

Yes we’ve introduced Scrum in the middle of a big chaotically executed waterfall. We’ve inherited tons of legacy code (not documented, not unit tested and some of it not even integrated) and this affected our productivity. We had to add additional effort to fully automate builds and maintain continuous integration but yet once again we ended up with a whole lot if integration issues at the end. We had many problems with transforming from traditional to agile testing and perhaps we in fact made very little progress during the course of the project.
So is there a way to predict these problems? I would say no. But this doesn’t help a lot when senior management is asking for dates. So what would you do? First of all always answer with a range and don’t afraid if there are many unknowns to use a wide range. If management doesn’t like it explain this is due to too many unknowns and then ask for more time to research. If they don’t understand at least you know that there isn’t a better way to solve the problem and if they don’t understand then ask them do they really understand and support Scrum.

Lesson number 3 – Integration, integration…

Back to the time when we were in the middle of the mess and tried to sort it out we failed to start integrating straight away. Of course there was so much to do with sprint organisation and backlog management and all those unfinished features and task but still it was a mistake. We found out about it 2 months later when we realised that all the integration bits between four projects are now broken. So like on a typical waterfall project we started integration in the last 3 sprints and of course we paid the price for it. Now the word “integrate” is pretty much part of our DONE definition and really that says it all about integration.

Lesson number 4 - How long does regression testing take?

When I asked this question for just one of our products the answer I received was – two months if done by one tester. We should have really packed our stuff and gone home if we were to do that. Waterfall testers will refuse to think about automation. They will try to persuade you that it is extremely difficult and very slow to do and in fact not really worth the effort as it cannot cover “a lot” of the testing plan. Lesson learned was- throw the plan in the bin immediately and ask the whole team to find a better way to test. And the team effort will do it.

Lesson number 5 – Do we really understand it?

I was really pleased about the number of trainings we have done about Scrum and the overall understanding seemed to be good however it turns out to be a battle you have to win over and over again. Is it because of the waterfall mentality which is difficult to change or because some people get it and some not? I am not ready to give final answer however we all need to understand that coaching is needed pretty much all the time. And making assumptions that “yes after this training they really seem to get it” is not helping. We learned that we need to continue coaching with the same patience and enthusiasm as on day one.

Actually thanks to a lot of those “good that we found it now” problems we spent 2 months stabilizing the whole set of products. Really the root cause to me was the size of this work, the fact that we’ve been asked to release so much in one go and the deadline wasn’t any flexible.

The good point is that we learned a lot about Scrum, ourselves, software development, and why waterfall is bad. One year later we know learning never ends but I believe we appreciate what we’ve been through and we value what we’ve got.

A game to help with estimation

A lot more useful than the BS by MS.
I've been looking for or even wanting to create a similar one (damn, I'm late again!) but anyway there it is a game developed to help with doing the XP planning game which I think is perfectly suitable for introducing team members to the SCRUM estimation and planning techniques as well.

Read about it here

A World of change [taken from the yahoo scrum group]

Below is a quote which I find quite interesting and so much reflecting the reality that it made me laugh ;)

Frank Herbert (from his book Heretics of Dune):

"Technology, in common with many other activities, tends toward
avoidance of risks by investors. Uncertainty is ruled out if possible.
Capital investment follows this rule, since people generally prefer
the predictable. Few recognize how destructive this can be, how it
imposes severe limits on variability and thus makes whole populations
fatally vulnerable to the shocking ways our universe can throw the
dice."

(Assessment of Ix, Bene Gesserit Archives)

UK Scrum Practitioners

I started a group on linked-in which I named Uk Scrum Practitioners.

If you are even remotely involved in Scrum you are more than welcome to join by clicking here.

A collection of Agile Videos

I've been keeping this in my links collection for a while and I tought it might be a good idea to share it.

I think there are a number of "must watch" videos for agile practitioners.

Enjoy ;)

Northgale or one dream less

I found this link today - http://www.sommarskog.se/dynsearch/Northgale.sql
ok I know I am a bit late most of you will probably know about it ;)
But it is simply great. It uses an existing Northwind database to generate the same tables inside Northgale but with a lot more data. For exmpla the order details table ends up with more than 2.3mln rows!
When I tried to select from order details all rows with quantity > 30 it took 13 seconds!
What can be better than that to allow you to experiment with your queries when you're looking to improve performance?!
I think the only requirement is a northwind database and it takes just about 5 minutes to setup.
I hope someone some day will discover this post and will find it useful ;)

The Three-Headed Product Owner

Note: Following is the ScrumAlliance version of my September post re-SCRUM Product Owner. The version below has been edited and published by the ScrumAlliance on 13th Nov here.

The Three-Headed Product Owner

My company had a fairly good transition to Scrum. The team adapted well to the change and we all felt very lucky with how well things were going. Even so, when my colleague and I got back from our Certified ScrumMaster course, we were amazed by how easy it was to spot some little problems that had previously gone unnoticed. It was immediately obvious to us that, though we had a product owner, he was “missing” in many fundamental ways.
We weren’t the only ones who noticed this. A week after we returned from training, the team mentioned the same problem at our retrospective meeting. Clearly, our product owner, who was also the product manager, was not doing what the team needed and expected him to do. We decided that we needed a commitment from someone to really take on the product owner role. But who?
The product manager was not very interested in “driving” the team. This was evident from his recent acceptance of a number of features that clearly were not complete because of debug information popping up everywhere on the screen. We had a senior manager who was very supportive about Scrum but he was too busy to commit more than 25 percent of his time. We weren’t in a position to hire someone to do the job either. It soon became obvious that I had to do something myself. I volunteered to be product owner for the team.
I am a technical guy, not a salesman or marketing guru. I could commit to being there for the team, but wasn’t sure I had the point of view the team needed in a product owner. Luckily, we’ve been able to think outside a strict interpretation of Scrum and take three important steps that have helped me be the kind of Product Owner the team needs.
Step 1. Direct Meetings
Thanks to my recent ScrumMaster training and some reading I had done on my own, when I took over as product owner my understanding about Scrum had reached the level where I could look after the already prioritized and estimated product backlog and organize the review and planning meetings.
In the beginning, I felt a bit unprepared, but I dove right in anyway. Right after I took the position, I created a proposed list of product backlog items for the next sprint and presented them to the team the same day. I started calculating the completed story points and told the team that I would sit with them during the sprint and be ready to answer questions and execute acceptance tests when asked to do so.
The team never mentioned it, but I sensed that they might have been a bit worried about my ability to provide user feedback. Yet, at the very next retrospective meeting the team reported that the problem with the missing product owner had been pretty much solved.
Step 2. Form a Customer Team
We’re a product company that has always been driven by the sales team—they historically had specified the features that need to be developed based on the sales they wanted to complete. I come from a technical background. How could I provide feedback for the team that would come from a sales point of view? How could I be sure which features were the most valuable to our business? I couldn’t. Not by myself. Yet I was the product owner.
To give the project the correct perspective, we formed a customer team that includes a product manager who is good at spotting market trends and knows the competition, the senior manager I mentioned previously (who makes most of the decisions regarding features priority), a member of the pre-sales team who receives a lot of customer feedback, and me. We meet together periodically to answer any outstanding questions, to turn feature requests into user stories and add them to the backlog, and to assign and prioritise based on benefit, penalty, risk, and cost. I am the sole point of contact for the development team. When a question arises, I consult with at least some of the other customer team members to arrive at an answer the represents the customer team’s point of view.
Step 3. Create User Proxies
The customer team improved things for the development team. However, other departments began to feel as if they had no mechanism to provide us with their feedback. We were inundated with feature requests from our support and services departments and had quite a few from sales and marketing as well. We needed a way to deal with those and still make sure the team always worked on the highest priority items.
About that time I read about user proxies. They seemed to be the solution to our problem. Now, the other departments maintain prioritised lists of feature requests. Twice a month, they give us an updated list, indicating any changed priorities and added or dropped features. The customer team reviews the lists and balances these needs against the other items in the backlog before setting the final priorities.
Are Three Heads Better than One?
Having one product owner who is supported by a customer team and user proxies works for us. But let’s look at whether it satisfies the key components of an effective product owner.
A basic knowledge of how software is developed and deployedCheck. Coming from technical background and knowing quite well all the technologies involved, I more than met this criterion.
An active stakeholder managementWe think we’re doing the best we can. Organizing the customer team meeting and collecting feedback from other departments might be the best you can do in a product company. Of course we would love to be able to receive feedback directly from the users, but right now it seems like we have some way to go before this could be achieved.
A thorough understanding of customer needs Yes, for the most part. Understanding the needs of over three hundred customers is always going to be a challenge. We get feedback from every single department that communicates with clients. Since each of these departments interacts with many different client levels, from decision makers to end users, we should be getting a good picture of client needs. We also have data available from customer satisfaction surveys and training feedback that we use in customer team discussions. Most, if not all, of the customer team members also sit in the review meeting and take part in deciding if a story is done.
After reading a number of books on Scrum and attending ScrumMaster training, I wanted to do Scrum as close to the books as possible. However, in our real world situation, it wasn’t feasible to find a single product owner who satisfied all of the necessary criteria. After looking at the situation we took a three-pronged approach: 1) A product owner who could take part in the planning and review meetings, update the product backlog, and help the team to get their answers 2) A diverse customer team and 3) User proxies to help the team make informed decisions when prioritising the product backlog. Will it work for other teams? Who knows? Inspect and adapt!
Reference: http://www.scrumalliance.org/articles/44-being-an-effective-product-owner