다음을 통해 공유

Demo Transcript: A First Look at SQL Server 2012 (Part 3 - SQL Server Data Tools)

  • 아티클

SQL11UPD00-TSCRIPT-05

This wiki is a transcript of a previously recorded video.

Related content assets:

Optimized Productivity with SQL Server Data Tools

View Previous Demo Transcript: A First Look At SQL Server 2012 (Part 2 - Data Quality Services) (SQL11UPD00-TSCRIPT-04)

Connected Development Experience

Hi and welcome back.  This is Roger Doherty, and this is part three of a four part demo exploring some of the exciting new capabilities in SQL Server 2012.  In this demo we are going to focus on SQL Server Data Tools, which is a great new development environment for building, testing, debugging and versioning your database solutions for both your on-premise environment and for the cloud.

So we left off in our previous demo having scrubbed up our address data using the Melissa Data service that was available to us from the Windows Azure Data Market.  So let's bail out of the application and take a look at SQL Server Data Tools.  I'll go ahead and close this project here and we'll start from scratch. So the first thing that you will notice here is that we have SQL Server Data Tools installed in an integrated fashion with Visual Studio 2010. You get this rich new SQL Server Object Explorer, which is a capability that is going to make it possible for developers to stay in Visual Studio for virtually everything they need to do from a development perspective with SQL Server.  We want to minimize the need for you to flip back and forth between multiple tools.  So what you see here is that we are connected up to our primary replica here that has the database for our product catalog.  Let's take a look at some of the connected development capabilities here in SQL Server Data Tools. 

I would use this if I want to walk right up to an instance that I have full privileges on and I want to do some development on it right now without having to go off and develop on a project or a set of source code.  We want to make that type of development very straight forward and very easy in SQL Server Data Tools.  So here you can see we've got our ProductsDb database, and in there we've got all of our tables that are available. I can go in and view the data right here from within SQL Server Data Tools.  I can browse and edit so a nice capability there.  The other thing that I can do is I can pop this table open in the brand new table designer and actually develop right on top of it.  If I open up View Designer here what you will see is the new table design UI.  In this area of the screen we have a nice graphical representation of each of our columns, their null status, their default status.  Over here on the right are all the table specific attributes.  Things like keys and constraints and indexes.  So I can use these parts of the tool here to develop and modify my database schema graphically.  Or, I can develop down here in this code window and we'll just automatically keep things in sync.  We'll even let you switch your view to your preferred way of doing development. 

So let's do something fairly straight forward here, let's say that this FinishedGoodsFlag column is no longer used in our solution and we want to get rid of it.  So I'm going to just right click on this column and select delete.  We see that the column is deleted.  Now this is a fairly naive thing.  Anybody who builds database solutions will know that there are all kinds of interrelated dependencies between objects in SQL Server.  But SQL Server Data Tools allows you to develop declaratively over a model of the database and understands all of those dependencies and allows you to refactor over top of it in very powerful ways.  So the first thing that we notice is if I go down and look at my error list, is that the deletion of that column has introduced an error.  The model has detected that that particular column was referenced by a view, this ProductView table here.  So if I double click that it actually opens up the definition of the view and it highlights the column that is giving me a problem.  I can just go in and delete this column.  We'll go ahead and blow away the column now and when I do that my error list clears up.  So you can see that SQL Server Data Tools allows us to get immediate feedback because we are developing over this model as to whether or not the changes that we are making are actually going to work. 

Now what will we actually do with these changes? All I really have to do is just push them right back to the database server.  I can do that right here just by clicking Update.  The next thing that you will notice is that we get a summary of the changes that are going to happen here.  We're going to go delete that column and it's warning us that there is going to be some possible data loss as a result of that operation. And, we've got to go and redefine this view that had the dependent column in it as well.  So I can generate a script here if I wanted to or I can just push the changes back to the database.  I'm going to go ahead and do that right here.  You can see SQL Server Data Tools is pushing those changes back.  It's updated those tables successfully, and if I go back to my development environment here and we open up the products table again, you will see in the columns that my live table no longer has that FinishedGoodsFlag column, and if I go to my views and we look at ProductView, the FinishedGoodsFlag column is not in there either.  So this is an example of how quickly you can just walk up to a SQL Server instance and do development right against it if that's what you want do do.

Platform Targeting

So now that we've taken a look at the connected development experience that SQL Server Data Tools provides, let's also take a look at the disconnected development experience which is going to make it easy for you to develop, debug, test and maintain your database solutions right within Visual Studio without even having to have SQL Server installed.  So most developers are going to want to check the source for their project into source control at some point and even add that database project to an overall solution.  So SQL Server Data Tools makes it very easy to reverse engineer a project to get started creating that source code that represents your database solution.  So since I kind of inherited this database, I'm going to use that capability.  I'm just going to right click on ProductsDb here, and I'm going to say Create New Project.  We will go ahead and call this project ProductsDb, same name as the database. We will go with the default settings here, and we will begin the import.  Very quickly SQL Server Data Tools goes and inspects that database and reverse engineers a project based upon it.  You can see here inside my project I've got little code artifacts for every individual object in my database, and I can start checking these things into source control, I can add this project to another solution and build it as a unit, I'm well on my way to moving to a nice managed source code environment here. 

So one if the things that you can do that is very powerful with a project is you can use SQL Server Data Tools to help re-target or re-platform your application.  So let's see in this case we've decided that we want to re-platform this entire web application in the cloud on Windows Azure.  When you move the database portion of an application like that there are some slight behavioral differences from one flavor of SQL Server to another.  So what we are going to do is we are going to go in here and use a feature in SQL Server Data Tools called platform targeting.  Right now our solution targets SQL Server 2012, that's where this database project was reverse engineered from.   I'm going to change it to SQL Azure just to see if this database would even work in the cloud.  Now in the past we wouldn't know this until we actually tried to migrate the database but here in SQL Server Data Tools we can try this out in a disconnected fashion before we try to run scripts that might not work against the target environment.  So let's go ahead and do a build of this solution and see if it works.  You can see here that the build failed.  If I go here into my error list we get a warning here.  It says the reseller table doesn't have a clustered index.  In SQL Azure every table in the database needs to have a clustered index, that's the way we maintain replicas for high availability.  It's an automatic baked in feature in SQL Azure.  How do we fix this?  So what I'll do is pull open my Reseller table in the designer and we're going to change this index to a clustered index from a non-clustered index.  So I'll pull up my properties window here and we can see that we've got an IsClustered Property exposed, so we'll just change that to TRUE.  That will make it possible for us to target this database to SQL Azure.  So I'll go ahead and save this thing back.

Maybe we're not ready to replatform this app just yet, but we want to maintain an environment that works both on SQL Server 2012 and on SQL Azure.  So what I"ll do is push this change from my project and deploy it back to my production database that's running on-premise.  So in order to do that I just right-click on this project and say Publish.  It's going to allow me to target this to my production database instance here on the primary replica that we have which is this guy here, G7-03.  There's the database it's going to target.  We'll test the connection and its working, so now we'll say Publish and its gone and pushed those changes from my database project back to my production on-premise installation. 

Replatforming on Windows Azure

In order to re-platform this solution on Windows Azure we would need to move each tier of this solution up to Windows Azure, including the web tier, the reporting tier, and the data tier.  We've done a couple of those in advance, but I did want to show you how to move the data tier.  There are a bunch of different ways of doing this.  One of the easiest ways is to go right into Management Studio, connect to your SQL Azure logical server, and you can see we have no databases there.  All I have to do is right-click on the database that I want to  re-platform, and select Deploy Database to SQL Azure.  What it will do next is kick off a process to actually package up this database into what we call a ".bacpac", and push it up for deployment onto SQL Azure.  We'll go ahead and connect to that same SQL Azure database server and we'll start the process of building that bacpac and publishing it on SQL Azure. 

While that is running let's go take a look at the other tiers that we deployed.  So here I am in the Windows Azure Management Portal, and what we will look at first is the web tier.  We've gone ahead and deployed that ASP.NET solution up to Windows Azure in a Web Role.  So that's the Web Tier.  We've also gone in advance and provisioned a Report Server using SQL Azure Reporting, and as you can see here we've already deployed our reports here to a report server.  So the web tier and reporting tier were here in advance of the demo, and the data tier we are pushing up there right now into SQL Azure. 

So you can see the wizard is running pretty quickly.  We've created a bacpac, we've pushed all of that into a single file, we've deployed it on SQL Azure, and we've actually loaded all of the tables with the data that was included in the bacpac.  So this is a really quick and dirty and easy way of moving databases from your on-premise environment to the cloud, and if we go back over here and do a refresh in Management Studio you can see now we have our ProductsDb database.  

So let's go poke around and see if our app is functional.  So if I go back up here to my Web Role you can see that Windows Azure has a URL for us to connect up to our web application running in the cloud.  So if I click on that link, hopefully our web application will be functional and working just fine.  This is the first time we've run it, and here it is.  Here is our ASP.NET solution, we can go in here and a Kinect Bike with size 32.  We can put a picture there in of our Kinect Bike and all the CRUD operations are working the same way they did on-premise but now the entire app is running up in the cloud.  Pretty cool.  So I can do all of my maintenance operations here, I can delete, the functionality of the app is identical.  

We can even go in here and render the reports, but this time the reports are not being rendered from a report server running on-premise.  They are being rendered from a report server that was provisioned for us up in the cloud in SQL Azure Reporting.  So this is the first time we've run this report so it will take a second to run it.  But here is that same report and this time you can see the database server that it's running against is my SQL Azure database.  

So in a very simple process we've used SQL Server Data Tools to reverse engineer a project, make some changes so that we can replatform our app up on SQL Azure, and then we've very quickly published the entire data tier of our app up to SQL Azure using some nice tooling in SQL Server Management Studio.  Now all three tiers of our app are functional up in the cloud including our web tier, our reporting tier, and our data tier.   

View Next Demo Transcript: A First Look at SQL Server 2012 (Part 4 - SQL Azure Data Sync) (SQL11UPD00-TSCRIPT-06)

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]