Voice of the DBA

Author: way0utwest

  • The Platform as Code

    data center
    Trying to manage all your servers individually is like tracing cables in this kind of setup.

    SQL Server has grown more and more complicated over the two decades that I have worked with the product. At the same time anyone can install it and set up a database with almost no training. There are more and more features and functions to learn, yet it’s become a much easier product to use. It seems as though one person may view the platform as very difficult to use while the next sees is as requiring almost no management. Both of these views can be true, but as we say often about SQL Server: it depends.

    When I started working with SQL Server, it was a cumbersome platform, the network configuration could be tricky and it wasn’t easy for developers to master the named pipe connections that were required. Since then it has become a very easy to set up and use platform, with many system administrators managing servers that almost manage themselves. “Almost” being the key word there.

    Just as with other Microsoft platforms, the days of having a set of procedures that handle your daily tasks and lead you through the solution are gone. The platform handles the simple stuff, the routine issues, but it is almost a requirement that a good system administrator be able to write T-SQL queries and string together segments of code.

    I was reading an interview with Jesse Robbins, the founder of OpsCode, a company focused on infrastructure automation. In the interview, he had a few quotes that struck me as very true. “What has happened over a period of time is that sysadmin skill set has been expanding more and more to include more and more basic infrastructure software development.” The second quote deals with managing more and more servers: ” That is not being built on your back every day, when you’re logging into the same 50 boxes and typing in the same 50 commands. ”

    Those quotes were similar to some of the presentations I saw on System Center 2012 at DevConnections. There is a focus on automation, on scripting, on working with groups of machines at scale, not as individual systems. The administrators, whether on Windows, Exchange, SQL Server, or some other platform need to learn how to better administer their systems with code, not with the GUI.Steve Jones

    SQL Server ConnectionsIf you want to learn more about Systems Center and SQL Server, come to DevConnections in the fall. It’s the best conference to learn about all parts of the Microsoft technology stack. Grant Fritchey and Steve Jones will be there speaking along with lots of other great Microsoft technology specialists.

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Managing Risk

    Risk board game
    Are you managing risk appropriately? Or do you suppress contrary opinions?

    Last year I read Team of Rivals, a book about the Lincoln Presidency in the US. It was a very interesting read, partially because of the topic and times, but mostly I was captivated by the diverse cabinet that Lincoln put together. He chose a number of rivals for positions, people that didn’t necessarily get along, and often had very different opinions about how to govern the country. It felt strange at first to think about a leader welcoming in foes and enemies into their governing committee, but Lincoln appeared to make it work, and the variety of thoughts and opinions helped the country get through the Civil War.

    It’s an interesting idea, and this article on the JP Morgan trading losses reminded me of that book. It talks about the need for debate and argument in making decisions. It makes me wonder if too often in technology we make decisions and implement architectures without listening to the dissenting voices. In my experience, the loudest voices, from both technology people and customers, are often the ones that overwhelm discussions.

    Do you welcome dissension in your meetings? I’ve had managers that preferred everyone to be on the same page, or be willing to make a very persuasive argument. The better managers I’ve had encouraged debate, and didn’t allow any one personality to dominate, and usually wouldn’t let the process degenerate into an argument. The better managers also knew that decisions had to be made and debate could not drag on for too long.

    The process makes me wonder if the very outspoken and domineering people working with technology, either as the manager, the client, ro the technologist, cause more problems than they solve. Perhaps if they were more willing to debate and accept the information about risks and issues from the quieter people, we might have more projects completed with fewer issues.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Creating a Filetable

    How do you create a filetable? I assume you’ve enabled Filestream and created a filegroup for your filestream and filetable data. Then you just do this:

    -- Create a filetable
CREATE TABLE AuthorDrafts 
  AS FileTable
GO

    The only optional part of this statement is the table name. No other options, no columns, no schema needed. The FileTable has a fixed schema, which is mostly metadata about the files that you put in it.

    If I were to select from this table, I’d use this statement. I’m not showing all the columns in the results since there are a lot, but they are in the select.

    -- check the table.
select 
   stream_id ,
          file_stream ,
          name ,
          path_locator ,
          parent_path_locator ,
          file_type ,
          cached_file_size ,
          creation_time ,
          last_write_time ,
          last_access_time ,
          is_directory ,
          is_offline ,
          is_hidden ,
          is_readonly ,
          is_archive ,
          is_system ,
          is_temporary
 from AuthorDrafts;
go

    Most of these are really meta data about the file. If I were to drop a table in the share, I’d see results like this:

    filetable1

    Putting files inside the table is really a drag and drop from Windows. I can get the share name for my filetable from :

    -- check the share
select  FileTableRootPath('dbo.AuthorDrafts');
go

    If I paste this in Explorer, I see my file:

    filetable2

    I can drag and drop, or use any scripting commands (Powershell, VBScript, etc) to move files in and out of this share, and they will appear in my table.

    It’s that easy to start working with FileTables. How you use them in your application? That’s a whole other series of posts. I’ll work on a few examples you can use over time.

  • New Security Holes

    Geek Security Holes

    One of the big advertising campaigns of 2011 was the Apple campaign that centered around Siri. This is a voice recognition feature of the iPhone 4S that allows you to manage many of your phone interactions by speaking instead of touch interactions. There are a whole series of commercials about Siri and some rather funny parodies (NSFW). I used Siri briefly on my iPhone 4 before the 4S launch and I didn’t like the interaction, but my wife loves it.

    Apparently, however, IBM doesn’t like the idea of Siri being used inside of the company. IBM’s CIO has banned the use of the voice recognition because the audio and translation are not done on the phone, but rather sent to a series of servers controlled by another company. Apple is ultimately responsible, but there are other companies who a subcontracted for pieces of the whole process. There is a potential security hole, especially with a company like IBM that is involved in research and new technical products that might compete with Apple.

    I have to admit this is a data security hole I never expected to encounter. It’s a valid concern, as your location, the content of your message, which might be an appointment or reminder to yourself, could be mined and used by Apple. As more companies use voice recognition, or even other types of interactions that might require data processing at a remote location.

    Ultimately I think that we can’t let the market work out the way that our privacy should be handled. I don’t think companies will do a good job of protecting the individuals’ or business partners’ interests. Until there is a good framework in place, anyone that uses third party libraries for processing things like voice recognition ought to be concerned over the data security that is offered as part of the service.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.