Installing SQL Server 2012.
So in this Nugget, we're going to walk through an
installation of SQL Server 2012.
We'll start with some pre-installation tasks.
We'll take a look at some post-installation tasks.
And I'll show you some things to look out for along the way
during the installation.
So checking out the Nugget overview, these are also the
exam objectives for this Nugget 470-462.
We're going to start with the pre-installation task of
configuring the operating system disk.
Just some little things we need to make sure are in order
before we install SQL Server.
I mean things like ensuring we have enough hard disk space,
ensuring that the operating system is updated with the
latest service packs, things of that nature.
Then we'll head over to the virtual Nugget lab and install
SQL Server 2012.
So we're going to spend a lot of time in the virtual Nugget
lab throughout this series.
The perfect place to start is getting SQL Server up and
running on us.
So we'll get an instance up and running over there.
And we'll install the database engine and get the client
tools and the things that we'll need to work with the
database engine up and running first.
Then we'll take a look at how to get an extra component
installed, SQL Server Integration Services, an
amazing tool for doing things such as importing or exporting
data, automating tasks and processes inside or even
outside of SQL Servers.
So we'll take a look at how to get that
component up and running.
We'll then take a look at how to enable and disable features
by going back through the set up, choosing what instance we
want to work with, and then we can turn features on and off
pretty easily.
Towards the end of this Nugget, I also want to show
you how we can install using a configuration file.
And this is a great way to standardize your installation
process, especially if you're going to install SQL Server
2012 in many places across the enterprise.
And you want a standard install for them or even just
a base install, something that is common across all
installations.
Although you may tweak some specific installations, at
least you can kind of get a base install going.
So the configuration file allows us to do that.
It basically allows us to walk through an install and then
take a snapshot of how we want that install to look, save to
a configuration file that we can then run through the
command line for future installs.
And that's a cool thing that you can run it through the
command line because then you got it.
We can match it out, script it out, so we can even start
automating these installs.
And at the very end here, we'll take a look at how to
test connectivity.
I'll show you a couple of ways that we can test connectivity.
Using things like SQL Server Management Studio, we'll test
connectivity to the database engine.
And I'll show you how we can test our connectivity to
integration services.
So we've got plenty to do in this Nugget.
Let's start with those pre-installation tasks and get
familiar with how we can configure an
operating system disk.
So some pretty simple pre-installation tasks here.
The first one is ensure that you have six gigabytes of
space available on your system volume.
So if your C volume is your system volume, then ensure
that you have six gigabytes on it.
Even if you install SQL Server on a different volume that
isn't your system volume, you're still going to use six
gigabytes of free space on your system volume because
it's going to extract all the set up files
onto your system drive.
So that's one big one to be aware of.
Another one here is update the operating system with the
latest service packs.
That should be a no brainer by now right?
Also ensure that .NET Framework 3.5.1 is installed.
And if it didn't come pre-installed, then on the
client versions of Windows, you can install it in the
Control Panel, Programs And Features.
You can turn the features on and off.
So that's where you'll find it on that framework in the
client version of Windows.
On the server version of Windows, you can find it
inside the Server Manager Features area.
So you can turn it on and off in there.
Also in a really big one here, it's always a good idea to get
a RAID 1 mirror going on the operating system disk just in
case the installation goes sour, and it somehow screws up
our volume.
We don't want that happening.
So that's always a good backup plan to make sure that we have
a little fault tolerance there on our system disk.
So from here, let's head over to our Virtual Nugget Lab and
get the database engine up and running.
So we made it over to our Virtual Nugget Lab instance.
And a few things I want to point out before we get into
the insulation is number one, the Virtual Nugget Lab.
If you have access to the Virtual Nugget Lab, this is a
starting point.
You will be exactly where I am now when you log in for the
first time.
So what that means is you'll follow along and do an
installation of SQL Server Express, and I'll show you, as
well, we're actually going to do an installation of the full
Enterprise Edition SQL Server in evaluation mode to showcase
some of the advanced features.
But this is where you'll be.
It's a fresh, clean installation of SQL Server
2008 Data Center Edition with Service Pack Two on it.
So let me go into the file system here.
Right click on the Start menu, hit Explore.
We'll head up to the C drive.
I also created a folder here called 70-462 support files.
This will be a nice centralized place where we'll
put any files that we reference and use throughout
this series.
And you can see I have two setups in here.
We have one for Express and one for Enterprise.
So first we're going to install the Express Edition.
And then we'll install the Enterprise Edition in
evaluation mode.
And the reason we need the Enterprise Edition is for some
of those advanced features that you can't get using SQL
Server 2012 Express.
And this Nugget is actually a good example of that.
We need to install integration services.
Well, guess what?
There are no integration services with SQL Server 2012
Express Edition, so that's why we have
the Enterprise Edition.
And the beauty of this is if you do have access to the
Virtual Nugget Lab, then when you install it, you'll have
six months from the point that you install it just like I'm
going to have six months from now, the point when we're
installing it.
And you'll notice that we actually have a sample
database in here as well.
The AdventureWorks 2012 sample database that
we're working with.
And we will get this into SQL Server when we deal with
attaching, and detaching, and restoring, and talk about
migration here in a future Nugget.
All right, let's get the database
engine up and running.
So we're going to do this using SQL Server
2012 Express Edition.
If we go into this folder, we have an exe in here that I'm
going to double click.
And this I downloaded this directly from Microsoft site
that gives us SQL Server Express with advanced
services, or advanced tools.
And this is the edition that gives you the most features
and functionality, the edition of SQL Server Express that is.
So once we run it, we can hit Run here.
It's going to unpack it into a random directory into our
system drive.
And then when it's done, it's going to ask us for
permission.
And then it's going to launch into the installation centers.
So what I'm going to do here is I'm going to spare you from
all of the loading screens, and progress bars, and wait
screens, and all that because there's plenty of that in your
future with SQL Server and other things I'm sure.
So let me pause the video.
And what we're going to do then is rather than wait every
time we click Next through the Wizard, you'll just see this
screen step by step.
So once you're done unpacking, it takes us to the
installation center.
And here really it's going to take us right into the
Installation tab where we can either do a new installation,
or we can upgrade an existing installation.
Also the Planning tab has some useful links on it.
We'll take you out to MSDN and give you like the hardware and
software requirements and some other good information.
The Maintenance tab here is for upgrading an edition.
So you can upgrade what we call an intra edition upgrade
if you want to upgrade from a specific edition in the same
version to another edition.
Repair and then you can launch for product updates.
We have some tools in here.
The System Configuration Checker.
The Discovery Report.
This is a really cool one.
I'll actually show you this when we're done installing
because this will give us information on what is
installed on this server.
So super handy link there.
And you got some other resources in here.
Just MSDN documentation.
So we're going to go to the Installation tab.
And we're going to install a brand new standalone instance
or add features to an existing insulation.
So that's right, when it comes time to add or remove
features, we'll just come right back into here so we can
modify what we did or didn't do.
So here is technically the second screen of the Wizard.
Now the reason it skipped the first screen is
because we were OK.
We didn't have any errors generated.
But you'll always want to go back just to make sure you
also don't have any warnings.
And you can see we do have a warning because Windows
Firewall is enabled on this machine.
Now if we wanted outside clients or anybody to remotely
connect to our SQL Server, then we'll need to generate an
exception or rule in the firewall.
And you can do that against the exe that SQL Server, the
actual service, runs under and also set up some
port rules and such.
So you may want to configure that if you want remote access
into the SQL Server on this machine.
Everything else is OK.
So we'll hit Next here.
And from here, we'll just want to accept the license terms.
We'll hit Next here.
And now it's going to take us into the feature selection,
the big one.
What features do we install?
And for this first install, let's just keep it nice, and
simple, and basic here.
And I just want to install the database engine.
So the database engine, all by itself, you can choose here
where the shared features are going to go.
And by the way, if you'll notice, here are the instance
features up here.
So you'll always get a service.
Every time you run a set up on this machine, you'll get a new
service specific to that instance inside of here for
these areas in the instance features.
And then these you only have to install once.
And you'll be able to use them across all of
the different instances.
That's why they're called shared features.
So that's where the shared feature directory comes from
down here and also for the x86 architecture.
So we're just going to choose the database engine.
And we'll hit Next.
Now this is another area where the Wizard will skip right
over this step if you pass the installation
rules, which we do.
And this is going to check for Service Pack One of the .NET
Framework 3.5.
So hence 3.51.
So it checked for that.
It was good, and it skipped over it.
Let's hit Next here.
And now the instance configuration.
Do you want a default instance or a named instance.
If you choose a default instance, you can only one of
these per machine.
And it's going to assume the name of the computer.
If you use an instance, we'll access it using the name of
the computer backslash name of the instance.
I always like to name the instances.
That way it's clear cut what kind of
server you're accessing.
You can give it meaningful information about what kind of
server it is as well.
If it's production, is it development, what is it?
For this one, though, since we're going to have multiple
versions of SQL on here, we're going to call this one how
about SQL 2012 XP Express.
There we go.
And if you click down here, it will also take that as the
instance ID, which means the Data Director will be named
the name of the version that we're on. msSQL11--
the underlying version--
dot name of the instance.
And also the instance root directory
then will go in here.
So it's just going to pend the ID onto the root directory
with the internal version of SQL Server on here.
So this is where all the data files gets stored.
Any time you do a backup, it'll
be the default directory.
So it's pretty much just a collection of everything that
we do on this instance will get stored somewhere in one of
the folders inside of there.
Also if you had any previous versions installed in this
machine, like we will this one when we run the evaluation
install, they'll show up in here with some good
information about it.
So we're down here.
Let's hit next.
Now the Wizard will do the same thing.
If we meet the disk space requirements, it'll jump right
over and take us into the server configuration where we
can configure the service accounts.
Let me just hit Back for a second her.
Just to show you it gives you a summary of the disk space
requirements and where it's going to put things.
So it's going to require about a gig of space for the
database engine and nothing more.
You can see we have about 24 gigs of space on the actual
drive that we're installing it on.
So it's a system drive.
It's going to take up 167 megabytes, not too bad.
The shared installation directory is zero because we
didn't have any shared features installed.
And our instance directory's going to take up 864 megs.
So we're good there.
Let's hit Next.
And now comes time to configure the services.
And this is really going to come down to configuring the
database engine.
The SQL Server browser service, this is irrelevant
unless you have multiple instances of SQL Server
installed, which we will.
So it'll become relevant.
And this is just really a helper service.
When incoming connections come in, and you have multiple
instances on a box, it's going to be the one that figures out
which instance the incoming connection really needs to and
then route it to it.
So this is disabled because there's only one instance on
this machine.
It will become enabled when we get into installing the next
version here, the evaluation edition.
The other thing to really think about is what service
accounts you're going to run under.
So MSA accounts.
The general rule is your MSA accounts, your
Managed Service Accounts.
Those are the ones with the dollar sign in the name.
Those are good if your service, your database engine,
for instance, needs network access.
So that's where you'll use that because again, it's an
account that's managed by the domain.
The SPNs are managed.
The passwords are managed.
And it's a good account because it can
have network access.
These virtual accounts have no network access.
So if your service will never need to go outside the local
machine, then you're OK with setting them as virtual
accounts, which are also nice because, again, the password
is managed for us.
So we'll leave these with the default.
And also, by the way, if you do want to use a domain
account for whatever reason, you can drop this down and
browse the Active Directory.
And you can also give it the password that it needs.
So when the service starts up, it'll login under that account
and be running under that account as far
as credentials go.
So let's use the virtual account for our services.
And we'll leave that as it is.
You can also specify a collation.
A collation determines the code pages, which are going to
determine the character sets that are supported as far as
data goes in SQL Server and how it's
physically stored on disk.
So that's it for the service configuration.
Now if we hit Next, it's going to take us into the Database
Engine Configuration where we can
configure things like security.
The first tab here is really about what kind of
authentication mode are we going to use, just plain old
Windows authentication, or are we going to allow mixed mode
where we can use SQL Server created logins and SQL Server
security while also supporting Windows authentication and
Windows security.
So I'll leave this as Windows authentication mode for now.
I'll show you when we get into instance management how you
can flip this back to mixed mode and assign the password
to the SA account, the System Administrator account, which
is, again, the major SQL Server authentication account.
Also it's going to add the current user in as an
administrator.
That way we can administrate it.
And we're on the Nugget user account
inside of this instance.
You can also specify your data directories.
So we have the Data Root Directory.
And then all of these as far as the database directory goes
are all going to point to the data directory here within our
instance folder.
So same with all the way down except to
the back of the directory.
That's the only one that's different.
So all of these are going to be where the data is stored,
temp db, user database, the actual directory itself for
the database, the MDF files, or NDF files, or log files.
These are just going to be all the defaults.
And you can override these when we create our database.
The last two tabs we're going to worry about now.
User Instances I wouldn't worry about because it's going
to be deprecated in a future version.
They've already announced that it will be going away and that
you should migrate off of it if you use it in any sort of
production manner.
Also file streams are great for working with unstructured
data and storing unstructured data inside and outside of SQL
Server and unstructured meaning like image data or
binary data.
So once we're done with this screen here, we can hit Next.
So once we hit Next, it's going to take us right to our
installation.
It's going to physically do the installation here.
It skipped over error reporting and the installation
configuration rules because there were no errors and we
met all the rules.
So we're good to go.
I'm going to let this install.
I'll be back when it's completed.
So we made it to the final screen here in the Wizard.
We're done.
Everything is successful.
So where we go from here?
Well, let's close out of set up here.
And we'll close out of the installation server for SQL
Server Express.
So the first place we want to look here is
just the Start menu.
If we go to the Start menu, All Programs, we have
Microsoft SQL Server 2012.
If we open it up here, we have the Import
and Export Data Wizard.
We have Configuration Manager, which I'll take you to in a
little bit here, Error and Usage Reporting.
And then there is our Installation Center again.
So if we wanted to click on the Installation Center, we
could get right back to where we were very easily.
Now the next thing I want to show you is the file system.
So let's head over to Windows Explorer.
Let's collapse a few nodes and head into our
Program Files directory.
And in here, we'll have a SQL Server directory.
And you can see these are directories for previous
editions of SQL, even though they were installed with this.
And the reason they do that is they give the bare minimum of
what we need or what SQL Server 2012 needs to be
backwards compatibility with other versions.
So you'll see it'll have some DLL files and things that it
needs, again, to communicate with prior versions.
But this is SQL Server 2012, the 110 directory, otherwise
known as the 11 directory, 8, 9, 10, 11.
And this contains all of the Program Files for SQL Server.
This is our instance directory that was created.
So if we go into here, every instance would if we were to
install another instance, it's going to have all of these in
here for a new instance, whatever we name it.
So you have your backup directory,
which we talked about.
We have the bin directory.
We have the data directory.
If we go into here, you can see anytime we make a
database, it's going to default into this directory
for the data and log files.
And these are the four system databases that
come with SQL Server.
We have the master model, MSDB and the temp DB.
And then we have some other things related to this
instance as well.
All instance specific folders and data.
Now if we back up a little bit back into our SQL Server
directory, and we go into this 11 directory.
Now in here, let's go into the Tools, into the bin directory.
And we have a command line application
here called SQL command.
And this is really your window into SQL Server from the
command prompt.
So think of this as like SQL Server Management Studio for
the command line.
And so if you didn't install Management Studio, or you
don't have access to it, you can still get it by just
installing the core database engine and still test
connectivity.
Now where this will really come into play is if you're
installing this on a server core installation because
there is no GUI.
There is no SQL Server Management Studio.
So unless you have access to Management Studio remotely,
and you can test connectivity that way,
then you can do that.
But if you need it local, and you want to test it local
while you're installing SQL Server 2012 on a server core
mode, SQL command will be your friend.
It'll be really your only way to test connectivity locally
on that server.
And it's pretty easy to work with.
Let's just fire up a command prompt really quick here.
We'll go into here.
I'm just going to paste the path into here.
Usually you can drag it in.
But I believe my ERP session is not going to let me do it.
So let me just paste in the past here.
And we'll just do a SQL CMD forward slash
question mark here.
Ooop, you know what?
I've got to wrap everything inside of double quotes here.
You know how it goes, right?
There we go.
And get my insert key off.
All right, now we're talking.
There we go.
So here is the help for all the switches that we can use
for SQL command.
In fact, if we wanted to connect to it here, we just do
a dash s and then accept the defaults for everything else.
We'll be able to trust the connection because we know the
nugget user has access.
Well, let's do the same thing.
Well, let's just do a dash s, capital S, followed by the
name of the instance, local host backslash SQL 2012
underscore Express.
And that is a good sign.
If you see one there were we can submit queries now.
And I like to do this here just to make sure that we can
get something back from SQL Server.
Select and add version.
Hit Enter.
Type in Go.
Hit Enter again.
And there we go.
We just ran a query against SQL Server.
And it returns to us the version information.
So you can see where 11.0.2100.60 on X64, it is the
Express Edition 64-bit on Windows NT6 and web building
service pack we're on, Windows Server 2008 Data Center with
Service Pack 2.
So I would call that a success.
SQL Server Express is installed with
just a database engine.
We can test connectivity.
Let's close out of this stuff.
And let's go back up into our support files.
And let's get SSIS going here.
Not only that, but let's use the
Enterprise Evaluation Edition.
And I already have this unpacked inside of here.
So you don't have to go through the unpacking phase.
If we just run setup.exe and hit Continue.
This should take us right into the installation center.
So that's exactly what it's doing here, launching the
Installation Center.
A lot of this is going to be the exact same.
So I'm not going to make you sit through all of it again.
What I am going to do, though, is just show you the parts
that really matter, such as the features, where you select
your features.
We need to add SSIS.
We need to add SQL Server Management
Studio, the data tools.
We'll get everything that we didn't install when we went
through our express installation.
So here we are back in the Installation Center.
And we're going to do the same thing.
We're going to do another install here.
We'll add another instance of the server.
This time, though, we'll add everything
that we missed before.
We'll add Management Studio, the data tools, and we'll get
Integration Services installed as well.
Now the thing to note about Integration Services is you
can only have one instance of Integration Services installed
per machine.
The only exception to that rule is if you're going across
versions because you can have an instance of SQL Server 2008
Integration Services and SQL Server
2012 Integration Services.
So we're good here.
Everything looks good.
We passed the validation and set up rules.
Let's hit OK.
And now it'll launch the true Setup Wizard.
Again, I won't make you sit through most of this.
So I'm going to pause this.
So here's the first screen.
And it's a little bit different because we already
have an instance installed.
And here it is.
It gives us some information about that
instance we just installed.
So here's where we would add features.
We could say add features to an existing instance of SQL
Server, run through, and choose the feature selection.
And then, it'll add those features to the instance.
We're going to do another brand new installation so we
have, again, multiple instances running on this
machine so we can test out some of the advanced features
we'll need the Evaluation Edition for.
And then we can still use SQL Express for everything else.
All right, so let's perform a new installation.
We'll hit Next here.
And now it's going to ask us you can enter a product key
here if you have a valid product key.
Or you can have six months of the Evaluation Edition, which
is what we're going to.
And then we'll hit Next.
And we're back to where we were in the previous
installation.
So we'll accept the licenses and terms.
We'll hit Next.
And this time because, again, we have all the features
available to us.
This is the full version here.
We can choose the SQL Server feature installation if we
want to install the database engine and then all of the
extra components.
If we want to install SQL Server PowerPivot for
SharePoint or all features with the default.
So let's just choose SQL Server Feature
Installation for now.
Hit Next.
And now we should get a really nice list of features.
So we've got a lot more than we've had before.
But we're definitely going to install the Database Engine.
We'll install replication, full text.
We're going to install everything as far as the
database engine goes.
And then for shared features, we want Integration Services.
I'm also going to add in here the data tools and the
management tools complete.
So these are the shared features that we'll be using
and working with.
Everything else I think we're good on.
We will come back in a future Nugget again and work with
getting analysis services, reporting services, and any
other tools that we may need to work
with here in the future.
But this looks good.
Let's hit Next.
So we chose our features.
We passed the installation rules, which were again just
checking for the .NET Framework 3.5.1.
We have our instance configuration next.
And this one we're going to name SQL 2012 underscore Eval.
There we go.
We're good.
It shows us or previous instance on here.
Here is the new instance directory for this instance
we're installing.
And now we'll hit next.
The Disk Base Requirements Summary Screen here just shows
us that we're going to require about five gigabytes on our
installation drive this time.
So if we hit Next here, it's going to ask us to configure
our services once again.
This time we have a little more.
We have SQL Server Agent, which is great for creating
jobs and automating anything based on a schedule inside of
SQL Server.
We have the database engine once again.
We'll leave all this stuff with the default.
And here is Integration Services.
It also runs as its own service.
We've got the full text file and the SQL Server browser,
which you see is now flipped to automatic because we have
multiple instances.
So the browser is going to help those incoming
connections find what instance they're looking for.
So everything looks good here.
Let's hit Next one more time.
And again, here is the server configuration screen, what
we're going to use here for security.
And this time it didn't add our user.
So let's just add the current user in here.
And that should add our Nugget user account here as an
administrator to this instance.
There it is.
Perfect.
So we're good to go here.
This one let's go ahead and put mixed mode on.
Actually why not?
We'll take two windows [INAUDIBLE].
We'll change that if we need to on the fly.
All right.
We'll hit Next here.
Finally, you have some error reporting if you
want to send errors.
And then comes the installation.
So it's going to show us some installation rules.
We're good here.
And now it's going to be installed.
And here's one thing I want to make note of.
See this?
You won't get this with SQL Server Express.
You'll only get this with the full versions.
But this is our configuration file.
Everything that we did inside of here is stored in this
configuration file.
So we're going to need this if we want to install based on
this configuration file.
So if we want to install another instance the exact
same way we chose this one with the same features and
everything, then we can copy this and remember it.
And I'll copy it and remember it because I'll show you how
you can install using this configuration file next.
But we're good here.
So let's set hit the Install button.
We'll let this install.
And I'll be back when it's finished.
And we're done.
Installation complete.
We've got the management tools in there.
We've got another instance of the database engine.
And we have Integration Services installed.
So if you close out of this.
Let's just head back into the Start menu for a second, into
all programs, back into SQL Server 2012.
And look at this.
We've got a lot more to work with here.
We've got Integration Services, which are some extra
tools for working with Integration Services in here.
Again, you won't launch anything in here to develop
Integration Services packages.
You'll be doing that using SQL Server data tools.
We also have SQL Server Management Studio, which is
what we're going to use to connect and test connectivity
rather than SQL command to show you another way to do it
and just to ensure that we've got everything up and running.
So let's launch SQL Server Management Studio.
So it'll fire up for the first time.
It'll take a second.
While that's launching, I'm going to close out of the
Installation Center.
I'm going to minimize our Windows Explorer.
And here we go.
So let's browse for more here.
Database engines and check it out.
We've got our Express and our Eval.
So if we just connect to our Express just to make sure that
it does work.
There we go.
And it does work.
We've got all the system databases.
And this instance is running and ready to go.
Now let's hit Connect here to another database engine.
This time let's browse for our Eval.
Hit OK.
Hit Connect.
And check it out.
Once again, another instance.
You can already tell this has some of the
advanced features in it.
It's a full featured version of SQL Server.
We even have SQL Server Agent.
But it's disabled.
I'll show you how to enable that.
But it's got a lot more already inside of here than
our instance of SQL Express does.
So how do we go about testing out an
Integration Services instance?
Well, we can drop down the connect button up here and
choose any the external tools here that is related to SQL
Server Analysis Services, Integration Services,
Reporting Services, as your storage.
Or if you wanted to connect to another database instance, you
could check this right here.
If we choose Integration Services, it's just going to
give us the Connect To Server dialogue and point the server
type to Integration Services.
So now we could drop down and browse.
And you can see we only have one instance installed.
So it defaulted to it.
And again, we don't have multi instances
with Integration Services.
So it's just going to assume the name of the machine that
it's installed.
And on this case, we're on SQL Nugget Lab.
So if we hit OK.
And we hit Connect, we're going to get yelled at.
Access is denied.
Now if this kind of stuff happens in Management Studio,
the best thing to do, at least the first thing to do anyway,
if we're going any further, is--
this always helps.
I'm going to minimize this.
We'll leave this one running.
We're going to run it again through the Start menu.
I'll take the shortcut here.
We'll Right Click on it and choose Run as Administrator.
Hit Continue.
And that should do it.
So running as the admin is always a good thing,
especially when you're working with these external tools such
as Analysis Services, Integration Services from
Management Studio because it'll bypass
some of those problems.
So let's try it again.
Same exact thing.
It's going to default to Integration Services since
that's what we last tried to use.
SQL Nugget Lab.
We'll hit Connect.
And look at that.
This time it works perfectly.
So that was the problem.
That was the error in that case.
And that'll happen sometimes.
So it's always a good idea just to run Management Studio
as an administrator.
And this is a very simple interface for managing
packages stored in the Integration Services instance.
So again, we have a database engine installed here.
The thing with Integration Services, if you don't have a
database engine installed with Integration Services, then
anytime a package is run from a job, the job is actually
going to run the package on the machine that invoked it.
So this can get people in trouble.
I've gotten in trouble a few times here because if you
develop a package on a machine that has Integration Services
on it, and you have pads that point to files and directories
on that machine, but you don't have the database engine
installed on that same machine that Integration Services
runs, then that package is going to execute on the
machine the job ran on.
And those pads and directories may not exist.
So you'll get some crazy errors that you'll be hunting
down for days.
So that's why it's a good idea if you do install Integration
Services on a machine, you should install the database
engine because it stores its packages as well here in the
MSDB database on that database instance.
In this case, we installed Integration Services with our
full e-validation.
So if we were to deploy our packages into the SQL Nugget
Lab Integration Services since they would go into the MSD
database in our Eval instance.
Now if you are one that creates Integration Services
packages, which you'll do over in SQL Server Data Tools, when
it comes time to deploy them, you will deploy them inside of
this instance more than likely.
And you can do things like this.
You can create an organized structure inside of MSDB here.
We can create a folder called My Imports.
And then from Data Tools, when it comes time-- and let's just
refresh this so we can see that folder.
In Data Tools, when it comes time to deploy it to this
instance, you'll point to your Integration Services instance,
browse to the folder you want to put it in, and then place
it inside of that folder.
And then from a database engine, you can go into SQL
Server Agent, create a job, point it to this Integration
Services server, browse to the package, and you're off and
running with automated SSIS packages.
So cool stuff there.
All right, let's close out of our
Management Studio instances.
And I want to show you where the services are, where you
can manage them, where we should be managing them,
according to Microsoft.
First let me just show you the standard place to find
services, which is Start, Administrative Tools, and the
Services Applet.
So this is what we're all used to I'm sure and what we've
become used to over the years of managing services.
And you can view the SQL Server Services in here.
So these two are the database engines because they just say
SQL Server followed by the instance name.
Here's SQL Server Agent, each instance.
You can see our Eval instance came manual.
Our Express instance came disabled.
And then we've got our supporting services as well.
So Microsoft recommends we do not start, stop, change logins
or start up types, or really manage the services from here.
Microsoft advises we do that through the
Configuration Manager.
So if we go to Start, All Programs, back into SQL Server
2012, inside of Configuration Tools, we have the SQL Server
Configuration Manager.
So if we launch this, you can see the first node here is SQL
Server Services.
So they advise we start, stop, change, and manage them
through here because it's through SQL Server.
And if there are any other dependent processes or
services, SQL will handle them appropriately.
And not only that, but we do have extra things, extra
properties and options we can configure at the service level
through Configuration Manager.
For instance, if we double click on our Eval database
engines, you'll notice we have things like always on high
availability options, file stream options.
And then you have some advanced things that are,
again, related to SQL Server.
So good idea to stay in here when we're going to do things
such as manage the services.
Now one more thing I do want to mention about adding and
removing features.
Adding features, piece of cake.
We just go into Installation Center.
We re-run the set up and then choose new features to add to
an existing instance.
Removing features is similar.
It's just where you find it is a little bit different.
So in the Start menu, inside of your Control Panel, you go
into Programs And Features.
In here you choose your SQL Server 2012 Installation.
Hit Uninstall Change.
And continue here.
And it's going to relaunch us into set up.
Here we would choose Remove.
And it's going to launch us back into a set up here.
The big difference is it's going to take us into the
Features Selection area of the Wizard just like when we were
doing installation.
Only rather than choose the features you want to install,
it's going to give us a list of
features that are installed.
And we choose the ones that we would like to uninstall.
That's really the only difference between adding
features and removing features is where you'll find it and
the fact that you're removing features that already exist
rather than add features that don't exist.
So you can see when we get launched into the Wizard, we
just drop down to what feature or what instance we want to
remove features from, or if we only want to remove shared
features, we choose our instance, we hit Next.
And now it'll give us a list of those features
that already exist.
And it also gives you the shared features as well.
So if you wanted to remove shared features, you could.
So you just choose what you want to remove, hit Next.
It'll remove it.
And you'll be done.
So let's cancel out of this and take a look at how we can
install using a configuration file, which is a great way to
save yourself some time, especially if you're doing
multiple installations across your network or enterprise.
And it's a great way to script out the installation.
So the configuration.ini file I pointed to earlier stores
all the settings that we did throughout the Wizard, the
Installation Wizard, everything that we chose.
And it gives us an easy way to script it out using setup.exe
at the command line, and passing in a switch, and then
passing in the path to the configuration file.
So what's cool about this is that, again, we
can script it out.
And you can then do an unattended installation.
I'll show you some switches we can use to quiet the
installation and accept the license agreement so it can be
completely unattended.
And you can push this down to whatever PCs you want using
whatever method you'd like.
The other side of this is if you have an installation of
SQL Server that's already out there that you want to take a
snapshot of, and maybe it was already installed before, and
you can't walk through the Wizard because you don't know
what the settings are, then sys prep is perfect for that.
Sys prep is a great way to do imaging.
So you can take an image of your SQL Server and deploy it
alternatively that way.
But we're going to focus here on how to use the
configuration.ini file.
And really it's just a matter of getting access and finding
that ini file, running through the Wizard once, setting up
exactly how you would want it to look, setting up your
service configurations and such, and then finding that
ini file, and then running it through setup.exe through the
command prompt.
That's really all there is to it.
So first of all, where do we find it?
Well, I copied the path earlier when we were in there.
That's probably the best way to do it.
Let me show you where it is anyway.
If we go into Program Files, SQL Server, it's going to be
in the version of SQL Server that we're currently in.
Setup bootstrap log.
And then in the log file, you want to find the date that you
ran the set up.
So I actually ran the set up on 8/3 at 005217.
There it is.
This is the one that I ran.
So inside of here, here it is.
Here's the one that we ran right here.
And if you take a look at this here--
I'm just going to Right Click on it and
open it with Notepad.
You can see that really this is what all the command line
options support.
So if we run setup.exe at the command line and look at all
the switches for it, these are really all of the switches
that are in there.
So action equals install or uninstall.
And it actually shows you what these switches do.
So that's all the configuration file does, just
tracks all the settings we chose through the Wizard.
So what we need to do now is head back up here to our
Install Director.
I'm just going to first get a command prompt going.
So we've got a command prompt going.
Now we need to get our path to our setup.exe in here.
So let's do that.
I'm just going to copy this path.
I'm going to come down here first.
I'm actually going to do an open quote here.
And then we'll paste in our path.
Let's do a setup.exe on the end of that, end of quote.
And then we're going to go forward slash configuration
file equals--
and now this is going to be the path to our configuration
file, which I just happen to have on the
Clipboard as well here.
So let's paste it in.
There it is.
So if we were to run this now, it would run
us through the Wizard.
It would launch the Wizard.
And it would just use a configuration
file for all the settings.
We would still need to click buttons and
watch progress bars.
So if we wanted to bypass that, we could use the Q
switch, which stands for quiet mode.
This'll bypass pretty much everything, the entire Wizard
and just run it with the settings.
The only other thing we really need to do here is I accept
SQL Server license terms.
There's your license agreement.
And by we can also do a QS.
A QS is quiet simple mode, which will give us the Wizard
and the windows.
You'll be able to see the progress bars, but you won't
need to click any buttons.
So Q won't show us anything.
It'll just do it quietly unattended without anyone
knowing, the ninja install.
Or you can do QS, and see some windows, and still see the
progress bars and such.
But that's it.
Not much too it.
This configuration file really makes things a lot easier
because it tracks everything we did in the Wizard.
And then we can reuse it for future installs whether you
want to do it unattended or not.
It's a great way to standardize your installs
across your network or enterprise.
So that's about all there is to installing SQL Server 2012.
So in this Nugget we took a look at the different ways
that we can install SQL Server 2012.
We started off with just some basic prerequisites there, as
far as hard drive space is concerned and software
requirements such as the .NET framework.
We also took a look there at how to install SQL Server
Express Edition.
We started with Express, just a basic database engine
install with nothing else.
And we saw that we could connect to it using the SQL
Command Utility.
We then took a look at how to do a full installation using
the Evaluation Edition where we got the database engine
installed, some tools up and running.
And we even took a look at how to get an external component
in SSIS, SQL Server Integration
Services, up and running.
We took a look at how to work with services where we can
configure them and work with SQL Server
Configuration Manager.
We saw how to test connectivity to the Database
Engine and Integration Services, took a look how you
can enable and disable features.
And at the end here, I showed you how to install it using a
configuration file from the command line.
I hope this has been informative for you.
And I thank you for viewing.