1 00:00:00,000 --> 00:00:01,060 2 00:00:01,060 --> 00:00:05,020 Planning a SQL Server 2012 Installation. 3 00:00:05,020 --> 00:00:07,710 So planning is all about understanding, understanding 4 00:00:07,710 --> 00:00:10,030 what kind of installation we're going to put on our 5 00:00:10,030 --> 00:00:13,140 servers, and not only that but preparing our servers and our 6 00:00:13,140 --> 00:00:15,430 infrastructure for that kind of installation. 7 00:00:15,430 --> 00:00:18,150 And really, planning can be the difference between a 8 00:00:18,150 --> 00:00:21,340 smooth road managing SQL Server and a rocky road. 9 00:00:21,340 --> 00:00:24,050 Because if we accidentally install the wrong edition 10 00:00:24,050 --> 00:00:26,770 where we say, down the road, oops, we needed that feature, 11 00:00:26,770 --> 00:00:27,400 well, guess what? 12 00:00:27,400 --> 00:00:28,610 Now we're in trouble, because we're going to have to 13 00:00:28,610 --> 00:00:30,950 reinstall the right edition to support it. 14 00:00:30,950 --> 00:00:33,360 So it's all about just understanding not only the 15 00:00:33,360 --> 00:00:35,870 kind of installation but the kind of environment we're 16 00:00:35,870 --> 00:00:38,450 going to need to prepare for installation, the kind of 17 00:00:38,450 --> 00:00:39,970 application, the kind of databases 18 00:00:39,970 --> 00:00:40,990 it's going to support. 19 00:00:40,990 --> 00:00:43,650 So it's really just an information-gathering phase to 20 00:00:43,650 --> 00:00:46,765 understand what kind of installation we need so we can 21 00:00:46,765 --> 00:00:49,400 get the right components, the right features, and we have 22 00:00:49,400 --> 00:00:52,970 the right hardware and software for a SQL Server, 23 00:00:52,970 --> 00:00:56,150 supporting a SQL Server 2012 instance. 24 00:00:56,150 --> 00:00:58,320 So a brief look at the Nugget overview will show us that 25 00:00:58,320 --> 00:01:01,370 we've got a few things to talk about when it comes to 26 00:01:01,370 --> 00:01:02,725 planning a SQL Server installation. 27 00:01:02,725 --> 00:01:04,790 And these are also, by the way, the exam objectives for 28 00:01:04,790 --> 00:01:06,675 70-462 for this Nugget. 29 00:01:06,675 --> 00:01:09,920 And planning is, again, an important part of your 30 00:01:09,920 --> 00:01:12,620 installation, because it's going to affect us well beyond 31 00:01:12,620 --> 00:01:14,090 just the installation. 32 00:01:14,090 --> 00:01:16,190 So we need to ensure that we have the right hardware, the 33 00:01:16,190 --> 00:01:18,690 right software, the right components and features. 34 00:01:18,690 --> 00:01:20,570 And so we're going to start here with evaluating the 35 00:01:20,570 --> 00:01:21,670 installation requirements. 36 00:01:21,670 --> 00:01:23,970 And really, this is just all about getting an understanding 37 00:01:23,970 --> 00:01:25,450 of what type of install we're doing. 38 00:01:25,450 --> 00:01:27,280 Because again, that hardware requirements, the software 39 00:01:27,280 --> 00:01:29,195 requirements, the features and the components are all going 40 00:01:29,195 --> 00:01:31,400 to determine what edition we need to select. 41 00:01:31,400 --> 00:01:32,700 So we'll go over all that good stuff. 42 00:01:32,700 --> 00:01:34,430 And we'll talk about designing an installation. 43 00:01:34,430 --> 00:01:35,960 Here, this is just understanding the difference 44 00:01:35,960 --> 00:01:38,980 between shared components and instance components. 45 00:01:38,980 --> 00:01:41,620 We'll talk about scale up versus scale out. 46 00:01:41,620 --> 00:01:45,140 As time goes on, the stress on our resources on our SQL 47 00:01:45,140 --> 00:01:46,410 Server is going to go up. 48 00:01:46,410 --> 00:01:49,670 So once we hit that slow down, what are we going to do? 49 00:01:49,670 --> 00:01:51,490 Are we going to beef up our server? 50 00:01:51,490 --> 00:01:54,080 Or are we going to add more SQL Servers and implement some 51 00:01:54,080 --> 00:01:56,550 kind of load balancing strategy? 52 00:01:56,550 --> 00:01:59,090 We'll also get the scoop on capacity planning and how we 53 00:01:59,090 --> 00:02:01,350 deal with our databases growing. 54 00:02:01,350 --> 00:02:02,430 Do we grow them on demand? 55 00:02:02,430 --> 00:02:04,190 Do we set up fixed sizes? 56 00:02:04,190 --> 00:02:07,190 How do we deal with shrinking, auto-shrinking and 57 00:02:07,190 --> 00:02:07,810 auto-growing? 58 00:02:07,810 --> 00:02:09,169 So we'll cover all those topics. 59 00:02:09,169 --> 00:02:12,100 We'll talk about designing new databases, what level of 60 00:02:12,100 --> 00:02:15,200 hardware RAID we should implement for our disk storage 61 00:02:15,200 --> 00:02:16,160 technology. 62 00:02:16,160 --> 00:02:18,560 And how do we set up our physical structure of our 63 00:02:18,560 --> 00:02:20,800 database and the logical structure of our database. 64 00:02:20,800 --> 00:02:23,235 We'll also take a look at how to identify standby database 65 00:02:23,235 --> 00:02:24,080 for reporting here. 66 00:02:24,080 --> 00:02:26,470 We'll talk about log shipping and database mirroring, 67 00:02:26,470 --> 00:02:29,540 replication, and the new always-on availability groups. 68 00:02:29,540 --> 00:02:33,130 We'll also talk about Windows and service-level security, 69 00:02:33,130 --> 00:02:36,400 and how we can set up our user accounts, and how we can set 70 00:02:36,400 --> 00:02:39,020 up our services to run under these accounts, what type of 71 00:02:39,020 --> 00:02:40,785 accounts we should use, and what kind of permissions they 72 00:02:40,785 --> 00:02:41,670 should have. 73 00:02:41,670 --> 00:02:44,090 We'll also take a look at the requirements for installing 74 00:02:44,090 --> 00:02:47,530 SQL Server in Windows 2008 Server Core mode. 75 00:02:47,530 --> 00:02:49,720 And if you're not familiar with Server Core mode, it's 76 00:02:49,720 --> 00:02:51,990 really Windows Server 2008 without all the bells and 77 00:02:51,990 --> 00:02:53,820 whistles and without even a GUI. 78 00:02:53,820 --> 00:02:54,570 There's no interface. 79 00:02:54,570 --> 00:02:57,440 You communicate with it using the command line. 80 00:02:57,440 --> 00:03:00,510 And what's neat about this is Microsoft claims it's far more 81 00:03:00,510 --> 00:03:02,975 secure, because it doesn't have all the bells and 82 00:03:02,975 --> 00:03:04,860 whistles and security vulnerabilities that a user 83 00:03:04,860 --> 00:03:07,200 interface and all those extra services that are 84 00:03:07,200 --> 00:03:08,450 running on it have. 85 00:03:08,450 --> 00:03:12,630 And it's much better from a performance standpoint. 86 00:03:12,630 --> 00:03:15,030 So a SQL Server 2012, the first version of SQL Server 87 00:03:15,030 --> 00:03:17,940 that can be installed on Windows Server Core mode. 88 00:03:17,940 --> 00:03:19,660 So we'll take a look at the requirements for doing that. 89 00:03:19,660 --> 00:03:21,870 And at the very end here, I'll get you familiar with some 90 00:03:21,870 --> 00:03:22,860 benchmarking tools -- 91 00:03:22,860 --> 00:03:25,245 SQL I/O and SQL I/O Sim. 92 00:03:25,245 --> 00:03:29,320 Some great tools that we can use to benchmark our disk 93 00:03:29,320 --> 00:03:33,120 subsystem for performance and for stress testing. 94 00:03:33,120 --> 00:03:34,850 So you can see there's a lot to think about when it comes 95 00:03:34,850 --> 00:03:36,890 to planning a SQL Server installation. 96 00:03:36,890 --> 00:03:39,460 And the better understanding we have of a majority of these 97 00:03:39,460 --> 00:03:43,420 areas, the better prepared we'll be for the installation 98 00:03:43,420 --> 00:03:45,520 and well beyond into our production environment. 99 00:03:45,520 --> 00:03:47,190 So let's get to it. 100 00:03:47,190 --> 00:03:48,820 So when it comes to installation requirements, 101 00:03:48,820 --> 00:03:51,600 it's all about gathering this information. 102 00:03:51,600 --> 00:03:53,920 Because the more you know about what components and 103 00:03:53,920 --> 00:03:56,790 features you need, what your target hardware is, what your 104 00:03:56,790 --> 00:03:59,470 operating system is, and then if you have any virtualization 105 00:03:59,470 --> 00:04:01,470 requirements, all of these together are going to help you 106 00:04:01,470 --> 00:04:03,840 make that decision on what edition 107 00:04:03,840 --> 00:04:04,980 you're going to install. 108 00:04:04,980 --> 00:04:08,540 Because if you don't need integration services or 109 00:04:08,540 --> 00:04:11,690 reporting services or analysis services or full-text search, 110 00:04:11,690 --> 00:04:12,840 then you're probably not going to need some of 111 00:04:12,840 --> 00:04:14,970 the advanced editions. 112 00:04:14,970 --> 00:04:16,800 On the flip side of that, the operating 113 00:04:16,800 --> 00:04:17,820 system is a big deal. 114 00:04:17,820 --> 00:04:21,149 That's really what's going to drive your decision on what 115 00:04:21,149 --> 00:04:22,000 edition you're going to use. 116 00:04:22,000 --> 00:04:25,110 Because if you are installing this on a Windows 7 or a 117 00:04:25,110 --> 00:04:26,890 Windows Vista machine, then you're going to 118 00:04:26,890 --> 00:04:28,440 be limited to Standard-- 119 00:04:28,440 --> 00:04:29,290 Express editions. 120 00:04:29,290 --> 00:04:31,320 On the other hand, if you're installing this on Windows 121 00:04:31,320 --> 00:04:35,420 Server 2008 R2 SP1, you're going to be able to install it 122 00:04:35,420 --> 00:04:37,250 on any edition. 123 00:04:37,250 --> 00:04:40,400 And by the way, Books Online has a nice chart that shows 124 00:04:40,400 --> 00:04:44,090 you the editions and what operating systems support 125 00:04:44,090 --> 00:04:44,810 which editions. 126 00:04:44,810 --> 00:04:46,480 So refer to that for more information. 127 00:04:46,480 --> 00:04:48,340 Also, it's good to know the minimum and recommended 128 00:04:48,340 --> 00:04:49,470 hardware requirements. 129 00:04:49,470 --> 00:04:52,720 For both 64- and 32-bit editions, 2 gigahertz is going 130 00:04:52,720 --> 00:04:54,700 to be the recommended for your CPU. 131 00:04:54,700 --> 00:04:56,610 Memory is going to be four gigabytes. 132 00:04:56,610 --> 00:04:58,870 And your disk requirements across the board, as far as 133 00:04:58,870 --> 00:05:01,770 storage goes, a full SQL Server installation with all 134 00:05:01,770 --> 00:05:05,530 components requires about 6 gigabytes of disk space. 135 00:05:05,530 --> 00:05:06,830 It's good to note the operating system 136 00:05:06,830 --> 00:05:09,450 prerequisites, especially on older operating systems. 137 00:05:09,450 --> 00:05:11,050 On anything after Windows 7, most of 138 00:05:11,050 --> 00:05:12,370 these should be preloaded. 139 00:05:12,370 --> 00:05:15,700 The only thing is .NET4, SQL Server will attempt to 140 00:05:15,700 --> 00:05:19,240 download and install if it's not on one of these machines. 141 00:05:19,240 --> 00:05:20,940 Other than that, these other ones, you're on your own. 142 00:05:20,940 --> 00:05:22,360 If you're on an older operating system, you'll need 143 00:05:22,360 --> 00:05:24,080 to get them on yourself. 144 00:05:24,080 --> 00:05:26,060 Also, virtualization requirements. 145 00:05:26,060 --> 00:05:29,330 Again, SQL Server is cloud-ready, and it is fully 146 00:05:29,330 --> 00:05:35,350 supported under Hyper-V in Windows Server 2008 R2 SP1 or 147 00:05:35,350 --> 00:05:37,560 Windows Server 2008 SP2. 148 00:05:37,560 --> 00:05:40,280 Their only recommendation is if you power down a virtual 149 00:05:40,280 --> 00:05:43,790 instance, that you shut down the SQL Server Services prior 150 00:05:43,790 --> 00:05:47,140 to powering down the virtual instance. 151 00:05:47,140 --> 00:05:49,320 Finally here, when it comes to installation requirements, 152 00:05:49,320 --> 00:05:51,610 designing your installation is just choosing what 153 00:05:51,610 --> 00:05:52,850 components you need. 154 00:05:52,850 --> 00:05:54,280 And there's two types of components when 155 00:05:54,280 --> 00:05:55,150 it comes to an install. 156 00:05:55,150 --> 00:05:57,710 There's instance components and shared components. 157 00:05:57,710 --> 00:06:01,800 Instance components, you have a service for every instance. 158 00:06:01,800 --> 00:06:03,610 And some of these things are things like the database 159 00:06:03,610 --> 00:06:03,970 engine. 160 00:06:03,970 --> 00:06:07,750 d you install SQL Server, you install an instance, it's 161 00:06:07,750 --> 00:06:10,570 going to get a service named after that instance. 162 00:06:10,570 --> 00:06:13,160 If you install two or three instances, you'll have two or 163 00:06:13,160 --> 00:06:17,070 three services all named after that instance. 164 00:06:17,070 --> 00:06:20,400 Other things are like replication, full-text search, 165 00:06:20,400 --> 00:06:23,820 data quality services, all of these reporting services. 166 00:06:23,820 --> 00:06:27,150 All of these are instance components, because each 167 00:06:27,150 --> 00:06:30,340 instance needs its own separate service to run under. 168 00:06:30,340 --> 00:06:32,850 Shared components, all instances on 169 00:06:32,850 --> 00:06:33,990 a machine can share. 170 00:06:33,990 --> 00:06:36,720 Things like the documentation, SQL Server Management Studio, 171 00:06:36,720 --> 00:06:39,030 SQL Server data tools, all of these things you 172 00:06:39,030 --> 00:06:40,260 only need one of. 173 00:06:40,260 --> 00:06:42,620 And you can work with all of those different instances 174 00:06:42,620 --> 00:06:44,490 using that one tool. 175 00:06:44,490 --> 00:06:47,080 Now, I just wanted to touch on the editions as well here. 176 00:06:47,080 --> 00:06:50,100 These bottom three are all free editions. 177 00:06:50,100 --> 00:06:54,330 And Web is good for ISPs and web providers and such. 178 00:06:54,330 --> 00:06:57,300 Developer is pretty much Enterprise edition for 179 00:06:57,300 --> 00:06:57,585 developers. 180 00:06:57,585 --> 00:06:59,680 You can build applications against it using all the 181 00:06:59,680 --> 00:07:00,560 advanced features. 182 00:07:00,560 --> 00:07:03,920 It's just not supported or licensed for a production 183 00:07:03,920 --> 00:07:04,420 environment. 184 00:07:04,420 --> 00:07:08,390 And Express is great for hobbyists and students and for 185 00:07:08,390 --> 00:07:09,710 testing and training on. 186 00:07:09,710 --> 00:07:11,760 And there's a lot of different versions of Express, 187 00:07:11,760 --> 00:07:13,900 everything from just the database all the way to your 188 00:07:13,900 --> 00:07:17,470 basic installation all the way to your advanced Express 189 00:07:17,470 --> 00:07:20,020 edition with all the tools and components, which is the one 190 00:07:20,020 --> 00:07:23,200 we're going to install in a future Nugget and use for 191 00:07:23,200 --> 00:07:25,530 demonstrations and hands-on training. 192 00:07:25,530 --> 00:07:27,890 So the three big ones are going to be Standard, Business 193 00:07:27,890 --> 00:07:29,390 Intelligence, and Enterprise. 194 00:07:29,390 --> 00:07:30,680 Standard's going to be good for your small- to 195 00:07:30,680 --> 00:07:32,030 medium-sized businesses. 196 00:07:32,030 --> 00:07:35,590 It supports up to 16 processor cores, and it still has 197 00:07:35,590 --> 00:07:37,770 integration services, reporting services. 198 00:07:37,770 --> 00:07:39,130 It just lacks some of the advanced 199 00:07:39,130 --> 00:07:40,870 capabilities of each of those. 200 00:07:40,870 --> 00:07:43,660 Business Intelligence is a brand new edition. 201 00:07:43,660 --> 00:07:45,640 It also supports 16 cores. 202 00:07:45,640 --> 00:07:49,440 But for reporting services and analysis services, it supports 203 00:07:49,440 --> 00:07:51,600 the OS maximum for cores. 204 00:07:51,600 --> 00:07:53,070 So that's the advantage you get over this. 205 00:07:53,070 --> 00:07:56,190 And you get all the advanced features and tools, things 206 00:07:56,190 --> 00:07:59,640 like master data services and some of the more advanced 207 00:07:59,640 --> 00:08:01,550 Business Intelligence features. 208 00:08:01,550 --> 00:08:03,480 And finally, we have the Enterprise edition, which 209 00:08:03,480 --> 00:08:04,760 supports everything. 210 00:08:04,760 --> 00:08:07,320 It's got an OS max across the board as far as processing 211 00:08:07,320 --> 00:08:10,770 cores and supports all the advanced security features, 212 00:08:10,770 --> 00:08:12,750 data warehousing features, and that. 213 00:08:12,750 --> 00:08:14,860 And it's what you'll need for the serious SQL Server 214 00:08:14,860 --> 00:08:15,905 installation. 215 00:08:15,905 --> 00:08:16,280 All right. 216 00:08:16,280 --> 00:08:18,820 Let's move on here, talk a little bit about scaling up 217 00:08:18,820 --> 00:08:22,010 versus scaling out and capacity planning. 218 00:08:22,010 --> 00:08:24,320 So scalability and capacity refer to how we're going to 219 00:08:24,320 --> 00:08:26,870 handle growth at the server level and at 220 00:08:26,870 --> 00:08:27,580 the database level. 221 00:08:27,580 --> 00:08:31,660 Starting at the server level, as concurrent users go up, the 222 00:08:31,660 --> 00:08:34,900 demand on our server's going to go up, which equates to our 223 00:08:34,900 --> 00:08:37,460 resources going down and our performance going down. 224 00:08:37,460 --> 00:08:40,390 So we've got a couple of different strategies to handle 225 00:08:40,390 --> 00:08:41,179 when this happens. 226 00:08:41,179 --> 00:08:43,370 We've got scaling up and scaling out. 227 00:08:43,370 --> 00:08:46,740 Scaling up simply means to add more hardware to 228 00:08:46,740 --> 00:08:48,510 our existing server. 229 00:08:48,510 --> 00:08:51,220 So we could add faster processors, more memory, 230 00:08:51,220 --> 00:08:53,080 faster and more hard drives. 231 00:08:53,080 --> 00:08:53,830 But really, we're just 232 00:08:53,830 --> 00:08:56,040 increasing our server resources. 233 00:08:56,040 --> 00:08:59,610 Scaling out means to add more or less powerful servers into 234 00:08:59,610 --> 00:09:02,570 the mix that all work together through load balancing or 235 00:09:02,570 --> 00:09:06,450 technologies like always-on availability groups, standby 236 00:09:06,450 --> 00:09:10,310 servers and such to offload some of that load off of the 237 00:09:10,310 --> 00:09:10,930 main server. 238 00:09:10,930 --> 00:09:12,750 So which one will you choose? 239 00:09:12,750 --> 00:09:14,130 Well, that's really going to come down 240 00:09:14,130 --> 00:09:15,330 to a couple of factors. 241 00:09:15,330 --> 00:09:17,450 The big one's probably going to be the financial decision. 242 00:09:17,450 --> 00:09:19,630 Which one is cheaper? 243 00:09:19,630 --> 00:09:21,000 That's what it usually comes down to. 244 00:09:21,000 --> 00:09:22,970 And a lot of times, that's going to depend. 245 00:09:22,970 --> 00:09:24,780 Is adding more servers going to up your electricity costs, 246 00:09:24,780 --> 00:09:27,010 your management costs? 247 00:09:27,010 --> 00:09:29,010 But at the same time and depending on what your target 248 00:09:29,010 --> 00:09:32,930 hardware is for scaling up, if you need super beefy hardware 249 00:09:32,930 --> 00:09:35,250 to even make a difference, that may be more expensive. 250 00:09:35,250 --> 00:09:37,770 So it's probably going to come down to a financial decision. 251 00:09:37,770 --> 00:09:41,000 But some applications may not scale well, especially older 252 00:09:41,000 --> 00:09:43,690 ones, by adding more servers to it. 253 00:09:43,690 --> 00:09:46,200 It may make very little to no difference at all adding more 254 00:09:46,200 --> 00:09:49,780 servers with load balancing if the application is terrible at 255 00:09:49,780 --> 00:09:51,410 executing queries anyway. 256 00:09:51,410 --> 00:09:56,990 And adding much more powerful hardware may be the solution. 257 00:09:56,990 --> 00:09:59,870 Now, on the database side, we need to deal 258 00:09:59,870 --> 00:10:01,260 with expanding databases. 259 00:10:01,260 --> 00:10:03,840 Just like the universe, our databases 260 00:10:03,840 --> 00:10:04,870 are constantly expanding. 261 00:10:04,870 --> 00:10:06,530 And what happens when things expand? 262 00:10:06,530 --> 00:10:09,150 Everything inside of them gets further apart. 263 00:10:09,150 --> 00:10:11,970 And that's called fragmentation. 264 00:10:11,970 --> 00:10:14,500 Fragmentation is a performance no-no. 265 00:10:14,500 --> 00:10:17,590 We get into performance issues when files get fragmented. 266 00:10:17,590 --> 00:10:18,910 And think about it. 267 00:10:18,910 --> 00:10:21,560 At the operating system level, what's one of the first things 268 00:10:21,560 --> 00:10:23,050 people do when they start experiencing, in 269 00:10:23,050 --> 00:10:24,720 their system, slowdowns? 270 00:10:24,720 --> 00:10:26,920 They turn on the defragger and go to sleep and hopefully wake 271 00:10:26,920 --> 00:10:28,690 up the next morning to a faster machine. 272 00:10:28,690 --> 00:10:30,630 Well, same thing in the database world, we have to 273 00:10:30,630 --> 00:10:32,000 worry about this kind of stuff. 274 00:10:32,000 --> 00:10:34,860 And this is where auto-growing can get you in trouble. 275 00:10:34,860 --> 00:10:39,800 Because if we create a small database that grows on demand 276 00:10:39,800 --> 00:10:43,830 a lot, well, that's going to cause a lot of fragmentation. 277 00:10:43,830 --> 00:10:46,650 Now, I've got a really cool real world scenario that'll 278 00:10:46,650 --> 00:10:50,010 help you with this a lot and actually give you great odds 279 00:10:50,010 --> 00:10:53,260 at predicting how your database is going to be. 280 00:10:53,260 --> 00:10:56,520 This is a solution I came up with a few years ago, because 281 00:10:56,520 --> 00:10:58,820 I got into trouble with auto-growing. 282 00:10:58,820 --> 00:11:00,890 Let me tell you, it wasn't cool. 283 00:11:00,890 --> 00:11:02,130 And auto-growing worked great for the 284 00:11:02,130 --> 00:11:03,000 first two, three months. 285 00:11:03,000 --> 00:11:05,340 It only grew once every couple of months. 286 00:11:05,340 --> 00:11:08,100 But as our data rates increased, a year down the 287 00:11:08,100 --> 00:11:11,230 road, our database was auto-growing every other day. 288 00:11:11,230 --> 00:11:14,590 And you have no idea what that does to performance when the 289 00:11:14,590 --> 00:11:17,490 fragmentation shoots up to 90% after two days. 290 00:11:17,490 --> 00:11:18,450 Not good. 291 00:11:18,450 --> 00:11:19,610 So here's what I did. 292 00:11:19,610 --> 00:11:21,390 I created a table. 293 00:11:21,390 --> 00:11:25,060 And then I put on a SQL job, just a very simple query that 294 00:11:25,060 --> 00:11:29,460 hit up the system views, which there's some very easy queries 295 00:11:29,460 --> 00:11:31,890 you can run to find out the size of your database. 296 00:11:31,890 --> 00:11:34,440 You can find out the size of every table in your database, 297 00:11:34,440 --> 00:11:36,160 every object in your database, your indexes. 298 00:11:36,160 --> 00:11:39,640 So I just wrote a very simple query that just grabbed all 299 00:11:39,640 --> 00:11:42,400 the sizes of my tables and then the size of the database. 300 00:11:42,400 --> 00:11:44,990 And every night, this job would run. 301 00:11:44,990 --> 00:11:49,090 And after a year, I had an incredible amount of data that 302 00:11:49,090 --> 00:11:52,360 I could trend out, throw into a report, that would give me 303 00:11:52,360 --> 00:11:55,740 exactly how fast, what rate my data was 304 00:11:55,740 --> 00:11:57,600 growing over the year. 305 00:11:57,600 --> 00:12:00,140 And then I could easily predict what the sizes would 306 00:12:00,140 --> 00:12:03,070 be for the next couple of years going forward. 307 00:12:03,070 --> 00:12:07,760 And that gave me a way to create not only a better 308 00:12:07,760 --> 00:12:12,070 auto-growing schedule, but I could more accurately, when I 309 00:12:12,070 --> 00:12:14,980 did auto-grow, allocate the amount of space that I needed 310 00:12:14,980 --> 00:12:16,930 so I could auto-grow even less. 311 00:12:16,930 --> 00:12:19,370 So that's the way to do it, certainly the way to do it. 312 00:12:19,370 --> 00:12:22,270 And fixed database sizes can obviously reduce 313 00:12:22,270 --> 00:12:22,990 fragmentation. 314 00:12:22,990 --> 00:12:26,470 The less often you're auto-growing your database, 315 00:12:26,470 --> 00:12:29,180 the less fragmentation is going to occur. 316 00:12:29,180 --> 00:12:32,860 Another thing I do want to mention, especially the DBA 317 00:12:32,860 --> 00:12:35,030 community frowns upon, is auto-shrinking. 318 00:12:35,030 --> 00:12:36,230 Stay away from auto-shrinking. 319 00:12:36,230 --> 00:12:38,340 It's totally unnecessary. 320 00:12:38,340 --> 00:12:40,630 There's no difference between free space in the operating 321 00:12:40,630 --> 00:12:43,400 system and free space inside of your data files. 322 00:12:43,400 --> 00:12:44,620 In fact, there's actually-- 323 00:12:44,620 --> 00:12:47,970 I've read many articles out there over the years of people 324 00:12:47,970 --> 00:12:51,060 lobbying Microsoft to remove the auto-shrink feature 325 00:12:51,060 --> 00:12:54,760 completely, because it can get people into trouble. 326 00:12:54,760 --> 00:12:56,620 The biggest problem with this is it causes index 327 00:12:56,620 --> 00:12:57,180 fragmentation. 328 00:12:57,180 --> 00:13:00,390 If you auto-shrink your database on a schedule, then 329 00:13:00,390 --> 00:13:03,540 your index fragmentation is going to go through the roof, 330 00:13:03,540 --> 00:13:06,180 which, again, will cause performance issues. 331 00:13:06,180 --> 00:13:10,490 So if you do, if you must auto-shrink, just remember to 332 00:13:10,490 --> 00:13:12,900 rebuild your indexes afterwards if you're doing it 333 00:13:12,900 --> 00:13:15,860 inside of an SSIS package or a SQL job or what not. 334 00:13:15,860 --> 00:13:19,310 Just always remember to re-index, rebuild your indexes 335 00:13:19,310 --> 00:13:20,580 afterwards. 336 00:13:20,580 --> 00:13:23,390 Oh, and I do want to mention, alerts are great for this kind 337 00:13:23,390 --> 00:13:25,870 of stuff, especially in the beginning when you may not 338 00:13:25,870 --> 00:13:29,140 know how your data is going to grow. 339 00:13:29,140 --> 00:13:32,360 Set up an alert to send you an email or a text message when 340 00:13:32,360 --> 00:13:34,290 your database gets to 80% capacity. 341 00:13:34,290 --> 00:13:35,050 And then you have a little more 342 00:13:35,050 --> 00:13:36,140 control over the situation. 343 00:13:36,140 --> 00:13:37,870 You can do it manually, at least in the beginning, of a 344 00:13:37,870 --> 00:13:39,120 database's lifetime. 345 00:13:39,120 --> 00:13:40,420 Always a good idea. 346 00:13:40,420 --> 00:13:43,170 Another thing to be aware of is the capacity limits in SQL 347 00:13:43,170 --> 00:13:44,370 Server 2012. 348 00:13:44,370 --> 00:13:47,090 Again, just Books Online has a great chart that has all of 349 00:13:47,090 --> 00:13:47,880 the capacity limits. 350 00:13:47,880 --> 00:13:49,350 Just do a search in it for capacity. 351 00:13:49,350 --> 00:13:51,890 But things like you can have up to 50 352 00:13:51,890 --> 00:13:53,770 instances on a SQL Server. 353 00:13:53,770 --> 00:13:56,500 An instance can contain up to 32,000 databases. 354 00:13:56,500 --> 00:14:01,250 A database can be up to 520,000 terabytes in size. 355 00:14:01,250 --> 00:14:05,070 That kind of stuff can be good information, especially if you 356 00:14:05,070 --> 00:14:08,610 may come across an extreme installation that may run up 357 00:14:08,610 --> 00:14:09,300 against those. 358 00:14:09,300 --> 00:14:11,400 Good to know at least where to find them. 359 00:14:11,400 --> 00:14:14,230 Now, when it comes time to design new databases, the big 360 00:14:14,230 --> 00:14:16,240 things we need to think about are going to be this disk 361 00:14:16,240 --> 00:14:19,090 subsystem, so the actual physical disks that our data 362 00:14:19,090 --> 00:14:21,050 files are going to reside on, the physical 363 00:14:21,050 --> 00:14:22,440 structure of our database. 364 00:14:22,440 --> 00:14:25,100 And that's going to come in the form of our MDF files, our 365 00:14:25,100 --> 00:14:27,360 NDF files, our LDF files. 366 00:14:27,360 --> 00:14:30,080 And then also the logical structure of our database, 367 00:14:30,080 --> 00:14:32,440 which is going to come in the form of filegroups. 368 00:14:32,440 --> 00:14:36,560 So let's start here with the actual storage technology. 369 00:14:36,560 --> 00:14:39,110 So RAID stands for Redundant Array of Independent Disks. 370 00:14:39,110 --> 00:14:41,560 And it's a storage technology that's been around for a 371 00:14:41,560 --> 00:14:43,740 while, so it's tried, true, and tested. 372 00:14:43,740 --> 00:14:46,270 And it's just really a way that multiple hard drives can 373 00:14:46,270 --> 00:14:49,000 work together to give us these performance and fault 374 00:14:49,000 --> 00:14:50,030 tolerance benefits. 375 00:14:50,030 --> 00:14:52,290 And there's a lot of different levels of RAID. 376 00:14:52,290 --> 00:14:54,630 There's RAID 0, there's RAID 1, there's RAID 5, there's 377 00:14:54,630 --> 00:14:56,730 RAID 10, otherwise known as RAID 1+0. 378 00:14:56,730 --> 00:14:59,210 And there's even RAID 0+1. 379 00:14:59,210 --> 00:15:03,780 So SQL Server works best with RAID 10 and RAID 5, because 380 00:15:03,780 --> 00:15:06,910 these both give us performance and fault 381 00:15:06,910 --> 00:15:08,740 tolerance benefits together. 382 00:15:08,740 --> 00:15:09,690 So let's talk a little bit about these. 383 00:15:09,690 --> 00:15:11,330 Now, first RAID 0 by itself. 384 00:15:11,330 --> 00:15:11,910 What's RAID 0? 385 00:15:11,910 --> 00:15:14,620 RAID 0 is known as disk striping. 386 00:15:14,620 --> 00:15:18,220 And RAID 0 by itself gives us performance gains but doesn't 387 00:15:18,220 --> 00:15:19,960 give us any fault tolerance gains. 388 00:15:19,960 --> 00:15:22,260 And how this works is, let's say that we had four hard 389 00:15:22,260 --> 00:15:23,960 drives, hard disks. 390 00:15:23,960 --> 00:15:27,400 If we were to do a write operation against a physical 391 00:15:27,400 --> 00:15:30,120 disk, in the file system, it would look 392 00:15:30,120 --> 00:15:31,370 like one disk to us. 393 00:15:31,370 --> 00:15:32,660 Call it the C drive. 394 00:15:32,660 --> 00:15:35,800 So our C drive may look like the C drive, but really it's 395 00:15:35,800 --> 00:15:39,242 on top of a RAID 0 array that has four disks underneath the 396 00:15:39,242 --> 00:15:40,680 hood at the hardware level. 397 00:15:40,680 --> 00:15:43,570 So if we were to write any data to our C drive, it would 398 00:15:43,570 --> 00:15:47,280 get spread out across all four of those drives and written 399 00:15:47,280 --> 00:15:47,940 asynchronously. 400 00:15:47,940 --> 00:15:50,100 And that would give us a huge performance gain. 401 00:15:50,100 --> 00:15:51,890 So that's RAID 0, disk striping. 402 00:15:51,890 --> 00:15:54,240 RAID 1 is known as disk mirroring. 403 00:15:54,240 --> 00:15:58,230 And it gives us our fault tolerance benefit, because 404 00:15:58,230 --> 00:16:00,370 same scenario, let's say we had four hard drives. 405 00:16:00,370 --> 00:16:02,980 If we were to write data to our C drive, underneath the 406 00:16:02,980 --> 00:16:04,880 hood, it would write that data to all four of those drives. 407 00:16:04,880 --> 00:16:09,020 So if one of those drives failed, no big deal, because 408 00:16:09,020 --> 00:16:13,330 that data exists on the other three drives. 409 00:16:13,330 --> 00:16:15,350 So if we combine them together, we get what's known 410 00:16:15,350 --> 00:16:17,070 as RAID 10 or RAID 1+0. 411 00:16:17,070 --> 00:16:20,850 And this is known as a stripe of mirrors, because it's the 412 00:16:20,850 --> 00:16:22,470 best of both worlds. 413 00:16:22,470 --> 00:16:24,660 So it really is the uber level of RAID. 414 00:16:24,660 --> 00:16:28,780 But because of that, it's also the most expensive strategy or 415 00:16:28,780 --> 00:16:29,820 solution here. 416 00:16:29,820 --> 00:16:31,650 And that's because instead of four disks, now 417 00:16:31,650 --> 00:16:32,530 we need eight disks. 418 00:16:32,530 --> 00:16:34,860 We need four for the data, and we need four for the mirror. 419 00:16:34,860 --> 00:16:37,550 And you can see that can add up really quickly, especially 420 00:16:37,550 --> 00:16:38,850 when you start working with more disks. 421 00:16:38,850 --> 00:16:41,900 Then it just gets exponentially more expensive. 422 00:16:41,900 --> 00:16:44,780 So that's why a lot of people will, with budgets, a lot of 423 00:16:44,780 --> 00:16:47,610 companies with budgets will fall back to RAID 5. 424 00:16:47,610 --> 00:16:52,490 RAID 5 is really RAID 0 with fault tolerance provided 425 00:16:52,490 --> 00:16:54,035 through what's known as parity information. 426 00:16:54,035 --> 00:16:57,030 And parity information is stored across all the disks. 427 00:16:57,030 --> 00:16:59,960 So that way, if something were to go down, all that data can 428 00:16:59,960 --> 00:17:02,630 be reconstructed using all the disks. 429 00:17:02,630 --> 00:17:06,430 So think of it as RAID 0 with a little bit of RAID 1, 430 00:17:06,430 --> 00:17:07,510 because we get the fault tolerance. 431 00:17:07,510 --> 00:17:10,760 It's just that instead of getting the fault tolerance in 432 00:17:10,760 --> 00:17:14,230 a mirror redundant fashion, we get it by using the existing 433 00:17:14,230 --> 00:17:18,470 disks and parity information to reconstruct any lost data. 434 00:17:18,470 --> 00:17:19,119 Now, moving on. 435 00:17:19,119 --> 00:17:21,690 The next big thing you need to think about is the physical 436 00:17:21,690 --> 00:17:23,050 file structure of our database. 437 00:17:23,050 --> 00:17:25,200 And we have three big files to work with here in SQL Server. 438 00:17:25,200 --> 00:17:27,859 We have what's known as the MDF file, which is the 439 00:17:27,859 --> 00:17:29,230 primary data file. 440 00:17:29,230 --> 00:17:33,770 We have one or more NDF files, which are called secondary 441 00:17:33,770 --> 00:17:35,000 data files. 442 00:17:35,000 --> 00:17:38,570 And we have LDF files, which is the transaction log. 443 00:17:38,570 --> 00:17:40,930 So starting with the MDF here, the primary data file. 444 00:17:40,930 --> 00:17:44,170 By default, if you create a database and that's it, no 445 00:17:44,170 --> 00:17:46,950 secondary files, everything is going to get stuffed into this 446 00:17:46,950 --> 00:17:48,260 primary data file. 447 00:17:48,260 --> 00:17:51,080 Small databases, that may be OK; large databases, that's 448 00:17:51,080 --> 00:17:53,560 not a good idea, because we have system objects in here 449 00:17:53,560 --> 00:17:54,510 and everything else. 450 00:17:54,510 --> 00:17:57,850 And all these system objects get accessed randomly on disk, 451 00:17:57,850 --> 00:18:00,130 and that's obviously not good for performance. 452 00:18:00,130 --> 00:18:04,200 Where if we break them out into NDF files and we 453 00:18:04,200 --> 00:18:07,470 specifically put our data inside of here, they will get 454 00:18:07,470 --> 00:18:11,000 accessed, the data itself will get accessed sequentially on 455 00:18:11,000 --> 00:18:12,810 disk, which is great for performance. 456 00:18:12,810 --> 00:18:14,620 And same with the transaction log. 457 00:18:14,620 --> 00:18:17,290 So the general idea here, especially if performance is a 458 00:18:17,290 --> 00:18:22,230 concern, is to break out your MDF and your NDFs, put your 459 00:18:22,230 --> 00:18:26,100 data and tables and such on these secondary files and 460 00:18:26,100 --> 00:18:29,590 leave the MDF to SQL Server to manage all the system objects. 461 00:18:29,590 --> 00:18:32,430 Now, the trick here is that when you create a table, you 462 00:18:32,430 --> 00:18:34,860 can't say, put it on this NDF file. 463 00:18:34,860 --> 00:18:35,940 You can't do that. 464 00:18:35,940 --> 00:18:37,600 That's why we have filegroups. 465 00:18:37,600 --> 00:18:42,260 We use filegroups to put objects on specific disks and 466 00:18:42,260 --> 00:18:43,650 specific files. 467 00:18:43,650 --> 00:18:46,640 Because if we have multiple MDF files like we do here, we 468 00:18:46,640 --> 00:18:48,520 have two NDF files on our sales filegroup. 469 00:18:48,520 --> 00:18:51,700 If we see a Create Table on this sales filegroup, it's 470 00:18:51,700 --> 00:18:55,810 going to spread the data out across these files. 471 00:18:55,810 --> 00:18:57,670 Now, we also have the transaction log. 472 00:18:57,670 --> 00:19:00,280 And think of the transaction log as just an audit trail 473 00:19:00,280 --> 00:19:02,195 against all the actions performed on our database, so 474 00:19:02,195 --> 00:19:03,860 all the inserts, updates, and deletes. 475 00:19:03,860 --> 00:19:06,810 Anything that happens in our database gets logged. 476 00:19:06,810 --> 00:19:09,350 And that's a good thing, because what that means is if 477 00:19:09,350 --> 00:19:11,940 the power goes out on our database while it was in the 478 00:19:11,940 --> 00:19:14,240 middle of an insert or an update or what we would call a 479 00:19:14,240 --> 00:19:17,710 transaction, when SQL Server boots back up, it scans the 480 00:19:17,710 --> 00:19:18,500 transaction log. 481 00:19:18,500 --> 00:19:22,593 And if that transaction didn't quite complete and our data is 482 00:19:22,593 --> 00:19:25,380 in an inconsistent state, it can roll back that transaction 483 00:19:25,380 --> 00:19:27,490 on anything that it did in the database. 484 00:19:27,490 --> 00:19:30,270 On the other side of that coin, if the transaction did 485 00:19:30,270 --> 00:19:32,390 complete but it didn't get fully written or materialized 486 00:19:32,390 --> 00:19:36,340 in the database, it can roll forward those changes. 487 00:19:36,340 --> 00:19:38,580 So that's the whole point of the transaction log. 488 00:19:38,580 --> 00:19:40,510 And that's another reason, because it's got so much 489 00:19:40,510 --> 00:19:43,220 activity, to separate the transaction log into its own 490 00:19:43,220 --> 00:19:46,900 RAID array or at least on its own physical disk, because 491 00:19:46,900 --> 00:19:48,960 there's a lot of things going on in the transaction log. 492 00:19:48,960 --> 00:19:52,610 So we want to separate that from the rest of our world. 493 00:19:52,610 --> 00:19:54,300 All right, speaking of physical disks in RAID. 494 00:19:54,300 --> 00:19:56,260 What if we put this all together? 495 00:19:56,260 --> 00:20:00,090 RAID, filegroups, data files, and design a database? 496 00:20:00,090 --> 00:20:00,890 Here's what I would do. 497 00:20:00,890 --> 00:20:04,060 The very first thing you should do is get your 498 00:20:04,060 --> 00:20:07,760 transaction log into its own RAID array, RAID 10 499 00:20:07,760 --> 00:20:10,030 preferably, because RAID 10 is best for write performance. 500 00:20:10,030 --> 00:20:10,420 And guess what? 501 00:20:10,420 --> 00:20:13,180 Our transaction log is constantly getting written to. 502 00:20:13,180 --> 00:20:16,660 So if you're thinking about performance, the first thing 503 00:20:16,660 --> 00:20:18,570 you want to do is get your transaction log at the very 504 00:20:18,570 --> 00:20:22,040 least on its own physical disk, ideally on its own RAID 505 00:20:22,040 --> 00:20:23,410 10 array of disks. 506 00:20:23,410 --> 00:20:25,150 Because again, lots of writes going here. 507 00:20:25,150 --> 00:20:26,590 RAID 10 is best for writes. 508 00:20:26,590 --> 00:20:28,660 And you want to separate it from-- 509 00:20:28,660 --> 00:20:31,680 because you don't want to be constantly fighting resources 510 00:20:31,680 --> 00:20:33,180 with everything else that's going on, 511 00:20:33,180 --> 00:20:34,480 especially your data files. 512 00:20:34,480 --> 00:20:36,650 So that would be the first step and the first priority. 513 00:20:36,650 --> 00:20:38,660 Get that transaction log on its own disk or 514 00:20:38,660 --> 00:20:39,570 its own RAID array. 515 00:20:39,570 --> 00:20:41,710 The next priority should be getting your data 516 00:20:41,710 --> 00:20:44,110 files on its own array. 517 00:20:44,110 --> 00:20:47,720 And ideally, you could probably get away with RAID 5. 518 00:20:47,720 --> 00:20:50,030 But RAID 10, obviously, ideally. 519 00:20:50,030 --> 00:20:53,310 And what I see a lot of people do for the big installs, which 520 00:20:53,310 --> 00:20:56,470 is a really smart idea, is to break these out into their own 521 00:20:56,470 --> 00:20:57,960 separate filegroups. 522 00:20:57,960 --> 00:20:58,755 And you would have a read 523 00:20:58,755 --> 00:21:00,460 filegroup and a write filegroup. 524 00:21:00,460 --> 00:21:02,220 So let's say this is a read filegroup over here. 525 00:21:02,220 --> 00:21:04,840 We would call this Sales Read filegroup. 526 00:21:04,840 --> 00:21:07,410 And we would identify all of our tables that don't have a 527 00:21:07,410 --> 00:21:10,630 lot of inserts and updates to them, just selects from them, 528 00:21:10,630 --> 00:21:12,145 so people reading data out of the tables. 529 00:21:12,145 --> 00:21:13,520 And we would put that table and that 530 00:21:13,520 --> 00:21:15,130 data in this filegroup. 531 00:21:15,130 --> 00:21:18,010 And we would identify what tables have a lot of inserts, 532 00:21:18,010 --> 00:21:19,560 updates, and deletes on them, and put 533 00:21:19,560 --> 00:21:20,790 that in its own filegroup. 534 00:21:20,790 --> 00:21:23,630 So we could call this our Sales Write filegroup. 535 00:21:23,630 --> 00:21:27,690 And then in our Read filegroup, we could do RAID 5. 536 00:21:27,690 --> 00:21:30,920 In our Write filegroup, we could do RAID 10. 537 00:21:30,920 --> 00:21:33,110 Otherwise, you could probably just get away with putting it 538 00:21:33,110 --> 00:21:35,500 all on a RAID 10 array. 539 00:21:35,500 --> 00:21:37,450 But again, this is really just-- it's going to depend on 540 00:21:37,450 --> 00:21:39,060 your budget, your fault tolerance needs, your 541 00:21:39,060 --> 00:21:40,080 performance needs. 542 00:21:40,080 --> 00:21:41,620 It's going to depend on a lot of those factors. 543 00:21:41,620 --> 00:21:44,380 But this is really the uber way of doing it. 544 00:21:44,380 --> 00:21:48,460 And in fact, you'll even see people break indexes out into 545 00:21:48,460 --> 00:21:50,110 their own filegroup. 546 00:21:50,110 --> 00:21:53,490 And you can pop your indexes in on a RAID 5 array, because, 547 00:21:53,490 --> 00:21:55,180 again, not a lot of writes against an index. 548 00:21:55,180 --> 00:21:56,350 It's more of reading and 549 00:21:56,350 --> 00:21:57,610 traversing the index structure. 550 00:21:57,610 --> 00:21:59,530 So you could put that there as well. 551 00:21:59,530 --> 00:22:01,920 And then your primary filegroup, you really just 552 00:22:01,920 --> 00:22:02,335 want redundancy. 553 00:22:02,335 --> 00:22:04,480 You're not really concerned about performance on here, 554 00:22:04,480 --> 00:22:07,310 because SQL Server is really using this 555 00:22:07,310 --> 00:22:08,550 at the system level. 556 00:22:08,550 --> 00:22:11,610 So you could probably put this on a RAID 1 array, of if you 557 00:22:11,610 --> 00:22:13,050 really wanted to, a RAID 5. 558 00:22:13,050 --> 00:22:15,860 And if performance is paramount, then you'll also 559 00:22:15,860 --> 00:22:19,880 want to put your TempDB, which SQL Server does 560 00:22:19,880 --> 00:22:21,110 a lot of with data. 561 00:22:21,110 --> 00:22:22,670 It does a lot of temporary storage in here 562 00:22:22,670 --> 00:22:23,400 for data and such. 563 00:22:23,400 --> 00:22:28,840 You'll want to get this on a RAID 5 or RAID 10 as well. 564 00:22:28,840 --> 00:22:30,070 Now, moving on. 565 00:22:30,070 --> 00:22:32,870 How can we create a standby database 566 00:22:32,870 --> 00:22:34,090 for reporting purposes? 567 00:22:34,090 --> 00:22:35,430 And we've got a lot of technologies 568 00:22:35,430 --> 00:22:36,440 we can use for this. 569 00:22:36,440 --> 00:22:38,350 And some of these technologies are fault tolerance 570 00:22:38,350 --> 00:22:38,710 technologies. 571 00:22:38,710 --> 00:22:42,940 But we can still use them to get some reporting off them, 572 00:22:42,940 --> 00:22:48,690 to do what we call shed load, get some resources pointed to 573 00:22:48,690 --> 00:22:53,880 other databases to spread the performance across databases 574 00:22:53,880 --> 00:22:56,140 and potentially across servers. 575 00:22:56,140 --> 00:23:00,060 So the first option we have here is log shipping. 576 00:23:00,060 --> 00:23:02,790 And log shipping, speaking of the transaction log, is just 577 00:23:02,790 --> 00:23:03,540 what it sounds like. 578 00:23:03,540 --> 00:23:07,190 We're shipping off the transaction log to one or more 579 00:23:07,190 --> 00:23:10,990 secondary databases or even databases on other servers. 580 00:23:10,990 --> 00:23:12,270 And how this works, it's really just 581 00:23:12,270 --> 00:23:13,220 a three-step process. 582 00:23:13,220 --> 00:23:15,420 And it relies on the old, tried, and true backup and 583 00:23:15,420 --> 00:23:16,490 restore process. 584 00:23:16,490 --> 00:23:18,260 This runs off SQL jobs. 585 00:23:18,260 --> 00:23:21,390 So you can have a SQL job kickoff that will take your 586 00:23:21,390 --> 00:23:24,410 primary, or otherwise known as the principal database, it'll 587 00:23:24,410 --> 00:23:27,540 back it up, send that transaction log or that 588 00:23:27,540 --> 00:23:30,830 backup, I should say, over to a secondary server, and then 589 00:23:30,830 --> 00:23:32,080 restore it over there. 590 00:23:32,080 --> 00:23:36,220 And then it'll do that for every secondary database tied 591 00:23:36,220 --> 00:23:37,110 to the job. 592 00:23:37,110 --> 00:23:38,260 So that's one way of doing it. 593 00:23:38,260 --> 00:23:39,840 And this is known in the fault tolerance 594 00:23:39,840 --> 00:23:42,960 world as a warm standby. 595 00:23:42,960 --> 00:23:46,620 It's warm, because there's no automatic failover. 596 00:23:46,620 --> 00:23:49,150 You would actually, if our primary database failed or was 597 00:23:49,150 --> 00:23:51,470 down or got corrupted or whatever reason, we would 598 00:23:51,470 --> 00:23:53,660 actually physically have to go over to one of those secondary 599 00:23:53,660 --> 00:23:57,510 databases and turn them on or bring them up. 600 00:23:57,510 --> 00:23:58,670 And we'd have to point out the client 601 00:23:58,670 --> 00:24:00,530 applications to them as well. 602 00:24:00,530 --> 00:24:02,175 So that's why it's called a warm standby, because there's 603 00:24:02,175 --> 00:24:02,790 some manual work. 604 00:24:02,790 --> 00:24:05,310 But as far as reporting purposes, as long as those 605 00:24:05,310 --> 00:24:09,240 secondary databases aren't in a restore process, you can use 606 00:24:09,240 --> 00:24:13,260 them to send other connections to. 607 00:24:13,260 --> 00:24:16,260 So that's really one option for shedding load, spreading 608 00:24:16,260 --> 00:24:18,630 the love around a little bit, as we like to call that. 609 00:24:18,630 --> 00:24:21,100 Database mirroring is another option. 610 00:24:21,100 --> 00:24:22,920 With database mirroring, it's only a one-to-one 611 00:24:22,920 --> 00:24:24,180 relationship, as you can see here. 612 00:24:24,180 --> 00:24:25,580 This is kind of a database mirror what I 613 00:24:25,580 --> 00:24:26,640 have going on here. 614 00:24:26,640 --> 00:24:30,140 And a database mirror is really 615 00:24:30,140 --> 00:24:31,280 used for fault tolerance. 616 00:24:31,280 --> 00:24:35,400 And this is considered a hot standby, because the mirror 617 00:24:35,400 --> 00:24:39,260 database is always, or at least very close to in sync 618 00:24:39,260 --> 00:24:41,220 with the principle database, the primary database. 619 00:24:41,220 --> 00:24:44,540 And if that primary database goes down, the secondary 620 00:24:44,540 --> 00:24:47,140 database can recognize it and come right back up. 621 00:24:47,140 --> 00:24:49,710 And the really nice thing about this is applications 622 00:24:49,710 --> 00:24:52,330 built with some of the latest .NET technology can also 623 00:24:52,330 --> 00:24:54,440 recognize this and automatically switch to client 624 00:24:54,440 --> 00:24:57,930 applications to use the mirrored database. 625 00:24:57,930 --> 00:25:00,660 So again, more mirroring use for fault tolerance. 626 00:25:00,660 --> 00:25:04,950 But you can use it for reporting purposes, but only 627 00:25:04,950 --> 00:25:08,410 if you use a snapshot, if you take a snapshot of that mirror 628 00:25:08,410 --> 00:25:09,840 and then point everything at the snapshot. 629 00:25:09,840 --> 00:25:12,930 So you can't really use a mirrored database, because 630 00:25:12,930 --> 00:25:15,550 it's always in use, it's always trying to stay 631 00:25:15,550 --> 00:25:18,360 synchronized with the principle database. 632 00:25:18,360 --> 00:25:20,920 And finally here, we have AlwaysOn availability groups. 633 00:25:20,920 --> 00:25:23,290 AlwaysOn, brand new in SQL Server 2012. 634 00:25:23,290 --> 00:25:24,835 And it's really cool technology. 635 00:25:24,835 --> 00:25:26,800 It really takes the best of all these worlds 636 00:25:26,800 --> 00:25:27,840 and combines them. 637 00:25:27,840 --> 00:25:30,360 And it's actually physically going to 638 00:25:30,360 --> 00:25:31,770 replace database mirroring. 639 00:25:31,770 --> 00:25:34,030 And they say within two versions of now, database 640 00:25:34,030 --> 00:25:37,510 mirroring will be deprecated and replaced by AlwaysOn. 641 00:25:37,510 --> 00:25:38,420 So what is AlwaysOn? 642 00:25:38,420 --> 00:25:40,260 Well, it's really the best of all these worlds. 643 00:25:40,260 --> 00:25:43,310 Because in the database mirroring world, we can only 644 00:25:43,310 --> 00:25:45,340 do a primary and then a mirror, or as we call it, the 645 00:25:45,340 --> 00:25:46,410 principal and then the mirror. 646 00:25:46,410 --> 00:25:46,760 That's it. 647 00:25:46,760 --> 00:25:48,130 It's a one-to-one relationship. 648 00:25:48,130 --> 00:25:50,690 With AlwaysOn, we can have up to four, what we call, 649 00:25:50,690 --> 00:25:53,180 replicas, four sets of replicas. 650 00:25:53,180 --> 00:25:56,600 Not only that, but instead of being one database against one 651 00:25:56,600 --> 00:26:00,420 database, we can actually have multiple databases that 652 00:26:00,420 --> 00:26:02,570 failover together in a set. 653 00:26:02,570 --> 00:26:04,870 And then again, so we have our primary set and then we can 654 00:26:04,870 --> 00:26:06,490 have up to four replicas. 655 00:26:06,490 --> 00:26:09,600 Not only that, but those replicas can be marked as 656 00:26:09,600 --> 00:26:12,180 read-only where then we can use them as a reporting 657 00:26:12,180 --> 00:26:14,400 database, and we don't have to worry about them being 658 00:26:14,400 --> 00:26:16,270 partially available like log shipping. 659 00:26:16,270 --> 00:26:18,420 If they were in the Restore mode, we couldn't access the 660 00:26:18,420 --> 00:26:18,960 reporting database. 661 00:26:18,960 --> 00:26:21,430 Well, now with AlwaysOn, we can always access those 662 00:26:21,430 --> 00:26:22,265 read-only replicas. 663 00:26:22,265 --> 00:26:23,767 And with database mirroring, you don't have 664 00:26:23,767 --> 00:26:24,920 to worry about snapshots. 665 00:26:24,920 --> 00:26:26,590 And we don't have to worry about the complexities of 666 00:26:26,590 --> 00:26:28,680 replication, because it's very easy to 667 00:26:28,680 --> 00:26:30,000 work with and configure. 668 00:26:30,000 --> 00:26:31,690 So we've got a Nugget dedicated to database 669 00:26:31,690 --> 00:26:34,640 mirroring, a Nugget dedicated to replication, and, of 670 00:26:34,640 --> 00:26:38,300 course, a Nugget dedicated to AlwaysOn. 671 00:26:38,300 --> 00:26:40,560 So we'll take a good look at all these great and very handy 672 00:26:40,560 --> 00:26:41,620 technologies. 673 00:26:41,620 --> 00:26:43,380 Again, Server Core mode, as I mentioned earlier in this 674 00:26:43,380 --> 00:26:45,240 Nugget, can be installed. 675 00:26:45,240 --> 00:26:48,760 We can install SQL Server, all editions on Server Core mode. 676 00:26:48,760 --> 00:26:50,500 The only thing we need to be careful of here is it can only 677 00:26:50,500 --> 00:26:55,240 be done on a Windows Server 2008 R2 SP1 or greater server. 678 00:26:55,240 --> 00:26:58,630 And we do this installation from the command line, because 679 00:26:58,630 --> 00:27:00,010 that's how we work with Server Core mode. 680 00:27:00,010 --> 00:27:02,820 We work with it through the command line. 681 00:27:02,820 --> 00:27:05,440 Now, another thing to think about and plan ahead for is 682 00:27:05,440 --> 00:27:06,600 service security. 683 00:27:06,600 --> 00:27:10,060 What accounts are we going to use to run our services under? 684 00:27:10,060 --> 00:27:11,980 And one of the first things you should think about here is 685 00:27:11,980 --> 00:27:14,030 ensuring that these accounts have minimal rights. 686 00:27:14,030 --> 00:27:17,430 You'll see a lot of people that set up all their services 687 00:27:17,430 --> 00:27:19,060 under the same domain administrator. 688 00:27:19,060 --> 00:27:23,290 And that's not a good idea for security reasons. 689 00:27:23,290 --> 00:27:26,460 But also, when you're using domain accounts, now you have 690 00:27:26,460 --> 00:27:27,790 to manage passwords. 691 00:27:27,790 --> 00:27:30,990 And a lot of-- especially with passwords policies these days, 692 00:27:30,990 --> 00:27:33,580 you're constantly changing passwords. 693 00:27:33,580 --> 00:27:36,000 And if you're constantly changing passwords on your 694 00:27:36,000 --> 00:27:38,010 accounts, well, you're going to get a nice surprise when 695 00:27:38,010 --> 00:27:41,210 you reboot your SQL Server or you reboot your SQL Service. 696 00:27:41,210 --> 00:27:42,850 And all of a sudden, your services don't start up, and 697 00:27:42,850 --> 00:27:44,960 you're left scratching your head and figuring out why. 698 00:27:44,960 --> 00:27:46,930 So that's one thing to think about. 699 00:27:46,930 --> 00:27:50,300 And how you can get around that is you can use Managed 700 00:27:50,300 --> 00:27:51,910 Service Accounts, MSAs. 701 00:27:51,910 --> 00:27:53,460 These are the accounts that have the dollar sign 702 00:27:53,460 --> 00:27:55,662 prefixed-- or suffixed, I should say, at the very end of 703 00:27:55,662 --> 00:27:56,420 the account. 704 00:27:56,420 --> 00:27:58,910 And also, virtual accounts are another good one, because 705 00:27:58,910 --> 00:28:01,310 virtual accounts are anything under the NT service and then 706 00:28:01,310 --> 00:28:04,590 backslash, service name, is one way that you can create 707 00:28:04,590 --> 00:28:05,400 these accounts. 708 00:28:05,400 --> 00:28:09,900 But these are great, because their passwords are-- 709 00:28:09,900 --> 00:28:12,680 and if you think about it, if there are any passwords on, 710 00:28:12,680 --> 00:28:15,460 they're managed by Windows, so you'll never know them. 711 00:28:15,460 --> 00:28:17,660 And these accounts are managed by Windows and they have 712 00:28:17,660 --> 00:28:20,230 minimal rights, so they're perfect candidates for running 713 00:28:20,230 --> 00:28:21,030 services under. 714 00:28:21,030 --> 00:28:25,070 Another good idea is to isolate your services, each of 715 00:28:25,070 --> 00:28:27,620 them with a different dedicated account. 716 00:28:27,620 --> 00:28:29,520 Because, again, especially if you're using domain accounts 717 00:28:29,520 --> 00:28:33,120 to do this, because if one of those accounts becomes 718 00:28:33,120 --> 00:28:37,870 compromised, then not all of your services are compromised. 719 00:28:37,870 --> 00:28:39,430 Finally here, we have benchmarking. 720 00:28:39,430 --> 00:28:43,010 And this is a great way to ensure, before we go live into 721 00:28:43,010 --> 00:28:46,230 a production environment, that our disk subsystem can handle 722 00:28:46,230 --> 00:28:49,650 what it's about to get thrown at it from our databases and 723 00:28:49,650 --> 00:28:50,580 our servers. 724 00:28:50,580 --> 00:28:52,780 So we can do stress and performance testing. 725 00:28:52,780 --> 00:28:55,730 For performance testing, you want to use SQLIO, which does 726 00:28:55,730 --> 00:28:58,290 not come bundled with your SQL Server installation. 727 00:28:58,290 --> 00:29:01,120 But you can get it from Microsoft's download area on 728 00:29:01,120 --> 00:29:01,530 their website. 729 00:29:01,530 --> 00:29:05,320 And I'll download it and put it into our Support files over 730 00:29:05,320 --> 00:29:07,990 in our virtual Nugget Lab for 70-462. 731 00:29:07,990 --> 00:29:12,690 But this is a great tool to use to really performance test 732 00:29:12,690 --> 00:29:15,630 our disk subsystem and our input, output, and our 733 00:29:15,630 --> 00:29:17,640 throughput, and all that stuff. 734 00:29:17,640 --> 00:29:20,410 And IO is a great tool that can give us some nice charts 735 00:29:20,410 --> 00:29:24,450 and show us exactly how our-- and you know how this works? 736 00:29:24,450 --> 00:29:25,890 It actually takes a file-- 737 00:29:25,890 --> 00:29:28,700 We can say, hey, here's a file of this size. 738 00:29:28,700 --> 00:29:30,990 And we can make it the size that we project our database 739 00:29:30,990 --> 00:29:33,710 to be, so we can get a pretty accurate representation of 740 00:29:33,710 --> 00:29:36,340 what kind of database and what kind of performance we're 741 00:29:36,340 --> 00:29:38,670 going to see based on the size that we're predicting our 742 00:29:38,670 --> 00:29:40,460 database to be. 743 00:29:40,460 --> 00:29:43,290 We can configure this file to be a specific size, and then 744 00:29:43,290 --> 00:29:45,930 SQL Server will throw a bunch of-- or SQLIO, I should say, 745 00:29:45,930 --> 00:29:48,010 will throw a bunch of read, writes against the disk based 746 00:29:48,010 --> 00:29:51,630 on that file size and give us some nice numbers and data 747 00:29:51,630 --> 00:29:54,070 here to see exactly how the disk and 748 00:29:54,070 --> 00:29:55,720 the subsystem performed. 749 00:29:55,720 --> 00:30:00,670 So I use SQLIO for performance testing and SQLIO Sim, which 750 00:30:00,670 --> 00:30:03,890 does come bundled with SQL Server and replaces SQLIO 751 00:30:03,890 --> 00:30:07,540 Stress and previous versions of SQL Server, to do disk 752 00:30:07,540 --> 00:30:09,150 integrity checks. 753 00:30:09,150 --> 00:30:14,250 And it does this unlike SQLIO, which is going to send as many 754 00:30:14,250 --> 00:30:16,030 reads and writes as possible at it to do 755 00:30:16,030 --> 00:30:16,880 a performance test. 756 00:30:16,880 --> 00:30:18,830 But a stress test is more it's going to do a lot of different 757 00:30:18,830 --> 00:30:21,980 kinds of read patterns and write patterns to see if the 758 00:30:21,980 --> 00:30:23,490 disk can handle it. 759 00:30:23,490 --> 00:30:26,700 So these are just great tools that you can use to optimize 760 00:30:26,700 --> 00:30:28,842 your hardware configuration. 761 00:30:28,842 --> 00:30:29,300 All right. 762 00:30:29,300 --> 00:30:31,420 So in this CBT Nugget, we took a look at planning a SQL 763 00:30:31,420 --> 00:30:35,200 Server 2012 installation and all the things we need to 764 00:30:35,200 --> 00:30:37,760 think about before getting SQL Server up and running. 765 00:30:37,760 --> 00:30:39,790 We started with evaluating those installation 766 00:30:39,790 --> 00:30:40,360 requirements. 767 00:30:40,360 --> 00:30:42,800 That's just going to come down to understanding what 768 00:30:42,800 --> 00:30:46,230 components and technologies in SQL Server we need to install, 769 00:30:46,230 --> 00:30:48,280 getting a good handle on software requirements, 770 00:30:48,280 --> 00:30:49,870 hardware requirements, if we're going to do 771 00:30:49,870 --> 00:30:52,790 virtualization, what edition of SQL Server are we going to 772 00:30:52,790 --> 00:30:54,950 use, what kind of features we're going to install, 773 00:30:54,950 --> 00:30:57,550 whether they're instance features or shared features. 774 00:30:57,550 --> 00:31:01,110 And we even saw how to plan for the future in scaling up 775 00:31:01,110 --> 00:31:03,730 versus scaling out, in capacity planning. 776 00:31:03,730 --> 00:31:04,760 How are we going to handle shrinking 777 00:31:04,760 --> 00:31:06,290 and growing of databases. 778 00:31:06,290 --> 00:31:08,970 How are we going to handle designing new databases? 779 00:31:08,970 --> 00:31:11,560 What RAID technology are we going to use to put these 780 00:31:11,560 --> 00:31:12,630 databases on? 781 00:31:12,630 --> 00:31:15,070 How are we going to design the physical structure of our 782 00:31:15,070 --> 00:31:18,510 database in our MDF primary files, our secondary NDF 783 00:31:18,510 --> 00:31:21,380 files, and our transaction log LDF file? 784 00:31:21,380 --> 00:31:24,290 And what logical filegroups are we going to use to contain 785 00:31:24,290 --> 00:31:26,740 those files and essentially write our SQL 786 00:31:26,740 --> 00:31:27,940 Server objects to them? 787 00:31:27,940 --> 00:31:30,580 We took a look at what technologies we're going to 788 00:31:30,580 --> 00:31:33,650 look at here in this series when it comes to standby 789 00:31:33,650 --> 00:31:36,790 databases for reporting purposes such as log shipping, 790 00:31:36,790 --> 00:31:39,630 database mirroring, replication, and the new 791 00:31:39,630 --> 00:31:41,820 AlwaysOn availability groups, most of those which we'll take 792 00:31:41,820 --> 00:31:43,240 a good look at in this series. 793 00:31:43,240 --> 00:31:45,760 We saw that SQL Server 2012 can be installed 794 00:31:45,760 --> 00:31:47,430 in Server Core mode. 795 00:31:47,430 --> 00:31:50,900 We took a look at Windows Server service security best 796 00:31:50,900 --> 00:31:52,870 practices and what kind of accounts we 797 00:31:52,870 --> 00:31:53,960 should be working with. 798 00:31:53,960 --> 00:31:56,890 And at the end here, just what tools we'll be using to do 799 00:31:56,890 --> 00:31:59,530 benchmarks and how benchmarking can really help 800 00:31:59,530 --> 00:32:02,870 us understand our disks and really find out what their 801 00:32:02,870 --> 00:32:03,910 capacity limits are. 802 00:32:03,910 --> 00:32:05,850 I hope this has been informative for you, and I 803 00:32:05,850 --> 00:32:07,100 thank you for viewing. 804 00:32:07,100 --> 00:32:07,840