Voice of the DBA

Author: way0utwest

  • Capacity planning for new hardware

    I get asked this question a lot: When getting a new application and database, what kind of hardware do I need?

    The answer is easy: it depends.

    It depends on

    • the load you will put on the SQL Server instance.
    • on the SLA and performance numbers you need.
    • the uptime you need to maintain (RPO/RTO factor in here)

    There are other factors, but essentially the server needs enough hardware to handle the workload in the time you need it to handle things.

    It doesn’t depend on

    • the number of users
    • the number of databases
    • the size of the databases*

    A slight asterisk on the last one. The size of the databases matter for the space you need to buy, but they don’t necessarily affect the RAM or CPU you need. The number of users and databases can contribute to load, but those numbers of a vacuum don’t affect the hardware. I have a test machine with dozens of databases, and it generates no load. Why not? No workload, or not much of one.

    The same thing applies to users. If the users do a lot of work, making changes, querying large data sets, it can be a loaded database. However I had a database one time that had 5,000 clients. It was updated by agent software on desktops in our company every hour. However each update was a singleton update to a specific row, and it was comfortably hosted on a 2CPU, 2GBRAM Standard SQL Server 2000 instance.

    How do I plan?

    Photo Jan 03, 8 50 26 AM

    This is a tough question in many ways. I have searched all over, and asked questions, and even written a SQL University week on it (overview, disk, other). There are two main scenarios here, but they both get similar treatment.

    You have to test your workload against hardware and see what happens. It can be a simulated one, or if you have an existing instance being upgraded, take a real workload.

    There are numerous replay tools or testing tools, but the bottom line is you have to simulate the way you use SQL Server on actual hardware. You can do some extrapolation, but don’t expect it to be a linear change.

    For example, if I have a dual core CPU of xx type, with 1GB of RAM and a 2 drive R1 array, I can’t assume I’ll get twice as much performance with a quad core of the same type with 2GB of RAM and 2 R1 drives. It almost becomes an art to examine the memory usage, the IOPS you are generating, and the percentage of CPU. As you scale up, the usage of those three values might changes and shift. More RAM can reduce IOPS and CPU, especially in reads, but not necessarily. If you have a 1TB table you’re constantly summarizing, you might not get much better performance going from 1 -> 2GB.

    Ultimately it’s a bit of a guess for most of us. Fortunately most of our workloads can be handled by hardware since most databases are relatively small. Test, make some guesses, and go a little big, especially on RAM and CPU. Those are the hardest to add later, from my experience. It seems people expect disks to fill up and we need more space, but it’s often harder to approve CPU/memory upgrades, especially if it requires newer motherboards.

    I wish I had a better answer, and there are numerous articles that don’t seem to me to do a better job, but read as much as you can, and try to learn how others view their systems. Then make your own guesses about what is best for your system.

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