back

Microsoft SQL Server 2012 - 70-462

Administering Microsoft SQL Server 2012 Databases14 H 39 M

This course has a virtual lab
This course has a practice test
Episodes
Episodes
  • Overview of Microsoft SQL Server 2012 (70-462)
    • Intro to Microsoft SQL
  • Install and configure
    • Install and Configure SQL
  • Maintain instances and databases
    • Manage and Configure Databases
    • Optimizing Database Files
  • Optimize and troubleshoot
    • SQL Server Agent
  • Manage data
    • Backup and Restore Databases
    • Import, Export, and Indexing Data
  • Implement security
    • Implementing Security
  • Implement high availability
    • Clustering and Redundancy
    • Monitoring SQL Server

Intro to Microsoft SQL

8 M

  • Episode Description
  • Transcript

The Administering Microsoft SQL Server 2012 Databases exam is designed to be the entry point for Systems Administrators into the world of databases. The course focuses on areas that are required knowledge for anyone responsible for the creation and maintenance of Windows servers running Microsoft SQL 2012. The topics covered range from installing SQL Server to backing up and restoring databases.

1 00:00:00,,034 [SOUND]. 2 00:00:08,,585 This ITProTV series is designed to introduce the viewer to 3 00:00:11,,620 Microsoft SQL Server 2012. 4 00:00:13,,320 More importantly, it's designed to introduce a system admin, to SQL Server. 5 00:00:18,,870 Now a lot of people are familiar with Microsoft SQL. 6 00:00:20,,280 It's one of the most common database servers in the world, 7 00:00:23,,540 you see it very frequently used in a lot of organizations. 8 00:00:26,,860 But, as a Systems Administrator, you're not a database administrator. 9 00:00:30,,150 You don't necessarily know the inner workings of a database. 10 00:00:34,,080 But you might be tasked with the responsibilities of maintaining, or 11 00:00:37,,590 even installing and creating that database server. 12 00:00:39,,980 It's a very common thing to fall into. 13 00:00:41,,600 As a result, Microsoft created an exam that was centered around, 14 00:00:45,,120 not the programming side of Microsoft SQL, but the supporting side of it. 15 00:00:50,,430 And that's what this show is going to be covering. 16 00:00:52,,510 The exam is the 70-462 exam and 17 00:00:55,,320 the official title is Administering Microsoft SQL Server 2012 Databases. 18 00:00:59,,300 Now there is a sister course associated with this one that does get a little 19 00:01:03,,860 more onto the, I don't wanna call it the developer side cuz you're not programming, 20 00:01:06,,920 but dealing with the SQL queries and all that other stuff. 21 00:01:10,,230 So there is one that gets a little more heavier into that. 22 00:01:13,,930 But this one, is specifically focused to systems admins, 23 00:01:16,,120 the people who are supporting Windows Server, who are putting the hardware 24 00:01:21,,130 servers in the rack and getting this infrastructure built up. 25 00:01:24,,880 They may need to bring up database servers also. 26 00:01:26,,102 Now you may have an entire staff of programmers who are ready to jump in there 27 00:01:30,,599 and start using those databases. 28 00:01:31,,059 But they don't understand how to backup and restore the database or 29 00:01:35,,060 how to do performance tuning of the database, things like that. 30 00:01:39,,070 They deal on the development side. 31 00:01:41,,580 We as System Admins, deal on the server side. 32 00:01:43,,585 And so that's what we tackle for this certification. 33 00:01:46,,895 Now, if you look at the requirements, I'll just kinda scroll down here a bit. 34 00:01:49,,525 You'll see it starts out real basic with things like installing and 35 00:01:52,,765 configuring, right? 36 00:01:53,,904 We need to get a SQL Server up and running so 37 00:01:55,,855 we can start to create databases on it and work with data. 38 00:01:58,,540 So that's exactly where it begins, just the initial configuration. 39 00:02:02,,370 Then, actually creating the databases. 40 00:02:05,,510 Somebody's gotta make it. 41 00:02:06,,610 And maybe you're getting a specific spec sheet from your development team saying 42 00:02:10,,750 here's what we need. 43 00:02:11,,990 But often times you don't get that. 44 00:02:12,,095 They just say, hey, we need a database server. 45 00:02:15,,505 So, we bring one up, we create the initial databases, and we get the system up. 46 00:02:19,,921 Now we'll also take a look at clustering, too. 47 00:02:21,,879 If this is a database that needs to be up as often as possible, 48 00:02:24,,171 like high availability, well, we're gonna need to take some extra precautions and so 49 00:02:29,,805 we'll see that in maintaining. 50 00:02:32,,030 It then goes on optimizing, right? 51 00:02:34,,540 How do you make the database run better? 52 00:02:36,,910 Anybody can bring up a database server, but the more that database begins to grow, 53 00:02:41,,880 the slower and slower the system is going to become. 54 00:02:44,,670 And everybody's data is a little bit different. 55 00:02:46,,540 So we take a look at some of the tools and 56 00:02:48,,540 techniques that are available to tweak that data, 57 00:02:51,,510 to change the way that SQL Server performs and get it running a lot faster. 58 00:02:55,,600 And I've got some stories I'll tell in those episodes. 59 00:02:57,,540 You can really make a big difference with just a few mouse clicks, so 60 00:03:01,,400 it's some really neat stuff that's in there. 61 00:03:04,,020 Once the server's up and running, we're done right? 62 00:03:07,,030 We get to walk away? 63 00:03:08,,510 Well, not quite. 64 00:03:09,,340 A lot of times we need to manage the data and 65 00:03:11,,240 that's what the next section jumps into. 66 00:03:13,,125 Especially from the standpoint of backup and restore, right? 67 00:03:17,,110 It's very, very easy to accidentally wipe all of the data out of a database, right? 68 00:03:22,,198 If you give somebody the access to write to the database they 69 00:03:24,,600 couldn't really do that. 70 00:03:25,,950 So we need to make sure that we've got backups, reliable backups, right? 71 00:03:28,,740 It's easy for us to restore and get our systems backup and going. 72 00:03:32,,930 And then this little section right here is probably the most important one in 73 00:03:36,,740 the entire exam objective set. 74 00:03:40,,910 Managing or implementing and maintaining indexes, all right? 75 00:03:43,,400 Indexes will change the performance profile of your server. 76 00:03:47,,860 They will make your server considerably faster. 77 00:03:50,,941 But, indexes are completely built around the types of queries people are running. 78 00:03:54,,402 So we have to understand the queries people are running, and 79 00:03:57,,050 then build the indexes appropriately. 80 00:03:59,,790 There is no one-size-fits-all index solution out there. 81 00:04:02,,820 So we'll take a look at how to analyze your database, see how it's being used, 82 00:04:06,,370 and build the appropriate index system to make it run faster. 83 00:04:11,,830 After that, we've got security, right? 84 00:04:12,,600 We don't wanna forget about that. 85 00:04:14,,810 Some of the biggest visibility attacks that have happened in the last ten 86 00:04:17,,420 years have all been focused on SQL. 87 00:04:20,,830 Well, I shouldn't say it that way, right? 88 00:04:22,,640 When we say SQL, that means Microsoft SQL. 89 00:04:25,,870 When we say S-Q-L, well that's the standard query language that's used across 90 00:04:29,,304 numerous products like Oracle, MySQL, and so on. 91 00:04:33,,960 And, SQL injection attacks 92 00:04:35,,870 are some of the biggest ones that have happened over the last ten years. 93 00:04:38,,832 Many of which went against Microsoft servers, but 94 00:04:40,,570 many of which went against the Oracle and the other guys too. 95 00:04:43,,240 So, how do we secure our system to help prevent from things like that? 96 00:04:47,,863 Well, a lot of that is prevented on the developer's side, 97 00:04:49,,234 they can take care of it there. 98 00:04:51,,475 But there are things we can do on the back end with databases permissions, 99 00:04:54,,321 log ins and roles. 100 00:04:55,,902 And other things to eliminate that accidental activity where people start to 101 00:04:59,,931 inject commands and to do things that we don't want to have happen. 102 00:05:03,,840 All right. And 103 00:05:04,,620 then the last part of the exam objectives is implementing high availability. 104 00:05:08,,110 We talk about it a little bit in the maintaining databases section. 105 00:05:13,,966 But then we get into it down here as far as setting up a mirror, 106 00:05:15,,120 getting Always On configured, setting up failover, and getting that put into place. 107 00:05:20,,870 So really good stuff to keep the system online. 108 00:05:22,,620 These are the tasks right here that an average system admin that 109 00:05:26,,280 supports a network that involves SQL servers are going to be expected to know. 110 00:05:30,,180 So it's a great set of knowledge. 111 00:05:32,,380 For a Microsoft exam this is one of the few ones where I look at it and 112 00:05:36,,467 I'm like man, this is all stuff will actually use. 113 00:05:39,,040 A lot of times there's at least one module in there where they put some fluff 114 00:05:43,,400 cloud service or whatever, that's more of a marketing module. 115 00:05:46,,118 But in this one, it's really skills that you'll use, all right. 116 00:05:50,,580 Now, if you're looking to go a little more onto the development side, 117 00:05:53,,613 if you're looking to write SQL queries. 118 00:05:55,,074 If you wanna learn how to create tables and set up foreign keys and 119 00:06:00,,385 relationships like that, well, the 70-462 doesn't do it. 120 00:06:05,,505 But if you're looking to work for 121 00:06:06,,675 your MCSA SQL Server, there's other exams involved. 122 00:06:10,,840 The 462 is just one of them, that's the one that really is designed for 123 00:06:14,,811 system administrators. 124 00:06:15,,171 But notice there's the 461, Querying Microsoft SQL Server 2012, all right. 125 00:06:21,,380 That is writing query statements. 126 00:06:23,,680 You actually start to learn the standard query language, 127 00:06:25,,160 how to interact with the data on the server. 128 00:06:28,,590 How to put data in the database, how to get data out of the database. 129 00:06:31,,921 A regular system admin doesn't really need to know how to do that, but 130 00:06:34,,560 somebody who's actually DBA, Database Administrator, would. 131 00:06:39,,310 And then, implementing a data warehouse with Microsoft SQL Server 2012. 132 00:06:44,,540 That's where you start learning about how to design your tables, 133 00:06:47,,590 how to work with the data inside of the database, 134 00:06:50,,590 how to create views and reports, how to deal with integration services. 135 00:06:54,,020 All these extra things that a normal system admin is not going to work with, 136 00:06:58,,830 but instead, a DBA would, all right. 137 00:07:00,,490 So the full certification, the MCSA SQL Server, is really designed for 138 00:07:05,,800 somebody who's operating in that DBA role, who is a database administrator. 139 00:07:10,,750 But this one that we're doing right here, 70-462, works really for 140 00:07:15,,375 anybody who has the responsibility of supporting a server running SQL. 141 00:07:19,,850 So I think this is one of the greater exams, one of the greatest set of topics. 142 00:07:23,,490 Even if you don't take the exam, 143 00:07:25,,310 the stuff we talk about on this show is really useful. 144 00:07:28,,690 It will help you operate your database environment. 145 00:07:30,,190 And those databases, for a lot of people, if you have a database server, 146 00:07:35,,710 it's usually the core of your business, and so losing that, right, is a big deal. 147 00:07:40,,460 It could take your business offline for hours, 148 00:07:43,,390 days, who knows how long to get a system back online. 149 00:07:46,,570 So having it set up right from the beginning and 150 00:07:48,,130 having a good backup strategy are two key pieces we wanna make sure we have covered. 151 00:07:53,,530 All right, now our show content will match up with the 70-462 exam, 152 00:07:56,,680 so we're gonna cover all the stuff that's on there like we normally do, 153 00:07:59,,584 you'll be prepared for that. 154 00:08:01,,670 If you wanna go on to do the other ones, 155 00:08:03,,660 we don't have plans in our schedule right now to tackle those. 156 00:08:06,,000 But things change over time, so by the time you're watching this, well, 157 00:08:10,,740 not if you're watching it live I guess, but 158 00:08:11,,170 if you're watching this down the road, we may have already recorded the other ones. 159 00:08:15,,424 So check out the library and see if they're there. 160 00:08:19,,650 All right, well that's pretty much my overview of this one. 161 00:08:26,,525 I'll see you in the show. 162 00:08:27,,444 [SOUND]

Just you? Training a whole team? There's an ITProTV plan that fits.

With more than 4,000 hours of engaging video training for IT professionals, you'll find the courses you and your team need to stay current and get the latest certifications.