as a SQL Server DBA, but you are familiar with SQL Common best practices all DBAs should follow. . Schedule these steps during the slow time of the day. It focuses on the differences between SQL Server and Oracle and on the main .. Service Packs (Microsoft‟s name for patchsets) are simple to install. 16 chapters of database administration training Course files .sql,.pdf,.xml, exercises, and more) have not undergone a full SQL Server DBA class. This is not a . - How to Move and Copy Databases with Backup and Restore.
|Language:||English, Spanish, German|
|Distribution:||Free* [*Register to download]|
This tutorial explains some basic and advanced concepts of SQL Server such as how to .. Step Select SQL Server feature installation option and click Next. What is Database? Need for DB Admins. •. Editions and Versions Of SQL Server. •. Roles and Responsibilities For SQL DBA. •. How to install SQL Server - Step. Database Administration Guide .. If you have an existing Microsoft SQL Server database you Restore from Backup frame and complete a few easy steps.
Just change the variables for your database name and the path where your restore fles live, and presto, the script will restore all of the fles for you automatically. You want to put each database in its own folder, and you want the fle names to have the database name and the time in them. I'm a big fan of that anyway - it makes life easier when I want to quickly scan and see what backups have been done.
It assumes the same database path when you restore. There are other restore scripts that can adjust data and log fle names at restore time, but they're not quite as elegant when it comes to restoring fulls, difs, and t-logs. You can do transaction log backups as ofen as you want. If the restore process is fully automated, why not do transaction log backups every minute? It doesn't cost you any extra. Rather than incurring extra overhead, it can actually incur LESS overhead. If you're only backing up logs once an hour, your users probably feel that hourly load big time when it kicks of.
If you do smaller backups more frequently, they'll be less likely to notice the impact. You can automate your own fre drill testing. I have a series of Agent jobs to restore a diferent server's backups every day, and then run DBCC jobs on them. This way I know I'm geting really good backups. Your Homework for This Chapter You don't have to go build this whole infrastructure out - but start laying the groundwork by making sure your backup fles are named in a restore-friendly way.
If you get errors, leave comments on that blog post describing the error, and ideally, contribute your script improvements back in. Now, when disaster strikes, you won't be clicking around blindly in a GUI - you'll just open a proven script and hit Execute.
Geting Even Fancier You can use this technique to build smaller copies of your production databases. For example, at StackOverfow, I built an ultra- compressed backup with even more tricks. I then backed it up with compression, and lef those backups in a folder that the developers could access.
That made it easier for developers to quickly grab a copy of production data as of last night, copy it to their workstation as quickly as possible, and restore it faster with less drive space required. Log Shipping Part 2: Backup Planning Video: Jeremiah explains 3 company scenarios and asks you to pick the right backup strategy for each. Our Backup Video Archive: We talk database mirroring, clustering, AlwaysOn Availability Groups, and more.
It lists the logins who have been granted the sysadmin or security admin roles. Don't think of them as logins. Think of them as people who can get you fred. These people can drop databases, drop tables, change stored procedures, edit data, or even change SQL Server confguration setings like max server memory or maxdop. You're probably not geting alerted when any of these things change - we just can't aford to monitor every single thing in SQL Server and send alerts on it.
At some point, we have to be able to trust certain logins, and that's where the sysadmin and security admin roles come in.
Except when we frst get started learning database administration, it's usually because we're the only DBA in the shop, and the server is a mess.
The front door is unlocked, the key is under the foor mat, and everybody knows we've got a big screen TV in the living room. In one shop, the head developer's SA rights were removed, and he stormed into the DBA's ofce the next morning screaming. Turns out one of his apps automatically created a processing database every night, did a bunch of work in it, and then dropped the database. Nobody knew because it was only around for minutes.
The problem could have been avoided by communicating the security changes ahead of time, and that's where we need to start. If they do, here's the list of applications that will be afected, including accounting and payroll. I don't want to take away all of their permissions - I just want to start by giving them full permissions over their database, but not in any other databases, and not at the server level.
Can I talk to them about doing that? Then, go to the end users or developers involved and say: I'm going to make you a complete database owner inside your database so you can do anything you want in there, but I'm going to take away your rights to the other databases like accounting and payroll , and I'm going to remove your permissions to change server-level setings like how much memory the server can use.
I'm planning on doing it next weekend, and I want you to have my email and phone number so that if anything breaks on that date, you can call me and I can audit what's happening on the server to see if it's related to the permissions change. For example, if the accounting database is on the server, go to the CFO and say: They have the permissions to drop the database at any given time, and there's nothing I can do to stop it.
I'd like to get that changed - can I schedule a short meeting with you and the development manager to get everyone on the same page?
Sometimes it's boring politics and paperwork, and this is one of those weeks. In the very frst chapter, we built a spreadsheet inventory of our servers, and now it's time to fll in a litle more details. Since we're analyzing security, we need to know which applications live on each server, and who's in charge of each of those applications. You don't have to fll in the specifcs of who has read or write permissions in each database, but we want Manage lots of servers?
The business contact is the one who really helps us get the database locked down because their job is on the line if this data is lost or unavailable. Come to think of it, you're someone who can get THEM fred! In the coming weeks, you'll be working more with them on reliability and performance, too, so now is a good time to start feshing out that part of the spreadsheet. Database Administration is Politics Sure, we like to think we're the police, here to protect and to serve, but most of what we do involves siting in meetings, convincing people to do what we want, how we want.
It's made more challenging because we ofen don't have any real authority. Sometimes the DBAs report to completely diferent managers than the developers - and sometimes, it's even diferent companies! We might be administering a database server that houses a third-party application, and the vendor's support team demands to have SA access.
Consulting Lines: Afer consulting for a while, I wrote a series of posts with lines you can use as a DBA: It's an old page whitepaper circa , but it might just be the best and most timeless technical document that Microsof has ever produced.
JC Cannon and Denny Lee deserve a big thank-you for a great job. I only need you to read pages In those six pages, you'll understand the diferences between risk management, governance, and compliance. Risk management means knowing what risks the company is taking with the data, governance means the actions taken to address the risks, and compliance means someone is double-checking that we're actually doing the governance stuf we say we're doing.
Your job as a DBA involves all three, but when you're just geting started with compliance, focus on risk management. We need to get a quick idea of the diferent ways we could lose data, or that supposedly secure data could get into the wrong hands. Yes, your developers are probably the wrong hands, but that's a diferent story. Look for Risky Business Odds are, nobody in the company has an inventory of the data we're storing in ridiculously unsafe ways. Here's a quick way to check your own database looking for dangerous felds: B65 Feel free to change out those keywords for other terms that are relevant to your business - felds that are sensitive, and that would hurt if they got out into the wild.
Then look at the contents of those tables - is the data being stored unencrypted? Who has access to it? If we're storing unencrypted passwords in the database, for example, then every database backup we've ever done is dangerous. If anybody gets access to any backup fle, like our ofsite tape backups, then we could be on the front page of the news tomorrow.
When you fnd it, email the developers and the business contact for that database. Explain that the data is not encrypted, and use examples from the SQL Server Compliance Guide to show how this is a risk for the business. Knowing Who Accesses What It's unusual for me to assign homework frst in the email, and then go on to talk about other things, but I want you to be aware of other things that companies usually do around their secure data.
Top tip: See, security maters. In practice, though, this is a problem for hard-core security teams because the very same DBA who manages the servers also has permissions to change the SQL Server audits. A greedy DBA could easily disable auditing, get the necessary data, and then enable it again.
With seriously ambitious work, you can lock auditing down so that can't happen, but As a result, most seriously secure companies end up with a completely diferent auditing solution that lives outside of the DBA's realm.
The appliance logs everything that happens in the SQL Server, and then the security team can review those logs without you the DBA knowing about it. These solutions are typically six fgures and up. They're expensive because they have to be absolutely bulletproof - if they fail, you can't have the access to the database suddenly stop. Plus, you'll need them in place for not just your production environment, but also your disaster recovery environment.
If you just want to check a box and make the auditors think you're secure, that's easy and cheap, but seriously good security is seriously expensive. Big Companies Love Security The bigger the company, the more interested they get in security. In small shops without a dedicated security department, everybody is ofen a domain administrator, and everybody knows where all the passwords are stored.
In big shops with a dedicated security team, the security team usually makes requests to disable the DBA's sysadmin access. They want the DBA to only have enough permissions to do their job, but not enough permissions to see the database contents.
Afer all, the DBA shouldn't see credit card data, health data, etc. For more info, check out the security enhancements section of What's New in Denny manages to bring a very dry topic to life. It's a tougher sell for small shops with only one full time DBA, though, because a lot of the security features discussed take time to digest and implement.
We could go on for hundreds of pages about those two levels alone. We could fll entire ebooks, perhaps even libraries, with cool concepts like how the diferential bitmap works, how to secure stored procedures with certifcates, how to confgure SQL Server when using VMware-based backups for storage replication, how to recover from a corrupted nonclustered index, yadda yadda yadda.
Our point of this ebook is a fast start. Your journey in SQL Server database administration is never really done. When you fnd an area that you want to dig deeper into, check out: Some even include quizzes! No, seriously, hang on a second. I'm not saying that you should ever say things like "It's not my job," but I do want you to understand when you should avoid reinventing the wheel. Your Windows administrators should be monitoring every Windows server you have, making sure it doesn't run out of drive space.
This isn't some wild, unique need that only SQL Server has - it's everywhere in your data center. It might actually BE your job to monitor this stuf. That's cool - that's how I got started too. So leave the drive space monitoring to the pros. You should learn what exceptions could happen, how to trap them, and how to fail elegantly. It's hard work, but if you get good at it - really good - you can build amazing things and make a killer living for yourself. But if you want to be a professional DBA, you should leave the developing to the developers.
I'm not saying you shouldn't learn the dynamic management views DMVs , how to dig through them to fnd the evidence you need, and how to turn that data into actions. Hopefully, I'm going to teach you how to do a lot of that over the course of the next six months. Take another look at the Hierarchy of Database Needs again, and think for a second about all the things we're going to be learning over the next six months.
Just in the last fve weeks, I've had you build an inventory of your servers, automate your database restores, start locking down security, and enumerate your database risks. The next few months are chock full of things that will help you become a hero in the eyes of your users and your developers. Building a crappy monitoring tool in your spare time will not give you that same level of respect. And yes, if you've only got your spare time at work, your monitoring tool is going to be crappy.
If you've got so much time that you can build a great monitoring Just when you thought it was safe to let your guard down, we throw a trick chapter at you. If the server runs out of drive space, if the service stops, if the event log starts flling up with errors, or zombies atack, these tools will let you know. Performance Diagnostics - these tools try to derive insight from SQL Server's dynamic management views DMVs to help performance tuners wring the most throughput out of their hardware.
If so, get them to start monitoring your servers. I'm being selfsh here - my real reason is that I want to save my monitoring budget for tools in the Performance Diagnostics category. Instead, no mater which category of tool you're downloading, make a list of the last things that have caused your phone to ring afer hours.
Here's some of my personal favorites: All of them provide free day trials.
Reproduce the problem, and watch how the monitoring sofware reacts. Does it lead you to the root cause quickly, or does it just have a bunch of fashing red lights on the console?
The best ones will save you time by geting you right to the solution. Afer you've done your evaluation and picked a favorite, get a quote from them - and get a quote from the other vendors as well. Share the quotes with the competing salespeople.
Your Homework I don't expect you to magically get budget approval for a tool this week, but I want you to start making a list of day to day problems as they strike. If you waste four hours troubleshooting a deadlock problem, make a note of the date, the time required, and a few words about the root cause. In a month or two, you'll be able to present this list to your boss as proof of why the right monitoring tool could save you money.
Down the road, I'll also link you to a video showing you how to get the budget approval for that tool. I'm not selling you vendor tools, by the way. I ofen link to vendor tools, but I don't receive any compensation. I'm just a huge believer in using the right tool to look like a hero fast - instead of building your own hammer every time you change jobs.
The lead DBA asked me, "If you were going to monitor your servers, how would you do it? He wanted me to use an open source monitoring framework. Today, several years down the road, now that I'm a Microsof Certifed Master, my answer is still the same. I don't reinvent the wheel, and neither should you. Focus on things that provide the business tremendous value, and your career will take care of itself.
If your business would fnd tremendous value in a killer monitoring system, then become a developer and give it to them, or consider contributing to the huge number of open source monitoring products. Don't build a new one from scratch, and defnitely know which metrics to monitor. What other statistical lies are lurking out there for you to fnd in Perfmon?
Learn why. Rotational drives can tell you how hot the drive is, provide bad sector counts, and a host of other information about drive health. Read more in this post. Before you build or download, check out the best free SQL Server downloads: That's kilobytes - not megabytes, not gigabytes, but just mere kilobytes. Say that we create a table: Now see what you did?
It's long again. Doggone it, it's hard teaching this stuf in an email. Anyhoo, in this table, each record takes up just a litle bit of space. It's the same 4 bytes whether that number is 1 or 1,, If all of our employees average about 10 characters in their name, that means we could ft about records per 8KB database page.
In reality, there's some overhead because SQL Server also needs to use some parts of the page to store metadata, and we'll talk about that later in the training. Brent Ozar Unlimited is a small company, so we can keep all of our employees on a single 8KB page. As we insert, update, and delete employees, SQL Server fetches that 8KB page of disk, brings it into memory, makes the necessary changes, and then writes that data page back to disk. Tables are cool, but as we start to dig into what SQL Server's doing under the hood, we want to start thinking about these three object types: Heap - a table with no clustered index.
In my dbo. Employees table, I didn't specify in what order SQL Server should store my data, so it's just going to slap the data down on my 8KB page in any old order. Clustered Index - what we normally think of as a table.
If I'd have created my table like this: That way, when I search for EmployeeID 42, it can go directly to that number without scanning through all of my employees. The clustered index is sorted in the order of the EmployeeID feld, but it's actually the full copy of our table, including all of our felds - in this case, just EmployeeName.
This copy of my table will only include the felds specifed in my index EmployeeName , plus whatever felds it needs to get back to the clustered index in this case, my clustering key, EmployeeID. All three of these objects - heaps, clustered indexes, and nonclustered indexes - will be stored on separate sets of pages.
We won't have the clustered index and nonclustered index for the same table on the same page - they're split. That's why when we're doing space analysis, we have to think in terms of indexes, not tables. Scroll down to the examples link in that Books Online page, and you'll get queries to examine a single table - I'd strongly recommend starting with a small table, because some of the parameters for this DMF will cause SQL Server to actually look at every page in the object.
That means if all of the pages for that object aren't cached in memory, SQL Server will go pull those pages of disk, and that can slow down your running SQL Server. This DMF also includes average record size and max record size. This makes for fun spelunking: Now, don't go changing your database structure just yet - you can easily break applications when datatypes change. Let's leave that for later. I use real database pages from the StackOverfow. Your Homework Let's start thinking about what objects are taking up space in our databases.
Is your database a hoarder, clutching on to a bunch of nonclustered indexes that aren't geting used, and are just taking up space? The details columns in the results will show how big each index is, and whether it's geting used or not.
This is important because the more indexes you have: As we start to move up the Hierarchy of Needs from capacity into performance, you'll start to see how these foundational items are so important. You pass in a database name, fle number, and page number, and SQL Server will return the nearly-raw contents of that page along with diagnostic information.
It's a fun way to get a peek under the hood.
This topic is a good example of how knowing the basics of database internals can come in handy when you step back and think about database server performance. What Pages are In Memory? SQL Server uses memory for lots of things: Last section, we looked at the 8KB pages in our database. Those pages are the same whether they're on disk or in memory - they include the database ID and the object ID, so if we looked at all of the pages in memory, we could fgure out which tables are being cached in memory right now.
The below query gives us the magic answers, but be aware that the more memory you have, the longer this will take. That might be completely okay - if you only regularly query just that amount of data - but what if we constantly need all GB, and we're constantly pulling it from disk? I get so excited by these concepts. How fast are the cached pages changing? From the moment we read an 8KB page of disk, how long does it stay in memory before we have to fush it out of the cache to make room for something else we're reading of disk?
The longer, the beter, as I explain in my Perfmon tutorial. Do the results change based on time of day? This is a one-time snapshot of what's in memory at the moment, but it can change in You might be surprised at how litle memory is used for caching data. If you have automated processes that run a bunch of reports in a single database at 2AM, then the memory picture will look completely diferent then. Are we caching low-value data? If you mix vendor apps and in-house-writen apps on the server, you'll ofen fnd that the worst- writen application will use the most memory.
Thing is, that might not be the most important application. Unfortunately, we don't have a way of capping how much memory gets used by each database. This is why most shops prefer to run vendor applications on separate virtual machines or servers - this way, they don't hog all the memory on a SQL Server that needs to serve other applications.
Do we have enough memory? It's the safest, easiest performance tuning change you can make. If you're in a VM or running Enterprise Edition, the memory question gets a lot tougher. Are we using memory for anything other than SQL Server? If we've got Integration Services, Analysis Services, Reporting Services, or any other applications installed on our server, these are robbing us of precious memory that we might need to cache data.
Put your management tools on a virtual machine in the data center, and remote desktop into that instead. Can we reduce memory needs with indexes? If we've got a really wide table lots of felds or a really wide index, and we're not actively querying most of those felds, then we're caching a whole bunch of data we don't need.
Remember, SQL Server is caching at the page level, not at the feld level. The less felds, the more data we can pack in per page. The more we can pack in, the more data we're caching, and the less we need to hit disk. When I tune indexes on a server I've never seen before, sys. The database with the most stuf cached here is likely to be the one that needs the most index help. I kept complaining to my SAN administrators because my storage didn't respond fast enough - my drives were taking 50ms, ms, or even ms in order to deliver data for my queries.
The SAN admin kept saying, "It's okay. The SAN has a cache. That's way less than what the SQL Server has in memory. Not gonna happen. We need to track: We will never have two records in here with the same phone number. We have to tell our database about that by making the phone number our primary key.
When we make the phone number the primary key, we're telling SQL Server that there can be no duplicate phone numbers. That means every time a record is inserted or updated in this table, SQL Server has to check to make sure nobody exists with that same phone number.
As of the year , there were about , people in Miami. Throw in businesses, and let's say our table has , records in it. That means by default, every time we insert one eensy litle record, SQL Server has to read half a million records just to make sure nobody else has the same phone number! Well, that won't work, will it? So let's organize our table in the order of phone number. That way, when SQL Server inserts or updates records, it can quickly jump to the exact area of that phone number and determine whether or not there's any existing duplicates.
It's called clustered because - well, I have no idea why it's called clustered, but the botom line is that if you could look at the actual hard drive the data was stored on, it would be stored in order of phone number.
Now we have the table organized by phone number, and if we want to fnd people by phone number, it'll be very fast. While our computer systems will usually need to grab people's data by phone number, our customers and end users ofen need to get numbers by other ways. That's where non- clustered indexes come in. In which we pretend the phone company, but instead of giving everybody unlimited calling, we just organize the data. What Goes First? A Non-Clustered Index Our customers constantly need to fnd people's phone numbers by their name.
They don't know the phone number, but they know the last name and frst name. We would create an index called the White Pages: Think about how you use the white pages: You scan through pages looking at just the leters at the top until you get close 2. When you get close, you open up the full book and jump to the right leters 3. You can quickly fnd the right single one record Now think about how you would do it without the White Pages.
Think if you only had a book with , records in it, organized by phone number. You would have to scan through all , records and check the last name and frst name felds. The database works the same way, except it's even worse! If a developer wrote a SQL query looking for the phone number, it would look like this: When you, as a human being, go through that list of , phone numbers, you would stop when you thought you found the right John Smith.
The database server can't do that - if it fnds John Smith at row 15, it doesn't mater, because there might be a few John Smiths. Whenever you do a table scan and you don't specify how many records you need, it absolutely, positively has to scan all , records no mater what. If the database has an index by last name and frst name, though, the database server can quickly jump to Smith, John and start reading.
The instant it hits Smith, Johnathan, it knows it can stop, because there's no more John Smiths. Covering Fields: Helping Indexes Help You But that's not always enough. Sometimes we have more than one John Smith, and the customer needs to know which John Smith to call. Afer all, if your name was John Smith, and the phone book didn't include your address, you'd get prety tired of answering the phone and saying, "No, you want the John Smith on Red Road.
He's No, but we include it for convenience because when we DO need it, we need it bad. And if we DON'T need it, it doesn't really hurt us much.
This is called a covering index because it covers other felds that are useful. Adding the address feld to our index does make it larger. A phone book without addresses would be a litle thinner, and we could pack more on a page. We probably don't want to include the Address 2 feld, because the Address 1 feld is enough to get what we need. When building covering indexes, the covering felds go at the end of the index.
Obviously, this index would suck: That wouldn't be as fast and easy to use. That's why the covering felds go at the end, and the names go frst - because we use those. This is more efcient than organizing the phone book by frst name then last name because there are more unique last names than frst names.
There are probably more Brents in Miami than Ozars. This is called selectivity. The last name feld is more selective than the frst name feld because it has more unique values. For lookup tables - meaning, when users need to look up a specifc record - when you've narrowed down the list of felds that you're going to use in an index, generally you put the most selective feld frst. Indexes should almost never be set up with a non-selective feld frst, like Gender.
Not that that's a bad thing - but no mater how much of a suave guy you think you are, you don't really need ALL of the women in Miami. This is why non- selective indexes aren't all that useful on lookup tables.
This rule is really important for lookup tables, but what if you aren't trying to look up a single specifc record? What if you're interested in a range of records? Well, let's look at I prefer ordering trucks of food. Another Index When we need to fnd a dog groomer, we don't want to go shufing through the white pages looking for anything that sounds like a dog groomer.
We want a list of organized by business category: We'll work with several of the records. Here, we're searching for a range of records, not just a single one. Notice that we didn't put the most selective feld frst in the index. The feld "Business Name" is more selective than "Business Category". But we put Business Category frst because we need to work with a range of records. When you're building indexes, you not only need to know what felds are important, but you have to know how the user is fetching records.
If they need several records in a row next to each other, then it may be more helpful to arrange the records like that by carefully choosing the order of the felds in the index.
Learning More About Indexes Indexes are really important, so we'll be covering these in more depth in the next two emails. In the meantime, here's a few great resources on geting started with indexes: Our index resources page - where we've got posts and videos about heaps, indexing for deletes, partitioning, and more.
Expert Performance Indexing by Jason Strate and Ted Krueger book - covers how indexes work and how to pick the right one. Also available on Kindle.
In this week's episode, we're going to spend just a paragraph or two covering the other types of indexes, starting with covering indexes.
The second book in the list is about SQL code smells , which talks about common SQL practices which every programmer should avoid dealing with the database. It contains code smells and I think the list is probably growing to , you can find more SQL code smells on their website.
This is again a good book for all developers who write SQL queries or deal with the database. The third and fourth book is about Microsoft SQL Server database, one focus on transaction log management, probably an advanced concept for many developers but very useful for DBAs and the second one is an introductory book on Microsoft SQL server database. A good book if you want to learn SQL Server from scratch.
The list is by no means exhaustive and first, a couple of books are really short which you can finish in a couple of days but they are also packed with a lot of useful information which every programmer who writes SQL query or works with the database should be aware of. Phil Factor, Grant Fritchey Download: This book will immensely help you to improve your application's database performance. Once again big thanks to Red Gate for providing these quality books for free.
It's a multi-part course but quite good from the admin perspective. Ross Mistry and Stacia Misner Download: Unlike most of the eBooks which are either older or not relevant, this book is both latest and relevant because it covers Microsoft SQL Server As I said, the first two books are seriously good and I recommend every programmer and application developer to read those books, not once, but twice.
They are just too good and summarized years of experience in just a few pages. The last a couple of books are good to start with Microsoft SQL Server database and learn the transaction logging in detail. And if you like online courses to learn a new technology then here are a couple of free courses you can use to learn Spring, Java, Apache Spark, Angular, and Android Development this year:.
Thanks for reading this article so far.