Voice of the DBA

Category: Blog

  • 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.

  • The Difference Between Change Tracking and Change Data Capture

    Change Tracking (CT) and Change Data Capture (CDC) were both added to SQL Server in 2008. At first it seems like these two items ought to be synonyms, but they’re separate features. They are similar, but there are some differences, and you might choose to use them in different situations.

    Change Tracking (CT)

    CT is not as well known as CDC, and I see it talked about less. This is really a feature that allows the net changes made to data to be easily returned from a query. This only lets you know that a particular row has changed since your last query. You have no idea

    • how many times it’s changed
    • the various change values over time

    The queries you run will return a table that lets you know which rows have changed since the last check, and then let you know the type of DML change. You need to join this table with the source table to actually get the data.

    This is really useful for those applications that cache data and periodically query to update their caches. Lots of .NET frameworks allow this, and it’s a great way to limit the load on your database server.

    Change Data Capture (CDC)

    CDC is more well known, and seemed like a great tool when I first saw it, but like many useful enhancements, there is a bit of complexity that you have to work through in order to understand and use this feature.

    CDC is a little more complex to implement, and it creates a bit more data in your database. You get a change table that is a copy of your table, along with a few additional columns that contain metadata. For each DML operation, you get a row(s) added to the change table. Inserts get one row (new data). Deletes get one row (old data) and updates get two rows (old and new data).

    This gives you lots of history and information about your table, but it’s a lot of data. The amount can grow quickly in a busy database, so you need to be sure that you extract the information you need and prune the change tables periodically.

    Which One?

    Which feature should you use? Is CT better than  CDC? They work differently, and they capture different amounts of data. There’s an entry in BOL that compares them, and you should understand the differences. However you really need to spend time working with both to make a decision about which one meets your needs.

    Pick a table or two, enable one, test with some workload changes, then evaluate. Then repeat with the other. You might find that you need to use CT in some places, and CDC in others, depending on the downstream processing of the data.