WEBVTT

00:00:07.990 --> 00:00:10.670
Hi, welcome to Visual Studio toolbox.
I'm your hose Robert Green

00:00:10.770 --> 00:00:14.890
and joining me today is Scott Klein.
Scott Klein is the host

00:00:14.940 --> 00:00:18.590
of the award winning show on Channel
9, data exposed 

00:00:18.640 --> 00:00:21.020
>> That's right. Which award was
it that you won?

00:00:23.340 --> 00:00:25.550
>> I'm trying to remember which
award I won. The best...

00:00:25.600 --> 00:00:26.470
>> Best new show.

00:00:26.520 --> 00:00:27.770
>> Best new show, that's right.

00:00:27.820 --> 00:00:28.610
>> So far this year.

00:00:28.660 --> 00:00:29.340
>> That's right.

00:00:29.390 --> 00:00:29.690
>> Excellent.

00:00:29.740 --> 00:00:30.590
>> It's all about the data.

00:00:30.640 --> 00:00:32.560
>> If you haven't watched that
show I highly recommend it.

00:00:32.610 --> 00:00:35.010
>> Yes. It's actually a really good
show because we haven't had

00:00:35.060 --> 00:00:38.780
one about data for a while so decided
to start one and it's actually

00:00:38.830 --> 00:00:42.740
getting quite... not getting
the 300,000 you're getting.

00:00:43.000 --> 00:00:45.420
>> Well, that's something to
as prior to, I guess.

00:00:45.470 --> 00:00:47.880
>> That's right. I want to
be Robert Green someday.

00:00:47.930 --> 00:00:48.730
>> Moving on.

00:00:49.480 --> 00:00:56.400
So you're here to talk about the SQL
Server data tools in Visual Studio.

00:00:56.450 --> 00:00:56.920
>> That's correct.

00:00:56.970 --> 00:01:01.410
>> And I think we find a lot of times
with tooling in Visual Studio

00:01:01.460 --> 00:01:05.440
that people are not necessarily
as aware of some of the tools

00:01:05.490 --> 00:01:10.400
in Visual Studio because they have
been doing something since

00:01:10.450 --> 00:01:13.430
before the tools were invented.
A classic example is XMAL.

00:01:13.480 --> 00:01:17.430
People did not type their XMAL
in the editor because when we

00:01:17.480 --> 00:01:21.840
all learned XMAL there really weren't
designers of the caliber

00:01:21.890 --> 00:01:22.550
there are now.

00:01:23.300 --> 00:01:26.780
When I need to do something in SQL
Server I'm more likely than

00:01:26.830 --> 00:01:30.100
not to fire up SQL Server management
studio and then when I pop

00:01:30.150 --> 00:01:33.710
back into Visual Studio and I see that
there's a SQL Server Explorer

00:01:34.220 --> 00:01:38.650
I think to myself, hmm, I wonder what
I can do with that as opposed

00:01:38.700 --> 00:01:41.680
to having to always have to go
into SQL management studio.

00:01:42.890 --> 00:01:46.350
So Scott, what can I do
inside Visual Studio?

00:01:46.920 --> 00:01:47.540
>> This is...

00:01:49.070 --> 00:01:54.120
you know as a DBA, right, I think
Microsoft is all about empowering

00:01:54.170 --> 00:01:54.920
the end user.

00:01:55.520 --> 00:01:58.000
You talk to any DBA and any time
you say okay, I'm going to give

00:01:58.050 --> 00:02:01.380
a developer the tools to modify
my database, right,

00:02:02.920 --> 00:02:04.710
you just started Armageddon, right?

00:02:05.270 --> 00:02:09.010
But I think there's some power to
that, right, especially with

00:02:09.060 --> 00:02:11.350
the abilities from a SQL Server
perspective to say okay, who

00:02:11.400 --> 00:02:14.530
can do what, right? So I can get
very granular to the permissions

00:02:14.580 --> 00:02:18.250
and what a user can do, but I think
there's... you can get quite

00:02:18.300 --> 00:02:20.980
dangerous within Visual Studio but
now within like you say with

00:02:21.030 --> 00:02:24.320
the SQL Server data tools there's a
lot I can do and we can probably

00:02:24.370 --> 00:02:27.260
spend hours talking about everything
SQL Server data tools can

00:02:27.310 --> 00:02:30.410
do, right? There's just a couple
of things I want to point out

00:02:30.460 --> 00:02:32.270
because I was talking with the...

00:02:32.960 --> 00:02:35.770
you know, the SQL Server data tool
seem, and they said, you know,

00:02:35.820 --> 00:02:37.950
there's a lot of things that people
really don't... like you said...

00:02:38.000 --> 00:02:40.650
really don't know about that SQL
Server data tools can do.

00:02:40.700 --> 00:02:43.760
Where can they go get it? How can
I edit? Things like that.

00:02:43.810 --> 00:02:46.970
Does it actually work with Azure?
Yeah. Yes, yes, yes, type

00:02:47.020 --> 00:02:50.150
of thing, right? So a couple of
things I just want to point out

00:02:50.200 --> 00:02:52.640
and we can always come back and
do more deeper dive if we need

00:02:52.690 --> 00:02:54.910
to, but just a few things I want
to point out, and the first

00:02:54.960 --> 00:02:57.290
thing is we need to understand that...

00:02:57.880 --> 00:03:00.890
depending on the version of Visual
Studio you have if you're

00:03:00.940 --> 00:03:04.710
using Visual Studio 2010 this is
an add-on, right? Just go up

00:03:04.760 --> 00:03:08.980
to MSDN, search for SQL Server data tools,
it's an add-on. Perfect, right?

00:03:09.030 --> 00:03:12.700
If you're using Visual Studio 2013
a lot of people don't know

00:03:12.750 --> 00:03:16.420
that it actually comes with Visual
Studio 2013. It is installed

00:03:16.470 --> 00:03:20.270
with Visual Studio, right? There it
is, SQL Server object explorer.

00:03:20.320 --> 00:03:24.490
Done, right? Allows me to connect
to a myriad of things, right,

00:03:24.540 --> 00:03:28.020
especially SQL Server and Azure
SQL database so I can do a lot

00:03:28.070 --> 00:03:31.900
of work with that, but it comes
with Visual Studio 2013.

00:03:32.290 --> 00:03:36.340
Another thing that a lot of people
don't know is that how functional

00:03:36.390 --> 00:03:38.300
SQL Server data tools
really is, right?

00:03:38.350 --> 00:03:38.960
>> Right.

00:03:39.010 --> 00:03:41.570
>> Like you said I'm a SQL Server
guy. If I'm going to do SQL

00:03:41.620 --> 00:03:44.570
Server where am I going to go first?
I am going to crack open

00:03:45.150 --> 00:03:46.470
SQL Server management studio.

00:03:46.520 --> 00:03:49.210
>> Which I often then need to
download, first of all.

00:03:49.260 --> 00:03:54.230
>> Exactly. From a developer perspective
a lot of us don't have

00:03:54.280 --> 00:03:57.300
SQL Server management studio. As
a SQL guy that's the first thing

00:03:57.350 --> 00:04:01.750
I am going to install is SQL Server
or the management tools for

00:04:01.800 --> 00:04:05.520
SQL Server. But as a developer, as
a Visual Studio guy, you know,

00:04:05.570 --> 00:04:08.180
a lot of times a lot of people come
to me and go, I need to install

00:04:08.230 --> 00:04:11.800
SQL Server management studio. Well,
do you have Visual Studio?

00:04:12.100 --> 00:04:15.300
I sure do. Okay. Let's work with
that, right? So there we go

00:04:15.350 --> 00:04:20.480
and so I can do things like, you know
connect to... so let me connect.

00:04:20.530 --> 00:04:21.730
>> I can create a new database?

00:04:21.780 --> 00:04:25.010
>> I can create a new database, I
can modify existing databases

00:04:25.060 --> 00:04:29.800
so I can say databases, add new
database. Here I go, right?

00:04:29.850 --> 00:04:36.910
So we can call this toolbox, right?
Done. If we want to go over

00:04:36.960 --> 00:04:38.450
SQL Server management studio now

00:04:40.070 --> 00:04:43.690
and refresh our list of databases
our database should be there.

00:04:43.740 --> 00:04:44.640
There's toolbox.

00:04:45.330 --> 00:04:47.710
So I can do databases. Now I can
actually go if I want to and

00:04:47.760 --> 00:04:51.180
say, all right, I want to expand
that. I have no tables but

00:04:51.230 --> 00:04:56.380
I can say add new table, right?
So this is very flexible and

00:04:56.430 --> 00:05:00.750
functional as to actually SQL Server
management studio can do.

00:05:00.800 --> 00:05:02.100
I can do everything here.

00:05:02.150 --> 00:05:04.590
>> You would think that it would not
able... because SQL management

00:05:04.640 --> 00:05:08.210
studio is nothing more than a UI
that knows the API to talk to

00:05:08.260 --> 00:05:08.720
SQL Server.

00:05:08.770 --> 00:05:12.890
>> Just call it SMO behind the scenes.
That's all this is doing.

00:05:12.940 --> 00:05:14.710
>> Presumably this should be doing
the exact same thing.

00:05:14.760 --> 00:05:16.030
>> Exact same thing.

00:05:16.080 --> 00:05:18.840
>> Minus whatever functionality
doesn't make it over there.

00:05:18.890 --> 00:05:22.510
I imagine that SQL management studio
does literally everything

00:05:22.560 --> 00:05:26.570
you can do in Visual Studio may do
a big subset of that. Is that

00:05:26.620 --> 00:05:27.600
a fair statement?

00:05:27.650 --> 00:05:30.270
>> I think that's a fair statement,
right. And like for example

00:05:30.320 --> 00:05:33.020
if I go into SQL Server management
studio and I want to...

00:05:33.070 --> 00:05:35.250
like let's go to AdventureWorks 2012,

00:05:37.090 --> 00:05:39.760
2014, and I want to modify a table,
for example, I'm going to

00:05:39.810 --> 00:05:44.590
say design, right? Then I go down
here and I add a column.

00:05:44.640 --> 00:05:49.010
I'm going to call this test col
int, right, and all I have to

00:05:49.060 --> 00:05:52.320
do here is I just have to click save,
right, and this will actually

00:05:52.370 --> 00:05:56.050
save, if I click save, right, well...

00:05:56.620 --> 00:06:04.870
now if I go and let's look at the he
select top thousand rows, right.

00:06:04.920 --> 00:06:05.670
>> Uh-huh.

00:06:06.200 --> 00:06:07.870
>> There's test col over there, right?

00:06:08.510 --> 00:06:11.960
Visual Studio is a little differently.
If I click save it's

00:06:12.010 --> 00:06:16.460
just going to save the T-SQL so
what SQL Server data tools does

00:06:16.510 --> 00:06:18.220
is if I want to go to
the same thing here

00:06:19.590 --> 00:06:22.380
so let me go to AdventureWorks 2012.
This is what a lot of people

00:06:22.430 --> 00:06:25.470
don't know is that I can do a lot
of things in Visual Studio

00:06:25.520 --> 00:06:28.420
that I can do in SQL Server management
studio just a little differently.

00:06:28.470 --> 00:06:32.290
So I expand the tables. There's
person.person. I can go

00:06:34.130 --> 00:06:37.570
view designer, right?
There's my designer.

00:06:38.500 --> 00:06:42.870
There's test col, right? There's actually
the T-SQL script, right,

00:06:44.130 --> 00:06:45.770
that it's going to generate.

00:06:46.340 --> 00:06:49.070
>> That kind of gives you
documentation table.

00:06:49.120 --> 00:06:52.100
>> You have got documentation and instead
of, you know... a little different.

00:06:52.150 --> 00:06:56.360
Yeah. If I want to do that in SQL
Server management studio and

00:06:56.410 --> 00:06:59.940
I wanted to see the T-SQL, you
know, what it does I'd have to

00:06:59.990 --> 00:07:02.870
actually go right here and say,
okay, what does the...

00:07:02.920 --> 00:07:03.950
where did

00:07:05.280 --> 00:07:07.040
I go?

00:07:07.090 --> 00:07:07.440
So...

00:07:10.690 --> 00:07:12.010
>> Script table as.

00:07:12.060 --> 00:07:15.770
>> Yeah, script table as. Create
two or alter two or something

00:07:15.820 --> 00:07:18.850
like that. I was looking for tasks,
sorry. Right. There it is.

00:07:18.900 --> 00:07:22.740
So I think there are some pros and
cons to both, right? For me

00:07:22.790 --> 00:07:25.830
you know I'm used to working with
this so if I want I can either

00:07:25.880 --> 00:07:29.580
go to designer or as a T-SQL guy
I'm going to... I'll not even

00:07:29.630 --> 00:07:31.640
going to open up the designer. I'm
going to create it, just say

00:07:31.690 --> 00:07:34.490
alter table add column or
things like that, right?

00:07:34.540 --> 00:07:35.410
>> Right.

00:07:35.460 --> 00:07:39.970
>> Most Visual Studio people like
how do I write T-SQL so that

00:07:40.020 --> 00:07:43.190
I think... this is why from a DBA
perspective I don't want to

00:07:43.240 --> 00:07:46.430
give these guys the tools, right?
But here in Visual Studio I

00:07:46.480 --> 00:07:49.900
can just say, all right, well, I'm
going to add test call two,

00:07:50.970 --> 00:07:54.860
make that an INT, right, and here
you can see that it automatically

00:07:54.910 --> 00:07:59.360
modified my T-SQL automatically as
I type. This is awesome because

00:07:59.920 --> 00:08:05.570
it's basically teaching and to some degree
teaching the developer T-SQL.

00:08:05.620 --> 00:08:08.790
Now, this isn't how you would do
it, right, because if I was

00:08:08.840 --> 00:08:12.660
an actual T-SQL guy I would say alter
table, add column or things

00:08:12.710 --> 00:08:13.220
like that, right?

00:08:13.270 --> 00:08:13.770
>> Right.

00:08:14.360 --> 00:08:17.380
>> Here if I were to actually click
save it would try to save

00:08:17.430 --> 00:08:20.340
this T-SQL script. It actually
wouldn't run it for me.

00:08:20.990 --> 00:08:21.510
Right?

00:08:21.560 --> 00:08:24.090
>> So it saves the alter.

00:08:24.140 --> 00:08:27.610
>> So if I click save it basically
saves this SQL script.

00:08:27.660 --> 00:08:31.630
Right? Save person.person.SQL, right?
It doesn't really run it

00:08:31.680 --> 00:08:35.830
for me. What I have to do is I
have to go and click update.

00:08:35.880 --> 00:08:39.050
What update does is it says, hey,
here's... it actually runs

00:08:39.100 --> 00:08:42.010
this T-SQL script and you can kind
of see down here what it's doing.

00:08:42.670 --> 00:08:45.130
It's really displaying update preview
so it's kind of running

00:08:45.180 --> 00:08:47.980
and I have to say here,
go update the database.

00:08:48.860 --> 00:08:51.880
Essentially what it does is it will
run that script and it says

00:08:51.930 --> 00:08:52.900
okay, we're good.

00:08:53.560 --> 00:08:56.540
>> So that's to cover the scenario
where you as the developer

00:08:56.590 --> 00:09:00.010
don't have the ability to actually
make the changes but you do

00:09:00.060 --> 00:09:02.360
have the ability to request
certain changes?

00:09:02.410 --> 00:09:04.820
>> Maybe request certain changes,
right. So I can actually go

00:09:04.870 --> 00:09:09.780
back to this table now, person.person.
Let's just refresh that.

00:09:09.830 --> 00:09:10.940
Look at my columns.

00:09:12.840 --> 00:09:15.950
>> Then the update will succeed
if you have permissions.

00:09:16.000 --> 00:09:19.990
>> If I have permissions, right. Because
I'm connecting through...

00:09:20.040 --> 00:09:25.370
so this is where I think is a DBA,
you know, DBA's can control

00:09:25.420 --> 00:09:28.370
what our developers can do so DBA's
can say okay, yes, you can make.

00:09:28.420 --> 00:09:32.170
Maybe in a test environment we will
give you free reign, right,

00:09:32.220 --> 00:09:34.760
to do this, but in a production
environment just generate the

00:09:34.810 --> 00:09:38.570
script and send it to the DBA and
the DBA will run it, right?

00:09:38.620 --> 00:09:41.780
But at least it gives the ability
for the developer to work in

00:09:41.830 --> 00:09:46.110
an environment that he's familiar
with while he's learning SQL

00:09:46.160 --> 00:09:48.880
Server a little bit and then makes
the changes he wants, but

00:09:48.930 --> 00:09:52.490
a lot of people don't know that
I can do a lot of the things

00:09:52.540 --> 00:09:54.910
in Visual Studio that I can do in
SQL Server management studio,

00:09:54.960 --> 00:09:56.190
a little different
like you said.

00:09:56.240 --> 00:09:58.000
>> Can you run queries?

00:09:58.250 --> 00:09:58.730
>> Absolutely.

00:09:58.780 --> 00:09:59.630
>> Use data.

00:09:59.680 --> 00:10:03.370
>> Yeah. So I can actually if I want
to run T-SQL I can, you know,

00:10:03.420 --> 00:10:04.520
say something like...

00:10:05.070 --> 00:10:09.410
I run queries so if I
wanted to, let's say,

00:10:10.610 --> 00:10:16.500
new query, right? There's my query
window and I can say select

00:10:16.550 --> 00:10:22.630
star from person and it's actually IntelliSense
which is awesome, right. Person.person.

00:10:24.190 --> 00:10:27.990
Okay. Just control E. Did it work
here? Nope. So we have to

00:10:28.040 --> 00:10:34.340
click execute. This... I have
to run execute. Control E.

00:10:34.390 --> 00:10:35.190
>> Control shift E.

00:10:35.240 --> 00:10:39.600
>> Control shift E. So
there's my data.

00:10:40.360 --> 00:10:43.430
I can edit here, things
like that.

00:10:43.480 --> 00:10:46.510
>> You can save scripts and open
them and the whole shebang.

00:10:46.560 --> 00:10:50.120
>> This is a very close to SQL Server
management studio, right,

00:10:50.170 --> 00:10:52.500
like you said earlier. I think
there's some nits and gnats.

00:10:52.550 --> 00:10:57.610
I think it's... you have to fit
into the boundaries of Visual

00:10:57.660 --> 00:10:58.750
Studio, it's a little
different, right.

00:10:58.800 --> 00:11:01.210
>> Can you see the diagrams
in Visual Studio?

00:11:01.810 --> 00:11:03.620
>> You know, that's a good question.
I really don't know. I mean,

00:11:03.670 --> 00:11:07.650
if we could go... let's go
in here and say hey...

00:11:08.890 --> 00:11:13.740
probably not. No. I haven't tried
that. That's a good question.

00:11:13.790 --> 00:11:17.610
I will have to try that. I would
not think so. Let's see.

00:11:20.160 --> 00:11:22.770
No, I don't think so. That
would be really nice.

00:11:23.330 --> 00:11:25.710
Maybe if there was... I don't have
any diagrams in my databases

00:11:25.760 --> 00:11:28.420
but maybe if I did it would pop
up here and say view diagram.

00:11:28.800 --> 00:11:32.480
I would think not just because I think that's
a completely different designer.

00:11:32.840 --> 00:11:33.300
>> Okay.

00:11:33.350 --> 00:11:36.570
>> The other thing that I really
like about this is so again I

00:11:36.620 --> 00:11:40.950
can work very well within Visual Studio
to do my database modification, great.

00:11:41.000 --> 00:11:43.670
I can run queries and modify views,
modify store procedure.

00:11:43.720 --> 00:11:46.230
A lot of things that SQL Server
management studio can do.

00:11:46.280 --> 00:11:49.470
Another thing that I really like
about this is... and I think

00:11:49.520 --> 00:11:51.950
one of the things that maybe we
do on another show is really

00:11:52.000 --> 00:11:55.030
talk about the publishing aspect
because, you know if I'm going

00:11:55.080 --> 00:11:59.030
to make a change I can deploy back
into and make changes that

00:11:59.080 --> 00:12:01.790
way so there's different ways but
what I really like is the ability

00:12:01.840 --> 00:12:03.990
is that this will actually
allow me to connect to...

00:12:05.010 --> 00:12:09.580
let me try this... so at SQL Server and
I'm going to try to connect to...

00:12:09.630 --> 00:12:14.990
let me do this. Oh, so instead of
talking on premise let's move

00:12:15.040 --> 00:12:16.270
to Azure a little bit
if that's okay.

00:12:16.320 --> 00:12:16.820
>> Sure.

00:12:17.420 --> 00:12:19.830
>> This actually works very well
with Azure. I connect to an

00:12:19.880 --> 00:12:23.960
Azure SQL database very easily and
one of the great things that

00:12:24.010 --> 00:12:27.130
people don't know about is that
when I go to SQL databases and

00:12:27.180 --> 00:12:31.110
they go how do I go get SQL Server data
tools it's actually pretty cool.

00:12:31.160 --> 00:12:32.770
If I go in here and I pick a database,

00:12:34.320 --> 00:12:38.440
install Microsoft SQL Server data
tools. Right there in the window.

00:12:38.490 --> 00:12:41.590
A lot of people don't know that's
there. That is awesome because

00:12:41.640 --> 00:12:44.430
if there is any question and they're
running maybe 2010, Visual

00:12:44.480 --> 00:12:48.980
Studio 2010, I can go get SQL
data tools. Bam. Done, right?

00:12:49.690 --> 00:12:51.950
So that's a great way to do that.
So what I'm going to do is

00:12:52.000 --> 00:12:54.180
here is I'm actually going to go
back and I'm going to click

00:12:54.230 --> 00:12:58.600
a server and I'm going to connect
to my west US server so I'm

00:12:58.650 --> 00:13:06.070
going to go grab this name here.
Right? Then I want to go back

00:13:06.120 --> 00:13:11.570
to SQL Server... to Visual Studio
and connect to that and hopefully

00:13:11.620 --> 00:13:13.260
my password hasn't changed.

00:13:16.450 --> 00:13:22.810
Right? I have to go add an IP address
so let's go do that real fast.

00:13:23.340 --> 00:13:26.180
Should have prepped this maybe a little
earlier. Let's go configure

00:13:26.230 --> 00:13:26.570
that one.

00:13:26.620 --> 00:13:31.140
>> That's something you run into
very quickly with working with

00:13:31.770 --> 00:13:32.490
SQL Azure that

00:13:34.230 --> 00:13:39.030
it has a range of IP addresses and
when you go to a new one like

00:13:39.080 --> 00:13:40.250
you're in a different office.

00:13:40.300 --> 00:13:40.700
>> Different office.

00:13:40.750 --> 00:13:45.000
>> You're at home. You're in the Channel
9 studio. Your IP address changes.

00:13:46.400 --> 00:13:47.880
>> Let's see, 159.172.

00:13:50.260 --> 00:13:51.930
Remember that, 159.172.

00:13:53.200 --> 00:13:56.580
>> So a lot of times what I just do
is connect manage, click manage

00:13:56.630 --> 00:13:57.790
and that will add it.

00:13:59.130 --> 00:14:01.070
>> You know what, that's
a good point. Got one.

00:14:03.380 --> 00:14:11.240
159... that's probably the easier
thing to do but we will just

00:14:11.290 --> 00:14:12.010
do this for now.

00:14:13.070 --> 00:14:14.880
But it's actually pretty easy.
Go back and click connect and

00:14:14.930 --> 00:14:16.960
I should be good. There we go.
So right now I'm connected...

00:14:17.010 --> 00:14:20.330
>> In the past there have been...
there used to be a longer delay.

00:14:21.080 --> 00:14:25.050
>> It used to... the message used to say
it might take up to five minutes.

00:14:26.050 --> 00:14:28.140
>> Recently it takes almost
no time at all.

00:14:28.190 --> 00:14:31.160
>> Yeah. Exactly. I have never had
it take more... I click save,

00:14:31.210 --> 00:14:37.450
go back to my app, whatever it
is. There is no delay. So this

00:14:39.480 --> 00:14:42.430
allows me to connect to all my databases
in the cloud on my server.

00:14:42.480 --> 00:14:44.660
This is really nice. Actually the
same thing. I can actually

00:14:44.710 --> 00:14:46.160
work with there.

00:14:46.710 --> 00:14:49.200
I think one of the other things
though too from a SQL Server

00:14:49.250 --> 00:14:52.260
data tools perspective is that
I really want to point out now

00:14:52.310 --> 00:14:53.780
that we have connected sort of to...

00:14:55.050 --> 00:14:59.060
talked about the on prem side there's
really the power SQL Server

00:14:59.110 --> 00:15:02.940
data tool has is in working with
the included or Azure SQL Server

00:15:02.990 --> 00:15:03.900
database side.

00:15:04.530 --> 00:15:08.460
And that's just as easy. One of
the things people talk about

00:15:08.510 --> 00:15:14.510
is how do I get my database from
on prem to Azure SQL database

00:15:14.560 --> 00:15:17.490
or something like that. This is
really since I can connect to

00:15:17.540 --> 00:15:21.090
the cloud and connect on prem it
makes the migration, makes the

00:15:21.140 --> 00:15:25.040
movement from on premises to Azure
SQL database much easier.

00:15:25.750 --> 00:15:29.800
A lot of people don't know but
there's the ability to say how

00:15:29.850 --> 00:15:32.640
do I clean up or how do I prepare
my database for move to the

00:15:32.690 --> 00:15:38.710
cloud, right, and SQL data tools
is wonderful for that, wonderful

00:15:38.760 --> 00:15:41.780
for that. So what I'm going to
do is I'm going to go and I'm

00:15:41.830 --> 00:15:43.050
going to go create...

00:15:43.950 --> 00:15:46.400
get rid of this real quick and
come back to that... but I'm

00:15:46.450 --> 00:15:48.050
going to say file, new project.

00:15:48.640 --> 00:15:51.840
I'm going to go create just a very simple...
an empty SQL Server project.

00:15:51.890 --> 00:15:54.840
We will call it database one, for
example, right. I'm not going

00:15:54.890 --> 00:15:57.900
to put anything in it. I'm just going
to create an empty database project.

00:15:57.950 --> 00:16:01.290
There it is. Nothing's in it, right?
Now, I want to say how do

00:16:01.340 --> 00:16:03.970
I get my database ready for migration
because I have this database

00:16:04.020 --> 00:16:07.570
and I want to take it and move it
to Azure SQL database so I'm

00:16:07.620 --> 00:16:09.760
going to go back to SQL Server
data tools and I'm going to go

00:16:09.810 --> 00:16:13.170
back to my on prem environment and
I'm going to right mouse click

00:16:13.220 --> 00:16:15.060
my database and I'm going
to say create new...

00:16:15.750 --> 00:16:20.390
actually, know what I'm going to
do is I'm going to do a Schema

00:16:20.440 --> 00:16:21.880
compare, right?

00:16:21.930 --> 00:16:22.780
>> Okay.

00:16:22.830 --> 00:16:26.090
>> No one knows... a lot of people
don't know that this is in there.

00:16:26.870 --> 00:16:29.050
I'm going to compare,
do a Schema compare.

00:16:29.100 --> 00:16:29.820
>> Of?

00:16:29.870 --> 00:16:34.940
>> So I'm going to compare this
database, AdventureWorks 2014

00:16:34.990 --> 00:16:37.950
to my empty database project. All
right. The power behind this

00:16:38.000 --> 00:16:40.580
is a lot of people don't know that
there's a Schema compare and

00:16:40.630 --> 00:16:43.440
a data compare in SQL
Server data tools.

00:16:43.490 --> 00:16:44.040
>> Yeah?

00:16:44.090 --> 00:16:46.560
>> Yeah. Right?

00:16:46.610 --> 00:16:47.680
>> What does it do?

00:16:47.730 --> 00:16:51.760
>> Let me show you. So here we go. You're
waiting with baited breath.

00:16:51.810 --> 00:16:52.280
All right. So...

00:16:52.330 --> 00:16:54.630
>> I'm waiting with baited breath
for you to show me.

00:16:54.680 --> 00:16:58.540
>> Yeah. Patience is a virtue. All right.
But I will just point out...

00:16:58.590 --> 00:16:59.630
I'm going to

00:17:01.450 --> 00:17:04.220
click really is slow here just
for you, right? So I am going

00:17:04.270 --> 00:17:06.580
to do a Schema compare. What I'm
going to compare here is I'm

00:17:06.630 --> 00:17:09.760
going to compare this database to...

00:17:11.450 --> 00:17:15.830
select target. I'm going...
to my empty project. Okay?

00:17:16.370 --> 00:17:20.380
Done. Now if I wanted to and there's
a whole... I'm not going

00:17:20.430 --> 00:17:24.970
to go through this... but one of
the things that we want to do

00:17:25.020 --> 00:17:27.750
to make this migration very easy
is I can go in to the options

00:17:27.800 --> 00:17:31.300
here and I want to start deselecting
some of the things that

00:17:31.350 --> 00:17:34.030
are not supported in Azure SQL database
so I know that aggregates

00:17:34.080 --> 00:17:37.870
isn't and I know that asymmetric
keys and assemblies and things

00:17:37.920 --> 00:17:41.570
like that. I know certificates aren't
and I know that the full

00:17:41.620 --> 00:17:43.550
text search things and there's a
whole list and there's a blog

00:17:43.600 --> 00:17:45.060
post out there and actually
I have a video

00:17:46.280 --> 00:17:50.230
out there that shows how to migrate
this, right. But I'm just

00:17:50.280 --> 00:17:52.210
going to click cancel, all right.
At this point I'm going to

00:17:52.260 --> 00:17:55.690
just do a compare, right, and I'm
just going to go compare what's

00:17:55.740 --> 00:17:58.620
in my Azure... what's in my database
to what's in this empty

00:17:58.670 --> 00:18:02.100
project and this will take a couple
seconds but it just goes

00:18:02.150 --> 00:18:05.690
and says hey, what's there, what's
available, and it basically

00:18:05.740 --> 00:18:10.190
builds out, there it is, it's all done.
It says there's my comparison.

00:18:10.800 --> 00:18:14.580
My project is still empty but now
I want to update that so I'm

00:18:14.630 --> 00:18:17.610
just going to say update. Yep,
I want to update the target.

00:18:17.660 --> 00:18:24.120
This doesn't take that long either
but what it's going to do

00:18:24.170 --> 00:18:27.860
now is start building and creating
basically the T-SQL scripts

00:18:27.910 --> 00:18:31.610
for all the objects in my database.
There it goes. It's all done.

00:18:33.190 --> 00:18:35.840
So there's all my objects. Now I
have a database project to work

00:18:35.890 --> 00:18:39.780
with so as a developer again in
Visual Studio I can either go

00:18:39.830 --> 00:18:44.630
in here and say hey, all right,
I can make changes here but as

00:18:44.680 --> 00:18:48.450
a developer, Robert, since I have
a project in Visual Studio

00:18:49.020 --> 00:18:50.310
what does allow me to do?

00:18:52.170 --> 00:18:55.090
Pop quiz. So I'm not

00:18:57.320 --> 00:18:59.090
making changes on
the fly, right?

00:18:59.140 --> 00:18:59.460
>> Yeah.

00:18:59.510 --> 00:19:03.720
>> I'm working with an actual project
so I can actually do source

00:19:03.770 --> 00:19:08.160
control, versioning, right, so if
I want to make a change I just

00:19:08.210 --> 00:19:12.100
go into the project, TFS or something
like that and make a change

00:19:12.150 --> 00:19:16.230
so now I can actually do publish
from here. I'm not making warn

00:19:16.280 --> 00:19:18.260
off changes. I am going
to right mouse click.

00:19:18.310 --> 00:19:22.080
>> So that project represents the
database. Can we see that in

00:19:22.130 --> 00:19:22.990
a little bit more detail?

00:19:23.040 --> 00:19:25.220
>> So I'm done with this. I'm
going to close this. Right?

00:19:25.270 --> 00:19:28.580
Let's open this now. There's all my...
so there's all the triggers

00:19:28.630 --> 00:19:30.860
and what it did is it broke it
down by Schema. If you look at

00:19:30.910 --> 00:19:31.850
the database.

00:19:31.900 --> 00:19:32.250
>> So...

00:19:33.090 --> 00:19:34.940
>> So I have person,
production, right?

00:19:34.990 --> 00:19:35.370
>> Yeah.

00:19:35.420 --> 00:19:38.630
>> So now I this is broken down by Schema
so there's human resources,

00:19:38.680 --> 00:19:40.030
there's all the tables.

00:19:40.080 --> 00:19:43.270
>> So it contains the SQL statements
to create each of these things?

00:19:43.320 --> 00:19:46.730
>> Empty project of these things.
If I double click on it there's

00:19:46.780 --> 00:19:51.870
just like we saw earlier, right?
So when I click publish so now

00:19:51.920 --> 00:19:52.440
what I do...

00:19:53.190 --> 00:19:54.690
so I can actually go
back in here now.

00:19:54.740 --> 00:19:59.220
>> 17 scripts to add the data
as well or just the Schema?

00:19:59.270 --> 00:20:00.510
>> This is just Schema
right now.

00:20:00.560 --> 00:20:00.900
>> Okay.

00:20:00.950 --> 00:20:04.200
>> This is just Schema. SQL Server
data tools does not do or from

00:20:04.250 --> 00:20:06.310
the Schema compare does
not do data, right?

00:20:07.050 --> 00:20:10.380
I can do... probably the easiest way
is like back, back, back, back.

00:20:10.430 --> 00:20:13.290
That's a whole different session
or something like that, right?

00:20:13.340 --> 00:20:17.400
But now again this is just the
artifacts, the T-SQL to create

00:20:17.450 --> 00:20:21.120
these objects. Now as a developer
and as a software development

00:20:21.170 --> 00:20:24.840
lifecycle perspective, deployment
publishing perspective I now

00:20:24.890 --> 00:20:29.340
have the ability to do change control
and versioning and things

00:20:29.390 --> 00:20:30.170
like that, right?

00:20:30.220 --> 00:20:31.100
>> Cool.

00:20:31.150 --> 00:20:33.660
>> So I can... now that this is
done I can actually go in here

00:20:33.710 --> 00:20:35.410
and say publish.

00:20:36.150 --> 00:20:38.310
All right. Where do I want to publish
this to? Well, I can go

00:20:38.360 --> 00:20:43.210
back into database one or go and I
can save the profile and actually

00:20:43.260 --> 00:20:45.790
I can publish this back into the
original databases if I want.

00:20:45.840 --> 00:20:48.350
I can make a change and publish
it back into the database, make

00:20:48.400 --> 00:20:51.780
my change that way or I'm just going
to create a database called

00:20:51.830 --> 00:20:56.390
database one and if I click, you
know... let me click edit and

00:20:56.440 --> 00:20:57.630
let me connect here.

00:20:58.330 --> 00:21:04.860
We can do this the easy way. Connect.
Just grab that name.

00:21:08.050 --> 00:21:08.780
There we go.

00:21:09.680 --> 00:21:12.130
This is going to create a database called
database one and click okay.

00:21:12.720 --> 00:21:15.100
If I click publish it will actually
go and create database one

00:21:15.150 --> 00:21:16.860
for me. I'm not going to do that
because it just take some time

00:21:16.910 --> 00:21:19.600
but, you know, it will actually go
and just deploy all that stuff.

00:21:19.650 --> 00:21:22.740
Now there is no data in it but
I can easily go to SQL Server

00:21:22.790 --> 00:21:25.360
management studio and say okay,
export, import, export wizard,

00:21:25.410 --> 00:21:29.390
move my data, things like that.
But there's some power behind

00:21:29.440 --> 00:21:33.030
this because now I'm working as a
developer, right, and especially

00:21:33.080 --> 00:21:38.160
a DBA loves... DBA's love this. I'm now
working in a source control fashion.

00:21:38.210 --> 00:21:41.080
I'm now doing versioning. I'm not
just blindly throwing stuff

00:21:41.130 --> 00:21:43.590
out there and saying I'm going
to modify a table, all right?

00:21:43.640 --> 00:21:46.670
I can now get... do the whole software
development life cycle

00:21:46.720 --> 00:21:49.210
gig and say all right, can you
approve this? Okay. Good.

00:21:49.260 --> 00:21:52.910
Done. Let me publish this out there,
right? So this is very powerful.

00:21:52.960 --> 00:21:55.280
Now I can also say you know what, I
want to connect... I'm connected

00:21:55.330 --> 00:21:59.740
to on prem or on premises, right.
You can actually connect to

00:21:59.790 --> 00:22:02.530
Azure SQL data and deploy this
out that way. All right?

00:22:02.580 --> 00:22:05.030
Done. Then move my data and things
like that. So this gives

00:22:05.080 --> 00:22:08.460
me great power and flexibility to how
I want to work from a developer

00:22:08.510 --> 00:22:13.020
perspective, how I want to work
with SQL Server, right?

00:22:13.640 --> 00:22:16.690
I think there's big power and flexibility.
You know, we're just

00:22:16.740 --> 00:22:19.740
really sort of scratching the surface
because I think there's

00:22:19.790 --> 00:22:23.330
some misconceptions, lack of knowledge
that look, I can do a

00:22:23.380 --> 00:22:26.130
lot like you saw. I can do a lot
of things in Visual Studio here

00:22:26.180 --> 00:22:28.580
that I can do in SQL Server management
studio. It's really all

00:22:28.630 --> 00:22:31.720
in the same API, right? You really
call an SMO under the covers.

00:22:32.360 --> 00:22:35.700
Just a little paradigm shift because
I'm not clicking save.

00:22:35.750 --> 00:22:39.030
I'm clicking update or I'm clicking
publish or things like that.

00:22:39.080 --> 00:22:41.430
But I can still do a lot of things
in SQL Server data tools.

00:22:41.480 --> 00:22:44.170
I can do a Schema compare. I can
do a data... in fact, I don't

00:22:44.220 --> 00:22:46.600
have to do import-export wizard.
If I migrate this I can then

00:22:46.650 --> 00:22:53.130
just do like you saw her, I can
do a data compare, right, data

00:22:53.180 --> 00:22:56.690
compare and it will move the data for
me just like the tools, right?

00:22:56.740 --> 00:22:59.170
So I don't have to do an import-export
wizard. I can do a data compare.

00:22:59.220 --> 00:23:04.110
Compare now database one to AdventureWorks
2014, and say, okay,

00:23:04.160 --> 00:23:06.360
there's nothing there and move
it. It will actually take care

00:23:06.410 --> 00:23:10.350
of a lot of the data problems for me
like keys and things like that.

00:23:10.400 --> 00:23:11.270
Move it in the right order.

00:23:11.320 --> 00:23:11.770
>> That's nice.

00:23:11.820 --> 00:23:12.740
>> Things like that.

00:23:12.790 --> 00:23:15.100
>> It's like the Azure tool. It
avoids the context. You don't

00:23:15.150 --> 00:23:18.890
have to leave Visual Studio, go
to another IDE, then come back.

00:23:18.940 --> 00:23:21.490
You can just stay in Visual
Studio for longer.

00:23:21.540 --> 00:23:24.700
>> Stay in Visual Studio for longer.
So if I'm a developer and

00:23:24.750 --> 00:23:25.320
I need...

00:23:26.260 --> 00:23:28.820
oh, shoot, there's a problem with
the query I can actually go

00:23:28.870 --> 00:23:32.120
here and say... SQL Server is not
a black box for me any more.

00:23:32.170 --> 00:23:32.610
>> Right.

00:23:32.660 --> 00:23:36.090
>> I can say oh, there's a problem.
What is the column in there?

00:23:36.140 --> 00:23:39.450
Did I spell it wrong or whatever?
Whatever. So it's called this

00:23:39.500 --> 00:23:43.350
or drag and drop. There is IntelliSense.
Awesome. So it allows

00:23:43.400 --> 00:23:46.820
me to stay in one tool and
be more productive.

00:23:47.240 --> 00:23:52.770
>> Now, are there edition limitations?
Does this work with Express Tools?

00:23:52.820 --> 00:23:54.390
Is it an ultimate only feature?

00:23:54.440 --> 00:23:56.600
>> No. This actually works
with express.

00:23:56.650 --> 00:24:03.190
I was asked that yesterday, yep,
TGIP. With express it's not

00:24:03.860 --> 00:24:07.760
an ultimate only feature. Works with
all versions of Visual Studio.

00:24:07.810 --> 00:24:12.660
So if you want SQL Server data tools
just install express Visual

00:24:12.710 --> 00:24:14.520
Studio in there SQL Server
data tools, right?

00:24:14.570 --> 00:24:14.910
>> Right.

00:24:14.960 --> 00:24:18.740
>> So you're not really limited.
I think that's great because

00:24:18.790 --> 00:24:22.160
there's some great power and benefit
to SQL Server data tools.

00:24:22.210 --> 00:24:27.540
Put it in the hands of developers
and again for any DBA's that

00:24:27.590 --> 00:24:30.720
are out there watching they know
that these developers now have

00:24:30.770 --> 00:24:35.440
these tools so be judicious about
who can do what, right, because

00:24:35.490 --> 00:24:38.850
we don't want just any developer
going out there and going, you

00:24:38.900 --> 00:24:41.440
know because they may actually
right mouse click...

00:24:41.490 --> 00:24:42.690
>> Can you do administrator...

00:24:42.740 --> 00:24:43.100
>> Delete.

00:24:43.150 --> 00:24:47.020
>> type things? Can you add users
and set permissions?

00:24:47.070 --> 00:24:47.080
>> No.

00:24:47.130 --> 00:24:48.950
>> Or do you have to
go into manage...

00:24:49.000 --> 00:24:51.270
>> That's SQL Server management studio,
right, because if we look

00:24:51.320 --> 00:24:55.090
at what's available there, there's
tables, there's security, right?

00:24:55.140 --> 00:24:59.800
So we can do add new users. We can
actually do quite a bit there.

00:24:59.850 --> 00:25:02.260
I think that's fairly new. I didn't
know you could do that.

00:25:02.310 --> 00:25:07.410
I think there's... so it's actually
full fledged, it looks like,

00:25:07.460 --> 00:25:12.750
but as a DBA you're going to limit
who can do what, right?

00:25:12.800 --> 00:25:15.350
This is on my local box, I'm an admin.
I'm going to see everything.

00:25:15.400 --> 00:25:15.810
>> Exactly.

00:25:15.860 --> 00:25:20.590
>> Right. As a DBA and an admin
I'm going to limit developers

00:25:20.640 --> 00:25:24.410
so I may not see all of this and
I may not be able to do because

00:25:24.460 --> 00:25:26.750
the last thing we want to do is
have a guy go... a developer

00:25:26.800 --> 00:25:32.820
go, delete, right, and it may not
let me delete it based it on

00:25:32.870 --> 00:25:35.200
the keys and constraints and things
like that but, you know,

00:25:35.250 --> 00:25:38.050
at least I'm not messing stuff
up or, you know, I don't need

00:25:38.100 --> 00:25:40.300
all this data. I actually did a
delete without a work clause

00:25:40.350 --> 00:25:44.360
and there goes all my data, right?
So as a DBA I'm going to be

00:25:44.410 --> 00:25:47.510
very judicious about what rights
does Robert have. Okay, I'm

00:25:47.560 --> 00:25:50.760
going to allow you to do stuff, maybe
more stuff in a test environment,

00:25:51.040 --> 00:25:54.600
dev environment but not in production.
Things like that.

00:25:54.650 --> 00:25:57.520
But you know, again, as you see,
it's fairly... you know, I

00:25:57.570 --> 00:26:00.850
can do a lot of stuff, which is
potentially dangerous but you

00:26:00.900 --> 00:26:03.560
know the great thing is a lot of
people don't know that this

00:26:03.610 --> 00:26:07.920
actually is very flexible, very
powerful, work locally, work

00:26:07.970 --> 00:26:11.160
the in the cloud as well which makes
migration very nice, very

00:26:11.210 --> 00:26:14.460
easy, a whole lot of blog posts
and videos on how to migrate,

00:26:14.510 --> 00:26:17.710
but again, this is... I can do...
like you said, everything's

00:26:17.760 --> 00:26:18.780
right here in a single tool.

00:26:18.830 --> 00:26:21.450
>> Right. Very
cool. Cool.

00:26:21.500 --> 00:26:23.060
>> Now, there's a lot more we can
talk about but maybe we will

00:26:23.110 --> 00:26:25.410
come back and talk about publishing
because again there is publishing

00:26:25.460 --> 00:26:28.740
and versioning and software lifecycle
but we can be here all day.

00:26:28.790 --> 00:26:30.860
Maybe we will come book
and do this again.

00:26:30.910 --> 00:26:33.960
>> Awesome. Thanks.
Cool stuff.

00:26:34.760 --> 00:26:39.450
So if you're aware that the SQL
Server data tools were there

00:26:39.500 --> 00:26:41.870
but you hadn't really played around
with them, I think this was

00:26:41.920 --> 00:26:45.620
a great spur to get you to see
what you can do with them.

00:26:45.670 --> 00:26:50.130
It looks like the vast majority of
the stuff that you as a developer

00:26:50.180 --> 00:26:52.730
would do on an every day basis you
can do without ever leaving

00:26:52.780 --> 00:26:53.830
Visual Studio.

00:26:53.880 --> 00:26:54.830
>> Yeah. Yeah.

00:26:54.880 --> 00:26:55.220
>> Very nice.

00:26:55.270 --> 00:26:56.990
>> Which is very nice, right.

00:26:57.040 --> 00:26:57.830
>> Cool.

00:26:57.880 --> 00:27:01.840
>> Gives developers more power
at their fingertips, right?

00:27:01.890 --> 00:27:02.010
>> Yeah.

00:27:02.060 --> 00:27:06.170
>> So again, it makes SQL Server
not a black box any more.

00:27:06.220 --> 00:27:08.120
>> Cool. All right. Thanks
for coming on.

00:27:08.170 --> 00:27:09.410
>> My pleasure. Happy
to be here.

00:27:09.460 --> 00:27:12.000
>> All right. See you next time
on Visual Studio toolbox.

00:27:12.050 --> 00:27:12.700
>> Thanks much.