1TB Databases for Developers [Video]
If you write code that accesses a database one terabyte or larger, you need to know that things are different around here. When you hit the very large database (VLDB) territory, you need to pay particular attention to statistics, TempDB, and staging tables. Microsoft Certified Master Brent Ozar will share his favorite lessons for developers who work with either OLTP or data warehouses in this 25-minute webcast:
The links discussed in the webcast include:
SQL Server 2012 Query Performance Tuning by Grant Fritchey – we talked about the statistics analysis chapter as an example of what matters much more in the terabyte territory.
SQL Server 2012 Internals and Troubleshooting – the storage chapter helps you pick the right storage for your TempDB, which matters much more in VLDBs.
Download SQL Server 2012 Management Studio – free download for all Microsoft SQL Server users. I’d recommend using this even if you’re not using SQL Server 2012 on the back end – it’s a better development environment. Bonus – check out Jes’s SSMS tips and tricks video.
Want to learn more? We’ve got video training explaining it! In our 90 minute video series How to Think Like the SQL Server Engine, you’ll learn:
- The differences between clustered and nonclustered indexes
- How (and when) to make a covering index
- The basics of execution plans
- What determines sargability
- How SQL Server estimates query memory requirements
- What parameter sniffing means, and why it’s not always helpful
For $29, you get 18 months of access to the videos for one person. You can watch them at work, at home, even on your iPad. Learn more about it now.
sp_Blitz™ v22: Output Results to a Table, Better Comments, and an App
Our free server troubleshooting tool, sp_Blitz™, just learned some new tricks. If you call it like this:
EXEC dbo.sp_Blitz
@OutputDatabaseName = ‘DBAtools’,
@OutputSchemaName = ‘dbo’,
@OutputTableName = ‘BlitzResults’
It will create the table DBAtools.dbo.BlitzResults (if it doesn’t already exist) and insert the results. The table includes columns for the server name and the check date, so you can query this table to see what’s changed since the last time you ran sp_Blitz™. Plus, there’s more:
Added new checks for disabled CPU schedulers (due to licensing or affinity masking), extended stored procedures, disabled remote access to the DAC, databases in unusual states like suspect or emergency, logins with CONTROL SERVER permission.
Easier readability – whenever anyone’s asked how to add checks, I’ve always just said, “Read the code.” Last week, I read the code with fresh eyes and realized it could use some cleanup and comments. I used Red Gate SQL Prompt to clean up the formatting, which is better but could still use some work. The code is nearing 4,000 lines, so I moved the old changes out of the proc and into a new online changelog. I put in some time to explain what the proc is doing, and that should help people reading it from the first time. I’ve also started an sp_Blitz™ Documentation page where I’ll be explaining some of the more advanced uses.
New downloadable sp_Blitz™ app for Windows - over and over, we heard from people that they wanted a quick way to generate a print-friendly copy of the sp_Blitz™ results. Jeremiah worked with a developer and built our first app:
sp_Blitz™ Windows App
The app creates sp_Blitz™ in TempDB on the server of your choice, executes it, shows you the results, and can output the results to PDF – complete with links to the explanations for each result.
We’ve got lots of ideas for the app, but we wanted to get it out there first and let you help drive it. If there’s something you’d like to see added or changed, leave a comment here or contact us. The whole point of this thing is to make your life easier. I wish this tool would have been available back when I was a production DBA struggling to figure out what was going on in my environment, so every little improvement helps. Hopefully this can save you from the stress I had back then. Get it from the download page and enjoy!
Write a Note and Shoot Yourself Today
When I’m writing a presentation or blog post, I often start here:
2004 Brent
It’s a photo of me in my office in Dallas, Texas in 2004. When I look at that picture, I remember everything like it was yesterday. I can talk at length about everything on the bookshelf, on my desk, in my drawers (the desk drawers, that is).
I can tell you what technology problems I was struggling with, plus what problems my manager was concerned about. I remember what I knew, and what I didn’t know yet. I can recite the web sites I frequented.
Next, I can turn the mental camera around and see exactly what’s outside my office door: my developers and my support team. I can tell you what they rocked at and what they wanted training on. I can remember how we decorated their cubes for their birthdays – covering Julian’s stuff in aluminum foil, building a princess’ castle for Hima.
The funniest thing, though, is that I didn’t remember any of this until I rediscovered this photo several years ago. All of a sudden, everything was clear to me.
And I realized who I was writing for.
Now, it’s really easy for me to scope my presentations and blog posts because I’m writing for 2004 Brent. 2004 Brent hadn’t studied databases and tried to turn them inside out – he just needed to store data and get it back out quickly. He wasn’t on a first name basis with book authors and MVPs – he didn’t even know what an MVP was.
You need to take this picture today.
Set up your camera with a self-timer or get a colleague to shoot a few pictures of yourself sitting in your work environment. Get pictures of the books on your shelf, the stuff on your desk, and maybe take a screenshot of your task list. Write yourself a one-page note covering:
- The stuff you’re comfortable with
- The stuff you’re uncomfortable with
- The things you want to learn this year
- The things you learned recently that surprised you
Stash these pictures and words away in a time capsule folder somewhere. A few years from now, when you’re writing a presentation covering something you’ve learned, get these back out. Think about what you knew and didn’t know, and that’s your target audience. Before you use a term or acronym, think back and ask, “Did 2013 Me know that? If not, lemme introduce the topic.”
When you’re writing, remember that you’re never writing for your current self. You’re writing for the past version of you. Having these pictures and words will help you define your audience.
Answers to Common High Availability and Disaster Recovery Questions
In our recent webcast on HA & DR basics for DBAs, we got a bunch of questions that couldn’t be answered fast enough. Here you go:
Q: Do you have book recommendations for replication, mirroring, and the other topics?
Absolutely. Check out the resources on our HA & DR Basics video page.
Q: Do any of these techniques work for companies with just 1-2 IT staff?
To keep things simple, I’d check out virtualization replication like VMware SRM. SRM isn’t necessarily easy, but if you use that one technique, you can protect all of your virtual machines no matter what they’re running. That’s easier than learning different HA/DR techniques for a lot of different applications.
Q: For 1,000 databases on an instance, what uses more resources – mirroring or log shipping?
Technically, the answer is going to be log shipping because you’re probably already doing transaction log backups on those databases anyway. However, when you’re running thousands of databases per instance, several other interesting issues arise. How do you react to newly added databases? Can you back up all 1,000 in a timely manner, or do you need to roll your own transaction log backup jobs to run simultaneously? What’s your RPO/RTO? There’s a lot of neat questions that determine the real answer at this scale. Feel free to contact us for consulting help – we’ve got clients who run as many as 10,000 databases per instance, and we can help you learn lessons the easy way instead of the hard way.
Q: What happens if my DR is in a different datacenter, on another domain?
Your life is harder. Many built-in solutions become more challenging, and even just getting your users to be able to connect to the database become more challenging. This is a scenario where I like to step back and ask, “What problem are we trying to solve by using different domains?”
Q: Do my HA and DR servers have to be on the same subnet or VLAN?
No, all of SQL Server’s HA/DR features work on different subnets and VLANs.
Q: Do you have to kill all SQL Server connections before restoring a database?
Yes. Restoring a database – even a similar copy – means starting over with all-new data. Restoring transaction logs means SQL Server will be jumping around all over inside the database, and it doesn’t have time to deal with your queries. 
Q: Does clustering work for SSAS, SSIS, and SSRS?
Microsoft sells these things inside the same box as SQL Server, but for all practical purposes, you should consider them different products. They’re like free toys that just come in the same box, but they’re wildly different. The techniques you use to protect the SQL Server engine won’t be the same as the techniques you use for the rest.
Q: Can you read a database mirror?
Not directly, but you can take a snapshot of it and read the snapshot. I rarely see this used, though, because in order to refresh the snapshot, you have to drop it – and that means kicking everybody out of the snapshot. Users don’t seem to be a big fan of getting their queries killed.
Q: What Windows Server edition is needed for clustering and AlwaysOn Availability Groups?
You can use any version of Windows Server 2012, but if you’re still using Windows Server 2008R2, you’ll need Enterprise Edition.
Q: How do you get hosts in different data centers into the same cluster?
Clusters don’t need shared storage anymore. This is why I wrote the post Everything You Know About Clustering Is Wrong. Things really have changed a lot in the last several years around clustering.
Q: How well do these features work over a slow connection?
If you’ve got enough bandwidth to keep up with copying transaction log backups as they’re happening, you can stay up to date. If you can’t, they don’t work well.
Q: Can you install SQL Server Enterprise on Windows Standard?
Yes.
Q: I just joined the webcast and it’s almost over. Can you send me the slides?
You’d be surprised how often I get this question. This is why we make our material available for free on our YouTube channel. I don’t usually share the slide decks, though, because I’ve had some nasty incidents of people taking my slide decks, plagiarizing them, and presenting them as their own material.
Q: I’m using an AlwaysOn Availability Group. If I fail over to an asynchronous replica, will there be data loss?
Probably. If your primary has been doing a lot of work, and hasn’t been able to send all of that work to the asynchronous secondary, you’re doing to lose that data. It’s up to you to monitor the DMVs to see how far behind the replicas are.
Q: Is (feature A) better than (feature B)?
Yes.
Q: Really?
No. They both suck.
Q: Seriously, I need an answer.
Getting to the right answer means knowing your RPO, RTO, business needs, staff capabilities, hardware, network throughput, and more. If you ask a stranger to guess the right answer for you with just five minutes of background information, odds are it’s going to be the wrong answer. You’re going to have to buckle down and do some research into the features and your business needs, or you can bring in an experienced outsider who does this for a living. This is what we do, and we can help you get to the right answer as quickly as possible, and probably for less money than you’d expect.
Q: Is there a way of automating failovers for SQL Agent jobs for HA purposes?
If you’re using clustering, then this is built in. If you’re using a solution that does failovers at the user database level, then things get trickier because you have to know which jobs you want to fail over. For example, you probably don’t want backup jobs failing over (because you can just run those jobs on all nodes), but you might want a few database-based jobs failing over. That’s left as an exercise for the reader, though, because every shop’s needs are different – often even different between database servers in the same shop.
Q: What’s the right quorum configuration for my cluster?
Check out the excellent Books Online quorum sections for Windows Server 2012 and Windows Server 2008/R2. It’s a lot of reading, but it’s worth it – the quorum decision is a big one.
Q: Can I integrate clustering with (mirroring/log shipping/replication/AlwaysOn AGs)?
Clustering is probably the strongest feature in terms of interoperability with other features. It’s been around a long time, so it’s got a good interoperability story for pretty much everything.
Q: How can one second RTO be achieved in a DR scenario?
By having both the production and disaster recovery systems live at all times, accepting queries. At that point, it’s up to the application logic to try both servers to see which one is available fastest. Some applications do this by timing out their database connections after just one second. Other applications do it by trying both database servers simultaneously and taking the first available connection.
Q: Can you back up a SQL Server to SQL Azure?
Not in the sense that you can restore directly into SQL Azure (or as it’s known now, Windows Azure SQL Database.) However, you can use Windows Azure as a backup solution if you’re so inclined.
Q: Azure is a French word meaning blue. Americans are not pronouncing French correctly.
French is the most beautiful language in the world – when it’s spoken by the French. When the rest of us try it, we sound like we’re coughing up an unfiltered Marlboro. Thank you for your cheese, wine, and wonderful words like lingerie and ménage à trois. You are a beautiful country, and the rest of us apologize for the sounds we make when confronted with French.
Q: Can you cluster inside VMware?
Yes, but if you’re asking the question, you shouldn’t be doing it.
Q: What are the best practice recommendations for achieving 99.999% uptime?
Obviously this is way beyond what I can answer quickly, but the best advice I can give is to remember that there’s three parts to every solution: people, process, and technology. You need written processes that cover exactly how to react to an outage, and you need to rehearse and improve those processes constantly. Car racing teams practice over and over to get the fastest pit stop possible so that their race car spends more time driving and less time sitting. DBAs need to do the same.
Q: What HA option is Brent’s personal favorite, and why?
I like failover clustering because it protects everything in the instance – jobs, logins, and the server name itself – with as little manual intervention as possible. It still has weaknesses in a few single points of failure, and it’s not completely easy to implement and manage. I think the benefits still outweigh the costs.
Cloud Alternatives to Microsoft SQL Server
When people say “cloud”, they’re simplifying a lot of different solutions into a single catchphrase. Let’s break out the different options and compare them.
1. SQL Server in Amazon EC2 and Azure VMs
Amazon EC2 is a virtualization platform. Amazon buys servers, installs their secret sauce software, and rents you Windows virtual machines by the hour. Microsoft offers a similar product, Windows Azure Virtual Machines, that just went officially live.
You can rent a blank Windows VM without SQL Server installed, and then install SQL Server yourself just like you would on-premise. That’s a licensing mess, though – you have to use your existing SQL Server licenses or buy new ones for your VMs. That doesn’t make much financial sense. Instead, Amazon and Microsoft will rent you a Windows VM with SQL Server already configured, and your hourly fee includes the SQL Server licensing.
SQL Server runs just as it would in your own datacenter, which means you can use this as a disaster recovery option for your on-premise SQL Servers. You can do log shipping or database mirroring up to SQL in the cloud, running in Amazon EC2 or Microsoft Azure. When disaster strikes, fail over to your EC2/MS infrastructure, and you’re off and running.
The term “cloud” conjures up images of easy scalability and redundancy, but that’s not really the case here. We’re talking about a single virtual machine. This isn’t much different from running SQL Server in a VMware or Hyper-V guest in your own datacenter or in a colo datacenter. You can use all your traditional tools and techniques to manage SQL Server, which is both a pro and a con. If you need to patch it or scale out to multiple servers, there’s no tools included here. I still consider this the cloud, though, because the infrastructure and licensing are managed by somebody else. It’s easy to get started with one – or a hundred – virtual SQL Servers with no initial investment.
This method is the most conventional, and as I explain other options, I’m going to move from conventional to really-far-out-there. SQL in EC2 or MS VMs just works – it’s easy to understand and leverage without changing your code or your techniques – but it doesn’t bring a lot of the cloud’s benefits.
2. Amazon RDS for SQL Server
Instead of running SQL Server in an EC2 virtual machine, let’s start giving up a little bit of control in order to get more of cloud benefits. The next layer of clouds is Amazon Relational Database Service (RDS). Here, Amazon builds a Windows VM, installs SQL Server, configures it, and manages both Windows and the SQL Server service for you.
This is still the real, true blue SQL Server you know and love – all of your commands still work exactly the same as you’re used to, as long as you don’t try to access the server’s local drives directly. (Example: you can’t upload a flat file to the server’s C drive and then try to BCP data from that file into SQL Server.)
Amazon RDS is kinda like they’re the DBA, and you’re a very, very powerful developer. You can create and drop databases and users, but you can’t Remote Desktop into the SQL Server, nor can you access the drives.
Amazon RDS takes a few job duties away from you:
- Storage management – want faster storage? Just pick (and pay for) more IOPs. There’s no arguing with the SAN guy.
- Monitoring – Amazon CloudWatch tracks performance metrics and sends you emails when they’re outside of your thresholds.
- Patching – You pick the major/minor versions you want and when you want patches applied.
- Backups – You pick a time window for the full backups, and Amazon manages it using storage snapshots. You can restore from snapshots just by pointing and clicking in the management console.
But herein lies the first real compromise: you can’t restore from anything except snapshots. You can’t upload your own database backup file and restore it. To get data into Amazon RDS, you’ll want to export it to a file, upload that file to Amazon S3 (cloud-based file storage), and then import it. This also means you can’t use Amazon RDS as a participant in log shipping or database mirroring.
Microsoft doesn’t have a competitor to Amazon RDS for SQL Server today. Well, I say that, but some shops already manage their SQL Servers this way – they have an internal admin team that manages Windows and SQL. Departments get access to create & drop databases, change code, etc, but they don’t get access to the server’s desktop or backups. This doesn’t really compete with Amazon RDS, though – RDS is for companies who are too small to have this kind of internal engineering infrastructure. (Or for companies that want to get rid of this large engineering burden, I suppose.)
3. VMware vFabric Data Director
vFabric Data Director is a lot like running Amazon RDS in your own datacenter, but you can control the base Windows virtual machines. You build a Windows template to be used by default whenever a new SQL Server is created. VMware vFabric manages the implementation details for backups and high availability.
vFabric also supports Oracle, PostgreSQL, and Hadoop for a single pane of glass to create and manage your database servers. When someone in your company wants a new database instance, the sysadmins open up vFabric, configure it, and within a few minutes, it’s up and running.
vFabric makes sense for 100% virtualization shops who aren’t interested in moving their databases up to the cloud, but they want easier database management integrated into their virtualization tools.
Microsoft doesn’t have a competitor to VMware vFabric Data Director today. In theory, you could build your own alternative using System Center and a whole lotta scripting. That is left as an exercise for the reader.
4. Microsoft Windows Azure SQL Database (WASD)
The artist formerly known as SQL Azure takes the least conventional approach of all. While it’s technically built on Microsoft SQL Server, that’s like saying your pants are built on your underpants. They both cover your back end, but you can’t use them interchangeably.
Microsoft essentially built a new product designed for common database storage requirements. Like your underpants, you only get a minimum of feature and command coverage here. The new features and commands Microsoft has been adding to the boxed product for the last couple of versions just aren’t available in WASD including partitioning, Resource Governor, Service Broker, and CLR. But hey – are you really using those anyway? Most folks aren’t.
Rather than building large databases, WASD encourages developers to shard out their data across multiple smaller databases. While on-premise SQL Servers have had techniques to do this in the past, Microsoft started over and developed a new technique that makes more sense for cloud implementations. Again, though – we’re talking about a difference from the boxed product, something else that developers have to learn differently. As we’ve gone up this ladder into the clouds, we’ve been handling our problems differently. WASD’s partitioning technique is a good reminder that once you’ve gone this far up into the cloud, you’re dealing with something very different from SQL Server. You’re not going to take large volumes of code written for SQL Server and simply point them at WASD – you’re going to be doing a lot of testing and code changes.
Like Amazon RDS, there’s no backup/restore functionality here to get your existing data into the cloud. You’ll be exporting your data to a file, loading it into the cloud, and then…staying there. There’s no database mirroring or log shipping to/from Azure SQL Databases to on-premise SQL Servers.
Which One is Right for You?
If you’ve got an existing app, and you just want to cut costs without changing code, you can get started today with any of the first three options. They support the same T-SQL commands and datatypes you already know and love.
If you’ve got an existing app, and you’re willing to make code changes & do testing, you might be able to save even more plus gain new flexibility by going to Windows Azure SQL Database. In most cases, though, the cost savings won’t come anywhere near the costs required for the code changes and code review. We haven’t seen a case yet where the tradeoff made sense for our clients. I’m sure it’s out there – the perfect example would be a small amount of code that the developers know very well, can easily modify, and produces large server loads in short bursts.
If you’re building a new app from scratch, then let’s take a step back and survey the database industry as a whole. This is an incredible time to be in databases and there’s a bunch of really good options we didn’t even cover here.
Brent Answers Your Virtualization Questions
I’m doing a 1-hour open Q&A session on May 8th for the PASS Virtualization Virtual Chapter. Bring your VMware and Hyper-V questions about setup, performance, management, monitoring, or whatever, and I’ll answer ‘em.
You can even get a head start here – post your questions in the comments below, and I’ll build slides to answer ‘em ahead of time. That way you can make sure you get the best answer possible. (Well, from me anyway, ha ha ho ho.)
Then come join us on the webcast and hear the answers. See you there!
New Magazine Article and Podcast
I joined RunAsRadio’s Richard Campbell to talk about how SQL Server 2012 adoption is coming along, how I do performance tuning, why the cloud hasn’t been catching on with most businesses, and why DBAs need to care about business metrics. Listen up here.
I also wrote about Being Invincible with SQL Server 2012 AlwaysOn Availability Groups for SQL Server Pro. You can preview the article here if you’re not a subscriber. I had a ton of fun with this article – lots of superhero and Superman references.
Free SQL Server Training Videos for Developers
You’re a developer who wants to learn more about SQL Server, and you like learning by watching videos. Here’s some of our favorite 30-minute videos targeted at developers:
Index Design
- Index Basics – Kendra Little
- The Okapis of Indexes – Jes Schultz Borland
- Heaps (Tables Without Clustered Indexes) – Kendra Little
Blocking and Isolation Levels
Query Design and Tuning
- Let’s Talk About Joins – Jeremiah Peschka
- Developer’s Guide to Dangerous Queries – Jeremiah Peschka
- How to Measure T-SQL Improvements – Kendra Little
- Windowing Functions in SQL 2012 – Jeremiah Peschka
Troubleshooting in Production
- First Responders Kit – Kendra Little
- What’s Going On in My SQL Server? – Kendra Little
- The Mystery of Query Timeouts – Kendra Little
SQL Server Setup & Usage
- How Does SQL Server Store Data? – Brent Ozar
- Developers and the Transaction Log – Kendra Little
- Completely Legal Performance Enhancements – Jeremiah Peschka
- SSMS: More than Meets the Eye – Jes Schultz Borland
Whew – that’s a lot of content! Don’t try to conquer them all in one sitting – instead, consider setting up a developer lunch & learn session once a week. The company buys pizza, and everybody watches one of these videos together in a conference room. Afterwards, talk about the lessons from the video and how they apply to the code challenges you’re facing.
If you’ve got Tuesday lunchtime free, check out our upcoming webcast lineup, too. Enjoy!
sp_Blitz™ Version 18 Out: Lots of Bug Fixes
There’s a new version in town. v18 adds new checks looking for:
- Backups stored on the same drive letters as user databases (Thanks @AlinSelicean!)
- Agent jobs that don’t have failure notifications set up (Thanks @Thirster42!)
- Shows free drive space per drive letter if you pass @CheckServerInfo = 1 in. We don’t consider this a problem – we just show additional info if you use this parameter.
- Added the elevated database permissions check back in (whoops! dropped it accidentally)
Plus bug fixes and improvements including:
- Lots and lots of typos
- Ignore max file sizes for filestream data files
- Switched a lot of @@SERVERNAME usage out with SERVERPROPERTY(‘ServerName’) because in some SQL Server cloud implementations, those don’t match, and it’s okay.
- Changed database name variables to be NVARCHAR(128) to handle those monster SharePoint databases
- Improved the non-aligned-indexes check to return results even if the index hasn’t been used
And more. Version 17 also added a neat new exclusions table parameter – if you’re the kind of DBA who wants to automate sp_Blitz data collection throughout the environment, but you want to skip certain checks or certain databases, you can do that now. I haven’t written up full instructions on that, but the basic idea is pretty obvious within reading the first 100-200 lines of the code.
As always, you can get the latest version and instructions from http://www.BrentOzar.com/blitz/. If you’d like to contribute code, you can email us at Help@BrentOzar.com. I’m still working through a backlog of about 20 more new check contributions, and we’ve got some fun stuff coming – including a thank-you page listing the contributors and their work.
[Video] Bad SQL Server Advice for DBAs
Get frustrated when you read conflicting opinions on the web? Me too — I can’t go to sleep when someone’s wrong on the Internet, but it’s tough to correct everybody. In this 30-minute session, I’ll explain the most common bad advice that I see, explain why it’s wrong, and show you how to set up your SQL Server for speed, not slowness.
