Skip to content

That didn’t last long

Here was my speaking schedule, a page I updated Wed night.

speakschedule

Late yesterday I got emails notifying me of acceptance to a new event. Two whole days without something scheduled.

One Database to Rule Them All

This is what you build to juggle 6,000 tweets a second. That’s the headline that caught my eye and it’s about the challenges of Twitter and the data that they handle. Twitter definitely has a tough problem, one that few of us have, but perhaps they can help us learn to better deal with our own data from their experiences on an edge case.

The story is journalistic, not so technical, but it is interesting. Twitter has struggled with a blend of data that is partially crucial and must be consistent now (usernames) and other data that can be a bit out of date (tweets). They also have lots of unstructured data (photo/video) that is combined with more traditional, structured data. They’ve used a few different database platforms to store this data and assemble it with their application. That’s the same things that most of us also do when we deal with many different types of data.

However Twitter is trying to find away around dealing with disparate systems. They’ve had a number of engineers working on Manhattan, their database designed to handle both structured and unstructured data. And because they work for Twitter, this platform is designed to manage all of this data with very high workload demands at scale.

It will be interesting to see if they come up with any innovative ideas. Certainly SQL Server already has options for managing structured and unstructured data, though perhaps not at the scale Twitter needs.

Steve Jones

FileTable–Adding a file to a folder

At my Filestream/Filetable talk yesterday at SQL Intersection, someone asked me about programmatically adding a file to a Filetable. Easy enough, I thought, since I’d seen someone do this in .NET and was thinking this has to be simple. Turns out it’s not quite so simple.

I was assuming that I could use the GetDescendent method of the hierarchy ID for the path_locator field to get the path you needed and insert that. It turns out that’s not correct.

I searched around when that didn’t work and found this post from Bob Beauchemin and a question on StackOverflow. If the two, Bob’s post explains things better. He actually dug into the Filetable, looking at the constraints and defaults in the schema. If you do that, you can find this constraint on the path_locator field:

filetable_h

The code in this constraint looks like this, which matches with what Bob and SO show as the way to calculate the path.

ALTER TABLE [dbo].[Explorer] ADD  CONSTRAINT [DF__Explorer__path_l__6477ECF3]  
DEFAULT (CONVERT(HIERARCHYID, '/' +     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' +
     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' +
     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/')) FOR [path_locator]
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This means that your insert statement needs to perform this calculation. For me, I decided to insert a jpg of a circle into the table, under the folder “books”. The folder looks like this:

filetable_g

Since I have the binary values for a circle image, I’ll use those, and then the code below. The first part of this code calculates the new path that’s needed, basing this on the path_locator value for the “Books” folder, and then building a NewID(). From there, it’s a simple insert.

DECLARE @path HIERARCHYID
DECLARE @new_path VARCHAR(675)
 
SELECT  @path = path_locator
FROM    dbo.Explorer
WHERE   name = 'Books'

SELECT  @new_path = @path.ToString()
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         1, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         7, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         13, 4))) + '/'
INSERT INTO dbo.Explorer
        ( stream_id
        , file_stream
        , name
        , path_locator
        )
VALUES  ( NEWID()
        , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
        , 'Circle.jpg'
        , @new_path      
      );
go

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

If I now look at the folder, I see this:

filetable_j

An insert into the folder of a file.

I could easily adapt this to stream in the binary values from a .NET application, or OPENROWSET, but this works well enough.

Nothing Scheduled

As of right now, my United account has no flights booked. My Hilton Honors account has no hotel rooms reserved. My National account has no car rentals to pick up.

And I have nothing scheduled for the rest of 2014.

For now. I suspect things will change soon, but it’s a good feeling for now.

High Performance Encryption

It is becoming more and more important to protect your data from anyone that should not access it, including the DBA. Learn how you can use column level encryption as well as TDE to protect your data files, with minimal performance impact to your SQL Server instance.

This session will look at securing data

  • in flight
  • at rest
  • inside the database

We do this with

  • IPSec/SSL Communications
  • Transparent Data Encryption
  • Partial Hash Bucket values with symmetric encryption.

Slides:

Code:

 

Smart Gadgets

I saw this post on what a smartwatch should look like and it got me thinking. I’ve got a Kreyos watch coming (I hope) and it fulfills some of the things I was looking for, and a few that the Pebble didn’t.

However I wonder where we’ll go with gadgets in the future. My son and I sometimes dream, trying to decide what we’d like to see built in the future. One of the main things we talk about is how we might wear things. A bracelet of some sort seems to be the consensus. Getting something larger that we can see better, but more convenient then a smart phone. Already I think the 5″ ones are a bit large and cumbersome to use. One of my complaints about the Galaxy S4 was that I couldn’t easily use it with one hand.

Glasses are an interesting idea. Google Glass has some good ideas, though I think the idea of including the camera was one that caused a lot of concern from people, and rightly so. Even though cell phone cameras haven’t caused too much of a problem, there certainly have been privacy incidents. I think I’d like the idea of getting some information in my field of vision, and I’d like to try some “smart glasses” at some point.

I found this post on gadgets out there, and most of them aren’t revolutionary and a few aren’t interesting. A USB drive in cuff links or a necklace? Doesn’t seem that interesting to me. What I’m looking for is perhaps some type of mix of gadgets that can work together.

Could I get a phone that connects with various devices that can run for a long time? Perhaps a belt buckle that provides power and also tracks movement like my Fitbit flex? It can connect to my watch as well, providing me with information from a device in a pocket. Ultimately I’m not sure what would be enhance the phone much. Any jewelry might not provide much more than notification of information through vibration, or possible light, and you’d still need a device.

Ultimately I think I’d like to have more ways to connect devices together. Easily get my phone to project on a larger monitor when it’s available, like in the kitchen or car, perhaps even using a camera there to communicate with others.

My hope is we’ll get gadgets that communicate, but with the separation of platforms and the reluctance for many to work together on common APIs, I’m not sure we’ll get there.

An Open Letter to Brogrammers

It’s not in the language I would write it in and it’s not necessarily safe for work, but it gets the point across.

An Open Letter to Brogrammers

One reason I do support Women in Technology, and other groups opposing discrimination. Apart from it’s poor behavior, it’s often just not even valid.

How Long Before You Upgrade?

It’s 2014. SQL Server 2000 is 14 years old, but there are still quite of you managing instances. SQL Server 2005 is 9 years old, and I’m sure more of you still deal with that version. I know because I work for a software vendor and I’m constantly asked if our software will run against those two versions.
For many of you, however, if you’re managing a SQL Server 2000 instance, it might only be 9 or 10 years old. Your company might still have been installing SQL Server 2000 in 2005. The same is true for SQL Serve 2008. I wouldn’t be surprised to find companies still setting up 2005 instances in 2008 or even 2009.
Companies don’t care much about versions. They tend to mostly care about databases getting the job done, and sometimes, support. Many organizations don’t see value in upgrading too often because of the overhead. I suspect many managers would prefer to get many years usage out of a platform before they change in order to minimize work that doesn’t add value to their business.
The question this week asks you about the longevity of your database instances. Think about the average instance, or even the majority of your applications and how long they will remain on a particular version.
How many years will you run a platform before you upgrade it?
Years ago I heard someone at a large Fortune 100 company say their stated policy was to get 10 years of service out of a database server. At the time I thought that was a long time, but the more I think about it, the more I think that might be a minimum amount of time I’d want from a platform.
Let us know this week what you experience, and perhaps what you’d prefer.
Steve Jones

If or When?

I saw this post recently about security and preparing for a data breach. The title caught my eye because it implies that we’re all doomed. Do the rest of you think that? Is it a question of when we’ll have a security breach not if?

Given the headlines, the news we find out about companies not disclosing security issues, the back doors and poor code in much software, is it any wonder that people think it’s a “when” and not an “if”? Given the lack of realization from many companies that suffer incidents that they were even attacked, perhaps that’s an assumption worth making.

We’ve been hacked at SQLServerCentral in the past. I don’t think we’ve been hacked in many years, but I also have no way of knowing. That’s the difficult part of dealing with bits. If they get copied, there’s not necessarily a trace of anything amiss. It’s quite possible that many of us have no idea that our bits are being copied. Every read is a copy of data and how long did the NSA read data without most of us being aware? How sure are we that they, or some other organization, hasn’t been reading much more than was disclosed?

I’d hate to think that our systems are so porous that we’re all likely to get hacked at some point. It’s probably technically possible, but hopefully not likely for most of us. However we should consider that it will happen and ensure we have some handle on our data security. It’s hard, and complex for most of us, and I’d like to think that Microsoft will recognize this and build better controls and features into future versions of Windows and SQL Server that enable easier auditing, granular permissions, and separation of duties.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Vote for me at SQLBits

SQL Bits XII is coming this July to Telford in the UK and voting is underway for sessions. I’ve submitted a few and am hoping to get accepted to go back. You can help.

You can log into the site and vote for the sessions you’d like to see. I’d prefer you vote for me because I’d like to go see friends and meet new people, but feel free to vote for the sessions you’d like to see.

The ones I’ve submitted are:

I’m not sure how long voting lasts, but take a few minutes today and pick your ten favorite sessions.

Follow

Get every new post delivered to your Inbox.

Join 4,299 other followers