Microsoft Sql Server 2008 basics for Oracle DBA's

In this demonstration you will see how to:
Stop and start service using the SQL Server Configuration Manager
Stop and start services using the command prompt
Connect to a database engine
View the properties of an instance in Configuration Manager
Review a database in object explorer
Connect to another database engine
Review associated registry keys for SQL Server installations

---

Closed Caption:

in this first demonstration
we're going to take a look at how we can
start in stop
the different instances a sequel server
running on a single box
will take advantage abusing a gooey tool
as well as using command prompts to
really use a gooey tool
we're going use a sequel Server
Configuration Manager tool
to get to this is this is our first time
will come down to the start button
will go to all programs Microsoft sequel
Server 2008
and over to the configuration tools then
we will choose sequel server
Configuration Manager
this provides us a list all the
components that have been installed
in sequel server on this box including
the sequel Server database engines
as well as some %uh the other components
we can see here that's equal server
named instance 01 is running
simply by looking at the green arrow we
also see the Siebel Server instance
02 as well as the default
instance which we just see the name is
ms SQL
server Microsoft sequel server what I'm
going to do so I had the capability of
just coming up in doing the right mouse
click
over any my instances and I can stop
pause a restart them so click on stop
it will let this go ahead and start the
process
watching the finishing
the instance and it's running like you
click Close
didn't even have time if I want to get
rid of the dialog box
we can now see that the instance 01
is stopped by the Red Square let me
minimize this
it will go look at how we can work with
this in a command-line
Sony move my cursor back over to start
I'll choose run in from here
all this go ahead and either type cmd or
confirm it
by clicking OK this will bring up a
command prompt for us to be able to work
with the environment
what I'm going to do is type in net
start and I want to start a service
running in the Windows environment
in this case it will be the Microsoft
sequel server and because I'm going into
just the default
what I pitchers like this when I hit
inter you'll notice it comes up with an
error stating that this service is
already been started
and if I need to learn more I can type
net help message 2182
now why does this show this already
started
at you thought that I stopped it what I
stopped was a named instance
not the default so want to build his
show to you what happens
if you try to start in existing instance
it doesn't change
so let's come back and do a net start
I love the instance in this case all
tight the in S
yes hywell insists on using a
named instance I provide a dollar sign
to help us build the fine the instance
name
and in this case it is instance 01
now I intentionally go ahead and do a
capitalization here
not because I have to but just feel the
show a mix a small case in uppercase
this is case insensitive it doesn't
matter this is the one we stopped so as
I
press the Enter key we see that it is
starting
and is quickly started back if we go
back
to are sequel Server Configuration
Manager
just by looking here it appears
that the instances not started the
reason being is rather than polling
all over databases and our servers at
different times what we have to do is go
ahead
India right mouse click on the sequel
servers services
and click Refresh this thing goes back
out the WM I
in shows is that the instance 01 is
actually started
we're talkin a future module
module for teen in look at
clustering but it is important to note
is that when you're using clustering you
should use the clustering services
to be able to start in stop your
instances now
let's go ahead and take a look at some
in the sequel server objects that are
available to us
through another Q retool or minimize the
Configuration Manager
awaiting close-up my command prompt
come back here to start and I'll go back
to my all programs and of course to
Microsoft's equal Server 2008
I will then open up the development
environment
called sequel Server Management Studio
no I say it's a development environment
because it's based off the initial shell
Visual Studio but this does not mean
that is strictly
only for developers it is also for
deviates
to be able to work with the databases
directly
when it first comes up it gives me an
opportunity to sign in to a specific
instance the name a bar box the server
when the Windows operating
services were installed is sequel TB WS
earlier I was using a dollar sign when I
was working with the services
but I will now use a backslash a
reference the instance 01
will talk security later whether we can
use the Windows authentication
or a special sequel login
in this case we're just going to leave
it as the default click Connect
all expand s SMS
to build the show that we are running
instance the
dear /url one a sequel server and we're
doing module
our skis me release 10 which is actually
sequel Server 2008
we also see the account that I've
registered myself in
as sequel TV WS as the actual
administrator if we want to take a look
at the properties
up this week into a right mouse click
over the instance
and come down to the bottom and choose
Properties
within the property serbian since we had
the capability is seeing several
different items right here
including looking at the general
information
the memory the processes the security
additional property so we can look at
for the incidents are back under the
sequel server
configuration so let's go back down here
since I just minimize that
will open this up and in the meantime
we'll also go ahead
and remove this require our request
to make sequel Server 2008 better
Microsoft if I go ahead and double click
on this
gives you the opportunity to provide
customer experience the back
note that they have a data privacy
statement
they are not going to capture any your
data just essentially how you're using
Siebel Server
you had the chance to choose Yes or go
with the default have no
will click OK we will not get that
prompt anymore
if we come back up the sequel server
instance 01 here
we can do a right mouse click a look at
its properties
from here we can define how this account
is being defined
with in sequel server with a specific
account name
as the service also providing a password
since it is the service we can define
how we want to be old for by this is
starting
automatically or it can be disabled when
we start up our system
or we had the opportunity just to simply
go
with manual
we'll talk about FileStream later but we
had the capability
using large objects enabling FileStream
to help us build to take advantage the
NTFS file system
in sharing data the Advanced tab
allows us to take a look at several
additional items
whether we're working in a clustered
environment where is our
error logs going to go to
also water our startup parameters
later we'll talk about the necessary
files and databases
that allow worst to run instance
a Siebel Server but this gives you the
information about where the database
files
and log files which are going to be used
we're gonna click Cancel here I really
don't want to make any direct changes
we come back over to ass SMS now
let's look inside at the instance we can
see that we have some databases
if I expand this will see that we have a
couple special folders
will talk about system databases in an
upcoming Marshall
as well as hell snapshots can work
inside here I have multiple
instances a database I have
AdventureWorks
I have AdventureWorks 2008 in these are
in user
database is essentially report server
works long with one of the components a
sequel server if we want to be reporting
we also have the capability looking at
security
in the key item a look at security right
here is that we can look at loggins
server roles working with Papa graffitis
now security is we'll talk about a
modules 6 and later in March will
9 is security is also at the instance
level
as well at the database level
if I open up AdventureWorks 2008 you'll
see that there is also a security folder
here
if I open the security folder here we
can see information about users roles
and have %uh schemas were used to
schemers in Oracle
and we can see that we have some
predefined schemers
as well as those that have been created
such as for Human Resources application
or production and purchasing
if we minimize this security
we can also take a look at some bar
objects like tables
it by expand the tables we can see every
single item that is available to us
including those schemers in their
specific tables
if we come down and look at say person
dot person
we have the capability of expanding it
in looking at the specific columns
or triggers in indexes that are
available with this particular
table as will learn more will see items
such as
the person type the name style the first
name
with all their appropriate data types as
well
we can also take these items in script
the code directly to build a duplicate
or use for
backup and recovery methods not so much
for the database
but is our code in script I take person
that person the right mouse click
I had the capability is scripting this
table
as say a create to statement
101 have to do is open up what we refer
to as a query
editor window I could assign this
directly to a file if I wish
I'll go ahead and click this item
and we will see the appropriate CREATE
TABLE statement
being able to be populated in changed if
we wish
we'll talk more about the actual
transects equal
language but we can see that's pretty
similar to Oracle
we create a table we define its schema
the table itself as well as several
other items
in columns not only can we work with
just one instance insider
the SSMS tool but we can also connect
two additional instances let me go ahead
and click Connect right here
for database engine instead of opening
just insincere 01
I'm gonna open up instance 02 so that I
can review it
now I have a capability looking at the
objects
in this services available under
instance 02
you can have a wide number
offenses not just on the same box but if
you have access through a local area
network
or some trusted network you have the
capability
bringing in any number instances which
you want a bill to manage
now let's go ahead and talk about
where this information is actually
stored for the databases
to do this we're gonna open up Windows
Explorer
in the best ways to get to this
typically
is if you have never use it you can get
excess Res
and Windows Explorer or we've already
got a set up right here
will open up the windows explorer and if
we go into the My Computer
C Drive we can see under program files
we have me go ahead and make that a full
screen
we have
the marks a sequel server directory if
week span this
we're gonna see that we have listings
have several different versions
their possible eighty is equivalent to
sequel server
2000 ninety or like the 9.0
Siebel Server 2005 and 100
its sequel Server 10 to be able to see
the install directories
if we come on down into say
the program files\microsoft sequel
server
and we open up one hundred we have the
capability looking at several items
weathers the components that have
installed or other items
we come down the tools come over into
the bin
directory we had the capability seeing
information about the BS shell
member earlier mentioned that s SMS
is part love the Visual Studio
development environment
will come on down into the common tools
to the I D E in here we see several
applications that are available to us
to be able to see s SMS all the scroll
down
in C the SSMS
execute will file to build a look at
some additional files we can go through
here
such as looking at each instance
by minimize this in go to NSS
QL this provides us the opportunity it
looking at
summer the instances that maybe define
now
you'll notice that there's not a lot of
information here
because there are different solutions
for
a box ever assessed SQL
is the database engine looking at
ms are s is reporting services in the
ASA something refer to this analysis
services
let's open up the instance one drilling
down into the binary file
in it is here as I mentioned that each
instance has his
own binary code if we scroll down
you can see that we have Nick and
they'll look at further
sequel server dot EXE
this will be the service that will be
available for each to the distant
different instances
weather is it since 01 or $0.02
or the default instance itself finally
one of the items that you need to be
able to take a look at is the registry
to be able to see
how to view the installation the click
on Start
in go to run I can take a look at the
registry
by doing a Reg added
and it brings up our registry if I go
ahead in nights panned out the local
machine
going into software to Microsoft
I can come down here in see some
references for Microsoft sequel server
if I stay in the sea expand the Siebel
Server
I can see the different installations
that I've created
if weeks pay and insincere or one
we can see several items that are poorly
here installed
one of the interesting items is what
instances
are the right names and if we click on
the instance names we can either look at
in the whole lap
reporting services with the sequel this
is a quick way if you want to be able to
see
which specific default or instance names
have been created and the Association
for the specific directories
that we saw Windows Explorer this is a
nice way to be able to look
at those direct items
give that a close they'll be very
careful whenever you working in
the registry tool if you make changes
you could corrector database
so it's important to always do a backup
this concludes
our demonstration with 0.1%