Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Here is a problem that torments developer teams everywhere. If you've ever worked with database changes and multiple developers, you know that keeping your database schema in sync is a never-ending task. If someone makes a change to the database schema, you must ensure that that change propagates to each developer's computer, makes it to the test team, and gets updated on all your production servers. The trouble is compounded when debugging previous versions of your product. Now you have to ensure that the database matches the code that is stored in the source control.
Some teams attempt to solve this problem by having a folder full of SQL scripts that contain the information to update the schema. These scripts can quickly get out of sync with the real database schema, however. Believe me, I know this from first-hand experience. To solve these problems, Microsoft created a special database project known as SQL project. Before I continue, I should add that these features are only available in Visual Studio Premium edition or Ultimate edition. If you do not have either of these editions, you will not be able to follow along with my demonstrations.
I'm inside Visual Studio, and I've opened a solution called SqlProjects. This has a project called UseExisting that I'm going to show you in about two minutes. But first, I want to show you how to create a brand-new SQL project. I'm going to right-click on this solution and choose Add > New Project. The projects I'm looking for are down here near the bottom of the Installed Templates section, in the Database node, underneath SQL Server. As you can see, there are several types of projects here.
The one that I want to use today is called the SQL Server 2008 Wizard. I'm going to come down here and have it generate a new BookSeller database, also in V1, like that, and then click OK. Now the wizard will launch and I get the Welcome screen, which I'm going to skip. The next type is what kind of project do I want to create? I'll create a user-defined database project, and I'm going to choose to store the script files by type of object.
Then I'm going to click on Next. These are the standard settings for the SQL Server database, so I'm going to leave them as is. Then I'll click on Next. At this point, I'm creating a brand-new databases, so I would be responsible for creating all of the scripts for creating the tabled, the start procedures, the triggers, and everything. I bet that you already have some databases in your organization. If that's the case, you could click on Import existing schema, go over here and choose your database, make connections to the server, tell it which database you want, and then when you clicked on Next, Visual Studio will go out and import all the information from your existing database.
Now since we don't have a full-blown version of SQL Server installed in this machine, I am not going to demonstrate this. Later, I'll show you a schema I did get from an existing database. At this point though, I'm going to uncheck this Import existing schema and then click on Next, and then verify that everything looks the way I want and click Finish and click Finish one more time. At this point, I have created a Visual Studio project that contains all the information about creating a brand-new database.
This can be stored in source control. Think about this for a second. This becomes very beneficial. Imagine this scenario: Each developer in your team works with his own local server instance of that database. When one of your developers makes the schema change--let's say he adds a new column to the table--he'll do this not in the real database, but inside this project. He'll go over here to Schema Objects > Tables. We're going to add a table here called Books.
So the change would be made here. You would write the SQL script here to modify the name of the column, or change the name of the column. Then I can check this in the source control. All the other developers can check out this item from source control and then they can run the Update Database feature that's inside this project. That will update their local copy of the database with the changes, and every developer can do that. Also, your SQL Server administrators can do the same thing on your production and your test servers.
Rather than walk through creating a bunch of tables and start procedures, I'm going to show you the second project that I have here, called UseExisting. I ran this against the NorthWnd database. So if I come down here and show you the table section here, you'd see that it contains a number of table schemas. I'll double-click on this Shippers.table.sql. And this shows how to create the table inside your local copy the database. There are plenty of other interesting tools.
There is a Data menu up here that has something called Schema Compare. What I can do with this is check two different databases. I can look at this project's schema, and this UseExisting project's schema, and when I compare the two of them, what's happening is it's looking at the differences between the tables and all the other entities in this database and showing me what's missing. For instance, if I look down here you'll see that I have a Books table in the BookSeller, but I don't have it in the using existing. So then I can say, create that table and then the next time when we run this script against the database, it's going to add that table to the UseExisting database.
There is also another tool called Data Compare. What this does is you point it at two separate databases, and it looks at all the actual data that's in the database and make sure that they match up. And there is also a Create Test Data feature, where I can tell it to generate fictitious data that I can use on our testing applications. Well, now that we've got this project created, it's time to see how to create design time data and modify database objects. That's the topic for another movie in this chapter.
Get unlimited access to all courses for just $25/month.Become a member