Voice of the DBA

Category: Blog

Enabling Filestream in SQL Server 2012

Filestream is a cool feature, albeit one that’s cumbersome to use in SQL Server 2008 and R2. However the FileTable feature in SQL Server 2012 builds on Filestream and you must enable this feature for FileTable to work.

There is a good document in BOL about this. It basically has you doing a few different things. The first step is to enable the filestream access from outside SQL Server using the SQL Server Configuration Manager. When you start the manager, right click the database service and select properties.

The database engine has to allow for the access to the file system, so this allows that integration. Typically a Windows administrator is required to dot his.

Once that is complete, you will see the account properties for the service. What we want to do is change to the FILESTREAM tab, shown on the bottom row to the right.

On this tab, we can enable Filestream only for SQL Server, for I/O access as well, and specify a share. For Filetable we need to enable both levels of access and create a share name. I chose “SQLFS” for my share name.

Once this is done, you need to switch to Management Studio and then right clicking the instance and selecting properties.

This will bring up a series of selections. If you choose the “Advanced” item on the left, you will get a list of properties for the instance. Near the top, there is the FILESTREAM section. Below I have dropped down the choices. By default, this is disabled, and for FILESTREAM you can select either of the other options, but FileTable needs the full access.

Once this is done, you need to restart the instance to enable the Filestream for the SQL Server. This doesn’t set up FILESTREAM in any of your databases; this merely enables it for the instance. You need to still create the FILESTREAM filegroups in any database that will use FILESTREAM data.

March 7, 2012
Second Chances for Plagiarism

I had an exchange with someone that had plagiarized some work recently. That’s nothing new, and I run into plagiarism constantly as an editor/publisher of information on the Internet.

As a quick aside, if you aren’t sure what plagiarism is, go to plagiarism.org, read Wikipedia, or ask someone. The short answer is, when you write anything, don’t cut/paste from other sources.

This person had copied some sections of a few posts for part of an article they had written. Their work was of good quality, and the plagiarized sections were fairly small. I was actually surprised that these particular sections were copied since they could have easily rewritten them.

In this case, I contacted the person, rejecting the article and chastising them for copying someone else’s work. We actually when back and forth a couple times as this person argued that they had cited the source and their experience in college had been some portions of other work could be used in a thesis. I agreed that portions of work can be used, but they must be cited properly. Quoted or set aside, and footnoted to make it clear that the words used are not the author’s words. Merely adding a note to your article that lists a source is not enough.

The other day I got a note from the author apologizing again, and agreeing that he had misunderstood was plagiarism was, agreeing not to do it again, and asking not to be banned from SQLServerCentral. My policy has been to blanket-ly no longer accept work from someone that plagiarizes since I don’t have enough resources to do extensive checking. However, I don’t often get much of an apology or an appeal of the ban.

I believe in second chances. I don’t think that a mistake, even a voluntary one, should condemn someone forever. We all act poorly at times, we all do things we regret later, or might do differently a second time, and we certainly all make mistakes.

I’ve been reconsidering my ban, and while I think it still makes some sense, I do think that I need to give people second chances at times. I’m unsure of how best to do this, and how best to involve the community. My initial thought is to create some walled area where we post content that needs a plagiarism check. However I’m not sure how to best do this and respect the privacy and reputation of people. If they plagiarize, I’m not against disclosing that, but if the person is doing a good job, I don’t want to bias people against their work.

I wish it were simple, but to me, it’s not. I am open to suggestions.

March 6, 2012
Restoring a Certificate
I have written about creating a certificate and backing up a certificate, and the next step is the restoration of a certificate into a server instance. This post covers the basics of restoring a certificate.

There is no RESTORE CERTIFICATE command because the CREATE CERTIFICATE command accomplishes the same thing. It is expected that many people will get a certificate from another company that is a trusted provider, and in that case, they would create their SQL Server internal certificates from the files provided by the vendor.

However you get your certificate, it will come as two separate files. A .cer file, which is the public key for the certificate, and the .pvk file, for the private key of the certificate. These are the same files that are were created by the makecert.exe utility. They are also the files that were created by the backup in the last post.

To restore the certificate, you perform the same procedure as the CREATE CERTIFICATE. For my exported certificate, I’ll execute:
```
create certificate MySalaryCert
 from file = N'c:\EncryptionPrimer\MySalaryCert.cer'
 with private key
  ( file = N'c:\EncryptionPrimer\MySalaryCert.pvk'
  , decryption by password = N'AReallyStr0ngK#y4You'
   );
```
This will load both the public and private sides of the encryption key into SQL Server and you can view them with this DDL:
```
select
  name
, certificate_id
, pvt_key_encryption_type_desc
, subject
, expiry_date
, start_date
, thumbprint
, pvt_key_last_backup_date
 from sys.certificates
```
This will show you the results. There are other fields here, but these are the ones I look at to check that this is the proper key. Note that on CREATE, the last backup date is NULL, even if you created this from a file.

Note that there are other uses for certificates in Windows. You can install them for IPSec, for SQL Server SSL communications, and more. If you use certificates for any purpose, be sure you can back them up, restore them, and properly manage passwords.
March 5, 2012
PepsiCo Women’s Inspiration Award Winner – If I can do it, so can you…

An inspiring story. A hard one, and one that comes from someone with talent.

I like seeing stories like this, and I think they do give some inspiration to others. Here is someone that was dealt a hard hand in life, and turned it around, with perseverance and hard work.

However this is clearly an exceptional person. I wish we saw more stories from people that were average, or even just slightly above average making a change in their lives, and growing. It’s more realistic, and I think it would motivate more people to actually pursue their goals, rather than looking at someone like this and thinking “I’m not that talented” and I won’t have that level of success.

You should pursue your dreams, not be afraid of the hard work and setbacks, and roll with them..

March 2, 2012