자막 자료실

Ah**********

-

2018-02-19

통합

294

02 - Planning a SQL Server 2012 Installation [0 B]

02 - Planning a SQL Server 2012 Installation.srt [44.17 KB]

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