Digital Cents

Got Power?

2011-03-07T09:00:00.097-06:00

Have you ever experienced a power outage at work? One that lasted several days? One that affected the entire state?

Hey man, can you give me a jump?

Once you have, I imagine you will think of disaster recovery in a different way. Most of the time, SQL Server performs perfectly, without any intervention from you. However, to get the most out of it, you will need to take a few proactive steps.

I have seen power outages that lasted only a few minutes or even several days. This could be caused by something as simple as someone tripping over a power cable, or a frozen pipe in a power plant a state away.

If they last long enough, your UPS could run out of power. Or, if you are using a back-up generator, it may require refueling.

No matter how it happens, when SQL Server loses power, you have the possibility of corruption occurring. You want to have a basic strategy in place to detect and deal with this.

Data Center

If you work in a large office, you may not normally have Data Center access. However, when all hands are on deck for disaster recovery, you may spend significant time in there. They tend to be cold, so bring a jacket or sweater. Keep an extra one handy in your office so that when you need it, you will have it on-hand.

Did your servers come up before the network or switches? If so, you may have to use a crash cart and the console to log-in to the servers. This involves plugging a mouse and keyboard directly into the server racks and logging in. Hopefully, the servers are clearly labeled so you will know which one is which. It is also handy to have a rolling cart, a.k.a. Crash Cart, to hold the mouse and keyboard. A rolling chair is nice, too. Spending hours on end crouched down or bent over at weird angles can be hard on your body.

SAN Considerations

Did you servers come back online before the SAN did? If so, you may need to start SQL Services manually. Once the SAN is back online, you can use SQL Server Configuration Manager to start SQL Server. Right-click on your instance and select Start. Depending on your situation, you may need to start some of the other services, as well. For example, SQL Server Agent (for your backup jobs) and SQL Server Browser (for your named instances).

SQL Server Configuration Manager

It is possible for write caching to come back to haunt you. While it does provide a performance boost, when you are facing a disaster recovery scenario, it may not seems like a good idea anymore.

If you do not have a battery backup for your write cache, you may have data corruption and inconsistency in the event of a power failure. If you do have battery backup, how long does it last, how often are you checking and changing the batteries? These are not questions you want to consider when your manager is asking you about recovery time.

If you would like to know more about the storage systems underneath SQL Server, I highly recommend reading some of the stuff Wesley Brown (blog | twitter) has put out.

Starting SQL Server in Single-User Mode

Sometimes you need to start SQL Server in single-user mode to do some advanced troubleshooting. You can do this by modifying the Startup Parameters in SQL Server Configuration Manager.

Right-click on your instance and select Properties. Go to the Advanced tab, and look at the entry for Startup Parameters. Place the following code at the beginning of the line: -m;

Be sure to include the semicolon to separate it from the rest of the commands.

Properties, Advanced Tab

Dedicated Administrator Connection

The Dedicated Administrator Connection is a system backdoor that allows you to connect to SQL Server, even when it becomes unresponsive to client connections. This is a good one to file away in case you ever need it.

To connect, you must be logged onto the server that is hosting SQL Server. You are not allowed to make a Dedicated Administrator Connection across the network. When you open SQL Server Management Studio, open a new Query Window and specify ADMIN: in front of the server name.

Dedicated Administrator Connection

Note: if there is already a DAC in use, your connection will fail.

Preventative Maintenance

Backups

Be sure to use WITH CHECKSUM in your backup commands. You are taking backups, aren’t you? As a general rule, I like to take FULL backups once per week, DIFFerential backups daily, and LOG backups every five minutes. You will need to consider your business requirements and devise a backup strategy that satisfies your needs.

BACKUP WITH CHECKSUM

When writing out a backup, the WITH CHECKSUM option makes SQL Server verify each page before it is written to the backup file. If an error is detected, the backup will cease. If you want the BACKUP to continue after an error is found, use the CONTINUE_AFTER_ERROR option.

PAGE_VERIFY CHECKSUM

When the PAGE_VERIFY_OPTION option is turned on, when each page is written out to disk a checksum is calculated and stored with the page header. Then, when the page is subsequently read from disk, the checksum is calculated and compared to the value stored with the page header. If the values are not the same, an error is logged to both the SQL Server error log and the Windows Application Event Log. Look for Error Message 824. This indicates a problem with your IO system.

One common misconception about this option, is that is automatically checks all of your pages. This is incorrect. The check is only made when a page is written to disk and then read back off of it. Once you turn this on, it may take some time until all of your pages go through this cycle.

Schedule DBCC CHECKDB Regularly

Either use SQL Agent Jobs or the Maintenance Plan Wizard to run DBCC CHECKDB at least once per week on all of your databases.

If you have large databases, or 24/7 activity, it may not be feasible to run this on your Production systems. If this sounds like your situation, there is a way out. Do you have DEV and QA systems? Do they get refreshed from Production regularly?

Run DBCC CHECKDB on your DEV and QA systems each time your refresh them from Production. If you detect an error, raise the red flag immediately, and prepare to run DBCC CHECKDB on your production system.

Some DBA Commands To Know

DBCC CHECKDB

DBCC CHECKDB performs a variety of physical and logical checks on every object in your database.

Full version, this can take a long time depending on the size of your database.

dbcc checkdb(YOURDBNAME)
with all_errormsgs, no_infomsgs;
go

Some quicker versions, which don’t take as long:

dbcc checkdb(YOURDBNAME, noindex);
go

dbcc checkdb(YOURDBNAME)
with physical_only;
go

Repairing Problems

If DBCC CHECKDB detects some errors, it will make some recommendations at the end of its messages.

However, Microsoft recommends that you first try to restore from a good backup. This way, you can try to compare the data from the backup and the corrupted database and decide which copy of the data to keep. Most likely, this will be a long and painful process, but it is preferable to blindly losing production data.

If this is not an option, then you can try using the repair options listed by DBCC CHECKDB. Note: This should be considered the last resort.

Before running a repair command, be sure to make a FULL backup.

backup database YOURDATABASE
to disk = 'c:\YOURDATABASE.bak'
with format, checksum, stats=1;
go

You must set the database into single-user mode.

alter database YOURDATABASE
set single_user;
go

Depending on the options you choose, you may need to set the database into emergency mode, as well.

alter database YOURDATABASE
set emergency;
go

Then, you can try some of the repair options. Try this one first, it can fix some problems with your indexes. This is not able to fix issues with FILESTREAM data.

dbcc checkdb(YOURDBNAME, repair_rebuild);
go

If that does not work, then you can try this one next. You may experience some data loss with this one. This should be your last resort.

dbcc checkdb(YOURDBNAME, repair_allow_data_loss);
go

Warning: this option will result in data loss. Try this only after all other options have been exhausted.

Afterwards, set your database back to multi-user or normal, and take another FULL backup.

alter database YOURDATABASE
set multi_user;
go

alter database YOURDATABASE
set online;
go

backup database YOURDATABASE

to disk = 'c:\YOURDATABASE.bak'

with format, checksum, stats=1;

Be sure to take a FULL backup.

For more information about the DBCC CHECKDB command, please refer to the maser, Paul Randal (blog | twitter).

Summary

A power failure can be a stressful thing to deal with. Everyone wants the systems back online as soon as possible. However, it is important to remain calm and be sure to run some basic consistency checks on the databases before allowing users back online.

Help yourself sleep better at night. Be sure to take regular BACKUPs, use CHECKSUM, and run DBCC CHECKDB weekly.

Review - SQL Saturday #57 Houston

2011-02-01T09:00:00.002-06:00

I attended my first SQL Saturday this past weekend in Houston. SQL Saturday is a free event sponsored by PASS, the local SQL Server user group (HASSUG), and some vendors. Well, it was almost free. I did have to pay ten dollars for lunch. It was a delicious selection of eats from Hinze's BBQ.

Schwag:The real reason we come...

In some ways, I found SQL Saturday to be more accessible than a full SQLPASS conference. SQLPASS can be a bit overwhelming; after the first couple of days, your brain starts to get mushy from all the content that has been thrown at you.

Since this was only one day, I was able to absorb all of the sessions I attended. You come away with tons of great ideas, tips, and tricks that you are ready to apply in your shop once you get back home. By keeping the total number of sessions small, I feel you increase the chances of actually following-up with the knowledge you have gained.

Sessions

There were five tracks with a total of forty-two different technical sessions from which to choose. The tracks were divided among Application Development, Business Intelligence, Database Administration, General, and PowerShell.

Each session I attended was full of great information that I can take back and use at work. The speakers were all enlightening and entertaining. I was able to ask questions, trade emails, and get copies of their presentations.

Presenters

The speakers were very engaging, and in some cases, downright entertaining. Several of the presenters were names I recognized from SQLPASS 2010 in Seattle. Either as presenters there, or as people I met during the various social events. So I knew I was in for a treat.

The day began with with Dean Richards (website) teaching us about VMWare and SQL Server. With more and more systems becoming virtualized every day, this is a good foundation for the future.

I followed-up with hometown hero Wesley Brown (blog | twitter) who got so down on storage systems, I started seeing things in terms of sectors, cylinders, and tracks. I loved his joke about RAID0 not being RAID at all, but rather just 'AID'... In other words, your resume may need some aid if you use this in production.

Thomas LeBlanc (blog | twitter) was especially hysterical, and I took in a double dose of his stuff, with no fluff. He covered Execution Plans and Business Intelligence. Many times during his presentations, he had the whole room in laughter. I had to ask if he ever did stand-up.

Another stand-out was Jason Wong (website). He took a normally serious topic, performance tuning, and turned it into a rollicking good time with his dry wit and dead-pan delivery.

One of my favorite sessions was presented by Trevor Barkhouse (blog | twitter) on Deadlock Detection. His was a session I recognized from SQLPASS 2010.

Some of the presenters would give mini-quizzes during their presentations and awards prizes to people who got them right. Others had books to give away from some of the sponsors.

Vendors and Employers

Just like any good technical conference, there were plenty of vendors on hand to show you samples of their products, or offer you trial licenses. In some cases, you had the possibility of winning a full license for their software.

Again, since the scale was more manageable, it is more likely that you will actually go back and download the trial software and try them out.

An added bonus was the presence of a few area employers who are in a hiring phase: sparkhound and intellinet. They each had recruiters and technical staff on hand to answer questions about the company, their projects, and what sort of people they are looking for.

Location

This SQL Saturday was held at the Bammel Church of Christ, who was kind enough to provide the facilities for the conference. I have to say, this was the coolest, most modern church I have been in. Amongst the various rooms was the ever-popular Xbox room, complete with a flat screen TV and fluffy couches.

Networking

I got to meet an interesting cross section of SQL professionals from a wide variety of industries. I was surprised at the number of people I met who came from out of state. After the conference, there was an informal dinner held at Outback Steakhouse. This was a great opportunity to socialize and mix with your fellow SQL peeps.

One thing I wish I had done, was attend the Speaker Mixer dinner the night before. Since I was new, and didn't know anyone, I decided not to attend. However, after meeting everyone on Saturday, and seeing how friendly the group was, I will definitely do this the next time.

At the end of the conference, there was a general meeting for the local user group, HASSUG, where some user group business was discussed, awards given for the volunteers, and a raffle for prizes from the sponsors. Too bad, I didn't win the Xbox or the iPad. There's always next time...

Conclusion

When I consider the quality of the education, and more importantly the food, this event was beyond free. It was more like they gave me money. I received quality technical training, networked with some great professionals, and ate some great BBQ all in one day.

My only disappointment was not being able to snag one of those cool shirts that all the speakers and volunteers were sporting. At the end of the conference, they had a few extras to give away, but alas, I was not able to beat the crowds. You would have thought Metallica was giving away free tickets to their next show.

Another opportunity is to volunteer to help with the next SQL Saturday. This entails arriving early, perhaps the day before, and helping to setup the facility, answering questions, or perhaps a more long-term commitment to help organize the whole affair.

If you decide to go to a SQL Saturday, I recommend traveling there the night before. They start at 8:30am and this way you will be fresh and rested; ready to absorb every last drop of SQL awesomeness. Buy using Hotels.com, you can find a wide range of hotels to fit your price range.

Executive Summary

It was like a mini-PASS conference for a fraction of the price; free!

SQLskills Immersion Training: Internals and Performance

2011-01-23T21:00:00.001-06:00

When I first saw the announcement for the SQLskills Immersion Training I was immediately excited. I have attended some of the stock MSXXXX training courses, and have found them to be OK for a basic introduction to SQL. However, if you are looking for something deeper, or adaptable to your specific questions, then you will likely be disappointed.

I am a SQL Server DBA for a Fortune 500 company where I manage over one hundred Servers and Instances. I am constantly trying to learn more about SQL Server and become a better DBA. Often times, it is overwhelming as there is so much to learn; it is difficult to know where to begin.

Too often, the trenches force one to focus in on a specific area or advanced topic just to get through the day. Being able to go back and focus on the fundamentals of SQL Server and truly understand the internals of SQL Server will give me the knowledge and confidence from which to map out my progression through the rest of the SQL Server Universe.

Every time I learn about a new topic and feel that maybe, just maybe, I have climbed the 1-10 ladder of competency a notch, I realize that there are twice as many things to know than I knew about the day before.

I would love to attend the 5-day Internals and Performance class in Dallas, February 21-25. Doing so would be an awesome springboard to deepen my knowledge of SQL Server and help to advance my career to the next level.

SQLskills is offering a great contest where you can win a free seat to this very class. All you have to do is write a letter to Santa about why you will make the best use of the knowledge from the class and you will get your Christmas in February.

Top Ten List

With apologies to David Letterman, here is my Top Ten list of reasons why I want to learn from SQLskills:

10) Breakfast at Cafe Brasil. Lunch at Cafe Brasil. Dinner at Cafe Brasil. Fourth Meal at Cafe Brasil.
9) I'll have a chance to wear my Cowboy hat and DBA hat at the same time.
8) I love staying up until 4:07am looking at error logs.
7) Dallas in February is way more fun than the Caribbean in March, or Alaska in May.
6) My credit card needs a vacation.
5) Being away for a week, means I can legitimately say "Honey, I would love to help you cut the poodles' hair, but I have to go learn about SQL Server..."
4) I can sing the lyrics to R.E.M's Jackson-Dallas bootleg while coming in for a landing. 'You should see Dallas from a DC-99. Dallas is a jewel, you know Dallas is a beautiful sight...'
3) It would be like watching the MCM videos in 3-D, but without the glasses.
2) Who better to learn SQL Server from, than someone who used to be on the development team?
1) I can finally say I paid Brent Ozar with Salmon Meals and Amy's Fish. Or was that the other way around?

~sa

SQL Saturday #57 Houston

2011-01-21T09:00:00.001-06:00

The other day, I registered for SQL Saturday #57 in Houston. I know what you're thinking; SQL on a Saturday? Don't I have something better to do? Well, yes, I have plenty of things I would rather be doing. But I felt this was a fairly unique opportunity that I wanted to take advantage of.

The first time I heard about SQL Saturday was at SQL PASS 2010 in Seattle last year. I kept seeing people with T-shirts that mentioned SQL Saturday. I asked some folks about it, and I found out it is an event that rotates locations enabling SQL Server Professionals to get together and learn more about SQL Server.

While at SQL PASS, I signed up for Twitter in order to keep track of all the updates and to be able to see what all the attendees were chatting about during the conference. Since then, I have been exploring what is out there. Recently, I saw a posting about the upcoming SQL Saturday in Houston. Well, since Texas is a relatively small state I decided a road trip was in my future.

After looking at the website and schedule of sessions, I was very impressed. SQL Saturday offers some very good training on a variety of topics for no cost. I noticed that several of the speakers were people that I met at SQL PASS; bloggers, presenters, or just some folks I exchanged business cards with.

I am excited about attending my first SQL Saturday. I think it will be a good way to meet some other people in the SQL community and to learn about more about SQL Server. Perhaps exploring a few new topics, or getting a deep dive on something familiar.

Either way, it should be a good experience. If you are interested in learning more about SQL Server, take a look at the SQL Saturday website and see if there is a session near you.

~sa