Planning a SQL Server 2012 Installation.
So planning is all about understanding, understanding
what kind of installation we're going to put on our
servers, and not only that but preparing our servers and our
infrastructure for that kind of installation.
And really, planning can be the difference between a
smooth road managing SQL Server and a rocky road.
Because if we accidentally install the wrong edition
where we say, down the road, oops, we needed that feature,
well, guess what?
Now we're in trouble, because we're going to have to
reinstall the right edition to support it.
So it's all about just understanding not only the
kind of installation but the kind of environment we're
going to need to prepare for installation, the kind of
application, the kind of databases
it's going to support.
So it's really just an information-gathering phase to
understand what kind of installation we need so we can
get the right components, the right features, and we have
the right hardware and software for a SQL Server,
supporting a SQL Server 2012 instance.
So a brief look at the Nugget overview will show us that
we've got a few things to talk about when it comes to
planning a SQL Server installation.
And these are also, by the way, the exam objectives for
70-462 for this Nugget.
And planning is, again, an important part of your
installation, because it's going to affect us well beyond
just the installation.
So we need to ensure that we have the right hardware, the
right software, the right components and features.
And so we're going to start here with evaluating the
installation requirements.
And really, this is just all about getting an understanding
of what type of install we're doing.
Because again, that hardware requirements, the software
requirements, the features and the components are all going
to determine what edition we need to select.
So we'll go over all that good stuff.
And we'll talk about designing an installation.
Here, this is just understanding the difference
between shared components and instance components.
We'll talk about scale up versus scale out.
As time goes on, the stress on our resources on our SQL
Server is going to go up.
So once we hit that slow down, what are we going to do?
Are we going to beef up our server?
Or are we going to add more SQL Servers and implement some
kind of load balancing strategy?
We'll also get the scoop on capacity planning and how we
deal with our databases growing.
Do we grow them on demand?
Do we set up fixed sizes?
How do we deal with shrinking, auto-shrinking and
auto-growing?
So we'll cover all those topics.
We'll talk about designing new databases, what level of
hardware RAID we should implement for our disk storage
technology.
And how do we set up our physical structure of our
database and the logical structure of our database.
We'll also take a look at how to identify standby database
for reporting here.
We'll talk about log shipping and database mirroring,
replication, and the new always-on availability groups.
We'll also talk about Windows and service-level security,
and how we can set up our user accounts, and how we can set
up our services to run under these accounts, what type of
accounts we should use, and what kind of permissions they
should have.
We'll also take a look at the requirements for installing
SQL Server in Windows 2008 Server Core mode.
And if you're not familiar with Server Core mode, it's
really Windows Server 2008 without all the bells and
whistles and without even a GUI.
There's no interface.
You communicate with it using the command line.
And what's neat about this is Microsoft claims it's far more
secure, because it doesn't have all the bells and
whistles and security vulnerabilities that a user
interface and all those extra services that are
running on it have.
And it's much better from a performance standpoint.
So a SQL Server 2012, the first version of SQL Server
that can be installed on Windows Server Core mode.
So we'll take a look at the requirements for doing that.
And at the very end here, I'll get you familiar with some
benchmarking tools --
SQL I/O and SQL I/O Sim.
Some great tools that we can use to benchmark our disk
subsystem for performance and for stress testing.
So you can see there's a lot to think about when it comes
to planning a SQL Server installation.
And the better understanding we have of a majority of these
areas, the better prepared we'll be for the installation
and well beyond into our production environment.
So let's get to it.
So when it comes to installation requirements,
it's all about gathering this information.
Because the more you know about what components and
features you need, what your target hardware is, what your
operating system is, and then if you have any virtualization
requirements, all of these together are going to help you
make that decision on what edition
you're going to install.
Because if you don't need integration services or
reporting services or analysis services or full-text search,
then you're probably not going to need some of
the advanced editions.
On the flip side of that, the operating
system is a big deal.
That's really what's going to drive your decision on what
edition you're going to use.
Because if you are installing this on a Windows 7 or a
Windows Vista machine, then you're going to
be limited to Standard--
Express editions.
On the other hand, if you're installing this on Windows
Server 2008 R2 SP1, you're going to be able to install it
on any edition.
And by the way, Books Online has a nice chart that shows
you the editions and what operating systems support
which editions.
So refer to that for more information.
Also, it's good to know the minimum and recommended
hardware requirements.
For both 64- and 32-bit editions, 2 gigahertz is going
to be the recommended for your CPU.
Memory is going to be four gigabytes.
And your disk requirements across the board, as far as
storage goes, a full SQL Server installation with all
components requires about 6 gigabytes of disk space.
It's good to note the operating system
prerequisites, especially on older operating systems.
On anything after Windows 7, most of
these should be preloaded.
The only thing is .NET4, SQL Server will attempt to
download and install if it's not on one of these machines.
Other than that, these other ones, you're on your own.
If you're on an older operating system, you'll need
to get them on yourself.
Also, virtualization requirements.
Again, SQL Server is cloud-ready, and it is fully
supported under Hyper-V in Windows Server 2008 R2 SP1 or
Windows Server 2008 SP2.
Their only recommendation is if you power down a virtual
instance, that you shut down the SQL Server Services prior
to powering down the virtual instance.
Finally here, when it comes to installation requirements,
designing your installation is just choosing what
components you need.
And there's two types of components when
it comes to an install.
There's instance components and shared components.
Instance components, you have a service for every instance.
And some of these things are things like the database
engine.
d you install SQL Server, you install an instance, it's
going to get a service named after that instance.
If you install two or three instances, you'll have two or
three services all named after that instance.
Other things are like replication, full-text search,
data quality services, all of these reporting services.
All of these are instance components, because each
instance needs its own separate service to run under.
Shared components, all instances on
a machine can share.
Things like the documentation, SQL Server Management Studio,
SQL Server data tools, all of these things you
only need one of.
And you can work with all of those different instances
using that one tool.
Now, I just wanted to touch on the editions as well here.
These bottom three are all free editions.
And Web is good for ISPs and web providers and such.
Developer is pretty much Enterprise edition for
developers.
You can build applications against it using all the
advanced features.
It's just not supported or licensed for a production
environment.
And Express is great for hobbyists and students and for
testing and training on.
And there's a lot of different versions of Express,
everything from just the database all the way to your
basic installation all the way to your advanced Express
edition with all the tools and components, which is the one
we're going to install in a future Nugget and use for
demonstrations and hands-on training.
So the three big ones are going to be Standard, Business
Intelligence, and Enterprise.
Standard's going to be good for your small- to
medium-sized businesses.
It supports up to 16 processor cores, and it still has
integration services, reporting services.
It just lacks some of the advanced
capabilities of each of those.
Business Intelligence is a brand new edition.
It also supports 16 cores.
But for reporting services and analysis services, it supports
the OS maximum for cores.
So that's the advantage you get over this.
And you get all the advanced features and tools, things
like master data services and some of the more advanced
Business Intelligence features.
And finally, we have the Enterprise edition, which
supports everything.
It's got an OS max across the board as far as processing
cores and supports all the advanced security features,
data warehousing features, and that.
And it's what you'll need for the serious SQL Server
installation.
All right.
Let's move on here, talk a little bit about scaling up
versus scaling out and capacity planning.
So scalability and capacity refer to how we're going to
handle growth at the server level and at
the database level.
Starting at the server level, as concurrent users go up, the
demand on our server's going to go up, which equates to our
resources going down and our performance going down.
So we've got a couple of different strategies to handle
when this happens.
We've got scaling up and scaling out.
Scaling up simply means to add more hardware to
our existing server.
So we could add faster processors, more memory,
faster and more hard drives.
But really, we're just
increasing our server resources.
Scaling out means to add more or less powerful servers into
the mix that all work together through load balancing or
technologies like always-on availability groups, standby
servers and such to offload some of that load off of the
main server.
So which one will you choose?
Well, that's really going to come down
to a couple of factors.
The big one's probably going to be the financial decision.
Which one is cheaper?
That's what it usually comes down to.
And a lot of times, that's going to depend.
Is adding more servers going to up your electricity costs,
your management costs?
But at the same time and depending on what your target
hardware is for scaling up, if you need super beefy hardware
to even make a difference, that may be more expensive.
So it's probably going to come down to a financial decision.
But some applications may not scale well, especially older
ones, by adding more servers to it.
It may make very little to no difference at all adding more
servers with load balancing if the application is terrible at
executing queries anyway.
And adding much more powerful hardware may be the solution.
Now, on the database side, we need to deal
with expanding databases.
Just like the universe, our databases
are constantly expanding.
And what happens when things expand?
Everything inside of them gets further apart.
And that's called fragmentation.
Fragmentation is a performance no-no.
We get into performance issues when files get fragmented.
And think about it.
At the operating system level, what's one of the first things
people do when they start experiencing, in
their system, slowdowns?
They turn on the defragger and go to sleep and hopefully wake
up the next morning to a faster machine.
Well, same thing in the database world, we have to
worry about this kind of stuff.
And this is where auto-growing can get you in trouble.
Because if we create a small database that grows on demand
a lot, well, that's going to cause a lot of fragmentation.
Now, I've got a really cool real world scenario that'll
help you with this a lot and actually give you great odds
at predicting how your database is going to be.
This is a solution I came up with a few years ago, because
I got into trouble with auto-growing.
Let me tell you, it wasn't cool.
And auto-growing worked great for the
first two, three months.
It only grew once every couple of months.
But as our data rates increased, a year down the
road, our database was auto-growing every other day.
And you have no idea what that does to performance when the
fragmentation shoots up to 90% after two days.
Not good.
So here's what I did.
I created a table.
And then I put on a SQL job, just a very simple query that
hit up the system views, which there's some very easy queries
you can run to find out the size of your database.
You can find out the size of every table in your database,
every object in your database, your indexes.
So I just wrote a very simple query that just grabbed all
the sizes of my tables and then the size of the database.
And every night, this job would run.
And after a year, I had an incredible amount of data that
I could trend out, throw into a report, that would give me
exactly how fast, what rate my data was
growing over the year.
And then I could easily predict what the sizes would
be for the next couple of years going forward.
And that gave me a way to create not only a better
auto-growing schedule, but I could more accurately, when I
did auto-grow, allocate the amount of space that I needed
so I could auto-grow even less.
So that's the way to do it, certainly the way to do it.
And fixed database sizes can obviously reduce
fragmentation.
The less often you're auto-growing your database,
the less fragmentation is going to occur.
Another thing I do want to mention, especially the DBA
community frowns upon, is auto-shrinking.
Stay away from auto-shrinking.
It's totally unnecessary.
There's no difference between free space in the operating
system and free space inside of your data files.
In fact, there's actually--
I've read many articles out there over the years of people
lobbying Microsoft to remove the auto-shrink feature
completely, because it can get people into trouble.
The biggest problem with this is it causes index
fragmentation.
If you auto-shrink your database on a schedule, then
your index fragmentation is going to go through the roof,
which, again, will cause performance issues.
So if you do, if you must auto-shrink, just remember to
rebuild your indexes afterwards if you're doing it
inside of an SSIS package or a SQL job or what not.
Just always remember to re-index, rebuild your indexes
afterwards.
Oh, and I do want to mention, alerts are great for this kind
of stuff, especially in the beginning when you may not
know how your data is going to grow.
Set up an alert to send you an email or a text message when
your database gets to 80% capacity.
And then you have a little more
control over the situation.
You can do it manually, at least in the beginning, of a
database's lifetime.
Always a good idea.
Another thing to be aware of is the capacity limits in SQL
Server 2012.
Again, just Books Online has a great chart that has all of
the capacity limits.
Just do a search in it for capacity.
But things like you can have up to 50
instances on a SQL Server.
An instance can contain up to 32,000 databases.
A database can be up to 520,000 terabytes in size.
That kind of stuff can be good information, especially if you
may come across an extreme installation that may run up
against those.
Good to know at least where to find them.
Now, when it comes time to design new databases, the big
things we need to think about are going to be this disk
subsystem, so the actual physical disks that our data
files are going to reside on, the physical
structure of our database.
And that's going to come in the form of our MDF files, our
NDF files, our LDF files.
And then also the logical structure of our database,
which is going to come in the form of filegroups.
So let's start here with the actual storage technology.
So RAID stands for Redundant Array of Independent Disks.
And it's a storage technology that's been around for a
while, so it's tried, true, and tested.
And it's just really a way that multiple hard drives can
work together to give us these performance and fault
tolerance benefits.
And there's a lot of different levels of RAID.
There's RAID 0, there's RAID 1, there's RAID 5, there's
RAID 10, otherwise known as RAID 1+0.
And there's even RAID 0+1.
So SQL Server works best with RAID 10 and RAID 5, because
these both give us performance and fault
tolerance benefits together.
So let's talk a little bit about these.
Now, first RAID 0 by itself.
What's RAID 0?
RAID 0 is known as disk striping.
And RAID 0 by itself gives us performance gains but doesn't
give us any fault tolerance gains.
And how this works is, let's say that we had four hard
drives, hard disks.
If we were to do a write operation against a physical
disk, in the file system, it would look
like one disk to us.
Call it the C drive.
So our C drive may look like the C drive, but really it's
on top of a RAID 0 array that has four disks underneath the
hood at the hardware level.
So if we were to write any data to our C drive, it would
get spread out across all four of those drives and written
asynchronously.
And that would give us a huge performance gain.
So that's RAID 0, disk striping.
RAID 1 is known as disk mirroring.
And it gives us our fault tolerance benefit, because
same scenario, let's say we had four hard drives.
If we were to write data to our C drive, underneath the
hood, it would write that data to all four of those drives.
So if one of those drives failed, no big deal, because
that data exists on the other three drives.
So if we combine them together, we get what's known
as RAID 10 or RAID 1+0.
And this is known as a stripe of mirrors, because it's the
best of both worlds.
So it really is the uber level of RAID.
But because of that, it's also the most expensive strategy or
solution here.
And that's because instead of four disks, now
we need eight disks.
We need four for the data, and we need four for the mirror.
And you can see that can add up really quickly, especially
when you start working with more disks.
Then it just gets exponentially more expensive.
So that's why a lot of people will, with budgets, a lot of
companies with budgets will fall back to RAID 5.
RAID 5 is really RAID 0 with fault tolerance provided
through what's known as parity information.
And parity information is stored across all the disks.
So that way, if something were to go down, all that data can
be reconstructed using all the disks.
So think of it as RAID 0 with a little bit of RAID 1,
because we get the fault tolerance.
It's just that instead of getting the fault tolerance in
a mirror redundant fashion, we get it by using the existing
disks and parity information to reconstruct any lost data.
Now, moving on.
The next big thing you need to think about is the physical
file structure of our database.
And we have three big files to work with here in SQL Server.
We have what's known as the MDF file, which is the
primary data file.
We have one or more NDF files, which are called secondary
data files.
And we have LDF files, which is the transaction log.
So starting with the MDF here, the primary data file.
By default, if you create a database and that's it, no
secondary files, everything is going to get stuffed into this
primary data file.
Small databases, that may be OK; large databases, that's
not a good idea, because we have system objects in here
and everything else.
And all these system objects get accessed randomly on disk,
and that's obviously not good for performance.
Where if we break them out into NDF files and we
specifically put our data inside of here, they will get
accessed, the data itself will get accessed sequentially on
disk, which is great for performance.
And same with the transaction log.
So the general idea here, especially if performance is a
concern, is to break out your MDF and your NDFs, put your
data and tables and such on these secondary files and
leave the MDF to SQL Server to manage all the system objects.
Now, the trick here is that when you create a table, you
can't say, put it on this NDF file.
You can't do that.
That's why we have filegroups.
We use filegroups to put objects on specific disks and
specific files.
Because if we have multiple MDF files like we do here, we
have two NDF files on our sales filegroup.
If we see a Create Table on this sales filegroup, it's
going to spread the data out across these files.
Now, we also have the transaction log.
And think of the transaction log as just an audit trail
against all the actions performed on our database, so
all the inserts, updates, and deletes.
Anything that happens in our database gets logged.
And that's a good thing, because what that means is if
the power goes out on our database while it was in the
middle of an insert or an update or what we would call a
transaction, when SQL Server boots back up, it scans the
transaction log.
And if that transaction didn't quite complete and our data is
in an inconsistent state, it can roll back that transaction
on anything that it did in the database.
On the other side of that coin, if the transaction did
complete but it didn't get fully written or materialized
in the database, it can roll forward those changes.
So that's the whole point of the transaction log.
And that's another reason, because it's got so much
activity, to separate the transaction log into its own
RAID array or at least on its own physical disk, because
there's a lot of things going on in the transaction log.
So we want to separate that from the rest of our world.
All right, speaking of physical disks in RAID.
What if we put this all together?
RAID, filegroups, data files, and design a database?
Here's what I would do.
The very first thing you should do is get your
transaction log into its own RAID array, RAID 10
preferably, because RAID 10 is best for write performance.
And guess what?
Our transaction log is constantly getting written to.
So if you're thinking about performance, the first thing
you want to do is get your transaction log at the very
least on its own physical disk, ideally on its own RAID
10 array of disks.
Because again, lots of writes going here.
RAID 10 is best for writes.
And you want to separate it from--
because you don't want to be constantly fighting resources
with everything else that's going on,
especially your data files.
So that would be the first step and the first priority.
Get that transaction log on its own disk or
its own RAID array.
The next priority should be getting your data
files on its own array.
And ideally, you could probably get away with RAID 5.
But RAID 10, obviously, ideally.
And what I see a lot of people do for the big installs, which
is a really smart idea, is to break these out into their own
separate filegroups.
And you would have a read
filegroup and a write filegroup.
So let's say this is a read filegroup over here.
We would call this Sales Read filegroup.
And we would identify all of our tables that don't have a
lot of inserts and updates to them, just selects from them,
so people reading data out of the tables.
And we would put that table and that
data in this filegroup.
And we would identify what tables have a lot of inserts,
updates, and deletes on them, and put
that in its own filegroup.
So we could call this our Sales Write filegroup.
And then in our Read filegroup, we could do RAID 5.
In our Write filegroup, we could do RAID 10.
Otherwise, you could probably just get away with putting it
all on a RAID 10 array.
But again, this is really just-- it's going to depend on
your budget, your fault tolerance needs, your
performance needs.
It's going to depend on a lot of those factors.
But this is really the uber way of doing it.
And in fact, you'll even see people break indexes out into
their own filegroup.
And you can pop your indexes in on a RAID 5 array, because,
again, not a lot of writes against an index.
It's more of reading and
traversing the index structure.
So you could put that there as well.
And then your primary filegroup, you really just
want redundancy.
You're not really concerned about performance on here,
because SQL Server is really using this
at the system level.
So you could probably put this on a RAID 1 array, of if you
really wanted to, a RAID 5.
And if performance is paramount, then you'll also
want to put your TempDB, which SQL Server does
a lot of with data.
It does a lot of temporary storage in here
for data and such.
You'll want to get this on a RAID 5 or RAID 10 as well.
Now, moving on.
How can we create a standby database
for reporting purposes?
And we've got a lot of technologies
we can use for this.
And some of these technologies are fault tolerance
technologies.
But we can still use them to get some reporting off them,
to do what we call shed load, get some resources pointed to
other databases to spread the performance across databases
and potentially across servers.
So the first option we have here is log shipping.
And log shipping, speaking of the transaction log, is just
what it sounds like.
We're shipping off the transaction log to one or more
secondary databases or even databases on other servers.
And how this works, it's really just
a three-step process.
And it relies on the old, tried, and true backup and
restore process.
This runs off SQL jobs.
So you can have a SQL job kickoff that will take your
primary, or otherwise known as the principal database, it'll
back it up, send that transaction log or that
backup, I should say, over to a secondary server, and then
restore it over there.
And then it'll do that for every secondary database tied
to the job.
So that's one way of doing it.
And this is known in the fault tolerance
world as a warm standby.
It's warm, because there's no automatic failover.
You would actually, if our primary database failed or was
down or got corrupted or whatever reason, we would
actually physically have to go over to one of those secondary
databases and turn them on or bring them up.
And we'd have to point out the client
applications to them as well.
So that's why it's called a warm standby, because there's
some manual work.
But as far as reporting purposes, as long as those
secondary databases aren't in a restore process, you can use
them to send other connections to.
So that's really one option for shedding load, spreading
the love around a little bit, as we like to call that.
Database mirroring is another option.
With database mirroring, it's only a one-to-one
relationship, as you can see here.
This is kind of a database mirror what I
have going on here.
And a database mirror is really
used for fault tolerance.
And this is considered a hot standby, because the mirror
database is always, or at least very close to in sync
with the principle database, the primary database.
And if that primary database goes down, the secondary
database can recognize it and come right back up.
And the really nice thing about this is applications
built with some of the latest .NET technology can also
recognize this and automatically switch to client
applications to use the mirrored database.
So again, more mirroring use for fault tolerance.
But you can use it for reporting purposes, but only
if you use a snapshot, if you take a snapshot of that mirror
and then point everything at the snapshot.
So you can't really use a mirrored database, because
it's always in use, it's always trying to stay
synchronized with the principle database.
And finally here, we have AlwaysOn availability groups.
AlwaysOn, brand new in SQL Server 2012.
And it's really cool technology.
It really takes the best of all these worlds
and combines them.
And it's actually physically going to
replace database mirroring.
And they say within two versions of now, database
mirroring will be deprecated and replaced by AlwaysOn.
So what is AlwaysOn?
Well, it's really the best of all these worlds.
Because in the database mirroring world, we can only
do a primary and then a mirror, or as we call it, the
principal and then the mirror.
That's it.
It's a one-to-one relationship.
With AlwaysOn, we can have up to four, what we call,
replicas, four sets of replicas.
Not only that, but instead of being one database against one
database, we can actually have multiple databases that
failover together in a set.
And then again, so we have our primary set and then we can
have up to four replicas.
Not only that, but those replicas can be marked as
read-only where then we can use them as a reporting
database, and we don't have to worry about them being
partially available like log shipping.
If they were in the Restore mode, we couldn't access the
reporting database.
Well, now with AlwaysOn, we can always access those
read-only replicas.
And with database mirroring, you don't have
to worry about snapshots.
And we don't have to worry about the complexities of
replication, because it's very easy to
work with and configure.
So we've got a Nugget dedicated to database
mirroring, a Nugget dedicated to replication, and, of
course, a Nugget dedicated to AlwaysOn.
So we'll take a good look at all these great and very handy
technologies.
Again, Server Core mode, as I mentioned earlier in this
Nugget, can be installed.
We can install SQL Server, all editions on Server Core mode.
The only thing we need to be careful of here is it can only
be done on a Windows Server 2008 R2 SP1 or greater server.
And we do this installation from the command line, because
that's how we work with Server Core mode.
We work with it through the command line.
Now, another thing to think about and plan ahead for is
service security.
What accounts are we going to use to run our services under?
And one of the first things you should think about here is
ensuring that these accounts have minimal rights.
You'll see a lot of people that set up all their services
under the same domain administrator.
And that's not a good idea for security reasons.
But also, when you're using domain accounts, now you have
to manage passwords.
And a lot of-- especially with passwords policies these days,
you're constantly changing passwords.
And if you're constantly changing passwords on your
accounts, well, you're going to get a nice surprise when
you reboot your SQL Server or you reboot your SQL Service.
And all of a sudden, your services don't start up, and
you're left scratching your head and figuring out why.
So that's one thing to think about.
And how you can get around that is you can use Managed
Service Accounts, MSAs.
These are the accounts that have the dollar sign
prefixed-- or suffixed, I should say, at the very end of
the account.
And also, virtual accounts are another good one, because
virtual accounts are anything under the NT service and then
backslash, service name, is one way that you can create
these accounts.
But these are great, because their passwords are--
and if you think about it, if there are any passwords on,
they're managed by Windows, so you'll never know them.
And these accounts are managed by Windows and they have
minimal rights, so they're perfect candidates for running
services under.
Another good idea is to isolate your services, each of
them with a different dedicated account.
Because, again, especially if you're using domain accounts
to do this, because if one of those accounts becomes
compromised, then not all of your services are compromised.
Finally here, we have benchmarking.
And this is a great way to ensure, before we go live into
a production environment, that our disk subsystem can handle
what it's about to get thrown at it from our databases and
our servers.
So we can do stress and performance testing.
For performance testing, you want to use SQLIO, which does
not come bundled with your SQL Server installation.
But you can get it from Microsoft's download area on
their website.
And I'll download it and put it into our Support files over
in our virtual Nugget Lab for 70-462.
But this is a great tool to use to really performance test
our disk subsystem and our input, output, and our
throughput, and all that stuff.
And IO is a great tool that can give us some nice charts
and show us exactly how our-- and you know how this works?
It actually takes a file--
We can say, hey, here's a file of this size.
And we can make it the size that we project our database
to be, so we can get a pretty accurate representation of
what kind of database and what kind of performance we're
going to see based on the size that we're predicting our
database to be.
We can configure this file to be a specific size, and then
SQL Server will throw a bunch of-- or SQLIO, I should say,
will throw a bunch of read, writes against the disk based
on that file size and give us some nice numbers and data
here to see exactly how the disk and
the subsystem performed.
So I use SQLIO for performance testing and SQLIO Sim, which
does come bundled with SQL Server and replaces SQLIO
Stress and previous versions of SQL Server, to do disk
integrity checks.
And it does this unlike SQLIO, which is going to send as many
reads and writes as possible at it to do
a performance test.
But a stress test is more it's going to do a lot of different
kinds of read patterns and write patterns to see if the
disk can handle it.
So these are just great tools that you can use to optimize
your hardware configuration.
All right.
So in this CBT Nugget, we took a look at planning a SQL
Server 2012 installation and all the things we need to
think about before getting SQL Server up and running.
We started with evaluating those installation
requirements.
That's just going to come down to understanding what
components and technologies in SQL Server we need to install,
getting a good handle on software requirements,
hardware requirements, if we're going to do
virtualization, what edition of SQL Server are we going to
use, what kind of features we're going to install,
whether they're instance features or shared features.
And we even saw how to plan for the future in scaling up
versus scaling out, in capacity planning.
How are we going to handle shrinking
and growing of databases.
How are we going to handle designing new databases?
What RAID technology are we going to use to put these
databases on?
How are we going to design the physical structure of our
database in our MDF primary files, our secondary NDF
files, and our transaction log LDF file?
And what logical filegroups are we going to use to contain
those files and essentially write our SQL
Server objects to them?
We took a look at what technologies we're going to
look at here in this series when it comes to standby
databases for reporting purposes such as log shipping,
database mirroring, replication, and the new
AlwaysOn availability groups, most of those which we'll take
a good look at in this series.
We saw that SQL Server 2012 can be installed
in Server Core mode.
We took a look at Windows Server service security best
practices and what kind of accounts we
should be working with.
And at the end here, just what tools we'll be using to do
benchmarks and how benchmarking can really help
us understand our disks and really find out what their
capacity limits are.
I hope this has been informative for you, and I
thank you for viewing.