IntroductionWelcome| 00:04 | Hi! I am Martin Guidry! And welcome
to SQL Server 2012 New Features.
| | 00:10 | In this course, we'll look at numerous innovative
features Microsoft added to its latest release of SQL Server.
| | 00:16 | I'll start by showing you how to search and
manipulate files using the new file tables.
| | 00:21 | We'll write efficient code that pages
through data, and handles errors elegantly.
| | 00:26 | Then, I'll show you how to use SQL Server
Management Studio to debug your queries more
| | 00:32 | effectively by setting breakpoints, using the Watch windows,
and understanding the improvements to IntelliSense.
| | 00:40 | We'll see how to store user passwords at the
database level instead of the server level.
| | 00:45 | We'll cover all these features along with
some other tools and techniques and we'll
| | 00:49 | try to have a little fun along the way too.
| | 00:51 | Now let's get started with
SQL Server 2012 New Features.
| | Collapse this transcript |
| What you should know| 00:00 | This course is designed for database developers
with experience in a previous version of Microsoft
| | 00:05 | SQL Server, such as SQL
Server 2008, or SQL Server 2005.
| | 00:11 | You should have a working knowledge of the T-
SQL language, queries, indexes, full-text
| | 00:17 | search, and some experience debugging T-SQL.
| | 00:21 | If you'd like to review some of these topics
before going through this course, you can
| | 00:25 | do so here in the lynda.com
online training library.
| | 00:29 | Look for the course SQL Server 2008
Essential Training with Simon Allardice.
| | Collapse this transcript |
| Using the exercise files| 00:00 | The exercise files for this course are arranged
by chapter in the folder here on my desktop.
| | 00:05 | Each chapter folder contains text files that
I use for copy and pasting snippets of code
| | 00:11 | into SQL Server Management Studio.
| | 00:14 | These code examples are largely used to demonstrate
concepts, and I'll describe what they do as I use them.
| | 00:20 | The exercise files for this course are available
freely to all members, and with that in mind,
| | 00:26 | let's get started with
SQL Server 2012 New Features.
| | Collapse this transcript |
|
|
1. Getting StartedFinding and downloading SQL Server 2012 Express edition| 00:00 | To complete the exercises for this course, you'll
need access to a Microsoft SQL Server 2012 machine.
| | 00:07 | Because we're focusing on the new features
of 2012, these things would not be available
| | 00:12 | in Microsoft SQL Server 2008 or 2005.
| | 00:16 | So you will certainly need a 2012 edition.
| | 00:19 | You can use just about any version.
| | 00:21 | So if you have access to Enterprise or
Developer edition, go ahead and use those.
| | 00:26 | But if you don't, I'm going to show you how
to download a free version called Express.
| | 00:30 | Microsoft has released a few of their
tools recently in Express versions.
| | 00:35 | The Express versions are free for anyone to use
for testing, development, learning, and training.
| | 00:40 | You can not put it in production, but you're
welcome to test and learn on it as much as you like.
| | 00:46 | To find Microsoft SQL Server 2012 Express
you can visit your favorite search engine,
| | 00:52 | and search for something to the
effect of SQL Server 2012 Express.
| | 00:59 | We'll see several options there.
| | 01:01 | I'm going to go head and
click on the first one.
| | 01:03 | We do get more than one option.
| | 01:05 | I'll scroll down a little, and we'll
see we get about a dozen options.
| | 01:08 | Some of our choices are 32 bit or 64 bit.
| | 01:12 | Obviously, you'll need to download whatever
is compatible with your operating system.
| | 01:16 | There are also a few different
choices on what features are included.
| | 01:21 | If we scroll down a little
more, it shows us that detail.
| | 01:26 | The one I'm interested in here is the last
bullet point, Express with Advanced Services.
| | 01:32 | The key point of Advanced Services is it gives us
Full Text Search, and in some of the exercises,
| | 01:38 | I do want to use the Full Text Search.
| | 01:41 | So I'm going to go ahead, and
download Express with Advanced Services.
| | 01:45 | And on my machine, I am 64
bit, so I will get that one.
| | Collapse this transcript |
| Setting up the SQL Server 2012 Express edition environment| 00:00 | So now let's look at
installing SQL Server 2012 Express.
| | 00:10 | So I have two choices here, one is a New
Installation and one is an Upgrade.
| | 00:14 | I'm going to choose New Installation.
| | 00:20 | I will need to accept
the terms of the license.
| | 00:30 | So now the machine is asking me which
features I would like to install and it already has
| | 00:35 | several of them checked off.
| | 00:37 | Most of these I agree with.
| | 00:38 | The one I'm going to
clear is Reporting Services.
| | 00:41 | For this course we do not
need Reporting Services.
| | 00:44 | Reporting Services is a good tool.
| | 00:46 | If you'd like to install it
you're more than welcome to do so.
| | 00:49 | It shouldn't cause any problems.
| | 00:51 | I just choose not to at this time.
| | 00:53 | At the bottom we see it's going to default
to C:\Program Files\Microsoft SQL Server.
| | 00:59 | I am perfectly fine with that.
| | 01:01 | So I'm going to leave it with the default.
| | 01:03 | You're welcome to change yours
if you like. Click on Next.
| | 01:08 | It's asking me for a name of the
Instance and it suggested the name SQLExpress.
| | 01:12 | I am going to change that slightly.
| | 01:16 | Typically, my test machines end up
with lots of different versions of SQL.
| | 01:21 | So I will want to know that this
one is 2012Adv, and I'll hit Next.
| | 01:29 | It's asking me which Account I would like to
use to run the different services and whether
| | 01:33 | or not I would like them to
store up automatically or manually.
| | 01:36 | I'm going to accept all the
defaults and just click Next.
| | 01:40 | Here it's asking about security.
| | 01:43 | Are we going to authenticate our user via
Windows or via Mixed Mode? I typically go
| | 01:48 | Mixed Mode, particularly
in a test environment.
| | 01:52 | Microsoft says that using just
Windows authentication is more secure.
| | 01:57 | So in production you might want to think about that,
but in testing it's fairly common to have Mixed Mode.
| | 02:03 | I'll need to have a
password for the administrator.
| | 02:10 | At the bottom it's asking me which Windows
users I would like to be administrators.
| | 02:15 | By default it has taken the
current user and put in there.
| | 02:18 | That's a good idea.
| | 02:19 | You can add an additional user, if you like.
| | 02:24 | No changes here and we wait.
| | 02:32 | Great! Lots of green check marks there saying
we have successfully installed Microsoft SQL
| | 02:37 | Server 2012 Express Edition.
| | 02:41 | Now that we have successfully installed SQL
Server 2012 Express Edition we will need to
| | 02:47 | set up a database that we will use for testing, and
also import some information into that database.
| | 02:53 | I am going to go ahead and open up SQL Server
Management Studio and we will need to connect
| | 03:02 | to the appropriate instance.
| | 03:03 | I have staged some code for you already.
| | 03:06 | It's in your exercise files and it's just a
script to create a database called myDatabase.
| | 03:12 | We'll copy that and paste it into
a new query in Management Studio.
| | 03:19 | I'll scroll up and point out one thing here.
| | 03:22 | We created down an mdf file and an ldf file.
| | 03:27 | I am putting both of those in the default
folder. If you'd like to put them in a different
| | 03:31 | folder, you are welcome to do so.
| | 03:33 | Just be careful about the
permissions on those particular folders.
| | 03:37 | So let's Execute this.
| | 03:39 | It says it completed successfully.
| | 03:43 | We can go here now and see
myDatabase. That is good.
| | 03:47 | myDatabase will need at least
one table for us to work with.
| | 03:51 | So I am going to right-click, go
to Task and go to Import Data.
| | 04:00 | My Data Source is going
to be a Flat File Source.
| | 04:06 | It will ask me the name of the file and
that is also in your exercise files.
| | 04:15 | It is a CSV file called Students.
| | 04:20 | That should be all I need to configure here.
| | 04:21 | So I'll hit Next.
| | 04:23 | It's asking me for my Row
delimiter and my Column delimiter.
| | 04:26 | I can accept the defaults for both of those.
| | 04:30 | And we'll see in the Preview that we
look like we're getting good data there.
| | 04:34 | The values and fields like ID, LastName,
FirstName, all of those fields make sense.
| | 04:40 | So I'll hit Next.
| | 04:42 | It's asking me for a Destination.
| | 04:43 | The Destination will be the current instance of
SQL Server, and it's asking me what database.
| | 04:49 | It selected my database by
default, and that is what I want.
| | 04:52 | So I'll hit Next.
| | 04:54 | It's asking me where I would like to put it.
| | 04:55 | I would like to put in a new table.
| | 04:57 | The machine figured that out for me and is
suggesting it is going to call the new table
| | 05:02 | (dbo).(Students).
| | 05:04 | I'm perfectly okay with that.
| | 05:05 | So I'll hit Next.
| | 05:08 | On this last when I just hit
Finish, and Finish one more time.
| | 05:13 | Lots of green check marks
indicates a high likelihood of success.
| | 05:18 | I'll hit Close here.
| | 05:21 | Expand the Tables tree.
| | 05:23 | I see dbo.Students. That's good.
| | 05:26 | Let me go ahead and right-click and select
some rows from there, and yes, I get a whole
| | 05:32 | bunch of rows and all of those look correct.
| | 05:35 | So we have successfully
installed SQL Server 2012 Express.
| | 05:40 | We successfully set up a database we'll use
for the exercises and then we imported one
| | 05:45 | table of data.
| | 05:46 | So I think we are ready to go.
| | 05:48 |
| | Collapse this transcript |
|
|
2. Database Engine ImprovementsUsing FileTables to enhance FileStream| 00:00 | When you open up SQL Server Management Studio,
one of the first changes you'll notice for
| | 00:05 | SQL Server 2012 is the addition of a
new type of table called a File Table.
| | 00:11 | A File Table allows us to make a connection
between a Windows Share and a Database Table,
| | 00:21 | such that any file that appears in the
Share will become a row item in the table.
| | 00:27 | It allows us to run queries that tell us how
many files we have in a Share, what type of
| | 00:33 | files, what size the files are, et cetera.
| | 00:37 | So setting this up is a multiple step process.
| | 00:40 | The first thing we'll have to do at the
instance level is enable file stream, and we'll do
| | 00:46 | that with the Configuration Manager Tool.
| | 00:50 | So under SQL Server 2012 > Configuration Tools >
SQL Server Configuration Manager, I'll go
| | 00:57 | to the instance I'm
interested in, EXPRESS2012.
| | 01:02 | Right-click on that, go to Properties,
and there is a tab for FILESTREAM.
| | 01:06 | In there, I'm going to click on all of
the check boxes, enabling every feature.
| | 01:12 | Then, back in Management Studio, there is
a brief line of code we'll need to run.
| | 01:19 | I have that code staged for you in
your exercise files, paste that over.
| | 01:24 | It is an sp_configure, changing
the filestream_access_level to 2.
| | 01:30 | So we'll run that.
| | 01:32 | So that's all we have to
do at the instance level.
| | 01:33 | Now there are a few steps we need to
accomplish at the database level.
| | 01:40 | We'll need to provide a new file group.
| | 01:42 | We'll need to enable non-transactional access,
and we'll need to specify a directory for
| | 01:47 | the File Tables, and then we'll
need to create a File Table.
| | 01:51 | We'll cut and paste this
over to Management Studio.
| | 01:55 | I'll scroll up to the top, and
I'll walk you through this code.
| | 01:59 | Lines 3, 4, and 5 here, alter the database
to add a new file group, and then lines 9
| | 02:06 | through 15, we'll add a
file to that file group.
| | 02:10 | I'm putting mine in C:\SQLData which is a directory
you'll need to create. Scroll down a little more,
| | 02:18 | and we see lines 22 and 23 will
enable non-transactional access.
| | 02:25 | So let's think about this for a minute.
| | 02:27 | Typically, a database works
entirely in transactions.
| | 02:31 | Meaning that reading or writing of data;
either 100% succeeds or 100% fails.
| | 02:37 | But a file system often
allows partial success.
| | 02:42 | You can start reading a file, and even if
the file isn't complete, the machine will
| | 02:46 | still return the beginning of the file.
| | 02:49 | It's more of a streaming mindset.
| | 02:52 | So now that we're connecting a File Table to a
Windows Share, we need to resolve those differences.
| | 02:58 | The solution Microsoft came up with is to
allow in some cases non-transactional access.
| | 03:06 | So now we've created a database that in
some cases may return a partial result set.
| | 03:12 | The code to do that is
right here at lines 22 and 23.
| | 03:17 | Scroll down a little more, lines 31 and 32
will alter the database again, saying which
| | 03:24 | directory we are interested
in putting our files in.
| | 03:28 | And finally, lines 40 through 45 will
create a new table called DemoFileTable.
| | 03:35 | We'll place the contents of that
table into the directory myFileTable.
| | 03:40 | So you want to make sure you're running this against
the myDatabase, Database and we'll click Execute.
| | 03:46 | It says completed successfully.
| | 03:49 | Let's double-check that.
| | 03:50 | I'll expand myDatabase.
| | 03:51 | I'll expand Tables, I'll expand File
Tables, and I'll have to hit Refresh.
| | 03:59 | Now I see DemoFileTable which
should be empty at this time.
| | 04:07 | No results return that is correct.
| | 04:10 | The contents of this table have been
connected to a Share on my machine.
| | 04:15 | So let's go ahead, and look at the Shares.
| | 04:17 | I'm going to go to the Run, and
type in the name of my machine.
| | 04:24 | That will show me all of the Shares available.
| | 04:26 | Notice one of them has the exact
same name as my instance of SQL Server.
| | 04:30 | Let's open that up.
| | 04:32 | We have something called myFileTable which I
just created, and then we see an empty folder.
| | 04:38 | I'm going to right-click in here and create a new
text document, and we can leave it blank for now.
| | 04:45 | Come back over to Management Studio, I run this
query again, and I now have one result returned.
| | 04:52 | You'll see in the third column
there, it says New Text Document.
| | 04:56 | So I believe I have successfully
connected this File Table to a Windows Share.
| | 05:03 | So this new text
document was a blank document.
| | 05:05 | It has no contents, not really interesting.
| | 05:08 | Let's look at a text file
that does have some contents.
| | 05:11 | I'll work with the 02_01_setup.
| | 05:15 | I'm going to copy that file over into the Share,
and I can come back to Management Studio,
| | 05:21 | and I should see that new file there.
| | 05:25 | I will now have to scroll to the
right to see 02_01_setup.txt is there.
| | 05:29 | So I scroll back to the left.
| | 05:33 | We see for the file_stream, I
get a whole bunch of numbers.
| | 05:37 | That is because SQL Server is reading that
data as binary data, and I would prefer for
| | 05:44 | it to read it as characters.
| | 05:47 | I can tell it to do that.
| | 05:48 | I can write a query that does exactly that.
| | 05:53 | So we'll have to do a Convert.
| | 05:56 | And remember with Convert, the first thing you type
in is what you would like the data to look like.
| | 06:02 | I would like this to look like characters.
| | 06:05 | I don't know exactly how many
characters it's going to be.
| | 06:07 | So I'm going to say max.
| | 06:10 | The name of the field I'm
interested in pulling is file_stream.
| | 06:16 | I also want the name of the file.
| | 06:19 | We'll run that, and now we see one column
of the name of the file, that's great! And
| | 06:23 | the other column has the contents of the file.
| | 06:26 | The new text document is in fact blank. That is
the accurate representation of the contents.
| | 06:32 | And then, the second one
is a lot more interesting.
| | 06:35 | It actually has the code
we use to create all this.
| | 06:38 | That is the contents of the
file that I copied over there.
| | 06:41 | So you can start to think about some
uses of this in your applications.
| | 06:46 | If you're writing some sort of lightweight
document management system, you now very quickly
| | 06:52 | and easily can find the largest file, the
smallest file, the most recently accessed file.
| | 06:59 | Let's look at the columns we're given,
and all of these were the default columns.
| | 07:04 | When I created the table, I just said,
I want this table to be a file table.
| | 07:09 | I never specified which columns I wanted.
| | 07:11 | Microsoft gave me all of these by default.
| | 07:14 | A lot of them are very useful.
| | 07:16 | File_type obviously could be quite useful,
Creation_time, last_write_time, last_access_time
| | 07:23 | all of those, it seems would very
likely be in an order by clause.
| | 07:29 | We could look for what is the order in which
my files were created, or the order in which
| | 07:35 | they were modified most recently.
| | 07:38 | So certainly lots of possibilities if you are
writing your own document management system.
| | 07:43 | I also think about a lot of websites that
I've worked on where the users are allowed
| | 07:47 | to upload a file, and we
keep that file in a directory.
| | 07:52 | This would be a nice way to link that
directory or files to a table, and have a little bit
| | 07:58 | easier way to query it.
| | 08:00 | I'm going to discourage you from writing a
heavyweight document management system from
| | 08:05 | scratch because those tools already exist.
| | 08:07 | Microsoft has tools like SharePoint that will
do your document management for you already.
| | 08:13 | So recreating that from scratch
doesn't make a whole lot of sense.
| | 08:17 | This is a valuable tool in a
situation where you couldn't run SharePoint.
| | 08:21 | For example, maybe you wanted to embed it
in a small application you're creating.
| | 08:26 | This is a great tool.
| | 08:27 | But it's probably not
best to reinvent the wheel.
| | 08:31 | Don't go back and recreate a heavyweight File
Management System because those systems already exist.
| | 08:38 |
| | Collapse this transcript |
| Exploring the new semantic search for unstructured data| 00:00 | In this section, I'd like to talk
about searching some unstructured data.
| | 00:05 | In order to do this, we'll need to
install an additional tool from Microsoft.
| | 00:09 | This tool contains a
semantic language database.
| | 00:14 | So in order to find this, one of the easiest
things to do is just search for Semantic Language
| | 00:19 | Database, and here our first option will take
us to the Download page. Two options on the
| | 00:27 | Download page; one for the 64 bit
version, and one for the 32 bit version.
| | 00:33 | So choose whichever one is
appropriate for your operating system.
| | 00:39 | So here in my Downloads folder, I have the
installer for Semantic Language Database.
| | 00:43 | Just go ahead and double-click on that.
| | 00:46 | I'll click on Next.
| | 00:50 | Yes, I accept the terms of this license.
| | 00:53 | Now it's asking me where I would like to
install it? I almost always take the defaults, but
| | 00:58 | this is one of the installers where
I am going to change the default.
| | 01:02 | It is defaulting to C:\Program Files\
Microsoft Semantic Language Database. That is a very
| | 01:08 | descriptive name but unfortunately that
folder does not have the proper permissions.
| | 01:13 | I want to install this database into
realistically any folder that has the proper permissions
| | 01:19 | to run a database, and I know one
of those folders exist already.
| | 01:23 | It's the folder where Microsoft is
storing all its other databases.
| | 01:28 | So from the top, that is, C: > Program Files >
Microsoft SQL Server, and then the one that
| | 01:35 | has the name of the instance I am working
with, and then there is only one folder in
| | 01:40 | there, so the choice is
obvious, and then DATA.
| | 01:44 | So that's the directory Microsoft
typically stores its databases in.
| | 01:49 | Therefore, the permissions
should already be set there.
| | 01:52 | Hit Next, hit Install, and that should be it.
| | 01:56 | Okay, it looks like we are
complete, so I will hit Finish.
| | 02:01 | By running this MSI package, we successfully
copied over the files that will eventually
| | 02:06 | become the database.
| | 02:07 | Now we need to go into Management
Studio and attach that database.
| | 02:12 | So here in Management Studio, I'll right-click on
Databases and go to Attach, and then I will hit Add.
| | 02:20 | And now I will need to browse to the location.
| | 02:23 | Actually the location came up by default as
the default data location for this instance.
| | 02:28 | We have the new one called
semanticsDB, hit OK here, and hit OK here.
| | 02:36 | And now over here in Databases, I
have a new database called semanticsDB.
| | 02:40 | If you don't see that right away, you
might need to right-click and hit Refresh.
| | 02:46 | Mine came up there, and now just more step,
we need to register this as a semanticsDB.
| | 02:54 | That will be two lines of code.
| | 02:56 | I've staged those for you
already in your Exercise folder.
| | 03:00 | Copy and paste that over
into Management Studio.
| | 03:04 | Let's look at what it says.
| | 03:05 | We are going to execute a store procedure called
fulltext_semantic_register_language_statistics.
| | 03:14 | That store procedure takes one parameter,
that parameter is the name of the database.
| | 03:17 | I am going to use the database I just
created a second ago called semanticsDB.
| | 03:23 | Hit Execute and it said Command(s)
completed successfully.
| | 03:27 | So now we will need some
test data to work with.
| | 03:30 | This demonstration is working with
what Microsoft calls unstructured data.
| | 03:35 | In other words data that is not
in a typical database table.
| | 03:38 | We are instead storing our data in documents which
obviously happens an awful lot in the real world.
| | 03:44 | I have a few text documents in your
exercise files called Memo1, Memo2, and Memo3.
| | 03:52 | We'll look at these real quickly.
| | 03:54 | Memo1 is a fictitious memo, introducing the
arrival of a new faculty member to our university.
| | 04:03 | Memo2 and Memo3 are similar,
using slightly different phrasing.
| | 04:10 | Memo1 and Memo2 are about the same length.
| | 04:14 | Memo3 is about twice as long, just has
more detail basically on the same topic.
| | 04:20 | Let's copy all three of those files into the
File Table we created in the previous exercise
| | 04:29 | which is also part of a share on our machine.
| | 04:36 | We had dropped two files in there previously.
| | 04:39 | We'll add Memo1, Memo2, and Memo3.
| | 04:43 | We'll also need to create a Full Text Index
on those files, and I've stated some code
| | 04:50 | for you to do that.
| | 04:51 | Copy and paste this code
into Management Studio.
| | 04:56 | We'll look at it briefly.
| | 04:58 | Line 4 creates a full text catalog, then line 7
through 15, creates a full text index on that catalog.
| | 05:05 | This is very similar to which you would
have done in previous versions of SQL Server.
| | 05:10 | There is a new statement on
line 12, STATISTICAL_SEMANTICS.
| | 05:15 | That is the new feature in SQL Server 2012 that
I'm going to demonstrate here in just a second.
| | 05:23 | That last line, line 14 where it says
KEY INDEX (PK_DemoFile_ and a number.
| | 05:31 | That number may be different on your machine.
| | 05:34 | That's a primary key that was created
automatically when we created our File Table.
| | 05:40 | So if you want to navigate to your File Table, you
can find out exactly what your key is called.
| | 05:46 | Under Keys, you will see one that says PK_
DemoFile, and has some number after it.
| | 05:54 | You want to make sure that
is the one you are using.
| | 05:58 | We'll hit Execute, and Command(s)
completed successfully.
| | 06:02 | So that means we have created a Full Text
Index with statistics on the semantics.
| | 06:09 | That means we can run some new types
of queries new in SQL Server 2012.
| | 06:15 | I have staged some of this code
for you in your exercise files.
| | 06:18 | I am going to work with
just the top query first.
| | 06:22 | Copy and paste that over.
| | 06:24 | Lines 1 through 7 or creating a variable
called Document ID, and storing the ID of Memo 01
| | 06:33 | in that variable, and then so most of our
exciting stuff happens in line 11, 12, and 13.
| | 06:41 | We see the table SEMANTICKEYPHRASETABLE.
| | 06:44 | That's actually a table valued function; a function
that takes three parameters and returns a table.
| | 06:50 | The first parameter is the name of the table
we will be querying, the next is the column
| | 06:55 | we are interested in using as the primary key,
and then the last one where we are passing
| | 07:00 | DocID, that is the value of the
document we are interested in.
| | 07:05 | We'll execute that, and I will scroll up, so
that we can see the key phrases it returned.
| | 07:12 | It found the most interesting key phrase as
Janelle, second as Roper, third as Faculty,
| | 07:18 | and Fourth as professor.
| | 07:20 | So Microsoft has used the semantic database
we created in earlier in this exercise, went
| | 07:27 | in and analyzed that memo and found what it
perceives to be the most important words.
| | 07:31 | So words like 'and', 'or', 'the' are
automatically considered to not be very important.
| | 07:38 | Words that are unusual, words that appear
often, words that are the very first word
| | 07:42 | in a sentence or come up early in a paragraph,
those words are considered to be more important.
| | 07:49 | So Microsoft's opinion, this is a memo about
Janelle, Roper, Faculty, Professor, and that's
| | 07:55 | actually pretty close to correct.
| | 07:57 | Those are the key points of that memo.
| | 08:00 | So this is a very interesting new feature.
| | 08:03 | I have one more query I want
to run that's also staged.
| | 08:08 | So I'll copy and paste that from the
exercise files to Management Studio.
| | 08:13 | The top lines 1 through 7 is pretty similar
to the example we just looked at, it's just
| | 08:18 | finding the DocID of Memo1.txt.
| | 08:21 | The query 9 through 14 is
a little more complex.
| | 08:26 | The heart of it is line 11 where it's
referencing SEMANTICSIMILARITYTABLE.
| | 08:32 | As the name implies, this is going to find
files that are semantically similar to Memo1.txt.
| | 08:40 | So if we go ahead and run this, and look at
the results, we see that the machine feels
| | 08:48 | Memo2 is 93% similar, Memo3 is 67% similar,
and the 02_01_setup file is only 1% similar.
| | 09:02 | I would have to agree with these numbers.
| | 09:03 | Memo2 is very similar to Memo1, Memo3 has
some of the same information, but it's about
| | 09:09 | twice as long, so only about half of the
information is similar, and then the 02_01_setup file
| | 09:16 | has basically no similarities to Memo1.
| | 09:19 | So I would agree with these results.
| | 09:22 | I've done these demos with text files.
| | 09:24 | If you would like to use different
type of files, that is possible.
| | 09:28 | SQL Server utilizes what's called an IFilter,
and an IFilter will extract the text from
| | 09:34 | files like Word documents, Excel, PDFs, et
cetera. Pretty easy to find IFilter files
| | 09:42 | with a web search.
| | 09:46 | If we search for something like IFilter office,
we should be taken to the Microsoft Download
| | 09:51 | page for the IFilters for the various office
documents, and that will work not only with
| | 09:59 | SQL Server, but also other Microsoft products.
| | 10:02 | So a pretty useful tool!
| | Collapse this transcript |
| Exploring enhancements to full text search| 00:00 | In this section I would like to talk about
a small enhancement to searching data that
| | 00:05 | is in a full text index.
| | 00:07 | It's a pretty small change from 2008, but
I think a very nice feature. It gives the
| | 00:12 | developer a little more control.
| | 00:15 | I'll be using the full text index
we created in the previous movie.
| | 00:19 | Now that full text index is on a file table.
That is not necessary for this to work. What
| | 00:24 | I'm going to demonstrate would work on any
full text index whether it's connected to
| | 00:29 | a file table or not.
| | 00:31 | I have staged a little code for you in your
exercise files. I'll copy and paste that over
| | 00:36 | into management studio.
| | 00:38 | And what I have on the screen right now is
actually something that would work in 2008.
| | 00:44 | We are going to search for information that
contains the word proud and the word Janelle
| | 00:51 | and they have to be near one another.
| | 00:54 | So I'll execute that.
| | 00:56 | And we see both Memo3 and Memo2 have
the word proud near the word Janelle.
| | 01:02 | In SQL 2008 this is about as far as I could
take it. I didn't have any more options.
| | 01:06 | In 2012 they've given me the option to say how far
or how close we would like these words to be.
| | 01:15 | So rather than near being some abstract
concept we can actually put a hard number on it.
| | 01:21 | I can say I want the proud to be
within 12 words of the word Janelle.
| | 01:29 | And we run that again and
we get the same results.
| | 01:34 | But now if you start to think about if we're
looking for a situation where someone is saying
| | 01:37 | that proud of Janelle and those two words
are 12 words apart they could actually be
| | 01:42 | saying they're proud of something else.
| | 01:44 | So I want them to be much closer. Let's
demonstrate that where they are only four words apart.
| | 01:52 | And now we only get one file, we just get
Memo2.txt where those two words are within
| | 01:58 | four words of one another.
| | 02:00 | So again, this is a pretty small change over
2008, but I do like it. I always like any change
| | 02:06 | that gives the developer more control.
| | 02:08 | So now rather than just saying the near I
have the option to say exactly how many words
| | 02:14 | apart these things should be.
| | Collapse this transcript |
| Implementing ad-hoc paging with offset and fetch| 00:00 | In this section, I would like to talk about
creating a query that returns data that is
| | 00:05 | ordered that will likely be
displayed to the user in small chunks.
| | 00:10 | So we'll display the first 10 records within
the next 10 records, within the next 10 records
| | 00:15 | et cetera, et cetera.
| | 00:18 | This is commonly done in many applications
and can sometimes require writing some complex
| | 00:23 | code in the application.
| | 00:25 | New in SQL Server 2012 we have some
additional tools where we can fairly simply modify a
| | 00:32 | database query that it will handle just giving
back part of the results commonly known as paging.
| | 00:40 | So we now have some built-in paging tools.
| | 00:43 | I'll start of with a new query. We'll get
to work with the students table, so let's select a
| | 00:49 | few fields from that student table, and let's
return them in an ordered format. We'll order
| | 00:56 | by ID and there we see
it returned 25,000 rows.
| | 01:02 | Obviously on an end-user application you're
not going to display 25,000 rows at one time.
| | 01:08 | You're going to cut that down. Let's look at
an example where we want to only get back
| | 01:13 | five records at a time. We'll add to the
order by clause, we'll add the phrase offset and
| | 01:19 | we'll say offset by five rows.
| | 01:24 | So now instead of starting on the very first
record, it skipped the first five records and
| | 01:30 | gave me what was number six. So this would
be the information for the second page of
| | 01:36 | data, and then we could say offset 10 rows
to give me the information that was stored
| | 01:43 | on the third page of data.
| | 01:46 | One problem though now is it still returning
way more records that I want. I said I only
| | 01:51 | want to display five at a time, and it's
displaying everything beyond those first 10 rows, so
| | 01:58 | 24,990 rows. I really don't need that many.
| | 02:03 | I can change that by telling it to
only fetch a certain number of rows.
| | 02:08 | So fetch is the other part of the new order by
clause, so I can say fetch next 5 rows only.
| | 02:21 | We'll run that and it only returns five rows.
| | 02:24 | So this is starting on record number 11, because
I set offset 10 rows and then it's going through
| | 02:30 | the next 5, and then the next query you would
say offset it by 15 and again only return
| | 02:37 | 5. We don't need to change that and
that gives me the next group of 5.
| | 02:42 | So by using the new offset and fetch feature
new in SQL Server 2012 we can implement paging
| | 02:50 | on the database side rather than having to
do all of our paging in the application.
| | 02:57 |
| | Collapse this transcript |
| Using the new throw statement to enhance try-catch blocks| 00:00 | In this section, I'd like to talk about a
new way to handle errors in SQL Server 2012.
| | 00:07 | First of all let's look at some of
the ways it's currently handled.
| | 00:10 | I have some code in your exercise files, and I
intend for this to be something fairly similar
| | 00:16 | to what most developers
do for handling errors.
| | 00:19 | Lines 2 and 3 are inside of a TRY statement,
and I intentionally have created an error
| | 00:25 | there. I'm going to attempt to divide by zero.
| | 00:28 | Lines 7 through 15 are located inside of a
CATCH block. They should handle that error
| | 00:36 | and return a message to the user.
| | 00:38 | We'll execute this and we
see it does in fact work.
| | 00:42 | This RAISEERROR technique has a few issues
and therefore Microsoft has implemented a
| | 00:47 | new way of doing this.
| | 00:49 | It's not dramatically different.
| | 00:51 | Let's talk about some of
the problems here first.
| | 00:54 | One is the unusual spelling of RAISEERROR.
| | 00:57 | If this is actually two words then I would
think you would be spelled raise with an E
| | 01:03 | and then another E to start error. And if
you combine those two words, in some places
| | 01:09 | that does work, but in most places it doesn't.
| | 01:12 | In most places you have to use just
one E in the middle of that word.
| | 01:16 | So there's always been some confusion amongst
developers about do we say RAISEERROR with
| | 01:22 | one E in the middle or with
the two Es in the middle.
| | 01:25 | Some places you have to use one, some
places you have to use a different one.
| | 01:29 | All of that is somewhat cumbersome.
| | 01:31 | We also have the issue when we raise errors, sometimes
it stops the control of flow, sometimes it does not.
| | 01:39 | So instead of doing a divide by zero up here in the TRY
block I'll change that to a RAISEERROR statement also.
| | 01:48 | And the first parameter we passed is the
message, and the next parameter is the severity.
| | 01:54 | So I'm going to start off with severity 12 and
then the state in this case doesn't matter.
| | 02:01 | So we run this and it
executes as we would think.
| | 02:06 | At the bottom we get the red message
indicating that it was an error, a level 12 error.
| | 02:13 | But if we change the level down to 10 and we
run this again, you notice now what comes
| | 02:18 | up at the bottom is black.
| | 02:21 | Because a level 10 error doesn't stop the flow. So this
continued on and it wasn't recognized as an error.
| | 02:28 | Being I use the phrase RAISEERROR and the
machine chose to interpret that as not an error,
| | 02:35 | it's been a little confusing to me.
| | 02:37 | Let's talk about the new way of doing this.
| | 02:40 | Microsoft has eliminated the word
RAISEERROR and they've changed it to THROW.
| | 02:50 | And I'll put this back like we had it a minute ago
where we were doing the divide by zero problem.
| | 02:57 | We'll execute that and we see that just by
using the word THROW in the CATCH block it
| | 03:04 | returns the message divide
by zero error encountered.
| | 03:08 | So that's simpler than the RAISEERROR technique,
it also may be more intuitive for many developers,
| | 03:14 | particularly if you've
written a lot of C# or Java code.
| | 03:18 | This is exactly how it works.
| | 03:21 | The THROW has the advantage of always
stopping the control of flow whereas the RAISEERROR
| | 03:27 | would sometimes stop the control of flow,
sometimes it wouldn't, depending on the severity.
| | 03:34 | So some reasons to stop using
RAISEERROR and start using THROW.
| | 03:39 | Number one, the spelling of RAISEERROR is odd,
the RAISEERROR has been deprecated, and
| | 03:46 | in Microsoft terms deprecated means
it will eventually be taken out.
| | 03:51 | It still works perfectly fine in SQL Server
2012 but eventually they are going to remove
| | 03:57 | the syntax of RAISEERROR.
| | 04:00 | The THROW is more similar to
other programming languages.
| | 04:04 | The THROW is simpler and the THROW will always stop
the control of flow and jump you to the CATCH block.
| | 04:10 | It's not a big change. All-in-all your error-
handling mindset and philosophy doesn't have to change
| | 04:16 | dramatically. It's more of a small syntactic
change, but I encourage you to start using
| | 04:21 | THROW and stop using RAISEERROR.
| | Collapse this transcript |
|
|
3. Improvements to SQL Server Management Studio DebuggingUsing breakpoint condition, breakpoint hit count, breakpoint filter, and breakpoint action| 00:00 | SQL Server 2012 introduces powerful new
debugging features associated with breakpoints.
| | 00:07 | Let's look at some code I have staged for you.
| | 00:09 | This code contains a variety of T-SQL statements,
some loops, some conditionals, and some queries.
| | 00:15 | I'm not trying to demonstrate any
particular feature of T-SQL.
| | 00:18 | I just wanted to create some code that
would provide lots of debugging opportunities.
| | 00:23 | So don't get too caught up on
what this code is accomplishing.
| | 00:27 | So anywhere in the code out to the left I
can click and create a breakpoint signified
| | 00:32 | by that little red circle.
| | 00:34 | Nothing new, same as SQL Server 2008.
| | 00:37 | But when we right-click on the
breakpoint we see some new options.
| | 00:42 | The top two options there Delete and Disable have
always been there and are fairly self-explanatory,
| | 00:46 | but everything under that is new.
| | 00:50 | So let's talk about these
one at a time. Location.
| | 00:53 | I'll go ahead and select Location and it saying
this breakpoint starts on Line 7, Character 1.
| | 01:00 | Every breakpoint defaults to starting at Character 1,
but you can change that if you would like it.
| | 01:06 | So I want to move this so we can see behind.
| | 01:08 | Currently, it has the red
highlight around the phrase SET @x = 1.
| | 01:13 | So that is where it's going to break.
| | 01:15 | I can change what character it starts at to make
that actually break when one of the other phrases.
| | 01:21 | So let's change that to 15.
| | 01:24 | Now it moves over and now it's
going to break on SET @y = 7.
| | 01:29 | So I have successfully changed
the location of that breakpoint.
| | 01:33 | It's still on the same line, but
it's at a different character.
| | 01:36 | So I'm going to create
another breakpoint down here.
| | 01:40 | Right-click on that and
we'll talk about Condition.
| | 01:42 | A Condition allows us to write a statement
that evaluates to either true or false.
| | 01:48 | If the statement evaluates to true, the
breakpoint will cause the break to happen.
| | 01:52 | If the statement evaluates to false,
the breakpoint will be skipped entirely.
| | 01:57 | We commonly look at the value of
different variables in these conditions.
| | 02:00 | So we can do something like, if y<1.
| | 02:05 | So in other words if y is 0 or a negative
number, I might think this logic is going
| | 02:11 | to function differently.
| | 02:12 | Therefore, I only want to break
under that condition. I hit OK.
| | 02:17 | And you'll notice at that
breakpoint the icon changed just a little.
| | 02:20 | There is now a white plus mark in the middle
signifying this is a conditional breakpoint.
| | 02:26 | Now I'll create another breakpoint here,
right-click on it, and will go to Hit Count.
| | 02:32 | This is probably my personal
favorite of the new options.
| | 02:35 | We can set a breakpoint to only function when
it has already been reached a certain number
| | 02:39 | of times or set it to stop functioning
after it has been reached too many times.
| | 02:45 | This is very useful if we are concerned
about loops that run too many times.
| | 02:49 | I sometimes set one of these breakpoints in
every loop and give it a very high value.
| | 02:54 | So basically I'm telling the machine I only want
a break if we've hit this point 1000 times.
| | 03:01 | Because if we've hit this point 1000 times,
something is wrong with my code and I need
| | 03:06 | to know about it.
| | 03:07 | So if this loop executes
more than 1000 times, we will break.
| | 03:12 | Set another breakpoint and
our next option is Filter.
| | 03:16 | Filter allows us to set up conditions.
| | 03:18 | The example Microsoft uses is to only break
if machine name is equal to a certain value
| | 03:24 | or process name is equal to a certain value.
| | 03:28 | So this would be likely we want to set up
breakpoints that will break in development,
| | 03:32 | but not break in production.
| | 03:34 | So you would set the machine name to your
development machine and allow it to break
| | 03:39 | only on development.
| | 03:43 | Next option is When Hit.
| | 03:46 | So the default action of a breakpoint is that
when it's hit, it stops the execution and
| | 03:50 | allows you to look at the values.
| | 03:53 | But we could have it do something else.
| | 03:55 | We could have it print a
message to you in the output window.
| | 03:59 | We can say continue execution
or don't continue execution.
| | 04:03 | We can change the behavior of the
breakpoint by using the Is Hit option.
| | 04:11 | Next is Edit Labels.
| | 04:13 | So we can give labels to our breakpoints
and then we can later search for those.
| | 04:19 | So I created a label called ABC.
| | 04:21 | I could later search for all the breakpoints
that have the label ABC and either enable
| | 04:27 | those, disable those, or
delete those as a group.
| | 04:31 | So this doesn't change the
functionality of the breakpoint at all.
| | 04:34 | It is just an administrative tool.
| | 04:37 | If you want to perform the same action on a
large number of breakpoints, you give them
| | 04:42 | all the same label, and then you can
perform that action on those as a group.
| | 04:47 | The last option is Export.
| | 04:50 | We can export all of our breakpoints to
an XML file and then later import them.
| | 04:56 | The logical place you would do this is while
you're in development, you might have all
| | 05:00 | of your breakpoints set up.
| | 05:02 | You then would remove the
breakpoints before you move into production.
| | 05:06 | But rather than having to re-create those
breakpoints later, if you exported them first,
| | 05:11 | you can then import them rather than
having lost all that work.
| | 05:16 | So again, lots of new options on breakpoints
and I'm excited to see these new features
| | 05:20 | in SQL Server 2012.
| | Collapse this transcript |
| Exploring the Watch, Quick Watch, and Quick Info windows| 00:00 | Now I would like to talk about a few of the changes
made to some of the windows while we're debugging.
| | 00:06 | So again I have some sample code staged for
you in your exercise files. It's actually
| | 00:10 | the exact same code we used in the previous
movie. Copy and paste that over to Management
| | 00:16 | Studio, and I'll go ahead and set a
breakpoint at a random point and I'll press Debug.
| | 00:24 | Now here in my debugging interface lots of
windows have popped up. Your windows may look
| | 00:30 | a little different. Remember you can open
and close many of these off the Debug menu
| | 00:35 | here at the top, we have the option to
turn on or turn off various windows.
| | 00:41 | First of all at the bottom I have the locals
window. Now once I've press Play and hit my
| | 00:46 | breakpoint, it shows me the value of the local
variables: X equal 1, Y equal 7 and Z equals 0.
| | 00:56 | Reading that information has not changed,
but in SQL Server 2008 this was a Read Only
| | 01:01 | window. In SQL Server 2012 you can change
values in this window. So what the value of Y is
| | 01:08 | currently 7, I'm going to change that to a
random number and that change will be reflected
| | 01:15 | in the code above. We'll see we are currently
paused on a line that is going to subtract
| | 01:21 | one from the value of Y.
| | 01:23 | So I'm going to press F11 on my keyboard to
cause that to advance through that line.
| | 01:30 | And we see it change the value of Y to 98, so it took
my new value and continued on with the code there.
| | 01:38 | We also have in SQL Server 2012 a new type
of window called the Quick Info window.
| | 01:45 | We get to this just by hovering the mouse
over a particular variable. You don't have
| | 01:50 | to click on it, just hover the mouse over and we
see here that Y currently has a value of 98.
| | 01:57 | I'll go ahead and click on the push pin there,
and now that Quick Info window is anchored
| | 02:03 | and I can press F11 a few times to continue
cycling through the code, and we'll see the
| | 02:09 | value of Y change there. The value of Y is
changing and again this is a Read/Write window,
| | 02:16 | so I can change the value
of Y if I so choose to.
| | 02:20 | I'll press F11 a few more times and we see it
continuing to work. So that's a new feature
| | 02:26 | of SQL Server 2012.
| | 02:28 | Another new feature is while we're in Debug
mode, while it is broken, we can add comments.
| | 02:36 | So I can comment here and type some comment.
| | 02:41 | And that comment will stay not only in Debug
mode, but also when I get out of Debug mode
| | 02:47 | that should still be there.
| | 02:52 | And there we go my comment has stayed.
| | 02:54 | So if you want to either make temporary notes
to yourself or write permanent comments that
| | 02:58 | will live on with your code forever, you can
do that either in this view or in Debug mode.
| | 03:04 | That's a nice new feature of SQL Server 2012.
| | 03:08 |
| | Collapse this transcript |
| Reviewing Transact-SQL IntelliSense improvements| 00:00 | SQL Server 2012 has added some new features
to IntelliSense, specifically support for code
| | 00:08 | snippets. So I have some code staged for you
for this one. It's very similar to the code
| | 00:13 | we used in the previous movie. I'll copy and
paste all of that into Management Studio.
| | 00:20 | So I can move my cursor anywhere in this code
and right-click, and we'll see the option to
| | 00:27 | insert snippet, which will give me a menu
of various things I can insert, so let's do
| | 00:34 | Index and then Create a Basic Index.
| | 00:38 | So code gets inserted, it has the
basic template for creating an index.
| | 00:44 | Some of the things here I'll need to add myself
such as what table I would like to use, what
| | 00:50 | column I would like to use
and the name of the index.
| | 00:54 | But it's got me started and that is the point
of these snippets, to be small sections of
| | 00:59 | reusable code that we can get some mileage out of;
write it once and then reuse it several times.
| | 01:07 | Typically they're not going to give us
complete functionality on their own they're going to
| | 01:12 | be sort of a fill in the blanks mindset of
there's a few more pieces of information we
| | 01:17 | need to get to really
finish it up and drive it home.
| | 01:20 | I'm going to right-click again and look at
Insert Snippet, and talk about a few of these
| | 01:25 | things, things like creating logins and
creating rolls I find are very useful, because those
| | 01:31 | are things that I don't do real often and therefore
having the prompting of a snippet helps me a lot.
| | 01:38 | I am going to scroll down here and highlight the
bottom of my code the last three lines, right-click.
| | 01:45 | And notice right under Insert Snippet there is
Surround With, and this is sort of a special
| | 01:53 | type of snippet, it's going to insert some
code right above where I'm highlighted and
| | 01:59 | other code right below
where I have highlighted.
| | 02:02 | So basically it will surround
the code I have highlighted.
| | 02:04 | It can surround things with things like an if
statement so now rather than my code always
| | 02:11 | running I have surrounded it with an if statement,
and I can create a conditional, and now that
| | 02:18 | code will only run under certain conditions. So
the Surround With snippets are also very useful.
| | 02:28 | SQL Server 2012 comes with a fairly complete
set of snippets, but if you would like to
| | 02:33 | create your own you can.
| | 02:36 | If we go to the Tools menu the very top option
is Code Snippets Manager which allows us to
| | 02:43 | remove existing snippets, create new snippets,
or change what folders these snippets are in.
| | 02:51 | Creating your own snippet is not as easy as
you might want it to be. I have some source
| | 02:57 | code for one in your exercise files. It's
called TryCatch.snippet. I'll right-click
| | 03:05 | on that and edit it in a text editor and
we'll see it is a combination of XML and SQL.
| | 03:13 | So the SQL here around lines 28 through 38,
this is the SQL that I would like to be part
| | 03:20 | of the snippets. All of the XML surrounding it
describes the type of snippet, what directory
| | 03:26 | it would be in, what
situations it could be used in.
| | 03:30 | So the syntax here is a little tricky because
you're writing XML and SQL at the same time.
| | 03:37 | Realistically, I've never had to write a snippet
from scratch. If there's a particular snippet
| | 03:42 | I'm looking for, I typically go to my favorite
web browser and search for something descriptive
| | 03:48 | about the snippet I'm looking for, and I've
always been able to find someone who's already
| | 03:53 | created this snippet and is willing
to let me copy and paste it over.
| | 03:58 | And so it is somewhat rare that you
have to create these from scratch.
| | 04:05 | As you become familiar with the new snippet
functionality, hopefully it will decrease
| | 04:11 | the amount of time it takes you to code, and
increase the consistency with which you can
| | 04:17 | write good code.
| | Collapse this transcript |
|
|
4. Changes to the Scope of ObjectsUnderstanding changes to user passwords and logins| 00:00 | In SQL Server 2005 and 2008 if we want to
give a user access to one of our databases,
| | 00:09 | we first have to create a login at the server
level and then create a user at the database level.
| | 00:16 | So just for the sake of
discussion I'll go ahead and do this.
| | 00:19 | I'm going to open up Security, right-
click on Logins, and create a New Login.
| | 00:26 | I want a SQL Server Login and
we will call him TestLogin.
| | 00:35 | Give it whatever password
you see fit and hit OK.
| | 00:40 | Now that we have a login called TestLogin, I
can go into any of my databases under the
| | 00:48 | Security section, open up Users.
| | 00:52 | I can right-click and create a New User.
| | 00:55 | I have to say which login name I want this to
connect to, and I've just created one called
| | 01:02 | TestLogin, and I'll call this guy TestUser.
| | 01:09 | So now I have TestUser contained in myDatabase
and I have TestLogin contained at the Server
| | 01:16 | level, and those two have a relationship.
| | 01:19 | One of the problems with this setup is that
if I ever move the database, myDatabase,
| | 01:25 | if I ever move that to a different server,
well, TestLogin isn't going to be on that
| | 01:30 | new server and therefore we have a problem.
| | 01:33 | TestUser would become disconnected from his
login and would probably stop functioning.
| | 01:40 | So this is a big problem for developers when
you work on a development machine that has
| | 01:44 | one set of logins and then you copy your
database to a production machine that has a different
| | 01:50 | set of logins, a lot of times things break.
| | 01:53 | It can be difficult to troubleshoot and we
usually end up having to write a bunch of
| | 01:57 | code to sync up the logins.
| | 02:00 | Microsoft has apparently thought of this and
come up with a new feature in SQL Server 2012.
| | 02:07 | We can now store all of the
information in the database.
| | 02:11 | So we don't have to have a
login at the server level anymore.
| | 02:17 | Everything happens at the database.
| | 02:19 | This is a special type of Database called a
Contained Database or called a Partially
| | 02:25 | Contained Database.
| | 02:26 | And I have some code staged
that will set one up for us.
| | 02:30 | So in your exercise files I'll copy it over into
Management Studio and we'll walk through this.
| | 02:39 | I'll scroll back up to the top.
| | 02:41 | Lines 1 through 4 are going to just turn on
the fact that we would like to use partially
| | 02:47 | contained databases.
| | 02:48 | We'll have to use the sp_configure for that.
| | 02:51 | Line 7 creates a new database that I'm going
to call PartContDB for partial containment,
| | 02:59 | and the only parameter I'm
passing to it is CONTAINMENT=PARTIAL.
| | 03:03 | That's the only option I'm setting.
| | 03:04 | Then line 16 and 17 will
create one table in that database.
| | 03:10 | Scrolling down line 25 and 26 will create a
new user called user1 and give him a password.
| | 03:18 | Then line 28 and 29 are going to
add that user to a particular role.
| | 03:24 | Go ahead and Execute all of that.
| | 03:26 | It looks like that was successful.
| | 03:28 | We can look for our new database by hitting
refresh, and there is my PortContDB, and in
| | 03:36 | there I should have a user.
| | 03:38 | A User called user1 and that user is not
contained to any login at the server level.
| | 03:44 | We can right-click and look at his properties
and see his password is stored with the database.
| | 03:51 | It's not stored at the server level.
| | 03:54 | So user1 should be able to login.
| | 03:57 | We can Connect Object Explorer.
| | 03:59 | We will have to change to
SQL Server Authentication.
| | 04:02 | The login will be user1, and if I try to just do
it like this, login into the server should fail.
| | 04:12 | So that is correct.
| | 04:15 | But if I hit Options here and say I
particularly want to only connect to one database.
| | 04:20 | I want to connect to PortContDB, then I can
in fact connect, and we see down here he is
| | 04:30 | connected to one database, the PortContDB.
| | 04:35 | So although user1 does not have the ability to
log into the server, he does have the ability
| | 04:41 | to connect to this one database, because
his password is contained in that database.
| | 04:47 | Meaning, if we copy this database to any
other server, he should still be able to login.
| | 04:53 | We don't need to create him a login on that server,
all of his information is stored in the database.
| | 04:59 | So this is a great feature.
| | 05:00 | Very convenient for anyone who moves databases
often, and just depending on your working environment
| | 05:06 | you may move databases often going from
development to production, or going from development to
| | 05:12 | testing and then testing to production.
| | 05:15 | Now do be aware of the different security like I
said we're storing the password in the database.
| | 05:21 | That's a new mindset.
| | 05:22 | So you now have some
confidential information in your database.
| | 05:26 | You need to be careful to protect that and
just be aware that now passwords can be stored
| | 05:32 | in the database.
| | 05:33 | That's information you're
responsible to protect.
| | Collapse this transcript |
| Creating user-defined server roles| 00:00 | Microsoft SQL Server has long
had the concept of server roles.
| | 00:05 | So under Security, we can see the available server
roles hasn't changed much in a few generations.
| | 00:13 | We have things like the securityadmin,
serveradmin, dbcreator, et cetera, et cetera.
| | 00:20 | Now in SQL Server 2012 for the first time,
we can create our own custom server roles.
| | 00:26 | They do not yet have a
graphical interface for this.
| | 00:29 | So if you right-click, you don't see any
options that say Create New Server Role.
| | 00:35 | We have to do all this in code.
| | 00:37 | So I have staged some code for
you in your exercise files.
| | 00:41 | Copy and paste all of that
over into Management Studio.
| | 00:46 | The hypothetical I'm going through here is that I want
to create a new server role called ReadOnly_Admin.
| | 00:53 | This is because maybe I am troubleshooting
something with an outside vendor or some other admins.
| | 00:59 | And in order to troubleshoot properly, they need to
be able to look at a lot of things on my server.
| | 01:04 | But I don't want them
changing anything on my server.
| | 01:07 | So I'm giving them a series of
permissions that are basically Read Only.
| | 01:11 | I'm calling that sever role ReadOnly_Admin.
| | 01:16 | So I will execute this, come over here to
Server Roles, hit Refresh, and we now see
| | 01:22 | the ReadOnly_Admin.
| | 01:24 | So now, I can go to the logins, and on any of
the logins, I can add them to that server role.
| | 01:31 | So I look at Properties > Server Roles, and my custom
one ReadOnly_Admin is now available in that list.
| | 01:41 | So this is a fairly simple tool Microsoft has
added that has a potential to significantly
| | 01:46 | reduce administrative effort.
| | 01:48 | There aren't any new permissions here.
| | 01:50 | It's not like I can grant or deny access
to anything that I couldn't previously.
| | 01:56 | But it does mean by creating a custom server role that
I could possibly reduce the administrative effort.
| | 02:04 | If I have 5 or 10 people that all need the
exact same access, rather than having to set
| | 02:09 | them up all individually, I could create a
new role, put all of them in that role, and
| | 02:15 | administer them as a group which is
going to be less effort in the long run.
| | Collapse this transcript |
| Understanding auditing| 00:00 | In this section I'd like to talk about some of
the changes to auditing in SQL Server 2012.
| | 00:06 | I have a little sample code for you. I'll
copy and paste over just the top part for
| | 00:12 | now, paste that into Management Studio.
| | 00:16 | We have some new options on
the ON_FAILURE area here.
| | 00:21 | The default is still ON_FAILURE to continue.
| | 00:24 | So in other words if for some reason the
machine is not able to audit the particular action,
| | 00:30 | the machine will continue running, the SQL
Server will continue operating as normal,
| | 00:35 | just nothing will be audited.
| | 00:37 | In previous versions of SQL Server, once it
failed to audit once it would stop trying.
| | 00:43 | That has changed in 2012.
| | 00:46 | Even if it fails once, when it's time to audit
again it will try again, and if it fails then
| | 00:51 | it will try again, over and over.
| | 00:53 | If it ever becomes successful again
the auditing will pick up from there.
| | 00:57 | We also have the option ON_FAILURE to shut
down the server. That hasn't changed since
| | 01:03 | previous versions. That's obviously a very
aggressive way to go, but it is the most secure.
| | 01:08 | It means that if for whatever reason we are
unable to audit something, the entire SQL Server
| | 01:14 | will be shutdown. We'll have
no more functionality.
| | 01:17 | The third option is new in 2012,
it is called FAIL_OPERATION.
| | 01:23 | What this does is the particular operation
that user was trying to do, that should have
| | 01:28 | been logged but can't be logged,
that operation will fail.
| | 01:33 | But any other action, any action that does not cause an
ordered event to occur will continue to run normally.
| | 01:40 | So for example, if you have a database set
up to audit all changes but not audit any
| | 01:46 | read-only access.
| | 01:49 | If auditing would fail for some reason, all
of the write operations would fail, but all
| | 01:54 | of the read operations would
continue to run normally.
| | 01:57 | This is a nice new option, and I think I will be
considering it for many of my production databases.
| | 02:03 | The other new feature I want to talk about is
a new stored procedure, and this is probably
| | 02:08 | more interesting to developers.
| | 02:10 | I have some code here, I'll copy and
paste over into Management Studio.
| | 02:14 | It's a new stored procedure, called sp_audit_write,
and as the name implies it is going to write
| | 02:21 | something to the audit log.
| | 02:23 | So in previous versions of SQL Server we
didn't have this fine level of control.
| | 02:28 | We would set up an audit specification that
will talk about large groups of things we
| | 02:33 | wanted to audit, and if you try hard enough
you can get fairly gray, you look to specific
| | 02:37 | events to audit them or not audit them.
| | 02:40 | But we never had the ability to just say, hey,
here's a phrase, I want to write to the
| | 02:44 | audit log immediately,
and now we do have that.
| | 02:47 | Fairly simple to operate, sp_audit_write. It takes
three parameters and event_id something about success.
| | 02:56 | Typically you're going to do a 1 or 0 here
saying the operation was successful or not
| | 03:01 | successful, and then the last parameter user_defined_
information which is text, and you can write anything you want.
| | 03:08 | So let's talk about a few likely
scenarios where you might use this.
| | 03:13 | If you have an error handling module that
writes all of your errors to an error log,
| | 03:19 | you could add this stored procedure to that
module and that would write all of your errors
| | 03:23 | to the audit log. That could be in addition
to writing them to the error log or instead
| | 03:29 | of writing them to the error log.
| | 03:30 | I think it would also be a good idea to write
things to the error log about major changes,
| | 03:35 | such as we installed a new service
pack or we made a change to the schema.
| | 03:40 | It would also make sense to say we
implemented a new security policy at this time.
| | 03:45 | So for example, if your company suddenly
changes the rules about who can and cannot access
| | 03:51 | certain pieces of data, and you've been
auditing who is and is not accessing those pieces of
| | 03:57 | data, you might also want to make a note in
the audit log that hey, at this point in time
| | 04:02 | the rules changed, so anything before this
point you need to evaluate that against the
| | 04:07 | old set of rules, and anything after this point you
need to evaluate against the new set of rules.
| | 04:14 | All in all sp_audit_write makes auditing more
powerful, more flexible, gives the developer
| | 04:22 | more control, and I always like
things that give developers more control.
| | Collapse this transcript |
|
|
5. High Availability from the Developer's Point of ViewExploring AlwaysOn| 00:00 | SQL Server 2012 introduces a new high
availability feature called AlwaysOn.
| | 00:07 | This course is focused on
development, not administration.
| | 00:09 | So I am not going to get too deep into the
nuts and bolts of setting up or maintaining
| | 00:14 | AlwaysOn, instead I want to focus on you,
the developer, and what you need to do to
| | 00:20 | make your applications work seamlessly
with databases configured with AlwaysOn.
| | 00:25 | First, let's talk a little background about AlwaysOn,
and get a little terminology under our belts.
| | 00:31 | As with most high availability techniques,
an AlwaysOn database runs most of the time
| | 00:37 | on a main server, and that's going
to be called the primary replica.
| | 00:42 | If anything goes wrong with that server, the
database is automatically copied to another
| | 00:47 | server, and that's called a secondary replica.
| | 00:51 | AlwaysOn actually supports up
to four secondary replicas.
| | 00:55 | Now with previous high availability techniques,
when a database would move, it would move
| | 01:00 | one at a time independent
of any other database.
| | 01:04 | This could be a problem because many
applications use more than one database.
| | 01:08 | So you had a problem if an application uses
two databases, one of them fails over, and
| | 01:14 | one of them doesn't.
| | 01:15 | So now you would be accessing data in
two locations; could be a problem.
| | 01:20 | AlwaysOn solves this problem by
creating what it calls Availability Groups.
| | 01:25 | An Availability Group contains multiple
databases all of which will fail over together.
| | 01:31 | So if this is used properly, you can
guarantee that all the databases you need to access
| | 01:36 | from a particular application are up
and running on the same replica.
| | 01:40 | So you want to be prepared to work with your
administrator to identify which databases
| | 01:45 | should be in the same Availability Group.
| | 01:48 | This could also possibly
change some database design.
| | 01:52 | In the past, some developers have forced a lot
of data into the same database, even though
| | 01:58 | that data wasn't necessarily related, they
just were concerned when the failover happened,
| | 02:04 | they wanted everything to move at one time.
| | 02:06 | So we ended up with things in the same database that
really should have been split out amongst two databases.
| | 02:13 | Now that we have the Availability Groups, you
can go ahead and split your large database
| | 02:18 | into two, or three, or four more logically
contained databases, place them all in the
| | 02:23 | same Availability Group, and we can be
assured that the failover will happen together.
| | 02:29 | When connecting to a database configured with
AlwaysOn, you may need to tweak a few things
| | 02:34 | in a typical connection string.
| | 02:36 | Here is an example of a valid
AlwaysOn connection string.
| | 02:40 | Notice that we always use a TCP/IP connection.
| | 02:44 | Named Pipes are not allowed with AlwaysOn.
| | 02:47 | You also notice that you
have to use a DNS name.
| | 02:50 | Using an IP address is not allowed because
the primary replica and secondary replicas
| | 02:56 | will all have different IP addresses.
| | 02:59 | And this connection string may be
potentially connecting to the primary, at other times
| | 03:04 | it may be connecting to the secondary.
| | 03:06 | So we can't put the IP address in there,
we have to put the DNS name instead.
| | 03:11 | Other than that, pretty much the
same as any other connection string.
| | 03:15 | This connection string will
automatically connect first to the primary replica.
| | 03:20 | If the primary fails, it will automatically
redirect you to one of the secondary replicas.
| | 03:26 | So as a developer, you do not need to write
any code to handle the redirect. That will
| | 03:30 | be handled behind-the-scenes for you.
| | 03:32 | However, there could be up to a 60 second pause where
neither the primary, nor the secondaries are available.
| | 03:39 | So your code should have some mechanism that
after a failed connection, it pauses and tries
| | 03:45 | again, and pauses and tries
again for up to 61 seconds.
| | 03:50 | Realistically, I would recommend that you
have that even if you aren't using this high
| | 03:54 | availability technique. We always want to write code
that's robust and can handle a short disconnect.
| | 04:01 | When AlwaysOn is set up, the primary replica is
a Read/Write copy of the data, fully functional,
| | 04:07 | you can both read and write.
| | 04:09 | All the secondary
replicas are Read Only copies.
| | 04:13 | So if you have an application that needs Read Only
access to the data like a reporting application,
| | 04:20 | you can use a special connection string.
| | 04:21 | I am going to have one of
these on the screen for you.
| | 04:25 | It's basically a typical connection string, and at the
end, we added the phrase ApplicationIntent=ReadOnly.
| | 04:33 | By telling the machine you want Read Only
access, you can open up the possibility to
| | 04:38 | being connected to a secondary replica.
| | 04:41 | This will move some of the
workload off of the primary replica.
| | 04:45 | So although, AlwaysOn is primarily a high
availability technique, using this modified
| | 04:51 | connection string, we can also make it into a
load distribution technique where only your
| | 04:57 | Read/Write operations need connect to the primary
and any Read Only application can connect
| | 05:04 | to the secondaries.
| | Collapse this transcript |
| Examining changes to replication| 00:00 | In the previous movie, we talked briefly about
the new AlwaysOn technology and how you could
| | 00:06 | use a connection string to connect an
application to an AlwaysOn replica.
| | 00:12 | In this section, I want to talk
about using AlwaysOn with replication.
| | 00:17 | As I'm sure you know, replication doesn't use
connection strings to connect to machines.
| | 00:22 | So we'll have to use a slightly different
technique to connect to an AlwaysOn group.
| | 00:26 | I've outlined a four
step process here for you.
| | 00:30 | The first thing you want to do is configure
the database publication and subscriptions
| | 00:34 | on your primary replica.
| | 00:36 | So not too surprising; you go to the Primary
Replica, and you configure replication just
| | 00:41 | like you normally would on any other machine.
| | 00:43 | Then you need to go to all of the secondary
replicas and make sure replication is turned on.
| | 00:49 | You don't have to actually configure a publication
nor a subscription, just make sure the services
| | 00:54 | are running, and the
permissions are set properly.
| | 00:57 | Then in step three, we are going to redirect
from the original publisher, and instead point
| | 01:02 | it at the AlwaysOn Availability Group.
| | 01:06 | This will allow replication to continue
on even if the original publisher fails.
| | 01:11 | Even if the primary replica fails, one of
the secondaries could become the publisher,
| | 01:16 | and our replication could carry on.
| | 01:18 | So it's a stored procedure
called sp_redirect_publisher.
| | 01:22 | It takes three parameters: the original publisher,
the database we're using for publishing, and
| | 01:30 | the name of the AlwaysOn group
we would like to redirect to.
| | 01:34 | Once that's done, we have another stored
procedure that we can use to verify the configuration;
| | 01:42 | sp_validate_replica_hosts_as_publishers.
| | 01:45 | When running the stored procedure, you should
get some data as the output, some of the data
| | 01:50 | that is being replicated from whatever
subscription you were concerned with.
| | 01:54 | As long as you get some data, that's a good
sign, as long as you don't get an error, that
| | 01:58 | means things are running properly.
| | 02:00 | The errors we usually get with
this are often permission issues.
| | 02:04 | When I've seen this go wrong, it's typically
a permission issue on the secondary servers.
| | 02:09 | You need to make sure the secondary servers
have the appropriate level of permission to
| | 02:14 | access all of the publishers.
| | Collapse this transcript |
|
|
6. LicensingChoosing an edition| 00:00 | SQL Server 2012 is
available in several editions.
| | 00:05 | The Enterprise edition remains the top shelf
edition containing every possible feature,
| | 00:10 | but also the highest price tag.
| | 00:12 | By researching the features of different editions,
you might be able to find all the functionality
| | 00:17 | you need in a lower-priced version.
| | 00:21 | The Microsoft website is certainly the best
source of information for details on what
| | 00:26 | features are included in what version.
| | 00:29 | The amount of information
presented here can be a bit overwhelming.
| | 00:32 | I would like to walk you through some of it
and point out some of the most popular features
| | 00:37 | and what versions they are available in.
| | 00:39 | Also should make a note that I am giving you
information that is up-to-date as time of
| | 00:45 | this recording, but Microsoft does tend change
these things from time to time, in particular
| | 00:50 | whenever they release a
service pack for SQL Server.
| | 00:53 | They often make minor changes
to what features are available.
| | 00:57 | With SQL Server 2012, Microsoft is primarily
marketing three editions: Enterprise, Business
| | 01:04 | Intelligence and Standard.
| | 01:07 | Enterprise and Standard have been around for a
while but Business Intelligence is new in 2012.
| | 01:13 | We can scroll down here and see some of the
capabilities, some of the features that are
| | 01:17 | included, and as we scroll down we will see a
bullet point in every single box or enterprise.
| | 01:23 | Like I said it contains every single feature,
and we see slightly less features in Business
| | 01:28 | Intelligence and Standard.
| | 01:31 | Notice the features of basic Business
Intelligence: Reporting, Analytics, Data Mining and Basic
| | 01:38 | data integration are included in all three
editions because they have one version named
| | 01:43 | Business Intelligence you might think that
Business Intelligence features aren't included
| | 01:47 | in the other versions.
That's not exactly correct.
| | 01:51 | The basic Business Intelligence, the basic
reporting, the basic analytic server is included
| | 01:56 | in all three versions.
| | 01:57 | What Business Intelligence gives you, is a
few advanced features that are not included
| | 02:02 | in Standard such as Power View and PowerPivot.
| | 02:06 | PowerPivot a very popular feature
particularly among SharePoint developers.
| | 02:12 | So that may be something that would push
you to buy the Business Intelligence server.
| | 02:16 | Also notice there's nothing in the Business
Intelligence server that isn't in the Enterprise,
| | 02:21 | so if you have access to Enterprise, there
is no reason to downgrade to the Business
| | 02:25 | Intelligence version.
| | 02:27 | Looking at some more detailed information, in
particular information about the hardware,
| | 02:32 | we will notice that the Enterprise version
always uses the maximum CPU and maximum RAM
| | 02:39 | supported by the operating system.
| | 02:42 | And on the other end of the spectrum, Standard
has some limitations on the number of CPUs
| | 02:48 | we can use and the amount of RAM we can use.
| | 02:52 | Currently that set to 16 cores and 64 GB.
| | 02:57 | Now you might be thinking, hey right
now that's a pretty beefy machine!
| | 03:00 | 16 core, 16 GB of RAM is a pretty
beefy machine by today's standards.
| | 03:06 | But remember you may be putting SQL Server
in production for 5, 10, 15 years. Is that
| | 03:12 | still going to be a beefy
machine 15 years from now?
| | 03:15 | I can't say for sure, how much RAM
will be common 15 years from now.
| | 03:19 | I'm betting it's a lot higher than 64 GB.
| | 03:21 | So those limitations are more of a
problem in the future than they are right now.
| | 03:28 | Notice Business Intelligence has some
limitations when you're running the database engine, but
| | 03:33 | it ramps up to operating system maximum on the
analysis services and reporting services piece.
| | 03:41 | The line on maximum relational database size
is also interesting, 524 petabytes. That is
| | 03:47 | very, very large.
| | 03:49 | A petabyte is 1000TB, a terabyte is 1000 GB, so
a petabyte is basically a million gigabytes.
| | 03:58 | I have yet to see a database that is even 1
petabyte, so 524 petabytes would seem to
| | 04:05 | be an overwhelming amount of information.
| | 04:08 | Again it's hard to say that 10 or 15 years
from now whether or not that'll still be a
| | 04:12 | reasonable limitation, but also you notice
Enterprise, BI and Standard all have the exact
| | 04:19 | same limitations, so it's not something to
consider one way or the other when purchasing.
| | 04:24 | We also have a line here
for the licensing model.
| | 04:27 | Notice that the Enterprise edition is only per
processor or per core, the Business Intelligence
| | 04:33 | edition is only available with licensing
model of server plus client access license.
| | 04:39 | But Standard edition is
available with either licensing model.
| | 04:44 | So when choosing edition, you also have to
consider the different licensing models and
| | 04:48 | we will get into those in
detail over the next few movies.
| | 04:53 |
| | Collapse this transcript |
| Licensing SQL Server 2012 in a non-virtual environment| 00:00 | SQL Server 2012 is available
with two licensing models.
| | 00:05 | We can license per processor core or we can license
as the Server plus a Client Access License or CAL.
| | 00:14 | First let's talk about Server+CAL because
it's a little simpler and it really hasn't
| | 00:19 | changed much since
previous versions of SQL Server.
| | 00:22 | In order to do this first you would license
a physical server, so whatever machine you
| | 00:26 | want to run SQL Server on, doesn't matter
how powerful or how weak of a box it is, and
| | 00:32 | then additionally you need a Client Access
License either for each person that accesses
| | 00:38 | or for each machine that accesses.
| | 00:42 | Now most companies have one human being
working at one computer, so you would end up paying
| | 00:47 | the same amount whether you
did per person or per machine.
| | 00:51 | But let's say your business works 3 shifts
during the day and all of those people share
| | 00:55 | a machine, well then it might
be cheaper to go per machine.
| | 00:59 | Really this hasn't changed since
previous versions of SQL Server.
| | 01:03 | Just remember licenses don't
roll forward, they only roll back.
| | 01:07 | So in other words if you have a Client Access
License for SQL Server 2008 it is not valid
| | 01:13 | for SQL Server 2012.
| | 01:16 | But if you buy a Client Access License for
SQL Server 2012, it is valid for 2008, 2005,
| | 01:24 | 2000; any previous version.
| | 01:28 | Now let's talk about the per core and this
is per processor core. The basic mindset is
| | 01:34 | similar to previous versions of SQL Server,
but the exact calculation has become a little
| | 01:39 | more complicated.
| | 01:41 | First of all, we used to license physical
processors, in other words a physical device you plug
| | 01:47 | into a mother board.
| | 01:48 | Now instead we are licensing per core and
pretty much every processor out these days
| | 01:55 | contains multiple cores, usually 2,
4, 6 or 8 cores per processor.
| | 02:01 | If you don't know how many cores are in your
processor, the manufacturer can certainly tell you.
| | 02:06 | And then additionally many machines have
more than one physical processor, so you could
| | 02:10 | likely end up with a scenario where you have
two physical processors, each one of them
| | 02:16 | has four cores, so you have a total of eight
cores in that machine. You have to buy a license
| | 02:22 | for all eight cores. You can't play any
tricks to try to reduce the number of cores.
| | 02:27 | And I'll demonstrate one of those things.
| | 02:31 | In Management Studio, you can right-click on
the name of your Server, go to Properties
| | 02:37 | and there is an option for Processors.
| | 02:42 | My machine shows me that
I have four processors.
| | 02:45 | In some versions of SQL Server you can
turn on or turn off certain processors.
| | 02:51 | This does not affect licensing, because there
are four logical processors in this machine,
| | 02:55 | in other words four cores. I still have
to buy four licenses for the four cores.
| | 03:02 | Even if I would go in here and turn off to
the cores, I still have the license every
| | 03:06 | core that is physically in that machine.
| | 03:09 | So once we know how many cores are in our
machine we're not 100% finished. We still
| | 03:14 | have to multiply that by a core factor and
Microsoft publishes a list of core factors.
| | 03:22 | If you follow that link, it will take you to
a download that shows a Core Factor table.
| | 03:31 | I'll scroll down here.
| | 03:34 | And we see that certainly for most processors
the Core Factor is just 1, so you multiply
| | 03:39 | times one. It doesn't change anything.
| | 03:41 | But for certain AMD Processors with 6
or more cores, we have a factor of 0.75.
| | 03:47 | So you would figure out how many cores you
have and then multiply times 0.75 and that's
| | 03:55 | the number of licenses you need.
| | 03:57 | So let's work through a
couple of examples on that.
| | 04:01 | Let's say I had 6 cores and their AMD processors,
so I multiple times 0.75, I come up with 4.5.
| | 04:11 | Now it's impossible to buy half a license, it's
also impossible to by less than two licenses.
| | 04:17 | SQL Server core licenses
are only sold in two packs.
| | 04:22 | So in this case you would still have to buy 6
licenses. Didn't actually save me any money.
| | 04:27 | But if I had an 8 core machine, I could multiply
times 0.75 and come up with 6, and then I only
| | 04:35 | have to buy 6 licenses
even though I have 8 cores.
| | 04:39 | So again, our process is to determine the
number of cores in the machine, multiply time
| | 04:44 | a Core Factor, come up with a total number
of licenses you have to buy, but remember
| | 04:50 | that you could only buy
licenses in packs of two.
| | 04:53 | Also we talked about the two different
models, the per core and the Server+CAL.
| | 04:59 | The Standard Edition is the only one that allows us
to choose which of those licensing models we use.
| | 05:05 | For Enterprise you have to do per core, for
the Business Intelligence edition you have
| | 05:10 | to do Server+CAL, but if you buy the Standard
Edition you can choose to either do per core
| | 05:16 | or Server+CAL.
| | Collapse this transcript |
| Licensing SQL Server 2012 in a virtual environment| 00:00 | In SQL Server 2012, licensing on virtual
machines can be slightly different than licensing on
| | 00:06 | physical machines.
| | 00:08 | If you are running the Server+CAL method,
it doesn't change things too much.
| | 00:13 | You still need one server license for each
SQL Server you run, and users still need one
| | 00:18 | CAL, regardless of whether they access
servers that are physical, or they access servers
| | 00:23 | that are virtual, but when we license per
processor core, it becomes little more complex
| | 00:28 | in a virtual environment.
| | 00:30 | Microsoft uses the term v-core
to discuss a virtual core.
| | 00:35 | One v-core license is required for each
hardware thread used by the virtual machine.
| | 00:41 | Microsoft defines a hardware thread as either
one physical core or one hyper-thread on a
| | 00:48 | physical processor.
| | 00:49 | Most hardware virtualization systems have
a 1:1 ratio of cores to hardware threads.
| | 00:56 | Meaning, you will need to buy one v-core license
for each virtual core presented to the machine.
| | 01:02 | Some virtualization systems allow two hardware
threads per core, and that would double the
| | 01:07 | number of licenses you need.
| | 01:10 | You only need to license the cores
presented to the virtual machine.
| | 01:13 | For example, if you have 24 cores in your
physical machine but the virtual SQL Server
| | 01:19 | is only allocated six of these cores, then
you only need to buy six v-core licenses.
| | 01:25 | Now remember, some virtualization systems
can dynamically add or remove CPU power at
| | 01:31 | runtime, and you need to have the
appropriate licenses in place before you increase the
| | 01:36 | number of v-cores
presented to a virtual server.
| | 01:40 | Last but not least, Microsoft requires that
every virtualized SQL Server require a minimum
| | 01:46 | of 4 v-core licenses.
| | 01:49 | Microsoft has given us one more option for
licensing SQL Server, and I think it is a
| | 01:53 | particularly good option
especially for large companies.
| | 01:57 | It is called licensing for
maximum virtualization.
| | 02:00 | This licensing model only applies to the
Enterprise Edition of SQL Server 2012.
| | 02:06 | It allows you to run unlimited number of
virtual SQL servers on one physical machine as long
| | 02:12 | as you buy the license for every
physical core on that machine.
| | 02:16 | So you would use the techniques we talked
about in a previous movie to determine how
| | 02:20 | many core licenses you need
for the physical machine.
| | 02:23 | Go ahead and buy a license for
every physical core in that machine.
| | 02:28 | And once you've done that, you can turn up a
new virtual SQL Server anytime, and don't
| | 02:33 | have to worry about purchasing new licenses.
| | 02:35 | This could be a significant savings,
particularly if you would like to run a very large number
| | 02:40 | of virtual servers on one very powerful box.
| | Collapse this transcript |
|
|
7. Additional ChangesIntroducing new conversion, logical, and string functions| 00:00 | SQL Server 2012 gives us 14 new functions.
| | 00:05 | And I know developers are always
excited to have new toys to play with.
| | 00:09 | Some of these are really useful, some of
these more serve a utility purpose that probably
| | 00:13 | wouldn't be used real often.
| | 00:16 | Let's go and go through some of these.
| | 00:18 | I have some code staged for
you in your exercise files.
| | 00:22 | I'm going to copy these over on at a
time, paste into Management Studio.
| | 00:27 | So our first new function is called PARSE
and this function is used for parsing dates,
| | 00:35 | times and currency options in
the different local culture.
| | 00:39 | As I'm sure you're aware some countries use
different symbols for currency. Some countries
| | 00:45 | use the comma and the period different as
delimiters between the whole numbers and the
| | 00:49 | fractional numbers, and certainly dates
can be formatted a wide variety of ways.
| | 00:55 | The Parse Function allows us to convert from
the common formatting of one culture to the
| | 01:00 | common formatting of another culture.
| | 01:02 | Or in this case, the example I have on the
screen, removing the formatting, just converting
| | 01:07 | to a SQL Datatype.
| | 01:09 | So we'll execute this, and we see simply that
it took off the currency symbol and changed
| | 01:14 | the comma to a period and
returned to us a common SQL Datatype.
| | 01:20 | Very useful function, if you working with
international applications that need to accept
| | 01:24 | input from a lot of different
people possibly in different countries.
| | 01:28 | You may find this quite useful.
| | 01:30 | However, if all of the applications you develop,
run in the same country in which they're developed,
| | 01:35 | and you only have customers from that
country then this may not be that useful for you.
| | 01:40 | The next two we have our
TRY_CONVERT and TRY_PARSE.
| | 01:44 | As the name implies TRY_CONVERT works
very similar to the way CONVERT works.
| | 01:50 | It just handles errors a little differently.
| | 01:53 | So I'll copy and paste this code over.
| | 01:55 | Basically, I'm trying to convert
the word hello into an integer.
| | 02:00 | Obviously, that is not going to be successful.
| | 02:03 | When I Execute this, it returns NULL.
| | 02:06 | If we had used the older CONVERT function
without the word try in front of it, this
| | 02:10 | would have returned an error instead.
| | 02:12 | It says conversion failed.
| | 02:14 | When the conversion is successful either
CONVERT or TRY_CONVERT do exactly the same thing.
| | 02:20 | They only handle errors differently.
| | 02:23 | TRY_CONVERT really avoid sending errors,
it returns NULL, instead of an error.
| | 02:29 | And TRY_PARSE is the same thing.
| | 02:32 | It functions exactly the same way
as PARSE when it works properly.
| | 02:35 | When there's an error, it will
return NULL rather than error.
| | 02:39 | Moving on to our logical functions; the first one
is called to CHOOSE. Copy and paste that over.
| | 02:46 | CHOOSE allows us to look at a list of items
and select one based on an index we pass as
| | 02:51 | the first parameter.
| | 02:53 | So here to my CHOOSE Function the first thing
I passed was the numeral 2, and then I passed
| | 02:58 | a list of three items.
| | 03:00 | What's going to be returned is in fact that
second item in the list, because I passed that 2.
| | 03:06 | This is somewhat similar
to working with arrays.
| | 03:09 | Except on arrays we
always start counting on 0.
| | 03:11 | And here with the CHOOSE, we start counting
on 1, so the first element is a 1 not a 0.
| | 03:19 | If you are used to working with arrays in
other programming languages, you might find
| | 03:23 | this a little awkward.
| | 03:24 | And I agree it's not as elegant as full array
support, but you know we work with what we have.
| | 03:30 | T-SQL does not yet have full-on arrays.
| | 03:33 | So we'll work with this CHOOSE structure.
| | 03:35 | Hopefully, in the future we'll get
some more powerful array functionality.
| | 03:40 | The next one I'm honestly not quite sure how to
pronounce, it's IIF spelled with two I's, so it's I-IF.
| | 03:47 | And that does function similar to a typical If
statement, but whereas an If statement typically
| | 03:52 | only allows us to look at what
to do if the value is true.
| | 03:57 | The one with two I's allows us to
say what happens if it's false.
| | 04:02 | So when our statement is true, whatever is
the next parameter is what will be returned,
| | 04:08 | and whenever our statement is false,
whatever is the third parameter will be returned.
| | 04:14 | This isn't real common in
other programming languages.
| | 04:16 | However, it is very
common in Microsoft Access.
| | 04:20 | And because people convert a lot of apps from Microsoft
Access to SQL Server, this is a logical thing to do.
| | 04:26 | If you do in fact convert from Access to
SQL Server, I think you'll use this often.
| | 04:31 | If you're not doing that conversion, I'm
not sure I see a whole lot of use for this.
| | 04:36 | Next with our string functions, we have
CONCAT which is an abbreviation for concatenate.
| | 04:44 | And as the name implies, it takes a series of
parameters and returns them as a concatenated string.
| | 04:51 | Obviously, there are other tools and techniques
for doing this inside of SQL Server, but many
| | 04:56 | of those tools and techniques stumble when
you pass parameters that are not strings,
| | 05:02 | CONCAT handles that well.
| | 05:03 | So you'll see I'll pass it a number, and it
will automatically convert that to a string.
| | 05:09 | And also very surprisingly it handles nulls,
so I can even pass the NULL value to it, and
| | 05:15 | it basically ignores the NULL value.
| | 05:19 | And just continues to right on after it.
| | 05:22 | So this is unique amongst our
different string concatenate techniques.
| | 05:27 | All other string concatenation
techniques would stumble on that null.
| | 05:31 | And the last one I want to
demonstrate is the FORMAT.
| | 05:34 | And FORMAT has some
similarities to the first one we did.
| | 05:38 | It again works with formatting dates,
times and currencies into different cultures.
| | 05:45 | The first function we did call PARSE was
assuming that the input was already formatted in some
| | 05:50 | culturally specific way and then
we converted it to a SQL Datatype.
| | 05:55 | This is the exact opposite.
| | 05:57 | We start off with a SQL Datatype and we
converted to some culturally specific way.
| | 06:03 | So I'm going to take the date of October 16th
and convert it into German, Great Britain,
| | 06:09 | and simplified Chinese, showing that there are
several different ways to express that date.
| | 06:16 | Again for some of you very, very useful, if you
work with a lot of international applications,
| | 06:21 | for other people probably not so useful.
| | 06:24 | We also have a big group of date and
time functions, new in SQL Server 2012.
| | 06:29 | I'm not going to do a demo on these.
| | 06:32 | There is good utility here for some of these.
| | 06:35 | They're probably not going to be used nearly as
often as the previous seven, but just stored
| | 06:40 | somewhere is on the back of your mind
that there are some new functions.
| | 06:43 | Most of these, as the names imply, allow us
to create a date from the individual parts.
| | 06:49 | So you would pass parameters individually for
month, year, day and then possibly the time.
| | 06:57 | And that could return either a DATE,
a DATETIME, a DATETIME2, et cetera, et cetera.
| | 07:03 | So file away in the back of your mind that
these exist and you can come back and use
| | 07:07 | them when it's necessary.
| | Collapse this transcript |
| Understanding changes to hardware and operating system support| 00:00 | Many of you may remember that from 2001 to
2009 Intel sold a family of processors called
| | 00:07 | Itanium processors.
| | 00:09 | You may also remember that SQL Server 2005 and
2008 were sold in special editions specifically
| | 00:17 | for the Itanium processor.
| | 00:20 | That is no longer the case. With SQL Server
2012 we do not have a special version for
| | 00:26 | Itanium processors, but you still can install
SQL Server on a machine running an Itanium
| | 00:32 | processor. Just use any of the 64-bit
versions of SQL Server.
| | 00:38 | New in SQL Server 2012 we have the option
to install SQL Server on a reduced version
| | 00:46 | of the operating system called Server Core.
| | 00:49 | Server Core is something Microsoft came out with,
with Windows server 2008. It is a scaled-down
| | 00:54 | version of the operating system. It has
removed pretty much all graphical elements and it
| | 01:00 | just a command line interface. It runs significantly
less services than a full-blown version of Windows.
| | 01:07 | So if you're running Windows Server 2008 or to
Service Pack 1 or Windows Server 2012 Server
| | 01:15 | Core, you can install any 64-bit
version of SQL Server on those.
| | 01:20 | Some of the advantages of that Server Core
only installs the minimum required services
| | 01:26 | for a manageable server. That means there will
be less time and energy to manage the server.
| | 01:32 | For example, when patches come out for
Windows servers, many of those patches do not apply
| | 01:38 | to sever core. Server core simply isn't
running the service that that patch applies to.
| | 01:44 | And less patching needs fewer reboots
meaning your SQL Server is online.
| | 01:49 | Server Core is also more secure, because there
is less things to attack. Less services running
| | 01:55 | means less attack surface.
| | 01:57 | Less services running also means more memory
of available to applications, available to
| | 02:02 | your SQL Server, and available to any
custom application you may want to run.
| | 02:08 | Not everything in a SQL Server is
available once you install on Server Core.
| | 02:13 | We'll see that the majority of the most important
things are certainly there. We get the database
| | 02:18 | engine, replication, full text search,
analysis services and integration services will all
| | 02:25 | work perfectly fine if you
install on Server Core.
| | 02:29 | However, reporting services will not work.
It's just simply not an option at this time.
| | 02:34 | Probably will work at some point in the future,
but right now we cannot install reporting
| | 02:38 | services on Server Core.
| | 02:41 | You also don't get the SQL Server books
online. Probably not a big deal because you could
| | 02:45 | look at that someplace else.
| | 02:47 | And the Management tools, so for example
Management Studio will not run on the machine that's
| | 02:53 | formatted Sever Core, but you could run
Management Studio on some other machine and still connect
| | 03:00 | to your Server Core machine.
| | 03:01 | So you will have to do remote management which
realistically on a production server you're
| | 03:07 | likely doing remote management anyway.
| | 03:10 | So we're really not giving up much functionality.
Not having reporting services will be a deal
| | 03:15 | breaker for some people, but other than that
you're not really giving up any functionality
| | 03:20 | and the advantages of several core are
significant. It's something everyone should consider.
| | Collapse this transcript |
| Exploring new types of indexes and new options on indexes| 00:00 | In SQL Server 2012, Microsoft has introduced a
new type of index called the columnstore index.
| | 00:07 | This is somewhat similar to traditional
indexes, in that, the point of it is to increase the
| | 00:13 | performance of queries. The mechanics
behind the scenes work a little different.
| | 00:17 | A columnstore index groups and stores data
for each column first and then worries about
| | 00:24 | the rows, whereas a traditional index
grouped and stored data by the row first, and then
| | 00:30 | worried about the columns.
| | 00:32 | You'll see the biggest performance advantage on
a columnstore index when you have a particular
| | 00:37 | situation, a particular pattern of data, and
this happens when a column has a small set
| | 00:43 | of distinct values that
are repeated over and over.
| | 00:46 | So for example, think about a database table
that tracks the sales of a product across
| | 00:51 | 50 retail locations.
| | 00:53 | There might be millions of rows in that table,
but the location ID column is always going
| | 00:59 | to contain one of 50 pieces of data.
| | 01:02 | So it's a relatively small number of
distinct values that are repeated over and over.
| | 01:08 | Furthermore, it's likely that you are going to run
reports grouped by that column, grouped by location.
| | 01:14 | In this situation you would see a big
advantage from one of the new columnstore indexes.
| | 01:21 | I would recommend as you're getting used of
columnstore indexes with some queries to try
| | 01:26 | it with a traditional index, and then remove
that index and create a columnstore index,
| | 01:32 | run the query again and see if
you get better performance.
| | 01:37 | Overtime as you do this, you hopefully start
to develop some instincts on which queries
| | 01:42 | would benefit most from a traditional index
and which queries would benefit most from
| | 01:47 | a columnstore index.
| | 01:49 | In SQL Server 2012, Microsoft has given us
the option for indexes to remain online while
| | 01:55 | you rebuild them, also while you perform other
alter statements on them, like adding a column
| | 02:01 | or removing a column.
| | 02:03 | The syntax for this is pretty simple. You
can take any ALTER INDEX statement and just
| | 02:08 | add at the end WITH (ONLINE = ON) and that
index will remain online and usable throughout
| | 02:15 | the alter process.
| | 02:17 | This is available in the Enterprise
Edition only, won't work on any other edition.
| | 02:22 | What goes on in the background when this
happens is a complex system of locking the index,
| | 02:27 | copying parts of the index over slowly and
re-creating that index, all while leaving
| | 02:33 | it online for any query to continue to use it.
| | 02:36 | This can be taxing on the system. It can use
a lot of processor, a lot of memory, and it
| | 02:41 | can hit your disk subsystem pretty hard.
| | 02:44 | So if you have a traditional maintenance window,
where you take indexes offline and perform
| | 02:49 | your maintenance offline, I would
recommend continuing to do that.
| | 02:53 | But if you have a situation where you really
need to be online 24/7 and you need all of
| | 02:58 | your indexes to be online 24/7, you do have
the option to do that, just be prepared to
| | 03:05 | shell out some big dollars for Enterprise
Edition SQL Server and you'll probably need
| | 03:09 | some beefier hardware.
| | 03:11 | So not too uncommon that wanting to be
online 24/7 is possible, it's just expensive.
| | 03:17 | SQL Server 2012 has made some
changes to our spatial indexes.
| | 03:22 | So just a quick review, spatial indexes were
introduced in SQL Server 2008 and they came
| | 03:28 | in two flavors: geometry and geography.
| | 03:32 | Geometry assumes a flat two-
dimensional plane like a black board.
| | 03:37 | Geography assumes a curved two-dimensional
surface such as the surface of the earth.
| | 03:43 | Pretty much all of the changes in 2012 have
come on the geography type. Microsoft has
| | 03:49 | made that more robust.
| | 03:51 | One of the limitations in SQL Server 2008 was you
could only work in one hemisphere at a time.
| | 03:58 | So for example you could draw a line from one
point in the Northern Hemisphere to another
| | 04:03 | point in the Northern Hemisphere, and you
could draw a line from one point in the Southern
| | 04:07 | Hemisphere to another point in the Southern
Hemisphere, but you could not draw a line
| | 04:12 | from a point in the Northern Hemisphere
to a point in the Southern Hemisphere.
| | 04:16 | 2012 removes that limitation and we can now
work with what they call the full globe.
| | 04:22 | So you can draw a line from any point. It
doesn't matter what hemisphere it's in.
| | 04:28 | Along with that, Microsoft has introduced three
new subtypes of circular arcs. They are called
| | 04:34 | circular string, compound
curve and curved polygon.
| | 04:39 | All of these things are structures that used to
exist in geometry and now have been modified
| | 04:45 | slightly to exist in the geography mindset
where they do a better job of hugging the
| | 04:51 | curvature of the sphere.
| | 04:53 | If you work with spatial indexes, I
encourage you to research these more.
| | 04:58 | SQL Server 2012 looks like it's continuing
more after the specialty market of GIS systems,
| | 05:04 | which is something that many developers
won't ever get to work with, but if you do have
| | 05:07 | the chance it can be very exciting.
| | Collapse this transcript |
| Understanding new administrative options| 00:00 | In this section, I would like to talk
briefly about administering SQL Server 2012.
| | 00:06 | This course is primarily for developers, but
let's be perfectly honest all developers end
| | 00:11 | up doing at least a little bit of administration.
Some of us end up doing a lot of administration.
| | 00:17 | One of the tools that's changed slightly,
is the sqlcmd.exe, the SQL command.
| | 00:22 | One of these changes is very exciting to
me and I think a very positive thing.
| | 00:26 | There was a problem with previous versions
of SQL command where it would take a single
| | 00:31 | quote and replace it with an ampersand APOS, in
other words, they thought it was an apostrophe
| | 00:37 | instead of a quote.
| | 00:38 | They have removed that or as I would say
they've fixed that bug and now a single quote stays
| | 00:44 | as a single quote. Definitely a
positive change in my opinion.
| | 00:48 | The other two changes here are very minor,
just changing how the system handles situations
| | 00:54 | where you don't put a value
after the decimal point.
| | 00:58 | Does it assume zero or does it
assume .000, et cetera, et cetera.
| | 01:04 | How many zeros does it assume? It's
a very minor change in my opinion.
| | 01:09 | A few changes with PowerShell, first PowerShell
is no longer installed with SQL Server, instead
| | 01:15 | it is a prerequisite.
| | 01:17 | So you have to have PowerShell 2.0 on a
machine before you can install SQL Server 2012.
| | 01:23 | Realistically most Microsoft operating systems
will already have PowerShell 2.0, but if you
| | 01:29 | don't have it, it's a free
download from Microsoft.
| | 01:32 | Also inside of Management Studio, when you
launch PowerShell, you will be launching the
| | 01:37 | full version of PowerShell 2.0.
| | 01:39 | Let me show you what I mean.
| | 01:41 | In Management Studio, there are lots of places
you can right-click, and one of the options
| | 01:46 | that comes up is Start PowerShell.
| | 01:49 | We see that on several of the different
containers we can click on, Start PowerShell.
| | 01:56 | It used to be, in previous versions of SQL
Server, when you would click on Start PowerShell,
| | 02:02 | it would actually open what's known as a mini-
shell, which is a reduced functionality PowerShell.
| | 02:08 | Basically you could only run
commands specific to SQL Server.
| | 02:12 | Now they've changed that and when we start this, it
is a full version of PowerShell, PowerShell 2.0.
| | 02:18 | So not only can you run any SQL command here,
you can run any other PowerShell command if
| | 02:24 | you would like to administer your
operating system at the same time.
| | 02:28 | A few changes to Dynamic Management Views
and functions; one of the more common Dynamic
| | 02:33 | Management Views the execute query
stats, they've added four columns.
| | 02:38 | A lot of people use exec_query_stats to look
for slow running queries and troubleshoot
| | 02:44 | slow running queries.
| | 02:45 | The four new columns will give you
additional information for your troubleshooting.
| | 02:49 | I think they'll all be useful.
| | 02:51 | They are total rows, minimum rows,
maximum rows, and last rows.
| | 02:56 | We also have a handful of new functions.
| | 02:58 | I am going to talk about three of the
ones that I feel are the most useful.
| | 03:01 | I am going to have a little demo setup
on these, in your exercise files.
| | 03:06 | We will run each of these one at a time,
copy and paste over into Management Studio.
| | 03:14 | So this first one dm_os_volume_stats, as the
name implies it's telling me about the volume
| | 03:24 | to which my database is running. In other
words, information about the dish drive.
| | 03:29 | So this tells me I'm running on the C drive,
and as I scroll over, it tells me how much
| | 03:35 | free space is on the C drive, whether or not
the C drive supports compression, and whether
| | 03:41 | or not it is read-only.
| | 03:43 | Obviously all of this information was available
through other sources, but this is the first
| | 03:48 | time we have it available in a
quick and easy table format.
| | 03:53 | The next Dynamic Management View I would like
to talk about is called dm_server_services.
| | 04:00 | If we execute this, you can see that it tells
me information about the SQL services that
| | 04:06 | are running on this machine.
| | 04:07 | So I'm running SQL Server, SQL
Server Agent and SQL Full-text Filter.
| | 04:13 | It shows me the startup_type and whether or not they
are running right now, and some other information.
| | 04:20 | Again all of this would have been available
through other places. You could have gone to Control
| | 04:24 | Panels and found out all this information, but it's
nice to have it available inside of SQL Server.
| | 04:30 | A lot of people write a little bit of SQL
code that will document their current solution
| | 04:34 | or their current environment.
| | 04:36 | This is a Dynamic Management View that's going
to make that documentation a little easier.
| | 04:40 | You can just run this query and drop that
into a text file, and you have a nice neat
| | 04:45 | documentation on what services
are running on your machine.
| | 04:49 | Next, we will talk about dm_server_registry.
| | 04:55 | This is going to show me all of the registry keys,
SQL Server is reading for its configuration,
| | 05:01 | which is a lot of information. It shows me
the registry key and as I scroll over the
| | 05:06 | value of that key, and
this returned about 63 rows.
| | 05:11 | I could've gotten this information directly
from the registry, but obviously there is
| | 05:15 | a lot more than 63 rows in the registry.
| | 05:18 | Finding all this information in the registry
would have involved looking through lots and
| | 05:22 | lots of other stuff, so having all this
information in a clear, concise location without all of
| | 05:30 | the other stuff from the
registry is very valuable.
| | 05:32 | I personally am working on some projects right
now where I'm trying to recreate a SQL Server
| | 05:38 | in one data center, exactly the
same as it is in another data center.
| | 05:42 | Unfortunately I'm not running SQL Server 2012.
| | 05:46 | If I was, I would certainly be using this
Dynamic Management View. What I would do is
| | 05:51 | run it on both machines and
then compare the results.
| | 05:53 | At any place that is different would be a
cause to go and investigate, and see if these
| | 05:59 | two servers actually do in fact
have a different configuration.
| | 06:03 | So this is a great way to first of all document
your current configuration and secondly compare
| | 06:08 | the configuration of
two separate SQL servers.
| | 06:12 |
| | Collapse this transcript |
| Accommodating changes to database structure| 00:00 | Microsoft SQL Server 2012 gives us the ability to
create multiple file groups for our file stream data.
| | 00:08 | So I'm going to right-click on this database
and go to Properties, and then I'll select
| | 00:13 | Filegroups and at the bottom we
have the File Stream section.
| | 00:18 | I currently have one file group. I can add another,
and I could add another, and I could another.
| | 00:24 | You can have as many as you want.
| | 00:26 | Previous versions of SQL
Server limited you to only one.
| | 00:30 | So the question is, why
might you want to do this?
| | 00:32 | Well, it's the same reasons that we
do this with non-file stream data.
| | 00:37 | We can create multiple file groups
typically for one of two reasons.
| | 00:41 | One would be performance. If we create files
on different disk drives we can often see
| | 00:46 | a performance increase.
| | 00:48 | So remember creating multiple files on the
same disk drive are typically not going to
| | 00:53 | increase performance, but creating multiple
files on different disk drives could in fact
| | 00:58 | increase performance.
| | 01:00 | The other thing is administration. Remember
some of your administrative tasks are performed
| | 01:04 | at the file group level,
such as backup and restore.
| | 01:07 | So if you had some file stream data that
changes on a daily basis and needs to be backed up
| | 01:12 | on a daily basis you could put
that in one file stream group.
| | 01:16 | And let's say you have other file stream data that
only changes maybe once a month or once a year.
| | 01:23 | Therefore it doesn't need to be backed up
nearly as often, you could put that in a different
| | 01:27 | file group with a different backup schedule.
| | 01:30 | We also need to talk briefly
about SQL Server 2012 endpoints.
| | 01:35 | We still have the TSQL, SERVICE_BROKER,
and DATABASE_MIRRORING endpoints.
| | 01:40 | However, Microsoft has removed the native XML
Web services endpoint. This feature simply
| | 01:48 | no longer exists in SQL Server 2012.
| | 01:51 | Kind of surprising that they removed it so
quickly. Usually a feature is moved to the
| | 01:55 | deprecated list and then in some future version
it is removed. In this one they kind of removed
| | 02:02 | this one rather quickly.
| | 02:03 | To be honest this is a feature that not a
whole lot of people used. It was never adopted
| | 02:07 | very well by the user community.
| | 02:09 | Some of the other endpoints are used very commonly,
particularly Database Mirroring is very common,
| | 02:15 | but the Native XML Web Service endpoint was not
commonly used and therefore has been removed.
| | 02:21 | There really isn't a work around, so if you
have a SQL Server that utilizes this feature,
| | 02:27 | you really can't upgrade to 2012. You
need to stay at 2008 or 2004 now.
| | 02:33 | Contact Microsoft, see if there is an
upgrade path in the future, but for right now you
| | 02:39 | need to not upgrade.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | This concludes our course,
SQL Server 2012 New Features.
| | 00:04 | I certainly hope you found it informative.
| | 00:06 | I'd like to leave you with a few thoughts,
some resources you can utilize to further
| | 00:11 | your knowledge of SQL Server 2012.
| | 00:14 | The first one is the Microsoft Documentation.
| | 00:16 | I find SQL Server to be the best
documented product Microsoft puts out.
| | 00:21 | In particular they have a publication called
the SQL Server 'Books Online' that is excellent.
| | 00:26 | It contains quite a wealth of information, lots
of technical information, lots of walk-throughs,
| | 00:32 | lots of introductory information.
| | 00:34 | It's available through the Microsoft website
or you may have installed it, when you install
| | 00:39 | SQL Server you have the option
to install Books Online locally.
| | 00:43 | I typically do that in case I'm
ever offline away from the Internet.
| | 00:48 | For example, on an airplane or something, I'd still
like to have that documentation there with me.
| | 00:53 | However, when I have it installed locally
and I'm off-line, I'm not sure SQL Server
| | 00:58 | Books Online is the right name for it.
| | 01:01 | Maybe it should be called
SQL Server Books Offline.
| | 01:03 | Anyway it's a very good resource and I encourage you to
probably go there first for most of your questions.
| | 01:10 | If you're looking for a third-party
point of view I like sqlservercentral.com.
| | 01:16 | It contains a wealth of information about SQL
Server and other issues that affect database
| | 01:21 | administrators and database developers, such as
Windows issues, security issues, and common regulations.
| | 01:29 | It has an active user community, so if you
have questions you can post them, typically
| | 01:34 | get a response fairly quickly.
| | 01:36 | It does require you to sign
up but the sign up is free.
| | 01:38 | I'll also encourage you to look into another course
here on the lynda.com online training library.
| | 01:45 | SQL Server: Triggers, Stored
Procedures and Functions.
| | 01:49 | It contains the information on those topics on
both SQL Server 2008 and the new SQL Server
| | 01:54 | 2012 that I'm sure you're excited about.
| | 01:57 | Again, thank you for your time
and I hope you enjoyed the course.
| | Collapse this transcript |
|
|
