SQL Server with Mr. Denny


May 17, 2013  5:04 PM

Recommended reading from mrdenny for May 17, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny

May 15, 2013  2:00 PM

Error Handeling and DBCC CHECKDB

Denny Cherry Denny Cherry Profile: Denny Cherry

A client that I’ve been working at for a while needed to start doing DBCC CHECKDBs on their production server.  We hadn’t been for a little while due to maintenance window constraints on their old production server.  Now that we’ve scaled the application across 6 different servers we can now do DBCC CHECKDB on the production server regularly to ensure that there aren’t any database corruption problems on their production server.

As a part of this configuration we wanted to do the DBCC CHECKDB as a part of the backup job so that after the DBCC CHECKDB is complete the databases will then be backed up.

The catch here is that we didn’t want to have to dig through the errorlog file to figure out what the database was that’s having the problem, and we didn’t want the job to fail if there was a problem so I started looking at catching errors when running DBCC CHECKDB.  Sadly there aren’t really any good ways to do this.  TRY/CATCH doesn’t work because DBCC doesn’t actually throw error messages like a normal SQL statement does.  It returns the errors, but it doesn’t actually throw the errors so the CATCH block isn’t actually captured.  Running DBCC CHECKDB within an EXEC sp_executesql doesn’t catch the error either for the same reason, the error isn’t thrown it is simply displayed.  (The reason that the errors from DBCC CHECKDB show up in Management Studio in red I would assume is because SSMS is catching the format and displaying it correctly.)

To catch the errors I had to resort to some old school “error handeling” using the @@ERROR system function.  While this isn’t perfect, I’m not looking for perfect here.  I’m just looking for something that says that there’s an error so that I can send an email when there is an error then continue to loop through the databases looking for others with problems.

As this is step 1 of a multistep job this step is configured to move on to the next step on success or failure (as an email will have been sent and the data logged to the ERRORLOG) then we’ll procede to the backups.

The code that I’m using looks something like this…

/*Populate the table #dbs with the databases that need to be checked.*/
DECLARE cur CURSOR FOR SELECT name from #dbs
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS 0
BEGIN
SET @sql = 'DBCC CHECKDB ([' + @name + '])'
EXEC sp_executesql @sql
IF @@ERROR 0
BEGIN
set @subject = 'CHECKDB failure for ' + @name
set @body = 'DBCC CHECKDB failed for database ' + @name + '
Command run was: ' + @sql
exec msdb.dbo.sp_send_dbmail...
END
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur

There’s some custom code at the top which figures out which databases to process so that the job step runs DBCC CHECKDB on the same databases which the job will backup (this is figured out based on the database size, if the database is online, and if the database is currently being bulk loaded).  This code isn’t shown as it’s not relivant to this specific problem.

Denny


May 5, 2013  2:38 PM

Two Factor Authentication Shouldn’t Depend on One Factor

Denny Cherry Denny Cherry Profile: Denny Cherry

Bank of America has decided to implement two factor authentication on their website when doing specific things like adding a remote account to transfer money to, or when doing a wire transfer (basically anything where money is going to leave the account). So far this sounds like an excellent plan. The second factor is that when I want to send money to another account or send a wire transfer they’ll send me a text message and I then enter the one time use code they text me into the website.

All this sounds perfect (except for if I’m out of the country and I can’t get their text messages), except for one little issue.

Adding a new cell phone to send a text message to is as simple as just logging onto the bank’s webpage. Once I log into the site I can simply add another cell phone, verify that I have the cell phone via a text message and then I can use that cell phone to approve any wire transfers. All very convenient. The problem is that is someone else figures out my username and password for the website they to can add a cell phone to my bank account, approve it for use, then start sending wire transfers off all my money to their account.

So while Bank of America has two factor authentication, the second factor is dependent on knowing the first factor. For this to be actually useful two factor authentication it would need to require that I go into a branch with my ID to prove that I’m me and that I can add the phone as a two factor authentication phone. Additionally they should be using as an option one of the phone application based two factor authentication processes so that if I have several phones I can just use the one application, or if I’m not in the country I can still manage my money (which has been a problem a couple of times).

While I applaud the effort that Bank of America has put into having two factor authentication, doing it correctly would be a lot more useful.  As currently you have one factor authentication with an annoyance.

Denny


May 3, 2013  10:25 AM

Atlanta SQL Saturday PreCon

Denny Cherry Denny Cherry Profile: Denny Cherry

At the upcoming SQL Saturday in Atlanta, GA I have the honor of delivering a precon on database security which has several seats still available.

This precon is a full day session where we will be talking about all the security best practices.

Signing up for the precon is pretty simple, just do to the URL for the precon and fill out the form.

I hope to see you there,
Denny


May 1, 2013  2:00 PM

I’m using simple recovery, so the log file isn’t important right?

Denny Cherry Denny Cherry Profile: Denny Cherry

One of the big myths of SQL Server resolves around the transaction log, and how it’s used with the simple recovery model.

Many people think that the simple recovery model doesn’t use the transaction log.  More specifically they think that it’s there because it has to be there, but that SQL Server doesn’t actually use it.  The reality is that SQL Server still uses the transaction log, much like it does in full or bulk logged recovery modes.  There are some transactions which are going to be minimally logged, but for the most part the INSERT, UPDATE and DELETE commands are going to be fully logged just like normal.

What SQL Server does with the transaction log in simple recovery model is that when the transactions are committed they are written to the transaction log and the pages are dirtied in the buffer pool.  When checkpoint runs the dirty pages in the buffer pool are written to the disk.  Everything up to this point is basically the same as with the other recovery models.  Once the checkpoint has been completed things get different between simple recovery and the other two recovery models.  With the simple recovery model the virtual log files which were just checkpointed and had their dirty pages written to disk will be marked as no longer in use (status=0).  With bulk logged and full recovery this doesn’t happen until the transaction log backup has been completed.

Hopefully this helps dispel the myth.

Denny


April 26, 2013  5:04 PM

Recommended reading from mrdenny for April 26, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


April 24, 2013  2:00 PM

Write about what you care about. If you do the page views will happen.

Denny Cherry Denny Cherry Profile: Denny Cherry

Ask any SEO “expert” and they’ll tell you to write stuff that’ll get you lots of page views, to use key words to lure people in.  The problem with this approach is that you end up not writing about the stuff that people want to read week over week, month over month.

Now getting people to show up on your site once isn’t all that hard.  Say something shocking, something like “The iPad sucks really bad”.  (Feel free to insert some other product in there.)  But this will only get the people to your site once, maybe twice.  In the long term you need to keep the reader coming back week over week, month over month.  To do this you need to write about things that you care about.  When you start doing that people that care about the same things that you care about will find your site and they will read your content.  And most importantly they will come back week after week, month after month, as long as you keep posting content that is relevant to your readers they will keep coming back.

Now that said yes I do realize that I’m breaking my own rule here.  Most of the time I’m writing on this site about SQL Server (or other kinds of technology stuff) and not how to maximize page views, but sometimes I like to step outside my comfort zone a little bit, and this would definitely qualify.

Denny


April 19, 2013  5:03 PM

Recommended reading from mrdenny for April 19, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


April 17, 2013  2:00 PM

Advertising != Engaging

Denny Cherry Denny Cherry Profile: Denny Cherry

Some people just really don’t get social media. Recently a twitter account which belongs to a SQL Server training company who based on their website is a legit company. In what I can assume was supposed to be an advertising campaign they completely ruined their social media standing, and in 7 minutes managed to get their twitter account suspended. Grant Fritchey summed up what they did perfectly in this tweet.

Their plan was apparently to message a lot of people (I counted about 70 including myself) with a link to their website. Apparently a lot of the people who received the message reported them as a spammer because within about 7 minutes of the first tweet their Twitter account was suspended thus ending their social media campaign.

I’m guessing that the goal of the tweets was to drive some business to their training program, but that has managed to backfire.

Lots of people make this mistake, and it’s a pretty easy one. Engaging with people that might become customers is a great thing, however engaging with those people doesn’t have to mean advertising and shoving your website down their throat. If people want to find your website they will. You don’t need to shove it at them. This is the digital version of the flier in the (snail mail) mailbox that everyone just throws away. But in this case the people getting the flier have a way to stop the flier from being delivered ever again. You’ve been blocked, and probably reported for SPAM which has gotten the account suspended and you’ll have to beg Twitter to let you have your account back.

Hopefully they have figured out that this isn’t the approach to take, and hopefully if you were planning on doing this you’ve changed your mind.

Remember Advertising <> Engaging and the goal of social media is to engage not to advertise.

Denny


April 12, 2013  5:03 PM

Recommended reading from mrdenny for April 12, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: