Voice of the DBA

Category: Editorial

  • Cloud Safety

    This doesn't look safe.

    One of the big concerns with databases and cloud computing is security. I recently ran across an article that asked the question, “how can you make sure your cloud provider can protect your data?”. There aren’t any guarantees, but there is some information in the article about the certifications that your provider might have earned and be able to prove. There are FIPS 200/SP 800-53, ISO 27001/27002, and SSAE 16, SOC 2 & 3 standards listed. Whether these are applicable to you, or provide the security you need is something you will have to decide. Be careful, and do your homework as some of the certifications mean that the certifying company can give you an opinion on security, which is their own and maybe different from the one another company would give.

    The article did make some good points about evaluating security for your company. You should understand what these certifications means, and in some cases, make sure the provider has multiple designations. For example, both ISO 27001 and ISO 27002 are needed together to ensure a reasonable level of security. The provider should also be able to provide you with copies of their audits, and contract with you to ensure ongoing audits and vulnerability tests. These are reasonable requests, and they are measures you should have in place for any of your facilities.

    Are SQL Azure and Windows Azure secure? Windows Azure does have the ISO 27001: 2005 certification, but I haven’t seen ISO 27002 listed. I also don’t think this covers SQL Azure, but it’s not clear. There is a note that Microsoft has completed the ISO 27001 and the SAS 70 Type I and II certifications, but I haven’t seen PCI listed for Microsoft. It is listed for Amazon Web Services, one of the other large SQL Server cloud hosting providers.

    Security is a process, not a product. It is something you need to create, adapt, alter, and monitor on a regular basis. Some cloud providers are diligent about applying and documenting their security controls and audit results, some are not. If you need secure services, it’s important that you get your requirements in writing from your cloud provider, or find a new vendor. No matter what work your cloud provider does to secure their facilities and network, however, it’s even more important that you develop your application securely. Restrict rights, avoid SQL Injection holes, and implement the best practices for secure development of applications as you write your code. It’s usually easier to attack your application than the hosting provider.

    Steve Jones

    The Voice of the DBA Podcasts

  • Referencing Remote Data

    Maybe if I searched more, I'd use synonyms

    One of the features added to SQL Server a few years ago were the ability to create synonyms and use those to reference other objects. The ability to create synonyms is something that I had wanted for years in SQL Server, but when they were released, I found them to be a tool that I rarely reached for. Whether it was because this was something I rarely needed to accomplish, or because my habits were too ingrained, I’m not sure, but I have only created synonyms for testing purposes and not for use in any production databases.

    When a developer needs to reference data in another database (or on another server), they have a variety of ways in which they can do this. Some people prefer a three or four part naming convention, others use a view local to the database, and still others might use synonyms. While they all work, from a maintenance standpoint, I think a view or synonym provide a nice layer of abstraction while minimizing the potential maintenance headaches of future changes.

    If you find synonyms more useful than local views, I would be interested in knowing why. They seem to almost operate in the same way to me, but for some reason I find views to be easier to track and manage. Perhaps it’s just an ingrained habit from years of making do with views, or maybe it’s my habit of browsing for objects, instead of using a tool like SQL Search.

    Whichever method you use, I do urge you to always consider a layer of abstraction. That other database you reference might be located on the same instance today, but in the future it might grow and require it’s own instance. If you have three part naming buried in all of your stored procedure or application code, it might not be as simple as a global search and replace to make changes. If it’s not, then you are wasting development time down the road by not having a layer of abstraction implemented at the beginning.

    Steve Jones

    Steve Jones

    The Voice of the DBA Podcasts

  • Read-only Data

    We keep gathering, storing, and managing more and more data. Many of our systems could use an archiving plan to migrate older data to another database or system where it can be accessed, but it won’t impact the performance of queries against our current data. If you don’t have any type of archive plan, you might consider building one for any future tables you design.

    Do you manage read only data differently?

    Once you migrate data to a new set of tables, typically you would consider that older data to be read only, and potentially mark it as such in it’s own storage location, perhaps even adding more indexes than you have on the current data. And if the data is static, then it doesn’t change from week to week, and you can reduce the amount of backups that you create from this data.

    However you can’t eliminate backups. There is still the possibility that you might have a disaster situation and need to recover the data. If your last backup of the read only database or file group is 18 months old, will you be able to find it? That can be quite a challenge, and I wanted to get some opinions this Friday about how to handle this situation. The poll this week is:

    How often do you back up read only data?

    I am also curious how you manage tracking these infrequent backups and recovering them if you must perform a restore. I’ll admit that I don’t have a great solution other than scheduling some regular backup interval, something like once a quarter and documenting the location someplace that would be accessible in a disaster.

    Steve Jones

    The Voice of the DBA Podcasts

  • Are Auto-Updates Good?

    I don't use IE, but I am glad auto updates are coming.

    There has been news recently about automatic updates coming to Internet Explorer. I haven’t paid a lot of attention for the simple reason that I almost never use IE. Since most every site I use these days support Firefox and Chrome, including many Microsoft ones, I use those browsers. While IE might have improved, I’m not sure that’s a reason for me to switch from the Firefox browser that I am very comfortable with.

    On one hand, I think this is a great idea for the browsers. It has the potential to not only ensure better security on the desktop, but potentially also making life easier for developers. If the browsers automatically update to the latest code, that means that the majority, or maybe all, of your clients will be running the same browser. That would greatly simplify the testing and coding requirements for developers.

    It might also greatly enhance the application experience for clients since every IE9, or Firefox 4, or Chrome 15 browser will be the same. I don’t know that I would want this for SQL Server, but for the browser, and potentially other software, I think it’s great.

    However silent updates could also break applications. That could result in a mad scramble when an update occurs by software developers that race to fix problems. I’ve always preferred to apply updates regularly, but not too aggressively. I like to be able to test changes before I deploy them to all my clients. This doesn’t give me that flexibility, but there is one benefit that I do like: my clients won’t have excuses for delaying updates.

    Now when I get an update on a developer’s machine, and they perform a build, I can be confident that the client will have installed the same updates.

    Steve Jones

    The Voice of the DBA Podcasts