Voice of the DBA

Tag: sql server

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

  • SQL Server on RDS

    Amazon RDS
    SQL Server 2008 R2 is now on RDS

    A database service in the cloud.

    Imagine being able to connect to SQL Server on a remote machine, without having to administer the underlying OS, and without having to change the database code that you build against your local instance? Amazon has provided that with it’s Relational Database Service (RDS) for Oracle and MySQL, and has just added SQL Server 2008 R2 as well. Red Gate Software and SQLServerCentral are happy to partner with Amazon to announce the launch of this service.

    RDS gives you the chance to deploy a SQL Server instance that’s essentially the same as the one you install on your local servers, allowing you to developer and test the same code you will deploy to the cloud, without compromising the features that are available. There are a few restrictions at the instance level since you don’t have access to the underlying host OS, but the benefit is that you don’t need to administer the OS, and if you just need a database service for an application, this  is one way to get it setup and running quickly. Specify a few parameters, and Amazon will handle the rest. In a few minutes, you have an instance that you can connect to and use. We’ve tested the Red Gate Software tools against RDS and they work just like they work on any other instance.

    The cloud isn’t for every database, and this service won’t work for every type of application, but for many people that need a database and a simple web application deployed, this is a great way to get started with a minimal investment in your public infrastructure. And you can leverage your local SQL Server 2008 R2 Developer instance to develop code that will work, without worrying about the services in the cloud.

    It’s a little scary to me to think about letting go of the control of managing my own database, but there are good capacity limits, security improves, and for many applications, just having a database service available would work great. And they’ll even handle the backups automatically for your new database. How many system administrators have you worked with that didn’t do that?

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Finding the Default Trace File

    A post more for me than for anyone else, since I’ll look for something in the default trace and I often need this snippet of code:

    select path 
 from sys.traces 
 where is_default = 1

    That returns something like this:

    path

    ————————————————————————-

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc

    This is the current file being used by the trace, which allows me to then look into the file for some event.

    From here, I usually start running a query like this:

    select 
   e.name as eventclass
 , t.textdata
 , t.starttime
 , t.error 
 , t.hostname
 , t.ntusername
 , t.ntdomainname
 , t.clientprocessid
 , t.applicationname
 , t.loginname
 , t.spid
 from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc', default) t
  inner join sys.trace_events e 
     on t.eventclass = e.trace_event_id 
  where eventclass = xx

    In this query, I take the output from the first query and use that as the FROM file and then include an event class number in the WHERE clause. I needed this today, running a check for the latest DBCC, and so I used the class 116.

    You can get a list of event classes here: Trace Event Classes

  • Learn XML

    XML sign on cab
    XML is going to be more important for DBAs in the future.

    I first learned a lot about XML from the PDC conference in Denver in 1998. I attended a number of sessions and thought this was a great technology that would soon dominate the data exchange markets. At the time I was developing an interface for an EDI system, and we knew XML would be important in the future.

    Over the years I’ve seen XML use grow, especially as a way to move data around between many systems, but I’m not sure it dominates data exchanges. There are still plenty of Excel documents and CSV files produced on a regular basis that must be imported into SQL Server. Many DBAs have an idea of what XML is, but they don’t often feel comfortable working with it, especially when it comes to the various XQuery functions inside SQL Server.

    As I’ve seen the use of XML increase in SQL Server. Not inside the database, but in the tools that we use as DBAs and data professionals. Our execution plans are XML documents, and XML is slowly becoming a more prevalent part of SQL Server with every release. There’s one place, however, that makes a very compelling case for all DBAs to learn how to work with and query XML: Extended Events.

    Understanding eventing will be more and more important over time. Profiler has been deprecated and will be removed at some point. DBAs should understand Extended Events and learn to work with them. That includes learning to query the data returned from the events, which you might have guessed, is in XML. I watched Jonathan Kehayias demonstrate this last year, and I’ve been following his writing on the topic, which includes

    Do yourself a favor, and learn how to work with XML and become comfortable querying them over the next year. We have some great articles on XML here on the site, and I you’ll be glad you did in the future.

    Steve Jones