Voice of the DBA

Author: way0utwest

  • Scaling Out

    Scaling Out
    One way to scale out

    When I first heard about Service Broker coming in SQL Server 2005, I knew it would be slow to gain traction, and many people would not see the power of guaranteed messaging. However I thought over the next 3-4 years it would catch on as we companies looked to scale out their databases to multiple physical servers.

    That hasn’t really happened, and it seems Service Broker has not been widely adopted by many database developers. It surprises me since it seems like a fantastic way to move data between servers. I’m not sure if too many data professionals think a message means a substantial delay in the movement of data, or if they don’t trust the architecture, but there don’t seem to be many people using this feature in SQL Server.

    They should, however, and I saw a great writeup on scaling out SQL Server with Service Broker. It talks about ways in which you might think about improving your application’s performance, especially at larger scales, but implementing Service Broker as a part of your architecture. It has advantages over replication, and it makes sense to me that this could be an extremely flexible way to handle your peak loads.

    However it isn’t the solution to all problems. It has a steep learning curve, the tools have not advanced very far, and the documentation and descriptions can be confusing and incomplete. I had hoped that things would improve over time, but this feature seems to have been largely ignored. However Service Broker and messaging are great technologies, and I’d encourage you to spend some time learning about it, and look for places in your environment that it might solve scale problems.

    Steve Jones

    The Voice of the DBA Podcasts

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

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

    fs_1

    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.

    fs_2

    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.

    fs_3

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

    fs_4

    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.

    fs_5

    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.

  • Data Gravity

    gravity
    Does data have gravity? And will it affect DBAs’ jobs?

    Many of us have worked with distributed applications at this point in our careers, with different parts of the application located on different physical servers. Some of us might have worked with applications that actually have different servers in different locations, potentially even different countries. That can be challenging, dealing with different time zones, different collations and regional settings on servers and more.

    While many of us are not looking to implement cloud computing now, we may do so in the future, but not for simple cost reasons. There’s a piece about data gravity in the cloud, and how the law can drive the location of data in some cases. Just as many companies that wanted to offer gambling services moved to countries with fewer laws about that business, I would expect that we see some companies moving pieces and parts of their data to other countries to take advantage of more lax or lenient privacy or other laws in the future.

    Most companies won’t bother trying to avoid laws like this right now, but at some point in the future, the cost of moving an application to another country might be tiny. We constantly build better tools for distributed development, deployment, and management, and whether it’s a cloud infrastructure, or just a rented VM, we may find that it becomes worthwhile in some cases to move data, not just for cost reasons, but potentially to reduce the costs of compliance with local laws.

    It’s hard to know whether compliance with the law will ever significantly affect the work a DBA does, but I certainly expect that we will have distributed applications with their various components more widely distributed to challenge us in the future.

    Steve Jones

    The Voice of the DBA Podcasts

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

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