Let’s assume you want to get started with Oracle. Maybe your employer is switching to Oracle, maybe you just want a career change. Where do you go to get started?
There’s no need to feel lost.
Getting the Database
You can get a hold of the Oracle database in two main ways – a VM or installing it yourself. Using a VM is definitely the easiest way to get started. Oracle have provided a Oracle VM VirtualBox image that you can install. If you’re not familiar with VirtualBox, that’s okay; Oracle has set up instructions that will get you up and running quickly.
What if you want to install Oracle yourself?
You can get started with Oracle Express Edition. Hit that link and scroll all the way to the bottom. You can download Oracle Express Edition 11g Release 2. 11gR2 is the previous release of Oracle but it’s good for learning basic Oracle concepts and you’ll find a lot people are happily running Oracle 11gR2 in production.
If you want to be on the latest and greatest version of Oracle, you’ll need to download a full edition of Oracle. Even though there’s no Developer Edition of Oracle, there are five editions available to choose from. Personal Edition contains most of the features of Oracle Enterprise Edition and can be purchased from the Oracle store. If you want practice with complex DBA tasks, you’ll want to use Enterprise Edition. Otherwise, Personal Edition is the right choice.
You can also download and install the binaries directly from the Oracle database download page and run a full copy of Oracle while you evaluate the software. To the best of my knowledge, it’s only servers that are part of the development-production cycle that need to be fully licensed.
If you’re even lazier, you can spin up an instance of Oracle in one of many different clouds. Both Microsoft Azure and Amazon Web Services have a variety of different Oracle database configurations available for you to choose from.
Some people are self-directed, others prefer guided learning. I find that I’m in the second camp until I develop some skills. If you need to get started quickly, guided labs are a great way to ramp up your skills.
Oracle has created a huge amount of content about the Oracle database. The Oracle Documentation Library is the Oracle equivalent of TechNet. In addition to product documentation, ODL contains several courses – the 2 Day DBA is a good place to get started. From there you can head off into various tuning or development courses or even explore on your own.
It’s easy to get started with Oracle. You can either:
Every single thing you do with a virtual SQL Server starts with one very important concern: how are you going to license it?
The 3 Common Ways to License Virtual SQL Servers
The software licensing section of my local library
Developer Edition, by the guest – Developer Edition has all the power of Enterprise Edition, but it’s way cheaper (like in the $50/server range) and you’re not allowed to use it for production purposes. Because the licensing is so cheap, just mix these VMs in with the rest of your guests on a shared pool of hosts. Developer Edition licensing is handled differently than other SQL Server editions, typically bundled with your developers’ MSDN subscriptions.
Enterprise Edition, by the host – When you have 5 or more guests, it usually makes sense to buy a dedicated pair of host servers in an active/passive configuration. For example:
Dell R720 Secondary – same hardware config – ~$25k USD
8 physical cores of SQL Server Enterprise Edition licensing and Software Assurance – ~$55k USD (you only have to pay licensing on the physical cores regardless of whether you enable hyperthreading)
Hypervisor, Windows, management software licensing, shared storage – left as an exercise for the reader
You can configure your hypervisor (VMware, Hyper-V, Xen) to use one of the hosts as the primary, and when it fails, automatically boot up the guests on the secondary R720. This way, you only have to license one of the two hosts, and you can run an unlimited* number of SQL Servers on this host. (* – Not really.) Even better, even though they may be small VMs, you can still use Enterprise Edition on these guests and get access to cool EE-only features like online index rebuilds.
As your SQL Server needs grow, you can license the Secondary and balance the VM load across both, and even start to add additional licensed hosts. The key, though, is to avoid running any non-SQL-Server loads here because these are your utmost expensive host cores.
Standard Edition, by the guest – When you have 4 or fewer SQL Server instances, and they don’t need enough hardware horsepower to merit a dedicated host pool, then you’re usually best off licensing each guest with Standard Edition. This way, you can intermingle them on the same hardware that the rest of your virtual guests use.
At around $2k USD per core, it’s roughly 1/4 the price of Enterprise Edition, but you have to keep an eye on the number of virtual cores you’re using. You may read training material that says “just add vCPUs when you have performance pressure,” but this is expensive advice when you’re licensing each guest by the core. As you approach 16 vCPUs of Standard Edition licensing, you need to build a plan to migrate over to the host-based Enterprise Edition licensing approach. 28 vCPUs of Standard costs about the same as 8 vCPUs of Enterprise, but only the latter lets you run an unlimited number of guests, use over 64GB of memory on SQL 2012 and prior, do online index rebuilds, etc.
How Licensing Affects Your Administration
If you need SSRS, SSIS, or SSAS, and you’re licensing hosts with Enterprise Edition, you simply deploy these other services on separate virtual servers. You never make these other services fight with the core SQL Server engine for the same memory. However, if you’re on Standard, you saved money on licensing, but you spend extra performance troubleshooting time. You’ll need to install these services on the same VM as the engine to keep costs down, but you’ll need to understand how to triage performance.
When you design a high availability solution, the edition determines your choices as well. If you’re on Standard, and you need automatic failover for OS & SQL problems, you’ve backed yourself into a corner of either failover clustered instances or synchronous database mirroring. These solutions add complexity that shops without a DBA often can’t afford, so they end up going without OS/SQL protection and sticking with the simpler hypervisor-based failovers. On the other hand, once you’ve decided to license the hosts with Enterprise Edition, you can take advantage of AlwaysOn Availability Groups and asynchronous database mirroring without extra licensing costs – even for your smallest VMs.
Knowing your licensing means faster decision-making on architecture, and that’s why you want to start your virtualization projects here. Licensing is usually the most expensive and most restrictive part of the project – get it right first.
Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, September 16! Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!
Curious how you can give a compelling technical presentation? Join Kendra to learn five important tips on how to select the right topic for your talk, write an effective abstract, construct a coherent presentation, and make it to the podium to give your first presentation.
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
Until now, SQL Server training usually meant registering for a course, showing up, hearing a lecture from a podium (often from someone who’s only done demos on their laptop, not production systems), working through a few workshops, and then going home with a bunch of ideas. It was like trying to ride a rollercoaster by jumping on as it came speeding toward the platform, having a great time, and then jumping off at high speed again. No graceful transitions, just a shocking experience.
We wanted to do better.
We wanted to ease students into the experience, get them more involved with the material ahead of time, connect the dots between the demos and their real environment, and empower them to help each other with their studies. We wanted to mix up the training so that it was less about theory, and more about their real world environment. Finally, as students left the classroom, we wanted to ease them back into the real world in a way that empowered them to succeed.
We couldn’t accomplish all of those goals immediately – doing this kind of practical, realistic, and comprehensive training is really hard work, so we’ve been gradually experimenting with more and more tactics this year. Here’s what we’ve come up with so far.
What It’s Like to Attend Our Training
We start mixing things up as soon as someone registers for one of our courses. For example, our How to Be a Senior DBA students were assigned a series of private pre-requisite training videos as soon as they registered for the course. They get started learning about RPO, RTO, and SQL Server’s options to meet their availability goals. (Behind the scenes, it took us years of work to get our e-commerce setup and training videos to this point, but the integration is definitely paying off now.)
On the first day of the in-person class, we started by discussing a couple of real-life case studies – then turned their own environment into a real case study. Each attendee used worksheets to describe their current production environment’s requirements to the person sitting next to them, and that person acted as a consultant. The team worked together to sketch out the right long-term solution for a company’s needs, and then they switched roles. Each student was able to sit on both sides of the issue – describing a problem, and building a solution for someone else.
In just one hour, this combination of pre-learning plus collaborative exercises accomplished three goals: it reinforced the student’s knowledge, helped them get a second opinion about their systems, and built a relationship with other people in the room. The relationship-building paid off throughout the course as the students carried on lively discussions at every break, over every lunch, and hanging around the flipcharts to talk shop. I wish I could say this was part of our giant master plan, but it was just a happy discovery that we made back in San Diego, and we’ve been using it ever since.
Another discovery this year was that students learned better when we assigned them script homework before the course. For example, before the SQL Server Performance Troubleshooting class, students were assigned scripts to run on their production servers and copy the results into Excel.
In Chicago, one of the students came up to me and said:
“I’m so glad you gave us the wait stats homework before class. I’d run the queries on several of my slow production servers, and I didn’t understand the results at the time, but now it all makes perfect sense. I understand why the data warehouse has PAGEIOLATCH and the web app has CXPACKET, and I know exactly what to do next.”
As we’d been covering wait stats, she’d been comparing her results with what we were discussing, and she was able to make a simple set of steps for her next maintenance window – all while sitting in class. Some of our students even used our lab scripts on their live production servers, fixing issues as we discussed them. (That might be taking the concept a little too far!)
We’re starting off with a day of lectures on performance tuning. We cover how to think like SQL Server, using wait stats to know why SQL Server is slow, tuning with the plan cache, beating anti-patterns, building the best indexes, and a challenge for attendees.
We weren’t content with that. People keep asking for more hands on sessions like “Watch Brent Tune Queries.” You really want to see our thought processes at work. We’ve done just that. Attendees think through the problem and sketch out their answers. Then they spend the day watching us solve it – they watch me doing query tuning, Kendra doing index tuning, and Jeremiah working through execution plans. At the end of the day, we judge their answers together.
We’re also offering a package deal – some attendees have said they want to watch recordings of the sessions later to reinforce what they’ve learned. (They already get the demo scripts, but sometimes it’s better to watch us doing it.) For an extra $200, the attendees get our 7-hour Developer’s Guide to SQL Server Performance videos that cover about half of what we’re teaching in-person, and we’ve extended similar deals to our 2014 training class attendees too.
For 2015, our new week-long courses represent another new experiment. This year’s attendees kept telling us, “As long as I’m out of the office, I might as well be out for the whole week.” Sounds good! So we’re taking more time to dive deeper into more topics.
We’re excited about how we’re doing SQL Server training differently than anybody else on the market, and we can’t wait to share it with you.
Make sure you expand the following plus sign – this is where all the good stuff is stored:
After expanding it, you’ll get a huge list of categorized advice for trace flags, cumulative updates, MAXDOP settings, and much more:
Detailed performance advice
I haven’t read through the details on this yet, but I’d note that this advice is focused on high performance workloads. Don’t go enabling trace flags without understanding what they do and their side effects.
I continue to be impressed by the documentation Microsoft is putting out. Books Online keeps getting better and better, and this is the coolest KB article I’ve seen in a long time. Way to go, Microsoft!
You store data in SQL Server, but you don’t have enough work to keep a full-time DBA busy.
In just half an hour, you’ll learn the basics of performance troubleshooting and index tuning from me – a recovering developer myself. I’ll show you the basic care and feeding of a Microsoft SQL Server instance and give you scripts to keep you out of trouble:
In this session that I gave at the 24 Hours of PASS 2014, I cover these steps:
1. Start every new table with a clustered index.
A clustered index tells SQL Server, “Here’s how I want you to sort the data.” Without that definition, SQL Server just sloppily throws your data anywhere that it can find space. While that might be awesome for insert speeds, it’s not so good as a universal data structure when we need to make performance tuning as easy as possible.
Generally, your clustered index needs to be on fields that are:
As you learn more about indexes, it’s tempting to micro-optimize them by saying, “Well, I think my end users are going to query this data on a combination of date and warehouse number.” Don’t over-think that before the users start using the app. Index designs are easy to change later.
As it runs your beautifully crafted queries, SQL Server is constantly gathering index diagnostics. It keeps track of what indexes would have been the most helpful, how many times they would have been used, and how to create those indexes. You can query those diagnostic tables yourself, but frankly, it’s painful.
That’s where the free sp_BlitzIndex comes in. Download it, install it on your production server, and then run it in your database. It gives a funny sanity check on which indexes you could probably drop, and which ones you should add.
Run it, and then cover your eyes. It’s going to produce all kinds of heinous warnings about the awful mess that the last guy stuck you with. I only want you to focus on two warnings:
Warning: Index Hoarding – these are nonclustered indexes that are in place now, but nobody’s using them. SQL Server has decided these indexes just aren’t the most efficient way to get your data. Disable these rather than dropping them, because that way if they’re needed again, you can just enable them again as explained in that link.
(Now would be a good time to mention that if you’ve been bossing SQL Server around by specifying index hints in your queries, you’re gonna have a bad time. Your queries will fail.)
Warning: Missing Indexes – SQL Server wants a copy of your table stored in a different order to make queries go faster. Careful adding these willy-nilly – aim for 5 or less nonclustered indexes per table, and each index should have 5 or less fields on it. Avoid indexing XML, VARCHAR(MAX), NVARCHAR(MAX), or other big data types because the index will be huuuuge.
If you’re on SQL Server Standard Edition, creating indexes can lock your table while you work, so do the actual adding in development first. Time how long it takes, and depending on your dev hardware’s speed and database size, you’ll have a rough idea of what it will look like in production. Then do the doin’ during a maintenance window or after hours. If you’re lucky enough to use SQL Server Enterprise Edition, you can use the WITH (ONLINE = ON) parameter while creating indexes to have a lower impact on your end users.
And remember that first step where we said to start with a clustered index? If you’re thinking SQL Server will recommend the right clustered index for you, think again.
Repeat this process every Friday. See, SQL Server empties out the contents of this diagnostic data whenever SQL Server restarts, and then it constantly keeps piling the data back up. On Friday, even if your server was restarted over the weekend, you’ve got some good diagnostic data to check because users have been hitting the app all week. (This is why you can’t run it in your dev/staging environment and get the same good recommendations.) Within a month or so, you won’t believe the difference in performance.
3. Build the Suckerboard weekly with sp_BlitzCache®.
As it’s executing queries, SQL Server tracks which queries get run most often, and which ones use the most server resources. It’s like capturing a trace of your server, but even better because SQL Server is already doing this for you. You can query the dynamic management views to pull the data out, but ain’t nobody got time for that.
That’s where the free sp_BlitzCache comes in. Download it, install it on your production SQL Server, and then run it with these parameters:
This will give you the top 10 queries that have run the longest in total. For example, if a query runs for 10 seconds, and it’s been called 1,000 times, then it’ll have 10,000 total seconds of duration, so it will rank higher than a query that was only ran once and ran for 500 seconds.
This is the opposite of the leaderboard – this is the Suckerboard. It’s the Hall of Shame, the queries that we’re not too proud of. The good news is that it’s usually really easy to fix these once you know what they are. On the far right of sp_BlitzCache’s output, you can click on the query plan to see the query’s execution plan, and you’ll often find things like missing index warnings or zombies.
Repeat the process with a few other parameters to see the highest CPU users, the ones that read the most data, and the ones that ran most frequently:
Repeat this every Friday and email the list of queries to your team. This has an amazing effect – people see their queries on the Suckerboard, and they immediately wanna work on those queries to get them off the Hall of Shame. It becomes a competition to keep off the Suckerboard.
After a month or so, you’ll be very comfortable with the list of queries. You’ll know them right away at a glance, and you’ll know why they show up – like what kind of work they’re doing. This makes you extremely well-equipped to deal with the next step.
4. When slowness strikes, use sp_WhoIsActive and sp_AskBrent®.
When someone comes running in screaming that the SQL Server is slow, run these two stored procedures:
sp_WhoIsActive lists all of the queries that are running right now, ordered by longest-running to newest.
sp_AskBrent checks a bunch of common potential issues on your server and warns you if a data file is growing, a transaction is stuck rolling back, a backup is running, and so on.
5. When you need to learn more, here’s what to do.
And if you’re coming to PASS, we’re holding a performance tuning class on Monday & Tuesday called Make SQL Server Apps Go Faster. If you learned something in this article & video, then you’ll learn a LOT in the class. Read more about it now.
DBAs reading this: stop freaking out.
I know, I’m simplifying a lot of stuff in here. When you read the sentence, “Index designs are easy to change later,” you instantly pop a vein in your forehead because you remember that time you had to change a clustered index on a 1TB data warehouse.
Take a step back and put yourself in the frame of mind of a developer who’s alone in a dark room with an application, a SQL Server, no DBA, no budget, and no time to get everything perfect. I’m trying to give them the tools to get good-enough performance while spending less than 2 hours a week worrying about their database.
Sure, there’s all kinds of interesting scenarios and tricks you’d like me to explain on this page – but remember, the time they spend reading this page and learning techniques counts against their 2 hours of spare time per week. Focus on the things that will truly make a difference in performance, and ease off the academic “well actually” stuff.
Thanks for tuning in to our webcast again this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, September 9! Not only do we answer your questions, we also give away a prize at 12:25 PM – don’t miss it!
The SQL Server community is unlike any other technical community. We have so many helpful people and our pick of resources. When you want to contribute, you have many options – from speaking to writing, attending conferences to running user groups, answering forum questions to writing articles. Which should you invest your time in to help your career and personal growth? Let me share my experiences with all of these so you can make the best choice.
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
Let’s say you’re a DBA managing a 2TB database. You use SQL Server transaction log shipping to keep a standby copy of the database nice and warm in case of emergency. Lots of data can change in your log shipping primary database: sometimes it’s index maintenance, sometimes it’s a code release, sometimes it’s just natural data processing.
And when a lot of data changes, your warm standby sometimes is a lot less warm than you’d like. It can take a long time to restore all those log files!
Here’s a trick that you can use to help “catch up” your secondary faster. A quick shout-out to my old friend Gina Jen, the SQL Server DBA and log shipper extra-ordinaire who taught me this cool trick years ago in a land far far away.
Log shipping Secret Weapon: Differential Backups
Through lots of testing and wily engineering, you’ve managed to configure nightly compressed full backups for your 2TB database that are pretty darn fast. (No, not everyone backs up this much data every night, but stick with me for the sake of the example.)
Log shipping primary had a full backup last night at 2 am
Log shipping secondary has transaction logs restored through 7 am
It’s 3 pm, and you’d really like to have everything caught up before you leave the office
Here’s an option: run a compressed differential backup against your log shipping primary. Leave all the log shipping backup and copy jobs running, though — you don’t need to expose yourself to the potential of data loss.
After the differential backup finishes, copy it over to a nice fast place to restore to your secondary server. Disable the log shipping restore job for that database, and restore the differential backup with NORECOVERY. This will effectively catch you up, and then you can re-enable the log shipping restore and you’re off to the races!
But Wait A Second. Aren’t Those Full Backups A Problem?
Running a full backup without the COPY_ONLY keyword will reset the “differential base”. That means that each differential backup contains changes since the last full backup.
But here’s the cool thing about log shipping: restoring a transaction log brings the new differential base over to the secondary.
So as long as you’ve restored transaction logs past the point of the prior full backup, you can restore a differential to your log shipping secondary.
This Sounds Too Good To Be True. What Can Go Wrong?
This isn’t foolproof. If you haven’t run a full backup in a long time, your differential backup may be really big, and taking that backup and restoring it may take much longer than restoring the logs. (Even if you’re using log shipping, you should be doing regular full backups, by the way.)
And like I mentioned above, if your log restores are so far behind that they haven’t “caught up” with the last full backup taken on the primary, you’re not going to be able to restore that differential backup to the secondary.
What If a Transaction Log Backup File Is Missing?
A technique like this could work for you, as long as a full backup hasn’t run since the transaction log backup file went missing. (If it has, you need to re-setup log shipping using a full).
But a word of warning: if you have missing transaction log backup files, you have a “broken” log chain. You should take a full backup of your log shipping primary database to get you to a point where you have functioning log backups after it, even if you’re going to use a differential to bridge the gap on your log shipping secondary. (And keep that differential around, too!) Keep in mind that you won’t have point-in-time recovery for a period around where the log file is missing, too.
You love Q&A sites like StackOverflow.com and DBA.StackExchange.com, but sometimes it’s hard to find interesting questions that need to be answered. So many people just sit around hitting refresh, knocking out the new incoming questions as soon as they come in. What’s a database person to do?
Use the power of the SQL.
Data.StackExchange.com lets you run real T-SQL queries against a recently restored copy of the StackExchange databases. Here’s my super-secret 3-step process to find questions that I have a shot at answering.
Step 1. Find out how old the restored database is.
I take a glance at StackExchange.Dba and StackOverflow to make sure I’m dealing with a relatively recent database restore. These database restores are done weekly, but you know how it goes – sometimes scheduled jobs fail.
Questions with many comments but no accepted answer
If you click on the Post Link, you’re taken to the live question. If the database restore was a few days ago, keep in mind that the live question may have changed or been answered by now.
The other columns help me see at a glance if this is a question I’m interested in. In the above screenshot, the third question, “New database server hardware,” has 2 answers, but none of them have been upvoted, and there hasn’t been any comments since 2014-05-13. It’s old, and the questioner has probably moved on, but you can revive the question by posting a really good answer. You can also take your time, knowing nobody else is really active on it right now. These are great opportunities to post a really in-depth answer for other folks to find as they search the web later.
Which brings me to my next favorite query:
Step 3. Find questions that people keep looking at.
Obscure error numbers are usually going to pop up here because there’s not much public information about ‘em. Now’s your chance to write an answer explaining how to troubleshoot that error number, and presto, you’re the next Jon Skeet.