Voice of the DBA

Author: way0utwest

  • 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

  • Creating Strong Passwords

    I was writing a presentation recently on encryption and one of the important things to show in the presentation is the use of strong passwords. Many of the encryption functions will use passwords as an alternate way to secure keys, and I hate showing bad habits, like “mystrongpassword” in examples.

    I searched around and find some interesting tricks for creating strong passwords. These were two guides that I liked:

    There are some good items in there, including the use of phrases and suffixes instead of trying to build some long word that you can easily remember. Personally I use a combination of techniques, and so far (knock on wood), my passwords have been fairly safe. At least as far as I know.

    I also use Password Safe to store passwords for various sites and I keep that in synch with Dropbox across my desktop, my iPhone (pwsafe), and my Macbook Air (Password Gorilla).

    This site also has tricks, but an interesting password tester as well that helps you score your passwords. A few of mine score in the mediocre range, which has me rethinking them and adding a few more characters to the length.

    Lastly, if you hate typing passwords, and I completely understand that, learn to type. The better you type, at least those items you type a lot, the less hassle better security is for you.

  • SQL Server 2012 Licensing VMs without SA

    I ran across a blog talking about Microsoft licensing, and found it very interesting. I learned a few things, and it’s a bookmark I’ll keep in mind.

    A subtle change, one that caught my eye. In SQL Server 2012, if you don’t have Software Assurance, you have this limitation for Enterprise Edition:

    “SQL Without Active Software Assurance (SA) VM density is limited to one VM per physical Core. VM Density can never rise above the hardware footprint. “

    Mmmm, that’s interesting. I know a lot of people were foregoing SA with after the delays with SQL Server 2005, and the idea they would not upgrade so frequently. However if you are looking to remove some old hardware, and migrate some of the older machines that might run fine in 2×2 or 4×4 environments to their own VM on a dense environment, you might be out of luck.

    Apparently that’s similar licensing to SQL Server 2008 R2. If you were upgrading from SQL Server 2008 and had SA, you had unlimited VM rights grandfathered in with R2.

    Licensing is strange and complex, especially with regards to virtualization. If  you have specific questions about something you are doing, especially if you are moving to a high volume VM environment, check with MS licensing first. It’s easy to get out of compliance, and while you won’t have to pay more yourself, you will get blamed if your company pays more.

  • 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