Rusty Divine

Live, Love, Learn, Teach

How to create a Visual Studio 2013 Database Project in 10 Minutes!

Watch my Pluralsight Author Audition video to learn how to create a Visual Studio 2013 Database Project from an existing SQL Server database and populate it with data from that database.

After you’ve created your database project, you can publish it to your development environment for a consistent development database that reduces the risk of data integrity bugs sneaking in.

Improve Team Collaboration with Visual Studio 2013 Database Projects

 

Approximate transcript:

Have you ever been part of a team who has had problems coordinating changes to the database during development or while publishing your work to production? Wouldn't it be nice to have some reliable and consistent test data that you can use while developing and debugging a new feature?

Hi, I'm Rusty Divine and in this course on improving team collaboration with Visual Studio 2013 database projects I am going to show you how to create a consistent data set for each member of your team and overcome database conflicts by creating a database project that can be published on your local machine at the click of the button. After watching this course you will be ready to create a new Visual Studio Database Project with test data so that you can develop your new features in isolation of any model or data changes by other team members.

************

On many software development teams more than one developer makes changes to the database. A team might use a shared development database on a server and have worked out a system to handle breaking database changes and buggy data that creep in. Some teams can script each change they make to the database model and seed data, then update or rebuild the database to any version they need to by running each script in order.

************

An option that has worked well for teams that I have been part of is to use a database project to manage the model and test-data changes. Database projects work well when the development of the solution will take months to years to build and the team of three or more will each be helping make changes to the database. The database project is a good option because it:

  • Gives developers a local database sandbox
  • Provides consistent data that can be wiped and reloaded when data integrity bugs creep in
  • Makes merging into your version control system easier than having to merge scripts that are dependent on the order they are ran
  • Allows reuse on automatic builds to publish a consistent database that automated tests can be written against

***********

Some potential drawbacks to database projects are:

  • Setting it up the first time takes time
  • On a new project where you are working to define the database and it is changing rapidly, it can feel like this takes you out of the develop-test flow
  • When moving to QA and Production you need to do a model comparison to generate change scripts and create any data motion scripts you need to change data in each environment

Let's take a look at what it will take to get a database project added to your solution.

***********

We'll be using this Northwind database to represent a backup of your development database where you've taken the time to get just the data you need into it - the less data the better because it will take less time to publish that way.

I've created a database project using the default settings from rt-clicking on this database and selecting create new project. You can see it has brought in all of the tables, views, and other schema, but it did not script the data.

Here I've added a scripts folder and a post-deployment folder to organize the data load scripts. Now, there are several ways to get the data scripts generated, but the way I find works best for a set of data that isn't reaching into the hundreds of thousands of rows is to use SQL Management Studio's script generation; for larger data sets I would recommend SSMS tools (pop-up a link).

In management studio, I will right-click on the database and choose to generate scripts. I want to select just the tables and then choose to generate a separate file for each. In the advanced settings, I choose to not generate the use database commands and change the export from schema to data. Now I'll export it to the post-deployment folder we created in our project.

I've shown all the files here in the project and want to include our new scripts. Now I need to set the build action to none for these so that they aren't included in the publish because I want to control the order of them.

Next, I will add a post-deployment script that will execute each of these data loads in the correct order that does not conflict with their foreign key relationships.

The format for running these is to use this colon-r followed by the script location to run the script. The script uses command syntax, so I will turn on sql command mode.

Now, I'll paste in the rest of the tables. When I try to build, I see an error with the order details table. Looking at it, I see that I need to add some quotation marks around it, and now it builds fine.

Let's publish this database to see our data. Rt-click on the project and choose publish. In the advanced mode, I want to choose recreate the database every time. There are ways you can merge this data into an existing database, but I want to make sure I wipe out any data related bugs that may have crept in and start clean each time. I'll save the profile for later use, and then click on publish.

There was a problem publishing the database, so I'll click this link to view the results. Here, I can see the script it generated and I could copy/paste this into SQL management studio to troubleshoot, but looking at the messages, I see there was a problem with the self-referential key on the employee table. I know that's going to be a problem to load, so I'll just drop the key before I load the table, then re-add it after.

Now, let's publish from the profile we saved. You can see it published successfully, and if I go into management studio and refresh the databases, there is our new database and you can see it did bring over our data.

*************

Summary

In this course we covered the benefits of having a consistent development data set for your team so that they develop and test their work in isolation.

We covered how a database project can

  • Help your medium to large team coordinate model and test-data changes
  • Provide an isolated sandbox to develop new features
  • Wipe out any data-related bugs that creep in during development

There are a lot of extra features that you can explore with database projects, and I'd encourage you to watch these other Pluralsight courses to learn more.

Some times we’ve failed with Agile

Tomorrow there’s a lunch gathering for our local Agile community and the topic is failure led by Keil Wilson, an agile project manager who works with me at NDOR.

At this Luncheon, we'll focus on the value of failing fast and forward.  Bring a simple--or the most heinous--failure you've encountered in your Agile Transformation.  We'll then focus on how it was discovered and the good that came out of it.  We'll also rely on the collective wisdom of our Community to offer other suggestions.

We often talk about failure being an option on our software development team. For example, we did not burn down last iteration when two of our user stories didn’t get completed in time. At our retrospective we brought this up to talk about it. One of our customers was there and she didn’t care that we didn’t burn down, in fact, just the opposite – she thought we were too focused on it when it wasn’t a problem. We talked about value and commitment but in the end agreed that if we fail to burn down occasionally it is actually a sign that we are pushing ourselves appropriately. If we always burned down it would seem a little suspicious that maybe we were slacking off some, and if we never burned down it would be a loss-of-trust issue where the expectation would begin to be set that the burn down is irrelevant. In our case, we burn down 80% of the time or more I’d estimate, and our team is comfortable with that amount of failure.

For the luncheon tomorrow though I’ve thought of a few examples of when I’ve been part of  projects and teams that have failed with Agile in the past:

  1. One customer brought us a requirements document they had created and put out to bid. We won that project and actually did a great job delivering on the project using Agile to guide us. The failures we had were poor definitions of conditions of acceptance from that document they wrote, missing assumptions and constraints, and a fixed budget that didn’t allow room for pivoting when they needed to left the customer with a solution they weren’t satisfied with in the end. Later, they paid another company to rewrite it, and that company is still working on it today, but at least they are charging time & materials.
  2. Another customer was a startup and we pre-billed them each month with a definition of what we were going to complete that month – we worried their funds might dry up. We were able to get done what they needed, but in the later stages of the project their project sponsor (the money-person) realized how much the project had cost, and their product owner (his brother) got convenient amnesia and pushed the blame on us. Our failure there was not making sure the product sponsor was involved more in the project as it was being completed and it ruined a relationship with that customer that could have been prevented with better project management.
  3. Some of my early experiences with Scrum and Agile took a light-approach. I now realize we could have done much better by occasionally reflecting on how we could be doing better instead of making the same mistakes over and over.
  4. On the project I currently work we still have some things we could be doing better. For instance, our customer does not get very involved with prioritizing the backlog, and when they do it seems like they aren’t thinking as strategically as would be good for the project in the long run. The result is we end up focusing on enhancements to existing features – moving fields around, swapping columns, changing shading, etc. – instead of taking on the new initiatives that need to be completed during this phase of the project. The silver lining is that our project manager specifically set a budget in this phase’s project charter for enhancements, and as of this iteration they only have a few points left to spend before they need to make an official request for more. Even though we put this safeguard in place, it didn’t prevent them from spending their entire enhancement budget first, including reversing some of the work they asked for, before working on anything new.

I’m looking forward to tomorrow’s meeting to see what the rest of the group has to say about their experiences. One things for sure, the restaurant we are meeting at has never failed to sever up some great burgers!

Business Analysis / Requirement Gathering Exercise

goal settingRecently the software development team that I lead decided on a new team goal to work on together – improve our business analysis and requirement gathering skills.

The Project Manager of our team, Brad, had an idea that he could be the customer for a software project that he actually wants built – something that will add a set of user stories and tasks to our Team Foundation System (TFS) for him. This takes him a few hours each iteration, but more importantly he just dreads doing it because it should be easy to automate.

Here is the text email I sent to the team earlier this week:

We have a fun exercise to work on requirements analysis that we will be starting this Friday during our tech training session. Brad has an idea for a real piece of software he wants to use (it has something to do with TFS tasks) and he wants us to help figure out what this thing is and what it should do.

Agenda:

We will break up into two groups so that we can spread the learning out. This is not a competition, but the two groups should work independently so that everyone has the opportunity to struggle a little.

Blue Team: Suma (Team leader), Ryan, Padma, Rusty

Green Team: Adam (Team leader), Dan, Satish, David

The team leaders will decide how to organize the team and should take the lead responsibility in getting the requirements right. Rusty and David will take off their tech lead hats and play the role of team member suffering from minor and temporary business analysis amnesia (basically we’ll try not to steer so that others can exercise their skills).

  • 10-10:30ish – Brad will present the overview of his idea to both teams.
  • 10:30-11 – Brad will “leave” the room (pretend he isn’t there), both the teams will independently organize and decide what questions they have and how they want to proceed
  • 11-12 – Brad will actually leave the room. The teams will take turns of no longer than 15 minutes to ask Brad follow-up questions and do whatever work they see fit in defining the project.

At noon we’ll assess where we’re at. It’s quite likely that we’ll need to spend most or all of next weeks’ time (and even more?) doing analysis.

When a team feels like they are ready to go, they can present their plan to Brad and he can decide whether or not he wants to sign off on it. If not, revise the plan and re-submit.

Each team will then actually build their version of the tool during tech training time in the following weeks. There’s no better validation of your requirements gathering than having to build what you defined! We’re not sure how much work will be involved at this point, so we’ll adjust as necessary.

In the coming weeks we’ll also spend some of the time during each tech training working on quick business analysis exercises or demonstrations. I may also send out some 15-minute drills occasionally that you can do during the week.

By the time we wrap this all up in a few months, we’ll do a retro of how this initial requirements went considering the things we’ve learned along the way. We may do a final requirements gathering exercise like this first one using the skills we’ve learned, but likely won’t build that project.

</eom>

Blackboard time quality moneyThe first meeting was pretty fun. I had a good time sitting back and letting everyone else on blue team take the lead in eliciting the requirements from Brad.

Next week the team will get an email every morning with a practice exercise that shouldn’t take more than 15-20 minutes; I’ll post them here, too. They will cover things like, “What is the Project Management Triad?” to give each of them a chance to think about requirements daily.

Powderhook.com is great for Beginning Hunters and Expert Hunters

pwdrhkIf you know how to hunt and need to find some people or places to go, or if you don’t know how to hunt and need to find some people to show you the ropes, then Powderhook.com is a great resource for you.

I’m a good shot and have been around guns, but have only hunted a few times in my life. This year I would like to learn how to hunt whitetail deer and I have a lot of questions about how to prepare myself, find the deer, and what to do with the deer after its harvested.

I am going to be learning by following the hunting learning path at OutdoorU.org, watching some videos, and checking out the library. I also would feel better getting out with someone with experience that can answer questions I will certainly still have my first few times out.

At Powderhook I created a discussion room (called a Card) for Lincoln Nebraska area hunters that is meant to connect beginners and experts in this area so that the experts can mentor us beginners and we can all have a good time and make new friends.

Not long after I posted Eric Dinger, the CEO and co-founder of Powderhook, dropped in to answer some of my questions! Hopefully it will be of some help to you, too, and I hope to see you there on Powderhook if you are interested in hunting:

My beginner-hunter questions:

  1. Where to go (Powderhook should be able to help there!)
  2. How close should you be to confidently make the kill (depending on your own skill of course); is 80% sure good enough for example?
  3. What happens if the animal is just wounded; how to finish it as cleanly as possible?
  4. What do you do after its dead - field clean, how to get it out to your vehicle, where can you take it (esp. deer)?
  5. and of course tips and tricks for tracking and finding game and general etiquette so that I don't feel like a newbie.

 

Eric’s answers:

  1. We can help. And, you've got a bunch of time to get ready for next year. Let's talk again this summer about finding a spot.
  2. I think many people would answer this question differently. The idea is if you're going to shoot at a deer you should be really confident you're going to kill it. Not sure if that's 80% or 95%, but it's probably a highly situational deal no matter your confidence level. For example, with some practice, a good scope, rest and a standing deer you could be accurate to several hundred yards. I've never killed one over 200 yards. Boone and Crockett would tell you that anything over 300 yards is not considered fair chase. I fall in the camp of 'if you're confident you'll make an ethical shot, then shoot.' Much of the fun is about getting close - so if I were just getting started I'd force myself to get really accurate up to 150 yards and hunt until I had a chance at an animal in that range. Doers choice, though, no judgment here!
  3. If you wound a deer you want to give it ample time to expire. Most deer you hit solidly with a rifle will die within a few hours. The last thing you want to do is chase them when they're wounded - they'll run for miles and you'll never find them. If you're confident you hit the deer a good rule of thumb is to give it at least an hour before you move. That hour will feel like 4 hours... which is why I'm saying an hour and not 30 minutes! If you pick up a really good blood trail you're probably safe to continue to track the deer. If there is scattered blood and you're not sure you made a lethal shot, I would back out and give the deer a few more hours. If you come up to a deer that hasn't expired but doesn't run, the ethical thing to do is make a lethal shot or use your knife do the same. Use the barrel of your gun to touch the deer's eye to make sure it's dead.
  4. Field dressing a deer is not nearly as difficult as you'd think. That said it's definitely intimidating at first. My suggestion? Find a friend who will show you in person. Or, there are lots of "how-to" videos online. The long and short of it is you have to get what's inside out - ideally without cutting into the intestines and getting deer musk all over your new jacket. Ha! My Step Dad showed me once and I've been good to go ever since. If you end up shooting a deer on your own, hit me up and either I will come help you or I'll find you someone who will - wherever that might be. As for where to take it, Schuster's is what I hear everyone talk about [Other spots in NE]. I always took mine to C and C in Diller, Ne, but I think they're done doing it as of this year. You can also do it yourself, but I don't because I don't have a good place to do it. Most importantly - just make plans to go. The rest will come together for you. There are hundreds of people like me who would love nothing more than to help you get started.

 

I have a lot of time to prepare before next season and am looking forward to finding some great places and new friends over at Powderhook.