Voice of the DBA

Author: way0utwest

  • Why Two Devices

    I used to travel with two laptops, one as a backup, however lately I’ve switched to taking the iPad and a laptop. The iPad doesn’t double as a backup, but it does let me do things like take notes at events or conferences with an extremely long battery life. My laptop only gets 2-2.5 hours, and when you’re on the go for 10 hours, you need something else. I also use the iPad for demos of SQL Monitor, from Red Gate, using http://monitor.red-gate.com/ 

    However I’ve been tempted to get a real second machine, something that is truly a backup. I read Brent Ozar’s post on his MacBook Air, and it’s really tempting to get one of those, especially as I use VMware, which works agree both Mac and PCs.

    Seth Godin as another interesting argument for a second machine. Use one for pleasure, or distractions, and one for work. That’s not bad, but I kind of do that with my phone. I read on it and listen to music, as a break from the laptop (or sometimes in conjunction with it).

    I’m a practical guy, and don’t want to get (too many) gadgets for fun, especially laptops. I went through that last year, going from a Win 7 tablet to the iPad, which was a good move for me. At least my daughter thinks so.

    For now I think I’ll stick with just the iPad and my Lenovo. However when I look for a new laptop, I have to say that the MBP will be high on my list, just to try something new.

  • Five Lines of Code

    An interesting story on how a database speeds up by changing some application code. Worth the read for sure.

    I caught this from Andy Warren’s blog, and it shows why we need developers to think for themselves and ask relevant questions, not just meet the spec. If someone had asked if Accuweather needed micro-granularity for location earlier, they might never have had issues.

  • Regular Audit Analysis

    Do you regularly review audit data?

    I was reading over a digital supplement that I received from Dark Reading recently, which details some of the issues in the Epsilon, Gawker Media, and a few other data breaches. It was light on details, but there were some nuggets of knowledge in there about how these attack occurred. Some were sophisticated, and some were insider attacks, but the advice given to help protect your data was all similar: limit access, watch for injection, audit, and monitor.

    I know that over the last decade as I’ve run SQLServerCentral, the topic of security and auditing has grown in importance. More and more people are implementing auditing functions in their applications and slowly tightening security where they can. There is a lot of work to do, and a lot more education that needs to be spread to a wider audience, but the trend is positive.

    However one thing in the article caught me eye, and it had me wondering how many people are going beyond the basics. For those of you that have auditing built into your application or database, I have a question this week:

    Do you regularly analyze the audit data to look for abnormal trends or access?

    All the data in the world doesn’t have any value if it’s not used. In a security context audit data isn’t all that useful if it’s only examined when an incident is discovered. The real value in auditing data is the ability to uncover problems before they occur. Looking for inappropriate access, unusual access for a particular individual or application, or even repeated attempts to gain access can help prevent a data breach.

    After all, catching the criminal later doesn’t necessarily mean you’ve “recovered” the data. Unlike physical objects, data can easily be copied and spread in way that prevents it’s complete recovery.

    Steve Jones

    The Voice of the DBA Podcasts

  • Setting a Unique Index on a Bit Field

    Can you set a unique index on a bit field? Well, you can, but you’d end up with a very short table of two (or three) rows. I defined this table:

    CREATE TABLE [dbo].[BitTest](
    [MyBit] [bit] NULL,
    [MyName] [varchar](50) NULL
) ON [PRIMARY]

GO

USE [db1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_BitTest] ON [dbo].[BitTest] 
(
    [MyBit] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

    and then added some data

    INSERT BitTest SELECT 1, 'Test'
INSERT BitTest SELECT 0, 'Test2'
INSERT BitTest SELECT NULL, 'Test 3'

SELECT * FROM BitTest

    This table has these rows:

    bittest2

    Adding another row:

    INSERT BitTest SELECT 1, 'Test 4'

    gets you an error:

    bittest3

    However what about a compound index? What if I make the table larger and add more fields. Here’s a larger table:

    CREATE TABLE UniqueBit
( BureauID INT
, CompanyID INT
, DivisionID int
, DefaultType BIT
, ProductName VARCHAR(100)
)
GO
INSERT Uniquebit SELECT 1, 1, 1, 1, 'Product 1'
INSERT Uniquebit SELECT 2, 1, 1, 1, 'Product 2'
INSERT Uniquebit SELECT 3, 1, 1, 1, 'Product 3'
INSERT Uniquebit SELECT 4, 1, 1, 1, 'Product 4'
INSERT Uniquebit SELECT 1, 2, 2, 1, 'Product 5'
INSERT Uniquebit SELECT 1, 2, 3, 1, 'Product 6'
INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

    If I now add a unique index:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueBit] ON [dbo].[UniqueBit] 
(
    [BureauID] ASC,
    [CompanyID] ASC,
    [DefaultType] ASC,
    [DivisionID] ASC
) ON [PRIMARY]
GO

    It works fine. I can add another unique row like this:

    INSERT Uniquebit SELECT 4, 2, 1, 0, 'Product 7'

    without an error. Adding in a non-unique row:

    INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

    gives me an error:

    bittest4

    There’s nothing special about a bit column for a unique index. There are restrictions for bit fields in some ways that relate to indexing, but uniqueness is not one of them.

    This was inspired by this post (before the complete details from the OP): http://www.sqlservercentral.com/Forums/Topic1126794-149-1.aspx#bm1126850