SQL Server Consolidation! Lessons Learned!

Hangout #12 with William Durkin on SQL Server consolidation - what do we need to know, why we should consolidate if at all and what approach to take?

---

Closed Caption:

video the broadcast media is life so
willem welcome to kendo sequel can go
out
number number 12 a few words for you
capital
your number 12 a few or 3*a
yeah okay so and my name's william texan
I'm a dbag working in Germany
and have been for dome 12 years not to
overthink 12 years now
binge well here's yeah and some urgent
well scammed out
yeah example 2012 no sonya
and I've being in Germany for 12 years
but I'm originally from the UK
yeah and I started really working with
sequel server
are its eight or nine years ago
so I was looking for a year any online
mail-order company here in Germany and
basically because started to seek server
with a better data warehousing a bit of
CRM
so stuff and M from there
they just said okay who who wants to
learn C so so
I wasn't you're not stupid has to get
let's go for it to cuba
yeah I could this story somewhere are
unaware
probably somewhere on the web so
as possible if I think you many people
were in your situation by the way
exactly what can happen though yah
so northeast at accidental deviation get
yet something like an accidental the
gate yep
so I made to you on the sequel said
Thursday Eve and
at slovenia right that's right yes
that's right
yet so you present the topic any
presented the case to the around
sequestering
consolidation and risk yet so this is
the
action on the topic that we're going to
talk about today so my first question to
you is
why should we consider consolidating
what's a Weber
okay no min the
the main topic especially my talk and at
sequel saturday was
okay am most people looking to
reduce their costs I T in whatever way
possible
and at the biggest smack from microsoft
recently was their licensing price
increases keeping
when on Kirkman to 2012 was released so
man
a lot of people were like seriously
started looking okay
a sequel sever used to be quite cheap
now it's trying to catch up with Oracle
now pricing structure
how can we see Anna alchemy and your
fight these cost raises
so yeah for me at least my starting
point is to say okay how can I reduce my
costs using sequence ever
okay so an straightaway
I would think I've consultation or
somehow reducing my footprint on my
network
however you would do that so the first
points
and it's not its if I'm not well
if I'm not wrong it's not just only you
that think
for reducing the cost is the business
like he had lunch
a course I mean for me I'm I'm quite
happy to run many many many sexy
server instances I'm it makes no great
difference directly
for me behalf 10 service or if I half
30 so much I'm your and driving force
obviously is the business side to sing
okay
and where can we start saving money
instantly when
we're looking at licensing costs RM I'm
like
for many companies you have I'm
enterprise agreement with Microsoft and
software showrooms
when these contracts are being am yeah
set up
and you have to look what my current
pricing structures
what licenses to have what they need in
the future so is immediately upon we can
say okay
sequel server starting to get very
expensive
yeah me happened under control yeah so
it's interesting because as I told you
we're also we'd just that they started
our consolidation project
and the just the day we migrated data
database is too old to the new server
and what was the scope up your up your
project
I believe so with some huge number seen
your project yeah
your womanly Lisa projects currently
running and I'm wearin titty 107 sis is
on a network which
and we need to basically identify what
they are
or did you just for and how they're
going to be used in the future
so an its it's an very interesting
situation to
to find secret service on a network
which I'm not directly involved
in our department so i'm too early to
know if my company and there's been a
lot of
rogue installations a secret service in
a network
where it's just under the radar we don't
know about them to not being
looks after so we discovering those I'm
to do that which is used a brilliant
tool I from Microsoft
Microsoft map tool hack remembers my up
to
I somewhat analysis Anna I can't
remember the name
and put the map tool I'm what provincial
scans the network Francis equal service
my personal things undiluted up
Microsoft's
analysis and planning wasn't it this way
and just quickly Google just a quick and
his team Microsoft assessment
I'm still learning to live Kia so I have
probably kept
I we probably have to say here that the
system
32 right oh yes yes something
I'm surprised microsoft does not I'm
watch football
or even charge for I mean Dr it's a
brilliant auditing tool
scan your entire network go through your
Active Directory
and actually and on the newest version
if you have System Center
running on the new surgeon system
centering it will speak to the apt
and correct related information from
there and also
and brilliant new feature in and it will
read see new logs in a Windows 2012
server
which pertain to license usage that she
needs an especial auditing on
which will and counts how many clients a
connection to a server
what Dec using what's up whether using
lexical server or exchange them for
whatever
and keep keep it comes out so really
interesting not looking to
deep sleeper yeah so and probably I
shoulda
one more feature because they are
actually spoke about this too
some years ago on the on the conference
it's a very interesting do when your
goal and use it for
consolidating with the specific
appliance
write him seem that there are templates
if you want to conceal
consolidate on the HP Database appliance
I haven't tina takes no I remember we
spoke about this in Slovenia
and dot this this the HP Database
appliance which can do some amazing
consolidation yeah.so
its it's very interesting do because it
understands the templates up the VM site
and the VM sizes some that
appliance its can show rebar months
collects performance data and they say
and SS
these VM this machine that you talk
which
can Pete Tong a media and this machine
should go on a huge here
and so on and so forth so worst up I
believe
both the me in use should are suggesting
first step
in a consolidation project run the mop
bucket
oh yes definitely definite yet day will
be surprises in your network
a yeah some very interesting machines
I'm and there will be some interesting
discussions at work as well
so although what can we expect what have
you seen in your case
I am in our case many small
installations
I've sequencer obviously and we have
quite a few developers so they
automatically gets equal
express in stock so yeah and there's a
lot of these instances which we
discovered which
express installation so not necessarily
very interesting for consultation but
also it's interesting to know that their
and but with we've seen many many
installations of third-party tools with
their own database engine
I'm installed in the background buy a
console to has come in and done the work
and so d/b/a team hasn't been involved
so
I'm I what's best practices are not
being adhered to
within our in our backup policies in the
same way as they should be
am I don't jam many
underutilized systems where a they are
perfect for consolidation
tiny small database maybe 10 gigabytes
you don't need an
an extra instance to run it's just not
necessary
yup we also after we see all
after we find all these pleasant
surprises kids call this way hmm what's
the next step
well missing deep-sea on just a huge
list of service
I'm many of them my maybe known to you
maybe not
you need to find who they belong to what
they're used for
is it a production system is a
development system I'm
exactly how does it fit into a licensing
structure and also how does it fit into
a business
business use case fielded is it a system
that is
maybe it's a test development system but
its
extremely important and has to be
available for developers to be able to
work
and it pushes the whole scale that
system may be away from
just being a small test VM to being a
very critical
production here development system yeah
and also what does approach when you
well when what
approaching the you have when you
started to consolidate
give you consolidate its for instance or
you started to build GM son moved the
move the they do miss their
with we're very lucky in that we've got
am systems
which already running am large systems
which are currently
underutilized okay leaping installed
with a with a few to having much higher
load in the future
so we're already open set up to two meet
any group that we require so now we can
take the systems which are currently
underutilized
and say okay which extra systems can we
move onto
onto these larger budget production
systems
yet in the most interesting part here
globally to our
up want to but powerful a conversation
her it's very interesting because our
customer
Baltz on impressive piece of hardware
one year or two ago and its the cargo
was just not utilized what so ever
Mike Dane you that they are going to be
using it
in the future but they were not and our
it comes
it this code word came in play very
seriously because
we built square service with any so
there are clusters
and we started migrating their unity its
cool
I'm just I'm just not happy that we are
migrating need to
2008 r2 I believe we should we
we definitely have pro you
think about 2012 it was not my decision
at all
near yeah what's the word what versions
are you running
well went with him moving on migration
is going through 22
2012 so all here week we already have
some very 30 clear use cases for 2012
ok am if when we moving on two systems
to try and get
three to five years usage from them so
going to 2000
at two was was just not sensible from
in from my standpoint at least okay how
did you
was so you you are finding all the
servers and then
how did you get past whether or not the
applications will work
on 2012 okay so we have an
an entire VM test environment so
ok im looking with with very looking in
that respect
to do you have a team has a year a to
one of three note VM closer well we can
just spin up
have virtual machines and so that's
basically what we've done we've
spano test systems I'm gonna test
migration
seen exactly what steps are required for
each migration
sometimes very important as you
obviously know if you can eighty
database migration you know there are
lots of people to talk to lots of tests
to run
so the actual migration itself is being
tested
by doing a VM migration and and them
yeah basically just running a software
against
and the Sun something I found is
basically from
sequel 20 2005 on what's the
the bassist up the that's equal server
is running on
has not changed so dramatically
notion for my opinion at least from
2002-2005
seems to be not exactly a breaking
change but a
Lodge scale of change and from 2005 on
what it's being
clever slow iteration well maybe not
slow but
cleared miss quite good compatibility
going upwards
also I I found testing and running these
things
as as long as you're not doing exotic
work which equals server
at directly bound to a certain version
East is generally not much of a headache
yeah yes so the the other and probably
the the the last thing that I want to
discuss some
probably this will go up it's a bit
longer but Walter the
water the problems that you faced
during the mic during the consultation
project II
I want to ask you this because I want to
share some
some real problems that someone can face
during the consultation free
face okay based
well I don't know if you really custom
ask consolidation project
issues in terms of the being a
consolidation project is generally Mar
Ave
aim project issue so whenever you're
trying to run through a project like
this
especially for touching so many systems
there are so many people involved
trying to keep everybody up-to-date on
where you are
and what your plans are and making sure
it's clear to everybody
what the road map is yeah is always
so difficult so this is a committee
communication problems here
correct yes yes so even I mean I work in
Germany
ability for so long it didn't a lot of
my work in that respect is conducted in
German
so I'm fluent in German that's not the
issue so it's not a language barrier
it's just a general understanding
barrier right
yeah so you can have as many meetings as
you want and people would still not
follow what's going on all and you can
communicate with in saying OK
tomorrow we're going to be doing this
this and this and us tomorrow and they
say wall
I don't know anything about his so
just two am to key people on the ball
keep yourself on the ball as well and a
step exactly what's going on his
is very difficult but like i said i
think thats a general project issue
rather than
and specifically for consultation yeah
uninteresting want from me shows
before some some hours spoke
we yesterday we received an email from
the customer that
stated that the best they the basis for
the developers should go
on them in stones that was not built all
ready
and heat yes so get customer told about
does there have to be uninstalls
for those databases but we were not that
sure about this
and this project is in the planning
phase because those projects are
Rodney lent more than execute its rights
the the plank is the their timely
problem here yes yes to definitely so we
told that there should be no other
instances but
he said I expect another instance here
so we just built yesterday evening we
built on you
instance in order to it
to meets his requirements and his vision
out for this so
the communication problem is enormous
much more yes yup the lesson
a time that i've seen I'm very often as
well list too
receive not any am a requirement
but rather this is the solution I want
implementing
yup so and the customer you're speaking
about I don't know whether it
you're doing its niche shit talking
about and in-house
think or if you're actually doing and
instrumentation for a an external
customer
then obviously if its in house and it's
not
it's been it okay okay so an external
customer can dictate so much more
your in-house it's so much easier I'm
action
i cant pushed back quite a lot to say
okay no yeah
what you're wanting is not technically
sensible yeah because if this this
miss so I'm that's also a difficulty
again but a not in consultation but in
project management
depending on which side of the fence
upstanding yeah and again you here
you can easily say here that you again
they're looking like you have the
environment
who migrated to 2012 you are in need
how's the batf
you're a look at the BA I certainly am I
said
I and a lot more problem that we face
the
they so up to them how did you migrated
the logins
okay yeah so again because we're in
house we met with it's much easier we
running
on am using Active Directory accounts ok
and
and groups so we have access-group
bundled together to access to certain
server
I'm so if we're going to be setting up a
new instance and migrating from one
instance to another
then basically all we need to do set up
those loggins
on the new machine to make sure that
they have connect to repair permissions
and the permissions themselves I then
set within each database
so no this one big group
containing all the users which can
connect to this instance
and then inside each database separate
groups
recut depending on who they are to allow
them access into those databases
okay so you're not using C quote
indication will
whatsoever no no I'm we're trying to
move away from that
happy again complete in-house
development software so
we have a lot of flexibility to decide
on things like this
and M we'd had decided Active Directory
is the way we want to go
for a user I'm get you to control
in all respects and so use in Cabarrus
is so much easier to do
delegation and Holly such things so I'm
yeah we use a lot Active Directory you
know
probably someone to something that I i
its
obviously didn't need that is not that
lucky as you yes so what we saw today
was that we migrated the logins
hmm and it turned out that one up the
user's
was actually needing to
be able to query and to see the Activity
Monitor
and he was not a sysadmin so he needed
to be part of the performance user is
sucking group
yet but when his crib the logins that
permissions on wheels level
your help sick or so tried to bingo
so my suggestion brought me here is try
to script all the users
but think of whether or not they kept
Windows permission so also on that box
that s true
a data that's something I stated in my
in mine
top talking slovenians well yeah shins
permissions
and communication at the two pinpointing
the soft project
yeah communication because people always
be an issue but permissions
they will bite you there is no way I
did it will always happen its net it's
never generally a seriously terrible
situation that occurs
but it will be one permission like he
say okay performance indicators
damn in taken stumble say why is it not
working
are you have cross set down everything's
fine yeah
and blast probably last question
have you seen any problems with with the
collation some the database
this and Joe are instances where you
migrated a game
I'm really lucky we use the
the default collation the all-time
that's not there
is SAP and and SharePoint
and okay you can't beat up but our
instance collation is always the d4
and later clay shaw okay deck and the
issues but its
in general for a special election
appointing seem to be quite
clear the collation is in the database
and everything runs okay
I'm for yet if you've got collation
issues
I'm really sorry for you yes old if
you've sold look you then you should
at least say to the people that are
going to watch this
please be careful with your databases
and we had relations
yeah collisions a gain take it's very
easy to fall over on that one
yeah I'm you can't it Inc everything's
on the migration itself will be
wonderful
all the logins a mapped correctly and
everything like that and all the sadness
tonya normal queries you stop procedure
calls and then
that I'm collation issues and that yet
just collation recognition strict
yeah and here is the last one when you
are great
from one server not sober
from one person to another Hillview
actually RAM
any check yet rerun any any commands
on the databases for example like we're
and that they
davis's the check to be with data purity
really refreshed old abuse we
updated told us that this stinks with
falls down
and we did something else all we are we
run that the BCC updates usage
new companies yeah do you have any other
commands that should be you should be
executed
strength Inc and ordered or do you
advise
dole's for which I yes that can be run
yeah went ahead and took to always
ensure that your database is a startling
yes definitely I'm it's something that
we
I'm run on a regular basis an hour
standard backup procedure to
restore databases run data I'm DBCC
0 over the database to make sure you're
OK I'm so
betrayed she duckling something that we
always make a point I was well is to
check the database settings
some migrating salmon oldest system to a
new one
things like am and Hall
page validation so you've got am on
pitch detection of yeah
or verify yeah so I'm we always check to
make sure these things to say it
compatibility mode at the database
whether you want to move that up to the
newest version
and also Anna mmm also creating
statistics refreshing statistics quality
database settings basically
are they on the settings that you're
expecting so he can cope with
you you know what well
is just I was gonna say am it was make
upon when we're going to be moving
system
and you said you did like an eighty
database migration where
that's something that I Jan 12 and
I would always take it one moment one
database at a time but
one system at a time so maybe a product
which uses five databases 10
just those five together migrate them
do a test migration see everything's
okay that migrate them
and complete that take that off the list
and then say okay we'll take the next
because up with eighty databases
even if I'm gonna test system is a
completely different thing but on a
production system
the headaches can come from that would
be an absolute nightmare
the though approach that we're going to
take about the production databases will
be totally different
any doubts I but one more him
when when you are in the best face when
you're building up those VM's where you
test the new the new abortion I you
switching
are you also testing the new combat
ability levels
or you're giving them yeah now I'm we
implement
try and move to a new list version then
we try and get the database to the
cut the highest available version
and just to make sure that all features
are available and and whatnot so
and yeah me go through the whole
scenario we also
I'm check the setting so if a database
is one simple but for
we don't just take simple and take as
dats
what's going to be did after
consultation we speak with the
with the end user okay we're now
offering a new upgraded system much more
power
we can now offer better pitch a am are
you sure you're happy with simple would
you like to move into a different asl a
group
summit also so that's another another
check that
ever on calendar yes definitely BRB
obviously to see what you're moving onto
what systems you gonna go what
performance or availability want to
offer a few
offering different levels of service
to then speak with your dad uses your
customers in China
okay and you can move from an old system
to a new one maybe you can
offer an upgraded and service level
for free injected so actually it's worth
spending the time the area
and yeah okay so will not work a huge
huge huge pleasure
where are people going to be able to see
you next
I okay is the only year this year I'm
hoping to speak I've submitted to speak
in am exit in the UK
for the next legal Saturday that would
be in much ok
and I would like to keep my eye out for
sequel bits: in the UK and I'm gonna be
submitting nap
if I get chosen I've no idea I would
love to go back to sleep in your own
I really enjoy for sure I am going to be
in Slovenia for sure
I hope so my hopes up and generally
I'm keep my eyes open for anywhere else
I'm spycam open for
this auditing remote after Ramon
Sessions before
so yeah I'm if there are any good
opportunities I'm
open for any so in the meantime people
are going to be able to follow you on
both Twitter and on your block
I'll pose the lens there excellent
together with the video so it will be up
huge collection of resources
her a huge collection the places where
people are going to be able to
hear from you actual okay thanks very
much
it was it this also a really really I
believe this was a really
interesting can really cock in a state
like
lots a low of knowledge what's past
here at local college for the people who
are just court
just now going to consolidate about to
take from here and lessons learnt
Rep this is the best so yeah this is the
best good for the soul
hope to hear from you see hope to hear
from you soon and actually hope to see
you soon
you will thank you have made always a
pleasure
things and by night by
and