Voice of the DBA

Tag: syndicated

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.
May 14, 2012
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.
May 10, 2012
T-SQL Tuesday #30 – Ethics
This month the topic is hosted by Chris Shaw, and his topic is ethics.

The blog party is the second Tuesday of each month. There is a theme and it is hosted by a different blogger every month. I’ve got a complete list of topics on my blog if you want to read about the past entries.

It was started by Adam Machanic and if you want to host one, let him know. It can be fun, though a little busy as you compile a summary of the posts from the month.

Ethics

I thought this was a great topic, and I’ve actually written on it before. Chris asks a few questions about ethics and they are good ones. I’d like to think that most people have a good code of ethics, but I also think many people haven’t really thought through what their ethics are on this situation. You should do your job, and do what you’re told, but I think breaking the law, or performing an action that violates your moral principles is a no-no.

However when you get into more subtle issues, like the one Chris talks about with regard to security, what do you do?

Overall you have to follow your moral compass and use The Test.
- If you have any doubt about what you’ve been asked to do, seek a second opinion, and get a confirmation from your boss (or their boss) in writing.
- If you are sure it’s wrong, don’t do it, give you reasons, and let them get someone else to do it.
- If it’s illegal, you should report it.
I know sometimes this puts you in a bad situation, and you may fear for your job. That’s natural, and understandable, especially if you are the sole breadwinner for your family.

However you also have to live with your actions, and they reflect on your soul daily. I would ask that you make the right decision, even when it’s not the most popular, profitable, or palatable one.
May 8, 2012
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
May 7, 2012