I’ve been looking at some security code in preparation for a few talks I plan on doing next year and there’s one thing I don’t see in many of the samples: backups.
When you get ready to encrypt data in a table, or you enable Transparent Data Encryption (TDE), you will end up creating a key of some sort, or a certificate. Some of these keys, especially the database master key, needs to be backed up. Otherwise you won’t be able to decrypt your database or data later on if you recover this database on another server.
As soon as you create a key for security, make sure you back it up right then. You might forget later, and then it might be too late. A little preparation will go a long way here.
Is it time to consider storing MDFs on NAS devices?
In the two decades I’ve worked with SQL Server, there has been some advice that I’ve always given to people: don’t backup to the network, and don’t try to put a database on a file share. SQL Server hasn’t dealt with either of those situations well for most of my career, and they’ve been a regular source of issues for clients and posters to the forums at SQLServerCentral. Network technology, while quickly improving, was still flaky enough that I always recommend that people keep their data and backups local as a guideline.
However technology evolves, and when it does, it’s time for the advice to change. Lately I have seen more and more people making backups across the network that seem very reliable. Even the tendency of Ethernet protocols to delay packets when collisions occur is mitigated by the increasingly common 1Gbps and 10Gbps network speeds that many companies are using. While I might still be a little nervous about running backups across the network, that’s my personal bias and I don’t know if I would continue to recommend against those backups.
I was surprised, however, to see Kevin Farlee of Microsoft post a blog noting it might be time to reconsider the placement of data files on a network share. He outlines the improvements in technology and protocols that may make this a viable way to store our SQL Server databases in the future. It’s an interesting argument though I suspect that it is slightly premature for most of us.
I do think that as networks become even more reliable, this is one way that we may better manage storage for data in the future, and perhaps see Shared Scalable Databases widely deployed as a way to scale out those read queries on other servers.
It had to change at some point in time. I’ve watched technology advance over the last few years, with dual core, quad core, and even up to twelve core processors appearing from Intel and AMD. With those advances, one of the huge advantages of choosing SQL Server over other RDBMS’s is that SQL Server has been licensed by physical socket rather than core licensing. For less cost, we have been getting more and more processing power for our CPU licenses. However with SQL Server 2012 being released next year sometime, that is due to change. Microsoft released initial licensing information and Denny Cherry (blog | @MrDenny) wrote a great post that helps explain how the licensing will be handled moving forward.
SQL Server 2012 is coming next year
Licensing is a strange topic for me. There are so many restrictions and decisions made by software companies as to how they will allow customers to use their products, it is not only confusing, it can be incredibly frustrating. For most DBAs, we don’t sign the checks to purchase the software, but we are charged with finding the best deals we can for our companies, and making the decisions about which editions are needed, how many licenses are appropriate and which type of license. We are also under pressure to minimize costs, which often results in lots of stress trying to make the best decision for our workload while ensuring compliance with vendor licensing.
I’ve made my thoughts on licensing known before (I prefer licensing by scale only), but at least the new scheme for 2012 is fairly simple. We have fewer editions and we are licensing by cores, so sockets * cores = licenses needed. This is unlike Oracle, which has some strange math required for cores of different speeds. There are a few twists with virtualization, but it does appear that overall it’s a simpler scheme, although perhaps more expensive for some customers. There are some benefits for customers that upgrade licenses, so if you are considering moving servers to 2012, you might want to purchase SQL Server 2008 R2 licenses now. If you upgrade them later, it may be cheaper if you have larger servers, especially those with more than 4 cores per socket.
With the ever increasing density of processing power in today’s CPUs, this changes seems fair to me. Microsoft can license SQL Server however they choose to do so, and I think the move to charging for cores makes sense. I would, however, like to see them just remove the feature restrictions and allow all features to exist in all editions of the product. We could build better applications and SQL Server would look to be a more polished product.
There can be tremendous volatility in short term data.
There seems to be a quest to move closer and closer to real time decision making. Gather data, analyze it, and make decisions instantly, preferably with the help oif expert systems. That makes some sense, and as shown in the article, it can allow analysts to respond to events very quickly, performing verification, fraud checks, or just about anything you can think of.
It’s a good goal, and it can definitely help many companies make more informed decisions at any point in time. However there are problems as well. Sometimes short term data can fundamentally distort the picture of reality. Some of our large stock market meltdowns are the result of automated systems, perhaps not so much expert systems, as very quick reacting systems that might overvalue the last few pieces of data and make decisions that are less than optimal.
We cannot program systems to handle every situation, nor can we even give enough guidance to inexperienced humans that might be involved in the workflow. Instead we ought to recognize that short term data might not represent longer trends and ensure that we have people looking over the data across a longer timeline before any important decisions are made.
Too often it seems we build systems, assuming that more data, delivered quicker, is the way to prevent poor business decisions. We might easily overwhelm other systems, or people with too much data, delivered too quickly, or used to inform decisions too quickly. Real time systems can provide many benefits, but their use should be tempered with this saying I have long believed: computers give us the power to make mistakes quicker than ever before.
Voice of the DBA 