Voice of the DBA

Category: Blog

  • Moving from NoSQL to an RDBMS

    I saw this link about a company that moved (Goodbye, CouchDB) from a NoSQL database (CouchDB) to a relational one (MySQL) because of some problems they experienced. Sauce Labs provides testing services in the cloud for developers using the Selenium framework.

    The first link is a blog post that talks about some of the things they initially liked about CouchDB and then the problems they experienced. Their move to MySQL showed better performance and stability over the CouchDB they were using. It’s an interesting read, not too long, and it seems well thought out.

    I think that this isn’t a knock on NoSQL databases, and I do think there are problem sets they are well suited to solve. I don’t know that I think because your developers don’t like SQL is a good reason, but there are problems with scale and size that are better handled with some NoSQL solutions. I’m an RDBMS person, but I don’t expect everyone to be that way.

    However this does say a few things about NoSQL maturity. Many of these products have gotten popular quickly, with some success stories, but that doesn’t meant they are necessarily ready for your application unless you have staff that have lots of experience with the tools. These are young platforms, they have problems inside the code, especially if you deviate from the way the authors used the platform, and you may have issues.

    Experience matters. Just as many people have issues with SQL Server scaling when they don’t know how to build a good data model or write efficient queries, I suspect that picking a NoSQL database because it’s cool, or seems easy to implement, or it worked well at some other company. I would almost always suggest that you stick with platforms that your staff is very experienced with, since they are likely going to have issues at scale on every platform, and their experience can make a difference.

    Note that this assumes you have good people. Just because you have a DBA with 10 years of experience doesn’t mean he or she is necessarily good. They could easily have 1 year of experience 10 times. You want people that have worked on a variety of systems in different areas.

  • Hustle

    As long as we have people with this attitude, we are going places:

    “If you hustle – all out, fully hustle – and you succeed, then you gain the satisfaction of a job well done. If you hustle and fail, you never have to worry about what might have been if you’d given everything.

    If you slack off, you might get lucky and still succeed, but there’s little satisfaction other than the temporary thrill of escaping with your life. If you slack off and fail, you have to live forever with not knowing what you were truly capable of.”

    From Todd Henry (via Tim Mitchell)

  • The Cloud is Nothing New

    Cloud computing doesn’t seem to have any great definition. Like the physical structures is is named for, it is amorphous, with no defined boundaries, and often, without a recognizable shape. There are even a whole host of different acronyms (IaaS, PaaS, SaaS), which attempt to provide some differentiation between the services being offered by “cloud vendors”.

    However much of what the cloud does is act like an IT department for hire. They provide us with Virtual Machines we can configure as we like. They provide software services, much like Twitter, or even Google, provide for us. They may provide us a way to deploy some software we write, but ultimately this is nothing magic or new.

    Most of what the “cloud” gives us is a new marketing term that has caught on. From the Microsoft “to the cloud” commercials to the buzz about Amazon’s various AWS services.

    It’s hard to even compare the various services from different companies because we don’t really have a common framework of discussion. The “as”s give us a starting point, but when using the generic “cloud” term, it quickly becomes confusing.

    To date, I haven’t seen much innovation in the cloud. The way the services are priced, packaged, and made available is different than in the past, but the technology is the same. And in many cases, the deployment of much of this technology on virtual machines makes me think of the mainframe days when we carved up the hardware on one large, monolithic machine into various compartments where different applications could be run, or different programs could be deployed.

    The cloud isn’t a lot different, technically, but the way the services are offered to us is new, and the speed at which they can be built, used, and thrown away, is truly stunning.

    The cloud is nothing new, but it can change the way in which make decisions about our investments, and it can change the timing of the IT investments that we choose to make.

  • Create a Filestream Filegroup for Filetables – SQL Server 2012

    Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the filestream data. This is pretty easy to do, and I’ll show you the SSMS and code versions.

    If you want to know more about these Filestream containers, you can read BOL. Let’s create a simple database:

    -- create a new database
create database UnstructuredData
go

    This is a simple database with my instance defaults in place. It has a single mdf, a single ldf, and the default Primary filegroup. Let’s not add a new filegroup:

    -- add a filestream FG
ALTER DATABASE [UnstructuredData]
  ADD FILEGROUP [FS] CONTAINS FILESTREAM 
GO

    Here I am adding the filegroup (empty) and specifying this as a filestream container. You cannot mix Filestream data and non-Filestream data in the same filegroup in SQL Server 2012.

    To add a file, we can use the ALTER DATABASE command:

    -- Add a file to the Filestream FG
ALTER DATABASE [UnstructuredData] 
  ADD FILE ( NAME = N'UnstructuredFS', 
             FILENAME = N'c:\fs\UnstructuredFS' ) 
     TO FILEGROUP [FS]
go

    Here I am adding a file, which is actually a folder in this case. According to the documentation, the path up to the last folder (c:\fs in this case) must exist, but the last folder (UnstructuredFS) must not.

    You could do all of this in one statement, as shown here:

    CREATE DATABASE [UnstructuredData]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'UnstructuredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FS] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'UnstructuredFS', FILENAME = N'c:\fs\UnstructuredFS' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'UnstructuredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

    This gets you a space for holding your Filestream data. In 2012, you can now have more than one file for Filestream data, so you can separate out your filegroup across different physical locations if you have a need to do so for performance or scalability.

    In the next post, I’ll build a FileTable and store some documents in it.