way0utwest

Database Maintenance Essentials – Resources

I told people in New York at SQL in the City that I’d post some resources on the blog from my talk. My apologies for not getting it done over the weekend, but during a little downtime in Austin I’m getting it done.

Checklist

From the last slide, a checklist of things for you to look at on your instances.

Backups scheduled on all database (full and log)
DBCC CHECKDB running regularly on all databases
Test restores scheduled
Manage mdf/ndf/ldf file sizes
Proactively monitor and maintain indexes and statistics
Monitor jobs and set up alerts

Challenge

At work, someday soon, but in the next 30 days, go through the checklist on your important servers, or all your production servers, and assess your maintenance.

Resources

From the slide deck, which will come soon in email. These are a list of links and resources from the talk.

Fragmentation Recommendations from Paul Randal – http://www.sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx
Backup Script from Ola Hallengren – ola.hallengren.com
How to Monitor Your SQL Server for Performance and High Availability
How to Defragment Indexes for Peak Performance
Inside the SQL Server Transaction Log
2012 Diagnostic Queries (Glenn Berry) – http://sqlserverperformance.wordpress.com/category/sql-server-2012/
SQLFool Missing Indexes – http://sqlfool.com/2009/04/a-look-at-missing-indexes/
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx (part II, part III)
Multiple log files and why they’re bad – SQLskills, Paul Randal
Reorganizing and Rebuilding Indexes – http://msdn.microsoft.com/en-US/library/ms189858%28v=SQL.90%29.aspx
Restore with Verify Only – http://msdn.microsoft.com/en-us/library/ms188902.aspx
Database Tuning Advisor Overview – http://msdn.microsoft.com/en-us/library/ms173494%28v=sql.105%29.aspx
Transaction Log File VLFs – http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Duplicate Indexes – http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx

October 1, 2012

SQL in the City

That’s where we were on Friday. 780 Third Avenue, in the middle of Manhattan. The start of our 2012 US SQL in the City tour.

I arrived Thursday, getting into the city in time to go running in Central Park. A solo #sqlrun, but one I enjoyed on a beautiful fall afternoon in the city. Then it was a short walk to rehearse a few things for the event.

Coming out of the elevator, I could see a bunch of new swag on the book shelf. Water bottles? Very cool. I snagged one for my daughter, and she loved it.

A nice setup, lots of demo booths, all ready for people to arrive.

Plenty of coffee cups, sugar, etc. ready to be stocked

And a big banner in our library. There was a set of almost every book we’ve published over the last couple years, and it’s quite a list of books. We have most of these available as free ebooks on SQLServerCentral, so check them out.

Rehearsals went fine in the main room. I think everyone checked out their laptop on the projector. Since I walk, I took a few minutes to see how the projector would affect me. It was right in my line of sight, but I mentally prepared myself to cross over it a few times.

Then it was off to dinner and an early bedtime. Friday morning I was up at 6:15 for a run. As I came downstairs, I noticed Grant had already run, and checked out his route. Not the one I was planning on.

The streets were wet, and I almost went back inside, but decided not to. I ended up having a nice run as the sun came up on the city. Lots of people out in the early morning, which is strange for someone that lives in the middle of nowhere. Even in most cities I visit, there aren’t that many people running at 6:30am.

As you can see, my run was more organized. I cleaned up and headed to the venue, arriving just before the rain came down.

We had banners outside, but had to bring them in. Fortunately I had my hat to keep dry. People wandered in and filled up the main room. Simon Galbraith, one of the joint CEOs at Red Gate, gave a nice keynote, talking about the challenges we face as an industry and how Red Gate is working to better serve our customers. Not only are we trying to build great products at value prices, but we are also putting on events like this, working with the community to educate users. We even support Open Source, funding the Glimpse project.

I had the first two sessions, talking about essential database maintenance and preparation for disaster. Lots of developers and accidental DBAs in the crowd and while I didn’t have many questions in the session, I did get some in the hallways later.

I watched a bit of other sessions, but mostly ended up chatting with different people throughout the day. It’s great to meet people at events, and the diversity and variety of people I meet in New York is amazing.

We had a great time, though I ducked out just before the closing ceremony, prizes, and drinks. With a busy tour, I wanted to get to the airport and get home Friday night. I made it, though it was a challenge. Huge thanks to United for outstanding customer service in moving my flight arrangements.

A great start to the tour, and I’m looking forward to Austin today.

October 1, 2012

Full Text Search – CONTAINS

I’ve been working on a new presentation for full text search and brushing up on some of my T-SQL operators. Part of my talk goes into the CONTAINS operator, which is one of the full text search keywords you need to know.

This operator is only used with full text indexes, so if you have a column that isn’t full-text indexed, it returns an error. If I issue this:

SELECT *
 FROM dbo.salary
  WHERE CONTAINS(empname, 'Steve')

I get this:

Msg 7601, Level 16, State 2, Line 3

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘dbo.salary’ because it is not full-text indexed.

I have a table that is full text indexed and I can issue a basic query, which looks like so many other T-SQL queries.

SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(*, 'AlwaysOn')
 ;
 go

This returns me all the rows where the columns in the full-text index (I used the star, *), have the term “AlwaysOn” in them. In this case, I’m hitting a FileTable table with lots of whitepapers in there.

This query is essentially a LIKE search, but it isn’t doing character matching. Instead it is working with those keywords in the full text index. I’ve used a simple search above. I could replace the * with the column, in this case the file_stream column.

SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(file_stream, 'AlwaysOn')
 ;
 go

I could also use a prefix term and the * wildcard, similar to LIKE.

SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(file_stream, 'Always*')
 ;
 go

These match other rows where “always” is the document, which matches “always” as a standalone word as well as “alwayson” as a term.

I could also limit the search to particular columns, using parenthesis and commas to separate them out. The BOL example from the CONTAINS page does a nice job of showing this.

Use AdventureWorks2012;
GO
SELECT Name, Color
 FROM Production.Product
 WHERE CONTAINS((Name, Color), 'Red');

This is just a very basic look at CONTAINS. In another post, I’ll look at a few more possibilities with this term.

September 27, 2012

Outsourcing Security

firewall — Are security services like this going to be outsourced in the future?

Security is becoming more of an issue for many companies. Increased regulation, more scrutiny from the media, and customers that are more concerned about their data handling and privacy are forcing companies to spend more resources to ensure better security. However building secure systems takes skill and experience, something many companies lack. Acquiring those skills can be hard, and I think as an industry, we do not disperse information on secure coding well. If we could get developers and administrators to read about security with the same level of interest they show in the iPhone 5 v the Galaxy S3 v the Lumia 920 debate, perhaps things would be different.

One vendor is betting that companies will look to outsource security needs. Webroot has moved to a hosted service, confident that customers will move to the new service for its ease of implementation and arguably better protection. By controlling the software themselves, and updating it as quickly as possible to meet new threats, Webroot can do a much better job of ensuring security than most companies can by managing the service themselves.

That’s an interesting idea, and it’s similar to the idea of threat detection that Bruce Schneier wrote about a decade ago. This doesn’t solve application issues, but it can improve security for threats that attack your network. That’s if the vendor actually does their job correctly.

There are any number of issues with this model, but as more companies get comfortable with the idea of renting services and platforms, moving to the “cloud”, I can see this idea growing. For us in the data world, this means that we have another group to interface with, potentially audit to ensure they are not violating any data protection. As far as your actions if you find issues? That can be complicated, but it certainly will require that your communication skills are strong.

The Voice of the DBA Podcasts

We normally publish three versions of the podcast each day for you to enjoy. Today there is no podcast due to Steve being ill. Hope to have the podcasts return tomorrow.