Manage Change, Mitigate Risk: Part Two

Second Step: Create Testing Environment

( Please see my previous post here, if you'd like to follow along with the CTP3 bits from TechEd )

Now that we have our meta data isolated in our off-line “sandbox”, we’re now ready to start the next step towards mitigating the risk inherent in changing database schema. That next step is all about establishing a testing / staging database that we can work against. Again, in order to continue the “best practice” of isolation when developing your database schema, the testing environment should also be part of your “sandbox”. For me, that means that I will establish a new database on the SQL Server instance on my local machine. That way, I’m guaranteed that I’m not messing with someone else’s environment, and certainly not my production environment!

Visual Studio Team Edition for Database Professionals directly supports this type of development by associating a test server with each database project, and then providing a Build and Deploy mechanism to those databases. ( I won’t be going into detail about Build and Deploy yet, but will show enough to get our testing database established ).

So, the next step I’ll show you is how to associate a testing database with the database project we have just created. Right click on the database project itself and select “Properties”:

The project properties window will be displayed. Select the “Build” tab:

You’ll notice a number of properties that can be manipulated on this page, but the one I’d like to focus your attention is the “Target connection” property. This is where we can choose / create a new connection to our testing database server, where our new test database that this new project will be associated with. You do that by clicking the “…” button, as seen below:

In the connection dialog that is displayed, I simply pick a database server ( not filling in the database name part of the connection dialog ). The results are displayed in the “Target connection” read-only text field as the connection string, similar to what you see below:

Assuming you have left everything else as is, creating my testing database from the off-line meta data housed in my database project is as simple as a right click and click action away! Right click on the database project node again, then select “Deploy Selection”. ( Make sure all went well by viewing the Output toolwindow. Should look similar to mine below ):

Bang! You’re test database is created and ready for use! ( By the way, you can verify that the project meta data and the meta data of the database you just created is the same by right clicking on the database project node, and selecting “Compare Schema…”. More on our schema compare functionality later! )